获取执行计划的6种方法
一.获取执行计划的6种方法(详细步骤已经在每个例子的开头注释部分说明了）：
1. explain plan for获取；
2. set autotrace on ；
3. statistics_level=all;
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
5. 10046 trace跟踪
6. awrsqrpt.sql
1. explain plan for获取；(类似PLSQL DEVELOPE里的F5)
set linesize 1000
set pagesize 2000
explain plan for
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8138 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=18 OR "T1"."N"=19)
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
已选择24行。
2. set autotrace on ；
set autotrace on
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
执行计划
----------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8138 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=18 OR "T1"."N"=19)
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
1032 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
3. statistics_level=all;
的方法，可以省略步骤1，直接步骤2,3。
2. 关键字解读（其中OMem、1Mem和User-Mem在后续的课程中会陆续见到）：
Starts为该sql执行的次数。
E-Rows为执行计划预计的行数。
A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。
Buffers为每一步实际执行的逻辑读或一致性读。
Reads为物理读。
OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，
这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的
1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，
就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存
大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,
大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)
OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。
0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存
*/
set autotrace off
alter session set statistics_level=all ;
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 1a914ws3ggfsn, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
Plan hash value: 3532430033
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | | 2 |00:00:00.01 | 12 |
| 2 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.01 | 10 |
| 3 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 5 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 2 | 2 |00:00:00.01 | 5 |
|* 5 | INDEX RANGE SCAN | T1_N | 2 | 1 | 2 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 2 | 1 | 2 |00:00:00.01 | 5 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 2 | 1 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("T1"."N"=18 OR "T1"."N"=19))
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
已选择29行。
4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取(知道sql_id后，直接带入的方式，简单，就步骤1）
select * from table(dbms_xplan.display_cursor('1a914ws3ggfsn'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID 1a914ws3ggfsn, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("T1"."N"=18 OR "T1"."N"=19))
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
5. 10046 trace跟踪
set autotace off
alter session set statistics_level=typical;
alter session set events '10046 trace name context forever,level 12';
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
alter session set events '10046 trace name context off';
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d;
exit
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc d:\10046.txt sys=no sort=prsela,exeela,fchela
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 12 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 12 0 2
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 94
Rows Row Source Operation
------- ---------------------------------------------------
2 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
2 NESTED LOOPS (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2)
2 INLIST ITERATOR (cr=5 pr=0 pw=0 time=16 us)
2 TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2)
2 INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621)
2 INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622)
2 TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 1.31 1.31
6. awrsqrpt.sql
二.适用场合分析
1.如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1；
2.跟踪某条SQL最简单的方法是方法1，其次就是方法2；
3.如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；
4.如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5；
5.要想确保看到真实的执行计划，不能用方法1和方法2；
6.要想获取表被访问的次数，只能使用方法3；
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/26.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
