« 诊断案例:PMON failed acquire latch QMNC Holder | Blog首页 | 分享,开拓,延展 - 20110625 ACOUG活动记事 »

ALL_OBJECTS与X$KGLDP、X$KZSPR的复杂执行计划

如果单看如下一条SQL，你可能绝对想不到其执行计划的复杂度。这条SQL的内容如下：

SELECT A.OBJECT, A.OWNER, A.TYPE, B.OBJECT_ID
FROM V$ACCESS A, ALL_OBJECTS B
WHERE A.OWNER = B.OWNER
AND A.OBJECT = B.OBJECT_NAME
AND A.TYPE = B.OBJECT_TYPE
AND A.SID = :1 AND A.OWNER <> 'PUBLIC'
AND A.OWNER <> 'SYS'

一个简单的查询，某个工具软件发给数据库的，查询了V$ACCESS 和 ALL_OBJECTS两个视图。
可是其执行计划却是相当壮观：

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time
0	SELECT STATEMENT				6 (100)
1	FILTER
2	TABLE ACCESS BY INDEX ROWID	OBJ$	1	78	3 (0)	00:00:01
3	NESTED LOOPS		1	642	5 (20)	00:00:01
4	NESTED LOOPS		1	564	2 (50)	00:00:01
5	VIEW	V_$ACCESS	1	550	1 (100)	00:00:01
6	VIEW	GV$ACCESS	1	576	1 (100)	00:00:01
7	HASH UNIQUE		1	663	1 (100)	00:00:01
8	NESTED LOOPS		1	663	0 (0)
9	NESTED LOOPS		3	1914	0 (0)
10	MERGE JOIN CARTESIAN		100	7000	0 (0)
11	FIXED TABLE FULL	X$KSUSE	1	32	0 (0)
12	BUFFER SORT		100	3800	0 (0)
13	FIXED TABLE FULL	X$KGLDP	100	3800	0 (0)
14	FIXED TABLE FIXED INDEX	X$KGLOB (ind:1)	1	568	0 (0)
15	FIXED TABLE FIXED INDEX	X$KGLLK (ind:1)	1	25	0 (0)
16	TABLE ACCESS BY INDEX ROWID	USER$	1	14	1 (0)	00:00:01
17	INDEX UNIQUE SCAN	I_USER1	1		0 (0)
18	INDEX RANGE SCAN	I_OBJ2	1		2 (0)	00:00:01
19	TABLE ACCESS BY INDEX ROWID	IND$	1	8	2 (0)	00:00:01
20	INDEX UNIQUE SCAN	I_IND1	1		1 (0)	00:00:01
21	NESTED LOOPS		1	24	2 (0)	00:00:01
22	INDEX RANGE SCAN	I_OBJAUTH1	1	11	2 (0)	00:00:01
23	FIXED TABLE FULL	X$KZSRO	1	13	0 (0)
24	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
25	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
26	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
27	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
28	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
29	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
30	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
31	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
32	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
33	VIEW		1	13	2 (0)	00:00:01
34	FAST DUAL		1		2 (0)	00:00:01
35	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
36	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
37	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
38	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
39	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
40	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
41	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
42	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
43	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
44	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
45	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
46	FIXED TABLE FULL	X$KZSPR	1	26	0 (0)
47	TABLE ACCESS BY INDEX ROWID	SUM$	1	26	0 (0)
48	INDEX UNIQUE SCAN	I_SUM$_1	1		0 (0)

这里面反复对X$KZSPR表进行了全表访问。这个执行计划显然和底层视图有关，我们解析一下可以发现主要的访问来自ALL_OBJECTS的复杂性。

以下是ALL_OBJECTS的创建语句：

create or replace view ALL_OBJECTS
    (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
     OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
     TEMPORARY, GENERATED, SECONDARY)
as
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP',
                     'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)
       or
       (o.type# = 1 and 1 = (select 1
                             from sys.ind$ i
                            where i.obj# = o.obj#
                              and i.type# in (1, 2, 3, 4, 6, 7, 9))))
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags, 128) = 0
  and
  (
    o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
    or
    (
      /* non-procedural objects */
      o.type# not in (7, 8, 9, 11, 12, 13, 14, 28, 29, 30, 56)
      and
      o.obj# in (select obj# from sys.objauth$
                 where grantee# in (select kzsrorol from x$kzsro)
                   and privilege# in (3 /* DELETE */,   6 /* INSERT */,
                                      7 /* LOCK */,     9 /* SELECT */,
                                      10 /* UPDATE */, 12 /* EXECUTE */,
                                      11 /* USAGE */,  16 /* CREATE */,
                                      17 /* READ */,   18 /* WRITE  */ ))
    )
    or
    (
       o.type# in (7, 8, 9, 28, 29, 30, 56) /* prc, fcn, pkg */
       and
       (
         exists (select null from sys.objauth$ oa
                  where oa.obj# = o.obj#
                    and oa.grantee# in (select kzsrorol from x$kzsro)
                    and oa.privilege# in (12 /* EXECUTE */, 26 /* DEBUG */))
         or
         exists (select null from v$enabledprivs
                 where priv_number in (
                                        -144 /* EXECUTE ANY PROCEDURE */,
                                        -141 /* CREATE ANY PROCEDURE */,
                                        -241 /* DEBUG ANY PROCEDURE */
                                      )
                )
       )
    )
    or
    (
       o.type# in (12) /* trigger */
       and
       (
         exists (select null from sys.trigger$ t, sys.objauth$ oa
                  where bitand(t.property, 24) = 0
                    and t.obj# = o.obj#
                    and oa.obj# = t.baseobject
                    and oa.grantee# in (select kzsrorol from x$kzsro)
                    and oa.privilege# = 26 /* DEBUG */)
         or        
         exists (select null from v$enabledprivs
                 where priv_number in (
                                        -152 /* CREATE ANY TRIGGER */,
                                        -241 /* DEBUG ANY PROCEDURE */
                                      )
              )
       )
    )
    or
    (
       o.type# = 11 /* pkg body */
       and
       (
         exists (select null
                   from sys.obj$ specobj, sys.dependency$ dep, sys.objauth$ oa
                  where specobj.owner# = o.owner#
                    and specobj.name = o.name
                    and specobj.type# = 9 /* pkg */
                    and dep.d_obj# = o.obj# and dep.p_obj# = specobj.obj#
                    and oa.obj# = specobj.obj#
                    and oa.grantee# in (select kzsrorol from x$kzsro)
                    and oa.privilege# = 26 /* DEBUG */)
         or
         exists (select null from v$enabledprivs
                 where priv_number in (
                                        -141 /* CREATE ANY PROCEDURE */,
                                        -241 /* DEBUG ANY PROCEDURE */
                                      )
                )
       )
    )
    or
    (
       o.type# in (22) /* library */
       and
       exists (select null from v$enabledprivs
               where priv_number in (
                                      -189 /* CREATE ANY LIBRARY */,
                                      -190 /* ALTER ANY LIBRARY */,
                                      -191 /* DROP ANY LIBRARY */,
                                      -192 /* EXECUTE ANY LIBRARY */
                                    )
              )
    )
    or
    (
       /* index, table, view, synonym, table partn, indx partn, */
       /* table subpartn, index subpartn, cluster               */
       o.type# in (1, 2, 3, 4, 5, 19, 20, 34, 35)
       and
       exists (select null from v$enabledprivs
               where priv_number in (-45 /* LOCK ANY TABLE */,
                                     -47 /* SELECT ANY TABLE */,
                                     -48 /* INSERT ANY TABLE */,
                                     -49 /* UPDATE ANY TABLE */,
                                     -50 /* DELETE ANY TABLE */)
               )
    )
    or
    ( o.type# = 6 /* sequence */
      and
      exists (select null from v$enabledprivs
              where priv_number = -109 /* SELECT ANY SEQUENCE */)
    )
    or
    ( o.type# = 13 /* type */
      and
      (
        exists (select null from sys.objauth$ oa
                 where oa.obj# = o.obj#
                   and oa.grantee# in (select kzsrorol from x$kzsro)
                   and oa.privilege# in (12 /* EXECUTE */, 26 /* DEBUG */))
        or
        exists (select null from v$enabledprivs
                where priv_number in (-184 /* EXECUTE ANY TYPE */,
                                      -181 /* CREATE ANY TYPE */,
                                      -241 /* DEBUG ANY PROCEDURE */))
      )
    )
    or
    (
      o.type# = 14 /* type body */
      and
      (
        exists (select null
                  from sys.obj$ specobj, sys.dependency$ dep, sys.objauth$ oa
                 where specobj.owner# = o.owner#
                   and specobj.name = o.name
                   and specobj.type# = 13 /* type */
                   and dep.d_obj# = o.obj# and dep.p_obj# = specobj.obj#
                   and oa.obj# = specobj.obj#
                   and oa.grantee# in (select kzsrorol from x$kzsro)
                   and oa.privilege# = 26 /* DEBUG */)
        or
        exists (select null from v$enabledprivs
                where priv_number in (
                                       -181 /* CREATE ANY TYPE */,
                                       -241 /* DEBUG ANY PROCEDURE */
                                     )
               )
      )
    )
    or
    (
       o.type# = 23 /* directory */
       and
       exists (select null from v$enabledprivs
               where priv_number in (
                                      -177 /* CREATE ANY DIRECTORY */,
                                      -178 /* DROP ANY DIRECTORY */
                                    )
              )
    )
    or
    (
       o.type# = 42 /* summary jjf table privs have to change to summary */
       and
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
    )
    or
    (
      o.type# = 32   /* indextype */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                      -205  /* CREATE INDEXTYPE */ ,
                                      -206  /* CREATE ANY INDEXTYPE */ ,
                                      -207  /* ALTER ANY INDEXTYPE */ ,
                                      -208  /* DROP ANY INDEXTYPE */
                                    )
             )
    )
    or
    (
      o.type# = 33   /* operator */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                      -200  /* CREATE OPERATOR */ ,
                                      -201  /* CREATE ANY OPERATOR */ ,
                                      -202  /* ALTER ANY OPERATOR */ ,
                                      -203  /* DROP ANY OPERATOR */ ,
                                      -204  /* EXECUTE OPERATOR */
                                    )
             )
    )
    or
    (
      o.type# = 44   /* context */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                      -222  /* CREATE ANY CONTEXT */,
                                      -223  /* DROP ANY CONTEXT */
                                    )
             )
    )
    or
    (
      o.type# = 48  /* resource consumer group */
      and
      exists (select null from v$enabledprivs
              where priv_number in (12)  /* switch consumer group privilege */
             )
    )
    or
    (
      o.type# = 46 /* rule set */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                      -251, /* create any rule set */
                                      -252, /* alter any rule set */
                                      -253, /* drop any rule set */
                                      -254  /* execute any rule set */
                                    )
             )
    )
    or
    (
      o.type# = 55 /* XML schema */
      and
      1 = (select /*+ NO_MERGE */ xml_schema_name_present.is_schema_present(o.name, u2.id2) id1 from (select /*+ NO_MERGE */ userenv('SCHEMAID') id2 from dual) u2)
      /* we need a sub-query instead of the directy invoking
       * xml_schema_name_present, because inside a view even the function
       * arguments are evaluated as definers rights.
       */
    )
    or
    (
      o.type# = 59 /* rule */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                      -258, /* create any rule */
                                      -259, /* alter any rule */
                                      -260, /* drop any rule */
                                      -261  /* execute any rule */
                                    )
             )
    )
    or
    (
      o.type# = 62 /* evaluation context */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                      -246, /* create any evaluation context */
                                      -247, /* alter any evaluation context */
                                      -248, /* drop any evaluation context */
                                      -249 /* execute any evaluation context */
                                    )
             )
    )
    or
    (
      o.type# = 66 /* scheduler job */
      and
      exists (select null from v$enabledprivs
               where priv_number = -265 /* create any job */
             )
    )
    or
    (
      o.type# IN (67, 79) /* scheduler program or chain */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                      -265, /* create any job */
                                      -266 /* execute any program */
                                    )
             )
    )
    or
    (
      o.type# = 68 /* scheduler job class */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                      -268, /* manage scheduler */
                                      -267 /* execute any class */
                                    )
             )
    )
    or (o.type# in (69, 72, 74))
    /* scheduler windows, window groups and schedules */
    /* no privileges are needed to view these objects */
    or
    (
      o.type# = 81 /* file group */
      and
      exists (select null from v$enabledprivs
               where priv_number in (
                                       -277, /* manage any file group */
                                       -278  /* read any file group */
                                    )
             )
    )
  )
/

其主要访问对象是V$ENABLEDPRIVS,该视图创建语法如下：

SQL> SELECT view_definition
  FROM v$fixed_view_definition
 WHERE view_name = 'GV$ENABLEDPRIVS';   

VIEW_DEFINITION
---------------------------------------------------------------------------------------------------
select inst_id,-kzsprprv from x$kzspr

我们可以看到X$KZSPR正是在此处引入的。

而V$ACCESS的定义相对复杂一点：

SQL> l
  1  SELECT view_definition
  2    FROM v$fixed_view_definition
  3*  WHERE view_name = 'GV$ACCESS'
SQL> /

VIEW_DEFINITION
---------------------------------------------------------------------------------------------------
select distinct s.inst_id,s.ksusenum,o.kglnaown,o.kglnaobj, decode(o.kglobtyp,       0, 'CURSOR',
1, 'INDEX',    2, 'TABLE',    3, 'CLUSTER',    4, 'VIEW',    5, 'SYNONYM',    6, 'SEQUENCE',    7,
'PROCEDURE',    8, 'FUNCTION',      9, 'PACKAGE',    10,'NON-EXISTENT',     11,'PACKAGE BODY',    12,'
TRIGGER',    13,'TYPE',    14,'TYPE BODY',    15,'OBJECT',    16,'USER',    17,'DBLINK',    18,'PIP
E',    19,'TABLE PARTITION',    20,'INDEX PARTITION',     21,'LOB',    22,'LIBRARY',    23,'DIRECTOR
Y',    24,'QUEUE',    25,'INDEX-ORGANIZED TABLE',    26,'REPLICATION OBJECT GROUP',    27,'REPLICAT
ION PROPAGATOR',    28,'JAVA SOURCE',     29,'JAVA CLASS',    30,'JAVA RESOURCE',    31,'JAVA JAR',
   'INVALID TYPE') from x$ksuse s,x$kglob o,x$kgldp d,x$kgllk l where l.kgllkuse=s.addr and l.kgllk
hdl=d.kglhdadr and l.kglnahsh=d.kglnahsh and o.kglnahsh=d.kglrfhsh and o.kglhdadr=d.kglrfhdl

至此这个SQL就清晰了。

然而如果不幸的，你会遇到如下BUG：
BUG 5454590 - SELECT FROM ALL_SYNONYMS AND ALL_OBJECTS IS MUCH SLOWER IN 10.2.0.2

这个客户的数据库版本正是10.2.0.2，这个SQL运行时间数小时不能完成，这进而导致了另外一个BUG：
BUG 7122093 - LIBRARY CACHE CHILD LATCH HELD FOR EXCESSIVE TIME IF SCAN X$KGLDP

由于X$KGLDP被长时间访问，导致了大量的LIBRARY CACHE竞争，数据库就此陷入困境。

这个案例告诫我们：对数据字典的查询应当相当谨慎，因为你不知道那后台隐藏着什么。

By eygle on 2011-06-24 08:05 | Comments (0) | Case | SQL.PLSQL | 2823 |

SQL