oracle的执行计划有多少种,Oracle 查看执行计划的几种方式

查看执行计划的几种方式

set autotrace on

https://www.cndba.cn/Expect-le/article/3354https://www.cndba.cn/Expect-le/article/3354

explain plan

explain plan for select 0 from dual;

select * from table(dbms_xplan.display)https://www.cndba.cn/Expect-le/article/3354

10046事件

alter session setevents ‘10046 trace name context forever,level 12’; (开启跟踪)

执行sql

alter session setevents ‘10046 trace name context off’; (关闭跟踪)

步骤4：找到跟踪后产生的文件

tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela (格式化命令)

例子：https://www.cndba.cn/Expect-le/article/3354

SQL> set autot off

SQL> alter session set statistics_level=typical;

Session altered.

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

Session altered.

SQL> select count(*) from test;

COUNT(*)

----------

7

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> select d.value

2 || '/'

3 || LOWER (RTRIM(i.INSTANCE, CHR(0)))

4 || '_ora_'

5 || p.spid

6 || '.trc' trace_file_name

7 from (select p.spid

8 from v$mystat m,v$session s, v$process p

9 where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,

10 (select t.INSTANCE

11 FROM v$thread t,v$parameter v

12 WHERE v.name='thread'

13 AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,

14 (select value

15 from v$parameter

16 where name='user_dump_dest') d;

TRACE_FILE_NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12195.trc

SQL> host

[oracle@ora12c ~]$ tkprof /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12195.trc /home/oracle/10046.txt sys=no sort=prsela,exeela,fchela

TKPROF: Release 12.1.0.1.0 - Development on Fri Jan 20 08:22:25 2017

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

SQL ID: 7b2twsn8vgfsc Plan Hash: 784602781

select count(*)

from

test

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 3 3 2 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 4 66 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.00 0.00 7 69 2 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 103

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

---------- ---------- ---------- ---------------------------------------------------

1 1 1 SORT AGGREGATE (cr=66 pr=4 pw=0 time=298 us)

7 7 7 PARTITION RANGE ALL PARTITION: 1 3 (cr=66 pr=4 pw=0 time=397 us cost=39 size=0 card=11)

7 7 7 TABLE ACCESS FULL TEST PARTITION: 1 3 (cr=66 pr=4 pw=0 time=290 us cost=39 size=0 card=11)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

db file sequential read 4 0.00 0.00

SQL*Net message to client 2 0.00 0.00

db file scattered read 1 0.00 0.00

SQL*Net message from client 2 7.03 7.03

statistics_level=all

alter session setstatistics_level=all;

执行sql

select * fromtable(dbms_xplan.display_cursor(null,null,’allstats last’));https://www.cndba.cn/Expect-le/article/3354https://www.cndba.cn/Expect-le/article/3354

dbms_xplan.display_cursor输入sql_id参数直接获取

select * fromtable(dbms_xplan.display_cursor(‘&sq_id’)); #从共享池获取

https://www.cndba.cn/Expect-le/article/3354https://www.cndba.cn/Expect-le/article/3354

https://www.cndba.cn/Expect-le/article/3354

awrsqlrpt.sql

@?/rdbms/admin/awrsqrpt.sql

选择你要的断点(begin snap 和end snap)

输入sql_idhttps://www.cndba.cn/Expect-le/article/3354

版权声明：本文为博主原创文章，未经博主允许不得转载。

执行计划