鲁商置业董事长简历:获取、阅读执行计划

反映SQL执行方式的唯一证明就是执行计划。能够获得、阅读执行计划是SQL调整最基本的技能。一、获得执行计划1、Explain Plan For·····SQL>explain plan for select ````````;
SQL>select * from table(dbms_xplan.display);
2、SET AUTOTRACE ON EXPLAIN
（set autot on exp)
SQLPLUS的命令，在执行SQL语句的同时显示执行计划，设置EXP(LAIN)的目的是只显示执行计划而不显示统计信息.。
执行了set autotrace on explain语句之后，接下来的查询、插入、更新、删除语句就会显示执行计划，直到执行“set autotrace off;”语句。如果是设置了set autotrace on，除了会显示执行计划之外，还会显示一些有用的统计信息。set autotrace off ---------------- 不生成autotrace 报告，这是缺省模式
set autotrace on explain ------ autotrace只显示优化器执行路径报告
set autotrace on statistics -- 只显示执行统计信息
set autotrace on ----------------- 包含执行计划和统计信息
set autotrace traceonly ------ 同set autotrace on，但是不显示查询输(1). set autotrace on explain; --只显示执行计划
SQL> set autotrace on explain;
SQL> 

select count(*) from dba_objects;

COUNT(*)
----------
    31820

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0   SORT (AGGREGATE)
  2    1     VIEW OF 'DBA_OBJECTS'
  3    2       UNION-ALL
  4    3         FILTER
  5    4           TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
  6    5             NESTED LOOPS
  7    6               TABLE ACCESS (FULL) OF 'USER$'
  8    6               INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
  9    4           TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
10    9             INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
11    3         NESTED LOOPS
12   11           TABLE ACCESS (FULL) OF 'USER$'
13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

(2). set autotrace on statistics;--只显示统计信息
SQL> set autotrace on statistics;
SQL> select count(*) from dba_objects;

COUNT(*)
----------
    31820

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
     25754 consistent gets
         0 physical reads
         0 redo size
       383 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

(3). set autotrace traceonly;--同set autotrace on 只是不显示查询输出
SQL> set autotrace traceonly;
SQL> select count(*) from dba_objects;

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0   SORT (AGGREGATE)
  2    1     VIEW OF 'DBA_OBJECTS'
  3    2       UNION-ALL
  4    3         FILTER
  5    4           TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
  6    5             NESTED LOOPS
  7    6               TABLE ACCESS (FULL) OF 'USER$'
 8    6               INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
  9    4           TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
10    9             INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
11    3         NESTED LOOPS
12   11           TABLE ACCESS (FULL) OF 'USER$'
13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
     25754 consistent gets
         0 physical reads
         0 redo size
       383 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

(4).set autotrace traceonly explain;--比较实用的选项，只显示执行计划，但是与set autotrace on explain;相比不会执行语句，对于仅仅查看大表的Explain Plan非常管用。
SQL> set autotrace traceonly explain;
SQL> select * from dba_objects;
已用时间: 00: 00: 00.00

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0   VIEW OF 'DBA_OBJECTS'
  2    1     UNION-ALL
  3    2       FILTER
  4    3         TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
  5    4           NESTED LOOPS
  6    5             TABLE ACCESS (FULL) OF 'USER$'
  7    5             INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
  8    3         TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
  9    8           INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
10    2       TABLE ACCESS (BY INDEX ROWID) OF 'LINK$'
11   10         NESTED LOOPS
12   11           TABLE ACCESS (FULL) OF 'USER$'
13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

3、SQL Trace和10046事件      两者的功能一致，都是跟踪某个session的活动情况。 10046事件包括5个级别：0级：相当于SQL_TRACE=FALSE，禁用跟踪功能；1级：相当于SQL_TRACE-TRUE,起用标准跟踪，也就等同于SQL TRACE；4级：在1级的基础上还会跟踪绑定变量信息；8级：在1级的基础上还会跟踪等待事件信息；12级：在1级的基础上加上绑定变量、等待事件信息，相当于1级+4级+8级。 启用10046事件：alter system set events='10046 trace name context forever,level 8';alter session set events='10046 trace name context forever,level 12'; 关闭跟踪：alter session set events='10046 trace name context off';注：设置这个事件后，会在短时间内产生大量trace文件，如不及时关闭，很容易迅速耗尽磁盘空间。select spid from v$process where addr in (select paddr from v$session where sid=&sid);DBA也可自定义跟踪文件格式，以便区分：alter session set tracefile_identifier='20100802'这时生成的Trace文件名就是sid_ora_spid_idenfier的格式了。 使用TKPROF工具来阅读跟踪文件：tkprof       .trc        .txt 在跟踪文件中，最需要关注的内容：DISK 、QUERY、CURRENT。这3列代表着Oracle的工作量，而所有的调整最终也是为了降低这3个统计量，而其他如CPU、ELAPSED其实都是一个间接指标。DISK：代表物理的磁盘读取次数。QUERY：一致性读，查询语句一般会使用一致性读。CURRENT：当前读，DML语句常用这种读取。 跟踪其他会话信息：方法一：使用DBMS_SUPPORT包。如果没有预先创建这个包，$ORACLE_HOME/rdbms/admin/dbmssupp.sql手工创建。exec dbms_support.start_trace_in_session(sid=>703,serial=>846,waits=>true,binds=>true);sid 和serial分别对应进程的SID和序号，可以从V$SESSION视图获得。而参数WAITS、BINDS如果设置成True，就是收集等待事件和绑定变量的信息。相当于12级的10046事件。exec dbms_support.stop_trace_in_session(sid=>703,serial=>846);方法二：oradebug这是oracle提供的在SLQ*PLUS环境下使用的工具。使用这个工具需要提供进程的OS PID和Oracle PID。SQL>select spid,pid  from v$process where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));SPID       PID8458        358如果使用OS PID作为参数，需要使用setospid子命令，注意命令执行的输出结果会同时显示OS PID和Oracle PID。SQL>oradebug setospid 8458；如果使用Oracle PID，就需要使用setorapid，注意命令提示中不再提示Oracle PID，只提示OS pid。SQL>oradebug setorapid 358;此时定义了要跟踪的进程后，接下来就要定义跟踪什么信息了，比如跟踪10046事件，就使用下面的命令。SQL>oradebug unlimit；SQL>oradebug event 10046 trace name context forever,level 12；SQL>oradebug event 10046 trace name context off; 4、从Library Cache中获取（查询已执行过的SQL语句，而不需要重新执行，直接从内存中取出真正的执行计划，而非估计值）V$SQLV$SQL_PLANV$SQL_PLAN_STATISTICSV$SQL_WORKAREAV$SQL_PLAN_STATISTICS_ALL(该视图是前三个视图的一个汇总，可以获得前3个视图的信息)V$SESSIONoracle 10g中：dbms_xplan.display_cursor(sql_id => ,
                          cursor_child_no => ,（如没指定，会罗列出所有的子游标的执行计划）
                          format => );（最常见的内置格式：BASIC、TYPICAL（default）、SERIAL、ALL）select * from table(dbms_xplan.display_cursor('                  ',null,'TYPICAL') 5、DISPLAY_AWR用户通过这个包来获取AWR中的执行计划，其信息来源于DBA_HIST_SQL_PLAN、DBA_HIST_SQLTEXT。dbms_xplan.display_awr(sql_id => ,plan_hash_value => ,db_id => ,format => );AWR产生的报告中会带有SQL ID一列，把这个值作为第一个参数传递方法就可以获得当时的执行计划。SQL>select * from table(dbms_xplan.display_awr('           ')); 二、阅读执行计划oracle执行计划的一些概念：
Rowid：系统给oracle数据的每行附加的一个伪列，包含数据表名称，数据库id，存储数据库id以及一个流水号等信息，rowid在行的生命周期内唯一。
Recursive sql：为了执行用户语句，系统附加执行的额外操作语句，譬如对数据字典的维护等。
Row source（行源）：oracle执行步骤过程中，由上一个操作返回的符合条件的行的集合。
Predicate（谓词）：where后的限制条件。
Driving table（驱动表）：又称为连接的外层表，主要用于嵌套与hash连接中。一般来说是将应用限制条件后，返回较少行源的表作为驱动表。在后面的描述中，将driving table称为连接操作的row source 1。
Probed table（被探查表）：连接的内层表，在我们从 driving table得到具体的一行数据后，在probed table中寻找符合条件的行，所以该表应该为较大的row source，并且对应连接条件的列上应该有索引。在后面的描述中，一般将该表称为连接操作的row source 2.
Concatenated index（组合索引）：一个索引如果由多列构成，那么就称为组合索引，组合索引的第一列为引导列，只有谓词中包含引导列时，索引才可用。
可选择性：表中某列的不同数值数量/表的总行数如果接近于1，则列的可选择性为高。
Oracle访问数据的存取方法：
Full table scans, FTS(全表扫描)：通过设置db_block_multiblock_read_count可以设置一次IO能读取的数据块个数，从而有效减少全表扫描时的IO总次数，也就是通过预读机制将将要访问的数据块预先读入内存中。只有在全表扫描情况下才能使用多块读操作。
Table access by rowed（通过rowid存取表，rowid lookup）：由于rowid中记录了行存储的位置，所以这是oracle存取单行数据的最快方法。
Index scan（索引扫描index lookup）：在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的rowid值，索引扫描分两步1，扫描索引得到rowid；2，通过 rowid读取具体数据。每步都是单独的一次IO，所以如果数据经限制条件过滤后的总量大于原表总行数的5%－10％,则使用索引扫描效率下降很多。而如果结果数据能够全部在索引中找到，则可以避免第二步操作，从而加快检索速度。
根据索引类型与where限制条件的不同，有4种类型的索引扫描：
l         Index unique scan（索引唯一扫描）：存在unique或者primary key的情况下，返回单个rowid数据内容。
l         Index range scan（索引范围扫描）：1，在唯一索引上使用了range操作符（>,<,<>,>=,<=,between）；2，在组合索引上，只使用部分列进行查询；3，对非唯一索引上的列进行的查询。
l         Index full scan（索引全扫描）：需要查询的数据从索引中可以全部得到。
l         Index fast full scan（索引快速扫描）：与index full scan类似，但是这种方式下不对结果进行排序。
目前为止，典型的连接类型有3种：
l         Sort merge join（SMJ排序－合并连接）：首先生产driving table需要的数据，然后对这些数据按照连接操作关联列进行排序；然后生产probed table需要的数据，然后对这些数据按照与driving table对应的连接操作列进行排序；最后两边已经排序的行被放在一起执行合并操作。排序是一个费时、费资源的操作，特别对于大表。所以smj通常不是一个特别有效的连接方法，但是如果driving table和probed table都已经预先排序，则这种连接方法的效率也比较高。
l         Nested loops（NL嵌套循环）：连接过程就是将driving table和probed table进行一次嵌套循环的过程。就是用driving table的每一行去匹配probed table 的所有行。Nested loops可以先返回已经连接的行，而不必等待所有的连接操作处理完成才返回数据，这可以实现快速的响应时间。
l         Hash join（哈希连接）：较小的row source被用来构建hash table与bitmap，第二个row source用来被hashed，并与第一个row source生产的hash table进行匹配。以便进行进一步的连接。当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。但需要设置合适的hash_area_size参数且只能用于等值连接中。
l         Cartesian product（笛卡尔积）：表的每一行依次与另外一表的所有行匹配。