oracle library cache之trace小記
/************獲取查詢sql的hash_value**********/
SQL> select vl.sql_text,vl.HASH_VALUE from v$sql vl where vl.sql_text like '%t_hash%';
SQL_TEXT HASH_VALUE
-------------------------------------------------------------------------------- ----------
select vl.sql_text,vl.HASH_VALUE from v$sql vl where vl.sql_text like '%t_hash% 2521061608
select /*+s1*/ count(a) from t_hash 3785405927
如下命令含義：
ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level LL';
其中LL代表Level級別,對於9.2.0及以後版本,不同Level含義如下:
Level =1 ,轉儲Library cache統計資訊
Level =2 ,轉儲hash table概要
Level =4 ,轉儲Library cache物件，只包含基本資訊
Level =8 ,轉儲Library cache物件，包含詳細資訊(包括child references,pin waiters等)
Level =16,增加heap sizes資訊
Level =32,增加heap資訊
/***********dump library cache hash table************/
SQL> alter session set events 'immediate trace name library_cache level 2';
Session altered
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base d:\oracle11g_64bit
1 ADR Home d:\oracle11g_64bit\diag\rdbms\second\second
1 Diag Trace d:\oracle11g_64bit\diag\rdbms\second\second\trace
1 Diag Alert d:\oracle11g_64bit\diag\rdbms\second\second\alert
1 Diag Incident d:\oracle11g_64bit\diag\rdbms\second\second\incident
1 Diag Cdump d:\oracle11g_64bit\diag\rdbms\second\second\cdump
1 Health Monitor d:\oracle11g_64bit\diag\rdbms\second\second\hm
1 Default Trace File d:\oracle11g_64bit\diag\rdbms\second\second\trace\second_ora_2364.trc
1 Active Problem Count 1
1 Active Incident Count 10
11 rows selected
/***********dump內容************/
*** 2013-04-23 10:17:10.668
SGA:60d88f30 Flags=2c7
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0
LIBRARY CACHE HASH TABLE: size=131072 count=6573
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 124694
1 6187
2 187
3 4
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
/************Level =1 ,轉儲Library cache統計資訊*******************/
SQL> alter session set events 'immediate trace name library_cache level 1';
*** 2013-04-23 10:22:14.313
Library Cache Dump
LIBRARY CACHE STATISTICS:
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
SQL AREA 13844 0.631 178805 0.976 57 219
TABLE/PROCEDURE 12408 0.818 24383 0.891 172 0 ---裡面是各種名稱空間的資訊，和v$library_cache同內容
BODY 1018 0.943 11099 0.994 0 0
TRIGGER 110 0.882 109 0.872 0 1
INDEX 322 0.789 262 0.664 20 0
CLUSTER 735 0.989 352 0.977 0 0
KGL TESTING 0 0.000 0 0.000 0 0
PIPE 0 0.000 0 0.000 0 0
LOB 0 0.000 0 0.000 0 0
DIRECTORY 1 0.000 2 0.000 0 0
QUEUE 4 0.500 6 0.333 0 0
REPLICATION OBJECT GROUP 0 0.000 0 0.000 0 0
REPLICATION PROPAGATOR 0 0.000 0 0.000 0 0
JAVA SOURCE 3 0.333 3 0.333 0 0
JAVA RESOURCE 3 0.333 3 0.333 0 0
REPLICATED TABLE OBJECT 0 0.000 0 0.000 0 0
REPLICATION INTERNAL PACKAGE 0 0.000 0 0.000 0 0
CONTEXT POLICY 0 0.000 0 0.000 0 0
PUB SUB INTERNAL INFORMATION 227 0.982 288 0.986 0 0
SUMMARY 0 0.000 0 0.000 0 0
DIMENSION 0 0.000 0 0.000 0 0
APP CONTEXT 18 0.944 18 0.944 0 0
STORED OUTLINE 0 0.000 0 0.000 0 0
RULESET 3 0.667 3 0.667 0 0
RESOURCE MANAGER 173 0.925 172 0.884 0 0
XML SCHEMA 0 0.000 0 0.000 0 0
PENDING RESOURCE MANAGER PLAN 0 0.000 0 0.000 0 0
PENDING RESOURCE MANAGER CONSUMER GROUP 0 0.000 0 0.000 0 0
SUBSCRIPTION 1 0.000 1 0.000 0 0
LOCATION 0 0.000 0 0.000 0 0
REMOTE OBJECT INFORMATION 0 0.000 0 0.000 0 0
SNAPSHOT META. DATA 0 0.000 0 0.000 0 0
JAVA DATA 3 0.333 3 0.333 0 0
SECURITY PROFILE 0 0.000 0 0.000 0 0
TRANSFORMATION 0 0.000 0 0.000 0 0
REPLICATION - LOG BASED CHILD 0 0.000 0 0.000 0 0
RULE 0 0.000 0 0.000 0 0
STREAM 0 0.000 0 0.000 0 0
RULE EVALUATION CONTEXT 1 0.000 0 0.000 0 0
STREAMS APPLY PROCESS 0 0.000 0 0.000 0 0
REPLICATION SOURCE 0 0.000 0 0.000 0 0
REPLICATION DESTINATION 0 0.000 0 0.000 0 0
IFS SCHEMA 0 0.000 0 0.000 0 0
XDB CONFIG 0 0.000 0 0.000 0 0
USER AGENT 0 0.000 0 0.000 0 0
MULTI-VERSION OBJECT FOR TABLE 427 0.799 185 0.768 0 0
SCHEDULER EVENT QUEUE INFORMATION 0 0.000 0 0.000 0 0
CHANGE SET 0 0.000 0 0.000 0 0
MULTI-VERSION OBJECT FOR INDEX 256 0.648 98 0.541 0 0
SQL TUNING BASE OBJECT 3 0.000 3 0.000 0 0
HINTSET OBJECT 9 0.667 9 0.667 0 0
SCHEDULER GLOBAL ATTRIBUTE 12887 1.000 12887 1.000 1 0
SCHEDULER EARLIEST START TIME 377 0.984 376 0.795 71 0
TEMPORARY TABLE 77 0.753 77 0.000 58 0
TEMPORARY CLUSTER 0 0.000 0 0.000 0 0
TEMPORARY INDEX 20 0.000 20 0.000 0 0
SCRATCH PAD 0 0.000 0 0.000 0 0
SCHEDULER JOB SLAVE 0 0.000 0 0.000 0 0
MINING MODELS 0 0.000 0 0.000 0 0
SYNC STREAMS CAPTURE 0 0.000 0 0.000 0 0
LIGHT WEIGHT SESSION 0 0.000 0 0.000 0 0
DATA SECURITY DOCUMENT 0 0.000 0 0.000 0 0
SECURITY CLASS 0 0.000 0 0.000 0 0
XDB ACL 0 0.000 0 0.000 0 0
EDITION 266 0.996 372 0.995 0 0
SCHEDULER LIGHTWEIGHT JOB 0 0.000 0 0.000 0 0
REMOTE OBJECT LOCAL SUMMARY REFERENCE 0 0.000 0 0.000 0 0
STREAMS PROPOGATION 0 0.000 0 0.000 0 0
FUSION XS PARAMETERS 0 0.000 0 0.000 0 0
DBLINK 38 0.974 0 0.000 0 0
XDB REPOSITORY 0 0.000 0 0.000 0 0
EV BASE TABLE 0 0.000 0 0.000 0 0
OBJECT ID 119 0.000 0 0.000 0 0
SCHEMA 3745 0.995 0 0.000 0 0
DBINSTANCE 1 0.000 0 0.000 0 0
CUMULATIVE 47099 0.832 229536 0.968 379 220
SGA:60d88f30 Flags=2c7
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0
/***********Level =4 ,轉儲Library cache物件，只包含基本資訊
SQL> alter session set events 'immediate trace name library_cache level 4';
/****轉換本文最開始查詢sql的hash_value為十六進位制*********/
SQL> select to_char(3785405927,'xxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR(3785405927,'XXXXXXXXXX
------------------------------
e1a0b5e7
-------level 4全是bucket,此僅選留與查詢sql相關的bucket
Bucket: #=46567 Mutex=60f50bf0(0, 9, 0, 6)
---bucket由多個library handle構成,library handle互組為雙向連結串列
LibraryHandle: Address=61498690 Hash=e1a0b5e7 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD ---注意這裡的hash_value的十六進位制
ObjectName: Name=select /*+s1*/ count(a) from t_hash
FullHashValue=26b633b016ffd6b2e59aafbbe1a0b5e7 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3785405927 wnerIdn=0 --identifier即是hash_value的10進位制
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=61498740(0, 0, 0, 0) Mutex=614987b8(41, 24, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists: ---等待lock和pin的列表,是對於library handle的等待與鎖定
Lock=61498720[61498720,61498720]
Pin=61498730[61498700,61498700]
Timestamp: Current=04-23-2013 10:15:16 --timestamp時間戳
LibraryObject: Address=56ce9540 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] ---library handle指向的library object地址
ChildTable: size='16'
Child: id='0' Table=56cea3f0 Reference=56ce9e90 Handle=614473a8
NamespaceDump:
Parent Cursor: sql_id=fb6pgrghu1dg7 parent=000007FF56CE9628 maxchild=1 plk=n ppn=n --父遊標的sql_id 對應v$sql.sql_id
/************Level =8 ,轉儲Library cache物件，包含詳細資訊(包括child references,pin waiters等)
SQL> alter session set events 'immediate trace name library_cache level 11';
----level 11比level 4內容更詳細，多了datablock
Bucket: #=46567 Mutex=60f50bf0(0, 14, 0, 6)
LibraryHandle: Address=61498690 Hash=e1a0b5e7 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD ---address即library cache hash bucket下的library handle的記憶體地址,對應v$sql.address
ObjectName: Name=select /*+s1*/ count(a) from t_hash -----查詢sql的名稱，即library cache object的名稱
---namespace為sql area，就是儲存執行過的sql typ為遊標，把執行過的sql儲存在cursor中
FullHashValue=26b633b016ffd6b2e59aafbbe1a0b5e7 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3785405927 wnerIdn=0
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=61498740(0, 0, 0, 0) Mutex=614987b8(41, 28, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=61498720[61498720,61498720]
Pin=61498730[61498700,61498700]
Timestamp: Current=04-23-2013 10:15:16
LibraryObject: Address=56ce9540 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks: --------多了這個datablock結構
Block: #='0' name=PCUR^e1a0b5e7 pins=0 Change=NONE
Heap=61441058 Pointer=56ce9628 Extent=56ce94c0 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=140614419
ChildTable: size='16'
--childtable即library cache object所依賴的基物件表如下為t_hash,可透過如下的handle獲知
Child: id='0' Table=56cea3f0 Reference=56ce9e90 Handle=614473a8 --handle即執行過的sql的子游標的handle記憶體地址,對應v$sql.child_address
NamespaceDump:
Parent Cursor: sql_id=fb6pgrghu1dg7 parent=000007FF56CE9628 maxchild=1 plk=n ppn=n
/*******如下等待事件也說明library cache pin及library cache lock基於library cache 的handle等待,parameter1為handle的記憶體地址，對應v$sql.address********/
SQL> select * from v$event_name where name like '%library%' and parameter1 like '%handle address%';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------
280 2802704141 library cache pin handle address pin address 100*mode+namespace 3875070507 4 Concurrency
281 916468430 library cache lock handle address lock address 100*mode+namespace 3875070507 4 Concurrency
---上述提到namespace,可參考：
http://blog.csdn.net/tianlesoftware/article/details/6624122
---library cache中library cache object可參考
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-759136/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- Oracle Library cacheOracle
- 【ASK_ORACLE】Library Cache概念篇（二）之Library Cache Pin的定義Oracle
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- oracle 10049 event之library cache lockOracle
- 【TUNE_ORACLE】等待事件之“library cache pins”Oracle事件
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- oracle異常:library cache lockOracle
- oracle dump trace redo幾則方法小記Oracle
- Oracle Library cache mutex x tipsOracleMutex
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- oracle11g ddl trace event事件小記Oracle事件
- oracle調優之-共享池尺寸調優+library cache+dicitonary library 命中率Oracle
- 深入理解shared pool共享池之library cache的library cache lock系列四
- 深入理解shared pool共享池之library cache的library cache pin系列三
- DBA手記（學習）-library cache pin
- library cache pin和library cache lock的診斷分析
- library cache lock和library cache pin區別總結
- Oracle Library cache內部機制詳解Oracle
- oracle10g_oracle11g_library cache_shared pool管理方面的小區別Oracle
- oracle buffer cache管理機制_buffer cache dump與lru機制小記Oracle
- library cache內容系列一之library hash bucket--library object handle--heapObject
- 定位Library Cache pin,Library Cache lock等待的解決方法
- Oracle SQL_TRACE使用小結OracleSQL
- [Oracle]--Library cache lock 故障解決一例Oracle
- oracle library cache相關的等待事件及latchOracle事件
- 【ASM_ORACLE】Library Cache最佳化篇（二）Library cache load lock的概念和解決辦法ASMOracle
- zt_如何平面解決library cache lock和library cache pin
- 記一次library cache pin事件解決事件
- 11G資料庫之library cache lock及library cache pin模擬結合hanganalyze定位資料庫
- 【BUFFER】Oracle buffer cache之 latch 學習記錄Oracle
- zt_Oracle Library cache 內部機制 說明Oracle
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin等待分析
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin 等待事件事件