The following queues caused waits during the recent uptime of this instance.
Geted by cumulative wait time (desc)
Geted by cumulative wait time (desc)
[ Users ] [ Datafiles ] [ Rollback ] [ Memory ]
[ Pool Sizes ] [ Shared Pool ] [ Buffer Pool ] [ SysStat ]
[ Events ] [ Invalid Objects ] [ Misc ] |
| Common Instance Information | |
|---|---|
| Hostname: | buzz |
| Instance: | ORADB |
| Version: | 9.0.1.0.0 |
| Archiver: | STARTED |
| ArchivedLogSize: | MB |
| FileSize (Data+Log) | 909.00 MB |
| Startup / Uptime | 19.11.2003 10:41 / 28.26 d |
| Report generated: | 17.12.2003 16:52 |
| User Information | ||||||
|---|---|---|---|---|---|---|
| Username | Account Status | Lock Date | Expiry Date | Default TS | Temporary TS | Created |
| SYS | OPEN | - | - | SYSTEM | TEMP | 07.08.2002 |
| SYSTEM | OPEN | - | - | SYSTEM | TEMP | 07.08.2002 |
| OUTLN | OPEN | - | - | SYSTEM | TEMP | 07.08.2002 |
| DBSNMP | OPEN | - | - | SYSTEM | TEMP | 07.08.2002 |
| REP | OPEN | - | - | ESP | TEMP | 28.03.2003 |
| CON | OPEN | - | - | ESP | TEMP | 28.03.2003 |
| ORACLE | OPEN | - | - | SYSTEM | TEMP | 30.10.2002 |
| DIRECT | OPEN | - | - | ESP | TEMP | 04.06.2003 |
| Admins | |
|---|---|
| User | Admin Option |
| SYS | YES |
| SYSTEM | YES |
| DB Links | |||||
|---|---|---|---|---|---|
| Owner | DB Link | Username | Host | Created | Status |
| PUBLIC | ORA1.INTRA.NET | INLN | ORA1 | 16.08.2002 | ACTIVE |
| PUBLIC | DS00.INTRA.NET | INLN | DS00 | 28.11.2003 | ACTIVE |
| PUBLIC | ETC.INTRA.NET | INLN | ETC | 03.03.2003 | ACTIVE |
| PUBLIC | PMST.INTRA.NET | INLN | PMST | 30.10.2002 | ACTIVE |
| PUBLIC | WMP.INTRA.NET | INLN | WMP | 30.09.2002 | ACTIVE |
| PUBLIC | XON.INTRA.NET | INLN | XON | 14.08.2003 | ACTIVE |
| PUBLIC | XOP.INTRA.NET | INLN | xtro | 02.01.2003 | ACTIVE |
| Data Files | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| Tablespace | Datafile | Status | Enabled | Size (kB) | Free (kB) | Used (%) | Phys.Reads | Phys.Writes | Avg.I/O Time |
| ESP | /opt/oracle/oraData/ORADB/vol1/esp.dbf | ONLINE | READ WRITE | 55,296.00 | 960.00 | 98.26 | 3,364 | 2,526 | -1,453,701,380 |
| SYSTEM | /opt/oracle/oraData/ORADB/vol1/system01.dbf | SYSTEM | READ WRITE | 332,800.00 | 130,376.00 | 60.82 | 2,049 | 3,890 | -2,059,251,148 |
| TEMP | /opt/oracle/oraData/ORADB/vol1/temp01.dbf | ONLINE | READ WRITE | 40,960.00 | 130,376.00 | -218.30 | 2,049 | 3,890 | -2,059,251,148 |
| UNDOTBS | /opt/oracle/oraData/ORADB/vol1/undotbs01.dbf | ONLINE | READ WRITE | 235,520.00 | 234,176.00 | 0.57 | 39 | 22,401 | 0 |
| Rollback Segments | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Segment | Status | Size (kB) | OptSize (kB) | HWMSize (kB) | Waits | XActs | Shrinks | Wraps | AveShrink | AveActive |
| SYSTEM | ONLINE | 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 | |
|---|---|
| Name | Size |
| 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_keep | 0 kB |
| buffer_pool_recycle | 0 kB |
| global_context_pool_size | 0 kB |
| Pool Sizes | |
|---|---|
| Pool | Space |
| 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 |
| Pool | Free 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:
| |||||
| NameSpace | Gets | Pins | Reloads | RPP | GetHitRatio (%) |
| 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: | |||||
| Parameter | Gets | GetMisses | Ratio | ||
| 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: | ||||
| Pool | physical_reads | consistent_gets | db_block_gets | Ratio |
| DEFAULT | 23,459 | 11,458,766 | 801,228 | 0.00 |
| 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.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 | 0.00 | how often the log file was full and Oracle had to wait for a new file to become available |
| table fetch continued row | 550.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 | 0 | 0 | 0.0 | 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 | 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) | 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 | 0 | 0 | 0.0 | 0 | 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 | 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) | |||||
| Queue | Total Requests | Total Waits | Succ.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: | ||||
| Owner | Object | Typ | Created | Last DDL |
| REP | /867432a1_LimitNotSetException | JAVA CLASS | 03.12.2003 08:22 | 03.12.2003 08:22 |
| REP | /d69ea820_IllegalGetChangeEx | JAVA CLASS | 03.12.2003 08:22 | 03.12.2003 08:22 |
| REP | GD_MOV | VIEW | 03.12.2003 04:27 | 03.12.2003 04:37 |
| REP | ZD_CONF | VIEW | 03.12.2003 04:27 | 03.12.2003 08:20 |
| REP | TO_DOCMOVEMENT | PROCEDURE | 03.12.2003 04:27 | 03.12.2003 04:28 |
| REP | GD_MOV_TMP | VIEW | 26.08.2003 03:22 | 03.12.2003 08:20 |
| SYS | ALL_REPGROUPED_COLUMN | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | ALL_REPPARAMETER_COLUMN | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | DBA_REPGROUPED_COLUMN | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | DBA_REPPARAMETER_COLUMN | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | DBMS_REPCAT_RGT_CUST | PACKAGE BODY | 07.08.2002 01:34 | 07.08.2002 01:34 |
| SYS | DBMS_REPCAT_UTL4 | PACKAGE BODY | 07.08.2002 01:33 | 07.08.2002 01:33 |
| SYS | USER_REPGROUPED_COLUMN | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | USER_REPPARAMETER_COLUMN | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | _ALL_REPCOLUMN_GROUP | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | _ALL_REPCONFLICT | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | _ALL_REPGROUPED_COLUMN | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | _ALL_REPPARAMETER_COLUMN | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| SYS | _ALL_REPRESOLUTION | VIEW | 07.08.2002 01:30 | 07.08.2002 01:30 |
| 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) | 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 |