[20120508]bad sql.txt

[20120508]bad sql.txt

1.昨天在优化一个程序外的sql，遇到这个帖子：
http://www.itpub.net/thread-1495845-1-1.html
ORACLE8I升级11G R2后，查询系统视图特别慢

我的测试版本:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

我修改了sql语句，执行如下:
SELECT    f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule
    FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
   WHERE f.owner = 'SCOTT'
     AND f.table_name = 'EMP'
     AND f.constraint_type = 'R'
     AND SYS.all_cons_columns.constraint_name = f.constraint_name
     AND SYS.all_cons_columns.table_name = 'EMP'
     AND SYS.all_cons_columns.owner = 'SCOTT'
     AND p.owner = f.r_owner
     AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION;

发现在11G下确实很慢，但是在3次执行时，速度明显变快。why？

原始链接是通过使用10GR2的SYS.ALL_CONSTRAINTS覆盖原来11G的定义来实现加速的。

对比两个定义如下：
23,24c23,40
---
>    SELECT ou.NAME, oc.NAME,
>           DECODE (c.type#,
>                   1, 'C',
>                   2, 'P',
>                   3, 'U',
>                   4, 'R',
>                   5, 'V',
>                   6, 'O',
>                   7, 'C',
>                   8, 'H',
>                   9, 'F',
>                   10, 'F',
>                   11, 'F',
>                   13, 'F',
>                   '?'
>                  ),
>           o.NAME, c.condition, ru.NAME, rc.NAME,
>           DECODE (c.type#, 4, DECODE (c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'), NULL),
48c64,72
---
>      FROM SYS.con$ oc,
>           SYS.con$ rc,
>           SYS."_BASE_USER" ou,
>           SYS."_BASE_USER" ru,
>           SYS."_CURRENT_EDITION_OBJ" ro,
>           SYS."_CURRENT_EDITION_OBJ" o,
>           SYS.cdef$ c,
>           SYS.obj$ oi,
>           SYS.user$ ui
53c77,78
---
>       AND (c.type# 17)                                                      /* don't include supplog cons   */
>       AND (c.type# != 12)                                                                     /* don't include log group cons */


主要的不同 SYS."_CURRENT_EDITION_OBJ"与 SYS.obj$不同。SYS."_CURRENT_EDITION_OBJ"是一个视图，好像与这个Edition-Based Redefinition有关。
正是这样的复杂定义导致执行变得更加复杂。


2.当然修改视图定义问题不大，总之不好，问题是为什么第3次执行会快呢？

SQL> column a format a100

SQL> select sql_id,child_number,substr(sql_text,1,100) a from v$sql where sql_text like '%f.constraint_name%';

SQL_ID        CHILD_NUMBER A

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

gmzkkrbp9s3zb            0 SELECT    f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.d

gmzkkrbp9s3zb            1 SELECT    f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.d

可以发现执行计划生成了2个光标。查看执行计划：

SQL> select * from table(dbms_xplan.display_cursor('gmzkkrbp9s3zb',NULL));

....太长。

 100 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))

       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))

 101 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

 103 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))

       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))

 104 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Note

-----

   - cardinality feedback used for this statement

可以发现 CHILD_NUMBER=1,使用11G的新特性cardinality feedback，执行计划发生了改变。这是这个原因导致执行计划第3次变快了。