Sample Report |
Database name: SAMPLEDB |
| Oracle Release: 9.2.0.7.0 |
| Elapsed Time: 57.25 min |
| DB Time: 219.7 min |
| Statspack Analyzer Recommends: The database has a potentially serious IO bottleneck. Your storage subsystem is holding up processing, and you could accelerate the database by moving the main data tables and indexes to higher performance storage, such as solid state disk . This Oracle system has a serious read load, performing 1,762 reads/second. This database is spending 50% of its available processing time waiting on reads from the storage system. Look at the table space IO statistics and pay attention to the heavily loaded tables that have a high average response time. Reduce the response time for these tables to less than 1 millisecond by moving them to solid state disks . |
Top Timed Events | |
| Event | Percentage of Total Timed Events |
| db file sequential read | 49% |
| The sequential read event occurs when Oracle reads single blocks of a table or index. Look at the tablespace IO section of the report for tablespaces with less than 2 average blocks per read, high response time, and a large percentage of the total IO. Improving the response time of these tables with faster storage will help reduce this wait event and speed up the database. | Moving the data files with the largest amount of time spend waiting on single-block reads to faster storage can significantly reduce the amount of time spent waiting on this event. By reducing the time spent waiting on this event, the database performance could increase 96%. |
| CPU time | 48% |
| CPU time is the amount of time that the Oracle database spent processing SQL statements, parsing statements, or managing the buffer cache. | If this is the main timed event, tuning SQL statements and/or increasing server CPU resources will provide the greatest performance improvement. |
Load Profile | |||||
| Summary: | |||||
| Logical reads: | 164,756/s | Parses: | 1098/s | ||
| Physical reads: | 1,762/s | Hard parses: | 0/s | ||
| Physical writes: | 30/s | Transactions: | 0.28/s | ||
| Rollback per transaction %: | 13.01% | ||||
| Custom Recommendations: | |||||
| Your database has relatively high logical I/O at 164,756 reads per second. Logical Reads includes data block reads from both memory and disk. High LIO is sometimes associated with high CPU activity. CPU bottlenecks occur when the CPU run queue exceeds the number of CPUs on the database server, and this can be seen by looking at the "r" column in the vmstat UNIX/Linux utility or within the Windows performance manager. Consider tuning your application to reduce unnecessary data buffer touches (SQL Tuning or PL/SQL bulking), using faster CPU?s or adding more CPU?s to your system. | |||||
| You are performing more than 1,762 disk reads per second. High disk latency can be caused by too-few physical disk spindles. Compare your read times across multiple datafiles to see which datafiles are slower than others. Disk read times may be improved if contention is reduced on the datafile, even though read times may be high due to the file residing on a slow disk. You should identify whether the SQL accessing the file can be tuned, as well as the underlying characteristics of the hardware devices. Check you average disk read speed later in this report and ensure that it is under 7ms. Assuming that the SQL is optimized, the only remaining solutions are the addition of RAM for the data buffers or a switch to solid state disks. | |||||
| You are performing more than 1,098 SQL parses per second. A parse is the process of executing your SQL, checking for proper security authorization, checks for the existence of tables, columns, and other referenced objects, and generating an execution plan. Your high parses suggest that your system has many incoming unique SQL statements or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables). Confirm that the 1,098 parses per second is reasonable and consider setting cursor_sharing=force if warranted. Setting cursor_sharing=force can cause dramatic performance improvements for systems with ad-hoc query tools such as Crystal Reports or Business Objects. | |||||
| You may have an application issue causing excessive rollbacks with 13.01% rollbacks per transaction. Due to Oracle´s assumption of a commit, the Rollback process is very expensive and should only be used when necessary. You can identify the specific SQL and user session that is executing the rollbacks by querying the v$sesstat view. | |||||
Instance Efficiency | |||||
| Summary: | |||||
| Buffer Hit: | 98% | In-memory Sort: | 100% | ||
| Library Hit: | 99% | Latch Hit: | 99% | ||
| Memory Usage: | 81% | Memory for SQL: | 58% | ||
Wait Events | ||
| Event | Avg Wait (ms) | Waits / txn |
| latch free | 3 | 20.4 |
| log file sync | 6 | 0.8 |
| SQL*Net message from client | 11 | ? |
| db file sequential read | 2 | 3661.3 |
| db file scattered read | 1 | 244.3 |
| buffer busy waits | 5 | 13.9 |
| direct path read | 0 | 13.8 |
| log file parallel write | 6 | 1.7 |
| control file parallel write | 2 | 1.3 |
| Custom Recommendations: | ||
| You have high latch free waits of 20.4 per transaction. The latch free wait occurs when the process is waiting for a latch held by another process. Check the later section for the specific latch waits. Latch free waits are usually due to SQL without bind variables, but buffer chains and redo generation can also cause them. | ||
| You have excessive buffer busy waits with 13.9 per transaction. Buffer busy waits are most commonly caused by segment header contention and can be remedied by increasing the value of the tables & index freelists or freelist_groups parameters, tuning your database writer (DBWR process, or by using Automatic Segment Storage Management (ASSM) in the tablespace definition. Using super-fast SSD can dramatically reduce wait times for other reads and in some cases lessen the buffer busy waits. | ||
Instance Activity Stats | |||
| Statistic | Total | per Second | per Trans |
| SQL*Net roundtrips to/from client | 10,770,583 | 3,135 | 11,301.8 |
| consistent gets - examination | 336,550,178 | 97,976 | 353,148.1 |
| db block changes | 109,743 | 32 | 115.2 |
| physical reads | 6,055,219 | 1,762 | 6,353.9 |
| physical reads direct | 91,306 | 26 | 95.8 |
| physical writes | 105,818 | 30 | 111 |
| physical writes direct | 91,246 | 26 | 95.8 |
| redo writes | 1,575 | 0.5 | 1.7 |
| table fetch continued row | 11,362,529 | 3,307 | 11,922.9 |
| table scans (long tables) | 432 | 0 | 0.5 |
| table scans (short tables) | 58,636 | 17 | 61.5 |
| Custom Recommendations: | |||
| You have high network activity with 3,135 SQL*Net roundtrips to/from client per second, which is a high amount of traffic. Review your application to reduce the number of calls to Oracle by encapsulating data requests into larger pieces (i.e. make a single SQL request to populate all online screen items). In addition, check your application to see if it might benefit from bulk collection by using PL/SQL "forall" or "bulk collect" operators. |
|||
| You have 97,976 consistent gets examination per second. "Consistent gets - examination" is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O. To reduce disk reads, you may consider moving your indexes to a large blocksize tablespace. Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure. | |||
| You have high disk reads with 1,762 per second. Reduce disk reads by increasing your data buffer size or speed up your disk read speed by moving to SSD storage. You can monitor your physical disk reads by hour of the day using AWR to see when the database has the highest disk activity. | |||
| You have 11,362,529 table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row" (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size), when we have tables with > 255 columns, and when PCTFREE is too small. You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining. | |||
| You have high small table full-table scans, at 17 per second. Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD will significantly increase the speed of small-table full-table scans. | |||
Tablespace IO Stats | ||||||
| Tablespace | Read/s | Av Rd(ms) | Blks/Rd | Read% | Write% | % of Total I/O |
| TABLE_1 | 551 | 2.2 | 2 | 100% | 0% | 48.46% |
| TABLE_2 | 530 | 1.5 | 1.1 | 100% | 0% | 46.71% |
| TABLE_3 | 42 | 0.1 | 1 | 100% | 0% | 3.69% |
| TABLE_4 | 4 | 0.1 | 7.1 | 50% | 50% | 0.7% |
Latch Activity | |||
| Latch | Get Requests | % Get Miss | % NoWait Miss |
| cache buffers chains | 803,593,366 | 0.3% | 0% |
| cache buffers lru chain | 30,611 | 0.2% | 0.2% |
| library cache | 39,161,832 | 0.6% | 10% |
| redo allocation | 63,537 | 0% | ?% |
| redo copy | 0 | 0% | 0.1% |
| Custom Recommendations: | |||
| You have high cache buffer chain latches with 803,593,366 get requests at 0.3% get miss. See MetaLink about increasing the hidden parameter _db_block_hash_buckets. | |||
| You have a high value for cache buffer LRU chain waits with 30,611 get requests at 0.2% get miss, and you need to reduce the length of the hash chains for popular data blocks in your RAM buffer. Investigate the specific data blocks that are experiencing the latches and reduce the popularity of the data block by spreading the rows across more data blocks by reorganizing with a higher value for PCTFREE. | |||
| You have high library cache waits with 39,161,832 get requests at 0.6% get miss. Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep. | |||
Buffer Pool Advisory | ||
| Summary: | ||
| Current: | 79,007,914 disk reads | |
| Optimized: | 32,503,580 disk reads | |
| Estimated Improvement: | 59% fewer | |
Custom Recommendations: | ||
| The Oracle buffer cache advisory utility indicates 79,007,914 disk reads during the sample interval. Oracle estimates that doubling the data buffer size (by increasing db_cache_size) will reduce disk reads to 32,503,580, a 59% decrease. | ||
PGA Memory Advisory | ||
| Summary: | ||
| Current: | 100% cache hit | |
| Optimized: | 100% cache hit | |
| Estimated Improvement: | 0% higher | |
Init.ora Parameters | |||
| Summary: | |||
| db_block_size | 8,192 | ||
| db_cache_size | 671,088,640 | ||
| db_file_multiblock_read_count | 16 | ||
| hash_join_enabled | true | ||
| optimizer_mode | choose | ||
| pga_aggregate_target | 1,048,576,000 | ||
| query_rewrite_enabled | true | ||
| shared_pool_size | 402,653,184 | ||
| sort_area_size | 1,024,000 | ||
| Custom Recommendations: | |||
| You are using the default optimizer mode and you may consider setting it to first_rows or first_rows_n if you have an online transaction processing system. | |||
| You have the default value for db_file_multiblock_read_count at 16. The CBO uses this parameter to determine the cost of a full-table scan. The default value is sometimes too large, and you can run scripts to determine the optimal setting. If full-table scans are unavoidable, you may consider placing those tables on SSD . | |||
| You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes. | |||
| Your shared pool is set at 402MB, which is an unusually large value. Allocating excessive shared pool resource can adversely impact Oracle performance. For further details, see the shared pool advisory. | |||
| You are not using your KEEP pool to cache frequently referenced tables and indexes. This may cause unnecessary I/O. When configured properly, the KEEP pool guarantees full caching of popular tables and indexes. Remember, an average buffer get is often 100 times faster than a disk read. Any table or index that consumes > 10% of the data buffer, or tables & indexes that have > 50% of their blocks residing in the data buffer should be cached into the KEEP pool. You can fully automate this process using scripts. | |||
| Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer. Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view. | |||
If the statspack analyzer is unable to process your report, please email your statspack file, and we will reply with your results.