[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# < 14 OR 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次變快了。