How to Get or Display Explain Plan of SQL Baseline - DBMS_XPLAN.display_sql_plan_baseline

advertisements

_____________________________________________________________________________________________________________________

you can use the DBMS_XPLAN. DISPLAY_SQL_PLAN_BASELINE procedure to display the execution plan associated with a specific SQL Baseline. This procedure is part of the DBMS_XPLAN package and is designed to show the execution plan for a SQL statement using its SQL ID or SQL handle, including those stored in SQL Plan Baselines. Here's an example of how to use DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE:

advertisements

 

SET LINESIZE 200
SET PAGESIZE 100
SET VERIFY OFF

BEGIN
  DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
    sql_handle => 'your_sql_handle',
    plan_name => 'your_plan_name'
  );
END;
/

Replace 'your_sql_handle' with the SQL handle of the SQL statement and 'your_plan_name' with the name of the plan in the SQL Plan Baseline that you want to explain. Explanation: The script uses SQL*Plus formatting commands to control the appearance of the output. The DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE procedure is used to display the execution plan associated with a specific SQL Baseline. The sql_handle parameter specifies the SQL handle of the SQL statement. The plan_name parameter specifies the name of the plan in the SQL Plan Baseline. Usage: Open SQL*Plus or any Oracle SQL tool. Replace 'your_sql_handle' with the SQL handle of the SQL statement and 'your_plan_name' with the name of the plan in the SQL Plan Baseline. Run the script.

Plan hash value: 164138068

---------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     1 (100)|          |
|*  1 |  TABLE ACCESS FULL| your_table|     1 |    59 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(your_condition)

The SQL handle is a unique identifier for a SQL statement and can be obtained from various Oracle views, such as DBA_HIST_SQLSTAT or V$SQL_PLAN, depending on your Oracle version and configuration. Please ensure you have the necessary privileges to access the SQL Plan Baseline and use the DBMS_XPLAN package. Test the script in a controlled environment before running it in a production database.

_____________________________________________________________________________________________________________________

Website Stats