查看执行计划的方法

最新推荐文章于 2024-05-07 15:21:43 发布

Yuanpeng.Li

最新推荐文章于 2024-05-07 15:21:43 发布

阅读量2.1k

点赞数

分类专栏： Oracle 11G 日常运维文章标签： Oracle执行计划

本文链接：https://blog.csdn.net/lyp13696402570/article/details/85097605

版权

Oracle 11G 同时被 2 个专栏收录

31 篇文章 0 订阅

订阅专栏

日常运维

15 篇文章 0 订阅

订阅专栏

查看执行计划的方法

1、Explain Plan For SQL

statistics_level设为ALL

查看执行计划的方法

在简单的执行计划中，执行计划的列分别有：Id，Operation，Name，Rows，Bytes，Cost (%CPU)，Time

Id，Operation，Name——这三项是每个执行计划中都有的

Rows，Bytes，Cost (%CPU)，Time——这四项是Oracle预估的值

Rows：指Oracle预估这一步返回多少行记录

Bytes：指Rows返回记录占用的空间

Cost (%CPU)：指Oracle内部计算的一个代价

Time：指Oracle预估的耗时。

正常情况下，Rows，Bytes，Cost (%CPU)，Time这四个值只是估算值，根据统计信息，主机性能以及一些指标计算出来的、内部认为的、估算出来的值，这些值可能是准确的，也可能是不准确的。可以作为参考，但是不能作为依据。

1、Explain Plan For SQL

实际执行SQL语句，生成的计划未必是真实执行的计划

必须要有plan_table

1.1 查看plan_table

SQL> desc plan_table
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
OTHER_XML CLOB
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)

SQL>

1.2 解析SQL执行计划

SQL> explain plan for select * from liyuper where employee_id=100;

Explained.

SQL>

1.3 查看执行计划

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

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIYUPER | 1 | 69 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T2_ID | 1 | | 1 (0) | 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPLOYEE_ID"=100)

14 rows selected.

SQL>

2、SQLPLUS AUTOTRACE

除set autotrace traceonly explain外均实际执行SQL，但仍未必是真实计划

必须有plan_table

2.1 查看plan_table

SQL>

2.2 查看SQL执行计划

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

SQL> set autotrace on

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY from liyuper where employee_id=100;

EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
100 Steven King 200

Execution Plan
----------------------------------------------------------
Plan hash value: 2406067938

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIYUPER | 1 | 23 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T2_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPLOYEE_ID"=100)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
755 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

3、SQL TRACE

需要启用10046或者SQL TRACE

一般用tkprof看得更清楚些，当然10046里本身也有执行计划信息

3.1 执行SQL TRACE

SQL> alter session set sql_trace=true;

Session altered.

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY from liyuper where employee_id=100;

EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
100 Steven King 200

SQL> alter session set sql_trace=false;

Session altered.

SQL> show parameter user_dump

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/lyp
db/lypdb/trace
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lypos ~]$

3.2 查看trc文件中的执行计划

[oracle@lypos ~]$ cd /u01/app/oracle/diag/rdbms/lypdb/lypdb/trace
[oracle@lypos trace]$ ls -rlt |head
total 152
-rw-r-----. 1 oracle oinstall 142144 Dec 10 11:40 alert_lypdb.log
-rw-r----- 1 oracle oinstall 95 Dec 10 15:36 lypdb_ora_4031.trm
-rw-r----- 1 oracle oinstall 2872 Dec 10 15:36 lypdb_ora_4031.trc
[oracle@lypos trace]$ tkprof lypdb_ora_4031.trc lypdb_ora_4031.tk.log

TKPROF: Release 11.2.0.4.0 - Development on Mon Dec 10 15:37:14 2018

[oracle@lypos trace]$ cat lypdb_ora_4031.tk.log

TKPROF: Release 11.2.0.4.0 - Development on Mon Dec 10 15:37:14 2018

Trace file: lypdb_ora_4031.trc
Sort options: default

*********************************省略***********************************************

Optimizer mode: ALL_ROWS
Parsing user id: 85
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID LIYUPER (cr=3 pr=0 pw=0 time=161 us cost=2 size=23 card=1)
1 1 1 INDEX RANGE SCAN I_T2_ID (cr=2 pr=0 pw=0 time=58 us cost=1 size=0 card=1)(object id 88098)

********************************************************************************

SQL ID: aam2chsgpj7mb Plan Hash: 0

alter session set sql_trace=false

***********************************省略*********************************************

Trace file: lypdb_ora_4031.trc
Trace file compatibility: 11.1.0.7
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
55 lines in trace file.
18 elapsed seconds in trace file.

[oracle@lypos trace]$

4、V$SQL和V$SQL_PLAN

可以查询到多个子游标的计划信息了，但是看起来比较费劲

4.1 session2执行SQL：

SQL> conn liyuper/liyuper;
Connected.
SQL> select count(*) from dba_objects,dba_objects,dba_objects,dba_objects;

4.2 session1查看SQL_ID

SQL> select SQL_ID,LAST_CALL_ET,PROGRAM,USERNAME from v$session where type !='BACKGROUND' and status = 'ACTIVE';

SQL_ID LAST_CALL_ET PROGRAM USERNAME
------------- ------------ ------------------------------------------------ ------------------------------
6h5xm74kx8n9k 0 sqlplus@lypos (TNS V1-V3) LIYUPER
1pw97a04vpt4h 96 sqlplus@lypos (TNS V1-V3) LIYUPER

SQL>

4.3 通过sql_id查看执行计划

SQL> select * from table(dbms_xplan.display_cursor('1pw97a04vpt4h',null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1pw97a04vpt4h, child number 0
-------------------------------------
select count(*) from dba_objects,dba_objects,dba_objects,dba_objects

Plan hash value: 198298237

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 190P(100)| |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | MERGE JOIN CARTESIAN | | 18E| | 190P (1)|999:59:59 |
| 3 | MERGE JOIN CARTESIAN | | 644T| | 2208G (1)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 7461M| | 25M (1)| 85:13:08 |
| 5 | VIEW | DBA_OBJECTS | 86382 | | 296 (2)| 00:00:04 |
| 6 | UNION-ALL | | | | | |
|* 7 | FILTER | | | | | |
|* 8 | HASH JOIN | | 86381 | 9279K| 296 (2)| 00:00:04 |
| 9 | INDEX FULL SCAN | I_USER2 | 89 | 356 | 1 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 86381 | 8941K| 295 (2)| 00:00:04 |
| 11 | INDEX FULL SCAN | I_USER2 | 89 | 1958 | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | OBJ$ | 86381 | 7085K| 293 (1)| 00:00:04 |
| 13 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 14 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 17 | 0 (0)| |
| 17 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| |
| 18 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 0 (0)| |
|* 19 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 20 | BUFFER SORT | | 86382 | | 25M (1)| 85:13:08 |
| 21 | VIEW | DBA_OBJECTS | 86382 | | 296 (2)| 00:00:04 |
| 22 | UNION-ALL | | | | | |
|* 23 | FILTER | | | | | |
|* 24 | HASH JOIN | | 86381 | 9279K| 296 (2)| 00:00:04 |
| 25 | INDEX FULL SCAN | I_USER2 | 89 | 356 | 1 (0)| 00:00:01 |
|* 26 | HASH JOIN | | 86381 | 8941K| 295 (2)| 00:00:04 |
| 27 | INDEX FULL SCAN | I_USER2 | 89 | 1958 | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | OBJ$ | 86381 | 7085K| 293 (1)| 00:00:04 |
| 29 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 30 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
| 32 | NESTED LOOPS | | 1 | 17 | 0 (0)| |
| 33 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| |
| 34 | TABLE ACCESS CLUSTER| USER$ | 1 | 4 | 0 (0)| |
|* 35 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 36 | BUFFER SORT | | 86382 | | 2208G (1)|999:59:59 |
| 37 | VIEW | DBA_OBJECTS | 86382 | | 296 (2)| 00:00:04 |
| 38 | UNION-ALL | | | | | |
|* 39 | FILTER | | | | | |
|* 40 | HASH JOIN | | 86381 | 9279K| 296 (2)| 00:00:04 |
| 41 | INDEX FULL SCAN | I_USER2 | 89 | 356 | 1 (0)| 00:00:01 |
|* 42 | HASH JOIN | | 86381 | 8941K| 295 (2)| 00:00:04 |
| 43 | INDEX FULL SCAN | I_USER2 | 89 | 1958 | 1 (0)| 00:00:01 |
|* 44 | TABLE ACCESS FULL | OBJ$ | 86381 | 7085K| 293 (1)| 00:00:04 |
| 45 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 46 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
| 48 | NESTED LOOPS | | 1 | 17 | 0 (0)| |
| 49 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| |
| 50 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 0 (0)| |
|* 51 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
| 52 | BUFFER SORT | | 86382 | | 190P (1)|999:59:59 |
| 53 | VIEW | DBA_OBJECTS | 86382 | | 296 (2)| 00:00:04 |
| 54 | UNION-ALL | | | | | |
|* 55 | FILTER | | | | | |
|* 56 | HASH JOIN | | 86381 | 9279K| 296 (2)| 00:00:04 |
| 57 | INDEX FULL SCAN | I_USER2 | 89 | 356 | 1 (0)| 00:00:01 |
|* 58 | HASH JOIN | | 86381 | 8941K| 295 (2)| 00:00:04 |
| 59 | INDEX FULL SCAN | I_USER2 | 89 | 1958 | 1 (0)| 00:00:01 |
|* 60 | TABLE ACCESS FULL | OBJ$ | 86381 | 7085K| 293 (1)| 00:00:04 |
| 61 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 62 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
| 64 | NESTED LOOPS | | 1 | 17 | 0 (0)| |
| 65 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| |
| 66 | TABLE ACCESS CLUSTER | USER$ | 1 | 4 | 0 (0)| |
|* 67 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
"O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#")
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 IS NOT NULL))))
8 - access("O"."SPARE3"="U"."USER#")
10 - access("O"."OWNER#"="U"."USER#")
12 - filter(("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0))
14 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curr
ent_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur
rent_edition_id'))))
15 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
"O2"."OWNER#"="U2"."USER#")
19 - access("L"."OWNER#"="U"."USER#")
23 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
"O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#")
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 IS NOT NULL))))
24 - access("O"."SPARE3"="U"."USER#")
26 - access("O"."OWNER#"="U"."USER#")
28 - filter(("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0))
30 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curr
ent_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur
rent_edition_id'))))
31 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
"O2"."OWNER#"="U2"."USER#")
35 - access("L"."OWNER#"="U"."USER#")
39 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
"O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#")
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 IS NOT NULL))))
40 - access("O"."SPARE3"="U"."USER#")
42 - access("O"."OWNER#"="U"."USER#")
44 - filter(("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0))
46 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curr
ent_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur
rent_edition_id'))))
47 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
"O2"."OWNER#"="U2"."USER#")
51 - access("L"."OWNER#"="U"."USER#")
55 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
"O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#")
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 IS NOT NULL))))
56 - access("O"."SPARE3"="U"."USER#")
58 - access("O"."OWNER#"="U"."USER#")
60 - filter(("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0))
62 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curr
ent_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','cur
rent_edition_id'))))
63 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
"O2"."OWNER#"="U2"."USER#")
67 - access("L"."OWNER#"="U"."USER#")

159 rows selected.

SQL>

5、SQL Monitor

准确显示各个步骤实际执行结果

6、其他第三方工具

注意PL/SQL developer之类工具F5看到的执行计划未必是真实的

这种查看执行计划的原理和本质跟第一种方式是一样的。

最详细的执行计划

通过sql_id查执行计划

一般的执行计划，只能通过cost的大小来看出问题所在的地方。但是这个方法不一定完全正确。

session执行SQL：

SQL> select /*+gather_plan_statistics */ count(*) from dba_objects;

COUNT(*)
----------
86480

SQL>

查找出SQL_ID

SQL> select sql_id from v$sql where sql_text like 'select /*+gather_plan_statistics */ count(*) from dba_objects';

SQL_ID
-------------
c8trdy942j9a9

SQL>

通过sql_id查看执行计划

如果是同一个会话，可以忽略sql_id，直接执行select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

就会默认的出来刚才执行的SQL的执行计划。（最后展示出来）

SQL> select * from table(dbms_xplan.display_cursor('c8trdy942j9a9',null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c8trdy942j9a9, child number 0
-------------------------------------
select /*+gather_plan_statistics */ count(*) from dba_objects

Plan hash value: 2509769952

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 1075 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 1075 | | | |
| 2 | VIEW | DBA_OBJECTS | 1 | 86382 | 86435 |00:00:00.25 | 1075 | | | |
| 3 | UNION-ALL | | 1 | | 86435 |00:00:00.23 | 1075 | | | |
|* 4 | FILTER | | 1 | | 86435 |00:00:00.11 | 1074 | | | |
|* 5 | HASH JOIN | | 1 | 86381 | 86435 |00:00:00.08 | 1074 | 2440K| 2440K| 1/0/0|
| 6 | INDEX FULL SCAN | I_USER2 | 1 | 89 | 90 |00:00:00.01 | 1 | | | |
|* 7 | HASH JOIN | | 1 | 86381 | 86435 |00:00:00.06 | 1073 | 1696K| 1696K| 1/0/0|
| 8 | INDEX FULL SCAN | I_USER2 | 1 | 89 | 90 |00:00:00.01 | 1 | | | |
|* 9 | TABLE ACCESS FULL | OBJ$ | 1 | 86381 | 86435 |00:00:00.03 | 1072 | | | |
| 10 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 12 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 13 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 1 | | | |
| 14 | INDEX FULL SCAN | I_LINK1 | 1 | 1 | 0 |00:00:00.01 | 1 | | | |
| 15 | TABLE ACCESS CLUSTER| USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 16 | INDEX UNIQUE SCAN | I_USER# | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
"O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87
AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") 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 IS NOT NULL))))
5 - access("O"."SPARE3"="U"."USER#")
7 - access("O"."OWNER#"="U"."USER#")
9 - filter(("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
"O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0))
11 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
12 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
16 - access("L"."OWNER#"="U"."USER#")

45 rows selected.

SQL>

所以在这里加了一些hint，让他有更准确的执行计划。多了几个新的列：A-Rows（真实返回的行数）、A-Time（准确的耗费时间）、Buffers（逻辑读）

SQL> select count(*) from OBJ$;

COUNT(*)
----------
86440

SQL>

这里可以看到这个86440，是大于上面返回的86535的，原因就是因为前面有个“*”号，下面有条件进行筛选。

SQL> select count(*) from OBJ$ o
2 where (("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
3 "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0))
4 ;

COUNT(*)
----------
86435

SQL>

这里看起来就一样了。

SQL> set autot on

SQL> select count(*) from OBJ$ o
2 where (("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
3 "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0))
4 ;

COUNT(*)
----------
86435

Execution Plan
----------------------------------------------------------
Plan hash value: 23986678

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 293 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 84 | | |
|* 2 | TABLE ACCESS FULL| OBJ$ | 86381 | 7085K| 293 (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1072 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

这里可以看到逻辑读是1072，对比上面的执行计划显示的1072是一致的。

不通过sql_id查执行计划

同一会话，跳过查询sql_id的步骤：

SQL> select /*+ gather_plan_statistics */ count(*) from dba_objects;

COUNT(*)
----------
86435

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 86pdr43g2p8fd, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from dba_objects

Plan hash value: 2509769952

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 2 |00:00:00.19 | 2150 | | | |
| 1 | SORT AGGREGATE | | 2 | 1 | 2 |00:00:00.19 | 2150 | | | |
| 2 | VIEW | DBA_OBJECTS | 2 | 86382 | 172K|00:00:00.47 | 2150 | | | |
| 3 | UNION-ALL | | 2 | | 172K|00:00:00.41 | 2150 | | | |
|* 4 | FILTER | | 2 | | 172K|00:00:00.35 | 2148 | | | |
|* 5 | HASH JOIN | | 2 | 86381 | 172K|00:00:00.25 | 2148 | 2440K| 2440K| 2/0/0|
| 6 | INDEX FULL SCAN | I_USER2 | 2 | 89 | 180 |00:00:00.01 | 2 | | | |
|* 7 | HASH JOIN | | 2 | 86381 | 172K|00:00:00.16 | 2146 | 1696K| 1696K| 2/0/0|
| 8 | INDEX FULL SCAN | I_USER2 | 2 | 89 | 180 |00:00:00.01 | 2 | | | |
|* 9 | TABLE ACCESS FULL | OBJ$ | 2 | 86381 | 172K|00:00:00.07 | 2144 | | | |
| 10 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 12 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 13 | NESTED LOOPS | | 2 | 1 | 0 |00:00:00.01 | 2 | | | |
| 14 | INDEX FULL SCAN | I_LINK1 | 2 | 1 | 0 |00:00:00.01 | 2 | | | |
| 15 | TABLE ACCESS CLUSTER| USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 16 | INDEX UNIQUE SCAN | I_USER# | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

45 rows selected.

SQL>

如果我们没有加hint：随便执行一条SQL，然后查详细的执行计划

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dyk4dprp70d74, child number 0
-------------------------------------
SELECT DECODE('A','A','1','2') FROM DUAL

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

13 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 738a0r0utp4wv, child number 0
-------------------------------------
select * from table(dbms_xplan.display_cursor())

Plan hash value: 3713220770

---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 8168 |
---------------------------------------------------------------------

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

19 rows selected.

SQL>

就会报一个错：

* hint 'gather_plan_statistics' is used for the statement or

加个hint

* parameter 'statistics_level' is set to 'ALL', at session or system level

或者把statistics_level设为ALL

因为加了ALLSTATS，所以他会去访问plan statistics执行计划的统计信息，然后给出以上两种方式

statistics_level设为ALL

SQL> alter session set statistics_level=ALL;

Session altered.

SQL> select count(*) from dba_tables;

COUNT(*)
----------
2826

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 56bs32ukywdsq, child number 0
-------------------------------------
select count(*) from dba_tables

Plan hash value: 1678981737

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.15 | 3199 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.15 | 3199 | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 1 | 6095 | 2826 |00:00:00.15 | 3199 | 1645K| 1645K| 1/0/0|
| 3 | TABLE ACCESS FULL | SEG$ | 1 | 5867 | 5867 |00:00:00.01 | 175 | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 1 | 2828 | 2826 |00:00:00.14 | 3024 | 2440K| 2440K| 1/0/0|
| 5 | INDEX FULL SCAN | I_USER2 | 1 | 89 | 90 |00:00:00.01 | 1 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 2828 | 2826 |00:00:00.14 | 3023 | 1519K| 1519K| 1/0/0|
|* 7 | HASH JOIN | | 1 | 2828 | 2826 |00:00:00.11 | 2765 | 2440K| 2440K| 1/0/0|
| 8 | INDEX FULL SCAN | I_USER2 | 1 | 89 | 90 |00:00:00.01 | 1 | | | |
|* 9 | HASH JOIN | | 1 | 2828 | 2826 |00:00:00.11 | 2764 | 1344K| 1344K| 1/0/0|
|* 10 | HASH JOIN OUTER | | 1 | 2828 | 2828 |00:00:00.06 | 1692 | 1301K| 1301K| 1/0/0|
|* 11 | HASH JOIN | | 1 | 2828 | 2828 |00:00:00.02 | 1434 | 2616K| 2616K| 1/0/0|
| 12 | MERGE JOIN CARTESIAN| | 1 | 7 | 7 |00:00:00.01 | 8 | | | |
|* 13 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 0 | 2293K| 2293K| 1/0/0|
|* 14 | FIXED TABLE FULL | X$KSPPI | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 15 | FIXED TABLE FULL | X$KSPPCV | 1 | 100 | 2914 |00:00:00.01 | 0 | | | |
| 16 | BUFFER SORT | | 1 | 7 | 7 |00:00:00.01 | 8 | 73728 | 73728 | |
| 17 | TABLE ACCESS FULL | TS$ | 1 | 7 | 7 |00:00:00.01 | 8 | | | |
|* 18 | TABLE ACCESS FULL | TAB$ | 1 | 2828 | 2828 |00:00:00.01 | 1426 | | | |
| 19 | INDEX FAST FULL SCAN | I_OBJ1 | 1 | 86392 | 86440 |00:00:00.01 | 258 | | | |
|* 20 | TABLE ACCESS FULL | OBJ$ | 1 | 86392 | 86437 |00:00:00.02 | 1072 | | | |
| 21 | INDEX FAST FULL SCAN | I_OBJ1 | 1 | 86392 | 86440 |00:00:00.01 | 258 | | | |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND "T"."TS#"="S"."TS#")
4 - access("CX"."OWNER#"="CU"."USER#")
6 - access("T"."DATAOBJ#"="CX"."OBJ#")
7 - access("O"."OWNER#"="U"."USER#")
9 - access("O"."OBJ#"="T"."OBJ#")
10 - access("T"."BOBJ#"="CO"."OBJ#")
11 - access("T"."TS#"="TS"."TS#")
13 - access("KSPPI"."INDX"="KSPPCV"."INDX")
14 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
18 - filter(BITAND("T"."PROPERTY",1)=0)
20 - filter(BITAND("O"."FLAGS",128)=0)

48 rows selected.

SQL>

Yuanpeng.Li

关注

0
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
查看执行计划的方法

目录查看执行计划的方法1、Explain Plan For SQL1.1 查看plan_table1.2 解析SQL执行计划1.3 查看执行计划2、SQLPLUS AUTOTRACE2.1 查看plan_table2.2 查看SQL执行计划3、SQL TRACE3.1 执行SQL TRACE3.2 查看trc文件中的执行计划4、V$SQL和V$SQL...
复制链接

扫一扫