DBMS_XPLAN - Script To get The Explain Plan from cursor

advertisements

_____________________________________________________________________________________________________________________

To retrieve the execution plan (explain plan) for a specific cursor in an Oracle database, you can use the following SQL*Plus script. This script uses the DBMS_XPLAN package to display the execution plan for a given SQL statement: An execution plan (explain plan) provides insights into how Oracle's query optimizer intends to execute a given SQL statement. It shows the sequence of operations and access paths used to retrieve data from the database. The DBMS_XPLAN package allows you to retrieve and display the execution plan for a specific cursor, helping you analyze and optimize your queries. Please ensure you have the necessary privileges to run the cursor's SQL statement and access the DBMS_XPLAN package. Test the script in a controlled environment before running it in a production database.

advertisements

 

SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));  

Replace the SQL statement inside the OPEN v_cursor FOR block with the actual SQL of the cursor for which you want to retrieve the explain plan. 

Sample Output

SQL>  select 1 from dual;

	 1
----------
	 1

SQL>  SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));   2  

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	7x6sb70rmkujt, child number 0
-------------------------------------
 select 1 from dual

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation	 | Name | Rows	| Cost (%CPU)| Time	|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |	|	|     2 (100)|		|
|   1 |  FAST DUAL	 |	|     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      END_OUTLINE_DATA
  */

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DUAL]]></t><s><
	![CDATA[SEL$1]]></s></h></f></q>



38 rows selected.

_____________________________________________________________________________________________________________________

Website Stats