DBMS_XPLAN学习

DBMS_XPLAN学习

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm#CACFJGHG

DBMS_XPLAN 有五个功能:

DISPLAY - to format and display the contents of a plan table.

 

DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.

 

DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.

 

DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle(11G新特性 SPM sql plan management)

 

DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.

先看第一种

display 直接查看目标sql语句预估的执行计划

SQL> explain plan for select * from emp where empno > 7900;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 169057108

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

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

|   0 | SELECT STATEMENT            |        |     1 |    38 |     2   (0)| 00:0

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     2   (0)| 00:0

|*  2 |   INDEX RANGE SCAN          | PK_EMP |     1 |       |     1   (0)| 00:0

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

Predicate Information (identified by operation id):

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

   2 - access("EMPNO">7900)

14 rows selected

SQL>  

DISPLAY_CURSOR  显示sql的真实执行计划

默认不加参数的时候显示上一条执行的sql语句的执行计划,这里有个小插曲,如果在pl sql developer里执行或者sqlplus执行过 set serveroutput on则会出错.

[oracle@my2950 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 20 14:23:05 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

14:23:05 SCOTT@mydb1>select count(*) from emp;

  COUNT(*)

----------

        14

Elapsed: 00:00:00.00

14:23:21 SCOTT@mydb1>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

8 rows selected.

Elapsed: 00:00:00.06

14:23:41 SCOTT@mydb1>

14:25:13 SCOTT@mydb1>set serveroutput off

14:25:18 SCOTT@mydb1>

14:25:19 SCOTT@mydb1>select count(*) from emp;

  COUNT(*)

----------

        14

Elapsed: 00:00:00.01

14:25:21 SCOTT@mydb1>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID  g59vz2u4cu404, child number 0

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

select count(*) from emp

Plan hash value: 2937609675

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

| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |        |       |     1 (100)|          |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

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

14 rows selected.

Elapsed: 00:00:00.32

14:25:24 SCOTT@mydb1>

也可以用来显示存储在 cursor cache 里的sql执行计划

14:49:56 SCOTT@mydb1>select /*+ 11111111 */ count(*) from emp where empno>9000;

  COUNT(*)

----------

         0

Elapsed: 00:00:00.00

14:50:16 SCOTT@mydb1>select sql_text,sql_id,child_number from v$sql where sql_text like '%111111%';

SQL_TEXT                                                                                             SQL_ID        CHILD_NUMBER

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

select /*+ 11111111 */ count(*) from emp where empno>9000                                            8m2c6qfyabqyj            0

select sql_text,sql_id,child_number from v$sql where sql_text like '%111111%'                        1ca5a8ha87r92            0

Elapsed: 00:00:00.10

14:50:26 SCOTT@mydb1>select * from table(dbms_xplan.display_cursor('8m2c6qfyabqyj',0));

PLAN_TABLE_OUTPUT

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

SQL_ID  8m2c6qfyabqyj, child number 0

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

select /*+ 11111111 */ count(*) from emp where empno>9000

Plan hash value: 109489892

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        |       |       |     1 (100)|          |

|   1 |  SORT AGGREGATE   |        |     1 |     4 |            |          |

|*  2 |   INDEX RANGE SCAN| PK_EMP |     1 |     4 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("EMPNO">9000)

19 rows selected.

Elapsed: 00:00:00.04

14:50:49 SCOTT@mydb1>

如果cursor cache里没有的话会报错

SCOTT@mydb1>select * from table(dbms_xplan.display_cursor('8m2c6qfyabqyj',0));

PLAN_TABLE_OUTPUT

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

SQL_ID: 8m2c6qfyabqyj, child number: 0 cannot be found

Elapsed: 00:00:00.05

SCOTT@mydb1>

这时候有可能会用到 display_awr

DISPLAY_AWR  查看awr中存储的sql语句的执行计划

不是所有的sql都会存储到awr报告里

SQL> select P.sql_id,P.plan_hash_value from dba_hist_sql_plan p where p.object_owner='SCOTT';

SQL_ID        PLAN_HASH_VALUE

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

0dfmuzjzug2r5      3335594643

0dfmuzjzug2r5      3335594643

SQL>  

SQL> select P.sql_id,P.plan_hash_value from dba_hist_sql_plan p where p.object_owner='ZABBIX2' and rownum<2;

SQL_ID        PLAN_HASH_VALUE

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

2gsfm3d8xkpyc      4280477550

SQL> 

SQL> select * from table(dbms_xplan.display_awr('2gsfm3d8xkpyc'));

PLAN_TABLE_OUTPUT

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

SQL_ID 2gsfm3d8xkpyc

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

select h.hostid,h.host,h.name,t.httptestid,t.name,t.variables,t.headers,

t.agent,t.authentication,t.http_user,t.http_password,t.http_proxy,t.retr

ies,t.ssl_cert_file,t.ssl_key_file,t.ssl_key_password,t.verify_peer,t.ve

rify_host from httptest t,hosts h where t.hostid=h.hostid and

t.nextcheck<=1416412955 and mod(t.httptestid,1)=0 and t.status=0 and

h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or

h.maintenance_type=0)

Plan hash value: 4280477550

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

| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU

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

|   0 | SELECT STATEMENT             |              |       |       |     4 (100

|   1 |  NESTED LOOPS                |              |     1 |   342 |     4   (0

|   2 |   TABLE ACCESS BY INDEX ROWID| HTTPTEST     |     1 |   245 |     3   (0

|   3 |    INDEX RANGE SCAN          | HTTPTEST_3   |     2 |       |     1   (0

|   4 |   TABLE ACCESS BY INDEX ROWID| HOSTS        |     1 |    97 |     1   (0

PLAN_TABLE_OUTPUT

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

|   5 |    INDEX UNIQUE SCAN         | SYS_C0027662 |     1 |       |     0   (0

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

23 rows selected

SQL>   

format 参数都是通用的,默认是typical,12c貌似多了个参数ADAPTIVE,还没有环境,回头测试一下.

        basic     仅仅显示最少的信息。基本上包括操作和操作的对象

        typical   显示大部分信息。基本上包括除了别名，提纲和字段投影外的所有信息，此为缺省值。

        serial     类似于typical，但不显示并行操作

        all          显示除提纲之外的所有信息

SCOTT@mydb1>select count(*) from emp;

  COUNT(*)

----------

        14

Elapsed: 00:00:00.00

SCOTT@mydb1>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID  g59vz2u4cu404, child number 0

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

select count(*) from emp

Plan hash value: 2937609675

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

| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |        |       |     1 (100)|          |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

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

14 rows selected.

Elapsed: 00:00:00.02

SCOTT@mydb1>select * from table(dbms_xplan.display_cursor('g59vz2u4cu404',null,'basic'));

PLAN_TABLE_OUTPUT

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

EXPLAINED SQL STATEMENT:

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

select count(*) from emp

Plan hash value: 2937609675

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

| Id  | Operation        | Name   |

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

|   0 | SELECT STATEMENT |        |

|   1 |  SORT AGGREGATE  |        |

|   2 |   INDEX FULL SCAN| PK_EMP |

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

14 rows selected.

Elapsed: 00:00:00.01

SCOTT@mydb1>

SCOTT@mydb1>select * from table(dbms_xplan.display_cursor('g59vz2u4cu404',null,'all'));

PLAN_TABLE_OUTPUT

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

SQL_ID  g59vz2u4cu404, child number 0

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

select count(*) from emp

Plan hash value: 2937609675

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

| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |        |       |     1 (100)|          |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1

   2 - SEL$1 / EMP@SEL$1

Column Projection Information (identified by operation id):

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

   1 - (#keys=0) COUNT(*)[22]

25 rows selected.

Elapsed: 00:00:00.03

SCOTT@mydb1>

 

---