Oracle中执行计划的存储位置

这两天我一直在想一个问题，那就是Oracle的执行计划到底存储在什么地儿？它会是一种什么样的格式？<？XML:NAMESPACE PREFIX = O />这里我试图对这个问题做一点我自己认为的解释，这个解释可能是有问题的。朋友们在看这篇文章之前，应该首先熟悉如下的这张图：怎样才叫熟悉这张图呢？我认为验证的方法就是看你是否能够仅仅看着这张图，在1个小时的时间内把这张图的内容解释清楚。这张图也许能够用来衡量你对library cache的了解程度。我们现在做的测试其实就来源于上面这张图：首先执行一下下述的sql:SQL_testdb>select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 ......省略显示部分内容 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. 接着查一下上述sql在library cache中的library cache object handle的地址：SQL_testdb>select address,sql_text from v$sqlarea where sql_text like "select * from scott.emp%"; ADDRESS SQL_TEXT---------------- ---------------------------------------070000001CAE<？XML:NAMESPACE PREFIX = ST1 />2C70 select * from scott.emp 然后我们dump一下library cache，注意这里level一定要大于等于8，否则看不到heap 0的内容：SQL_testdb>alter session set events "immediate trace name library_cache level 11"; Session altered. SQL_testdb>oradebug setmypidStatement processed.SQL_testdb>oradebug tracefile_name/cadrasu01/app/oracle/admin/testdb/udump/testdb_ora_4128918.trc 从上述trace文件中我们以070000001CAE2C70为关键字去查询，查询结果如下：BUCKET 58504: LIBRARY OBJECT HANDLE: handle=70000001cae2c70 name=select * from scott.emp hash=a7ee488 timestamp=05-25-2011 18:03:28 namespace=CRSR flags=RON/TIM/PN0/SML/[12010000] kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=1 lwt=70000001cae2ca0[70000001cae2ca0,70000001cae2ca0] ltm=70000001cae2cb0[70000001cae2cb0,70000001cae2cb0] pwt=70000001cae2cd0[70000001cae2cd0,70000001cae2cd0] ptm=70000001cae2d60[70000001cae2d60,70000001cae2d60] ref=70000001cae2c80[70000001cae2c80, 70000001cae2c80] lnd=70000001cae2d78[70000001cae2d78,70000001cae2d78] LIBRARY OBJECT: object=70000001cae2a78 type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0 CHILDREN: size=16 child# table reference handle ------ -------- --------- -------- 0 70000001cae2b88 70000001cae1810 70000001cae15e0 DATA BLOCKS: data# heap pointer status pins change ----- -------- -------- ------ ---- ------ 0 70000001cae6e10 70000001cae1aa8 I/-/A 0 NONE BUCKET 58504 total object count=1 注意看上述library cache object的类型是cursor，名字是select * from scott.emp，它有一个子cursor，其library cache object handle的地址是70000001cae15e0。我们现在就以这个地址继续搜索上述trace文件，搜到的内容如下：LIBRARY OBJECT HANDLE: handle=70000001cae15e0 namespace=CRSR flags=RON/KGHP/PN0/[10010000] kkkk-dddd-llll=0000-0041-0041 lock=0 pin=0 latch#=1 lwt=70000001cae1610[70000001cae1610,70000001cae1610] ltm=70000001cae1620[70000001cae1620,70000001cae1620] pwt=70000001cae1640[70000001cae1640,70000001cae1640] ptm=70000001cae16d0[70000001cae16d0,70000001cae16d0] ref=70000001cae15f0[70000001cae1810, 70000001cae1810] lnd=70000001cae16e8[70000001cae16e8,70000001cae16e8] CHILD REFERENCES: reference latch flags --------- ----- ------------------- 70000001cae1810 4 CHL[02] LIBRARY OBJECT: object=70000001cae1208 type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0 DEPENDENCIES: count=1 size=16 dependency# table reference handle position flags ----------- -------- --------- -------- -------- ------------------- 0 70000001cae1128 70000001cae1098 70000001cadf6b8 20 DEP[01] ACCESSES: count=1 size=16 dependency# types ----------- ----- 0 0009 DATA BLOCKS: data# heap pointer status pins change ----- -------- -------- ------ ---- ------ 0 70000001cae1520 70000001cae0e90 I/-/A 0 NONE 6 70000001cae1328 70000001cadfaf0 I/-/A 0 NONE 从上述内容中我们可以看到，子cursor是没有名字的，这个其实很正常----因为Oracle是通过先访问其parent cursor后才会来访问它。另外，这个子cursor只有两个data block，分别是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, a status indicator, the pin under which the heap is loaded, and so on. 那么剩下的就只有一种可能，就是Oracle把sql的执行计划存储在了heap 6里。接下来我们dump一下heap 6的内容：SQL_testdb>oradebug setmypidStatement processed.SQL_testdb>alter session set events "immediate trace name heapdump_addr level 2, addr 0x70000001cae1328"; Session altered. SQL_testdb>oradebug tracefile_name/cadrasu01/app/oracle/admin/testdb/udump/testdb_ora_2859106.trc 从上述trace文件中我们可以看到如下的内容：HEAP DUMP heap name="sql area" desc=70000001cae1328 extent sz=0x1040 alt=32767 het=32 rec=0 flg=2 opc=5 parent=700000000000058 owner=70000001cae1208 nex=0 xsz=0x0EXTENT 0 addr=70000001cadc3a0 Chunk 70000001cadc3b0 sz= 1496 perm "perm " alo=149670000001CADC3B0 50B38F00 000005D9 00000000 00000000 [P...............]70000001CADC3C0 07000000 1CADDC80 000005D8 00000000 [................]70000001CADC3D0 00000000 00070007 00070200 00160000 [................]70000001CADC3E0 07020000 00000000 00000000 00000000 [................]70000001CADC3F0 00000000 00000000 00000000 00000000 [................] Repeat 6 times70000001CADC460 000075C8 00000000 00000000 00080008 [..u.............]70000001CADC470 00080200 00160000 02000000 00000000 [................]70000001CADC480 00000000 00000000 00000000 00000000 [................] Repeat 4 times70000001CADC4D0 00000008 00000000 00000000 00000000 [................]70000001CADC4E0 00000000 00000000 00000000 00000000 [................]70000001CADC4F0 00000000 00000000 000075C8 00000000 [..........u.....]70000001CADC500 00000000 00000000 00000000 00000000 [................]70000001CADC510 00000000 00000000 07000000 1CADC528 [...............（]70000001CADC520 000075C8 00000000 00000000 00000000 [..u.............]70000001CADC530 07000000 1CADC520 00000000 3EEEEEEE [....... ....>...]70000001CADC540 00000000 00000000 07000000 1CADC3B0 [................]70000001CADC550 07000000 1CADC550 07000000 1CADC550 [.......P.......P]70000001CADC560 00000000 00000000 00000001 1030F390 [.............0..]70000001CADC570 00000001 1030F390 00000001 1030F390 [.....0.......0..]70000001CADC580 00000000 00000000 00000000 00000000 [................]70000001CADC590 00000000 00000000 07000000 1CADFFA8 [................]70000001CADC5A0 00080000 00000000 07000000 1CAE0888 [................]70000001CADC5B0 07000000 1CAE07F8 07000000 1CAE0768 [...............h]70000001CADC5C0 07000000 1CAE06D8 07000000 1CAE0648 [...............H]70000001CADC5D0 07000000 1CAE05B8 07000000 1CAE0528 [...............（]70000001CADC5E0 07000000 1CAE0498 00000018 02180000 [................]70000001CADC5F0 00000020 00900000 00160000 00010000 [... ............]70000001CADC600 07000000 1CADDCA0 00010000 00000000 [................]70000001CADC610 00000018 02300000 00000020 00800000 [.....0..... ....]70000001CADC620 000A0000 00020000 07000000 1CADDD38 [...............8]70000001CADC630 00010000 00000000 00000018 02480000 [.............H..]70000001CADC640 00000020 00700000 00090000 00030000 [... .p..........]70000001CADC650 07000000 1CADDDD0 00010000 00000000 [................]70000001CADC660 00000018 02600000 00000020 00580000 [.....`..... .X..]70000001CADC670 00160000 00040000 07000000 1CADDE68 [...............h]70000001CADC680 00010000 00000000 00000018 02780000 [.............x..]70000001CADC690 00000020 00500000 00070000 00050000 [... .P..........]70000001CADC6A0 07000000 1CADDF00 00010000 00000000 [................]70000001CADC6B0 00000018 02900000 00000020 00380000 [........... .8..]......省略显示部分内容70000001CAE08B0 00160000 00000000 00000000 00000000 [................]70000001CAE08C0 07000000 1CADC5E8 00040000 00000000 [................]70000001CAE08D0 07000000 1CADDCA0 00000000 00000000 [................]70000001CAE08E0 07000000 1CADFF50 07000000 1CAE0918 [.......P........]70000001CAE08F0 00000001 1030EA80 00000000 00000000 [.....0..........]70000001CAE0900 07000000 1CADFF40 07000000 1CAE0878 [.......@.......x]70000001CAE0910 07000000 1CAE0888 00000007 0005454D [..............EM]70000001CAE0920 504E4F00 00000000 00000007 0005454E [PNO...........EN]70000001CAE0930 414D4500 00000000 00000007 00034A4F [AME...........JO]70000001CAE0940 42000000 00000000 00000007 00034D47 [B.............MG]70000001CAE0950 52000000 00000000 00000007 00084849 [R.............HI]70000001CAE0960 52454441 54450000 00000007 00035341 [REDATE........SA]70000001CAE0970 4C000000 00000000 00000007 0004434F [L.............CO]70000001CAE0980 4D4D0000 00000000 00000007 00064445 [MM............DE]70000001CAE0990 50544E4F 00000000 07000000 1CAE09C0 [PTNO............]70000001CAE09A0 07000000 1CAE07F8 07000000 1CAE0928 [...............（]70000001CAE09B0 00000000 00000000 10000000 00000000 [................]70000001CAE09C0 07000000 1CAE09E8 07000000 1CAE0768 [...............h]70000001CAE09D0 07000000 1CAE0938 00000000 00000000 [.......8........]70000001CAE09E0 10000000 00000000 07000000 1CAE0A10 [................]70000001CAE09F0 07000000 1CAE06D8 07000000 1CAE0948 [...............H]70000001CAE0A00 00000000 00000000 10000000 00000000 [................]70000001CAE0A10 07000000 1CAE0A38 07000000 1CAE0648 [.......8.......H]70000001CAE0A20 07000000 1CAE0958 00000000 00000000 [.......X........]70000001CAE0A30 10000000 00000000 07000000 1CAE0A60 [...............`]70000001CAE0A40 07000000 1CAE05B8 07000000 1CAE0968 [...............h]70000001CAE0A50 00000000 00000000 10000000 00000000 [................]70000001CAE0A60 07000000 1CAE0A88 07000000 1CAE0528 [...............（]70000001CAE0A70 07000000 1CAE0978 00000000 00000000 [.......x........]70000001CAE0A80 10000000 00000000 00000000 00000000 [................]70000001CAE0A90 07000000 1CAE0498 07000000 1CAE0988 [................]70000001CAE0AA0 00000000 00000000 10000000 00000000 [................]Permanent space = 6504MARKS: Mark 70000001cadc538 上述trace文件的内容告诉我们heap 6实际上就是sql area（这个和上图中描述的heap 6是sql context不尽相同），剩下的内容除了我们能看出包含了表scott.emp的各个字段的内容之外，其他的就看不懂了。 我猜测Oracle把sql的执行计划存在了这个sql的子cursor的heap 6（也就是sql area）中,只不过存储的形式是编译好的二进制格式。 感谢MOS，让我找到了如下的论据，可以在某种程度上让我自圆其说：Parsing a cursor builds four different library cache structures, if they do not already exist, within the library cache:1、parent cursor handle2、parent cursor object, containing the child dependency list3、child cursor handle, inserted in the child dependency list of the parent object4、child cursor object, containing the compilation and run-time execution plan for the compiled SQL statement.Librarycache内部机制详解Oracle Namespace 说明相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）