读书笔记-《基于Oracle的SQL优化》-第二章-1

bisal

发布于 2019-01-29 15:43:05

8750

发布于 2019-01-29 15:43:05

文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

第二章：Oracle里的执行计划

2.1 什么是执行计划

Oracle用来执行目标SQL语句的这些步骤的组合就被称为执行计划。

执行计划可以分为如下三个部分：

1、目标SQL的正文、SQL ID和其执行计划所对应的的PLAN HASH VALUE。

2、执行计划的主体部分。

可以看到Oracle在执行目标SQL时所用的内部执行步骤，这些步骤的执行顺序，所对应的的谓词信息、列信息，优化器评估出来执行这些步骤后返回结果集的Cardinality、成本等内容。

执行计划行前*字符指执行步骤有对应的驱动或者过滤查询条件，这个星号对应的具体的驱动或过滤查询条件可以从执行计划的“Predicate Information(identified y operation id)”中找到。实际上，这部分内饿哦那个就是上述执行步骤所对应的谓词信息。access表示驱动查询条件。

3、执行计划的额外补充信息。

是否使用动态采样(dynamic sampling)

是否使用Cardinality Feedback(Oracle 11g中引入的修正执行计划中返回结果集的Cardinality的一种技术手段)

是否使用SQL Profile(Oracle 10g中引入的调整、稳定执行计划的一种方法)。

2.2 如何查看执行计划

(1)、explain plan命令

按F5，PL/SQL Developer就调用explain plan命令，F5只是explain plan命令上的一层封装而已。

语法：

explain plan for + 目标SQL

select * from table(dbms_xplan.display)

执行explain plan命令，则Oracle就将解析目标SQL所产生的执行计划的具体执行步骤写入PLAN_TABLE$，随后执行的select * from table(dbms_xplan.display)只是从PLAN_TABLE$中将这些具体执行步骤以格式化的方式显示出来。PLAN_TABLE$是一个ON COMMIT PRESERVE ROWS的GLOBAL TEMPORARY TABLE，所以这里Oracle可以做到各个session只能看到自己执行的SQL所产生的执行计划，并且各个session往PLAN_TABLE$写入执行计划的过程互不干扰。

SQL> select dbms_metadata.get_ddl('TABLE', 'PLAN_TABLE$', 'SYS') from dual;

CREATE GLOBAL TEMPORARY TABLE "SYS"."PLAN_TABLE$"

( "STATEMENT_ID" VARCHAR2(30),

"PLAN_ID" NUMBER,

"TIMESTAMP" DATE,

"REMARKS" VARCHAR2(4000),

"OPERATION" VARCHAR2(30),

"OPTIONS" VARCHAR2(255),

"OBJECT_NODE" VARCHAR2(128),

"OBJECT_OWNER" VARCHAR2(30),

"OBJECT_NAME" VARCHAR2(30),

"OBJECT_ALIAS" VARCHAR2(65),

"OBJECT_INSTANCE" NUMBER(*,0),

"OBJECT_TYPE" VARCHAR2(30),

"OPTIMIZER" VARCHAR2(255),

"SEARCH_COLUMNS" NUMBER,

"ID" NUMBER(*,0),

"PARENT_ID" NUMBER(*,0),

"DEPTH" NUMBER(*,0),

"POSITION" NUMBER(*,0),

"COST" NUMBER(*,0),

"CARDINALITY" NUMBER(*,0),

"BYTES" NUMBER(*,0),

"OTHER_TAG" VARCHAR2(255),

"PARTITION_START" VARCHAR2(255),

"PARTITION_STOP" VARCHAR2(255),

"PARTITION_ID" NUMBER(*,0),

"OTHER" LONG,

"OTHER_XML" CLOB,

"DISTRIBUTION" VARCHAR2(30),

"CPU_COST" NUMBER(*,0),

"IO_COST" NUMBER(*,0),

"TEMP_SPACE" NUMBER(*,0),

"ACCESS_PREDICATES" VARCHAR2(4000),

"FILTER_PREDICATES" VARCHAR2(4000),

"PROJECTION" VARCHAR2(4000),

"TIME" NUMBER(*,0),

"QBLOCK_NAME" VARCHAR2(30)

) ON COMMIT PRESERVE ROWS

Oracle 10g及其以上版本，explain plan命令在执行后确实将解析目标SQL所产生的执行计划的具体步骤写入了PLAN_TABLE$，随后执行的select * from table(dbms_xplan.display)只是从PLAN_TABLE$中将具体执行步骤以格式化的方式显示出来。

SQL> select count(*) from sys.plan_table$;

COUNT(*) ---------- 0 SQL> select sid from v$mystat where rownum < 2; SID ---------- 1178 SQL> select count(*) from v$mystat; COUNT(*) ---------- 604 SQL> select saddr from v$session where sid=1178; SADDR ---------------- 00000001EEC37778 SQL> select count(*) from v$transaction where ses_addr='00000001EEC37778'; COUNT(*) ---------- 0 SQL> select count(*) from v$locked_object; COUNT(*) ---------- 0

SQL> explain plan for select empno, ename, dname from scott.emp, scott.dept where emp.deptno=dept.deptno; Explained.

SQL> set long 90000 SQL> set heading off SQL> set serveroutput on size 1000000

SQL> select operation, options, object_name, id, cardinality, cost from sys.plan_table$; OPERATION ------------------------------ OPTIONS -------------------------------------------------------------------------------- OBJECT_NAME ID CARDINALITY COST ------------------------------ ---------- ----------- ---------- SELECT STATEMENT 0 14 6 MERGE JOIN 1 14 6 OPERATION ------------------------------ OPTIONS -------------------------------------------------------------------------------- OBJECT_NAME ID CARDINALITY COST ------------------------------ ---------- ----------- ---------- TABLE ACCESS BY INDEX ROWID DEPT 2 4 2 INDEX FULL SCAN OPERATION ------------------------------ OPTIONS -------------------------------------------------------------------------------- OBJECT_NAME ID CARDINALITY COST ------------------------------ ---------- ----------- ---------- PK_DEPT 3 4 1 SORT JOIN 4 14 4 TABLE ACCESS OPERATION ------------------------------ OPTIONS -------------------------------------------------------------------------------- OBJECT_NAME ID CARDINALITY COST ------------------------------ ---------- ----------- ---------- FULL EMP 5 14 3 6 rows selected.

SQL> select count(*) from v$transaction where ses_addr='00000001EEC37778'; 1 SQL> select count(*) from v$locked_object; 1

SQL> select object_id from v$locked_object;

5003

SQL> select owner, object_name from dba_objects where object_id=5003;

SYS

PLAN_TABLE$

Oracle 10g：

SQL> select version from v$instance; 10.2.0.4.0 SQL> desc t; Name Null? Type ----------------------------------------- -------- ---------------------------- X NUMBER

SQL> select operation, options, object_name, id, cardinality, cost from sys.plan_table$; SELECT STATEMENT 0 10013476 483 PX COORDINATOR 1 PX SEND QC (RANDOM) :TQ10000 2 10013476 483 PX BLOCK ITERATOR 3 10013476 483 TABLE ACCESS FULL T 4 10013476 483 SQL> select count(*) from v$transaction where ses_addr='00000000A5A07A70'; 1 SQL> select count(*) from v$locked_object; 0

SQL> select table_name, degree from user_tables;

TABLE_NAME DEGREE ------------------------------ --------------------

T 8

alter table t parallel(degree 1);

SQL> select table_name, degree from user_tables;

TABLE_NAME DEGREE ------------------------------ --------------------

T 1

SQL> select operation, options, object_name, id, cardinality, cost from sys.plan_table$; SELECT STATEMENT 0 10013476 3483 TABLE ACCESS FULL T 1 10013476 3483

(2)、DBMS_XPLAN包

select * from table(dbms_xplan.display);

select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));

advanced比all显示结果更详细一些。

select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));

select * from table(dbms_xplan.display_awr('sql_id'));

(3)、SQLPLUS中的AUTOTRACE开关

SET AUTOTRACE ON(SET AUTOT ON)：显示执行结果，执行计划和资源消耗。

SET AUTOTRACE OFF(SET AUTOT OFF)：只显示执行结果。

SET AUTOTRACE TRACEONLY：不显示执行结果，与ON区别只显示执行结果的数量，不显示执行结果的具体内容，适合于执行结果的具体内容特别长，刷屏的SQL，这时只关心执行计划和资源消耗量。

SET AUTOTRACE TRACEONLY EXPLAIN(SET AUTOT TRACE EXP)：与TRACEONLY区别不显示资源消耗量和执行计划，只显示执行计划。

SET AUTOTTRACE TRACEONLY STATISTICS(SET AUTOT TRACE STAT)：只显示资源消耗量，与TRACEONLY区别不显示执行计划，只显示执行结果的数量和资源消耗量。

(4)、10046事件

和explain plan、dbms_xplan和autotrace开关不同之处：所得到的的执行计划中明确显示了目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。

USER_DUMP_DEST生成trace文件。

激活10046事件：

alter session set events '10046 trace name context forever, level 12'

oradebug event 10046 trace name context forever, level 12，推荐这种方法，因为可以在激活10046事件后执行命令oradebug tracefile_name来得到当前session所对应的的trace文件的具体路径和名称。

通常值是12，表示产生的trace文件中除了有目标SQL的执行计划和资源消耗明细之外，还会包含目标SQL所使用的绑定变量的值及该session所经历的的等待事件。

alter session set events '10046 trace name context off'

oradebug event 10046 trace name context off

10046产生的原始trace文件习惯称为裸trace文件(raw trace)，不直观，Oracle提供了tkprof命令，翻译裸文件trace。

oradebug setmypid表示准备对当前session使用oradebug命令。

(5)、10053事件

(6)、AWR报告或Statspack报告。

(7)、一些现成的脚本(display_cursor_9i.sql等)。

2.3 如何得到真实的执行计划

除了10046事件：

explain plan命令

DBMS_XPLAN包

SQLPLUS中的AUTOTRACE开关

这几种方法得到的执行计划都有可能是不准确的。

Oracle中判断得到的执行计划是否准确，就是看目标SQL是否被真正执行，真正执行过的SQL所对应的的执行计划就是准的，反之则有可能不准。注意，这里的判断原则从严格意义上来说并不适用于AUTOTRACE开关，因为所有使用AUTOTRACE开关所显示的执行计划都有可能是不准的，即使对应的目标SQL实际上已经执行过。

(1)、explain plan命令

因为此时SQL并没有被实际执行，可能不准的，尤其SQL包含绑定变量时。默认开启绑定变量窥探的情况下，对含绑定变量的目标SQL使用explain plan得到的执行计划只是一个半成品，Oracle随后对该SQL的绑定变量进行窥探后就得到了这些绑定变量具体的值，此时Oracle很可能会对上述半成品的执行计划做调整，一旦做了调整，使用explain plan命令得到的执行计划就不准了。

(2)、DBMS_XPLAN包

select * from table(dbms_xplan.display);执行计划可能不准，因为它只适用于查看使用explain plan命令得到的目标SQL的执行计划，目标SQL此时还没有被真正执行。

(3)、AUTOTRACE开关

SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY，目标SQL都已被实际执行，所以SET AUTOTRACE ON和SET AUTOTRACE TRACEONLY能看到SQL的实际资源消耗情况。当使用SET AUTOTRACE TRACEONLY EXPLAIN时，如果执行的是SELECT语句，则并没有被实际执行，如果执行的是DML语句，会被Oracle实际执行。

使用SET AUTOTRACE ON、SET AUTOTRACE TRACEONLY和SET AUTOTRACE TRACEONLY EXPLAIN来获得DML语句的执行计划时要小心，因为这些DML语句实际已经被执行过了。

但即使执行过了，但所有使用SET AUTOTRACE命令所得到的的执行计划都有可能是不准的，因为使用SET AUTOTRACE命令所显示的执行计划都是来源于调用explain plan命令。

执行计划还在共享池中：

脚本：display_cursor_9i.sql

存储过程：printsql

得到真实的执行计划和资源消耗情况。

如果执行计划已经被age out出shared pool了，可以执行DBMS_XPLAN.DISPLAY_AWR或者使用AWR SQL报告(awrsqrpt.sql)和Statspack SQL报告来得到其历史执行计划和资源消耗。(sprepsql)

display_cursor_9i.sql适用于Oracle 9i及以后，执行脚本时传入待查勘执行计划的目标SQL的SQL HASH VALUE和CHILD CURSOR NUMBER。

9i中没有DBMS_XPLAN包中的DISPLAY_CURSOR方法，无法使用select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));，但执行这个脚本可以得到真实执行计划。

如果执行计划已经被Oracle age out出shared pool，能否得到执行计划取决于： 1、10g以上版本，SQL执行的计划被Oracle捕获并存储到了AWR Repository中，则可以用AWR SQL得到真实执行。

2、9i，除非额外部署Statspack报告，并且采集Statspack报告的level值大于或等于6。

和DBMS_XPLAN.DISPLAY_AWR一样，AWR SQL报告显示的执行计划中也看不执行步骤对应的谓词条件，因为Oracle将执行计划的采样数据从V$SQL_PLAN挪到AWR Repository的基表WRH$_SQL_PLAN中时，没有保留V$SQL_PLAN中记录谓词条件的列ACCESS_PREDICATES和FILTER_PREDICATES的值。 SQL> desc WRH$_SQL_PLAN Name Null? Type ----------------------------------------- -------- ---------------------------- SNAP_ID NUMBER DBID NOT NULL NUMBER SQL_ID NOT NULL VARCHAR2(13) PLAN_HASH_VALUE NOT NULL NUMBER ID NOT NULL NUMBER OPERATION VARCHAR2(30) OPTIONS VARCHAR2(30) OBJECT_NODE VARCHAR2(128) OBJECT# NUMBER OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(31) OBJECT_ALIAS VARCHAR2(65) OBJECT_TYPE VARCHAR2(20) OPTIMIZER VARCHAR2(20) PARENT_ID NUMBER DEPTH NUMBER POSITION NUMBER SEARCH_COLUMNS NUMBER COST NUMBER CARDINALITY NUMBER BYTES NUMBER OTHER_TAG VARCHAR2(35) PARTITION_START VARCHAR2(5) PARTITION_STOP VARCHAR2(5) PARTITION_ID NUMBER OTHER VARCHAR2(4000) DISTRIBUTION VARCHAR2(20) CPU_COST NUMBER IO_COST NUMBER TEMP_SPACE NUMBER ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000) PROJECTION VARCHAR2(4000) TIME NUMBER QBLOCK_NAME VARCHAR2(31) REMARKS VARCHAR2(4000) TIMESTAMP DATE OTHER_XML CLOB

2.4 如何查看执行计划的执行顺序

先从最开头一直连续往右看，直到看到最右边的并列的地方；

对于不并列的，靠右的先执行；

如果见到并列的，就从上往下看，对于并列的部分，靠上的先执行。

select * from table(dbms_xplan.display_cursor);

XPLAN包其实是对DBMS_XPLAN包的封装，使用XPLAN包就可以很清晰地看到执行计划中每一步的执行顺序。执行顺序在XPLAN包的显示结果中以列Order来显示，Order的值从1开始递增，表示执行顺序的先后。

SQL> select /*+ xplan_example1 */ a from t; A ---------- 1 SQL> select sql_text, sql_id, child_number from v$sql where sql_text like 'select /*+ xplan_example1 */%'; SQL_TEXT -------------------------------------------------------------------------------- SQL_ID CHILD_NUMBER ------------- ------------ select /*+ xplan_example1 */ a from t 1smx7psgknjbd 0

2.5 Oracle里常见的执行计划

2.5.1 与表访问相关的执行计划

1、全表扫描：TABLE ACCESS FULL

2、ROWID扫描：TABLE ACCESS BY USER ROWID或TABLE ACCESS BY INDEX ROWID。取决于访问表时的ROWID来源。ROWID是来源于用户手工指定，或来源于索引。

2.5.2 与B树索引相关的执行计划

包括索引唯一扫描(INDEX UNIQUE SCAN)、索引范围扫描(INDEX RANGE SCAN)、索引全扫描(INDEX FULL SCAN)、索引快速全扫描(INDEX FAST FULL SCAN)和索引跳跃式扫描(INDEX SKIP SCAN)。

索引唯一扫描(INDEX UNIQUE SCAN)：CREATE UNIQUE INDEX xxx ON xxx(xxx);

索引范围扫描(INDEX RANGE SCAN)：CREATE INDEX ...

select * from xxx where xxx=xxx;

select *(plan_table_output) from table(dbms_xplan.display_cursor(null, null, 'ALL'));

begin

for i in 1 .. 5000 loop

insert into xxx value('a', i);

end loop;

commit;

end;

/

exec dbms_stats.gather_table_stats(ownname=>'IPRA', tabname=>'XXX', estimate_percent=>100, cascade=>TRUE, no_invalidate=>false, method_opt=>'FOR ALL COLUMNS SIZE 1');

即使使用select XXX(索引) from xxx;，用HINT，也会用全表扫描，不用索引。因为Oracle无论如何总会保证目标SQL结果的正确性，可能会得到错误结果的执行路径Oracle是不会考虑的。

对于单键值B树索引，NULL值不会存储在其中，一旦索引列出现NULL值，扫描索引会漏掉这些字段为NULL值的记录。-不准的执行计划。即使使用HINT。

此时只能将列修改为NOT NULL。则会用INDEX FAST FULL SCAN。HINT使用/*+ index(索引) */则用INDEX FULL SCAN。

2.5.3 与位图索引相关的执行计划

位图索引块的原因：主要是位图索引实现了快捷的按位运算的缘故。

位图索引的物理存储结构为：<被索引的键值，对应rowid的下限，对应rowid的上限，位图段>。这里的位图段是被压缩存储的，解压缩后就是一连串0和1的二进制位图序列，其中1表示被索引键值的一个有效rowid，Oracle通过一个转换函数(mapping function)将解压缩后的位图段中的1结合对应rowid的上下限，转换为被索引键值所对应的的有效rowid。

位图索引的物理存储结构就决定了Oracle数据库中位图索引的锁的粒度是在索引行的位图段上。

对于Oracle数据库中的位图索引而言，他是没有行锁这个概念的，要锁就锁索引行的整个位图段，而多个数据行可能对应同一个索引行的位图段。

位图索引的优势：

1、如果被索引的列的distinct值较少，那么位图索引和相同列上的B树索引比起来，会显著节省存储空间。

2、如果需要在多个列上创建索引，那么位图索引和同等条件下的B树索引比起来，往往会显著节省存储空间。

3、位图索引能够快速处理一些包含了各种AND或OR查询条件的SQL，这主要是因为位图索引能够实现快捷的按位运算。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2013年12月31日，如有侵权请联系 cloudcommunity@tencent.com 删除

sql