|
This is the analysis gibes no indication how to resolve the eng:HW ..?!?
Database Info Tue Feb 15 10:14:39 UTC 2011
DB ID Instance Release RAC Host
6734121920 GHLT02 11.1.0.7.0 NO undefined
Elapsed: 60.04 (min) 3,602.4 (sec)
DB Time: 103.99 (min) 6,239.67 (sec)
Cache: 2,880 MB
Block Size: 8,192 bytes
Transactions: 16.9 per second
Performance Summary
Physical Reads: 129/sec MB per second: 1.01 MB/sec
Physical Writes: 104/sec MB per second: 0.81 MB/sec
Single-block Reads: 95.44/sec Avg wait: 4.79 ms
Multi-block Reads: 0.05/sec Avg wait: 17.65 ms
Tablespace Reads: 129/sec Writes: 86/sec
Top 5 Events
Event Percentage of Total Timed Events
enq: HW - contention 36.3%
db file sequential read 26.4%
log file sync 17.7%
direct path sync 11.7%
DB CPU 3.1%
Top 5 Events
Event Percentage of Total Timed Events
enq: HW - contention 36.3%
Other
db file sequential read 26.4%
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 36%.
log file sync 17.7%
This event is caused by waiting for the LGWR to post after a session performs a commit. This can be tuned by reducing the number of commits. Moving some or all copies of your redo logs logs onto the WriteAccelerator can reduce the amount of time spent waiting for this event.
direct path sync 11.7%
Other
DB CPU 3.1%
Other
Tablespace I/O Stats
Tablespace Read/s Av Rd(ms) Blks/Rd Writes/s Read% % Total IO
LOADTEST_SI 129 3.6 1 86 60% 99.42%
Tablespace I/O Stats
Tablespace Wait (s) Read/s Av Rd(ms) Blks/Rd Writes/s Read% % Total IO
LOADTEST_SI 2,786 129 3.6 1 86 60% 99.42%
Load Profile
Logical reads: 4,534/s Parses: 6.9/s
Physical reads: 129/s Hard parses: 0.1/s
Physical writes: 104/s Transactions: 16.9/s
Rollback per transaction: ?% Buffer Nowait: 99.99%
2 Recommendations:
You are performing more than 104 disk writes per second. Check to ensure that you are using direct I/O (non buffer) on your database and perform an IO balance and an IO timing analysis to determine if a disk or array is undergoing IO related stress, and verify your DBWR optimization. Check you average disk read speed later in this report and ensure that it is under 7 milliseconds. If not, consider a WriteAccelerator for faster physical writes.
Long I/O times are occuring when using the following tablespaces: SYSAUX , SYSTEM . Consider placing these tablespace´s datafiles on solid state disk for faster disk access.
Instance Efficiency
Buffer Hit: 99.95% In-memory Sort: 100%
Library Hit: 99.85% Latch Hit: 99.99%
Memory Usage: 87.86% Memory for SQL: 97.38%
0 Recommendations:
SQL Statistics
Click here to see all SQL data
Wait Events
Event Waits Wait Time (s) Avg Wait (ms) Waits/txn
enq: HW - contention 98,326 2,265 23 1.6
db file sequential read 343,802 1,646 5 5.7
log file sync 123,593 1,103 9 2.0
direct path sync 302,127 729 2 5.0
SQL*Net more data from cli 632,102 154 0 10.4
direct path write 302,666 124 0 5.0
enq: TX - index contention 768 11 14 0.0
db file scattered read 170 3 20 0.0
read by other session 191 2 13 0.0
control file sequential re 2,394 2 1 0.0
2 Recommendations:
You have a high value for log file sync waits at 2.0 per transaction. Check to ensure that your application does frequent commits and consider moving your redo log files to the Write Accelerator. Also consider increasing your log_buffer size.
Your average wait time for db file scattered read is 20 milliseconds, which is very slow. The scattered read events occur when Oracle reads multiple blocks of a table or index (a full table scan of fast full index scan). Full-table scans may indicate missing indexes. Check for missing indexes and verify that you have optimized your db_file_multiblock_read_count. If these steps fail to reduce your disk wait times, moving some of your datafiles to solid state disk will reduce the amount of time spent waiting for scattered disk reads.
Instance Activity Stats
Statistic Total per Second per Trans
consistent gets 7,936,414 2,203.2 130.8
consistent gets - examination 1,638,297 454.8 27.0
db block changes 4,390,508 1,218.8 72.3
execute count 359,018 99.7 5.9
parse count (hard) 276 0.1 0.0
parse count (total) 24,727 6.9 0.4
physical reads 465,395 129.2 7.7
physical reads direct 458,308 127.2 7.6
physical writes 377,276 104.7 6.2
physical writes direct 338,587 94.0 5.6
redo writes 158,484 44.0 2.6
session cursor cache hits 37,378 10.4 0.6
sorts (disk) 0 0.0 0.0
sorts (memory) 10,540 2.9 0.2
table fetch continued row 65 0.0 0.0
table scans (long tables) 1 0.0 0.0
table scans (short tables) 8,229 2.3 0.1
workarea executions - onepass 0 0.0 0.0
3 Recommendations:
You have high update activity with 1,218.8 db block changes per second. The DB block changes are a rough indication of total database work. This statistic indicates (on a per-transaction level) the rate at which buffers are being dirtied and you may want to optimize your database writer (DBWR) process. You can determine which sessions and SQL statements have the highest db block changes by querying the v$session and v$sessatst views.
You have high disk write activity with 104.7 per second. You should drill-down and identify the sessions that are performing the disk writes as they can cause locking contention within Oracle. Also investigate moving your high-write datafiles to a smaller data buffer to improve the speed of the database writer process. In addition, you can dramatically improve your disk write speed by moving the high-write datafiles to a WriteAccelerator.
You have high small table full-table scans, at 2.3 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 or the WriteAccelerator will significantly increase the speed of small-table full-table scans.
Init.ora Parameters
Parameter Value
db_block_size 8,192
log_archive_start true
pga_aggregate_target 1.46GB
_optimizer_cost_model choose
session_cached_cursors 50
cursor_sharing exact
3 Recommendations:
You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes.
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.
|
enq: HW - contention, enq: HW - contention
Feb 14 2011, 10:34 AM
