方法一、檢視計劃表生成執行計劃

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事件

檢視sql執行計劃方法彙總

相關文章