观察library cache lock/pin等待对象的变化
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/12/ddl-library_cache_lock.html
链接：http://www.dbaroad.me/archives/2008/12/ddl-library_cache_lock.html
关于library cache lock/pin原理，eygle老大的文章已经讲得很深入了，详见：
关于shared pool的深入探讨(五)
我这里借一次批量授权的机会，通过dba_ddl_locks视图观察library cache lock/pin等待对象的变化。(对数据库名、用户名、表名做了更改)
当前会话信息：
SQL> select b.spid,a.sid,a.serial#,a.oT_USER,a.process 2 from v$session a,v$process b 3 where a.PADDR=b.addr and a.sid= 4 (select max(sid) sid from v$mystat); SPID SID SERIAL# OT_USER PROCESS ------------ ---------- ---------- ---------------------- ---------- 7162 1033 1871 oracle 7161
执行DDL操作 GRANT 上千张表，给某一用户
sys@DB1>@sw_gb
INST_ID EVENT SQL_HASH_VALUE USERNAME COUNT(*)
------- ----------------------- -------------- ---------- ----------
1 latch free 2891800513 E_USER1 1
1 async disk IO 0 1
1 library cache pin 1271958979 LD_USER1 1
1 library cache lock 1271958979 LD_USER1 1
1 library cache lock 3042763892 E_USER1 1
1 library cache lock 3455664041 SYS 1
1 PX Deq: Execute Reply 1386780949 SYS 1
1 db file sequential read 673864318 LD_USER1 1
2 latch free 1271958979 LD_USER1 1
2 library cache pin 3500893576 E_USER1 1
2 library cache pin 2827598917 E_USER1 1
2 library cache pin 2290525860 E_USER1 1
2 library cache pin 2134081133 LD_USER1 1
2 async disk IO 0 1
1 db file sequential read 206691788 EI_USER2 1
1 library cache lock 1302056986 E_USER1 1
2 library cache pin 1271958979 LD_USER1 3
1 library cache pin 3042763892 E_USER1 4
2 enqueue 2827598917 E_USER1 8
2 library cache pin 3042763892 E_USER1 14
1 library cache pin 2891800513 E_USER1 17
1 library cache lock 3255127537 E_USER1 17
1 library cache lock 3560802024 E_USER1 20
产生了大量的library cache lock/pin。观察dba_ddl_lock的变化：
sys@DB1>@get_ddl.sql SID OWNER NAME TYPE MODE_HELD MODE_REQU ---- ------ --------------- -------------------- --------- --------- 1033 T_USER T_PROD_LN Table/Procedure/Type None Exclusive 1080 T_USER T_PROD_CFGVER Table/Procedure/Type Share None 1080 T_USER T_ORDER_ITEM_X Table/Procedure/Type Share None 1080 T_USER T_ORG_EXT Table/Procedure/Type Share None 1080 T_USER T_ORDER_ITEM Table/Procedure/Type Share None 1080 T_USER T_ORDER_ITEM_OM Table/Procedure/Type Share None 1867 T_USER T_ASSET Table/Procedure/Type Share None 1867 T_USER T_PARTY Table/Procedure/Type Share None 1867 T_USER T_ORG_EXT Table/Procedure/Type Share None 1867 T_USER T_CTLG_CAT Table/Procedure/Type Share None 122 T_USER T_LOY_MEMBER Table/Procedure/Type Share None 122 T_USER T_ADDR_PER Table/Procedure/Type Share None 122 T_USER T_POSTN Table/Procedure/Type Share None 122 T_USER T_ORG_EXT_X Table/Procedure/Type Share None ... ... ... ... 1016 T_USER T_ASSET_X Table/Procedure/Type Share None 1016 T_USER T_DOC_AGREE Table/Procedure/Type Share None 1016 T_USER T_CONTACT Table/Procedure/Type Share None 1016 T_USER T_ORG_EXT Table/Procedure/Type Share None 1016 T_USER T_ADDR_PER Table/Procedure/Type Share None 1016 T_USER T_PROD_LN Table/Procedure/Type Share None 2697 T_USER T_ORG_EXT Table/Procedure/Type Share None 2697 T_USER T_ASSET_BU Table/Procedure/Type Share None 2697 T_USER T_PROD_CFGVER Table/Procedure/Type Share None 1080 T_USER T_VDISCNT_ITEM Table/Procedure/Type Share None 604 T_USER T_PROD_LN Table/Procedure/Type None Share 833 T_USER T_PROD_LN Table/Procedure/Type None Share 75 T_USER T_PROD_LN Table/Procedure/Type None Share 1867 T_USER T_PROD_LN Table/Procedure/Type None Share 1832 T_USER T_PROD_LN Table/Procedure/Type None Share 1456 T_USER T_PROD_LN Table/Procedure/Type None Share 812 T_USER T_PROD_LN Table/Procedure/Type None Share 1094 T_USER T_PROD_LN Table/Procedure/Type None Share 1100 T_USER T_PROD_LN Table/Procedure/Type None Share 1240 T_USER T_PROD_LN Table/Procedure/Type None Share 2697 T_USER T_PROD_LN Table/Procedure/Type None Share 106 rows selected.
此时可以看出，1033会话GRANT时，在T_PROD_LN表上产生了等待，而T_PROD_LN被1016以Share模式持有，而604、833等一堆会话，也在请求Share模式锁
sys@DB1>@get_ddl SID OWNER NAME TYPE MODE_HELD MODE_REQU ---- ------ --------------- -------------------- --------- --------- 1033 T_USER T_ONL_PROJECT Table/Procedure/Type Exclusive None 1033 T_USER T_NQ_INSTANCE Table/Procedure/Type None Exclusive 149 T_USER T_PROD_CFGVER Table/Procedure/Type Share None 149 T_USER T_ORDER_ITEM_X Table/Procedure/Type Share None 149 T_USER T_ASSET_X Table/Procedure/Type Share None 149 T_USER T_ORDER Table/Procedure/Type Share None 149 T_USER T_ASSET Table/Procedure/Type Share None 149 T_USER T_LOY_ATTRDEFN Table/Procedure/Type Share None 149 T_USER T_USER Table/Procedure/Type Share None 149 T_USER T_CONTACT Table/Procedure/Type Share None 149 T_USER T_DOC_AGREE Table/Procedure/Type Share None 296 T_USER T_LOY_PROD_PT Table/Procedure/Type Share None ... ... ... ... 117 rows selected.
此时1033在会表T_ONL_PROJECT做授权操作,以Exclusive模式持有，同样，又在表T_NQ_INSTANCE上产生了等待
sys@DB1> sys@DB1>@get_ddl SID OWNER NAME TYPE MODE_HELD MODE_REQU ---- ------ --------------- -------------------- --------- --------- 149 T_USER T_PROD_CFGVER Table/Procedure/Type Share None 149 T_USER T_ORDER_ITEM_X Table/Procedure/Type Share None 149 T_USER T_ASSET_X Table/Procedure/Type Share None 149 T_USER T_DOC_AGREE Table/Procedure/Type Share None 149 T_USER T_CONTACT Table/Procedure/Type Share None 149 T_USER T_ORG_EXT Table/Procedure/Type Share None 149 T_USER T_ASSET Table/Procedure/Type Share None 149 T_USER T_ORDER Table/Procedure/Type Share None 1456 T_USER T_ASSET_X Table/Procedure/Type Share None 1456 T_USER T_ASSET Table/Procedure/Type Share None 1456 T_USER T_ASSET_BU Table/Procedure/Type Share None 122 T_USER T_PROD_CFGVER Table/Procedure/Type Share None 122 T_USER T_ASSET_X Table/Procedure/Type Share None 122 T_USER T_ORDER Table/Procedure/Type Share None 122 T_USER T_ASSET Table/Procedure/Type Share None 122 T_USER T_VDISCNT_ITEM Table/Procedure/Type Share None ... ... ... ... 48 rows selected. sys@DB1>
此时GRANT操作已完成
sys@DB1>/ SID OWNER NAME TYPE MODE_HELD MODE_REQU ---- ------ --------------- -------------------- --------- --------- 1691 T_USER T_PROD_CFGVER Table/Procedure/Type Share None 1691 T_USER T_ORDER_ITEM_X Table/Procedure/Type Share None 1691 T_USER T_ASSET_X Table/Procedure/Type Share None 1691 T_USER T_ORDER Table/Procedure/Type Share None 1691 T_USER T_DOC_AGREE Table/Procedure/Type Share None 1691 T_USER T_ASSET Table/Procedure/Type Share None 1691 T_USER T_CONTACT Table/Procedure/Type Share None 1691 T_USER T_ORG_EXT Table/Procedure/Type Share None 1691 T_USER T_LOY_ATTRDEFN Table/Procedure/Type Share None 1691 T_USER T_CTLG_CAT Table/Procedure/Type Share None 1691 T_USER T_ADDR_PER Table/Procedure/Type Share None 1691 T_USER T_PRI_LST Table/Procedure/Type Share None 1691 T_USER T_ENTLMNT Table/Procedure/Type Share None 1691 T_USER T_LOY_PROD_PT Table/Procedure/Type Share None 1691 T_USER T_ACT_PRDINT Table/Procedure/Type Share None 1691 T_USER T_ORDER_ITEM_OM Table/Procedure/Type Share None 1691 T_USER T_ORDER_ITEM Table/Procedure/Type Share None 1691 T_USER T_SRC_EVT_REG Table/Procedure/Type Share None 1691 T_USER T_ORDER_X Table/Procedure/Type Share None 1691 T_USER T_INVLOC Table/Procedure/Type Share None 1691 T_USER T_SRC Table/Procedure/Type Share None 1691 T_USER T_PROD_INT Table/Procedure/Type Share None 22 rows selected. sys@DB1>/ SID OWNER NAME TYPE MODE_HELD MODE_REQU ---- ------ --------------- -------------------- --------- --------- 1016 T_USER T_ADDR_PER Table/Procedure/Type Share None 1016 T_USER T_PROD_LN Table/Procedure/Type Share None 1016 T_USER T_PROD_INT Table/Procedure/Type Share None 727 T_USER T_PROD_CFGVER Table/Procedure/Type Share None 727 T_USER T_ASSET_X Table/Procedure/Type Share None 727 T_USER T_DOC_AGREE Table/Procedure/Type Share None 727 T_USER T_ASSET Table/Procedure/Type Share None 727 T_USER T_ASSET_EMP Table/Procedure/Type Share None 727 T_USER T_ASSET_OM Table/Procedure/Type Share None 727 T_USER T_ASSET_BU Table/Procedure/Type Share None 727 T_USER T_PARTY_RPT_RE Table/Procedure/Type Share None 727 T_USER T_CONTACT Table/Procedure/Type Share None 727 T_USER T_PARTY Table/Procedure/Type Share None 727 T_USER T_ORG_EXT Table/Procedure/Type Share None 14 rows selected. sys@DB1>@get_ddl no rows selected
library cache lock/pin 逐渐消失
sys@DB1>@sw_gb
INST_ID EVENT SQL_HASH_VALUE USERNAME COUNT(*)
------- ----------------------- -------------- ---------- ---------
1 async disk IO 0 1
1 library cache pin 2950176105 LD_USER1 1
1 PX Deq: Execute Reply 1386780949 SYS 1
1 db file sequential read 1673886824 LD_USER1 1
2 async disk IO 0 1
2 library cache pin 1983405473 LD_USER1 1
2 library cache pin 2599548713 LD_USER1 1
7 rows selected.
sys@DB1>
sys@DB1>@sw_gb
INST_ID EVENT SQL_HASH_VALUE USERNAME COUNT(*)
------- ----------------------- -------------- ---------- ---------
1 async disk IO 0 1
1 library cache pin 4055944923 LD_USER1 1
1 PX Deq: Execute Reply 1386780949 SYS 1
1 db file sequential read 3290832398 EI_USER2 1
2 async disk IO 0 1
— The End —
关键字: 基础知识
站内搜索