sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle oracle sql execution plan在什么地方

时间:2016-12-04 22:28   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于oracle sql execution plan在什么地方的文章。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: sql execution plan in heap 6

今天在看Jonathan Lewis大师的oracle core第194页时,Jonathan Lewis提到了一个知识点,一个看似大家都知道的但是似乎又没有去关注过:oracle sql execution plan在什么地方呢?原文如下:

At various stages in the process, though, we have to worry about concurrent activity.Historically we would get and hold the library cache hash latch while we walked alongthe chain searching for the correct cursor. We would create a library cache lock (KGLlock) for Heap 0 to make sure that it didn’t get flushed from memory and we would createa library cache pin (KGL pin) to make sure that the plan (Heap 6 / SQL Area) didn’tget flushed from memory while we were executing the query. However, you may recall allthose latches relating to the library cache that appeared in 10g—like the library cachepin allocation latch—pins and locks are little chunks of memory that have to be allocatedfrom, and returned to, the shared pool. So, the act of pinning a cursor meant you had toget a couple of latches, allocate some emory and so on. This is expensive stuff if yourrate of execution is extremely high.

Jonathan Lewis大师说是在heap 6的位置里面,下面我们来进行验证。

SQL> SELECT * FROM v$version WHERE rownum <3;

BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production

SQL> CREATE TABLE tab_plan AS SELECT owner,object_name,object_id FROM dba_objects;
TABLE created.

SQL> CREATE INDEX t_idx_id ON tab_plan(object_id);
INDEX created.

SQL>  analyze TABLE tab_plan compute statistics FOR TABLE FOR ALL indexes FOR ALL indexed COLUMNS;
TABLE analyzed.

SQL> SELECT owner,object_name FROM tab_plan WHERE object_id=1000;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SYS
V_$BUFFER_POOL

我们要去dump library cache,那么就需要先知道某个handle的具体位置,不然dump出来我们也不知道具体是哪个.

SQL> SELECT ADDRESS,SQL_ID,SQL_FULLTEXT FROM v$sql WHERE sql_fulltext LIKE '%select owner,object_name%';

ADDRESS  SQL_ID
-------- -------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
29AAF0C4 8jx2bhk59ddz8
SELECT ADDRESS,SQL_ID,SQL_FULLTEXT FROM v$sql WHERE sql_fulltext LIKE '%select o

2994F5A0 b6ag3nmy9pv11
select owner,object_name from tab_plan where object_id=1000

29A1BB5C 71zg735t9m5ck
create table tab_plan as select owner,object_name,object_id from dba_objects

2994F5A0就是我们需要找的handle 地址,下面我们来dump library cache。

SQL> conn /as sysdba
Connected.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug unlimit
Statement processed.

SQL> oradebug dump library_cache 4
Statement processed.

SQL> oradebug close_trace
Statement processed.

SQL> oradebug tracefile_name
/home/ora10g/admin/roger/udump/roger_ora_28972.trc

我们vi 该trace 然后搜索2994f5a0 可以发现如下内容:

BUCKET 60449:
  LIBRARY OBJECT HANDLE: handle=2994f5a0 mtx=0x2994f654(1) lct=1 pct=1 cdp=1
  name=select owner,object_name from tab_plan where object_id=1000
  hash=b9ba37bb77521151b329e3a4fc9aec21 timestamp=07-04-2012 03:05:23
  namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
  kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=1 hpc=0000 hlc=0000
  lwt=0x2994f5fc[0x2994f5fc,0x2994f5fc] ltm=0x2994f604[0x2994f604,0x2994f604]
  pwt=0x2994f5e0[0x2994f5e0,0x2994f5e0] ptm=0x2994f5e8[0x2994f5e8,0x2994f5e8]
  ref=0x2994f61c[0x2994f61c,0x2994f61c] lnd=0x2994f628[0x2994f628,0x2994f628]
    LIBRARY OBJECT: object=258f5e58
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 27213eb0  27213ce0 29893a90
  BUCKET 60449 total object count=1

这里可以看到其子游标的handle地址是 29893a90,我们继续搜索29893a90:
发现没有需要的内容了,怪了。
可能是上面的dump level小了点了,关于library cache dump的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信息

所以我们这里level必须大于8才行,下面我们继续dump下:

SQL> conn /AS sysdba
Connected.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug unlimit
Statement processed.

SQL> oradebug dump library_cache 16
Statement processed.

SQL> oradebug close_trace
Statement processed.

SQL> oradebug tracefile_name
/home/ora10g/admin/roger/udump/roger_ora_29602.trc

下面我们开始搜索:2994f5a0,找到如下信息:

BUCKET 60449:
  LIBRARY OBJECT HANDLE: handle=2994f5a0 mtx=0x2994f654(1) lct=1 pct=1 cdp=1
  name=select owner,object_name from tab_plan where object_id=1000
  hash=b9ba37bb77521151b329e3a4fc9aec21 timestamp=07-04-2012 03:05:23
  namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
  kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=1 hpc=0000 hlc=0000
  lwt=0x2994f5fc[0x2994f5fc,0x2994f5fc] ltm=0x2994f604[0x2994f604,0x2994f604]
  pwt=0x2994f5e0[0x2994f5e0,0x2994f5e0] ptm=0x2994f5e8[0x2994f5e8,0x2994f5e8]
  ref=0x2994f61c[0x2994f61c,0x2994f61c] lnd=0x2994f628[0x2994f628,0x2994f628]
    LIBRARY OBJECT: object=258f5e58
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 27213eb0  27213ce0 29893a90
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 26684d08 258f5ef0 I/P/A/-/-    0 NONE   00      1.01     1.05
  BUCKET 60449 total object count=1

我们可以清楚的看到heap 的总大小是1.05k,其中分配使用了1.01k。

继续搜索29893a90,找到如下详细信息:

LIBRARY OBJECT HANDLE: handle=29893a90 mtx=0x29893b44(0) lct=1 pct=2 cdp=0
  namespace=CRSR flags=RON/KGHP/PN0/EXP/[10010100]
  kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=1 hpc=0000 hlc=0000
  lwt=0x29893aec[0x29893aec,0x29893aec] ltm=0x29893af4[0x29893af4,0x29893af4]
  pwt=0x29893ad0[0x29893ad0,0x29893ad0] ptm=0x29893ad8[0x29893ad8,0x29893ad8]
  ref=0x29893b0c[0x27213ce0,0x27213ce0] lnd=0x29893b18[0x29893b18,0x29893b18]
    CHILD REFERENCES:
    reference latch flags
    --------- ----- -------------------
     27213ce0     0 CHL[02]
    LIBRARY OBJECT: object=272fe92c
    type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
    DEPENDENCIES: count=1 size=16
    dependency#    table reference   handle position flags
    ----------- -------- --------- -------- -------- -------------------
              0 25a3aa9c  25a3a9d4 2994c33c       30 DEP[01]
    ACCESSES: count=1 size=16
    dependency# types
    ----------- -----
              0 0009
    TRANSLATIONS: count=1 size=16
    original    final
    -------- --------
    2994c33c 2994c33c
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr alloc(K)  size(K)
    ----- -------- -------- --------- ---- ------ --- -------- --------
        0 298e9cfc 272fe9c4 I/-/A/-/-    0 NONE   00      2.20     3.07
        6 27213b88 2691b8e4 I/-/A/-/E    0 NONE   00      4.56     7.95
这里补充下:

namespace的说明;

namespace........Character represenation of the namespace in the library
                   cache where this exists. This can be one of :
                    0 - [CRSR]      Cursor
                    1 - [TABL/PRCD] Table/view/sequence/synonym/procedure/
                                    function or package specification
                    2 - [BODY]      Package Body
                    3 - [TRGR]      Database Trigger
                    4 - [INDX]      Index
                    5 - [CLST]      Cluster
                    6 - [OBJE]      Object
                    7 - [PIPE]      Database Pipe
                    8 - [ ]         Invalid (out of range)


FLAGS的说明:
flags............Set of flags represented in character form followed by the
                   flag bitmask in hexidecimal.
                    0x00010000 - [RON] Read-only/non-stored (eg shared cursor)
                    0x00020000 - [REM] Object is remote
                    0x00040000 - [FIX] This object is fixed.
                    0x00080000 - [CGA] This object is in CGA memory.
                    0x00400000 - [OBS] This object is obselete.Do not use again.
                    0x00800000 - [KEP] Keep this object pinned at all times.
                    0x01000000 - [SEC] This object is secondary.
                    0x02000000 - [SML] Small handle so use KGHX to allocate
                    0x04000000 - [FUL] Free the object upon unlock.
                    0x08000000 - [FUP] Free the object upon unpin.
                    0x10000000 - [PN0] Pin heap 0 as long as it is locked.
                    0x20000000 - [USE] In use - do not unpin.               
                    0x40000000 - [MED] Medium handle so use KGHX to allocate
                    0x80000000 - [FRE] In the list of handles to be freed. 
                    0x00000001 - [LRG] Large handle so use KGHX to allocate

我们看到这个cursor有2个data#,分别是0和6,Jonathan Lewis大师说sql plan是在heap 6里面,那我们就来dump下 heap 6看看是否是这样:

9.2以前版本dump heap的命令如下:

ALTER SESSION SET EVENTS‘immediate trace name heapdump_addr level 2153245561′;

9.2以后版本则变为如下命令:

ALTER SESSION SET EVENTS‘immediate trace name heapdump_addr level 2, addr 2153245560′;

具体用法请参考http://www.juliandyke.com/Diagnostics/Dumps/HEAPDUMP_ADDR.html

下面我们开始dump heap 6.

SQL> SELECT kglobhd0, kglobhd6
  2      FROM x$kglob
  3      WHERE kglhdadr = '2994F5A0';

KGLOBHD0 KGLOBHD6
-------- --------
26684D08 00

SQL> SELECT kglobhd0, kglobhd6
  2      FROM x$kglob
  3      WHERE kglhdadr = '29893A90';

KGLOBHD0 KGLOBHD6
-------- --------
298E9CFC 27213B88

SQL> SELECT to_number('27213B88','xxxxxxxxxxxxxxxx') FROM dual;

TO_NUMBER('27213B88','XXXXXXXXXXXXXXXX')
----------------------------------------
                               656489352

SQL> conn /AS sysdba
Connected.

SQL> oradebug setmypid
Statement processed.

SQL> oradebug unlimit
Statement processed.

SQL> oradebug dump HEAPDUMP_ADDR 2 656489352
Statement processed.

SQL> oradebug close_trace
Statement processed.

SQL> oradebug tracefile_name
/home/ora10g/admin/roger/udump/roger_ora_30064.trc

trace 信息如下:

******************************************************
HEAP DUMP heap name="sql area"  desc=0x27213b88
extent sz=0xff4 alt=32767 het=156 rec=0 flg=2 opc=2
parent=0x2000002c owner=0x27213af4 nex=(nil) xsz=0xff4
EXTENT 0 addr=0x25ccad34
  Chunk 25ccad3c sz=     3464    free      "               "
Dump of memory from 0x25CCAD3C to 0x25CCBAC4
25CCAD30                            C0000D89              [....]
25CCAD40 00000000 27213C00 27213C00 23595144  [.....

从上面可以清楚的看到index T_IDX_ID的信息,也就验证了Jonathan Lewis的说法。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle oracle sql execution plan在什么地方

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1365.html

Oracle研究中心

关键词:

sql execution plan in heap 6

oracle sql execution plan在什么地方呢?