Oracle Bb_cache_size,Oracle Library cache 内部机制 说明

二. 测试

在上面的说明中，提到，对于多child cursor，所有的child cursor 都保存在child table里，并且SQL 的执行计划也保存在child cursor的Heap 6中。 下面进行相关的测试，证明以上结论。

SYS@anqing1(rac1)> create table lct asselect * from dba_objects;

Table created.

分别用SYSTEM和Dave 用户执行如下SQL:

/* Formatted on 2011/7/24 15:07:20(QP5 v5.163.1008.3004) */

DECLARE

x     VARCHAR2 (20);

str   VARCHAR2 (200);

BEGIN

x := 'I_PROXY_ROLE_DATA$_1';

str := 'select* from sys.lct where object_name=:x';

EXECUTE IMMEDIATE str USING x;

END;

/

查看SQL 的version count信息

SYS@anqing1(rac1)> selectsql_id,version_count from v$sqlarea where sql_text like 'select * from sys.lctwhere %';

SQL_ID        VERSION_COUNT

------------- -------------

5d8tu19t1fug2             2

--查看parentcursor 和 child cursor的handle address：

SYS@anqing1(rac1)> selectaddress,child_address from v$sql where sql_id='5d8tu19t1fug2';

ADDRESS CHILD_AD

-------- --------

30A9DCDC 2CBA64AC

30A9DCDC 2A32C000

将library cache dump出来：

SQL> alter session set events 'immediatetrace name library_cache level 11';

Session altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/oradata/XEZF/admin/udump/xezf_ora_14041.trc

--查看trace 文件：搜索30A9DCDC

BUCKET 92642:

LIBRARY OBJECT HANDLE:handle=30a9dcdc mtx=0x30a9dd90(2) cdp=2

--object handle 的内存地址

name=select * from sys.lct whereobject_name=:x

--object 的名称

hash=55555e74e494aa0356a33a0a721769e2 timestamp=07-24-2011 15:03:04

--hash值和timestamp

namespace=CRSRflags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]

--namespace 和 flags

kkkk-dddd-llll=0000-0001-0001 lock=N pin=0latch#=1 hpc=0006 hlc=0006

--Heaps loaded and kept,lock, pin, and latch modes

lwt=0x30a9dd38[0x30a9dd38,0x30a9dd38]ltm=0x30a9dd40[0x30a9dd40,0x30a9dd40]

pwt=0x30a9dd1c[0x30a9dd1c,0x30a9dd1c]ptm=0x30a9dd24[0x30a9dd24,0x30a9dd24]

ref=0x30a9dd58[0x30a9dd58,0x30a9dd58]lnd=0x30a9dd64[0x30a9dd64,0x30a9dd64]

--以上是：Linklists of lock waiters, temporary locks, pin waiters, temporary pins andreferences

LOCK OWNERS:

lock     user  session count mode flags

-------- -------- -------- ----- ---- ------------------------

2d1a0cac 30efbd20 30efbd20     2N    [00]

2d10501c 30f10e78 30f10e78     2N    [00]

LIBRARY OBJECT: object=2aa6cf2c

--Memory address of theobject (heap 0)

type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0

--Object type, flags, andstatus

CHILDREN: size=16

child#    table reference   handle

------ -------- --------- --------

02c08c08c  2c08bd5c 2cba64ac

1 2c08c08c  2c08bec8 2a32c000

--这个就是所有child cursor 的handle 的内存地址

DATA BLOCKS:

data#     heap  pointer   status pins change whr

----- -------- -------- --------- ---- ------ ---

0 2b8839b4 2aa6cfc4 I/P/A/-/-    0NONE   00

--Object data structures(heap descriptors)

BUCKET 92642 total object count=1

查看其中一个child cursor 地址，在trace 里搜索：2cba64ac

LIBRARY OBJECT HANDLE: handle=2cba64acmtx=0x2cba6560(0) cdp=0

namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]

kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=1 hpc=0002 hlc=0002

lwt=0x2cba6508[0x2cba6508,0x2cba6508] ltm=0x2cba6510[0x2cba6510,0x2cba6510]

pwt=0x2cba64ec[0x2cba64ec,0x2cba64ec]ptm=0x2cba64f4[0x2cba64f4,0x2cba64f4]

ref=0x2cba6528[0x2c08bd5c,0x2c08bd5c]lnd=0x2cba6534[0x2cba6534,0x2cba6534]

CHILD REFERENCES:

reference latch flags

--------- ----- -------------------

2c08bd5c     0 CHL[02]

LOCK OWNERS:

lock     user  session count mode flags

-------- -------- -------- ----- ---- ------------------------

2d345160 30efbd20 30efbd20     1N    [00]

2d1a0bdc 30f10e78 30f10e78     1N    [00]

LIBRARY OBJECT: object=2c1a3280

type=CRSR flags=EXS/RIV[0201] pflags=[0000] status=VALD load=0

DEPENDENCIES: count=1 size=16

dependency#    tablereference   handle position flags

----------- -------- --------- -------- -------- -------------------

0 2db7d838  2db7d770 2bb25644       18 DEP[01]

AUTHORIZATIONS: count=1 size=16 minimum entrysize=16

00000000 30000000 00020000 00000000

ACCESSES: count=1 size=16

dependency# types

----------- -----

0 0009

SCHEMA: count=1 size=262144

0000003d

DATA BLOCKS:

data#     heap  pointer   status pins change whr

----- -------- -------- --------- ---- ------ ---

0 2a078574 2c1a3318 I/P/A/-/-    0NONE   00

6 2aa6d2542c4f9cf0 I/-/A/-/E    0 NONE   00

这个结构和Heap 0的差不多。 但是这个child cursor 没有name了。 因为我们访问时先通过parent cursor。 在到child cursor。所以parent cursor 有name 就可以了。

这里的Data blocks 有2个： data block 0和data block 6. 对应heap 0 和heap 6.  关于Heap 0的说明如下：

The data block structure for a heap, stored in heap 0,contains a pointer to the first data block that is allocated for the heap, astatus indicator, the pin under which the heap is loaded, and so on.

执行计划保存在Heap 6里，我们把Heap 6 dump 出来，看一下：

SYS@anqing1(rac1)> alter session setevents 'immediate trace name heapdump_addr level 2,addr0x2aa6d254';

Session altered.

--注意，这里的addr前面加了0x。

SYS@anqing1(rac1)> oradebug setmypid

Statement processed.

SYS@anqing1(rac1)> oradebugtracefile_name

/u01/app/Oracle/admin/anqing/udump/anqing1_ora_1533.trc

在trace 里搜索：0x2aa6d254

HEAP DUMP heapname="sql area" desc=0x2aa6d254

extent sz=0xff4 alt=32767 het=156 rec=0 flg=2opc=2

parent=0x2000002c owner=0x2aa6d1c8 nex=(nil)xsz=0xff4

EXTENT 0 addr=0x2a73d5e8

Chunk 2a73d5f0 sz=      788    free     "               "

Dump of memory from 0x2A73D5F0 to0x2A73D904

2A73D5F0 C0000315 00000000 2AA6D2B42AA6D2B4  [...........*...*]

2A73D600 2B10E1FC C0000051 000000002C49E55C  [...+Q.......\.I,]

2A73D610 2AEA8820 00314E00 0000800400010035  [ ..*.N1.....5...]

2A73D620 00930024 00020001 0019003A00020074  [$.......:...t...]

2A73D630 001A0019 000200BF 007400B000500004  [..........t...P.]

2A73D640 001A0024 00BF0033 0003001200010022  [$...3......."...]

2A73D650 62000006 0000024D 2A73D6040C9D040C  [...bM.....s*....]

2A73D660 00000000 2A73D8A0 0035000000240001  [......s*..5...$.]

2A73D670 00010093 003A0002 0074001900190002  [......:...t.....]

2A73D680 00BF001B 00B00002 0004007400240039  [........t...9.$.]

2A73D690 0033001B 001200BF 0022000300060001  [..3.......".....]

2A73D6A0 12021400 00010022 0A000006006B0802  [....".........k.]

2A73D6B0 03671F85 EF042C04 001C004E000C0000  [..g..,..N.......]

2A73D6C0 001D0400 001C0056 000002E0001D0040  [....V.......@...]

2A73D6D0 00B10028 000D0028 008C00260026005E  [(...(...&...^.&.]

2A73D6E0 00A40026 000E000C 0E00002612011201  [&.......&.......]

2A73D6F0 00010022 AC000009 00AF0A0000010021  ["...........!...]

2A73D700 00020074 00210009 001500BF00780026  [t.....!.....&.x.]

....

dbsnake blog里的总结如下：

heap6实际上就是sql area(这个和上图中描述的heap 6是sql context不尽相同)。 Oracle把sql的执行计划存在了这个sql的子cursor的heap 6(也就是sql area)中,只不过存储的形式是编译好的二进制格式。

MOS 上的相关说明：

Parsinga cursor builds four different library cache structures, if they do not alreadyexist, within the library cache:

1、parent cursor handle

2、parent cursor object, containing the child dependency list

3、child cursor handle, inserted in the child dependency list of theparent object

4、childcursor object, containing the compilation and run-time execution plan for thecompiled SQL statement.