在给客户分析问题时，有个时段DB Time很高，这是一套12.2.0.1的RAC
TOP EVENT为enq: TX - index contention,这个等待一般是索引单向增长，分裂导致，但同时可以看到平均等待达到30s，可能并没有表面上那么简单
通过ASH分析等待链，如下
1、
->[JDBC Thin Client](enq: TX - index contention)->[JDBC Thin Client](L1 validation)
2、
->[JDBC Thin Client](enq: TX - index contention)->[JDBC Thin Client](gc buffer busy release)
3、
->[JDBC Thin Client](enq: TX - index contention)->[JDBC Thin Client](ON CPU)
当前只拿到这些数据，初步分析如下
Root blocker，等待(L1 validation),在ASSM管理的段中查找FREE BLOCK(L1块,FREE BLOCK), 这个不常见
这里考虑为特殊场景，或BUG可能
MOS 上查找如下匹配文档，产生争用的INDEX并不大，争用这么严重大概率考虑为BUG。
Bug 27986817 - L1 Validation Waits With High Concurrent Workload (Doc ID 27986817.8)
但后来拿到alert log（在这之前客户一直没给alert log)，看到在问题时间有表空间不足的报错，并且与问题时段相同，从这看这不是一个巧合。
第一时间看alert log很重要！！！
2020-04-13T09:22:58.663945+08:00
XXXDB(5):ORA-1654: unable to extend index XXXLOG.IND_TABLE_OPERATE_DATE_I by 8192 in tablespace XXXLOG [XXXDB]
此时推测为，索引所在的表空间满，一直没有办法扩展EXTENT，大量进程并发产生了争用。
后来进行了测试验证，测试过程如下
1、创建测试的表空间，表及索引。表空间只有5M，很快就不够用了
SQL> create tablespace test datafile size 5m autoextend off;
Tablespace created.
SQL> create table dbmt.test1(c1 varchar2(100),c2 varchar2(100)) tablespace users ;
Table created
SQL> create index dbmt.idx_test1 on dbmt.test1(c1) tablespace test;
Index created.
2、Python角本200 process并发执行SQL
insert into dbmt.test1 values ('test1','test2');
commit;
3、观察active session,看到大量wait enq: TX - index contention,少量wait L1 validation
SQL> select /*+ordered rule*/ ses.username, ses.sid, CASE WHEN ses.state != 'WAITING' THEN 'On CPU / runqueue' ELSE event end as event, ses.machine, regexp_substr(ses.module,'[^@]+') module, ses.status, ses.last_call_et --, seq#
2 ,ses.sql_id,wait_time||':'||SECONDS_IN_WAIT wai_secinwait , ROW_WAIT_OBJ# ,substr(sql.sql_text,1,30) sqltext,blocking_instance||':'||blocking_session bs,sql_child_number ch# ,osuser
3 , to_char(sql_exec_id,'xxxxxxxx') hex -- ,sstat.value cpu_value --ltrim(p1raw,'0') p1raw
4 --taddr
5 from v$session ses left join v$sql sql on ses.sql_hash_value = sql.hash_value and sql.child_number=ses.sql_child_number and
6 ses.sql_child_number=sql.child_number where ses.type = 'USER' and ses.status<>'INACTIVE' and ses.sid not in(select sys_context('userenv','sid') from dual) order by last_call_et,SECONDS_IN_WAIT;
select sysdate current_time from dual;
USERNAME SID EVENT MACHINE MODULE STATUS LAST_CALL_ET SQL_ID WAI_SECINW ROW_WAIT_OBJ# SQLTEXT BS CH# OSUSER HEX
----------- ---------- -------------------- ---------- -------------------- -------- ------------ --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ---------
DBMT 660 On CPU / runqueue test python ACTIVE 0 -1:0 0 1:495 root
DBMT 137 On CPU / runqueue test python ACTIVE 0 22:0 3341584 1:495 root
DBMT 18 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102223d
DBMT 19 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:37 0 root 102228e
DBMT 20 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:501 0 root 1022287
DBMT 26 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:416 0 root 102222b
DBMT 28 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:531 0 root 102228c
DBMT 29 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:524 0 root 1022207
DBMT 33 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:307 0 root 1022277
DBMT 36 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 10221c2
DBMT 37 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 0 insert into dbmt.1 values : 0 root 1022228
DBMT 49 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022221
DBMT 133 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022251
DBMT 136 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:926 0 root 102227b
DBMT 139 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:659 0 root 1022290
DBMT 146 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 0 insert into dbmt.1 values : 0 root 10221a3
DBMT 147 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:909 0 root 102226b
DBMT 152 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:913 0 root 1022240
DBMT 158 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:416 0 root 102228d
DBMT 164 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:660 0 root 10221c6
DBMT 172 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:927 0 root 1022234
DBMT 175 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022231
DBMT 180 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:420 0 root 10221c3
DBMT 183 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:419 0 root 1022211
DBMT 262 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022271
DBMT 270 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022283
DBMT 273 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:37 0 root 102226e
DBMT 277 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 0 insert into dbmt.1 values : 0 root 1022292
DBMT 282 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:501 0 root 1022253
DBMT 290 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:784 0 root 1022247
DBMT 297 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102225d
DBMT 378 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:926 0 root 1022281
DBMT 383 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:628 0 root 1022288
DBMT 384 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:175 0 root 102222a
DBMT 385 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:927 0 root 102226c
DBMT 386 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:133 0 root 1022291
DBMT 387 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:416 0 root 1022264
DBMT 388 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:659 0 root 10221c8
DBMT 390 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:880 0 root 1022274
DBMT 393 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:662 0 root 1022261
DBMT 395 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:146 0 root 102227d
DBMT 397 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 10221ea
DBMT 398 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:909 0 root 1022269
DBMT 404 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:913 0 root 1022238
DBMT 410 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:924 0 root 102227a
DBMT 413 buffer busy waits test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:419 0 root 102223e
DBMT 414 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:663 0 root 1022268
DBMT 419 On CPU / runqueue test python ACTIVE 0 f9qav875np46u -1:0 -1 insert into dbmt.1 values : 0 root 1022295
DBMT 427 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:749 0 root 1022220
DBMT 501 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022272
DBMT 504 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:18 0 root 1022244
DBMT 505 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:524 0 root 10221ab
DBMT 511 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:749 0 root 102225f
DBMT 522 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022246
DBMT 523 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102226d
DBMT 524 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 0 insert into dbmt.1 values : 0 root 10221dd
DBMT 527 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:524 0 root 102226f
DBMT 531 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102228a
DBMT 533 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:297 0 root 1022286
DBMT 628 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values : 0 root 1022278
DBMT 633 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:659 0 root 1022258
DBMT 634 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:924 0 root 1022204
DBMT 639 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:405 0 root 102220c
DBMT 640 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:138 0 root 10221b2
DBMT 647 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 0 insert into dbmt.1 values 1:396 0 root 102228b
DBMT 652 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:880 0 root 1022237
DBMT 653 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:427 0 root 102224d
DBMT 658 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102223b
DBMT 659 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 0 insert into dbmt.1 values : 0 root 10221b8
DBMT 662 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022263
DBMT 663 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102225e
DBMT 749 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022289
DBMT 751 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022255
DBMT 753 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:645 0 root 10221ca
DBMT 762 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:37 0 root 10221aa
DBMT 767 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:524 0 root 102223c
DBMT 771 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:18 0 root 1022242
DBMT 774 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:501 0 root 1022273
DBMT 776 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 10221d7
DBMT 783 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102224a
DBMT 788 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:784 0 root 1022201
DBMT 792 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:307 0 root 1022279
DBMT 795 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:749 0 root 102228f
DBMT 797 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022280
DBMT 799 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:262 0 root 1022282
DBMT 802 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:37 0 root 10221bf
DBMT 862 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:420 0 root 102227f
DBMT 879 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:397 0 root 1022209
DBMT 880 On CPU / runqueue test python ACTIVE 0 f9qav875np46u 1:0 3341584 insert into dbmt.1 values : 0 root 1022252
DBMT 884 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:663 0 root 102224b
DBMT 886 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:659 0 root 102222c
DBMT 888 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:659 0 root 10221e9
DBMT 889 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:645 0 root 1022285
DBMT 892 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:645 0 root 1022284
DBMT 897 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:133 0 root 1022275
DBMT 898 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:662 0 root 1022270
DBMT 902 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:146 0 root 102227c
DBMT 909 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022257
DBMT 913 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 10221de
DBMT 915 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:175 0 root 102227e
DBMT 923 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:658 0 root 1022293
DBMT 926 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022215
DBMT 927 L1 validation test python ACTIVE 0 f9qav875np46u 0:0 0 insert into dbmt.1 values : 0 root 102226a
DBMT 929 enq: TX - index cont test python ACTIVE 0 f9qav875np46u 0:0 -1 insert into dbmt.1 values 1:628 0 root 1022294
DBMT 405 L1 validation test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 10220d4
DBMT 396 L1 validation test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:419 0 root 10220c7
DBMT 300 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:784 0 root 1021e76
DBMT 286 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 102216c
DBMT 285 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1022153
DBMT 420 L1 validation test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022181
DBMT 157 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:397 0 root 10220ac
DBMT 515 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 10220b4
DBMT 144 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:660 0 root 102212c
DBMT 58 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:749 0 root 102205b
DBMT 416 On CPU / runqueue test python ACTIVE 1 f9qav875np46u 1:0 3341584 insert into dbmt.1 values : 0 root 10220f9
DBMT 307 L1 validation test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102215d
DBMT 781 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1021ed9
DBMT 784 L1 validation test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1022128
DBMT 877 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:419 0 root 1021f1e
DBMT 885 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:416 0 root 102208d
DBMT 893 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:396 0 root 1022169
DBMT 900 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:405 0 root 10220dd
DBMT 924 L1 validation test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 10220f1
DBMT 421 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:419 0 root 10220fb
DBMT 38 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 10220be
DBMT 52 enq: TX - index cont test python ACTIVE 1 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:784 0 root 1021ffa
DBMT 271 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1021cfe
DBMT 622 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:660 0 root 1021b8e
DBMT 40 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1021e42
DBMT 143 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:419 0 root 1021afe
DBMT 758 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:784 0 root 1021d42
DBMT 27 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1021ab9
DBMT 138 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:660 0 root 1021c44
DBMT 177 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:416 0 root 1021aab
DBMT 529 enq: TX - index cont test python ACTIVE 2 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1021b26
DBMT 304 enq: TX - index cont test python ACTIVE 3 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1021a3f
DBMT 155 On CPU / runqueue test python ACTIVE 3 f9qav875np46u -1:0 3341584 insert into dbmt.1 values 1:419 0 root 10219bb
DBMT 46 enq: TX - index cont test python ACTIVE 3 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1021924
DBMT 30 enq: TX - index cont test python ACTIVE 3 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1021a8f
DBMT 665 enq: TX - index cont test python ACTIVE 3 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:137 0 root 1021905
DBMT 549 enq: TX - index cont test python ACTIVE 3 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 10219eb
DBMT 22 enq: TX - index cont test python ACTIVE 4 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 10216a8
DBMT 422 buffer busy waits test python ACTIVE 4 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:419 0 root 10215f1
DBMT 679 enq: TX - index cont test python ACTIVE 4 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:419 0 root 102182a
DBMT 526 enq: TX - index cont test python ACTIVE 4 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:784 0 root 10218e5
DBMT 649 enq: TX - index cont test python ACTIVE 4 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:645 0 root 102182d
DBMT 261 enq: TX - index cont test python ACTIVE 4 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:784 0 root 10215f0
DBMT 309 enq: TX - index cont test python ACTIVE 4 f9qav875np46u 0:0 0 insert into dbmt.1 values 1:307 0 root 10218b4
DBMT 646 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1020bab
DBMT 648 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1020bc5
DBMT 669 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:648 0 root 1020b92
DBMT 770 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 10208bb
DBMT 773 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 10207e5
DBMT 775 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 0 insert into dbmt.1 values 1:18 0 root 102083c
DBMT 780 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 102083a
DBMT 789 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1020914
DBMT 790 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1020b81
DBMT 801 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1020993
DBMT 10 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 10208f0
DBMT 14 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020818
DBMT 25 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 102090b
DBMT 31 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 102143a
DBMT 51 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020810
DBMT 55 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020961
DBMT 161 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:660 0 root 1020b83
DBMT 165 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:660 0 root 1020b76
DBMT 166 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:660 0 root 1020bc0
DBMT 167 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:660 0 root 1020b7f
DBMT 267 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 10208b4
DBMT 268 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020819
DBMT 274 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020802
DBMT 276 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020834
DBMT 279 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020f32
DBMT 281 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 102093d
DBMT 291 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 10208fc
DBMT 292 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 10207d0
DBMT 294 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1021238
DBMT 500 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 10208b5
DBMT 508 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1020918
DBMT 512 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 0 insert into dbmt.1 values 1:18 0 root 1020c90
DBMT 521 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020800
DBMT 535 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1021430
DBMT 539 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020862
DBMT 541 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1020ac7
DBMT 544 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:18 0 root 1020817
DBMT 548 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:307 0 root 1020bc7
DBMT 636 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 1020ba3
DBMT 642 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values 1:645 0 root 1020b7b
DBMT 645 enq: TX - index cont test python ACTIVE 5 f9qav875np46u 0:0 3341584 insert into dbmt.1 values : 0 root 102103d
189 rows selected.
4、查看等待链
SQL> set lines 1000
SQL> col wait_chain format a400
SQL> with t as (select * from v$session)
2 ,t2 as (select level lv,sid,serial#,program,event,connect_by_iscycle iscycle,connect_by_isleaf leaf, LEVEL,SYS_CONNECT_BY_PATH('['||program||']'||'('||nvl(event,state)||')','->') wait_chain from t
3 connect by NOCYCLE prior blocking_session= sid
4 start with state='WAITING')
5 select wait_chain,count(*),max(iscycle)iscycle from t2 where leaf=1 AND LV>1 group by wait_chain order by count(*) desc;
WAIT_CHAIN COUNT(*) ISCYCLE
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
->[python@test (TNS V1-V3)](enq: TX - index contention)->[python@test (TNS V1-V3)](L1 validation) 133 0
->[python@test (TNS V1-V3)](log file sync)->[oracle@perf-monitor (LGWR)](LGWR any worker group)->[oracle@perf-monitor (LG00)](log file parallel write) 9 0
->[python@test (TNS V1-V3)](enq: TX - index contention)->[python@test (TNS V1-V3)](latch: cache buffers chains)->[python@test (TNS V1-V3)](L1 validation) 5 0
->[python@test (TNS V1-V3)](enq: TX - index contention)->[python@test (TNS V1-V3)](latch: cache buffers chains) 5 0
->[python@test (TNS V1-V3)](enq: TX - index contention)->[python@test (TNS V1-V3)](log file sync)->[oracle@perf-monitor (LGWR)](LGWR any worker group)->[oracle@perf-monitor (LG00)](log file parallel write) 5 0
->[python@test (TNS V1-V3)](enq: TX - index contention)->[python@test (TNS V1-V3)](enq: TX - contention)->[python@test (TNS V1-V3)](enq: TX - contention) 2 0
->[python@test (TNS V1-V3)](latch: cache buffers chains)->[python@test (TNS V1-V3)](L1 validation) 2 0
->[python@test (TNS V1-V3)](enq: TX - index contention)->[python@test (TNS V1-V3)](enq: TX - contention) 1 0
->[oracle@perf-monitor (LGWR)](LGWR any worker group)->[oracle@perf-monitor (LG00)](log file parallel write) 1 0
->[python@test (TNS V1-V3)](enq: TX - contention)->[python@test (TNS V1-V3)](enq: TX - contention) 1 0
->[python@test (TNS V1-V3)](enq: TX - index contention)->[python@test (TNS V1-V3)](enq: TX - index contention)->[python@test (TNS V1-V3)](enq: TX - contention) 1 0
11 rows selected.
最后修改时间：2020-04-20 20:51:40
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
