在ORACLE中，一条SQL的执行计划可以帮助我们了解该SQL的运行步骤，从而判断相应的执行计划是否合理，其瓶颈在何处等。所以，执行计划是我们调整SQL的一个重要参考。
在说明如何获取真实的执行计划前，我们先看一下通常获取执行计划的几种方法：
1、explain for ...
2、set autotrace on
3、 dbms_xplan.display_cursor
4、 10046 trace跟踪
5、 awrsqrpt.sql
在进行具体的演示前，我们均以下面的SQL做为样例。
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
其中，t1表有记录1000行，t2表有记录100000行；在T1表的n列和T2表的T1_id列上建有索引。
方法1：explain plan for
SQL> explain plan for
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 128660979
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8138 | 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 8138 | 8 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 4 | | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (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
23 rows selected.
特点：1、不会真正执行SQL语句。故，执行计划结果返回快，无SQL语句的执行结果输出，返回的执行计划可能不是真正的执行计划。
2、无统计信息
注：在一些图形化开发和管理集成工具中（比如PL/SQL DEVELOPER)查看执行计划，其实质就是使用explain for的方法。
方法2： set autotrace on;
SQL> set autotrace on
SQL> set pagesize 2000;
SQL> SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19); 2 3 4
ID N
---------- ----------
CONTENTS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
ID T1_ID N
---------- ---------- ----------
CONTENTS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
18 18
yFfYpcNtsxEpvWUOceJmHtvpCEbcJTUTeKExdHlGlwoIFEgmOo
18 18 18
OWCPIKKFQTMBZAVJBUGPOGOZGPHCMTGFDJTMCIZXRFVXYCATTY
19 19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
19 19 19
BEQUUMJSUDRLTCGOCIUHSZCNNJOTZOCEQKBZPSLHGAKRTLJAHX
Execution Plan
----------------------------------------------------------
Plan hash value: 128660979
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4198 | 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 4198 | 8 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 116 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 2 | | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (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
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1164 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
特点：1、SQL语句被执行。故，执行计划结果的返回时间的快慢，取决于SQL语句执行时间的长短。有SQL语句执行结果的输出。
2、有统计信息
方法3：dbms_xplan.display_cursor
使用ORACLE数据库内置的dbms_xplan包来获取执行计划，也是常用的方法之一。根据调用包中过程及参数的不同，可以衍生出很多种方法。这里我们只介绍最常用的2种。
注意，我们这里使用的是 dbms_xplan.display_cursor,而不是方法1中使用的dbms_xplan.display,虽然同属一个包，但方法是不同的，不要搞混。
（1）、设置会话选项为statistics_level=all后，用dbms_xplan.display_cursor(null,null,’allstats
last’)查看执行计划。
SQL> alter session set statistics_level=all ;
SQL> SET AUTOTRACE OFF --如果此前该开关为ON的状态，会影响我们后续的执行计划的获取和输出。所以，这里的目的是确保该开关已关闭。
SQL> SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19);
ID N
---------- ----------
CONTENTS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
ID T1_ID N
---------- ---------- ----------
CONTENTS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
18 18
yFfYpcNtsxEpvWUOceJmHtvpCEbcJTUTeKExdHlGlwoIFEgmOo
18 18 18
OWCPIKKFQTMBZAVJBUGPOGOZGPHCMTGFDJTMCIZXRFVXYCATTY
19 19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
19 19 19
BEQUUMJSUDRLTCGOCIUHSZCNNJOTZOCEQKBZPSLHGAKRTLJAHX
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
SQL_ID cshvm6ngravw0, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
Plan hash value: 128660979
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 2 |00:00:00.01 | 14 |
| 2 | NESTED LOOPS | | 1 | 2 | 5 |00:00:00.01 | 12 |
| 3 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 2 | 2 |00:00:00.01 | 7 |
|* 5 | INDEX RANGE SCAN | T1_N | 2 | 2 | 2 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 2 | 1 | 2 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------
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
28 rows selected.
特点：1、SQL语句只有被执行过，才能得到其执行计划。故，得到执行计划时间的快慢，取决于SQL语句运行时间的长短。有SQL语句执行结果的输出。
2、可以看到表被访问的次数（执行计划中的STARTS列内容）。
3、可以看到执行计划各步骤中，实际得到的结果集行数（A-ROWS）与评估得到的结果集行数（E-ROWS）。利用该信息，可以判断统计信息是否准确。
4、可以看到各执行计划步骤实际经历的时间（A-TIME），利用该信息，可以快速找到最耗时的执行计划步骤。
5、无统计信息的输出，但执行计划中有逻辑读次数的信息（执行计划中的BUFFERS列内容）。
(2)、 dbms_xplan.display_cursor()输入SQL_ID的方法
先通过以下SQL，获取相应SQL的SQL_ID.
注：使用这种方法，要求相应SQL的执行计划还在内存中，即还没有被排出SHARED POOL。否则，查询结果会为空。
SQL> SELECT sql_id,CHILD_NUMBER FROM v$sql A WHERE sql_text LIKE 'SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)%';
SQL_ID CHILD_NUMBER
------------- ------------
cshvm6ngravw0 0
SQL> select * from table(dbms_xplan.display_cursor('cshvm6ngravw0'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
SQL_ID cshvm6ngravw0, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
Plan hash value: 128660979
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 4198 | 8 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 116 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 2 | | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (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
28 rows selected.
特点：1、SQL语句只有被执行过，才能得到其执行计划。故，得到执行计划时间的快慢，取决于SQL语句运行时间的长短。有SQL语句执行结果的输出。
2、无统计信息
方法4： 10046 trace跟踪
SQL> alter session set events '10046 trace name context forever,level 12'; --打开跟踪
Session altered.
SQL> SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19); --执行相应的SQL
ID N
---------- ----------
CONTENTS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
ID T1_ID N
---------- ---------- ----------
CONTENTS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
18 18
yFfYpcNtsxEpvWUOceJmHtvpCEbcJTUTeKExdHlGlwoIFEgmOo
18 18 18
OWCPIKKFQTMBZAVJBUGPOGOZGPHCMTGFDJTMCIZXRFVXYCATTY
19 19
jAOYAJHBzwhNWHImZeFUOaxvMycbQsCLKhsUnSFwZpyctEahjK
19 19 19
BEQUUMJSUDRLTCGOCIUHSZCNNJOTZOCEQKBZPSLHGAKRTLJAHX
SQL> alter session set events '10046 trace name context off'; --关闭跟踪。
Session altered.
--通过以下SQL，获取跟踪期间产生的跟踪文件的所在位置。
SQL> 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,
2 3 4 5 6 7 8 9 10 (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; 11 12 13 14 15 16
TRACE_FILE_NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oracle/app/oracle/admin/orcl/udump/orcl_ora_2509.trc
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining
and Real Application Testing options
--通过以下命令，进行跟踪文件的转换，以方便阅读。
[oracle@localhost ~]$ tkprof /oracle/app/oracle/admin/orcl/udump/orcl_ora_2509.trc 10046.txt sys=no sort=prsela,exeela,fchela
TKPROF: Release 10.2.0.5.0 - Production on Sun Apr 27 11:06:18 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
[oracle@localhost ~]$ ll
total 280
-rw-r--r-- 1 oracle oinstall 5537 Apr 27 11:06 10046.txt
-rw-r--r-- 1 oracle oinstall 797 Feb 9 12:14 demo.txt
-rw-r--r-- 1 oracle oinstall 30335 Nov 23 14:29 zyash.html
-rw-r--r-- 1 oracle oinstall 234760 Nov 23 14:04 zyawr.rpt
[oracle@localhost ~]$ cat 10046.txt
TKPROF: Release 10.2.0.5.0 - Production on Sun Apr 27 11:06:18 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: /oracle/app/oracle/admin/orcl/udump/orcl_ora_2509.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
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 14 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 14 0 2
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID T2 (cr=14 pr=0 pw=0 time=142 us)
5 NESTED LOOPS (cr=12 pr=0 pw=0 time=79 us)
2 INLIST ITERATOR (cr=7 pr=0 pw=0 time=53 us)
2 TABLE ACCESS BY INDEX ROWID T1 (cr=7 pr=0 pw=0 time=46 us)
2 INDEX RANGE SCAN T1_N (cr=5 pr=0 pw=0 time=33 us)(object id 51619)
2 INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=25 us)(object id 51620)
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 30.60 30.60
特点：1、SQL语句只有被执行过，才能得到其执行计划。故，得到执行计划时间的快慢，取决于SQL语句运行时间的长短。有SQL语句执行结果的输出。
2、获取执行计划的步骤较复杂。
3、解析时间和执行时间分别列出
4、可以看到相关的统计信息（逻辑读、物理读、写等，执行计划中的每一步执行时间精确到微秒）
5、如果SQL语句中有函数调用，SQL中有SQL，将会都被列出
6、看不到谓词信息
方法5： awrsqrpt.sql
该方法主要用于获取已经被排出SHARED POOL，即，无法在v$sql中查询出来，但仍在AWR快照中的历史SQL。
SQL> @?/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1347935162 ORCL 1 orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1347935162 1 ORCL orcl localhost.lo
caldomain
Using 1347935162 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 533 27 Apr 2014 09:59 1
534 27 Apr 2014 11:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 533
Begin Snapshot Id specified: 533
Enter value for end_snap: 534
End Snapshot Id specified: 534
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: cshvm6ngravw0
SQL ID specified: cshvm6ngravw0
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_533_534.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrsqlrpt_1_533_534.html
--以下为生成的报告的内容：
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
|
DB Name |
DB Id |
Instance |
Inst num |
Release |
RAC |
Host |
|
ORCL |
1347935162 |
orcl |
1 |
10.2.0.5.0 |
NO |
localhost.localdomain |
|
Snap Id |
Snap Time |
Sessions |
Cursors/Session |
|
|
Begin Snap: |
533 |
27-Apr-14 09:59:56 |
22 |
2.1 |
|
End Snap: |
534 |
27-Apr-14 11:00:57 |
22 |
4.0 |
|
Elapsed: |
|
61.02 (mins) |
|
|
|
DB Time: |
|
0.10 (mins) |
|
|
SQL Summary
|
SQL Id |
Elapsed Time (ms) |
Module |
Action |
SQL Text |
|
68 |
SQL*Plus |
|
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18, 19)... |
SQL ID: cshvm6ngravw0
- 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range
- SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
|
# |
Plan Hash Value |
Total Elapsed Time(ms) |
Executions |
1st Capture Snap ID |
Last Capture Snap ID |
|
68 |
1 |
534 |
534 |
Plan 1(PHV: 128660979)
Plan Statistics
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
|
Stat Name |
Statement Total |
Per Execution |
% Snap Total |
|
Elapsed Time (ms) |
68 |
67.82 |
1.10 |
|
CPU Time (ms) |
45 |
44.99 |
1.36 |
|
Executions |
1 |
|
|
|
Buffer Gets |
225 |
225.00 |
0.10 |
|
Disk Reads |
0 |
0.00 |
0.00 |
|
Parse Calls |
1 |
1.00 |
0.01 |
|
Rows |
2 |
2.00 |
|
|
User I/O Wait Time (ms) |
0 |
|
|
|
Cluster Wait Time (ms) |
0 |
|
|
|
Application Wait Time (ms) |
0 |
|
|
|
Concurrency Wait Time (ms) |
0 |
|
|
|
Invalidations |
0 |
|
|
|
Version Count |
1 |
|
|
|
Sharable Mem(KB) |
27 |
|
|
Back to Plan 1(PHV: 128660979)
Back to Top
Execution Plan
|
Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
Time |
|
0 |
SELECT STATEMENT |
|
|
|
8 (100) |
|
|
1 |
TABLE ACCESS BY INDEX ROWID |
T2 |
1 |
2041 |
2 (0) |
00:00:01 |
|
2 |
NESTED LOOPS |
|
2 |
4198 |
8 (0) |
00:00:01 |
|
3 |
INLIST ITERATOR |
|
|
|
|
|
|
4 |
TABLE ACCESS BY INDEX ROWID |
T1 |
2 |
116 |
4 (0) |
00:00:01 |
|
5 |
INDEX RANGE SCAN |
T1_N |
2 |
|
2 (0) |
00:00:01 |
|
6 |
INDEX RANGE SCAN |
T2_T1_ID |
1 |
|
1 (0) |
00:00:01 |
- dynamic sampling used for this statement
Back to Plan 1(PHV: 128660979)
Back to Top
Full SQL Text
|
SQL Id |
SQL Text |
|
cshvm6ngravw0 |
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18, 19) |
特点：1、SQL语句只有被执行过，才能得到其执行计划。
2、可以看到已经从SHARED_POOL中被清除的执行计划。
3、看不到谓词信息。
综上，对各种获取执行计划的方法适用场景归纳如下：
1. 如果某SQL执行时间较长，而又需要尽快看到执行计划，可以用explain plan for和dbms_xplan.display_cursor指定SQL_ID的方法；
2. 跟踪某条SQL最简单的方法是explain plan for，其次就是set autotrace on；
3. 如果想观察到某条SQL有多条执行计划的情况，应使用dbms_xplan.display_cursor指定SQL_ID和CHILD_NUMBER的方法和查看指定SQL_ID语句的AWR报告的方法；
4. 如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，应使用10046跟踪事件的方法；
5. 要想获取表被访问的次数，应使用设置会话选项为statistics_level=all后，用dbms_xplan.display_cursor(null,null,’allstats last’)查看执行计划的方法；
上述方法中，哪些才能获得真实的执行计划？
顾名思义，只有被真正执行过的SQL，其所对应的执行计划才是真实的，反之，则不能保证你所看到的执行计划与真实执行时所采用的执行计划相同。
因此，上述方法中
1、explain for ...
不能保证获取到真正的执行计划。
而剩下的四种方法：
2、set autotrace on
3、 dbms_xplan.display_cursor
4、 10046 trace跟踪
5、 awrsqrpt.sql
都可以获得真实的执行计划。但有一点需要注意，如果SQL中包含有绑定变量，那么用set autotrace on方法所获取到的执行计划，也有可能不是真实的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21251711/viewspace-2120217/ ，如需转载，请注明出处，否则将追究法律责任。
