[ TableStats ][ Datafiles ][ DBWR ][ LGWR ]
[ Pool Sizes ] [ Shared Pool ] [ Buffer Pool ] [ Advices ] [ SysStat ]
[ Events ] [ Buffer Waits ] [ FreeList Contention ] [ Wait Objects ] [ Enqueue Waits ]
[ Invalid Objects ] |
Common Instance Information | |
---|---|
Hostname: | buzz |
Instance: | oradb |
Version: | 9.2.0.5.0 |
Archiver: | STOPPED |
ArchivedLogSize: | MB |
FileSize (Data+Log) | 2,270.00 MB |
Startup / Uptime | 18.10.2005 13:58 / 2.93 d |
Report generated: | 21.10.2005 12:20 |
Data Files | |||||||||
---|---|---|---|---|---|---|---|---|---|
Alerts and warnings (highlighted table cells) indicate a write enabled non-autoextensible data file filled > 85% / 95% | |||||||||
Tablespace | Datafile | Status | Enabled | Size | Free | Used | Phy.Reads | Phy.Writes | Avg.I/O Time |
DATA | /database/oradb/data/data_1.dbf | ONLINE | READ WRITE | 500.0 M | 143.3 M | 71.35 % | 127,357 | 736 | 0.000 |
INDEX1 | /database/oradb/index/index_1.dbf | ONLINE | READ WRITE | 300.0 M | 255.0 M | 15.00 % | 5,175 | 835 | 0.000 |
MONITORING | /database/oradb/data/monitoring_1.dbf | ONLINE | READ WRITE | 10.0 M | 6.4 M | 36.17 % | 1,167 | 867 | 0.000 |
SYSTEM | /database/oradb/data/system_1.dbf | SYSTEM | READ WRITE | 250.0 M | 76.3 M | 69.50 % | 2,305 | 1,135 | 0.000 |
TEMP | /database/oradb/temp/temp_1.dbf | ONLINE | READ WRITE | 300.0 M | 276.6 M | 7.79 % | 3,111 | 2,528 | 0.000 |
TOOLS | /database/oradb/data/tools_1.dbf | ONLINE | READ WRITE | 500.0 M | 201.2 M | 59.76 % | 6,959 | 17 | 0.000 |
UNDO_TS | /database/oradb/rbs/undo_ts01.dbf | ONLINE | READ WRITE | 350.0 M | 341.5 M | 2.43 % | 41 | 4,954 | 0.000 |
Pool Sizes | |
---|---|
Pool | Space |
Shared_Pool_Size | 48.00 M |
Shared_Pool_Reserved_Size | 2.38 M |
Large_Pool_Size | 0.00 K |
Java_Pool_Size | 32.00 M |
Sort_Area_Size | 0.06 M |
Sort_Area_Retained_Size | 0.00 K |
Pool | Free Space |
shared pool | 5.7 M |
java pool | 32.0 M |
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 | 146,524 | 2,546,594 | 3,016 | 0.12 | 98.17% |
TABLE/PROCEDURE | 77,420 | 1,216,667 | 311 | 0.03 | 98.21% |
BODY | 5,072 | 5,082 | 8 | 0.16 | 98.88% |
TRIGGER | 203 | 235 | 16 | 6.81 | 89.66% |
INDEX | 886 | 953 | 0 | 0.00 | 84.09% |
CLUSTER | 2,776 | 1,500 | 0 | 0.00 | 99.60% |
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 | 1,835 | 182 | 9.92% | ||
dc_used_extents | 180 | 180 | 100.00% | ||
dc_segments | 23,857 | 653 | 2.74% | ||
dc_tablespaces | 691,921 | 8 | 0.00% | ||
dc_tablespace_quotas | 124 | 1 | 0.81% | ||
dc_files | 5,480 | 3 | 0.05% | ||
dc_users | 741,970 | 34 | 0.00% | ||
dc_rollback_segments | 24,673 | 19 | 0.08% | ||
dc_objects | 25,592 | 1,217 | 4.76% | ||
dc_global_oids | 35 | 10 | 28.57% | ||
dc_object_ids | 63,597 | 1,064 | 1.67% | ||
dc_sequences | 185 | 24 | 12.97% | ||
dc_usernames | 15,225 | 19 | 0.12% | ||
dc_database_links | 212 | 2 | 0.94% | ||
dc_histogram_defs | 32,594 | 2,620 | 8.04% | ||
dc_profiles | 2,630 | 2 | 0.08% | ||
dc_user_grants | 18,238 | 32 | 0.18% |
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 | 628,008 | 10,648,657 | 663,299 | 0.06 |
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 | 1,985 | 638.6% | 4,638,174 |
DEFAULT | 32 M | 3,970 | 420.9% | 3,271,005 |
DEFAULT | 48 M | 5,955 | 136.6% | 1,485,823 |
DEFAULT | 64 M | 7,940 | 63.9% | 1,029,463 |
DEFAULT | 80 M | 9,925 | 17.8% | 739,869 |
DEFAULT | 96 M | 11,910 | 12.3% | 705,342 |
DEFAULT | 112 M | 13,895 | 6.0% | 665,366 |
DEFAULT | 128 M | 15,880 | 1.7% | 638,550 |
DEFAULT | 144 M | 17,865 | 0.7% | 632,678 |
DEFAULT | 160 M | 19,850 | 0.0% | 628,008 |
DEFAULT | 176 M | 21,835 | -0.3% | 626,381 |
DEFAULT | 192 M | 23,820 | -0.8% | 622,701 |
DEFAULT | 208 M | 25,805 | -1.3% | 619,659 |
DEFAULT | 224 M | 27,790 | -2.0% | 615,485 |
DEFAULT | 240 M | 29,775 | -3.3% | 607,419 |
DEFAULT | 256 M | 31,760 | -8.5% | 574,377 |
DEFAULT | 272 M | 33,745 | -15.0% | 533,906 |
DEFAULT | 288 M | 35,730 | -21.2% | 494,850 |
DEFAULT | 304 M | 37,715 | -35.2% | 406,691 |
DEFAULT | 320 M | 39,700 | -54.0% | 288,816 |
Shared Pool Advice | ||||||
---|---|---|---|---|---|---|
The following values are an estimation how changes to the shared pool size would affect the Library Cache (LC). | ||||||
Size | SizeFactor | LC Size | Objects | LC Time Saved | TimeFactor | Object Hits |
32.0 M | -33.3% | 34.0 M | 3,859 | 1:32:49 | -0.3% | 370,018 |
48.0 M | 0.0% | 49.0 M | 5,931 | 1:32:55 | 0.0% | 371,407 |
64.0 M | 33.3% | 65.0 M | 7,671 | 1:32:16 | 1.1% | 373,378 |
80.0 M | 66.7% | 80.0 M | 9,513 | 1:32:22 | 1.4% | 374,475 |
96.0 M | 100.0% | 97.0 M | 11,528 | 1:32:22 | 1.4% | 374,626 |
PGA Target Advice | |||||
---|---|---|---|---|---|
The following values are an estimation how changes to the PGA size would affect the performance. A nonzero value for OverAlloc means that Size for PGA_TARGET_FOR_ESTIMATE is not large enough to run the work area workload. | |||||
Size | SizeFactor | BytesProcessed | XtraBytesRW | CacheHits | OverAlloc |
12.0 M | -75.0% | 1.7 G | 554.9 M | 76.0% | 46 |
24.0 M | -50.0% | 1.7 G | 381.1 M | 82.0% | 1 |
36.0 M | -25.0% | 1.7 G | 197.8 M | 90.0% | 0 |
48.0 M | 0.0% | 1.7 G | 81.1 M | 96.0% | 0 |
57.6 M | 20.0% | 1.7 G | 79.7 M | 96.0% | 0 |
67.2 M | 40.0% | 1.7 G | 79.7 M | 96.0% | 0 |
76.8 M | 60.0% | 1.7 G | 79.7 M | 96.0% | 0 |
86.4 M | 80.0% | 1.7 G | 79.7 M | 96.0% | 0 |
96.0 M | 100.0% | 1.7 G | 79.7 M | 96.0% | 0 |
144.0 M | 200.0% | 1.7 G | 79.7 M | 96.0% | 0 |
192.0 M | 300.0% | 1.7 G | 47.2 M | 97.0% | 0 |
288.0 M | 500.0% | 1.7 G | 0.0 K | 100.0% | 0 |
384.0 M | 700.0% | 1.7 G | 0.0 K | 100.0% | 0 |
PGA Workarea Usage | |
---|---|
Target is, if possible, to have
only optimal executions (i.e. process everything in memory) or at least
eliminate the multipass executions. Crosscheck with the PGA Target Advices above (especially to minimize OverAlloc). | |
Statistic | Value |
workarea executions - optimal | 79,042 |
workarea executions - onepass | 46 |
workarea executions - multipass | 0 |
SYSSTAT Info | ||
---|---|---|
Name | Value | Description |
Percent DiskSorts (of DiskSorts + MemSorts) | 0.01 | 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.00 | 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 | 31.00 | how often the log file was full and Oracle had to wait for a new file to become available |
table fetch continued row | 896.00 | How many migrated rows did we encounter during this instances life time? |
Chained-Fetch-Ratio | 0.03% | Percentage of fetched "continued rows" per "rowid" fetch. If this value is very low, do not care about the previous value ;) |
Selected Wait Events (from v$system_event) | |||||
---|---|---|---|---|---|
Name | Totals | Total WaitTime | Avg Waited | Timeouts | Description |
free buffer waits | 0 | 0 | 0.000 | 0 | 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 | 792 | 1:39 | 0.049 | 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 | 24,040 | 1:18:37 | 0.194 | 0 | Indicator for I/O problems on index accesses (Consider increasing the buffer cache when value is high) |
db file scattered read | 100,980 | 1:03:10 | 0.038 | 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 table stats above (if enabled)) |
undo segment extension | 0 | 0 | 0.000 | 0 | High wait times here could indicate a problem with the extent size, the value of MINEXTENTS, or possibly IO related problems. |
enqueue | 40 | 10:15 | 15.375 | 2 | This type of event may be an indication that something is either wrong with the code or possibly the physical design. |
latch free | 52 | 25 | 0.481 | 36 | 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 | 234 | 1 | 0.004 | 0 | 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.000 | 0 | Higher values for one of these events indicate that either your ReDo logs are too small or there are not enough log file groups |
log file switch (archiving needed) | 0 | 0 | 0.000 | 0 | |
log file switch completion | 31 | 2:37 | 3.129 | 0 | You may consider increasing the number of logfile groups. |
log buffer wait | 0 | 0 | 0.000 | 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 | 9 | 4:01 | 27.778 | 1 | 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 | 114,047 | 3:43:24 | 0.086 | 113,984 | 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 | 76 | 12 | 0.158 | 0 | |
SQL*Net message to client | 1,942,122 | 5:14 | 0.000 | 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 | 105 | 0 | 0.000 | 0 |
Buffer Wait Ratios | |||
---|---|---|---|
Class/Name | Count | Time | Ratio |
Buffer Busy Waits | |||
data block | 692 | 29 | 2,386.21 |
undo header | 93 | 0 | 0.00 |
segment header | 4 | 1 | 400.00 |
undo block | 3 | 0 | 0.00 |
FreeList Contention | |||||||
---|---|---|---|---|---|---|---|
Owner | Table | AvgRowLen | PctUsed | PctFree | FreeLists | AvgFreeSpace | BlockSize |
No affected tables found. |
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 |
ONION.INF | TABLE | DATA | physical reads | 374,577 |
ONION.PWA | TABLE | DATA | physical reads | 152,197 |
ONION.SYS_LOB0000012058C00002$$ | LOB | DATA | physical reads | 70,860 |
ONION.SYS_LOB0000012058C00002$$ | LOB | DATA | physical reads direct | 70,856 |
ONION.BEA | TABLE | DATA | physical reads | 12,761 |
ONION.BED | TABLE | DATA | physical reads | 10,011 |
ONION.INF_BACKUP | TABLE | DATA | physical reads | 9,755 |
PERFSTAT.STATS$SQL_SUMMARY | TABLE | TOOLS | physical reads | 9,650 |
PERFSTAT.STATS$LATCH | TABLE | TOOLS | physical reads | 7,222 |
PERFSTAT.STATS$SYSSTAT | TABLE | TOOLS | physical reads | 6,222 |
Top 10 BufferBusy Objects
| ||||
---|---|---|---|---|
Object | Type | TableSpace | Wait Type | Waits |
ONION.PRO | TABLE | DATA | buffer busy waits | 635 |
ONION.SLG | TABLE | DATA | buffer busy waits | 6 |
ONION.AUF | TABLE | DATA | buffer busy waits | 3 |
ONION.PWA | TABLE | DATA | buffer busy waits | 3 |
ONION.XPKPRO_H | INDEX | INDEX1 | buffer busy waits | 3 |
ONION.XPKPWA | INDEX | INDEX1 | buffer busy waits | 3 |
ONION.XPKSLG | INDEX | INDEX1 | buffer busy waits | 2 |
ONION.PRO_H | TABLE | DATA | buffer busy waits | 2 |
ONION.XPKSTS | INDEX | INDEX1 | buffer busy waits | 1 |
ONION.XPKSKW | INDEX | INDEX1 | buffer busy waits | 1 |
Top 10 RowLock Objects
| ||||
---|---|---|---|---|
Object | Type | TableSpace | Wait Type | Waits |
ONION.XPKZTL | INDEX | INDEX1 | row lock waits | 6 |
ONION.XPKAUF | INDEX | INDEX1 | row lock waits | 3 |
ONION.XPKPRO_H | INDEX | INDEX1 | row lock waits | 2 |
ONION.XPKPRO | INDEX | INDEX1 | row lock waits | 2 |
ONION.XPKBAB | INDEX | INDEX1 | row lock waits | 1 |
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 | PctWaits | Succ.Requests | Failed Req. | PctFailed | Cum.WaitTime |
TM | 895,594 | 33 | 0.00 | 895,593 | 0 | 0.00 | 3.361 |
TX | 111,897 | 2 | 0.00 | 111,897 | 0 | 0.00 | 3.328 |
SQ | 188 | 3 | 1.60 | 188 | 0 | 0.00 | 0.007 |
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 | Type | Created | Last DDL |
SYS | DBMS_PCLXUTIL | PACKAGE BODY | 15.05.2003 05:30 | 29.10.2004 02:32 |
SYS | DBMS_PRVTAQIP | PACKAGE BODY | 15.05.2003 05:31 | 29.10.2004 02:32 |
SYS | DBMS_SNAPSHOT | PACKAGE BODY | 15.05.2003 05:31 | 29.10.2004 02:32 |
SYS | UTL_RECOMP | PACKAGE BODY | 06.07.2004 03:30 | 03.11.2004 02:03 |