/************获取查询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可参考
http://www.hellodb.net/2010/07/oracle-library-cache.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-759136/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-759136/