Hello,
We are observed high enq: TX - index contention yesterday.
DB Version 11.2.0.3.7 (PSU 7 applied)
Top 5 events (AWR for 15 minutes during problem period):
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: TX - index contention 2,081 95,380 45834 74.3 Concurrenc
db file sequential read 640,143 8,200 13 6.4 User I/O
enq: UL - contention 26,090 8,039 308 6.3 Applicatio
DB CPU 6,758 5.3
enq: TX - row lock contention 82 5,592 68195 4.4 Applicatio
Segment by row lock waits:
{noformat}
| Segments by Row Lock Waits | DB/Inst: ORCL/orcl Snaps: 65089-65090 |
-> % of Capture shows % of row lock waits for each top segment compared
-> with total row lock waits for all segments captured by the Snapshot
| | Row |
| | Tablespace | Subobject Obj. | Lock | % of |
| Owner | Name | Object Name | Name | Type | Waits Capture |
---------- ---------- -------------------- ---------- ----- ------------ -------
| SEC_USER | SEC_USER | SEC$_ASYNCH_QUEUE | TABLE | 2,198 43.40 |
| APP_USER | USERS | SUPPORT_STATUS_IDX | INDEX | 1,598 31.55 |
| APP_USER | APP_USER_DATA TRF_STATE_WTIME_IDX | INDEX | 256 | 5.05 |
| APP_USER | USERS | APP$_TRANSFER | TABLE | 248 | 4.90 |
| APP_USER | USERS | APP$_QUEUE_DD | TABLE | 210 | 4.15 |
{noformat}
Wait event histogram (4 sec to 2 min):
{noformat}
| | % of Total Waits |
| | ----------------------------------------------- |
| | Waits |
| | 4s |
| Event | to 2m <2s <4s <8s <16s <32s < 1m < 2m >=2m |
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
| SQL*Net message from dblin 208 99.8 | .2 | .0 | .0 |
| enq: TX - index contention 356 66.5 | .7 1.2 2.0 2.1 3.8 7.3 16.4 |
| enq: TX - row lock content | 13 57.3 | 6.1 | 9.8 26.8 |
| enq: UL - contention | 389 98.5 | .7 | .5 | .2 | .1 | .0 | .0 | .0 |
{noformat}
SUPPORT_STATUS_IDX - index on status column and had next value distribution:
15:35:29 SQL> select /*+ index_ffs(s) parallel_index(s 8)*/status, count(1) from t s where status is not null group by status
16:07:19 2 /
ST COUNT(1)
-- ----------
D 122521622
X 10092
S 100
O 7
E 13111287
Index split statistics:
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
| branch node splits | 29 | 0.0 | 0.0 |
| leaf node 90-10 splits | 1,697 | 1.9 | 0.0 |
| leaf node splits | 7,490 | 8.3 | 0.0 |
| root node splits | 0 | 0.0 | 0.0 |
| failed probes on index block rec | 342 | 0.4 | 0.0 |
Oracle Support pointed to note:
And suggests to rebuild SUPPORT_STATUS_IDX as reverse.
I understand benefit of this recommendation for sequence based indexes, but cannot figure out how can reverse key index can help for index with 4 distinct values on status based columns?
Looks like a strange recommendation for me.
Would like ask a community opinion about such recommendation.