Report for ORADB

[ Users ] [ Datafiles ] [ Rollback ] [ Memory ] [ Pool Sizes ] [ Shared Pool ] [ Buffer Pool ] [ 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) 7,650.00 MB
Startup / Uptime28.11.2003 16:43 / 4.98 d
Report generated:03.12.2003 16:16

User Information
UsernameAccount StatusLock Date Expiry DateDefault TSTemporary TSCreated
SYSOPEN--SYSTEMTEMP28.10.2003
SYSTEMOPEN--SYSTEMTEMP28.10.2003
OUTLNOPEN--SYSTEMTEMP28.10.2003
USER00OPEN--DATA_DDBTEMP19.11.2003
INLNOPEN--SYSTEMTEMP19.11.2003
USER01OPEN--DATA00TEMP01.12.2003
DBSNMPOPEN--SYSTEMTEMP28.10.2003
WMSEXPIRED & LOCKED28.10.2003-SYSTEMTEMP28.10.2003

Admins
UserAdmin Option
SYSYES
SYSTEMYES

Data Files
TablespaceDatafileStatus EnabledSize (kB)Free (kB)Used (%)Phys.ReadsPhys.Writes Avg.I/O Time
DATA00/opt/oracle/oraData/ORADB/v1/data00.dbfONLINEREAD WRITE 3,491,840.00 18,368.00 99.47 27,801,451 815,537 51,099,649
INDX00/opt/oracle/oraData/ORADB/v1/indx00.dbfONLINEREAD WRITE 1,320,960.00 1,984.00 99.85 385,809 1,229,638 -106,741,881
SYSTEM/opt/oracle/oraData/ORADB/v1/system.dbfSYSTEMREAD WRITE 153,600.00 11,472.00 92.53 11,972 1,496 0
TEMP/opt/oracle/oraData/ORADB/v1/temp.dbfONLINEREAD WRITE 122,880.00 11,472.00 90.66 11,972 1,496 0
UNDOTBS/opt/oracle/oraData/ORADB/v1/undotbs.dbfONLINEREAD WRITE 307,200.00 115,392.00 62.44 1,714 2,064,806 398,483,758

Rollback Segments
SegmentStatusSize (kB)OptSize (kB) HWMSize (kB)WaitsXActsShrinksWrapsAveShrink AveActive
SYSTEMONLINE 392.00 .00 392.00 0 0 0 0 0 0
_SYSSMU1$ONLINE 14,456.00 .00 113,848.00 449 0 155 1,717 8,327,300 10,636,063
_SYSSMU10$ONLINE 14,456.00 .00 90,232.00 389 0 97 1,235 7,826,484 10,321,046
_SYSSMU2$ONLINE 15,480.00 .00 82,040.00 795 0 213 2,274 7,487,103 7,741,925
_SYSSMU3$ONLINE 15,480.00 .00 106,616.00 654 0 195 2,085 7,694,934 11,158,285
_SYSSMU4$ONLINE 28,792.00 .00 106,616.00 332 0 131 1,465 7,948,366 9,390,782
_SYSSMU5$ONLINE 28,792.00 .00 82,040.00 326 0 90 1,147 7,712,859 9,749,225
_SYSSMU6$ONLINE 14,456.00 .00 106,616.00 545 0 146 1,647 8,359,879 10,264,377
_SYSSMU7$ONLINE 28,792.00 .00 82,040.00 931 0 232 2,429 7,330,992 9,360,595
_SYSSMU8$ONLINE 15,480.00 .00 114,808.00 627 0 169 1,896 8,103,196 11,156,637
_SYSSMU9$ONLINE 15,480.00 .00 98,424.00 374 0 129 1,478 8,144,753 11,085,042

Memory Values
NameSize
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_keep0 kB
buffer_pool_recycle0 kB
global_context_pool_size0 kB
olap_page_pool_size 32,768.00 kB

Pool Sizes
PoolSpace
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
PoolFree Space
shared pool 104,492.59 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:
  • RPP (100*reloads/pins) > 1

  • gethitratio < 90%
    • NameSpaceGets PinsReloadsRPPGetHitRatio (%)
    SQL AREA 750,963 77,264,148 15 0.00 99.88
    TABLE/PROCEDURE 18,021 25,031,497 424 0.00 90.81
    BODY 194 194 0 0.00 86.08
    TRIGGER 2,705 2,705 0 0.00 99.82
    INDEX 2,171 2,136 0 0.00 1.84
    CLUSTER 770 842 0 0.00 98.70
    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 819 3 0.37
    dc_segments 24,817 2,672 10.77
    dc_tablespaces 51,736 10 0.02
    dc_tablespace_quotas 2,738 25 0.91
    dc_files 979 10 1.02
    dc_users 66,667 23 0.03
    dc_rollback_segments 99,738 11 0.01
    dc_objects 14,309 3,714 25.96
    dc_global_oids 4 2 50.00
    dc_constraints 7,596 7,589 99.91
    dc_object_ids 13,284 3,494 26.30
    dc_sequences 625,625 334 0.05
    dc_usernames 8,788 10 0.11
    dc_histogram_defs 7,950 7,489 94.20
    dc_table_scns 392 392 100.00
    dc_profiles 1,474 1 0.07
    dc_user_grants 51,471 21 0.04

    Buffer Pool Statistics
    Ratio = physical_reads/(consistent_gets+db_block_gets) should be < 0.9:
    Poolphysical_readsconsistent_getsdb_block_getsRatio
    DEFAULT 190,474,329 247,899,270 550,392,125 0.24

    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 2,002 1.5% 193,371,260
    DEFAULT 32 M 4,004 0.9% 192,129,618
    DEFAULT 48 M 6,006 0.7% 191,791,601
    DEFAULT 64 M 8,008 0.6% 191,604,735
    DEFAULT 80 M 10,010 0.5% 191,474,420
    DEFAULT 96 M 12,012 0.5% 191,367,048
    DEFAULT 112 M 14,014 0.4% 191,259,569
    DEFAULT 128 M 16,016 0.4% 191,153,160
    DEFAULT 144 M 18,018 0.3% 191,046,598
    DEFAULT 160 M 20,020 0.2% 190,926,898
    DEFAULT 176 M 22,022 0.1% 190,740,216
    DEFAULT 192 M 24,024 0.1% 190,646,058
    DEFAULT 208 M 26,026 0.0% 190,474,330
    DEFAULT 224 M 28,028 -0.1% 190,372,564
    DEFAULT 240 M 30,030 -0.2% 190,178,046
    DEFAULT 256 M 32,032 -0.2% 190,024,969
    DEFAULT 272 M 34,034 -0.3% 189,865,737
    DEFAULT 288 M 36,036 -0.4% 189,707,605
    DEFAULT 304 M 38,038 -0.5% 189,472,178
    DEFAULT 320 M 40,040 -1.7% 187,233,471

    PGA Target Advice
    The following values are an estimation how changes to the PGA size would affect the performance.
    SizeSizeFactorBytesProcessedXtraBytesRW CacheHitsOverAlloc
    10,485,760 -50.0% 1,222,709,248 1,786,818,560 41.0% 19
    15,728,640 -25.0% 1,222,709,248 1,786,818,560 41.0% 19
    20,971,520 0.0% 1,222,709,248 1,528,704,000 44.0% 1
    25,165,824 20.0% 1,222,709,248 1,528,704,000 44.0% 0
    29,360,128 40.0% 1,222,709,248 1,528,704,000 44.0% 0
    33,554,432 60.0% 1,222,709,248 1,528,704,000 44.0% 0
    37,748,736 80.0% 1,222,709,248 1,528,704,000 44.0% 0
    41,943,040 100.0% 1,222,709,248 1,528,704,000 44.0% 0
    62,914,560 200.0% 1,222,709,248 829,679,616 60.0% 0
    83,886,080 300.0% 1,222,709,248 440,202,240 74.0% 0
    125,829,120 500.0% 1,222,709,248 440,202,240 74.0% 0
    167,772,160 700.0% 1,222,709,248 440,202,240 74.0% 0

    SYSSTAT Info
    NameValueDescription
    Percent DiskSorts (of DiskSorts + MemSorts) 0.00Should 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.01Increase 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 169.00how often the log file was full and Oracle had to wait for a new file to become available
    table fetch continued row 115.00How many migrated rows did we encounter during this instances life time? Since these can cause acute performance degration, they should be corrected immediately if they are being reported. For this, you may have to analyse your tables: 
    ANALYZE TABLE tablename COMPUTE STATISTICS;
SELECT num_rows,chain_cnt FROM dba_tables WHERE table_name='tablename';
    utlchain.sql then may help you to automatically eliminate migration (correct PCTFREE before running that!).

    Selected Wait Events (from v$system_event)
    NameTotals Total WaitTime (s)Avg Waited (ms)TimeoutsDescription
    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. This could be an indication that either your database is having an IO problem (check the other IO related wait events to validate this) or your database is very busy and you simply don't have enough block buffers to go around. A possible solution is to adjust the frequency of your checkpoints by tuning the CHECK_POINT_TIMEOUT and CHECK_POINT_INTERVAL parameters to help the DBWR process to keep up. Increasing the buffer cache may also be helpful.
    buffer busy waits
    		 6,457 145 22.5 0 Indicates 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 4,343,714 419,025 96.5 0 Indicator for I/O problems on index accesses
    (Consider increasing the buffer cache when value is high)
    db file scattered read 23,873,007 3,674,844 153.9 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 1,666,206 694 0.4 1,666,175 Whenever the database must extend or shrink a rollback segment, this wait event occurs while the rollback segment is being manipulated. High wait times here could indicate a problem with the extent size, the value of MINEXTENTS, or possibly IO related problems.
    enqueue 26 124 4,769.2 0 This type of event may be an indication that something is either wrong with the code (should multiple sessions be serializing themselves against a common row?) or possibly the physical design (high activity on child tables with unindexed foreign keys, inadequate INITRANS or MAXTRANS values, etc. Since this event also indicates that there are too many DML or DDL locks (or, maybe, a large number of sequences), increasing the ENQUEUE_RESOURCES parameter in the init.ora will help reduce these waits.
    latch free 406 339 835.0 218 This event occurs whenever one Oracle process is requesting a "willing to wait" latch from another process. The event only occurs if the spin_count has been exhausted, and the waiting process goes to sleep. Latch free waits can occur for a variety of reasons including library cache issues, OS process intervention (processes being put to sleep by the OS, etc.), and so on.
    LGWR wait for redo copy 520 1 1.9 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.00 Higher values indicate that either your ReDo logs are too small or there are not enough log file groups
    log file switch completion 140 619 4,421.4 0 You may consider increasing the number of logfile groups.
    log buffer wait000.00 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 space000.00 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 78,750 2,838 36.0 78,664 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 144 34 236.1 0
    SQL*Net message to client 50,029,617 10,735 0.2 0 These wait events occur when the Databaseunexpectedly looses Net8 connectivity with a remote client or Database. Frequent occurences of these events could indicate a networking issue.
    SQL*Net message to dblink000.00

    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. If you had many db file scattered reads above and now find some entries with segment type = table in here, these may need some|more|better|other indices. Use Statspack or Oracle Enterprise Manager Diagnostics Pack to find out more. Other things that may help to avoid some of the db file * read wait events are:
    • Tune the SQL statements used by your applications and users (most important!)
    • Re-Analyze the schema to help the optimizer with accurate data e.g. with dbms_stats
    • Stripe objects over multiple disk volumes
    • Pin frequently used objects
    • Increase the buffer caches

    Top 10 IO Objects
    ObjectTypeTableSpaceWait TypeWaits
    USER01.MAIN_IDX1INDEXINDX00physical writes 504,745
    USER01.MAINVALUETABLEDATA00physical reads 442,215
    USER01.MAINVALUETABLEDATA00physical writes 428,406
    USER01.MAIN_IDX1INDEXINDX00physical reads 155,051
    USER01.MAIN_IDX1INDEXINDX00physical writes direct 114,439
    USER01.MAINVALUETABLEDATA00physical writes direct 102,897
    USER01.MAIN_SYSDAT_IDXINDEXINDX00physical writes 39,389
    USER01.MAIN_PKINDEXINDX00physical writes 26,024
    USER01.MAIN_YDAY_IDXINDEXINDX00physical writes 23,110
    USER01.MAIN_SYSDAT_IDXINDEXINDX00physical writes direct 7,161

    Top 10 BufferBusy Objects
    ObjectTypeTableSpaceWait TypeWaits
    USER01.MAIN_YDAY_IDXINDEXINDX00buffer busy waits 1

    Top 10 RowLock Objects
    ObjectTypeTableSpaceWait TypeWaits
    USER01.MAIN_SYSDAT_IDXINDEXINDX00row lock waits 114
    USER01.MAIN_YDAY_IDXINDEXINDX00row lock waits 92
    USER01.MAIN_IDX1INDEXINDX00row lock waits 74
    USER01.MAIN_PKINDEXINDX00row lock waits 72

    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:
    OwnerObjectTypCreatedLast DDL
    SYSALL_REPGROUPED_COLUMNVIEW28.10.2003 04:5028.10.2003 04:50
    SYSALL_REPPARAMETER_COLUMNVIEW28.10.2003 04:5128.10.2003 04:51
    SYSDBA_REPGROUPED_COLUMNVIEW28.10.2003 04:5028.10.2003 04:50
    SYSDBA_REPPARAMETER_COLUMNVIEW28.10.2003 04:5128.10.2003 04:51
    SYSDBMS_REPCAT_RGT_CUSTPACKAGE BODY28.10.2003 04:5228.10.2003 04:52
    SYSDBMS_REPCAT_UTL4PACKAGE BODY28.10.2003 04:5228.10.2003 04:52
    SYSUSER_REPGROUPED_COLUMNVIEW28.10.2003 04:5028.10.2003 04:50
    SYSUSER_REPPARAMETER_COLUMNVIEW28.10.2003 04:5128.10.2003 04:51
    SYS_ALL_REPCOLUMN_GROUPVIEW28.10.2003 04:5028.10.2003 04:50
    SYS_ALL_REPCONFLICTVIEW28.10.2003 04:5128.10.2003 04:51
    SYS_ALL_REPGROUPED_COLUMNVIEW28.10.2003 04:5028.10.2003 04:50
    SYS_ALL_REPPARAMETER_COLUMNVIEW28.10.2003 04:5128.10.2003 04:51
    SYS_ALL_REPRESOLUTIONVIEW28.10.2003 04:5028.10.2003 04:50

    Miscellaneous
    NameValue
    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) 12,508,372
    table scans (long tables) 929
    table scans (rowid ranges) 0
    table scans (cache partitions) 0
    table scans (direct read) 0

    Created by OraRep v0.1.7 © 2003 by Itzchak Rehberg & IzzySoft