당신은 주제를 찾고 있습니까 “enq hw contention – How Oracle Determines Wait Time When It Releases Control“? 다음 카테고리의 웹사이트 https://chewathai27.com/you 에서 귀하의 모든 질문에 답변해 드립니다: https://chewathai27.com/you/blog. 바로 아래에서 답을 찾을 수 있습니다. 작성자 OraPub, Inc. 이(가) 작성한 기사에는 조회수 739회 및 좋아요 1개 개의 좋아요가 있습니다.
Table of Contents
enq hw contention 주제에 대한 동영상 보기
여기에서 이 주제에 대한 비디오를 시청하십시오. 주의 깊게 살펴보고 읽고 있는 내용에 대한 피드백을 제공하세요!
d여기에서 How Oracle Determines Wait Time When It Releases Control – enq hw contention 주제에 대한 세부정보를 참조하세요
Have you ever wondered how the Oracle Database 12c (and earlier versions) determines the wait time when it has absolutely no control over how long the wait will take? If so, then read on in my blog posting: http://blog.orapub.com/20140630/How-The-Oracle-Database-Determines-Wait-Time-When-It’s-Not-Set.html
Check out OraPub’s Online Institute where this topic is part of the seminar, Introduction To Oracle Time Based Analysis. https://resources.orapub.com/OraPub_Online_Seminars_About_Oracle_Database_Performance_s/100.htm
enq hw contention 주제에 대한 자세한 내용은 여기를 참조하세요.
enq: HW – contention tips – Burleson Consulting
Answer: 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 …
Source: www.dba-oracle.com
Date Published: 7/16/2021
View: 6524
enq: HW – contention- High Water Mark Brokered – LinkedIn
The enqueue signifies that multiple PX can not update the HWM value at the same time. In general, HWMB results in less extents being added to …
Source: www.linkedin.com
Date Published: 2/8/2022
View: 7443
enq: HW – contention | Oracle Tuning – WordPress.com
This fix proves a workaround option which has to be carefully installed and integrated into the workload – this workaround can help reduce the …
Source: magnusjohanssontuning.wordpress.com
Date Published: 10/13/2022
View: 2864
How To Analyze wait: ‘enq: HW – contention’ – Database Blog
How To Analyze wait: ‘enq: HW – contention’ … This wait can have an impact to production and deteriorate performance on a DB, so lets …
Source: www.alex-sanz.com
Date Published: 1/11/2021
View: 1030
Combat with an enqueue wait event (enq: HW–Contention)
The basic reason why the ‘enq: HW – contention’ raises is because the additional extents are not being allocated to in a timely manner for the data being …
Source: www.dbasolved.com
Date Published: 4/23/2021
View: 5645
enq: HW – contention – Database Tutorials
enq: HW – contention … Enqueues are shared memory structures that serialize access to database resources.(locks) They can be associated with a …
Source: dbtut.com
Date Published: 5/3/2021
View: 8644
enq HW-contention analysis – Katastros
The high water mark of a segment is the boundary between used and unused space in that segment. If contention is occurring for “enq: HW – contention ”It is …
Source: blog.katastros.com
Date Published: 10/16/2021
View: 4231
Resolving HW enqueue contention
Heavy inserts in to a table can result in increased HWM activity leading to HW enqueue contention. This issue is prevalent if the table has LOB …
Source: orainternals.wordpress.com
Date Published: 4/22/2021
View: 3147
주제와 관련된 이미지 enq hw contention
주제와 관련된 더 많은 사진을 참조하십시오 How Oracle Determines Wait Time When It Releases Control. 댓글에서 더 많은 관련 이미지를 보거나 필요한 경우 더 많은 관련 기사를 볼 수 있습니다.
주제에 대한 기사 평가 enq hw contention
- Author: OraPub, Inc.
- Views: 조회수 739회
- Likes: 좋아요 1개
- Date Published: 2014. 6. 21.
- Video Url link: https://www.youtube.com/watch?v=WdJa4feBvWg
enq: HW – contention tips
enq: HW – contention tips Oracle Database Tips by Donald Burleson August 17, 2015
Question: My AWR report is showing enq: HW – contention in the Foreground Wait Events. The table resides in a ASSM tablespaces. We are waiting significantly on ‘enq: HW – contention’ in our STATSPACK report also.
Answer: 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. This is basically a hardware serialization wait. 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.
One solution is to spread the target objects across more physical disk spindles. 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:
Get the Complete
Oracle SQL Tuning Information
The landmark book “Advanced Oracle SQL Tuning The Definitive Reference” is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum . Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.
Burleson Consulting
The Oracle of Database Support Oracle Performance Tuning Remote DBA Services
Copyright © 1996 – 2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.
enq: HW – contention- High Water Mark Brokered
Recently, team reported slowness while performing bulk loading. Below statement was identified
INSERT /*+ PARALLEL(TEMP_CH,16) APPEND ENABLE_PARALLEL_DML */ INTO TEMP_CH SELECT /*+ PARALLEL(A,16) full(A) */ * FROM TEMP_CH_2 A;
Execution Plan:
While analyzing the scenario, below were the events that i came across HW contention was the major one. Although it was not a major worry for me but somehow it lead to a good discovery. So wanted to share my thoughts.
EVENT COUNT(EVENT) ————————————————– ———— enq: HW – contention 262 buffer busy waits 34 Datapump dump file I/O 33 direct path write 17 direct path read temp 15 Disk file operations I/O 5 direct path write temp 4 gc current multi block request 2
So from here there were few points to capture and to connect dots.
a) Direct Path Insert (Append Used). b) Parallel degree Used. c) ENQ HW Contention observed. d) Source and Target table were partitioned and both were equally partitioned. No of partition were 61. e) In the Execution Plan: You can see Space management used by Oracle “HIGH WATER MARK BROKERED” f) No other statement were executed on the table TEMP_CH; only above insert was executed.
g) No Lob columns.
So one thing is clear since append was used so insert will load the data above High Water Mark(HWM), Rows above the high water mark will not touched by queries unless you commit it and once committed HWM moves to new position. So from here at least 2 dots were connected i.e. Direct path insert via append and HWM movement.
Now for HW contention to happen; it has to have multiple sessions or slaves to move the HWM at same point of time; i.e while one moves other waits.
So one more step forward and observe point E as mentioned above, the space management used by Oracle “HIGH WATER MARK BROKERED” or HWMB.
Oracle database does manages space management while performing direct path loading and such information is clearly visible in the execution plan itself starting 12C.
Brief about HWMB: Oracle can use High water mark brokering (HWMB) if multiple PX server try to hit the same table or partition segment. So basically depending upon the degree of parallelism, partition spread and execution plan; multiple PX may hit the same segment/partition which means HWM will be altered at same time as it is append/direct path insert.
So if multiple PX processes at same time, it is mandatory for Oracle to maintain the coordination between the slaves so that HWM is not ruptured, so here brokering is required.
To ensure the data consistency, a structure like enqueue was needed to protect the High Water Mark being written by multiple slaves. The “HV “enqueue is used. Brokering is basically implemented using HV enqueues.
Each segment/partition has its own HV enqueue, which record the position that the high water mark must be moved to once the transaction is committed.
The enqueue signifies that multiple PX can not update the HWM value at the same time. In general, HWMB results in less extents being added to the table (or partition) segment when compare to temp merge.
So above covered point B and Point E; in-fact since i talked here partition/segment as well, So it covered point D as well.
Please note there are detailed articles written by Oracle experts on space management in respect to Direct path insert. So please read them whenever you feel so:); the only thing which is not properly documented on what basis Oracle choose among different options or may be i missed to understand.
Here in the article i emphasized on HWMB. Now the question arises why Oracle choose HWMB here; why it didn’t Oracle choose PART KEY or Equi loading.
By the way: Equi-partition loading will be chosen automatically if you copy data in parallel between two equi-partitioned tables, and only if the tables have a relatively large number of partitions.
So here i put my observation to understand the behavior in terms of partition and no of slaves; it is not in terms of Oracle internal algorithm/hidden parameter affect but for most of the case it worked for me and it helped designing the parallel slaves for each table for performing bulk load.Although I welcome all the comment if someone knows exact reason.
I tried no of traces but i didn’t get much info on what basis Oracle is choosing different load methods.
What i saw especially for partition segments: (No of partition/DOP ) if this ratio is greater than or equal to 8 then instead of HWMB you will have a different space management while doing direct path loading and if the ratio is less than 8 then mostly you will always see HWMB,means multiple PX server will process the same segment which leads to contention.
In above case table does have 61 partition and the DOP used is 16. So 61/16 is 3 taking whole number which is less than 8, so you can see it went to HWMB.
So lets try to decrease the degree of parallel to 8
INSERT /*+ PARALLEL(TEMP_CH,8) APPEND ENABLE_PARALLEL_DML */ INTO TEMP_CH SELECT /*+ PARALLEL(A,8) full(A) */ * FROM TEMP_CH_2 A;
So again from execution plan you can see here Oracle took HWMB only and the ratio of (no of partition/DOP) 61/8= 7
Lets further reduce the parallel degree to 6.
INSERT /*+ PARALLEL(TEMP_CH,6) APPEND ENABLE_PARALLEL_DML */ INTO TEMP_CH SELECT /*+ PARALLEL(A,6) full(A) */ * FROM TEMP_CH_2 A;
So see here no of partition is till 61 but degree is reduced to 6; so 61/6=10; so you see Oracle changed the plan and you can see distribution is by partition key, TQ also increased under the DFO tree and here it is just high water mark i.e. no race between px processes.
If you observed all the cases it is the PQ_DISTRIBUTION i.e. parallel distribution method is the one which is doing the trick which is the causing Oracle to choose different space management for such direct path inserts.
Off-course you can always influence parallel distribution using PQ_DISTRIBUTE hint.
INSERT /*+ PARALLEL(TEMP_CH,16) APPEND ENABLE_PARALLEL_DML pq_distribute(TEMP_CH partition) */ INTO TEMP_CH SELECT /*+ PARALLEL(A,16) full(A) */ * FROM TEMP_CH_2 A;
In short ratio helped me while designing the flow in terms of parallelism, although i still need to study in deep to see what is the real cause till then.
Enjoy Learning!!!!
enq: HW – contention
While doing a health check for a customer I noticed that enq: HW – contention was high, we had 19.63% of the db time in the AWR report. The analyze showed that it was a limitation of the feature that was used.
Database Info
Oracle 11.2.0.3 on redhat Linux 6
The AWR report:
What is an enqueue?
Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction.
What is high water mark (HW)
The boundary between used and unused space in a segment.
The analyze is as follows:
Oracle stores information about almost all executions in the database, sql executed, elapsed time, cpu time etc. Wait information is also stored. We can look for that information in historic views.
First we need to find the event id for enq: HW – contention, we do that by querying
v$event_name.
SELECT event_id, name, parameter1, parameter2, parameter3 FROM v$event_name WHERE name = ‘enq: HW – contention’; EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 ———- ——————– ———— ———- ————- 1645217925 enq: HW – contention name|mode table space # block
Now when we have the event_id we can get the information about the event from dba_hist_active_sess_history.
SELECT event, p1, p2, p3, Count(1) FROM dba_hist_active_sess_history WHERE event_id = 1645217925 GROUP BY event, p1, p2, p3; EVENT P1 P2 P3 COUNT(1) ——————— ——————– ———- ———- ——– enq: HW – contention 1213661190 14 58720522 22681
So we get the information from dba_hist_active_sess_history, this view contains the history of the contents of the in-memory active session history of recent system activity. We can see that we have waited 22681 times for this event.
Next step is to determine the correct file and block number by using dbms_utility package.
SELECT dbms_utility.Data_block_address_file(58720522) FILE#, dbms_utility.Data_block_address_block(58720522) BLOCK# FROM dual;
FILE# BLOCK# ———- ———- 14 266
Now we can use this information to get which segment it belongs too.
SELECT owner, segment_type, segment_name FROM dba_extents WHERE file_id = 14 AND 266 BETWEEN block_id AND block_id + blocks – 1;
OWNER SEGMENT_TYPE SEGMENT_NAME ————- —————– ————————————- XXXX LOBSEGMENT SYS_LOB0000014653C00008$$
So we can see that this is a lob segment, but we do not which table the segment belongs to. We can find out by checking dba_lobs table.
SELECT owner, table_name, column_name, chunk, securefile FROM dba_lobs WHERE segment_name = ‘SYS_LOB0000014653C00008$$’
OWNER TABLE_NAME COLUMN_NAME CHUNK SECUREFILE ————- ————- ———————– ———- ———- XXXX JBM_MSG PAYLOAD 8192 NO
So now we have the table and column that uses this lob segment.
The most interesting information here is that we can see that this is not a securefile (=No) but a basic file. What if we search in MOS on basic files and enq. HW – contention ?
We get this information popping up:
Bug 7319120 – HW contention for BasicFile LOBs – special workaround
(Doc ID 7319120.8)
“When concurrent inserts into basic file LOBS suffers a performance bottle neck due to HW enqueue contention then you have likely hit this issue. This is really a limitation with basicfile LOBs . This fix provides a workaround option which has to be carefully installed and integrated into the workload – this workaround can help reduce the HW contention. Workaround: The preferred option for such contention on basicfile LOBs is to migrate the LOBS to Securefiles in RDBMS 11g or later. Securefile LOBs have been designed for better concurrency.”
Additional information
2015-08-17, My colleague Peter Kramsu showed me an alternative to convert to securefiles. I still think it is the best solution but it requires some planning and perhaps it cannot be done immediately.
Information comes from Bug 6376915 : ENQ: HW – CONTENTION WITH LOB SEGMENTS
This fix causes ASSM LOB space to batch up reclaim instead of just reclaiming the requested/required number of LOB chunks. To enable this fix, set event 44951 to the maximum number of chunks that you would like to have reclaimed per attempt. The maximum allowed is 1024. Anything larger becomes 1024. However, if the requested amount of space is larger than the event’s value, the reclaim will be for the requested amount of space. SQL> ALTER SYSTEM SET EVENT=”44951 TRACE NAME CONTEXT FOREVER, LEVEL <1 .. 1024>” scope=spfile ; NOTE: that a database restart is required for it to be effective.
Conclusion
The limitation of basic files is the main reason for the wait event, convert basic file to secure file should fix the issue.
If you are interested how you can convert a basicfile to a securefile, please see this post
Refrence
Bug 7319120 – HW contention for BasicFile LOBs – special workaround (Doc ID 7319120.8) ‘enq HW – contention’ For Busy LOB Segment(Doc ID 740075.1) How To Analyze the Wait Statistic: ‘enq: HW – contention'(Doc ID 419348.1)
How To Analyze wait: ‘enq: HW – contention’ – Database Blog
This wait can have an impact to production and deteriorate performance on a DB, so lets investigate…
Lets see the output from a AWR report where this event is 23% of the total wait time:
Top Timed Events DB/Inst: PROD_DB/PROD_DB1 Snaps: 32287-32288 -> Instance ‘*’ – cluster wide summary -> ‘*’ Waits, %Timeouts, Wait Time Total(s) : Cluster-wide total for the wait event -> ‘*’ ‘Wait Time Avg (ms)’ : Cluster-wide average computed as (Wait Time Total / Event Waits) in ms -> ‘*’ Summary ‘Avg Wait Time (ms)’ : Per-instance ‘Wait Time Avg (ms)’ used to compute the following statistics -> ‘*’ [Avg/Min/Max/Std Dev] : average/minimum/maximum/standard deviation of per-instance ‘Wait Time Avg(ms)’ -> ‘*’ Cnt : count of instances with wait times for the event Wait Event Wait Time Summary Avg Wait Time (ms) ————————————————— ———————- —————————— —————————————- I# Class Event Waits %Timeouts Total(s) Avg(ms) %DB time Avg Min Max Std Dev Cnt —- ———- —————————————- ———— ——— ————- ——- ——– ——– ——– ——– ——– —- * Configurat enq: HW – contention 897,229 3.6 526,247.20 586.5 23.78 719.44 258.25 1180.63 652.22 2 Other block change tracking buffer space 475,058 89.0 444,324.44 935.3 20.08 939.31 933.58 945.03 8.10 2 DB CPU N/A N/A 409,874.75 N/A 18.52 2 Concurrenc buffer busy waits 12,345,097 0.0 187,904.05 15.2 8.49 17.68 14.25 21.10 4.84 2 Cluster gc buffer busy acquire 12,999,693 0.2 128,460.30 9.9 5.80 8.66 6.59 10.74 2.93 2 Other cr request retry 11,189,744 97.5 103,385.16 9.2 4.67 9.26 9.23 9.28 0.03 2 User I/O cell single block physical read 289,619,915 0.0 82,946.50 0.3 3.75 0.29 0.29 0.29 0.00 2 Cluster gc buffer busy release 654,707 0.2 46,417.12 70.9 2.10 70.36 48.51 92.21 30.90 2 Concurrenc enq: TX – index contention 351,843 0.0 45,980.36 130.7 2.08 129.05 97.22 160.89 45.02 2 Concurrenc latch: cache buffers chains 12,476,971 0.0 33,659.74 2.7 1.52 2.92 2.61 3.22 0.43 2
As per “Analyzing ‘enq: HW – contention’ Wait Event (Doc ID 740075.1)”, this can be generated by multiple sessions fighting each other allocating space.
Lets see what object was generating this issue:
SQL> SELECT event_id, name, parameter1, parameter2, parameter3 FROM v$event_name WHERE name = ‘enq: HW – contention’; EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 ———- ————————– —————– ——————– ————- 1645217925 enq: HW – contention name|mode table space # block SQL> SELECT event, p1, p2, p3, Count(1) FROM dba_hist_active_sess_history WHERE event_id = 1645217925 GROUP BY event, p1, p2, p3 order by 5; EVENT P1 P2 P3 COUNT(1) —————————— ———- ———- ———- ———- … enq: HW – contention 1213661190 17 25241170 39 enq: HW – contention 1213661190 22 448829394 51 enq: HW – contention 1213661190 17 349234098 52 enq: HW – contention 1213661190 17 25330626 127 enq: HW – contention 1213661190 0 4194432 282 enq: HW – contention 1213661190 17 1677529874 438 enq: HW – contention 1213661190 17 1520175170 777 enq: HW – contention 1213661190 17 1660762946 897 enq: HW – contention 1213661190 16 4278533507 1027 enq: HW – contention 1213661190 17 156612530 2822 enq: HW – contention 1213661190 19 234750090 18554 enq: HW – contention 1213661190 17 349104930 255525 <<<--------- 715 rows selected. SQL> What file/block? SQL> SELECT dbms_utility.Data_block_address_file(349104930) FILE#, dbms_utility.Data_block_address_block(349104930) BLOCK# FROM dual; FILE# BLOCK# ———- ———- 83 977698 SELECT owner, segment_type, segment_name, PARTITION_NAME FROM dba_extents WHERE file_id = 83 AND 977698 BETWEEN block_id AND block_id + blocks – 1; OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME —————— —————— ———————— —————————— TEST_USER TABLE PARTITION TABLE_AFFECTED SYS_P1871
So problem is generated by Partition SYS_P1871.
Lets see the extents allocated to this partition:
col OWNER format a10 col PARTITION_NAME format a20 col TABLESPACE_NAME format a20 select OWNER, SEGMENT_NAME, PARTITION_NAME, TABLESPACE_NAME,BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_SIZE from dba_segments where segment_name =’DETB_RTL_TELLER’ and PARTITION_NAME = ‘SYS_P1871’ ; OWNER SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_SIZE ———- ——————– —————- —————- ———- ———- ———- ————– ———– ———– ———- TEST_USER TABLE_AFFECTED SYS_P1871 USERS 9096396800 1110400 1735 5242880 5242880 1 2147483645 We can see this partition has many extents across many datafiles: SQL> SELECT owner, segment_name, PARTITION_NAME, tablespace_name, file_id, block_id AS “FROM BLOCK”, block_id+blocks AS “TO BLOCK” FROM dba_extents WHERE segment_name = ‘TABLE_AFFECTED’ AND owner = ‘TEST_USER’ and PARTITION_NAME = ‘SYS_P1871’; OWNER SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME FILE_ID FROM BLOCK TO BLOCK ———- ——————– ——————– ——————– ———- ———- ———- … TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5325 35848 36488 TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5331 42888 43528 TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5334 43528 44168 TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5340 29448 30088 TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5346 42248 42888 TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5353 13448 14088 TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5354 23048 23688 TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5360 29448 30088 TEST_USER TABLE_AFFECTED SYS_P1871 USERS 5365 44808 45448 1735 rows selected. SQL>
How to fix? We can preallocate extents so sessions are not waiting for the space to be allocated.
We have 2 options:
1:- Preallocate across all datafiles: SQL> ALTER TABLE TEST_USER.TABLE_AFFECTED MODIFY PARTITION SYS_P1871 ALLOCATE EXTENT (size 100M ); Table altered. SQL> 2:- Preallocate on a specific datafile: SQL> select FILE_ID, FILE_NAME, autoextensible, bytes/1024/1024/1024 as “GB” , maxbytes/1024/1024/1024 as “Max GB” from dba_data_files where FILE_ID = 5365; FILE_ID FILE_NAME AUT GB Max GB ———- ———————————————————– — ———- ———- 5365 +DATAFC01/PROD_DB/datafile/USERS.5791.1074924187 YES 10 31.9999847 SQL> ALTER TABLE TEST_USER.TABLE_AFFECTED MODIFY PARTITION SYS_P1871 ALLOCATE EXTENT (size 100M DATAFILE ‘+DATAFC01/PROD_DB/datafile/USERS.5791.1074924187’); Table altered. SQL>
After this change, wait event is gone:
Combat with an enqueue wait event (enq: HW–Contention)
Recently I’ve been combating a high water mark enqueue wait (enq: HW – contention) on a single node within an Exadata I’m supporting. I first noticed the wait when I was looking at the performance page for the node in Oracle Enterprise Manager 12c. What I noticed was the a lot of brown looking spikes (Image 1). These spikes correspond to a Configuration wait.
Image 1:
When I clicked on Configuration in the legend on the side of the graph, I’m taken to the Active Session Waiting: Configuration (Image 2) page. On this page, I can clearly see that the wait event I’m waiting on is the “enq: HW – contention” wait.
Image 2:
Now, that I know what wait event I’m waiting on, I needed to figure out what object was causing the wait. Everyone has a different approach to how they performance tune; I find it easier to identify objects causing the wait by using the Active Session History (ASH) report. ASH reports can be ran directly from the OEM Active Session Waiting page (page with the graphs) or from the command line. Since I like the pretty graphs that OEM provides, I prefer to run ASH from the command line and use the graphs to help identify what direction my tuning is going.
Active Session History (ASH) Report
The Active Session History (ASH) report is handy in helping to identify what object(s) the wait is waiting on. In order to run the ASH Report from the command line, there are a few requirements needed. They are:
Set ORACLE_HOME
Move to the directory where you would like to save the report
Once these requirements have been meet, the ASH Report can be ran from the command line using the ashrpt.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sh
When the script starts to run, it will ask for input on what format you would like the report in. For search ability purposes (from command line), I go with the “Text” option. Next, it asks for a timeframe (Image 3); provide a timeframe as per the layout listed.
Image 3:
Lastly, it will ask you what you would like to name the report. I normally prefix it with my initials and an underscore (bc_) (Image 4).
Image 4:
Once I press enter, the report runs and the output will be stored in the directory you started SQL*Plus from.
Reading the ASH Report
In reading the ASH Report, I want to identify the primary object that is holding the “enq: HW – contention” wait. In order to do this, I need to look at the “Top Event P1/P2/P3 Values” section of the report (Image 5). Reviewing this section, I see that the Top Event is the “enq: HW – contention”, the same as I saw from the OEM performance pages. Next, I need to find the values of P2 and P3.
Image 5:
The values for P2 and P3 will tell me what data file (P2) to look at for the block (P3) holding the wait. The values P2 & P3 for the wait are currently “85” and “16018”.
Finding the Object
Armed with the values for P2 and P3 from the ASH Report, I can now go and locate the object via SQL. The SQL used to identify the object is as follows:
select /*+ parallel(a dba_extents, 4) */ a.owner, a.segment_type,a.segment_name
from DBA_EXTENTS a
where a.FILE_ID = &P2
and &P3 between a.block_id and a.block_id + blocks – 1;
Note: I’m using a parallel hint; this is to speed of the identification of the block. Can help in VLDBs.
Once the script is done running, it returns the owner, segment_type, and segment_name of the object holding the enq: HW – contention wait (Image 6). In this case, the wait is on a system generated LOB index.
Image 6:
Resolving the Wait
At this point, I have identified the following:
The wait: enq: HW – contention
The object holding the wait
Next, I want to resolve the wait as fast as I can. Knowing that the wait is a high watermark (enq: HW –contention) wait event, there needs to be an basic understanding of high watermarks and how extents are allocated. The basic reason why the ‘enq: HW – contention’ raises is because the additional extents are not being allocated to in a timely manner for the data being inserted into the object.
In order to resolve this wait, additional extents need to be allocated. The environment I’m working in is using big file data files with automatic segment space management (ASSM). When initially looking into how to increase the extents; there are many different ways. Being that this database is using ASSM, makes the extent allocation simpler. To allocate another extent for the object identified, use the following:
SQL> alter index
.SYS_IL0000253792C00002$$ allocate extent; The above alter statement is correct; however, the problem is that I was trying to allocate an extent on a system generated index. The below error message states that this is not allowed:
Error starting at line : 32 in command –
alter index
.SYS_IL0000253792C00002$$ allocate extent Error report –
SQL Error: ORA-22864: cannot ALTER or DROP LOB indexes
22864. 00000 – “cannot ALTER or DROP LOB indexes”
*Cause: An attempt was made to ALTER or DROP a LOB index.
*Action: Do not operate directly on the system-defined LOB index.
Perform operations on the corresponding LOB column.
In order to add extents, I needed to identify the object that is using the system generated LOB index. This inform
ation can be found in DBA_LOBS (SQL below).
select owner, table_name, column_name, segment_name, index_name
from DBA_LOBS
where index_name = ‘SYS_IL0000253792C00002$$’;
The SQL returns the name of the owner, table_name and LOB column within the table that is using the system generated LOB index (Image 7). Using this information, I can now allocate additional extents to the LOB.
Image 7:
In order to allocate an extent on the object identified, the following was used:
alter table
. modify lob(
) (allocate extent (size 5M)); Once this completed, I was expecting wait (enq: HW – contention) to go away. After sometime, I saw no improvement. This lead me to open an service request (SR) with Oracle Support in trying to resolve.
Research
Opening the SR has lead me to dig a bit and do some research on extent allocation and LOBs while waiting on Oracle’s answer. Prior to Oracle Database 11g (11.2.0.1), there is a known bug (6376915). This bug is related to high watermark enqueue contention for ASSM LOB segments (Note ID: 6376915.8). This bug was actually fixed in release 11.2.0.1 but it needs to be “turned on” in later releases. To turn the fix for this bug on in 11.2.0.2+, an event needs to be set in the spfile.
SQL> ALTER SYSTEM SET EVENT=”44951 TRACE NAME CONTEXT FOREVER, LEVEL <1 .. 1024>” scope=spfile ;
By setting this event between 1 and 1024 will identify the number of chunks to be cleaned up each time a reclamation operation is performed. In turn this reduces the number of requests against the high watermark enqueue.
Once the event has been set in the spfile, the database instance needs to be rebooted to make the change effective.
Conclusion
The steps in resolving this enqueue event are not difficult; however, when extents need to be allocated manually it becomes very interesting. The downside to setting the event, per Oracle Support, is the need to reboot the database instance to make the event active.
Enjoy!
twitter: @dbasolved
blog: http://dbasolved.com
enq: HW – contention
Enqueues are shared memory structures that serialize access to database resources.(locks) They can be associated with a session or transaction.
HW (high water mark) is the bound between used and unused areas in the segment.
If AWR reports show a high number of “enq: HW – contention” pending events, the issue needs to be fixed. It is generally seen in insert operations performed on tables containing lob fields in the form of basicfile.
To correct the problem, the lob field in the corresponding table must be changed from basicfile to securefile.
The following steps can be followed to identify the problematic objects.
First of all, we detect event ids related to “enq: HW – contention”.
SQL> SELECT event_id, name, parameter1, parameter2, parameter3 FROM v$event_name WHERE name = ‘enq: HW – contention’; EVENT_ID NAME PARAMETER PARAMETER PARAMETER3 ———- ———————- ——————- ——————- ——————- 1645217925 enq: HW – contention name|mode table space # block 1 2 3 4 5 6 7 8 9 10 11 SQL > SELECT event_id , name , parameter1 , parameter2 , parameter3 FROM v $ event_name WHERE name = ‘enq: HW – contention’ ; EVENT_ID NAME PARAMETER PARAMETER PARAMETER3 — — — — — — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — – — — — — — — — — — – 1645217925 enq : HW – contention name | mode table space # block
Then, from the dba_hist_active_sess_history view, we determine the necessary information for the detection of file and block information about the event id.
SQL> SELECT event, p1, p2, p3, COUNT (1) FROM dba_hist_active_sess_history WHERE event_id = 1645217925 GROUP BY event, p1, p2, p3; 2 3 4 5 6 7 8 9 10 11 EVENT P1 P2 P3 COUNT(1) —————————————————————- ———- ———- ———- ———- enq: HW – contention 1213661190 25 17761024 1 enq: HW – contention 1213661190 0 4294200 61 enq: HW – contention 1213661190 38 853351013 118356 enq: HW – contention 1213661190 38 373735013 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SQL > SELECT event , p1 , p2 , p3 , COUNT ( 1 ) FROM dba_hist_active_sess_history WHERE event_id = 1645217925 GROUP BY event , p1 , p2 , p3 ; 2 3 4 5 6 7 8 9 10 11 EVENT P1 P2 P3 COUNT ( 1 ) — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — enq : HW – contention 1213661190 25 17761024 1 enq : HW – contention 1213661190 0 4294200 61 enq : HW – contention 1213661190 38 853351013 118356 enq : HW – contention 1213661190 38 373735013 1
With this information, the file and block information is detected with the dbms_utility package. The information used in the package is the information in the P3 field in the query above. The lob segment, which has too many waits, needs to be identified.
SQL> SELECT DBMS_UTILITY.Data_block_address_file (853351013) FILE#, DBMS_UTILITY.Data_block_address_block (853351013) BLOCK# FROM DUAL; 2 3 FILE# BLOCK# ———- ———- 203 1907301 1 2 3 4 5 6 7 SQL > SELECT DBMS_UTILITY . Data_block_address_file ( 853351013 ) FILE #, DBMS_UTILITY . Data_block_address_block ( 853351013 ) BLOCK # FROM DUAL ; 2 3 FILE # BLOCK# — — — — — — — — — — 203 1907301
We identify the lob segment with datafile and block information.
SQL> SELECT owner, segment_type, segment_name 2 FROM dba_extents 3 WHERE file_id = 203 AND 1907301 BETWEEN block_id AND block_id + blocks – 1; OWNER SEGMENT_TYPE SEGMENT_NAME —————————— —————— ——————————————————————————— ADURUOZ LOB PARTITION SYS_LOB0000291772C00006$ 1 2 3 4 5 6 7 SQL > SELECT owner , segment_type , segment _ name 2 FROM dba _ extents 3 WHERE file_id = 203 AND 1907301 BETWEEN block_id AND block_id + blocks – 1 ; OWNER SEGMENT_TYPE SEGMENT_NAME — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — – ADURUOZ LOB PARTITION SYS_LOB0000291772C00006 $
We find the table where the detected lob segment belongs from the dba_lobs image .
SQL> SELECT owner, table_name, column_name, chunk, securefile FROM dba_lobs WHERE segment_name = ‘SYS_LOB0000291772C00006$’; OWNER TABLE_NAME COLUMN_NAME CHUNK SEC —————- ————— ————— ————— ————— ADURUOZ TESTTABLE DOSYA 8192 NO 1 2 3 4 5 6 7 8 9 10 11 SQL > SELECT owner , table_name , column_name , chunk , securefile FROM dba_lobs WHERE segment_name = ‘SYS_LOB0000291772C00006$’ ; OWNER TABLE_NAME COLUMN_NAME CHUNK SEC — — — — — — — — — — — — — — — – — — — — — — — – — — — — — — — – — — — — — — — – ADURUOZ TESTTABLE DOSYA 8192 NO
From the output of the above query, the relevant table is determined and it is seen that the lob field of this table is not securefile.
The main reason for this wait event is that the lob field is a basic file. To correct the problem, the corresponding lob field must be changed to securefile.
enq HW-contention analysis
1. Perform performance pressure test to achieve quasi real-time synchronization of data from mysql to oracle. MySQL uses sub-database and table, and oracle is a traditional rac architecture. The source side uses the full initialization + incremental analysis binlog method to ensure data synchronization. Previously, Oracle goldengate was used to synchronize data, but goldengate does not support mysql xa transactions, so other synchronization tools are tested to solve the problem of mysql to oracle data synchronization (details will be described later) ). The performance pressure test of the synchronization tool was carried out as scheduled, but the alarm information of the number of database connections was received, which was not in accordance with common sense. After inspection, it was found that enq:HW-contention accounted for nearly 60% of the database waiting events.
2. enq: HW-contention official explanation
If you encounter a waiting event, you can get the general meaning by querying the official. The waiting event belongs to enq from the classification point of view. This is a memory structure lock and a serial.
Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction.
The HW enqueue is used to manage the allocation of space beyond the high water mark of a segment. The high water mark of a segment is the boundary between used and unused space in that segment. If contention is occurring for “enq: HW – contention ”It is possible that automatic extension is occuring to allow the extra data to be stored since the High Water Mark has been reached. Frequent allocation of extents, reclaiming chunks, and sometimes poor I/O performance may be causing contention for the LOB segments high water mark.
According to official documents, the significance of Oracle’s design of HW-contention queue is that for database resources, the concept of “lock” is needed to protect the memory structure to control concurrency. When the application exceeds the high water mark space, in order to prevent multiple processes from modifying HWM at the same time The provided locks are called HW locks. The process that wants to move the HWM must obtain the HW lock, but the acquisition of the HW lock is still an exclusive lock, because an exclusive lock is added to ensure that the structure is not destroyed. When requesting to expand the space outside the high water mark of the segment, it is necessary to obtain an application lock.
Three: enq:HW-contention problem analysis
Approximate implementation of synchronization tools:
According to the explanation of the previous waiting event, after we are backlog of data, multiple tables at the source end correspond to a table at the target end, similar to A001 writing A to A031 writing A, theoretically when concurrent writing, the corresponding process space is not enough. The high water mark is required. HW exclusive locks need to be applied for when there is more space, otherwise concurrent writing will not be affected, unless each process must be forced to write data above the high water mark, so that active locking under large concurrency will become a foul operation and corresponding blockage will occur. operating.
After analyzing the performance test tool, the space written every time is at the high water mark (using append hint to force), which causes the need for an exclusive lock every time to block other delete, update, and insert on this table.
1. This method is not suitable for concurrent write processes or when there are other businesses in the table
2. For systems that run batches regularly in the early morning, the append method is used to increase the speed of large batch writes.
HW appearance scene:
1. HW caused by large concurrent inserts
2. Use APPEND method
3. The table has a LOB field
Specific processing ideas:
1. Analyze the number of occurrences of this waiting event through ash, and the number of waiting times for a single node within 1 hour is as high as 6000 times. The actual environment is more serious. The waiting time is 2s each time.
select instance_num,p1,p2,p3,count(*) from dba_hist_active_sess_history t
where event=’enq: HW-contention’ and t.sample_time>=timestamp’2019-07-12 15:00:00′ and t.sample_time<=timestamp'2019-07-12 16:00:00' group by instance_num,p1,p2,p3; P1 can be converted to request lock modes, which are all 6, and 6 is an exclusive lock. 4857 is the ASCILL name of HW. Where p1, p2, p3 explain: P1 = name|mode P2 = tablespace# P3 = block (RDBA) (RDBA) name|mode The lock name and requested mode are encoded into P1 as " name <<16 | mode ". This is best seen in P1RAW (or you can convert P1 to hexadecimal). For this wait: name is always the ASCII for "HW" = 0x4857 mode is the mode that the HW lock is being requested in. In most cases mode will be either mode 4 or mode 6: " 6 " = e X clusive mode TX wait. " = e clusive mode TX wait. " 4 " = S hared mode TX wait. eg: P1RAW of 48570006 = eXclusive mode HW wait, P1RAW of 48570004 = Shared mode HW wait. 2. Locate the specific object-this is not intuitive, it is better to query specific SQL Use P3 to locate the specific contention target SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(425098194) RELFILE#, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(425098194) BLOCK# FROM dual; FILE# BLOCK# 101 1473490 Locate specific OBJECT according to FILE# and BLOCK# select OWNER,SEGMENT_NAME from dba_extents where FILE_ID =101 and 1473490 between block_id and block_id + block_id -1; OWNER SEGMENT_NAME; PIPELINE T_XXX_TASK_SHIPMENT 3. Obtain specific SQL and cause analysis The complete SQL is not displayed. It can be seen that the APPEND method is used. The append uses the space above the high water mark. According to the previous P1, it is known to wait All need to obtain exclusive lock level 6. Large concurrency causes system performance problems, mainly due to the append method. The database em displays the waiting status as follows: summary: Through analysis, the synchronization tool adopts the append method and multiple processes operate on the same table, which leads to serious HW waiting. Each waiting time is 2s. The total waiting time is 107755s, and the total number of waiting times in one hour is: 107755/2=50000 times. At the same time, use more than 85% of the total number of connections (2000 sessions per node*4=8000*85%=6800). The solution is to remove the append method. 4: Reasons and benefits of adopting MERGE MERGE official description: Purpose Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements. MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement. Note: Oracle Database does not implement fine-grained access control during MERGE statements. If you are using the fine-grained access control feature on the target table or tables, then use equivalent INSERT and UPDATE statements instead of MERGE to avoid error messages and to ensure correct access control. Note: From the above point of view, the same row cannot be updated multiple times, so the merge method must be deduplicated first, and at the same time non-fine-grained access to the data, if you need to fine-grained access to the table, you cannot use the merge method. The synchronization tool database log is parsed into a message and written to KAFKA, and then written from KAFKA to the ORACLE database temporary table (for rows that have been updated multiple times, take the last row to avoid merge errors), by setting the write batch size and time threshold Come as the end point, and then merge the temporary table with the original table to realize the update and insert operations. Realize high-efficiency performance through batches. For the delete operation, go to normal operation. MERGE advantage description: The merge method shields the difference between update and insert, and realizes one pass processing data. Merge can be combined with parallel to speed up processing. Because batch matching reduces the number of scans of the original table to improve performance Five: HW related BUG Note : Each performance problem does not involve BUG. Known Issues/Bugs: You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
Oracle database internals by Riyaj
Recently, I had few email exchanges about HW enqueue contention in oracle-l list and offline. There were few interesting observations emerged from test cases created during that discussion.
HW enqueue
When a session needs access to a resource, it requests a lock on that resource in a specific mode. Internally, lock and resource structures are used to control access to a resource. Enqueues, as name suggests, have First In First Out queueing mechanism. You can find more information about internals of locks in my paper [printed in 2001 ]Internals of locks
Segments have High Water Mark (HWM) indicating that blocks below that HWM have been formatted. New tables or truncated tables [ that is truncated without reuse storage clause ], have HWM value set to segment header block. Meaning, there are zero blocks below HWM. As new rows inserted or existing rows updated (increasing row length), more blocks are added to the free lists and HWM bumped up to reflect these new blocks. HW enqueues are acquired in Exclusive mode before updating HWM and essentially HW enqueues operate as a serializing mechanism for HWM updates.
In non-ASSM tablespaces, HWM is bumped up by 5 blocks at a time ( Actually, undocumented parameter _bump_highwater_mark_count controls this behavior and defaults to 5). Heavy inserts in to a table can result in increased HWM activity leading to HW enqueue contention. This issue is prevalent if the table has LOB columns or if the row length is big.
Measuring HW enqueue contention
We will use few test cases to see how underlying extent size and table structures are affecting HW enqueue contention. But, before we need to find a way to measure total number of gets on HW enqueue. If total number of gets on HW enqueue is reduced, enqueue contention can be relieved.
Fixed table x$ksqst stores statistics about total number of enqueue gets, success and failures of those gets at instance level. For example, to see total number of gets on HW enqueue, following query can be used. Column ksqstreq indicates total # of gets and ksqstwat shows total # of waits.
SQL> select ksqstreq, ksqstwat from x$ksqst where ksqsttyp=’HW’; KSQSTREQ KSQSTWAT ———- ———- 546953 50
From Oracle version 10g and above, x$ksqst is externalized as v$enqueue_statistics.
However, this statistics shows activity at instance level. While we can use this statistics to measure HW enqueue activity , we need to make sure that there is no other session acquiring HW enqueue. Event 10704 can be used to trace enqueues and every call to get an enqueue prints few lines in the trace file. SQL statement to dump this information to trace file is :
alter session set events ‘10704 trace name context forever, level 15’;
Event 10704 is documented as below:
10704, 00000, “Print out information about what enqueues are being obtained” // *Cause: When enabled, prints out arguments to calls to ksqcmi and // ksqlrl and the return values. // *Action: Level indicates details: // Level: 1-4: print out basic info for ksqlrl, ksqcmi // 5-9: also print out stuff in callbacks: ksqlac, ksqlop // 10+: also print out time for each line
Few lines from the trace files printed below. ksq is internal Oracle module names for enqueues and ksqgtl is to get locks on a resource. From the lines below, we can see that HW enqueue is acquired in mode 6, exclusive mode. Timestamp is also printed since we enabled this event at level 15. If we need count number of HW enqueue gets, we need to count occurrences of HW- string in the trace file.
*** 2008-05-04 10:08:35.734 ksqgtl *** HW-00000007-01800014 mode=6 flags=0x11 timeout=21474836 *** ksqgtl: xcb=0x1E283158, ktcdix=2147483647, topxcb=0x1E283158 ktcipt(topxcb)=0x0 *** 2008-05-04 10:08:35.734 ksucti: init session DID from txn DID: ksqgtl: ksqlkdid: 0001-0014-00000016 *** 2008-05-04 10:08:35.734 *** ksudidTrace: ksqgtl ktcmydid(): 0001-0014-00000016 ksusesdi: 0000-0000-00000000 ksusetxn: 0001-0014-00000016 ksqgtl: RETURNS 0 *** 2008-05-04 10:08:35.750 ksqrcl: HW,7,1800014 ksqrcl: returns 0
Now, we can grep for HW- in the trace file, count it and match that against v$enqueue_statistics. Following test case illustrates this method using an example table below:
SQL> select ksqstreq, ksqstwat from x$ksqst where ksqsttyp=’HW’; KSQSTREQ KSQSTWAT ———- ———- 546198 50 SQL> insert into test_hw select n, lpad(n, 4000,’a’) v1 from (select level n from dual connect by level SQL> SQL> commit; Commit complete. SQL> select ksqstreq, ksqstwat from x$ksqst where ksqsttyp=’HW’; KSQSTREQ KSQSTWAT ———- ———- 546953 50
A difference of 755 (546953-546198) total gets to HW enqueue.
Searching for HW- enqueues in the trace file also prints 755.
/oracle/app/oracle/admin/TEST1/udump> grep ‘HW-‘ test1_ora_26668.trc |wc -l
755
So, in my test database, my session is the only session and we could query v$enqueue_statistics for our test cases.
Test case
Following test case will be used to see the impact of extent size, segment management on HW enqueue gets. If total number of HW enqueue gets can be reduced, contention can be relieved.
Following script creates a tablespace, then creates a table with lob column in that tablespace, inserts 9999 rows in to that table and prints difference in total enqueue gets before and after insert statement.
— Script enq_hw1.sql ———– variable v_begin_cnt number variable v_end_cnt number prompt prompt Tablespace: Locally managed with \&1 prompt drop tablespace TS_LMT_HW including contents and datafiles; create tablespace TS_LMT_HW datafile ‘D:\ORACLE\ORADATA\ORCL11G\TS_LMT_AUTO_1M_01.DBF’ size 200M extent management local \&1; create table test_hw (n1 number , c1 clob ) tablespace TS_LMT_HW; begin select total_req# into :v_begin_cnt from v$enqueue_statistics where eq_type =’HW’; end; / insert into test_hw select n, lpad(n, 4000,’a’) v1 from (select level n from dual connect by level <10000); commit; select total_req#, succ_req#, failed_req# from v$enqueue_statistics where eq_type ='HW'; begin select total_req# into :v_end_cnt from v$enqueue_statistics where eq_type ='HW'; end; / select :v_end_cnt - :v_begin_cnt diff from dual; --- script end enq_hw1.sql ----------- Above script is called by following script passing various tablespace attributes, essentially running same test for different types of tablespaces: spool call_eng_hw1.lst REM Test cases #1 through #6 @enq_hw1 "uniform size 5M segment space management manual" @enq_hw1 "uniform size 5M segment space management auto" @enq_hw1 "uniform size 40K segment space management manual" @enq_hw1 "uniform size 40K segment space management auto" @enq_hw1 "autoallocate segment space management manual" @enq_hw1 "autoallocate segment space management auto" spool off Test Results Tested above script for tablespace in versions 10.2.0.4 and 11.1.0.6. Here are the test results: test Extent management Segment space management 10.2.0.4 11.1.0.6 #1 uniform size 5M manual 2049 64 #2 uniform size 5M auto 50 48 #3 uniform size 40K manual 6604 6045 #4 uniform size 40K auto 7552 7554 #5 autoallocate manual 2231 279 #6 autoallocate auto 269 291 There are few key points here. 1. In test cases #1 and #5, there is a dramatic decrease in enqueue gets between 10g and 11g for tablespace with segment space management set to manual. Looks like, a new feature faster lob is kicking in. Tested above script for a table without lob column. Virtually there is no difference between 10g and 11g, if the table has no lob column. row length is kept around 3000 bytes for this test. create table test_hw (n1 number , v1 varchar2(1000), v2 varchar2(1000), v3 varchar2(1000) ) tablespace TS_LMT_HW; ... declare i number; begin for i in 1 .. 10000 loop insert into test_hw values ( i, lpad(i, 1000, 'a'), lpad(i, 1000, 'a'), lpad(i, 1000, 'a') ); commit; end loop; end; / ... test Extent management Segment space management 10.2.0.4 11.1.0.6 #1 uniform size 5M manual 1020 1019 #2 uniform size 5M auto 33 31 #3 uniform size 40K manual 3004 3004 #4 uniform size 40K auto 3055 3055 #5 autoallocate manual 1132 1132 #6 autoallocate auto 163 164 2. If tablespace uniform size is too low, then # of HW enqueue gets increases sharply. Compare test cases #1 & #3 for 11g. Enqueue gets decreased 10 times for tablespace with appropriate extent size. 3. In all test cases, automatic segment space management tablespaces performed fewer enqueue gets. In ASSM tablespaces HWM is bumped up by much higher number. I am not suggesting to use ASSM, since space wastage *can* increase in ASSM tablespaces. However, if resolving HW contention is primary issue, then consider ASSM. 4. Allocating additional extent with instance keyword seems to help in non-ASSM tablespace [ Behavior is different for ASSM tablespace and needs more research to understand that ]. In test case #1 above, HWM for the table was set at file_id 6, block_id 80 and that extent starting at file 6, block_id 9. That is, 70 blocks were below HWM initially. After allocating an extent with instance 1 keyword, HWM was bumped up by 1209 blocks. [ Yong has a test case for this using dbms_space package: http://yong321.freeshell.org/oranotes/Dbms_Space.txt ] From segment header block, before allocating extent, HWM: 0x01800050 (dba for file 6,block 80) alter table test_hw allocate extent (instance 1); From segment header block, after allocating extent, HWM: 0x01802f89 (dba for file 6,block 12169) Extent Map —————————————————————– 0x0180000a length: 639 0x01802d09 length: 640 5. High HW enqueue contention is prevalent during Oracle applications upgrade also. During one of our recent upgrade from 11.5.8 to 11.5.0, there was heavy HW enqueue contention on sys.source$ table due to many packages compiled/added during the upgrade. Only option was to increase _bump_highwater_mark_count during upgrade, to relieve excessive waits on HW enqueue contention. Of course, Oracle support must be contacted before adding any underscore parameters in production environment. Side effect of setting this parameter is that, for smaller tables, more blocks might be added below HWM leading to full table scan reading more blocks then necessary.
키워드에 대한 정보 enq hw contention
다음은 Bing에서 enq hw contention 주제에 대한 검색 결과입니다. 필요한 경우 더 읽을 수 있습니다.
이 기사는 인터넷의 다양한 출처에서 편집되었습니다. 이 기사가 유용했기를 바랍니다. 이 기사가 유용하다고 생각되면 공유하십시오. 매우 감사합니다!
사람들이 주제에 대해 자주 검색하는 키워드 How Oracle Determines Wait Time When It Releases Control
- oracle database 12c
- oracle wait time
- oracle tuning
- oracle time based analysis wait time
- Oracle Database (Software)
How #Oracle #Determines #Wait #Time #When #It #Releases #Control
YouTube에서 enq hw contention 주제의 다른 동영상 보기
주제에 대한 기사를 시청해 주셔서 감사합니다 How Oracle Determines Wait Time When It Releases Control | enq hw contention, 이 기사가 유용하다고 생각되면 공유하십시오, 매우 감사합니다.
