| Data Files |
|---|
| Tablespace | Datafile | Status |
Enabled | Size (kB) | Free (kB) | Used (%) | Phys.Reads | Phys.Writes |
Avg.I/O Time |
|---|
| DATA02 | /opt/oracle/ORADB/vol01/data02.dbf | ONLINE | READ WRITE | 307,200.00 | 97,856.00
| 68.15 | 18,373 | 1,945 | 0 |
| DATA03 | /opt/oracle/ORADB/vol01/data03.dbf | ONLINE | READ WRITE | 5,120,000.00 | 17,152.00
| 99.67 | 1,189,535 | 68,144 | 0 |
| DATA01 | /opt/oracle/ORADB/vol01/data01.dbf | ONLINE | READ WRITE | 1,536,000.00 | 11,968.00
| 99.22 | 12 | 6 | 0 |
| INDEX02 | /opt/oracle/ORADB/vol01/index02.dbf | ONLINE | READ WRITE | 409,600.00 | 90,112.00
| 78.00 | 27,832 | 501 | 0 |
| INDEX03 | /opt/oracle/ORADB/vol01/index03.dbf | ONLINE | READ WRITE | 2,048,000.00 | 79,104.00
| 96.14 | 438,655 | 182,096 | 0 |
| INDEX01 | /opt/oracle/ORADB/vol01/index01.dbf | ONLINE | READ WRITE | 122,880.00 | 10,944.00
| 91.09 | 12 | 6 | 0 |
| SYSTEM | /opt/oracle/ORADB/vol01/system.dbf | SYSTEM | READ WRITE | 143,360.00 | 1,120.00
| 99.22 | 4,029 | 294 | 0 |
| TEMP | /opt/oracle/ORADB/vol01/temp.dbf | ONLINE | READ WRITE | 552,960.00 | 1,120.00
| 99.80 | 4,030 | 294 | 0 |
| UNDOTBS | /opt/oracle/ORADB/vol01/undotbs.dbf | ONLINE | READ WRITE | 2,027,520.00 | 1,923,776.00
| 5.12 | 501 | 121,835 | 0 |
| SYSSTAT Info |
|---|
| Name | Value | Description |
|---|
| DiskSorts / MemorySorts | 0.00 | Higher values are an indicator to increase SORT_AREA_SIZE |
| 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.01 | 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 |
| redo log space requests | 15.00 | how often the log file was full and Oracle had to wait for a new file to become available |
| table fetch continued row | 62.00 | How many migrated rows did we encounter during this instances life time? If the number is markable, 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) |
|---|
| 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.
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. |
| db file sequential read | 1,310,206 | 385,631 | 294.3 | 0
| Indicator for I/O problems on index accesses
(Consider increasing the buffer cache when value is high) |
| db file scattered read | 368,623 | 71,677 | 194.4 | 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 | 142,942 | 56 | 0.4 | 142,941
| 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 | 0 | 0 | 0.0 | 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. |
| latch free | 289 | 305 | 1,055.4 | 106
| 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 | 161 | 1 | 6.2 | 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 indicate that either your ReDo logs are too small or there are not enough log file groups |
| log file switch completion | 14 | 137 | 9,785.7 | 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 | 43,761 | 128 | 2.9 | 43,755
| 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 | 48 | 8 | 166.7 | 0 |
| SQL*Net message to client | 8,585,433 | 1,847 | 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 dblink | 0 | 0 | 0.0 | 0 |
| 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) | 1,426,021 |
| table scans (long tables) | 8 |
| 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 some candidates, having less than 10 percent free blocks left: |
| SKANDA.BAM | 5.13% |
| SKANDA.BOA | 2.21% |
| SKANDA.COMMON | 0.00% |
| SKANDA.BASKET | 0.00% |
| SKANDA.FKI | 8.51% |
| SKANDA.HISTA | 0.13% |
| SKANDA.KUNDEN | 5.15% |
| SKANDA.INKASSO | 2.51% |
| SKANDA.KUM | 2.25% |
| SKANDA.LIMA | 4.76% |
| SKANDA.PRICE | 8.44% |
| SKANDA.RISK | 3.64% |
| SKANDA.TIPPS | 0.75% |