SHARED POOL ARCHITECTURE - ORACLE IN ACTION摘要：本文首先详细介绍了oracle中shared pool的概念以及所包含的内存结构。然后深入介绍了oracle对于shared pool的管理机制。最后全面介绍了有关buffer cache监控以及调优的实用方法。
1. shared pool的概念
oracle数据库作为一个管理数据的产品，必须能够认出用……
声明：该文章系网友上传分享，此内容仅代表网友个人经验或观点，不代表本网站立场和观点；若未进行原创声明，则表明该文章系转载自互联网；若该文章内容涉嫌侵权，请及时向
论文写作技巧
上一篇：下一篇：
相关经验教程查看: 1430|回复: 2
谁能详细解释一下shared pool duration特性
论坛徽章:0
不明白这个特性的用意和作用到底是什么
为什么还要划分sub partition呢
另外我昨天发了重复的帖子号被封了，求知心切，我有罪，呵呵
能不能解封？
版规在哪？
论坛徽章:47
本帖最后由 Yong Huang 于
10:28 编辑
A shared pool has subpools (starting with 9i) and each subpool has sub-subpools or durations (starting with 10gR2 I think). It's easy to understand why there' each subpool is managed pretty much independently, with its own LRU list etc., and all subpools have the same functionality. But durations are different. Each one holds different types of objects. According to heapdump, as shown in Jonathan Lewis's &Oracle Core Essential Internals& p.184, dictionary cache is in duration 1, heap 0 (cursor head or parent cursor) in duration 2, SQLArea (heap 6) in duration 3.[note] Different types of objects have different characteristics. Within each type, objects have similar characteristics, such as size requirement, how long they stay in memory, etc. Oracle's work on shared pool durations is kind of like Solaris or Linux kernel slab. A chunk of memory is allocated from a specific duration (slab) depending on what function you want to use this memory chunk for. There're of course differences. A Solaris or Linux kernel has many slabs, while there're only 4 Oracle shared pool durations. Each slab is exclusively dedicated to one type of objects, such as inode_cache, nfs_page. A duration can contain vari the rule about what shared pool objects are in what duration is not very strict.
Other than Jonathan Lewis's book, this note
: ORA-600 [5351], ORA-600 [4000] AND ORA-4031 OCCURING IN GOLDEN GATE AND TIMESTEN
talks about the concept of durations, and more or less satisfy my curiosity why it's called &duration&, a term that suggests time or how long something lasts.
One paragraph (Solution c) in
LCK temporarily stuck waiting for latch 'Child row cache objects' (Doc ID )
also talks about durations.
The fixed table showing durations is x$ksmsp_nwex. You can find a query using this table in
: ORA-4031 WITH 7 SUBPOOLS AND DURATIONS THEN CPU SPIKES TO 100%
______________
On an 11.2.0.3 database, I have durations enabled (_enable_shared_pool_durations is TRUE). If you don't use ASMM nor AMM, you may have to manually set this parameter to true and bounce the database. I create a level 2 heapdump (oradebug dump heapdump 2) and manually split the trace file into four files, each for one duration of subpool 1. Then I aggregate on the memory chunk usage string and see what type of usage is the most common in each duration.
$ cut -c53-67 duration1-0 | sort | uniq -c | sort -rn | head
& &7127 KQR PO& && &&&&-- &KQR PO& row cache parent objects are very common in duration 0
& &2826 KGLHD& && &&&&-- Library cache handles are common too
& &&&27 SQLA^c22c7d28
& &&&27 KGLH0^c22c7d28
& &&&23 SQLA^7e6b9434
& &&&23 KGLH0^7e6b9434
& &&&20 SQLA^a2ac011a
& &&&20 perm
$ cut -c53-67 duration1-1 | sort | uniq -c | sort -rn | head
&&10578 KGLHD& && &&&&-- lots of library cache handles in duration 1
& &7127 KQR PO
& &6588 KGLDA
& &1217 ges resource
& & 143 parameter table
& &&&72 name-service
& &&&28 KGLNA
& &&&19 KKSSP
$ cut -c53-67 duration1-2 | sort | uniq -c | sort -rn | head
& & 191 KGLH0^9e6af5b8& && &&&&-- not fair to include &random& numbers (SQL hash values) in uniq -c
& &&&53 KGLH0^52ccb2f2
& &&&52 KGLH0^d7bcc960
& &&&49 KGLH0^c22c7d28
& &&&49 KGLH0^1a8436ae
& &&&45 KGLH0^a2ac011a
& &&&35 KGLH0^d9085754
& &&&35 KGLH0^c5be8292
$ cut -c53-57 duration1-2 | sort | uniq -c | sort -rn | head -5
& &8197 KGLH0& && && && && & &-- So cut wit we see 8197 entries for heap 0 in duration 2
& & 175 PRTMV
& & 115 PRTDS
$ cut -c53-67 duration1-3 | sort | uniq -c | sort -rn | head
& &2810 SQLA^b3947bfc
& &1168 SQLA^52ccb2f2
& &1104 SQLA^d7bcc960
& &1020 SQLA^1a8436ae
& & 401 SQLA^8bfc3f48
& & 297 SQLA^c22c7d28
& & 240 SQLA^a2ac011a
& & 234 SQLA^b91ee9fa
[oracle@dctrpdbms3b trace]$ cut -c53-57 duration1-3 | sort | uniq -c | sort -rn | head -5
&&18019 SQLA^& && && && && & &-- re we see most entries in duration 3 are SQL Area
& &1022 KGLS^
& & 757 PLMCD
& & 454 PLDIA
论坛徽章:0
Yong Huang 发表于
Is your account locked? If you can't reply, email me at .
A shared pool has subp ...
受益匪浅，谢谢！
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号oracle实验记录 （oracle 分析shared pool(2)）-fufuh2o-ITPUB博客
oracle实验记录 （oracle 分析shared pool(2)）
1044阅读　0评论　
分析shared pool结构与控制
dictionary cache:当一条SQL语句 比如select * from t1进入 shared pool library cache前，oracle服务进程会到dictionary cache（row cache）找与t1 table相关的 数据字典信息（表名，列名，权限等）如果没找到从system tablespace数据字典里读入buffer cache（从disk读入），然后将这些数据字典信息按行的方式 放入shared pool dictionary cache，然后再从dictionary cache取信息放入library cache
library cache：存SQL语句，SQL语句解析树，SQL语句执行计划，还存放控制结构(lock、pin、dependency table等)，也存放从dictionary cache获得的对象信息.library cache使用hash 算法得到一个值 就是 hash bucket SQL语句应用hash算法计算出所在的hash bucket 进入该hash buckt进行扫描确定是否存在相同的语句 存在，当该bucket上 此对象的library cache handle不存在 就是第一次执行此时进程会构建一个library cache handle挂到该bucket上 装载对象 ，如果该对象的library cache handle存在但该handle指向的对象已经换出内存，此时对象将重新装载reload。&library cache使用多个hash bucket管理 每个hash bucket上串联多个 library cache handle（存放对象name,namespace,对象标记,heap0对象的指针,heap0 存对象类型，相关的 表 ，实际 执行计划，执行pl/sql的机器码等,heap是由一个或多个chunk组成，这些chunk分布在library cache中 不需要连续）形成一个双向链表
libarary cachehash bucket -------library cache handle-library cache handle(LCO(LIBRARY CACHE object))hash bucket.........
SQL> select table_name ,owner from all_tables where table_name='T1';
TABLE_NAME&&&&&&&&&&&&&&&&&&&& OWNER------------------------------ ------------------------------T1&&&&&&&&&&&&&&&&&&&&&&&&&&&& TRT1&&&&&&&&&&&&&&&&&&&&&&&&&&&& XH
SQL> show userUSER 为 "XH"SQL> select * from t1;
&&&&&&&& A----------&&&&&&&& 1
SQL> show userUSER 为 "TR"SQL> select * from t1;
&&&&&&&& A----------&&&&&&&& 2
SQL> select sid,PREV_HASH_VALUE,SQL_HASH_VALUE from v$session where username='XH';
&&&&&& SID PREV_HASH_VALUE SQL_HASH_VALUE---------- --------------- --------------&&&&&& 120&&&&& &&&&&&
SQL> select sid,PREV_HASH_VALUE,SQL_HASH_VALUE from v$session where username='TR';
&&&&&& SID PREV_HASH_VALUE SQL_HASH_VALUE---------- --------------- --------------&&&&&& 123&&&&& &&&&&&&&&&&&& 0
SQL> col sql_text format a40SQL> select sql_text,executions,PARSE_CALLS,child_number from v$sql where hash_value='';
SQL_TEXT&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& EXECUTIONS PARSE_CALLS CHILD_NUMBER---------------------------------------- ---------- ----------- ------------select * from t1&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1&&&&&&&&&& 1&&&&&&&&&&& 0select * from t1&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1&&&&&&&&&& 1&&&&&&&&&&& 1
SQL> col sql_text format a30SQL> select sql_text,version_count,executions,PARSE_CALLS,kept_versions from v$sqlarea where hash_value='';
SQL_TEXT&&&&&&&&&&&&&&&&&&&&&& VERSION_COUNT EXECUTIONS PARSE_CALLS------------------------------ ------------- ---------- -----------KEPT_VERSIONS-------------select * from t1&&&&&&&&&&&&&&&&&&&&&&&&&& 2&&&&&&&&& 2&&&&&&&&&& 2&&&&&&&&&&& 0
SQL> desc v$object_&名称&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 是否为空? 类型&----------------------------------------- -------- ----------------------------
&FROM_ADDRESS&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& RAW(4)&FROM_HASH&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NUMBER&TO_OWNER&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& VARCHAR2(64)&TO_NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& VARCHAR2(1000)&TO_ADDRESS&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& RAW(4)&TO_HASH&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NUMBER&TO_TYPE& &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NUMBER&FROM_ADDRESS& :一个包过程游标装载到shared pool中 指向语句的地址&FROM_HASH:一个包过程游标在 shared pool中hash value&TO_OWNER:对象依赖拥有者&TO_NAME:依赖对象名&TO_ADDRESS:依赖对象的handle&TO_HASH：依赖对象的 hash 值&TO_TYPE:依赖对象的 类型
SQL> select from_address,from_hash,to_owner,to_name,to_address,to_char(to_hash,'xxxxxxxx') from v$object_dependency where to_name='T1';
FROM_ADD& FROM_HASH TO_OWNER&& TO_NAME&&& TO_ADDRE TO_CHAR(T-------- ---------- ---------- ---------- -------- ---------1C6B055 XH&&&&&&&& T1&&&&&&&& 18F71378& bbf0d9e5 ~~~~~~~来自不同的对象,所以有2个子游标,2个执行计划1C6B055 TR&&&&&&&& T1&&&&&&&& d0f9e97
ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';
使用 name既SQL语句本身 or 1C6B7124(v$object_dependency.from_address一个包过程游标装载到shared pool中 指向语句的地址) 在trace中找BUCKET 92407:& LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2)& name=select * from t1& hash=cac9b23ea1a timestamp=10-08-:22& namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[]& kkkk-dddd-llll=01 lock=0 pin=0 latch#=2 hpc=0000 hlc=0000& lwt=1C6BBB7180] ltm=1C6BBB7188]& pwt=1C6BBB7164] ptm=1C6B716C[1C6B716C,1C6B716C]& ref=1C6B71A0[1C6B71A0,1C6B71A0] lnd=1C6B71AC[20AB00]&&& LIBRARY OBJECT: bject=1de670c0&&& type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0&&& CHILDREN: size=16&&& child#&&& table reference&& handle&&& ------ -------- --------- --------&&&&&&&& 0 1de6704c& 1de66d00 1c50ed24~~~~这个handle 就是不同子游标指向各自的heap的指针&&&&&&&& 1 1de6704c& 1de66e60 &&& DATA BLOCKS:&&& data#&&&& heap& pointer&&& status pins change whr alloc(K)& size(K)&&& ----- -------- -------- --------- ---- ------ --- -------- --------&&&&&&& 0 18eaf050 1de67158 I/P/A/-/-&&& 0 NONE&& 00&&&&& 0.67&&&& 1.05&&& HEAP DUMP OF DATA BLOCK 0:******************************************************HEAP DUMP heap name="PCursor"& desc=18EAF050&extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=0&parent=03C38510 wner= nex= xsz=0x424EXTENT 0 addr=1DE66C68& Chunk 1de66c70 sz=&&&&& 596&&& perm&&&&& "perm&&&&&&&&&& "& alo=520Dump of memory from 0x1DE66C70 to 0x1DE66EC41DE66C70 0B0 & [U..@.....p......]1DE66C80 00 & [................]1DE66C90 1DE66CF0 00& [.l..............]
查找heap 0,用 子游标的handle& 1c50ed24 需要共享到这个heap0才算 真正共享执行计划~~~否则即使SQL语句一样也会产生不同执行计划,产生子游标,并且第一次产生的话是hard parse.
LIBRARY OBJECT HANDLE: handle=1c50ed24 mutex=1C50EDD8(0)& namespace=CRSR flags=RON/KGHP/PN0/[]& kkkk-dddd-llll=41 lock=0 pin=0 latch#=2 hpc=0000 hlc=0000& lwt=1C50ED80[1C50ED80,1C50ED80] ltm=1C50ED88[1C50ED88,1C50ED88]& pwt=1C50ED64[1C50ED64,1C50ED64] ptm=1C50ED6C[1C50ED6C,1C50ED6C]& ref=1C50EDA0[1DE66D00,1DE66D00] lnd=1C50EDAC[1C50EDAC,1C50EDAC]&&& LIBRARY OBJECT: bject=1de66860&&& type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0&&& DEPENDENCIES: count=1 size=16&&& dependency#&&& table reference&& handle position flags&&& ----------- -------- --------- -------- -------- -------------------&&&&&&&&&&&&& 0 1de823cc& 1de820cc 20a7817c&&&&&& 14 DEP[01]&&& READ ONLY DEPENDENCIES: count=1 size=16&&& dependency#&&& table reference&& handle flags&&& ----------- -------- --------- -------- -------------------&&&&&&&&&&&&& 0 1de66c1c& 1deb7124 /ROD/KPP[60]&&& ACCESSES: count=1 size=16&&& dependency# types&&& ----------- -----&&&&&&&&&&&&& 0 0009&&& TRANSLATIONS: count=1 size=16&&& original&&& final&&& -------- --------&&& 20a7c&&& DATA BLOCKS:&&& data#&&&& heap& pointer&&& status pins change whr alloc(K)& size(K)&&& ----- -------- -------- --------- ---- ------ --- -------- --------&&&&&&& 0 20af3c38 1de669b0 I/-/A/-/-&&& 0 NONE&& 00&&&&& 2.27&&&& 3.14&&&&&&& 6 1debec8 I/-/A/-/-&&& 0 NONE&& 00&&&&& 2.88&&&& 4.00
看到data#部分0 就是heap 0, 6就是heap 6,其中heap 列就是在内存中的实际地址&&& HEAP DUMP OF DATA BLOCK 0:******************************************************HEAP DUMP heap name="CCursor"& desc=20AF3C38&extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=3&parent=03C38510 wner=1DE66860 nex= xsz=0x424EXTENT 0 addr=1DE81FE8& Chunk 1de81ff0 sz=&&&&& 636&&& perm&&&&& "perm&&&&&&&&&& "& alo=392Dump of memory from 0x1DE81FF0 to 0x1DE8226C1DE81FF0 DE66BA8 & [}..@.....k......]1DE10 0DEB08C& [........`h......]1DE00 00& [................]
******************************************************&&& HEAP DUMP OF DATA BLOCK 6:******************************************************HEAP DUMP heap name="sql area"& desc=1DE82000&extent sz=0xff4 alt=32767 het=16 rec=0 flg=2 pc=2&parent=03C38510 wner=1DE66860 nex= xsz=0xfe4EXTENT 0 addr=1D19B08C& Chunk 1d19b094 sz=&&&& 1116&&& free&&&&& "&&&&&&&&&&&&&& "Dump of memory from 0x1D19B094 to 0x1D19B4F01D19B090&&&&&&&&& CDE8206C&&&&& [].......l ..]1D19B0A0 1DEA26C 1D1BA114 & [l ..l...........]1D19B0B0 D19B098 607DA5C& [..........}`\...]1D19B0C0 534B5 000015& [..KSUSEFLG......]1D19B0D0 1D19B0B0 607DB160 1D1FC834& [......}``...4...]1D19B0E0 D19B0CC 607B796C & [u.......ly{`....]
以下解释(来自IT168)
&&& Heap是通过调用服务器进程进行分配的，任何对象都具有heap 0，至于还应该分配哪些其他的heap则是由对象的类型决定的，比如SQL游标具有heap 1和 6，而PL/SQL程序包则具有heap 1、2、3和4。按照heap的使用情况，oracle会在SGA（library cache）、PGA或UGA中分配heap，但是heap 0始终都是在library cache中进行分配的。如果所请求的heap已经在SGA中分配了，则不会在PGA中再次分配heap。Heap是由一个或多个chunk组成的，这些 chunk可以是分散的分布在library cache中的，不需要连续分布。
1) object type：library cache中的对象类型包括：表、视图、索引、同名词等等。每个对象只能有一个object type，根据object type将对象归类到不同的namespace里。一个object type对应一个namespace，但是一个namespace可能对应多个object type。这样的话，查找一个对象时，只要在该对象所属的namespace中去找就可以了。比较常见的namespace包括： a) SQL AREA：也可以叫做CRSR，表示shared cursor，存放共享的SQL语句。 b) TABLE/PROCEDURE：存放的object type包括：table、view、sequence、synonym、 procedure的定义、function的定义以及package的定义。 c) BODY：存放procedure的实际代码、function的实际代码以及package的实际代码。 d) TRIGGER：存放的object type为trigger。 e) INDEX：存放的object type为index。 2) object name：对象名称由三部分组成： a) Schema的名称，对于共享游标（SQL语句或PL/SQL程序块）来说为空。 b) 对象名称。分为两种情况：对于共享游标（SQL语句或PL/SQL程序块）来说，其对象名称就是SQL的语句本身；而对于其他对象（比如表、视图、索引等）就是其在数据字典中的名称。 c) Database link的名称。这是可选的，如果是本地对象，则为空。 这样，对象的名称的格式为：。比如，可以为，也可以为hr.employees等。 3) flags：flags主要用来描述对象是否已经被锁定。对象具有三种类型的flag： a) public flag：表示对象上没有锁定（pin）或者latch。 b) status flag：表示对象上存在锁定（pin），说明对象正在被创建或删除或修改等。 c) specitial flag：表示对象上存在library cache latch。 4) tables：对每个对象，都会维护以下一串tables中的若干个： a) dependency table：含有当前对象所依赖的其他对象。比如一个视图可能会依赖其组成的多个表、一个存储过程可能依赖其中所调用的其他存储过程、一个游标可能依赖其中所涉及到的多个表等。Dependency table中的每个条目都指向一块物理内存，该物理内存中含有当前对象所依赖的对象的句柄。 b) child table：含有当前对象的子对象，只有游标具有child table。Child table中的每个条目都指向一个可执行的SQL命令所对应的句柄。 c) translation table：包含当前对象所引用的名称是如何解释为oracle底层对象的名称，只有游标具有translation table。 d) authorization table：包含该对象上所对应的权限，一个条目对应一个权限。 e) access table：对于dependency table中的每一个条目，都会在access table中存在对应的一个或多个条目。比如，假设对象A依赖对象B，那么在A的dependency table和access table中都会存在一个条目指向B。位于access table中的指向B的条目说明了对B具有什么样的访问类型，从而也就说明了用户要执行A则必须具有对B的权限。 f) read-only dependency table：类似于dependency table，但是存放只读的对象。 g) schema name table：包含authorization table中的条目所属的schema。 5) data blocks：对象的其他信息会存放在不同的heap中，为了找到这些heap，会在heap 0中存放多个（最多16个，但是这16个data block不会都用到）data blocks结构，每个data block含有指向这些实际heap内存块的指针。 除了heap 0以外，还有11个heap，根据对象的不同进行分配，并存放了不同的内容： 1) Heap 1：存放PL/SQL对象的源代码。 2) Heap 2：存放PL/SQL对象的解析树，这有个好听的名字： DIANA。 3) Heap 3：存放PL/SQL对象的伪代码。 4) Heap 4：存放PL/SQL对象的基于硬件的伪代码。 5) Heap 5：存放了编译时的错误信息。 6) Heap 6：存放了共享游标对象的SQL文本。 7) Heap 7：可用空间。 8) Heaps 8–11：根据对象的不同而使用的子heap。
用heap0中 依赖表部分 这个handle 20a7817c 找到实际依赖对象为tr.t1 &LIBRARY OBJECT HANDLE: handle=20a7817c mutex=20A78230(0)& name=TR.T1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~& hash=ef05a2cf9e97 timestamp=10-08-:28& namespace=TABL flags=KGHP/TIM/SML/[]& kkkk-dddd-llll=01 lock=N pin=0 latch#=2 hpc=0002 hlc=0002& lwt=20A781D8[20A781D8,20A781D8] ltm=20A781E0[20A781E0,20A781E0]& pwt=20A781BC[20A781BC,20A781BC] ptm=20A781C4[20A781C4,20A781C4]& ref=20A781F8[20A781F8,20A781F8] lnd=20AAF434,1C6B71AC]&&& LIBRARY OBJECT: bject=1de81be0&&& type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0&&& DATA BLOCKS:&&& data#&&&& heap& pointer&&& status pins change whr alloc(K)& size(K)&&& ----- -------- -------- --------- ---- ------ --- -------- --------&&&&&&& 0 1c5fc78 I/-/A/-/-&&& 0 NONE&& 00&&&&& 0.52&&&& 0.00&&&&&&& 8 1de81e08 1dd61214 I/-/A/-/-&&& 0 NONE&& 00&&&&& 0.39&&&& 1.05&&&&&&& 9 1de81ea0 1dd60a24 I/-/A/-/-&&& 0 NONE&& 00&&&&& 0.17&&&& 1.05&&&&&& 10 1de81ef0 1df09b30 I/-/A/-/-&&& 0 NONE&& 00&&&&& 0.11&&&& 1.05&&& HEAP DUMP OF DATA BLOCK 0:******************************************************HEAP DUMP heap name="Heap0: KGL"& desc=1C5F5928&extent sz=0x424 alt=32767 het=16 rec=9 flg=2 pc=0&parent=03C38510 wner=1DE81BE0 nex= xsz=0x414EXTENT 0 addr=1DE81BC8& Chunk 1de81bd0 sz=&&&& 1036&&& perm&&&&& "perm&&&&&&&&&& "& alo=880Dump of memory from 0x1DE81BD0 to 0x1DE81FDC1DE81BD0 70& [...P........p...]1DE81BE0 20ABE4 1DE81BE4 1DE81BEC& [|.. ............]1DE81BF0 1DE81BEC 05& [................]
关于library cache的控制:oracle 用lock,pin,library cache latch(修改时用) 控制shared pool library cache结构,如果oracle进程需要修改library cache中信息 ,那么再对应的bucket上,先要获得library cache latch,再接着在相应的handle(handle 可以理解为library cache object 的buffer header)上获得lock,再在heap上获得pin修改完后释放 PIN,LOCK ,LIBRARY CACHE LATCH
比如user 1 进程 向library cache 填加一个子游标,此时user 2进程也填加 它们俩所填加的子游标完全一样 这样就浪费shared pool空间,还需要防止heap 内存被多个进程同时写入
当用户 比如实验中 user xh,执行select * from t1时 对这条SQL语句进行hash运算,找到了bucket& 92407 中对应的library cache& handle(bucket 就是由 多个library cache handle 串联的 双项链表),但发现不能共享以前的执行计划因为T1来自不同的schema,这时候 需要产生新的执行计划,新的子游标 向bucket 92407 中对应的library cache object handle 的 CHILDREN中添加一个新handle指向heap0 新的执行计划,解析树,对象信息等,此时这个进程 必须获得 这个library cache& handle上的lock,其他的进程要等待(library cache lock 等待时间 就是这么产生的,方式并发 若另一个进程也添加 同样的children信息 那浪费空间了)
另外 如果根据SQL语句HASH 找到了bucket找到了 library cache handle 但里面的子游标handle(指向heap 0)不存在了 表明可能语句执行过,但换了出去,那么将该sql语句的handle(游标handle)重新加载到shared pool (v$librarycache.reloads)
SQL> select event,total_waits from v$system_event where event='library cache lock';
EVENT&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& TOTAL_WAITS---------------------------------------------------------------- -----------library cache lock&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 4
This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:
One client can prevent other clients from accessing the same object
The client can maintain a dependency for a long time (for example, no other client can change the object)
This lock is also obtained to locate an object in the library cache.
Wait Time: 3 seconds (1 second for PMON)
Parameter Description handle address Address of the object being loaded lock address Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object. mode Indicates the data pieces of the object which need to be loaded namespace See "namespace"
lock 分3类:null,shared,exclusive,读取取时一般是获取null,shared,此时其他进程也可以对相同的handle 加null,shared 的lock,修改时,此时要exclusive 的lock,此时其他进程不能再加exclusived的lock,只能加null 的lock
关于pin:pin是针对heap,主要是防止多个进程对一个heap(内存)进行更新操作,获取PIN 前必须获取lock,实验中当user xh执行SQL语句时,在bucket bucket& 92407中先获得library cache lock 查看发现子游标不能共享(来自不同的SCHEMA) 此时将产生一个hard parse,生成新的执行计划,新子游标 要写到这个bucket 的library cache handle中,shared pool会分配chunk(连续内存)做为heap 0,heap 6等,获得该heap上的 PIN ,并在该bucket的 对应 library cache handle 中的children部分加一条子游标记录 其handle 指向heap 0 然后 向PIN主的HEAP上写记录,然后释放PIN,然后释放lock ,PIN不住就是等待事件library cache pin,如果heap不在 将heap加载到library cache 同时pin住
PIN有2种模式:shared ,exclusive,读时 shared pin (此时其它进程不能 exclusive pin),修改时 先shared pin heap进行错误安全检查,如果没错就是exclusive pin heap,然后修改
********************总结:整体shared pool 操作过程 基本是这样的:oracle先 将sql语句进行hash 运算,获取library cahe latch(保护内存中执行计划等,解析时候向library cache中加新执行计划时候需要获取该latch)根据sql hash_value在shared pool=>library cache=>bucket=>library cache handle 上先获得library cache lock(shared ,null 获取lock防止并发修改handle) 然后查找该handle子游标信息 其中子游标信息handle 指向heap 0,获得这个HEAP 0的 PIN(SHARED),如果可以共享那么就使用这个执行计划对象信息 解析树等 是一次soft parse,如果不能共享 比如来自不同schema,SQL语句引用列的 数据类型不一样等
如果不是这样的话oracle将释放library cache latch 获得shared pool latch(负责分配shared pool空间的latch)分配自由空间chunk 为heap (heap 0,heap 6等)分配后 释放shared pool latch 再获得library cache latch(解析过程插入新执行计划时需要它)并且获得 该library cache handle的 lock(exclusive因为要修改handle加入新的子游标信息)然后获得 分配的chunk 既这些chunk组成的heap 0,6等 获得这些heap上的 pin(exclusive),并在该bucket的 对应 library cache handle ,中的children部分加一条子游标记录 其handle 指向heap 0 (新分配的)然后 向PIN主的HEAP上写记录,然后释放PIN ,SQL及其执行计划写入library cahce 后,释放library cache latch 保持null模式的library cache lock,这是一个hard prase.
从实验中看当user xh执行时先用hash value 找到& BUCKET 92407:& 持有library cache latch& LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2)& name=select * from t1获得一个null的lock 查看 chidren信息
&&& CHILDREN: size=16&&& child#&&& table reference&& handle&&& ------ -------- --------- --------&&&&&&&& 0 1de6704c& 1de66d00 1c50ed24~~~~这个handle 就是不同子游标指向各自的heap的指针(不是user xh执行的)&&&&&&&& 1 1de6704c& 1de66e60 (先忽略这个)
根据 1c50ed24找到LIBRARY OBJECT HANDLE: handle=1c50ed24 mutex=1C50EDD8(0)& namespace=CRSR flags=RON/KGHP/PN0/[]获取上面的lock (NULL,SHARED) PIN(SHARED)进行查看从依赖表部分发现来自不同的SCHEMA ,需要产生新执行计划,此时候释放library cache latch,获得shared pool latch 分配chunk为heap,释放shared pool latch 后 再获得library cache latch 并且获得lock(exclusive) 因为(要往 BUCKET 92407:& LIBRARY OBJECT HANDLE: handle=1c6b7124 mutex=1C6B71D8(2) 这个handle上添加 子游标信息了)
& 1 1de6704c& 1de66e60
添加了这条,然后到新分配的heap 所在的bucket上 获得lock(exclusive)
BUCKET 92501&LIBRARY OBJECT HANDLE: handle=20a7817c mutex=20A78230(0)&& 在这个 handle中添加信息 (lock(exclusive) )& name=XH.T1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~并在heap上获得pin(exclusive)添加新执行计划SQL代码,解析树等
释放library cache latch,释放PIN ,LOCK
************************
关于library cache latch:保护library cache中的 SQL及执行计划,当oracle搜索library cache查找匹配的SQL及执行计划,若没有oracle将进行hard parse,获得library cache latch向library cache中加 新执行计划SQL代码等，搜索bucket时也要持有library cache latch.另外 LIBRARY CACHE& LATCH 也管理LOCK 获得lock前必须持有library cache latch，获得不到latch那么将等待,lock结束 释放library cache latch
关于library cache latch的数量由& _kgl_latch_count 控制
NAME&&&&&&&&&&&&&&&&&&&&&&&&&& VALUE&&&&&&&&&&&&&&&&&&&& ISDEFAULT ISMOD&&&&& ISADJ
------------------------------ ------------------------- --------- ---------- -----
KSPPDESC------------------------------------------------------------------------------------------------------------------------------------_kgl_latch_count&&&&&&&&&&&&&& 0&&&&&&&&&&&&&&&&&&&&&&&& TRUE&&&&& FALSE&&&&& FALSE
number of library cache latches
我的是 10GR2
0 simply means it's default. Actual number of latches is determined by cpu_count. Manually setting cpu_count can change the number of library cache child latches (check in v$latch_children).
该参数缺省值为大于等于系统中CPU个数的最小的素数
SQL> select name,gets,misses,sleeps from v$latch_children where name='library cache';
NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& GETS&&&& MISSES-------------------------------------------------- ---------- ----------&&& SLEEPS----------library cache&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 212571&&&&&&& 102&&&&&&&& 5
library cache&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 192186&&&&&&&& 35&&&&&&&& 1
library cache&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 232298&&&&&&&& 53&&&&&&&& 3
SQL> col name format a30SQL> select name,gets,misses,sleeps from v$latch_children where name='library cache';
NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& GETS&&&& MISSES&&&& SLEEPS------------------------------ ---------- ---------- ----------library cache&&&&&&&&&&&&&&&&&&&&& 213125&&&&&&& 102&&&&&&&&& 5library cache&&&&&&&&&&&&&&&&&&&&& 192499&&&&&&&& 35&&&&&&&&& 1library cache&&&&&&&&&&&&&&&&&&&&& 232640&&&&&&&& 53&&&&&&&&& 3
library cache latch负责哪个bucket ,latch号=mod(bucket号,latch的数量)
SQL> select latch#,name,gets,misses,sleeps from v$latch_children where name='shared&pool';
&&& LATCH# NAME&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& GETS&&&& MISSES&&&& SLEEPS---------- ------------------------------ ---------- ---------- ----------&&&&&& 213 shared pool&&&&&&&&&&&&&&&&&&&&&&&&&&& 48&&&&&&&&& 0&&&&&&&&& 0&&&&&& 213 shared pool&&&&&&&&&&&&&&&&&&&&&&&&&&& 48&&&&&&&&& 0&&&&&&&&& 0&&&&&& 213 shared pool&&&&&&&&&&&&&&&&&&&&&&&&&&& 48&&&&&&&&& 0&&&&&&&&& 0&&&&&& 213 shared pool&&&&&&&&&&&&&&&&&&&&&&&&&&& 48&&&&&&&&& 0&&&&&&&&& 0&&&&&& 213 shared pool&&&&&&&&&&&&&&&&&&&&&&&&&&& 48&&&&&&&&& 0&&&&&&&&& 0&&&&&& 213 shared pool&&&&&&&&&&&&&&&&&&&&&&&&&&& 48&&&&&&&&& 0&&&&&&&&& 0&&&&&& 213 shared pool&&&&&&&&&&&&&&&&&&&&&&& 308211&&&&&&& 227&&&&&&&& 43
已选择7行。 只有一个shared pool latch,受sub pool影响每个sub pool都有一个shared pool latch ,_kghdsidx_count控制sub pool数 既控制shared pool latch数量
EYGEL大牛有一个比较经典针对library cache pin的等待 用grant 制造的等待,不过我现在的版本模拟不出来了,10R2 PIN 基本要被mutex 一种互斥机制取代了但还是采用重新编译或drop 也可以做出来 library cache pin& 并针对这个PIN进行分析跟踪解决
SQL>~~hang
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';
会话已更改。
BUCKET 51272:& LIBRARY OBJECT HANDLE: handle=2c93d46c mutex=2C93D520(1)& name=BEGIN END;& hash=55ee1ce42b9d8e98cc848 timestamp=10-10-:23& namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[]& kkkk-dddd-llll=01 lock=N pin=0 latch#=1 hpc=0004 hlc=0004& lwt=2C93D4C8[2C93D4C8,2C93D4C8] ltm=2C93D4D0[2C93D4D0,2C93D4D0]& pwt=2C93D4AC[2C93D4AC,2C93D4AC] ptm=2C93D4B4[2C93D4B4,2C93D4B4]& ref=2C93D4E8[2C93D4E8,2C93D4E8] lnd=2C93D4F4[2C94A9C8,2C93E2C0]&&& LIBRARY OBJECT: bject=2ef6165c&&& type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0&&& CHILDREN: size=16&&& child#&&& table reference&& handle&&& ------ -------- --------- --------&&&&&&&& 0 2ef615e8& 2efd388&&& DATA BLOCKS:&&& data#&&&& heap& pointer&&& status pins change whr alloc(K)& size(K)&&& ----- -------- -------- --------- ---- ------ --- -------- --------&&&&&&& 0 ef616f4 I/P/A/-/-&&& 0 NONE&& 00&&&&& 0.67&&&& 1.05& BUCKET 51272 total object count=1
BUCKET 47958:& LIBRARY OBJECT HANDLE: handle=2c93dcfc mutex=2C93DDB0(0)& name=SYS.CALLING & hash=eeae667a13b1f1e561fbb56 timestamp=10-10-:05& namespace=TABL flags=KGHP/TIM/SML/[]& kkkk-dddd-llll=bf lock=N pin=S latch#=3 hpc=0006 hlc=0006& lwt=2C93DD58[2C93DD58,2C93DD58] ltm=2C93DD60[2C93DD60,2C93DD60]& pwt=2C93DD3C[2C93DD3C,2C93DD3C] ptm=2C93DD44[2C93DD44,2C93DD44]& ref=2C93DD78[2C93DD78,2C93DD78] lnd=2C93DD84[2C9DFC0]&&& LIBRARY OBJECT: bject=3126ab60&&& type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0&&& DEPENDENCIES: count=4 size=16&&& dependency#&&& table reference&& handle position flags&&& ----------- -------- --------- -------- -------- -------------------&&&&&&&&&&&&& 0 3126af1c& 3126acb4 2c946f38&&&&&&& 0 DEP[01]&&&&&&&&&&&&& 1 3126af1c& 3126ace8 33e94310&&&&&&& 0 DEP[01]&&&&&&&&&&&&& 2 3126af1c& e70f94&&&&&&& 0 DEP[01]&&&&&&&&&&&&& 3 3126af1c& e14d10&&&&&&& 0 DEP[01]&&& ACCESSES: count=2 size=16&&& dependency# types&&& ----------- -----&&&&&&&&&&&&& 0 000c&&&&&&&&&&&&& 2 000c&&& DATA BLOCKS:&&& data#&&&& heap& pointer&&& status pins change whr alloc(K)& size(K)&&& ----- -------- -------- --------- ---- ------ --- -------- --------&&&&&&& 0 33e810ec 3126abf8 I/P/A/-/-&&& 0 NONE&& 00&&&&& 0.75&&&& 0.00&&&&&&& 2 b9a1b58 I/-/A/-/-&&& 0 NONE&& 00&&&&& 4.93&&&& 8.00&&&&&&& 4 b99db58 I/P/A/-/-&&& 2 NONE&& 00&&&&& 0.63&&&& 4.00& BUCKET 47958 total object count=1
可以看到这时 访问这个HEAP0 那么lock为 NULL,PIN为Shared 不许添加exclusive pin
SQL> alter proce~~hang
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 16';
会话已更改。
BUCKET 47958:& LIBRARY OBJECT HANDLE: handle=2c93dcfc mutex=2C93DDB0(0)& name=SYS.CALLING & hash=eeae667a13b1f1e561fbb56 timestamp=10-10-:05& namespace=TABL flags=KGHP/TIM/SML/[]& kkkk-dddd-llll=bf lock=X pin=S latch#=3 hpc=0006 hlc=0006& lwt=2C93DD58[2C93DD58,2C93DD58] ltm=2C93DD60[2C93DD60,2C93DD60]& pwt=2C93DD3C[31B1C61C,31B1C61C] ptm=2C93DD44[2C93DD44,2C93DD44]& ref=2C93DD78[2C93DD78,2C93DD78] lnd=2C93DD84[2C9DFC0]&&& LIBRARY OBJECT: bject=3126ab60&&& type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0&&& DEPENDENCIES: count=4 size=16&&& dependency#&&& table reference&& handle position flags&&& ----------- -------- --------- -------- -------- -------------------&&&&&&&&&&&&& 0 3126af1c& 3126acb4 2c946f38&&&&&&& 0 DEP[01]&&&&&&&&&&&&& 1 3126af1c& 3126ace8 33e94310&&&&&&& 0 DEP[01]&&&&&&&&&&&&& 2 3126af1c& e70f94&&&&&&& 0 DEP[01]&&&&&&&&&&&&& 3 3126af1c& e14d10&&&&&&& 0 DEP[01]&&& ACCESSES: count=2 size=16&&& dependency# types&&& ----------- -----&&&&&&&&&&&&& 0 000c&&&&&&&&&&&&& 2 000c&&& DATA BLOCKS:&&& data#&&&& heap& pointer&&& status pins change whr alloc(K)& size(K)&&& ----- -------- -------- --------- ---- ------ --- -------- --------&&&&&&& 0 33e810ec 3126abf8 I/P/A/-/-&&& 0 NONE&& 00&&&&& 0.75&&&& 0.00&&&&&&& 2 b9a1b58 I/-/A/-/-&&& 0 NONE&& 00&&&&& 4.93&&&& 8.00&&&&&&& 4 b99db58 I/P/A/-/-&&& 2 NONE&& 00&&&&& 0.63&&&& 4.00& BUCKET 47958 total object count=1
此时执行修改,那么lock 为exclusive,以前是NULL 所以现在可以加上,但PIN 已经是shared 所以 EXCLUSIVE不可以加上,所以等待 产生library cache pin
具体抓到 堵塞SQL 及其 执行SEESIONSQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%'& 2& ;
&&&&&& SID&&&&&& SEQ#---------- ----------EVENT&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& P1---------------------------------------------------------------- ----------P1RAW&&&&&&&&&&& P2 P2RAW&&&&&&&&&&& P3 P3RAW&&& STATE-------- ---------- -------- ---------- -------- -------------------&&&&&& 139&&&&&&&& 53library cache pin&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 2C93DCFC& B1C600&&&&&&& 301 0000012D WAITING
查看等待 发现,有library cache pin ,关于里面p1,p2,p3值的意义 可以查看 p1text,p2text,p3text, 查看后p1text: handle address表示这个HANDLE的地址 trace中BUCKET 47958 上有这个handle
SQL> col KGLNAOWN for a10SQL> col KGLNAOBJ for a20SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ& 2& from X$KGLOB& 3& where KGLHDADR ='2C93DCFC';
ADDR&&&& KGLHDADR KGLHDPAR KGLNAOWN&& KGLNAOBJ&&&&&&&&&&&&&& KGLNAHSH KGLHDOBJ-------- -------- -------- ---------- -------------------- ---------- --------C93DCFC 2C93DCFC SYS&&&&&&& CALLING&&&&&&&&&&&&&& 26AB60
查看 谁持有这个handle 可以看到owner sys, 对象是calling ,KGLNAHSH:object hash value
SQL> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ from v$session a,x$kglpn b where a.saddr=b.kglpnuse and b.kglpnhdl = '2C93DCFC' and b.KGLPNMOD0;
&&&&&& SID USERNAME---------- ------------------------------PROGRAM&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& ADDR---------------------------------------------------------------- --------KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK&& KGLPNMOD&& KGLPNREQ-------- -------- -------- -------- -------- ---------- ----------
&&&&&& 133 SYSsqlplus.exe&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 0805FB9831BE1B5C
2C93DCFC 31BD5544&&&&&&&&& 2&&&&&&&&& 0
查到 持有这个HANDLE的SESSION SID
SQL> select sid,seq#,event,p1text,state from v$session_wait where sid=133;
&&&&&& SID&&&&&& SEQ#---------- ----------EVENT----------------------------------------------------------------P1TEXT----------------------------------------------------------------STATE-------------------&&&&&& 133&&&&&&&&& 9PL/SQL lock timerdurationWAITING
查看这个占有handle的用户在做什么
用sid 查到hash_value 查到他的 sql_text
SQL> select PREV_HASH_VALUE,sql_hash_value from v$session where sid=133;
PREV_HASH_VALUE SQL_HASH_VALUE--------------- --------------&&&&&&&&&&&&& 0&&&&
trace中& BUCKET 51272
SQL> select sql_text from v$sqlarea where hash_value='';
SQL_TEXT--------------------------------------------------------------------------------BEGIN END;
另一个SESSIONSQL> alter proce hang
这会产生lock因为handle上已经有了 EXCLUSIVE的lock 所以加不上了 ,library cache lock出现
具体的抓SQL方法跟上面一样
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%';
&&&&&& SID&&&&&& SEQ#---------- ----------EVENT&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& P1---------------------------------------------------------------- ----------P1RAW&&&&&&&&&&& P2 P2RAW&&&&&&&&&&& P3 P3RAW&&& STATE-------- ---------- -------- ---------- -------- -------------------&&&&&& 139&&&&&&&& 53library cache pin&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 2C93DCFC& B1C600&&&&&&& 301 0000012D WAITING
&&&&&& 159&&&&&&&&& 9library cache lock&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 2C93DCFC& BD5B64&&&&&&& 301 0000012D WAITING
&&&&&& SID&&&&&& SEQ#---------- ----------EVENT&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& P1---------------------------------------------------------------- ----------P1RAW&&&&&&&&&&& P2 P2RAW&&&&&&&&&&& P3 P3RAW&&& STATE-------- ---------- -------- ---------- -------- -------------------
现在3G网络，都不怎么好，也就中移动拔尖点。现在中国移动还大力推广OPhone 手机操作系统和手机,先进的平台和人性化的操作界面，将得到广大手机用户的追捧，为手机软件市场带来巨大商机。欢迎程序员访问。/
北京皓辰网域网络信息技术有限公司. 版权所有