解读Oracle执行计划(一)
以SYS用户身份连接Oracle 12c 数据库,然后,执行下面的语句清空buffer_cache和shared_pool中的数据(生产环境请谨慎使用该语句)
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL>
打开另外一个SQL Plus会话窗口,以SYS用户连接到Oracle 12c数据库,可以是PDB,然后做一些基本的设置,设置 set autotrace traceonly,接着执行一条SQL查询语句:
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> set linesize 200;
SQL> set pagesize 999;
SQL>
关于设置autotrace的说明
- SET AUTOTRACE OFF:此为默认值,即关闭Autotrace
- SET AUTOTRACE ON EXPLAIN:只显示执行计划
- SET AUTOTRACE ON STATISTICS:只显示执行的统计信息
- SET AUTOTRACE ON:包含执行计划和统计信息
- SET AUTOTRACE TRACEONLY:与ON相似,但不显示语句的执行结果
SQL> select count(*) from dba_objects order by object_type asc;
产生的执行结果中:
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
- ID: 一个序号,但不是执行的先后顺序。执行的先后根据缩进来判断
- Operation: 当前操作的内容。
- Rows: 当前操作的Cardinality,Oracle估计当前操作的返回结果集。
- Cost(CPU):Oracle 计算出来的一个数值(代价),用于说明SQL执行的代价。
- Time:Oracle 估计当前操作的时间。
在看执行计划的时候,除了看执行计划本身,还需要看谓词和统计信息。 通过整体信息来判断SQL效率。
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("S"."OBJ#"=:B1)
6 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>2 AND "O"."TYPE#"<>6 OR "O"."TYPE#"=1 AND NOT
EXISTS (SELECT 0 FROM "SYS"."OBJ$" "IO","SYS"."TAB$" "T","SYS"."IND$" "I" WHERE
"I"."OBJ#"=:B1 AND "I"."BO#"="T"."OBJ#" AND BITAND("T"."PROPERTY",36893488147419103232)=368934
88147419103232 AND "IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2) OR "O"."TYPE#"=2 AND (SELECT 1
FROM "SYS"."TAB$" "T" WHERE "T"."OBJ#"=:B2 AND
BITAND("T"."PROPERTY",36893488147419103232)=0)=1 OR "O"."TYPE#"=6 AND (SELECT 1 FROM
"SYS"."SEQ$" "S" WHERE "S"."OBJ#"=:B3 AND (BITAND("S"."FLAGS",1024)=0 OR "S"."FLAGS" IS
NULL))=1) AND (BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR
"O"."TYPE#"<>88 AND NOT EXISTS (SELECT 0 FROM "SYS"."USER_EDITIONING$" "UE" WHERE
"TYPE#"=:B4 AND "UE"."USER#"=:B5) OR EXISTS (SELECT 0 FROM "SYS"."USER_EDITIONING$" "UE"
WHERE "UE"."TYPE#"=:B6 AND "UE"."USER#"=:B7) AND
(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0
FROM "SYS"."USER$" "U2","SYS"."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
"O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B8 AND "U2"."TYPE#"=2 AND