ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120508]bad sql.txt

[20120508]bad sql.txt

原创 Linux操作系统 作者:lfree 时间:2012-05-08 17:24:14 0 删除 编辑
[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', '?'), o.NAME, c.condition,
<           ru.NAME, rc.NAME, DECODE (c.type#, 4, DECODE (c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'), NULL),
---
>    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.user$ ou, SYS.user$ ru, SYS.obj$ ro, SYS.obj$ o, SYS.cdef$ c, SYS.obj$ oi, SYS.user$ ui
---
>      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# != 12                                                                           /* don't include log groups */
---
>       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次变快了。






来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-723066/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2285
  • 访问量
    6025572