转载

精通Oracel SQL书籍阅读 --SQL 执行计划

一.解释计划
1. 查看解释计划
SQL>explain for select * from hr_employees;
SQL>select * from table(dbms_xplan.display(format=>'BASIC+COST+PREDICATE'));

2.解释计划和执行计划不一致
解释计划是基于您使用它的时候的环境来生成的；
且解释计划不考虑绑定变量的数据类型（所有的绑定变量都是VARCHAR2的），解释计划不窥视绑定变量的值；
解释计划只考虑原始计划而不显示最终的计划；
基于以上这些原因，解释计划很有可能就会生成与语句实际执行不一致的计划。

3.如何阅读计划
查看解释计划中缩进最多的运算，缩进最多的运算实际上是执行过程中首先进行的运算。如果在同一层次上有多个运算，则按照自上而下的顺序依次执行；
每一步将行源数据传递给其父步骤，直到所有步骤完成为止。

4.访问和选择谓词
解释计划输出中最有用的部分之一就是被称为谓语信息的部分。在这个部分中，将会示出ACCESS_PREDICATES和FILTER_PREDICATES列。这两列与计划预算列表中的一行（用ID列来指示）相关。您会发现计划中每一个有相关的访问或选择谓语的运算，在其ID的旁边都有精通Oracel SQL书籍阅读 --SQL 执行计划

一个星号（*）。当您看到星号时，您就知道要在谓语信息部分寻找ID号来确定哪个谓语（where子句中的条件）是与该运算相关的。使用这些信息您就可以确认用来进行索引访问的列是正确的（或不正确），并且可以确定在哪里进行了条件的过滤。
    访问谓语要么进行索引运算，要么进行联结运算。访问谓语就是一种更直接的访问数据的方法，它只获取表中满足WHERE 子句的条件，或者与联结两张表中的字段相匹配的数据。选择型谓语是一种不太准确的获取数据的方法。因为在应用选择谓语是，需要验证当前数据行源中的所有数据行，以便确定某条数据是否满足选择条件。相反，访问谓语只有在确切匹配时，才会将数据行放入结果集中。通常访问谓语的效率更高，因为它只会收集满足条件的数据行，而不是都读取所有的行，然后舍弃不匹配的数据。
   较晚的进行过滤是常见的性能抑制剂；

二.执行计划
    当一条SQL语句执行时将会生成该语句的实际执行计划。在语句被硬解析后，所选的执行计划会存到库存高速缓存中以便以后重用。可以查询V$SQL_PLAN查看计划运算。
1.查看最近生成的SQL语句
例如：查询V$SQL以获取最近SCOTT用户执行的SQL语句
SQL>select /* recentsql */ sql_id, child_number, hash_value, address,execution,sql_text from v$sql
        where parsing_user_id = (select user_id from all_users where username = 'SCOTT')
         and command_type in (2,3,6,7,189)
         and upper(sql_text) not like UPPER('%recentsql%')

2.查看相关执行计划
(1)使用dbms_xplan.display_cursor函数
SQL>SELECT /*+ gather_plan_statistics */ empno,ename from scott.emp where ename = 'KING';
SQL>set serveroutput off
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS_LAST'));
   查询中使用的/*+ gather_plan_statistics */ 提示。为了为计划抓取行数据源执行统计信息，您必须告诉ORACLE在语句执行时收集这些信息。行数据源的执行统计信息包括行数，一致性读取行数，物理读取行数，物理写入次数，以及每一个运算在一行数据上的运行时间（在starts,a-rows,a-time,buffers)。可以使用这个提示来一句一句的收集这些信息，或者也可以将STATISTICS_LEVEL（实例参数的设置为ALL）。抓取这些统计信息确实增加了语句执行的成本，所以并不需要一直打开这个功能，使用提示在需要的时候打开；

(2)收集执行计划统计信息
   当无法获取计划统计信息是，给出的计划运算与解释计划的输出本质上是一样的。要想准确的知道计划的效果如何，您需要计划的行数据源执行统计信息。这些值可以告诉您计划中的每个运算实际上发生了什么。该数据是从V$SQL_PLAN_STATISTICS视图中获取的。

(3)标识SQL语句以便取回SQLID
用一段注释唯一标识SQL语句
SQL> SELECT /* KM-EMPTEST1 */ empno, ename from emp where job = 'MANAGER';
SQL> select sql_id, child_number, sql_test from v$sql where sql_text like '%KM-EMPTEST1%';   --sql id = v_sql_id
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('v_sql_id',0,'ALLSTATS LAST'));

自动为任何SQL语句取出执行计划
SQL> select xplan.*
         from (select max(sql_id) keep(dense_rank last order by last_active_time) sql_id,
                      max(child_number) keep(dense_rank last order by last_active_time) chlid_number
                from v$sql
                where upper(sql_text) like '%&1%'
                 and upper(sql_text) not like '%from v$sql where upper(sql_text) like%'
               )sqlinfo,
       table(dbms_xplan.display_cursor(sqlinfo.sql_id,sqlinfo.child_number,'ALLSTATS LAST')) xpan;

正文到此结束