Report for ORADB


Common Instance Information
Hostname:buzz
Instance:ORADB
Version:9.0.1.0.0
Archiver:STARTED
ArchivedLogSize: MB
FileSize (Data+Log) 909.00 MB
Startup / Uptime19.11.2003 10:41 / 28.26 d
Report generated:17.12.2003 16:52


User Information
UsernameAccount StatusLock Date Expiry DateDefault TSTemporary TSCreated
SYSOPEN--SYSTEMTEMP07.08.2002
SYSTEMOPEN--SYSTEMTEMP07.08.2002
OUTLNOPEN--SYSTEMTEMP07.08.2002
DBSNMPOPEN--SYSTEMTEMP07.08.2002
REPOPEN--ESPTEMP28.03.2003
CONOPEN--ESPTEMP28.03.2003
ORACLEOPEN--SYSTEMTEMP30.10.2002
DIRECTOPEN--ESPTEMP04.06.2003

Admins
UserAdmin Option
SYSYES
SYSTEMYES

DB Links
OwnerDB Link UsernameHostCreatedStatus
PUBLICORA1.INTRA.NETINLNORA1 16.08.2002ACTIVE
PUBLICDS00.INTRA.NETINLNDS00 28.11.2003ACTIVE
PUBLICETC.INTRA.NETINLNETC 03.03.2003ACTIVE
PUBLICPMST.INTRA.NETINLNPMST 30.10.2002ACTIVE
PUBLICWMP.INTRA.NETINLNWMP 30.09.2002ACTIVE
PUBLICXON.INTRA.NETINLNXON 14.08.2003ACTIVE
PUBLICXOP.INTRA.NETINLNxtro 02.01.2003ACTIVE


Data Files
TablespaceDatafileStatus EnabledSize (kB)Free (kB)Used (%)Phys.ReadsPhys.Writes Avg.I/O Time
ESP/opt/oracle/oraData/ORADB/vol1/esp.dbfONLINEREAD WRITE 55,296.00 960.00 98.26 3,364 2,526-1,453,701,380
SYSTEM/opt/oracle/oraData/ORADB/vol1/system01.dbfSYSTEMREAD WRITE 332,800.00 130,376.00 60.82 2,049 3,890-2,059,251,148
TEMP/opt/oracle/oraData/ORADB/vol1/temp01.dbfONLINEREAD WRITE 40,960.00 130,376.00 -218.30 2,049 3,890-2,059,251,148
UNDOTBS/opt/oracle/oraData/ORADB/vol1/undotbs01.dbfONLINEREAD WRITE 235,520.00 234,176.00 0.57 39 22,401 0


Rollback Segments
SegmentStatusSize (kB)OptSize (kB) HWMSize (kB)WaitsXActsShrinksWrapsAveShrink AveActive
SYSTEMONLINE 392.00 .00 392.00 0 0 0 0 0 0
_SYSSMU1$ONLINE 120.00 .00 2,232.00 5 0 4 24 753,664 199,589
_SYSSMU10$ONLINE 120.00 .00 2,040.00 0 1 3 20 655,360 63,386
_SYSSMU2$ONLINE 120.00 .00 2,040.00 0 0 4 29 573,440 109,182
_SYSSMU3$ONLINE 120.00 .00 2,040.00 0 0 3 21 655,360 80,740
_SYSSMU4$ONLINE 120.00 .00 16,568.00 7 0 6 41 3,123,882 5,841,180
_SYSSMU5$ONLINE 120.00 .00 2,168.00 0 0 4 25 753,664 209,331
_SYSSMU6$ONLINE 120.00 .00 2,232.00 4 0 4 24 753,664 215,602
_SYSSMU7$ONLINE 120.00 .00 2,040.00 0 0 3 21 655,360 63,504
_SYSSMU8$ONLINE 120.00 .00 2,168.00 0 0 4 25 753,664 194,576
_SYSSMU9$ONLINE 120.00 .00 2,040.00 0 0 3 20 655,360 70,818


Memory Values
NameSize
Fixed Size 273.05 kB
Variable Size 163,840.00 kB
Database Buffers 65,536.00 kB
Redo Buffers 520.00 kB
shared_pool_size 65,536.00 kB
shared_pool_reserved_size 3,276.80 kB
large_pool_size 1,024.00 kB
java_pool_size 65,536.00 kB
buffer_pool_keep0 kB
buffer_pool_recycle0 kB
global_context_pool_size0 kB


Pool Sizes
PoolSpace
Shared_Pool_Size 65,536.00 kB
Shared_Pool_Reserved_Size 3,276.80 kB
Large_Pool_Size 1,024.00 kB
Java_Pool_Size 65,536.00 kB
Sort_Area_Size 512.00 kB
Sort_Area_Retained_Size 0.00 kB
PoolFree Space
shared pool 48,002.04 kB
java pool 60,220.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 212,129 1,545,952 246 0.02 99.57
TABLE/PROCEDURE 34,005 69,840 367 0.53 94.64
BODY 1,275 1,287 3 0.23 98.90
TRIGGER 3,960 4,064 103 2.53 99.32
INDEX 32 32 0 0.00 0.00
CLUSTER 648 541 0 0.00 98.92
OBJECT 0 0 0 0.00 100.00
PIPE 0 0 0 0.00 100.00
JAVA SOURCE 5 5 0 0.00 80.00
JAVA RESOURCE 0 0 0 0.00 100.00
JAVA DATA 15 472 0 0.00 93.33
 
Row Cache
If Ratio = (getmisses/gets)*100 > 15, SHARED_POOL_SIZE may have to be increased:
ParameterGetsGetMissesRatio
dc_free_extents 63,539 10 0.02
dc_used_extents 1 1 100.00
dc_segments 6,395 137 2.14
dc_tablespaces 10,987 4 0.04
dc_tablespace_quotas 198 2 1.01
dc_users 53,583 29 0.05
dc_rollback_segments 176,362 11 0.01
dc_objects 19,440 1,534 7.89
dc_global_oids 4 2 50.00
dc_object_ids 11,347 483 4.26
dc_sequences 337 7 2.08
dc_usernames 17,546 11 0.06
dc_database_links 4,814 23 0.48
dc_histogram_defs 3,949 470 11.90
dc_profiles 5,989 1 0.02
dc_user_grants 26,592 21 0.08

Buffer Pool Statistics
Ratio = physical_reads/(consistent_gets+db_block_gets) should be < 0.9:
Poolphysical_readsconsistent_getsdb_block_getsRatio
DEFAULT 23,459 11,458,766 801,228 0.00


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.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 0.00how often the log file was full and Oracle had to wait for a new file to become available
table fetch continued row 550.00How many migrated rows did we encounter during this instances life time?


Selected Wait Events (from v$system_event)
NameTotals Total WaitTime (s)Avg Waited (ms)TimeoutsDescription
free buffer waits
000.00 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
23 2 87.0 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 3,787 1,309 345.7 0 Indicator for I/O problems on index accesses
(Consider increasing the buffer cache when value is high)
db file scattered read 2,924 294 100.5 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 12 0 0.0 12 High wait times here could indicate a problem with the extent size, the value of MINEXTENTS, or possibly IO related problems.
enqueue 62 34 548.4 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,568 4,991 1,398.8 1,969 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 20 0 0.0 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 for this event indicate that either your ReDo logs are too small or there are not enough log file groups
log file switch completion000.00 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 22,970 70,816 3,083.0 0 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 7 0 0.0 0
SQL*Net message to client 1,239,804 353 0.3 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 dblink 5,151 1 0.2 0


Enqueue Waits 
The following queues caused waits during the recent uptime of this instance.
Geted by cumulative wait time (desc)
QueueTotal RequestsTotal WaitsSucc.Requests Failed Req.Cum.WaitTime
SQ 369 54 369 0 355


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
REP/867432a1_LimitNotSetExceptionJAVA CLASS03.12.2003 08:2203.12.2003 08:22
REP/d69ea820_IllegalGetChangeExJAVA CLASS03.12.2003 08:2203.12.2003 08:22
REPGD_MOVVIEW03.12.2003 04:2703.12.2003 04:37
REPZD_CONFVIEW03.12.2003 04:2703.12.2003 08:20
REPTO_DOCMOVEMENTPROCEDURE03.12.2003 04:2703.12.2003 04:28
REPGD_MOV_TMPVIEW26.08.2003 03:2203.12.2003 08:20
SYSALL_REPGROUPED_COLUMNVIEW07.08.2002 01:3007.08.2002 01:30
SYSALL_REPPARAMETER_COLUMNVIEW07.08.2002 01:3007.08.2002 01:30
SYSDBA_REPGROUPED_COLUMNVIEW07.08.2002 01:3007.08.2002 01:30
SYSDBA_REPPARAMETER_COLUMNVIEW07.08.2002 01:3007.08.2002 01:30
SYSDBMS_REPCAT_RGT_CUSTPACKAGE BODY07.08.2002 01:3407.08.2002 01:34
SYSDBMS_REPCAT_UTL4PACKAGE BODY07.08.2002 01:3307.08.2002 01:33
SYSUSER_REPGROUPED_COLUMNVIEW07.08.2002 01:3007.08.2002 01:30
SYSUSER_REPPARAMETER_COLUMNVIEW07.08.2002 01:3007.08.2002 01:30
SYS_ALL_REPCOLUMN_GROUPVIEW07.08.2002 01:3007.08.2002 01:30
SYS_ALL_REPCONFLICTVIEW07.08.2002 01:3007.08.2002 01:30
SYS_ALL_REPGROUPED_COLUMNVIEW07.08.2002 01:3007.08.2002 01:30
SYS_ALL_REPPARAMETER_COLUMNVIEW07.08.2002 01:3007.08.2002 01:30
SYS_ALL_REPRESOLUTIONVIEW07.08.2002 01:3007.08.2002 01:30


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) 79,019
table scans (long tables) 28,646
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
If there are tables that will for sure need more extents shortly, we can reduce I/O overhead allocating some extents for them in advance, using "ALTER TABLE tablename ALLOCATE EXTENT". Here are the max. Top 10 candidates, having less than 10 percent free blocks left:
REP.GDGET 0.00%
REP.GDMOV 0.00%
REP.GDCOST 0.00%
REP.HGDGET 0.00%
REP.HGDMOV 0.00%
REP.HGDCOST 0.00%
REP.PDENUM 0.00%
REP.SDAPART 0.00%
REP.SDBASE 0.00%
REP.SDBASEGEB 0.00%


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