Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> enq: HW - contention, enq: HW - contention
hussaini
post Feb 14 2011, 10:34 AM
Post #1


Advanced Member
***

Group: Members
Posts: 97
Joined: 12-January 05
Member No.: 1,275



Hi,

My AWR report is showing enq: HW - contention in the Foreground Wait Events. The table resides in a ASSM tablespaces.

What could be causing this..? The version is 11.1.0.7.0..?

Thanks,
Go to the top of the page
 
+Quote Post
burleson
post Feb 14 2011, 01:25 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,537
Joined: 26-January 04
Member No.: 13



Hi Hussaini,


See here: http://www.dba-oracle.com/t_enqueue_wait_types_.htm


>> "enq: HW":

The HW High Water enqueue (a.k.a. enq: HW – contention) occurs when competing processing are inserting into the same table and are trying to increase the high water mark of a table simultaneously. The HW enqueue can sometimes be removed by adding freelists or moving the segment to ASSM.

This is a hardware serialization wait.

You need to provide more info to get the exact source of the contention!

If you trace this, the dump would provide more clues . . . See MOSC note 419348.1:

http://support.oracle.com

What are your avg disk read times?

You have have object that has small NEXT extent sizes?

Do you see CPU enqueues?

Does you table have a LOB coluumn?

Do you see object extending rapidly?


Can you take a STATSPACK or AWR report, and paste it into STATSPACKANALYZER for primitive analysis and post the results?

http://www.statspackanalyzer.,com

***********************************************
>> ASSM

ASSM can cause degredation under high insert DML:

http://www.dba-oracle.com/oracle10g_tuning...performance.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
hussaini
post Feb 15 2011, 06:03 AM
Post #3


Advanced Member
***

Group: Members
Posts: 97
Joined: 12-January 05
Member No.: 1,275



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.

Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 1st October 2014 - 11:14 AM