你的位置:首页 > 数据库

[数据库]oracle里的执行计划


内容主要来自看书学习的笔记,如下记录了常见查询执行计划的方法。

2.2 如何查看执行计划
1.explain plan
2.dbms_xplan包
3.autotrace
4.10046事件
5.10053事件
6.awr/statspack报告(@?/rdbms/admin/awrsqrpt)
7.脚本(display_cursor_9i.sql)

2.2.1 explain plan
explain plan for sql
select * from table(dbms_xplan.display);
SQL> explain plan for select * from emp;
已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |   468 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    12 |   468 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

已选择8行。

其原理是oracle会把explain plan的sql的执行计划写入plan_table$,执行查询 table(dbms_xplan.display),会把其格式化后输出。
plan_table$是一个on commit preserve rows的global temporary table,各个session只能看到自己的,互不干扰。

2.2.2 dbms_xplan包
按照场景不同,有以下四种方法:
1.select * from table(dbms_xplan.display);
2.select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
3.select * from table(dbms_xplan.display_cursor(’sql_id/hash_value’,child_cursor_number,’advanced’));
4.select * from table(dbms_xplan.display_awr(’sql_id'));

第一种方法就是explain plan。
第二种方法是查看刚刚执行过的sql的执行计划。
第三种方法是查看指定sql的执行计划,需要sql_id或hash_value,还有child_number(v$sql)。
select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like ‘%xxxx%’;
第四种方法是查看指定sql所有历史执行计划,第二、第三种方法需要其sql执行计划还在shared pool种,如果被age out出shared pool(v$sqlarea中查询不到),只要其被采集到awr repository中,就可以用第四种方法查看。(v$sqlarea中的version_count代表有几个执行计划)
select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘xxx’; —可以查询到
exec dbms_workload_repository.create_snapshot; --采集awr
alter system flush shared_pool;  —清理shared pool
select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘xxx’; —查询不到
此时已经无法使用第二、第三种方法查看。但是第四种方法无法显示谓词条件,不完美。

2.2.3 autotrace
set autotrace {off|on|traceonly}
[explain]
[statistics]
不仅可以得到执行计划,还可以得到资源消耗量。
set autotrace on = set auto on —结果集+执行计划+消耗资源量
set autotrace off = set autot off --关闭
set autotrace traceonly = set auto trace —执行计划+消耗资源量
set autotrace traceonly explain = set autot trace exp --执行计划
set autotrace traceonly statistics = set autot trace stat —消耗资源量

2.2.4 10046事件与tkprof
1.激活10046事件
alter session set events '10046 trace name context forever,level 12';
oradebug setmypid;
oradebug event 10046 trace name context forever,level 12;
2.执行sql
3.关闭10046事件
alter session set events '10046 trace name context off'
oradebug event 10046 trace name context off
4.tkprof tracefile outputfile
注意oradebug只能sys用户使用,不是很方便。

使用display_cursor_9i.sql
@/xxx/display_cursor_9i.sql hash_value child_number
使用printsql
使用sys用户@/xxx/printsql.prc创建过程
set serveroutput on size 1000000
exec printsql(xxxxx,'SID')
exec printsql(xxxxx,'SPID')

2.3真实的执行计划
explain plan、set autotrace、select * from table(dbms_xplan.display)都有可能不准,主要是因为绑定变量,在默认开启绑定变量窥探bind peeking的情况下,以上方法的执行计划只是半成品。

2.4执行计划的执行顺序
先从最开头一直连续往右看,直到看到最右边的并列的地方;对于不并列的,靠右先执行;如果见到并列的,就从上往下看,对于并列的部分,靠上先执行。