Oracle获取执行计划的五种方法

--Oracle获取执行计划的五种方法


1.执行EXPLAIN PLAN语句然后查询其输出所写入的表。
2.查询动态性能视图来显示缓存在库缓存中的执行计划。
3.使用实时监控（Real-time Monitoring）来获取关于正在执行或刚刚执行完毕的SQL信息。
4.查询自动负载存储库（AWR）。

5.激活跟踪功能提供执行计划。

1.EXPLAIN PLAN 语句

EXPLAIN PLAN
   [ SET STATEMENT_ID = string ]
   [ INTO [ schema. ] table [ @ dblink ] ]
FOR statement ;

SET STATEMENT_ID Clause：
Specify a value for the STATEMENT_ID column for the rows of the execution plan in the output table. You can then use this value to identify these rows among others in the output table. Be sure to specify a STATEMENT_ID value if your output table contains rows from many execution plans. If you omit this clause, then the STATEMENT_ID value defaults to null.

INTO table Clause：
Specify the name of the output table, and optionally its schema and database. This table must exist before you use the EXPLAIN PLAN statement. --如果不存在执行$ORACLE_HOME/rdbms/admin/utlxplan.sql
If you omit schema, then the database assumes the table is in your own schema.
The dblink can be a complete or partial name of a database link to a remote Oracle Database where the output table is located. You can specify a remote output table only if you are using Oracle Database distributed functionality. If you omit dblink, then the database assumes the table is on your local database. See "References to Objects in Remote Databases" for information on referring to database links.
If you omit INTO altogether, then the database assumes an output table named PLAN_TABLE in your own schema on your local database.

FOR statement Clause：
Specify a SELECT, INSERT, UPDATE, DELETE, MERGE, CREATE TABLE, CREATE INDEX, or ALTER INDEX ... REBUILD statement for which the execution plan is generated.

--实验
SYS@PROD1> explain plan for select * from t;

Explained.

SYS@PROD1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |  2000 |  8000 |   138   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T	 |  2000 |  8000 |   138   (1)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

--指定参数
SYS@PROD1> explain plan set statement_id='tt' into plan_table for select * from t;

Explained.

SYS@PROD1> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |  2000 |  8000 |   138   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T	 |  2000 |  8000 |   138   (1)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SYS@PROD1> select statement_id,plan_id,operation,object_name,cost,cpu_cost from plan_table;

STATEMENT_    PLAN_ID OPERATION 		     OBJECT_NAM       COST   CPU_COST
---------- ---------- ------------------------------ ---------- ---------- ----------
		  302 SELECT STATEMENT				       138    7421440
		  302 TABLE ACCESS		     T		       138    7421440
tt		  303 SELECT STATEMENT				       138    7421440
tt		  303 TABLE ACCESS		     T		       138    7421440

2.动态性能视图
相关视图：v$sql_plan
		  v$sql_plan_statistics
		  v$sql_workarea
		  v$sql_plan_statistics_all
		  
(1).获取当前连接到实例的有关SQL信息
--此时另一session的EODA用户正在执行select count（*） from big_table；
SYS@PROD1> select sql_id,sql_child_number from v$session where username='EODA';

SQL_ID	      SQL_CHILD_NUMBER
------------- ----------------
6fxg0qj7j7ftd		     0

(2).仅通过SQL语句内容就可获得有关SQL信息
SYS@PROD1> select sql_id,child_number,sql_text from v$sql where sql_fulltext like '%big_table%' and sql_text not like '%v$sql%';

SQL_ID	      CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------
6fxg0qj7j7ftd		 0 select count(*) from big_table


SYS@PROD1> select * from table(dbms_xplan.display_cursor('6fxg0qj7j7ftd',0));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	6fxg0qj7j7ftd, child number 0
-------------------------------------
select count(*) from big_table

Plan hash value: 1764098166

------------------------------------------------------------------------------
| Id  | Operation	      | Name	     | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	     |	     |	3213 (100)|	     |
|   1 |  SORT AGGREGATE       | 	     |	   1 |		  |	     |
|   2 |   INDEX FAST FULL SCAN| BIG_TABLE_PK |	9980K|	3213   (4)| 00:00:01 |
------------------------------------------------------------------------------


14 rows selected.

3.AWR
捕获某个快照时，AWR能够收集执行计划，其中都保存在dba_hist_sql_plan中。

--此处实验紧跟在上述的动态性能视图实验后
SYS@PROD1> select count(*) from dba_hist_sql_plan;

  COUNT(*)
----------
     10450

SYS@PROD1> select * from table(dbms_xplan.display_awr('6fxg0qj7j7ftd'));  --无结果

no rows selected

手动生成一个新的AWR报表：
SYS@PROD1> exec dbms_workload_repository.create_snapshot;

SYS@PROD1> start /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql
End of Report
</body></html>
Report written to awrrpt_1_591_592.html

SYS@PROD1> select count(*) from dba_hist_sql_plan;  --视图内记录数变多

  COUNT(*)
----------
     10671

SYS@PROD1> select * from table(dbms_xplan.display_awr('6fxg0qj7j7ftd'));  --此时查询可以获得结果

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 6fxg0qj7j7ftd
--------------------
select count(*) from big_table

Plan hash value: 1764098166

------------------------------------------------------------------------------
| Id  | Operation	      | Name	     | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	     |	     |	3213 (100)|	     |
|   1 |  SORT AGGREGATE       | 	     |	   1 |		  |	     |
|   2 |   INDEX FAST FULL SCAN| BIG_TABLE_PK |	9980K|	3213   (4)| 00:00:40 |
------------------------------------------------------------------------------


14 rows selected.

4.跟踪工具
(1).10053事件
ORA-10053: CBO Enable optimizer trace

SYS@PROD1> alter session set events '10053 trace name context forever';

Session altered.

SYS@PROD1> select count(*) from eoda.big_table where object_name like '%A%';

  COUNT(*)
----------
   3360615

SYS@PROD1> alter session set events '10053 trace name context off';

Session altered.

SYS@PROD1> oradebug setmypid
Statement processed.
SYS@PROD1> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_5522.trc

--以下摘取自trace文件中
----- Current SQL Statement for this session (sql_id=980c0hxvqt1yn) -----
select count(*) from eoda.big_table where object_name like '%A%'
sql_text_length=65
sql=select count(*) from eoda.big_table where object_name like '%A%'
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------------+-----------------------------------+-------------------------+
| Id  | Operation               | Name     | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib |
-------------------------------------------+-----------------------------------+-------------------------+
| 0   | SELECT STATEMENT        |          |       |       |   11K |           |      |      |           |
| 1   |  SORT AGGREGATE         |          |     1 |    25 |       |           |      |      |           |
| 2   |   PX COORDINATOR        |          |       |       |       |           |      |      |           |
| 3   |    PX SEND QC (RANDOM)  | :TQ10000 |     1 |    25 |       |           |:Q1000| P->S |QC (RANDOM)|
| 4   |     SORT AGGREGATE      |          |     1 |    25 |       |           |:Q1000| PCWP |           |
| 5   |      PX BLOCK ITERATOR  |          | 3269K |   80M |   11K |  00:02:21 |:Q1000| PCWC |           |
| 6   |       TABLE ACCESS FULL | BIG_TABLE| 3269K |   80M |   11K |  00:02:21 |:Q1000| PCWP |           |
-------------------------------------------+-----------------------------------+-------------------------+
Predicate Information:
----------------------
6 - access(:Z>=:Z AND :Z<=:Z)
6 - filter("OBJECT_NAME" LIKE '%A%')

(2).10132事件
ORA-10132: dump plan after compilation

使用方法与10053相似，此处不再赘述。