方法一、查看计划表生成执行计划
explain plan for select * from SYS_USER_UNIT;
select * from table(dbms_xplan.display);
explain plan for 后面为查询语句
SQL> explain plan for select * from SYS_USER_UNIT;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3749126911
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 1292 | 3 (0)| 00:00:0
1 |
| 1 | TABLE ACCESS FULL| SYS_USER_UNIT | 19 | 1292 | 3 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
8 rows selected.
方法二、查看动态性能视图v$sql_plan，查看执行计划
select * from table(dbms_xplan.display_cursor('&sql_id',&child));
|
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',&child)); Enter value for sql_id: aq8yqxyyb40nn Enter value for child: 0 old 1: select * from table(dbms_xplan.display_cursor('&sql_id',&child)) new 1: select * from table(dbms_xplan.display_cursor('aq8yqxyyb40nn',0)) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID aq8yqxyyb40nn, child number 0 ------------------------------------- update sys.job$ set this_date=:1 where job=:2 Plan hash value: 2981428395 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 1 (100)| | 1 | UPDATE | JOB$ | | | | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 2 | INDEX UNIQUE SCAN| I_JOB_JOB | 1 | 5 | 0 (0)| --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("JOB"=:2) 19 rows selected. SQL> |
方法三、查看awr资料库中存储的执行计划
select * from table(dbms_xplan.display_awr('&sql_id'));
|
SQL> select * from table(dbms_xplan.display_awr('&sql_id')); Enter value for sql_id: aq8yqxyyb40nn old 1: select * from table(dbms_xplan.display_awr('&sql_id')) new 1: select * from table(dbms_xplan.display_awr('aq8yqxyyb40nn')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID aq8yqxyyb40nn -------------------- update sys.job$ set this_date=:1 where job=:2 Plan hash value: 2981428395 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 1 (100)| | 1 | UPDATE | JOB$ | | | | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 2 | INDEX UNIQUE SCAN| I_JOB_JOB | 1 | 5 | 0 (0)| --------------------------------------------------------------------- 14 rows selected. SQL> |
方法四、sql跟踪
|
序号 |
命令 |
解释 |
|
1 |
SET AUTOTRACE OFF |
此为默认值，即关闭Autotrace |
|
2 |
SET AUTOTRACE ON |
产生结果集和解释计划并列出统计 |
|
3 |
SET AUTOTRACE ON EXPLAIN |
显示结果集和解释计划不显示统计 |
|
4 |
SETAUTOTRACE TRACEONLY |
显示解释计划和统计，尽管执行该语句但您将看不到结果集 |
|
5 |
SET AUTOTRACE TRACEONLY STATISTICS |
只显示统计 |
方法五、10046和10053事件
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27571661/viewspace-1783116/ ，如需转载，请注明出处，否则将追究法律责任。
