Report for oradb


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 / Uptime18.10.2005 13:58 / 2.93 d
Report generated:21.10.2005 12:20


Table Statistics
StatisticValueComment
table scan blocks gotten / table scan rows gotten 0.03This ratio (blocks scanned per scanned row) should get close to 0 with acceptable table scan blocks gotten if you are not using LONG objects frequently. If it is high, you probably should:
  • Check missing index(es) on statements doing Full Table Scan(FTS).
  • If Statements have to do FTS, reorganize tables used in FTS to reset High Water Mark(HWM). Because, in FTS, Oracle scans table from bottom of table to top of table(HWM) even if there are just a few rows in table. This makes problems on frequently inserted/deleted tables since INSERT increases HWM, but DELETE does not decrease HWM. After reorganization, the number of scanned blocks will be reduced.
table fetch continued row / table fetch by rowid 0.00The chained-fetch-ratio indicates the average chained/migrated rows in multiple blocks, which are accessed by a single ROWID. This ratio should be as low as possible to access a row in a single block. If it is high,
  • find the chained/migrated rows
  • increase PCTFREE, decrease PCTUSED storage parameters of tables which have many chained/migrated rows frequently used
  • reorganize these tables (e.g. ALTER TABLE..MOVE, or EXP / IMP)
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) 1,176,572 
table scans (long tables) 486 
table scans (rowid ranges) 0 
table scans (cache partitions) 0 
table scans (direct read) 0 


Data Files
Alerts and warnings (highlighted table cells) indicate a write enabled non-autoextensible data file filled > 85% / 95%
TablespaceDatafileStatus EnabledSizeFreeUsedPhy.ReadsPhy.Writes Avg.I/O Time
DATA/database/oradb/data/data_1.dbfONLINEREAD WRITE 500.0 M 143.3 M 71.35 % 127,357 7360.000
INDEX1/database/oradb/index/index_1.dbfONLINEREAD WRITE 300.0 M 255.0 M 15.00 % 5,175 8350.000
MONITORING/database/oradb/data/monitoring_1.dbfONLINEREAD WRITE 10.0 M 6.4 M 36.17 % 1,167 8670.000
SYSTEM/database/oradb/data/system_1.dbfSYSTEMREAD WRITE 250.0 M 76.3 M 69.50 % 2,305 1,1350.000
TEMP/database/oradb/temp/temp_1.dbfONLINEREAD WRITE 300.0 M 276.6 M 7.79 % 3,111 2,5280.000
TOOLS/database/oradb/data/tools_1.dbfONLINEREAD WRITE 500.0 M 201.2 M 59.76 % 6,959 170.000
UNDO_TS/database/oradb/rbs/undo_ts01.dbfONLINEREAD WRITE 350.0 M 341.5 M 2.43 % 41 4,9540.000


Database Writer Statistics Help
StatisticValueComment
DBWR checkpoint buffers written / hour 1.46Number of buffers written per hour for checkpoints
DBWR transaction table writes / hour 0.03Number of rollback segment headers written per hour by DBWR. This statistic indicates how many "hot" buffers were written, causing a user process to wait while the write completed.
DBWR undo block writes / hour 1.13Number of rollback segment blocks written per hour by DBWR
DBWR revisited being-written buffer / hour 0.00Number of times per hour that DBWR tried to save a buffer for writing and found that it was already in the write batch. This statistic measures the amount of "useless" work that DBWR had to do in trying to fill the batch. (If the same buffer from different sources is considered for adding to the write batch, then all but the first attempt will be "useless" because the buffer is already marked as being written.)
DBWR buffers scanned / DBWR lru scans 0.00Average number of buffers scanned per scan
DBWR summed scan depth / DBWR lru scans 0.00Average scan depth
free buffer inspected / free buffer requested 0.00If this value is to high, you may need to increase your buffer cache.
dirty buffers inspected / DBWR buffers scanned 0.00This ratio should be as low as possible. If this value is high, it indicates DBWR is not writing dirty buffers efficiently. High ratio may cause write complete wait wait event.
pinned buffers inspected / DBWR buffers scanned 0.00This ratio should be as low as possible. If this value is high, it indicates high amount of pinned(busy) buffers encountered during free buffer search. This may cause free buffer waits wait event by reducing the possibility of finding free buffer in the LRU list, and then may cause DBWR to perform large batch write to make clean buffers available at the tail of LRU list. This may also increase physical IO if aged out blocks are needed to be re-accessed.
A possible solution is to increase the DB_BLOCK_BUFFERS (Oracle 8) / DB_CACHE_SIZE (Oracle 9+).
DBWR free buffers found / DBWR make free requests 0.00This ratio shows the average reusable buffers, and should be as high as possible. If it is low, it indicates lack of free space to use. In this case, check the followings:
  • If dirty buffers inspected is high, it indicates DBWR is not writing dirty buffers efficiently.
  • If pinned buffers inspected is high, increase DB_BLOCK_BUFFERS / DB_CACHE_SIZE.


Log Writer Statistics
StatisticValueComment
redo log space requests 31The active logfile had been full, and Oracle had waited for disk space to be allocated for the redolog entries by performing log switches. High values indicate high amount of log switches.
redo log space requests / h 0.44This number should be as low as possible. When encountering high values here, the reason could be e.g. one of these:
  • too small log files
  • not enough redo log groups
  • too many checkpoints / log file switches (also indicated by high redo wastage, see below)
redo log space wait time 98Total wait time waiting for completion of redo log space requests in 1/10 ms. High values cause log file switch... related wait events. If there are many processes waiting for log switch completion, it is possible to see log buffer space wait event after log switch is completed. Since redo generation is disabled during log switch, there can be high volume of redo generation after log switch. This may cause log buffer space wait event.
redo log space wait time / redo log space requests 0.32ms/request. If this ratio is high, check the followings:
  • Increase the size of redolog files and/or add new redolog groups
  • Ensure that log switches occurring not more frequent than around all 20-30 minutes
redo wastage percentage 27.57%Percentage of redo bytes written "unnecessarily" (redo wastage describes the log buffer blocks had been needed to be flushed out to disk before they were completely full, which does not mean a problem: high values just indicate high LGWR activity). Naturally, this should be very low; if it exceeds 20..30% plus you have many log writer wait events, you should check for unnecessary checkpoints/log switches.
redo synch writes 108,794Changes to redolog buffer had been flushed out to disk immediately such as COMMIT / ROLLBACK.
redo synch time 1,001,282Elapsed time of all redo synch writes in 1/10 ms. High values cause log file sync wait event.
redo synch time / redo synch writes 920.35Milliseconds per write. If this ratio is high, check the followings:
  • Do not set LOG_BUFFER to value higher than 1Mb. High LOG_BUFFER parameter may cause log file sync wait event. This impacts COMMIT / ROLLBACK response time, and possibly DBWR performance.
  • Reduce COMMIT / ROLLBACK frequency.
  • If there are other redolog related wait events, check them. They may indirectly cause log file sync wait event.
redo buffer allocation retries / h 0.53Number of retries per hour necessary to allocate space in the redo buffer. Retries are needed either because the redo writer has fallen behind or because an event such as a log switch is occurring.
redo buffer allocation retries / redo blocks written 0.00should 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 blocks written / redo writes 3.07Number of blocks per write
redo emergence 29.1 K/minHow much redo information is written on average during the snapshot interval given.
redo time used 0.03%Rate of time spent for writing redo information during the snapshot interval given. This value should be close to 0%.
redo size 120.1 MTotal amount of redo generated

Related Information
ParameterValue
log_checkpoint_interval 0
log_checkpoint_timeout 0
log_checkpoint_mttr_target 
log_buffer 512.0 K
LogGroupMembersSize
12 10.0 M
22 10.0 M
32 10.0 M
WaitEventWaitsWaitTimeAvgWaitTimeTimeouts
LGWR wait for redo copy 23410.004 0
log file switch (checkpoint incomplete) 000.000 0
log file switch (archiving needed) 000.000 0
log file switch completion 312:373.129 0
log file parallel write 114,0473:43:240.086 113,984
log file single write 76120.158 0
log buffer wait 000.000 0
log buffer space 94:0127.778 1


Pool Sizes
PoolSpace
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
PoolFree 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:
  • RPP (100*reloads/pins) > 1
  • gethitratio < 90%
NameSpaceGets PinsReloadsRPPGetHitRatio
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:
ParameterGetsGetMissesRatio
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:
Poolphysical_readsconsistent_getsdb_block_getsRatio
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.
PoolSizeBuffersEstd.PhyRd FactorEstd.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).
SizeSizeFactorLC SizeObjectsLC Time Saved TimeFactorObject 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.
SizeSizeFactorBytesProcessedXtraBytesRW CacheHitsOverAlloc
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).
StatisticValue
workarea executions - optimal 79,042
workarea executions - onepass 46
workarea executions - multipass 0


SYSSTAT Info
NameValueDescription
Percent DiskSorts (of DiskSorts + MemSorts) 0.01Should 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.00Increase your buffer cache if this value is too high
redo buffer allocation retries / redo blocks written 0.00should 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.00how often the log file was full and Oracle had to wait for a new file to become available
table fetch continued row 896.00How 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)
NameTotals Total WaitTimeAvg WaitedTimeoutsDescription
free buffer waits
000.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
7921:390.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,0401:18:370.194 0 Indicator for I/O problems on index accesses
(Consider increasing the buffer cache when value is high)
db file scattered read 100,9801:03:100.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 000.000 0 High wait times here could indicate a problem with the extent size, the value of MINEXTENTS, or possibly IO related problems.
enqueue 4010:1515.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 52250.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 23410.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) 000.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) 000.000 0
log file switch completion 312:373.129 0 You may consider increasing the number of logfile groups.
log buffer wait 000.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 94:0127.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,0473:43:240.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 76120.158 0
SQL*Net message to client 1,942,1225:140.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 10500.000 0


Buffer Wait Ratios
Class/NameCount TimeRatio
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 Help
OwnerTableAvgRowLen PctUsedPctFreeFreeListsAvgFreeSpaceBlockSize
No affected tables found.


Objects causing Wait Events Help
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 Help
ObjectTypeTableSpaceWait TypeWaits
ONION.INFTABLEDATAphysical reads 374,577
ONION.PWATABLEDATAphysical reads 152,197
ONION.SYS_LOB0000012058C00002$$LOBDATAphysical reads 70,860
ONION.SYS_LOB0000012058C00002$$LOBDATAphysical reads direct 70,856
ONION.BEATABLEDATAphysical reads 12,761
ONION.BEDTABLEDATAphysical reads 10,011
ONION.INF_BACKUPTABLEDATAphysical reads 9,755
PERFSTAT.STATS$SQL_SUMMARYTABLETOOLSphysical reads 9,650
PERFSTAT.STATS$LATCHTABLETOOLSphysical reads 7,222
PERFSTAT.STATS$SYSSTATTABLETOOLSphysical reads 6,222

Top 10 BufferBusy Objects Help
ObjectTypeTableSpaceWait TypeWaits
ONION.PROTABLEDATAbuffer busy waits 635
ONION.SLGTABLEDATAbuffer busy waits 6
ONION.AUFTABLEDATAbuffer busy waits 3
ONION.PWATABLEDATAbuffer busy waits 3
ONION.XPKPRO_HINDEXINDEX1buffer busy waits 3
ONION.XPKPWAINDEXINDEX1buffer busy waits 3
ONION.XPKSLGINDEXINDEX1buffer busy waits 2
ONION.PRO_HTABLEDATAbuffer busy waits 2
ONION.XPKSTSINDEXINDEX1buffer busy waits 1
ONION.XPKSKWINDEXINDEX1buffer busy waits 1

Top 10 RowLock Objects Help
ObjectTypeTableSpaceWait TypeWaits
ONION.XPKZTLINDEXINDEX1row lock waits 6
ONION.XPKAUFINDEXINDEX1row lock waits 3
ONION.XPKPRO_HINDEXINDEX1row lock waits 2
ONION.XPKPROINDEXINDEX1row lock waits 2
ONION.XPKBABINDEXINDEX1row lock waits 1


Enqueue Waits Help
The following queues caused waits during the recent uptime of this instance.
Ordered by cumulative wait time (desc)
QueueTotal RequestsTotal WaitsPctWaits Succ.RequestsFailed Req.PctFailedCum.WaitTime
TM 895,594 33 0.00 895,593 0 0.003.361
TX 111,897 2 0.00 111,897 0 0.003.328
SQ 188 3 1.60 188 0 0.000.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:
OwnerObjectTypeCreatedLast DDL
SYSDBMS_PCLXUTILPACKAGE BODY15.05.2003 05:3029.10.2004 02:32
SYSDBMS_PRVTAQIPPACKAGE BODY15.05.2003 05:3129.10.2004 02:32
SYSDBMS_SNAPSHOTPACKAGE BODY15.05.2003 05:3129.10.2004 02:32
SYSUTL_RECOMPPACKAGE BODY06.07.2004 03:3003.11.2004 02:03


w3c Created by OraRep v0.3.3 © 2003-2005 by Itzchak Rehberg & IzzySoft IzzySoft