How to obtain the execution plan

EXPLAIN PLAN-This command is used to generate an SQL statement execution plan, but the statement is not actually executed.

1. Dictionary View-In the oracle dictionary view, we can query the execution plans of SQL statements that have been executed in the cache in the memory.

Explain plan command

V $ SQL _PLAN

Automatic Workload Repository (AWR)

SQL Tuning Set (STS)

SQL Plan Baseline (SPM)

Use EXPLAIN PLAN

SQL> explain plan for select count (*) from products; explained. SQL> select * from table (dbms_xplan.display ('','', 'Basic '); PLAN_TABLE_OUTPUT partition Plan hash value: 589338964 bytes | Id | Operation | Name | ------------------------------------------------------------------ | 0 | select statement | 1 | sort aggregate | 2 | bitmap conversion count | 3 | bitmap index fast full scan | PRODUCTS_PROD_STATUS_BIX | ------------------------------------------------------------------ 10 rows have been selected.

If you bind a variable, explain plan's output result

SQL> var v numberSQL> exec: v: = 145PL/SQL process completed successfully. SQL> explain plan for select count (*) from products where prod_id =: v; explained. SQL> select * from table (dbms_xplan.display ('','', 'typical + PEEKED_BINDS '); PLAN_TABLE_OUTPUT partition Plan hash value: 2065297493 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ------------------------------------------------- --------------------------------- | 0 | select statement | 1 | 4 | 0 (0) | 00:00:01 | 1 | sort aggregate | 1 | 4 | * 2 | index unique scan | PRODUCTS_PK | 1 | 4 | 0 (0) | 00:00:01 | identified by operation id: PLAN_TABLE_OUTPUT ---------------------------------------------------------------- --------------------------------------------------------------------------- 2-access ("PROD_ID" = TO_NUMBER (: V) -- select 14 rows for type conversion.

Use V $ SQL _PLAN

Using DBMS_XPLAN.DISPLAY_CURSOR, we can access the execution plan stored in V $ SQL _PLAN. The syntax format is as follows:

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE parameter name type input/output default value? ------------------------------ --------------------- ------ -------- SQL _ID VARCHAR2IN DEFAULT CURSOR_CHILD_NONUMBER (38) IN DEFAULT FORMAT VARCHAR2IN DEFAULT

If SQL _ID and CURSOR_CHILD_NO are omitted, the SQL Execution Plan last executed in the current session is queried by default.

SQL> var v numberSQL> exec: v: = 144PL/SQL process completed successfully. SQL> select count (*) from products where prod_id =: v; COUNT (*) ---------- 1SQL> select * from table (dbms_xplan.display_cursor ('','', 'typical + PEEKED_BINDS '); PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL _ ID9gnda3r7m7jvw, child number 0 ----------------------------------- select count (*) from products where prod_id =: vPlan h Ash value: 2065297493 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Bytes | 0 | select statement ||| 1 (100) | 1 | sort aggregate | 1 | 4 | PLAN_TABLE_OUTPUT Partition ---------------------------------------------------------------------------------------------- ------ | * 2 | index unique scan | PRODUCTS_PK | 1 | 4 | 0 (0) | inclupeeked Binds (identified by position): ------------------------------------ 1-: V (NUMBER ): 144 -- In the explain plan, there is no Predicate Information (identified by operation id) for this section: ------------------------------------------------- PLAN_TABLE_OUTPUT --------------------------- ------------------------------------------------------------------------- 2-access ("PROD_ID" =: V) has selected 24 rows.

Through AWR

Similar to v $ SQL _plan, you can also use awr to obtain the execution plan. When DBMS_XPLAN.DISPLAY_AWR () is used, the syntax format of this function is as follows:

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE parameter name type input/output default value? ------------------------------ --------------------- ------ -------- SQL _ID VARCHAR2IN PLAN_HASH_VALUENUMBER (38) IN DEFAULT DB_IDNUMBER (38) IN DEFAULT FORMAT VARCHAR2IN DEFAULT

Example:

SQL> select * from table (dbms_xplan.display_awr ('hour'); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID ------------------ delete from WRH $ _ IOSTAT_FUNCTION tab where (: beg_snap <= tab. snap_idand tab. sn Ap_id <=: end_snap and dbid =: dbid) andnot exists (select 1 from WRM $ _ BASELINE B where (tab. dbid = B. dbid) and (tab. snap_id> = B. start_snap_id) and (tab. snap_id <= B. end_snap_id) Plan hash value: 1772020.47plan_table_output partition ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 0 | delete statement ||| 4 (100) | 1 | DELETE | WRH $ _ IOSTAT_FUNCTION | 2 | FILTER | | 3 | index range scan | WRH $ _ IOSTAT_FUNCTION_PK | 1 | 17 | 2 (0) | 00:00:01 | 4 | table access by index rowid | WRM $ _ BASELINE | 1 | 33 | 2 (0) | 00:00:01 | 5 | index range scan | WRM $ _ BASELINE_PK | 1 | 1 (0) | 00:00:01 | 22 rows have been selected for partitions.

Use sqlplanbaseline

You can also obtain the execution plan through sqlplan baseline. Sqlplan baseline is a new technology introduced in 11g, mainly used to support SPM. The following describes how to obtain the execution plan through sqlplan baseline.

SQL> alter session set optimizer_capture_ SQL _plan_baselines = true; the session has been changed. SQL> select count (*) from t1; COUNT (*) ---------- 74008SQL>/COUNT (*) ---------- 74008SQL> select SQL _handle, plan_name, accepted from dba_ SQL _plan_baselines where SQL _text like 'select count (*) from t1'; SQL _HANDLE PLAN_NAME ACC distinct rows --- distinct YESSQL> select * from table (dbms_xplan.display_ SQL _plan_bas Eline ('SQL _ e208a16bb98b6a04'); PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL handle: SQL _e208a16bb98b6a04SQL text: select count (*) from t1 then ----------------------------------------------------------------------------------------------------------- Your Plan name: SQL _plan_f1_1dfwsquh4dcde45 Plan id: 3704692293 Enabled: YES Fixed: NOAccepted: YES Origin: AUTO-CAPTURE--------------------------------------------------------------------------------PLAN_TABLE_OUTPUT your Plan hash value: 129980005 ------- ------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 46 (0) | 00:00:01 | 1 | sort aggregate | 1 | 2 | index fast full scan | I1 | 74008 | 46 (0) | 00:00:01 | selected 20 Line.