oracle11g中 connect by 语句执行计划改变

从10.2.0.3升级到11.2.0.4的朋友，如果细心会发现，以下sql在11.2.0.4中执行效率变低(该sql主要是获取连接用户获取权限信息)
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
如果你接触是Oracle版本比较多,而且还比较细心,你可能会进一步发现在11.2.0.2中该条sql是:select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 也就是说使用了/*+ connect_by_filtering */提示.我这里通过简单测试说明问题.

在11.2.0.4环境中
14:16:19 SQL> set autot trace exp stat
14:16:20 SQL> set time on
14:16:20 SQL> set timing on
14:16:20 SQL> var a1 number;
14:16:20 SQL> exec :a1:=6;
 
PL/SQL 过程已成功完成。
 
已用时间:  00: 00: 00.00
14:16:20 SQL> select privilege#,level from sysauth$ connect by grantee#=prior
14:16:20 SQL> privilege# and privilege#>0 start with grantee#=:a1  and privilege#>0
14:16:22 SQL> /
 
已用时间:  00: 00: 00.01
 
执行计划
----------------------------------------------------------
Plan hash value: 2624122540
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |            |     7 |   182 |     3  (34)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|            |       |       |            |          |
|   2 |   INDEX FAST FULL SCAN                  | I_SYSAUTH1 |   618 |  4944 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GRANTEE#"=PRIOR "PRIVILEGE#")
       filter("PRIVILEGE#">0 AND "GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0)
 
 
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
这里可以看出来这里使用的执行计划使用了CONNECT BY NO FILTERING WITH START-WITH,逻辑读为7.

10.2.0.3环境中
14:32:57 SQL> set lines 150
14:33:00 SQL> set autot trace exp stat
14:33:01 SQL> set time on
14:33:01 SQL> set timing on
14:33:01 SQL> var a1 number;
14:33:01 SQL> exec :a1:=6;
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
14:33:01 SQL> select privilege#,level from sysauth$ connect by grantee#=prior
14:33:01 SQL> privilege# and privilege#>0 start with grantee#=:a1  and privilege#>0  ;
 
Elapsed: 00:00:00.00
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2620769641
 
----------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     3 |    24 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|            |       |       |            |          |
|*  2 |   INDEX RANGE SCAN        | I_SYSAUTH1 |     3 |    24 |     2   (0)| 00:00:01 |
|   3 |   NESTED LOOPS            |            |       |       |            |          |
|   4 |    CONNECT BY PUMP        |            |       |       |            |          |
|*  5 |    INDEX RANGE SCAN       | I_SYSAUTH1 |     3 |    24 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GRANTEE#"=PRIOR "PRIVILEGE#")
       filter("PRIVILEGE#">0)
   2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0)
   5 - access("GRANTEE#"=PRIOR "PRIVILEGE#" AND "PRIVILEGE#">0)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        583  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
这里执行计划使用的为CONNECT BY WITH FILTERING,而且逻辑读为4,对于这个sql来说,使用CONNECT BY WITH FILTERING执行效率更高.

这里可以很明显的看到:connect by查询的执行计划从10g的CONNECT BY WITH FILTERING变为了11g中的CONNECT BY NO FILTERING WITH SW (UNIQUE)，从而使得执行计划发生改变。但是Oracle一般有个特性,就是当引入新特性之时,一般都会伴随隐含参数或者event来屏蔽新特性.这里也例外,我们可以通过”_optimizer_connect_by_elim_dups” = false和”_connect_by_use_union_all” = “old_plan_mode”来屏蔽11g中关于connect by执行计划的改变,使得执行计划恢复到10G的CONNECT BY WITH FILTERING方式
14:30:45 SQL> alter session set "_optimizer_connect_by_elim_dups" = false;
 
会话已更改。
 
已用时间:  00: 00: 00.00
14:30:46 SQL> alter session set "_connect_by_use_union_all" = "old_plan_mode";
 
会话已更改。
 
已用时间:  00: 00: 00.00
14:30:46 SQL> set autot trace exp stat
14:30:46 SQL> set time on
14:30:46 SQL> set timing on
14:30:46 SQL> var a1 number;
14:30:46 SQL> exec :a1:=6;
 
PL/SQL 过程已成功完成。
 
已用时间:  00: 00: 00.00
14:30:46 SQL> select privilege#,level from sysauth$ connect by grantee#=prior
14:30:46 SQL> privilege# and privilege#>0 start with grantee#=:a1  and privilege#>0 ;
 
已用时间:  00: 00: 00.01
 
执行计划
----------------------------------------------------------
Plan hash value: 2620769641
 
----------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     2 |    16 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|            |       |       |            |          |
|*  2 |   INDEX RANGE SCAN        | I_SYSAUTH1 |     2 |    16 |     2   (0)| 00:00:01 |
|   3 |   NESTED LOOPS            |            |       |       |            |          |
|   4 |    CONNECT BY PUMP        |            |       |       |            |          |
|*  5 |    INDEX RANGE SCAN       | I_SYSAUTH1 |     2 |    16 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GRANTEE#"=PRIOR "PRIVILEGE#")
       filter("PRIVILEGE#">0)
   2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0)
   5 - access("GRANTEE#"=PRIOR "PRIVILEGE#" AND "PRIVILEGE#">0)
 
 
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
11.2.0.2中也许是考虑到connect by 不够成熟,因此使用了hint /*+ connect_by_filtering */ 来固定执行计划
14:22:09 SQL> select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior
14:22:09 SQL>  privilege# and privilege#>0 start with grantee#=:a1  and privilege#>0
14:22:10 SQL> /
 
已用时间:  00: 00: 00.00
 
执行计划
----------------------------------------------------------
Plan hash value: 2620769641
 
----------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     7 |   182 |     8  (25)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|            |       |       |            |          |
|*  2 |   INDEX RANGE SCAN        | I_SYSAUTH1 |     2 |    16 |     2   (0)| 00:00:01 |
|   3 |   NESTED LOOPS            |            |     5 |   105 |     4   (0)| 00:00:01 |
|   4 |    CONNECT BY PUMP        |            |       |       |            |          |
|*  5 |    INDEX RANGE SCAN       | I_SYSAUTH1 |     2 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("GRANTEE#"=PRIOR "PRIVILEGE#")
       filter("PRIVILEGE#">0)
   2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0)
   5 - access("GRANTEE#"="connect$_by$_pump$_002"."prior privilege# " AND
              "PRIVILEGE#">0)
 
 
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
CONNECT BY NO FILTERING WITH SW (UNIQUE)和CONNECT BY WITH FILTERING,没有明显的优劣之分,只有在特定的情况下,进行了实际的测试,选择合适自己的sql的执行计划

原文连接:http://www.xifenfei.com/5585.html 有问题可直接联系手机(13429648788)

 

时间： 2024-11-09 10:14:51

oracle11g中 connect by 语句执行计划改变的相关文章

使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能.很多情况下,数据库性能的低下差不多都是不良SQL语句所引起.而SQL语句的执行 计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端,本文描述的将是如何通过EXPLAIN PLAN 获取SQL语句执行计划来获 取SQL语句的执行计划. 一.获取SQL语句执行计划的方式     1. 使用explain plan 将执行计划加载到表plan_table,然后查询该表来获取预估的执行计划      2. 查询动态性能视图

oracle 执行计划改变导致数据库负载过高解决办法

数据库主机负载 这里明显表现系统load 偏高,而且还在上升中:top的进程中,占用cpu都计划100% top - 16:25:39 up 123 days,  1:42,  4 users,  load average: 46.19, 45.08, 43.93 Tasks: 1469 total,  28 running, 1439 sleeping,   0 stopped,   2 zombie Cpu(s): 45.9%us,  1.1%sy,  0.0%ni, 47.1%id,  5

Oracle 历史SQL语句执行计划的对比与分析

    基于CBO优化器的环境中,SQL执行计划的生成依赖于统计信息的真实与完整.如列的离散度,列上的直方图,索引的可用性,索引上的聚簇因子.当这些信息是真实完整的情况下,CBO优化器通常都可以制定最优的执行计划.也正因此CBO优化器也灵活,难以控制,任一信息的不真实或缺失都可能导致执行计划发生变化而产生多个版本.经常碰到的情形是之前的某个SQL语句前阵子还不是TOP SQL,而最近变成了TOP SQL.或者说之前尽管是TOP SQL但,但最近尽然成了TOP 1.对于此情形,我们可以比对SQL语

sql中的select语句执行属性问题

问题描述 sql中的select语句执行属性问题 SELECT orders.*, (SELECT username FROM USER WHERE orders.user_id = user.id)username, (SELECT sex FROM USER WHERE orders.user_id = user.id)sex FROM orders 下面这个语句 的执行顺序是啥 这个语句这样写感觉好奇怪 有没有对sql比较懂得大神 解决方案 先执行子查询,然后结果保留,然后执行外部查询进行

怎样看oracle查询语句执行计划?

oracle|语句|执行 SQLPLUS的AutoTrace是分析SQL的执行计划,执行效率的一个非常简单方便的工具,在绝大多数情况下,也是非常有用的工具. 1.如何设置和使用AUTOTRACE SQL> connect / as sysdba SQL> @?/rdbms/admin/utlxplan.sql Table created. SQL> create public synonym plan_table for plan_table; Synonym created. SQL&

Oracle中使用DBMS_XPLAN处理执行计划详解_oracle

DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包:在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示计划的方式. 这5个函数分别对应不同的显示计划的方式,DBMS_XPLAN包不仅可以获取解释计划,它还可以用来输出存储在AWR,SQL调试集,缓存的SQL游标,以及SQL基线中的语句计划,实现如上的功能,通常会用到一下5个方法: 1.DISPLAY 2.DISPLAY_A

[20120915]10046事件与执行计划改变.txt

    使用10046事件来跟踪解决oracle的许多问题,是非常常用的手段,但是实际上可能出现跟踪的sql执行计划与原来不同的情况,自己应该引起注意. 测试如下: 1.测试环境建立: SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition

[20120214]异常数据导致执行计划改变.txt

今天上午,用户反应一条sql执行有点慢.我检查发现,原来使用索引的语句现在变成了全表扫描,而且昨晚oracle数据库自动分析过这个表. 语句很复杂,抽取有问题的部分: SELECT *  FROM med_operation_schedule a WHERE (       scheduled_date_time >= TO_DATE ('2012-02-15 00:00', 'yyyy-mm-dd hh24:mi')           AND scheduled_date_time     

.net中使用update语句执行不成功！我没分送了。大哥们帮帮忙

问题描述 SqlConnectionconn=newSqlConnection(ConfigurationManager.ConnectionStrings["bookshopConnectionString"].ConnectionString);conn.Open();SqlCommandcom=newSqlCommand("updatebookinfosettype=@type,bookname=@bookname,author=@author,translator=@