[20211111]18c index (re)build lock or pin object.txt

[20211111]18c index (re)build lock or pin object.txt --//18c 推出了新的等待事件'index (re)build lock or pin object'，简单探究看看。 --//测试参考链接ksun-oracle.blogspot.com/2019/08/oracle-18-new-wait-event-index-rebuild.html 1.环境： YYYY> @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 18.0.0.0.0 BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. YYYY> @ ev_name 'index (re)build lock or pin object' YYYY> @ prxx ============================== EVENT#                        : 333 EVENT_ID                      : 3347698104 NAME                          : index (re)build lock or pin object PARAMETER1                    : namespace PARAMETER2                    : lock_mode PARAMETER3                    : pin_mode WAIT_CLASS_ID                 : 4166625743 WAIT_CLASS#                   : 3 WAIT_CLASS                    : Administrative DISPLAY_NAME                  : index (re)build lock or pin object CON_ID                        : 0 PL/SQL procedure successfully completed. --//分类在Administrative，也就是建立索引或者rebuild时需要。 2.测试： YYYY> create table tx as select * from all_objects; Table created. YYYY> @ 10046on 12 Session altered. YYYY> create index i_tx_object_id on tx(object_id); Index created. YYYY> @ 10046off Session altered. # grep -n "index (re" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_1136.trc 1089:WAIT #140172263031880: nam='index (re)build lock or pin object' ela= 5 namespace=1 lock_mode=2 pin_mode=2 obj#=-1 tim=61102533810230 1136:WAIT #140172263031880: nam='index (re)build lock or pin object' ela= 31 namespace=4 lock_mode=3 pin_mode=3 obj#=-1 tim=61102533820501 2171:WAIT #140172263031880: nam='index (re)build lock or pin object' ela= 14 namespace=1 lock_mode=3 pin_mode=3 obj#=290065 tim=61102534318758 2172:WAIT #140172263031880: nam='index (re)build lock or pin object' ela= 5 namespace=1 lock_mode=3 pin_mode=3 obj#=290065 tim=61102534318850 --//注意namespace lock_mod,pin_mode,obj#. YYYY> select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from user_objects where object_name in ('I_TX_OBJECT_ID','TX'); OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID -------------------- ---------- -------------- I_TX_OBJECT_ID           290066         290066 TX                       290065         290065 --//我的理解前面2步对象没有建立Obj#=-1,后面2个针对的是表。 --//The Lock/pin mode seems referring to those documented in v$libcache_locks: Lock/pin mode: 0 - No lock/pin held 1 - Null mode 2 - Share mode 3 - Exclusive mode 3.继续测试看看rebuild的情况： YYYY> @ tix YYYY> @ 10046on 12 Session altered. YYYY> alter index i_tx_object_id rebuild; Index altered. YYYY> @ 10046off Session altered. # grep -n "index (re" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_1136_0001.trc 165:WAIT #140172262962128: nam='index (re)build lock or pin object' ela= 6 namespace=1 lock_mode=2 pin_mode=2 obj#=253 tim=61103406305282 199:WAIT #140172262962128: nam='index (re)build lock or pin object' ela= 13 namespace=4 lock_mode=3 pin_mode=3 obj#=253 tim=61103406306603 1065:WAIT #140172262962128: nam='index (re)build lock or pin object' ela= 18 namespace=1 lock_mode=3 pin_mode=3 obj#=290066 tim=61103406618200 1066:WAIT #140172262962128: nam='index (re)build lock or pin object' ela= 6 namespace=1 lock_mode=3 pin_mode=3 obj#=290066 tim=61103406618302 YYYY> select OBJECT_NAME,object_type,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name in ('I_TX_OBJECT_ID','TX') or data_object_id=253; OBJECT_NAME          OBJECT_TYPE  OBJECT_ID DATA_OBJECT_ID -------------------- ----------- ---------- -------------- I_LINK1              INDEX              253            253 TX                   TABLE           290065         290065 I_TX_OBJECT_ID       INDEX           290066         290067 --//什么会出现I_LINK1。 YYYY> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE from dba_indexes where index_name='I_LINK1'   2  @ prxx ============================== OWNER                         : SYS INDEX_NAME                    : I_LINK1 INDEX_TYPE                    : NORMAL TABLE_OWNER                   : SYS TABLE_NAME                    : LINK$ TABLE_TYPE                    : TABLE PL/SQL procedure successfully completed. 4.继续测试看看rebuild online的情况： YYYY> @ tix YYYY> @ 10046on 12 Session altered. YYYY> alter index i_tx_object_id rebuild online; Index altered. YYYY> @ 10046off Session altered. # grep -n "index (re" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_1136_0002.trc 146:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 6 namespace=1 lock_mode=2 pin_mode=2 obj#=290066 tim=61103844239635 179:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 14 namespace=4 lock_mode=3 pin_mode=3 obj#=290066 tim=61103844240858 212:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 15 namespace=1 lock_mode=3 pin_mode=3 obj#=290066 tim=61103844241876 1052:WAIT #140172263004288: nam='index (re)build lock or pin object' ela= 5 namespace=1 lock_mode=2 pin_mode=2 obj#=-1 tim=61103844305776 1100:WAIT #140172263004288: nam='index (re)build lock or pin object' ela= 21 namespace=4 lock_mode=3 pin_mode=3 obj#=-1 tim=61103844313657 1446:WAIT #140172263004288: nam='index (re)build lock or pin object' ela= 11 namespace=1 lock_mode=3 pin_mode=3 obj#=-1 tim=61103844324239 7717:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 20 namespace=1 lock_mode=3 pin_mode=3 obj#=-1 tim=61103844831892 7718:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 9 namespace=4 lock_mode=3 pin_mode=3 obj#=-1 tim=61103844831955 10284:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 12 namespace=1 lock_mode=3 pin_mode=3 obj#=-1 tim=61103844912987 10285:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 8 namespace=4 lock_mode=3 pin_mode=3 obj#=-1 tim=61103844913080 10497:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 11 namespace=1 lock_mode=3 pin_mode=3 obj#=-1 tim=61103844920041 13970:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 14 namespace=1 lock_mode=3 pin_mode=3 obj#=-1 tim=61103845174681 13971:WAIT #140172244923656: nam='index (re)build lock or pin object' ela= 8 namespace=4 lock_mode=3 pin_mode=3 obj#=-1 tim=61103845174726 5.分析表和索引呢？ @ tix @ 10046on 12 execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'tx',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false) @ 10046off # grep -n "index (re" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_1136_0004.trc @ tix @ 10046on 12 exec dbms_stats.gather_index_stats('TTT', 'I_TX_OBJECT_ID'); @ 10046off # grep -n "index (re" /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_1136_0005.trc --//原始链接产生等待事件，我的测试没有。