How to get the SQL execution plan

***********************************************************

----1: Get the execution plan for "just" display_cursor

***********************************************************

Explain the plan command in Oracle, you can directly parse subsequent SQL statements and save execution plans in a plan_table intermediate table. Then the method of Dbms_xplan package is obtained.

Select COUNT (*) from T1;

--Query The V$sql view to find the sql_id of the statement (provided that the SQL statement you are querying is still in shared pool):

Select sql_id from V$sql where sql_text= ' select COUNT (*) from T1 ';

sql_id

-------------

5bc0v4my7dvr5

--Call the Dbms_xplan package to see the implementation plan when the statement executes:

SELECT * FROM table (dbms_xplan.display_cursor (' 5BC0V4MY7DVR5 '));

Plan_table_output

--------------------------------------------------------------------------------

sql_id 5BC0V4MY7DVR5, child number 0

-------------------------------------

Select COUNT (*) from T1

Plan Hash value:3724264953

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU) | Time |

-------------------------------------------------------------------

| 0 |      SELECT STATEMENT |       |    |          74 (100) | |

|  1 |      SORT AGGREGATE |     |            1 |          | |

Plan_table_output

--------------------------------------------------------------------------------

|   2 | TABLE ACCESS full|   T1 |    100k| 74 (2) | 00:00:01 |

-------------------------------------------------------------------

Note

-----

-SQL Plan baseline sql_plan_f4251dfwsquh4616acf47 used for this statement

18 rows have been selected.

/*************

Call Display_cursor directly, and without specifying SQL_ID, you can query the SQL command execution plan just executed by the current session from the library cache.

Note: Display_cursor also supports the format parameter, which allows you to extract detailed execution plan information.

Can only be used on Sqlplus or SQLPLUSW. If you are Toad, pl/sql develop, and other tripartite tools, you may not be able to invoke normal use.

***********/

***********************************************************

2:explain Plan for

***********************************************************

---Case 1, show a simple Plan

Explain plan for SELECT COUNT (*) from T1;

View results:

SELECT * FROM table (Dbms_xplan.display ());

Plan_table_output

--------------------------------------------------------------------------------

Plan Hash value:3724264953

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU) | Time |

-------------------------------------------------------------------

| 0 |      SELECT STATEMENT |     |    1 | 74 (2) | 00:00:01 |

|  1 |      SORT AGGREGATE |     |            1 |          | |

|   2 | TABLE ACCESS full|   T1 |    100k| 74 (2) | 00:00:01 |

-------------------------------------------------------------------

Note

Plan_table_output

--------------------------------------------------------------------------------

-----

-SQL Plan Baseline "sql_plan_f4251dfwsquh4616acf47" used for this statement

13 rows have been selected.

--Case 2, show detailed execution plan information

Explain plan for SELECT COUNT (*) from T1;

View results:

SELECT * FROM table (Dbms_xplan.display (Null,null, ' advanced '));

Plan_table_output

--------------------------------------------------------------------------------

Plan Hash value:3724264953

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU) | Time |

-------------------------------------------------------------------

| 0 |      SELECT STATEMENT |     |    1 | 74 (2) | 00:00:01 |

|  1 |      SORT AGGREGATE |     |            1 |          | |

|   2 | TABLE ACCESS full|   T1 |    100k| 74 (2) | 00:00:01 |

-------------------------------------------------------------------