[ Users ] [ Datafiles ] [ Rollback ] [ Memory ]
[ Pool Sizes ] [ Shared Pool ] [ Buffer Pool ] [ Advices ] [ SysStat ]
[ Events ] [ Wait Objects ] [ Invalid Objects ] [ Misc ] |
Common Instance Information | |
---|---|
Hostname: | buzz |
Instance: | ORADB |
Version: | 9.2.0.1.0 |
Archiver: | STARTED |
ArchivedLogSize: | MB |
FileSize (Data+Log) | 11,180.00 MB |
Startup / Uptime | 28.11.2003 16:43 / 46.05 d |
Report generated: | 13.01.2004 17:53 |
User Information ![]() | ||||||
---|---|---|---|---|---|---|
Username | Account Status | Lock Date | Expiry Date | Default TS | Temporary TS | Created |
SYS | OPEN | - | - | SYSTEM | TEMP | 28.10.2003 |
SYSTEM | OPEN | - | - | SYSTEM | TEMP | 28.10.2003 |
OUTLN | OPEN | - | - | SYSTEM | TEMP | 28.10.2003 |
USER0 | OPEN | - | - | DATA_DDB | TEMP | 19.11.2003 |
INLN | OPEN | - | - | SYSTEM | TEMP | 19.11.2003 |
USER1 | OPEN | - | - | DATA01 | TEMP | 01.12.2003 |
DBSNMP | OPEN | - | - | SYSTEM | TEMP | 28.10.2003 |
WUM | EXPIRED & LOCKED | 28.10.2003 | - | SYSTEM | TEMP | 28.10.2003 |
Admins | |
---|---|
User | Admin Option |
SYS | YES |
SYSTEM | YES |
Data Files | |||||||||
---|---|---|---|---|---|---|---|---|---|
Tablespace | Datafile | Status | Enabled | Size (kB) | Free (kB) | Used (%) | Phys.Reads | Phys.Writes | Avg.I/O Time |
DATA01 | /opt/oraData/ORADB/v01/data01.dbf | ONLINE | READ WRITE | 4,710,400.00 | 57,280.00 | 98.78 | 36,371,076 | 9,553,609 | -967,520,879 |
INDX01 | /opt/oraData/ORADB/v01/indx01.dbf | ONLINE | READ WRITE | 2,129,920.00 | 8,128.00 | 99.62 | 202,984,761 | 18,401,904 | 1,562,545,745 |
SYSTEM | /opt/oraData/ORADB/v01/system.dbf | SYSTEM | READ WRITE | 153,600.00 | 11,472.00 | 92.53 | 106,514 | 5,962 | 0 |
TEMP | /opt/oraData/ORADB/v01/temp.dbf | ONLINE | READ WRITE | 122,880.00 | 11,472.00 | 90.66 | 106,514 | 5,962 | 0 |
UNDOTBS | /opt/oraData/ORADB/v01/undotbs.dbf | ONLINE | READ WRITE | 1,894,400.00 | 1,734,336.00 | 8.45 | 27,277 | 25,679,417 | -1,727,836,658 |
Rollback Segments ![]() | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
Segment | Status | Size (kB) | OptSize (kB) | HWMSize (kB) | Waits | XActs | Shrinks | Wraps | AveShrink | AveActive |
SYSTEM | ONLINE | 392.00 | 0.00 | 392.00 | 0 | 0 | 0 | 0 | 0 | 0 |
_SYSSMU1$ | ONLINE | 15,480.00 | 0.00 | 227,448.00 | 2,729 | 0 | 1,266 | 14,904 | 2,907,551 | 11,288,223 |
_SYSSMU10$ | ONLINE | 15,480.00 | 0.00 | 212,088.00 | 2,584 | 0 | 1,259 | 15,163 | 2,755,166 | 10,384,987 |
_SYSSMU2$ | ONLINE | 16,504.00 | 0.00 | 196,728.00 | 3,006 | 0 | 1,289 | 15,233 | 2,589,155 | 11,821,724 |
_SYSSMU3$ | ONLINE | 15,480.00 | 0.00 | 192,632.00 | 3,721 | 0 | 1,471 | 16,629 | 180,435 | 11,238,835 |
_SYSSMU4$ | ONLINE | 15,480.00 | 0.00 | 230,520.00 | 3,574 | 0 | 1,571 | 17,432 | 958,636 | 11,295,682 |
_SYSSMU5$ | ONLINE | 16,504.00 | 0.00 | 204,920.00 | 3,027 | 0 | 1,284 | 15,523 | 2,878,786 | 11,947,996 |
_SYSSMU6$ | ONLINE | 15,480.00 | 0.00 | 224,376.00 | 2,745 | 0 | 1,234 | 14,890 | 2,743,855 | 11,182,469 |
_SYSSMU7$ | ONLINE | 16,504.00 | 0.00 | 196,728.00 | 3,222 | 0 | 1,321 | 15,455 | 2,808,473 | 11,779,619 |
_SYSSMU8$ | ONLINE | 16,504.00 | 0.00 | 198,776.00 | 3,664 | 0 | 1,439 | 16,383 | 298,122 | 11,845,204 |
_SYSSMU9$ | ONLINE | 16,504.00 | 0.00 | 221,304.00 | 3,566 | 0 | 1,530 | 17,103 | 768,527 | 11,902,584 |
Memory Values | |
---|---|
Name | Size |
Fixed Size | 441.94 kB |
Variable Size | 1,490,944.00 kB |
Database Buffers | 212,992.00 kB |
Redo Buffers | 49,292.00 kB |
shared_pool_size | 114,688.00 kB |
shared_pool_reserved_size | 5,734.40 kB |
large_pool_size | 16,384.00 kB |
java_pool_size | 65,536.00 kB |
buffer_pool_keep | 0 kB |
buffer_pool_recycle | 0 kB |
global_context_pool_size | 0 kB |
olap_page_pool_size | 32,768.00 kB |
Pool Sizes | |
---|---|
Pool | Space |
Shared_Pool_Size | 114,688.00 kB |
Shared_Pool_Reserved_Size | 5,734.40 kB |
Large_Pool_Size | 16,384.00 kB |
Java_Pool_Size | 65,536.00 kB |
Sort_Area_Size | 64.00 kB |
Sort_Area_Retained_Size | 0.00 kB |
Pool | Free Space |
shared pool | 97,346.29 kB |
large pool | 16,384.00 kB |
java pool | 65,536.00 kB |
Shared Pool Information | |||||
---|---|---|---|---|---|
Library Cache | |||||
The following cases are indicators that SHARED_POOL_SIZE may have to be increased:
| |||||
NameSpace | Gets | Pins | Reloads | RPP | GetHitRatio (%) |
SQL AREA | 6,769,067 | 800,824,588 | 15 | 0.00 | 99.99 |
TABLE/PROCEDURE | 50,454 | 260,044,809 | 424 | 0.00 | 96.69 |
BODY | 630 | 630 | 0 | 0.00 | 95.71 |
TRIGGER | 15,161 | 15,161 | 0 | 0.00 | 99.97 |
INDEX | 2,171 | 2,136 | 0 | 0.00 | 1.84 |
CLUSTER | 869 | 926 | 0 | 0.00 | 98.85 |
OBJECT | 0 | 0 | 0 | 0.00 | 100.00 |
PIPE | 0 | 0 | 0 | 0.00 | 100.00 |
JAVA SOURCE | 0 | 0 | 0 | 0.00 | 100.00 |
JAVA RESOURCE | 0 | 0 | 0 | 0.00 | 100.00 |
JAVA DATA | 0 | 0 | 0 | 0.00 | 100.00 |
Row Cache | |||||
If Ratio = (getmisses/gets)*100 > 15, SHARED_POOL_SIZE may have to be increased: | |||||
Parameter | Gets | GetMisses | Ratio | ||
dc_free_extents | 7,446 | 3 | 0.04 | ||
dc_segments | 108,560 | 2,672 | 2.46 | ||
dc_tablespaces | 358,298 | 10 | 0.00 | ||
dc_tablespace_quotas | 2,854 | 25 | 0.88 | ||
dc_files | 979 | 10 | 1.02 | ||
dc_users | 179,306 | 23 | 0.01 | ||
dc_rollback_segments | 471,370 | 11 | 0.00 | ||
dc_objects | 27,501 | 3,726 | 13.55 | ||
dc_global_oids | 4 | 2 | 50.00 | ||
dc_constraints | 7,596 | 7,589 | 99.91 | ||
dc_object_ids | 13,868 | 3,494 | 25.19 | ||
dc_sequences | 6,500,715 | 334 | 0.01 | ||
dc_usernames | 33,226 | 10 | 0.03 | ||
dc_histogram_defs | 7,950 | 7,489 | 94.20 | ||
dc_table_scns | 392 | 392 | 100.00 | ||
dc_profiles | 13,779 | 1 | 0.01 | ||
dc_user_grants | 126,535 | 21 | 0.02 |
Buffer Pool Statistics | ||||
---|---|---|---|---|
Ratio = physical_reads/(consistent_gets+db_block_gets) should be < 0.9: | ||||
Pool | physical_reads | consistent_gets | db_block_gets | Ratio |
DEFAULT | 402,085,999 | 1,033,967,809 | 4,832,840,113 | 0.07 |
DB Cache Advice | ||||
---|---|---|---|---|
The following values are an estimation how changing the size of a given buffer would affect the amount of physical reads. |
||||
Pool | Size | Buffers | Estd.PhyRd Factor | Estd.PhyRds |
DEFAULT | 16 M | 2,002 | 23.5% | 496,676,612 |
DEFAULT | 32 M | 4,004 | 19.2% | 479,412,056 |
DEFAULT | 48 M | 6,006 | 17.9% | 474,148,367 |
DEFAULT | 64 M | 8,008 | 17.0% | 470,301,261 |
DEFAULT | 80 M | 10,010 | 15.8% | 465,711,940 |
DEFAULT | 96 M | 12,012 | 14.5% | 460,273,604 |
DEFAULT | 112 M | 14,014 | 13.0% | 454,136,868 |
DEFAULT | 128 M | 16,016 | 11.3% | 447,485,568 |
DEFAULT | 144 M | 18,018 | 9.2% | 439,220,802 |
DEFAULT | 160 M | 20,020 | 6.9% | 429,951,970 |
DEFAULT | 176 M | 22,022 | 4.3% | 419,290,920 |
DEFAULT | 192 M | 24,024 | 1.8% | 409,227,712 |
DEFAULT | 208 M | 26,026 | 0.0% | 402,086,000 |
DEFAULT | 224 M | 28,028 | -1.6% | 395,520,001 |
DEFAULT | 240 M | 30,030 | -3.2% | 389,155,694 |
DEFAULT | 256 M | 32,032 | -5.1% | 381,490,818 |
DEFAULT | 272 M | 34,034 | -7.3% | 372,614,587 |
DEFAULT | 288 M | 36,036 | -10.1% | 361,586,508 |
DEFAULT | 304 M | 38,038 | -12.8% | 350,629,545 |
DEFAULT | 320 M | 40,040 | -16.6% | 335,513,327 |
PGA Target Advice | |||||
---|---|---|---|---|---|
The following values are an estimation how changes to the PGA size would affect the performance. | |||||
Size | SizeFactor | BytesProcessed | XtraBytesRW | CacheHits | OverAlloc |
10,485,760 | -50.0% | 2,262,507,520 | 1,809,887,232 | 56.0% | 33 |
15,728,640 | -25.0% | 2,262,507,520 | 1,809,887,232 | 56.0% | 33 |
20,971,520 | 0.0% | 2,262,507,520 | 1,528,704,000 | 60.0% | 4 |
25,165,824 | 20.0% | 2,262,507,520 | 1,528,704,000 | 60.0% | 0 |
29,360,128 | 40.0% | 2,262,507,520 | 1,528,704,000 | 60.0% | 0 |
33,554,432 | 60.0% | 2,262,507,520 | 1,528,704,000 | 60.0% | 0 |
37,748,736 | 80.0% | 2,262,507,520 | 1,528,704,000 | 60.0% | 0 |
41,943,040 | 100.0% | 2,262,507,520 | 1,528,704,000 | 60.0% | 0 |
62,914,560 | 200.0% | 2,262,507,520 | 829,679,616 | 73.0% | 0 |
83,886,080 | 300.0% | 2,262,507,520 | 440,202,240 | 84.0% | 0 |
125,829,120 | 500.0% | 2,262,507,520 | 440,202,240 | 84.0% | 0 |
167,772,160 | 700.0% | 2,262,507,520 | 440,202,240 | 84.0% | 0 |
SYSSTAT Info | ||
---|---|---|
Name | Value | Description |
Percent DiskSorts (of DiskSorts + MemSorts) | 0.00 | Should be less than 5% -
higher values are an indicator to increase SORT_AREA_SIZE , but you of course have to consider the amount of physical memory available on your machine. |
summed dirty queue length / write requests | If this value is > 100, the LGWR is too lazy -- so you may want to decrease DB_BLOCK_MAX_DIRTY_TARGET | |
free buffer inspected / free buffer requested | 0.03 | Increase your buffer cache if this value is too high |
redo buffer allocation retries / redo blocks written | 0.00 | should be less than 0.01 - larger values indicate
that the LGWR is not keeping up. If this happens, tuning the values for LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT (or, with Oracle 9i,
FAST_START_MTTR_TARGET ) can help to improve the situation. |
redo log space requests | 1,939.00 | how often the log file was full and Oracle had to wait for a new file to become available |
table fetch continued row | 115.00 | How many migrated rows did we encounter during this instances life time? |
Selected Wait Events (from v$system_event) | |||||
---|---|---|---|---|---|
Name | Totals | Total WaitTime (s) | Avg Waited (ms) | Timeouts | Description |
free buffer waits | 20 | 1,496 | 74,800.0 | 8 | This wait event occurs when the database attemts to locate a clean block buffer but cannot because there are too many outstanding dirty blocks waiting to be written. |
buffer busy waits | 36,920 | 679 | 18.4 | 0 | buffer busy waits indicate
contention for a buffer in the SGA. You may need to increase the INITRANS parameter for a specific table
or index if the event is identified as belonging to either a table or index. |
db file sequential read | 215,580,777 | 10,467,980 | 48.6 | 0 | Indicator for I/O problems on index accesses (Consider increasing the buffer cache when value is high) |
db file scattered read | 23,924,760 | 3,724,467 | 155.7 | 0 | Indicator for I/O problems on full table scans (On increasing DB_FILE_MULTI_BLOCK_READ_COUNT if this value is high see the first block of Miscellaneous below) |
undo segment extension | 11,106,882 | 4,379 | 0.4 | 11,106,706 | High wait times here could indicate a problem with the extent size, the value of MINEXTENTS, or possibly IO related problems. |
enqueue | 197 | 310 | 1,573.6 | 0 | This type of event may be an indication that something is either wrong with the code or possibly the physical design. |
latch free | 3,613 | 3,051 | 844.5 | 1,894 | Latch free waits can occur for a variety of reasons including library cache issues, OS process intervention, and so on. |
LGWR wait for redo copy | 5,371 | 14 | 2.6 | 8 | This only needs your attention when many timeouts occur. A large amount of waits/wait times does not necessarily indicate a problem - normally it just says that LGWR waited for incomplete copies into the Redo buffers that it intends to write. |
log file switch (checkpoint incomplete) | 0 | 0 | 0.0 | 0 | Higher values for this event indicate that either your ReDo logs are too small or there are not enough log file groups |
log file switch completion | 1,737 | 9,812 | 5,648.8 | 3 | You may consider increasing the number of logfile groups. |
log buffer wait | 0 | 0 | 0.0 | 0 | If this value is too high, log buffers are filling faster than being emptied. You then have to consider to increase the number of logfile groups or to use larger log files. |
log buffer space | 0 | 0 | 0.0 | 0 | This event frequently occurs when the log buffers are filling faster than LGWR can write them to disk. The two obvious solutions are to either increase the amount of log buffers or to change your Redo log layout and/or IO strategy. |
log file parallel write | 801,190 | 39,204 | 48.9 | 800,280 | Indicator for Redo log layout and/or IO strategy As the wait times on these events become higher, you will notice additional Wait Events such as log buffer space, log file switch (archiving needed), etc. |
log file single write | 1,654 | 496 | 299.9 | 0 | |
SQL*Net message to client | 520,363,091 | 110,904 | 0.2 | 0 | These wait events occur when the Database unexpectedly looses Net8 connectivity with a remote client or Database. Frequent occurences of these events could indicate a networking issue. |
SQL*Net message to dblink | 0 | 0 | 0.0 | 0 |
Objects causing Wait Events ![]() | ||
---|---|---|
On the following segments we noticed one of the events buffer busy waits, db file sequential read, db file scattered read or free buffer waits at the time the report was generated. |
Top 10 IO Objects
![]() | ||||
---|---|---|---|---|
Object | Type | TableSpace | Wait Type | Waits |
USER1.TAR_SYSDAT_IDX | INDEX | INDX01 | physical reads | 193,533,789 |
USER1.TAR_IDX1 | INDEX | INDX01 | physical writes | 15,254,569 |
USER1.TARVALUE | TABLE | DATA01 | physical writes | 9,165,685 |
USER1.TARVALUE | TABLE | DATA01 | physical reads | 9,010,932 |
USER1.TAR_IDX1 | INDEX | INDX01 | physical reads | 7,866,339 |
USER1.TAR_SYSDAT_IDX | INDEX | INDX01 | physical writes | 1,028,840 |
USER1.TAR_YDAY_IDX | INDEX | INDX01 | physical writes | 909,281 |
USER1.TAR_YDAY_IDX | INDEX | INDX01 | physical reads | 828,748 |
USER1.TAR_PK | INDEX | INDX01 | physical writes | 571,967 |
USER1.TAR_PK | INDEX | INDX01 | physical reads | 524,994 |
Top 10 BufferBusy Objects
![]() | ||||
---|---|---|---|---|
Object | Type | TableSpace | Wait Type | Waits |
USER1.TARVALUE | TABLE | DATA01 | buffer busy waits | 239 |
USER1.TAR_IDX1 | INDEX | INDX01 | buffer busy waits | 4 |
USER1.TAR_YDAY_IDX | INDEX | INDX01 | buffer busy waits | 2 |
USER1.TAR_SYSDAT_IDX | INDEX | INDX01 | buffer busy waits | 2 |
Top 10 RowLock Objects
![]() | ||||
---|---|---|---|---|
Object | Type | TableSpace | Wait Type | Waits |
USER1.TAR_SYSDAT_IDX | INDEX | INDX01 | row lock waits | 167 |
USER1.TAR_YDAY_IDX | INDEX | INDX01 | row lock waits | 137 |
USER1.TAR_IDX1 | INDEX | INDX01 | row lock waits | 120 |
USER1.TAR_PK | INDEX | INDX01 | row lock waits | 107 |
Enqueue Waits ![]() | |||||
---|---|---|---|---|---|
The following queues caused waits during the recent uptime of this instance. Ordered by cumulative wait time (desc) | |||||
Queue | Total Requests | Total Waits | Succ.Requests | Failed Req. | Cum.WaitTime |
CF | 1,789,551 | 183 | 1,789,549 | 2 | 2,067 |
TC | 70 | 14 | 70 | 0 | 1,111 |
Invalid Objects | ||||
---|---|---|---|---|
The following objects may need your investigation. These are not necessarily problem indicators (e.g. an invalid view may automatically re-compile), but could be: | ||||
Owner | Object | Typ | Created | Last DDL |
SYS | ALL_REPGROUPED_COLUMN | VIEW | 28.10.2003 04:50 | 28.10.2003 04:50 |
SYS | ALL_REPPARAMETER_COLUMN | VIEW | 28.10.2003 04:51 | 28.10.2003 04:51 |
SYS | DBA_REPGROUPED_COLUMN | VIEW | 28.10.2003 04:50 | 28.10.2003 04:50 |
SYS | DBA_REPPARAMETER_COLUMN | VIEW | 28.10.2003 04:51 | 28.10.2003 04:51 |
SYS | DBMS_REPCAT_RGT_CUST | PACKAGE BODY | 28.10.2003 04:52 | 28.10.2003 04:52 |
SYS | DBMS_REPCAT_UTL4 | PACKAGE BODY | 28.10.2003 04:52 | 28.10.2003 04:52 |
SYS | USER_REPGROUPED_COLUMN | VIEW | 28.10.2003 04:50 | 28.10.2003 04:50 |
SYS | USER_REPPARAMETER_COLUMN | VIEW | 28.10.2003 04:51 | 28.10.2003 04:51 |
SYS | _ALL_REPCOLUMN_GROUP | VIEW | 28.10.2003 04:50 | 28.10.2003 04:50 |
SYS | _ALL_REPCONFLICT | VIEW | 28.10.2003 04:51 | 28.10.2003 04:51 |
SYS | _ALL_REPGROUPED_COLUMN | VIEW | 28.10.2003 04:50 | 28.10.2003 04:50 |
SYS | _ALL_REPPARAMETER_COLUMN | VIEW | 28.10.2003 04:51 | 28.10.2003 04:51 |
SYS | _ALL_REPRESOLUTION | VIEW | 28.10.2003 04:50 | 28.10.2003 04:50 |
Miscellaneous | |
---|---|
Name | Value |
If we have many full table scans, we may have to optimize DB_FILE_MULTI_BLOCK_READ_COUNT . Beneath the statistic below, we need the block count of the largest
table to find the best value. A common recommendation is to set DB_FILE_MULTI_BLOCK_READ_COUNT to the highest possible value for maximum performance, which is 32 (256k) in
most environments. The absolute maximum of 128 (1M) is mostly only available on raw devices. | |
table scans (short tables) | 130,011,594 |
table scans (long tables) | 929 |
table scans (rowid ranges) | 0 |
table scans (cache partitions) | 0 |
table scans (direct read) | 0 |
Created by OraRep v0.2.1 © 2003 by Itzchak Rehberg & IzzySoft |