[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

--//原始連結產生等待事件，我的測試沒有。