DBMS_XPLAN学习

2120阅读　0评论2014-11-20　摸_摸
分类：Oracle

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>




 











 






上一篇：脚本安装 zabbix-agent
下一篇：如何利用客户端在CU发博客