Library cache pin/lock 在Oracle 10g的增強
從Oracle 10g開始,以上測試將不會看到同樣的效果,這是因為Oracle 10g對於物件編譯與重建做出了增強。注意以下測試(來自Oracle 10gR2環境):
sys@NEI> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
sys@NEI> select object_name,last_ddl_time from user_objects where object_name='PINING';
OBJECT_NAME LAST_DDL_TIME
-------------------- -------------------
PINING 2009-12-06 15:09:24
sys@NEI> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
Procedure created.
sys@NEI> select object_name,last_ddl_time from user_objects where object_name='PINING';
OBJECT_NAME LAST_DDL_TIME
-------------------- -------------------
PINING 2009-12-06 15:09:24
注意當重新replace一個過程時,Oracle會首先執行檢查,如果程式碼前後完全相同,則replace工作並不會真正進行(因為沒有變化),物件的LAST_DDL_TIME不會改變,這就意味著Latch的競爭可以減少。
再來看一下此前的測試在Oracle 10g中的情況如果。
首先在Session 1中執行:
sys@NEI> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
Procedure created.
sys@NEI> create or replace procedure calling
2 is
3 begin
4 pining;
5 dbms_lock.sleep(60);
6 end;
7 /
Procedure created.
sys@NEI> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');
OBJECT_NAME LAST_DDL_TIME
-------------------- -------------------
PINING 2009-12-06 15:26:58
CALLING 2009-12-06 15:19:38
sys@NEI> set time on
15:31:49 sys@NEI> exec calling;
PL/SQL procedure successfully completed.
15:32:59 sys@NEI>
在Session 1過程中,切換到Session 2執行授權:
sys@NEI> set time on
15:32:00 sys@NEI> grant execute on pining to tq;
Grant succeeded.
可以看到Session 2的授權順利通過,再轉到Session 1:
15:32:59 sys@NEI> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');
OBJECT_NAME LAST_DDL_TIME
-------------------- -------------------
PINING 2009-12-06 15:32:02
CALLING 2009-12-06 15:19:38
注意到物件pining的LAST_DDL_TIME已經變化。grant授權已經能夠繞過library cache pin的競爭,這是Oracle 10g的增強。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8183550/viewspace-659590/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- library cache pin和library cache lock的診斷分析
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- 定位Library Cache pin,Library Cache lock等待的解決方法
- enq:Library cache lock/pin等待事件ENQ事件
- Library cache lock/pin詳解(轉)
- library cache lock和library cache pin區別總結
- library cache pin/lock的解決辦法
- zt_如何平面解決library cache lock和library cache pin
- library cache lock\pin的查詢與處理
- zt_library cache pin和lock等待分析
- Shared pool的library cache lock/pin及硬解析
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- 0317Library Cache Pin/Lock Wait EventsAI
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- library cache lock和cursor: pin S wait on X等待AI
- oracle異常:library cache lockOracle
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- 【ASK_ORACLE】Library Cache概念篇(二)之Library Cache Pin的定義Oracle
- 由最長SQL想到的Latch Free( Library Cache Pin/Lock)整理~~草稿SQL
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin等待分析
- library cache pin 等待事件事件
- 11G資料庫之library cache lock及library cache pin模擬結合hanganalyze定位資料庫
- oracle 11g下如何捕捉library cache物件執行時產生的lock、pin等資訊Oracle物件
- create index online與非online在library cache lock/pin方面的區別Index
- 共享池之六:shared pool latch/ library cache latch /lock pin 簡介
- library cache pin等待事件的模擬事件
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- oracle 10049 event之library cache lockOracle
- LIBRARY CACHE LOCK 等待事件事件
- 解決library cache pin等待事件事件
- library cache pin 阻塞程式查詢
- 查詢Library Cache Pin等待原因
- 常用定位library cache lock的方法