Oracle中执行计划的存储位置


这两天我一直在想一个问题，那就是Oracle的执行计划到底存储在什么地儿？它会是一种什么样的格式？

这里我试图对这个问题做一点我自己认为的解释，这个解释可能是有问题的。 

朋友们在看这篇文章之前，应该首先熟悉如下的这张图：

kglob_resize.jpg

怎样才叫熟悉这张图呢？我认为验证的方法就是看你是否能够仅仅看着这张图，在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

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

070000001CAE2C70 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 setmypid

Statement 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 setmypid

Statement 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=0x0

EXTENT 0 addr=70000001cadc3a0

  Chunk  70000001cadc3b0 sz=     1496    perm      "perm           "  alo=1496

70000001CADC3B0 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 times

70000001CADC460 000075C8 00000000 00000000 00080008  [..u.............]

70000001CADC470 00080200 00160000 02000000 00000000  [................]

70000001CADC480 00000000 00000000 00000000 00000000  [................]

        Repeat 4 times

70000001CADC4D0 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    =     6504

MARKS:

  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 handle

2、parent cursor object, containing the child dependency list

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

4、child cursor object, containing the compilation and run-time execution plan for the compiled SQL statement.

相关内容