Performance Tuning 1: Generate Explain Plan

1. EXPLAIN PLAN Statement: 

• Creating the PLAN_TABLE (if it doesn't exist):

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

• Generating the Explain Plan: 

EXPLAIN PLAN SET STATEMENT_ID = 'my_query_1' FOR

SELECT e.employee_id, e.first_name, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.location_id = 1700;

• Displaying the Explain Plan using DBMS_XPLAN: 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'my_query_1', 'ALL'));

Simple Example:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. Using sql_id from cursor in oracle

To generate an explain plan using a SQL_ID from a cursor in Oracle, you can use the DBMS_XPLAN.DISPLAY_CURSOR function, specifying the SQL_ID and optionally the CHILD_NUMBER.

1. Identify the SQL_ID:

SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%your_sql_text%';

  it can also be found  

• find the PREV_SQL_ID in V$SESSION.
• find SQL_ID from SQL-trace, AWR reports, or ASH reports.

SELECT SQL_ID, SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID = 'your_sql_id';

2. Use DBMS_XPLAN.DISPLAY_CURSOR:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID => 'your_sql_id', CHILD_NUMBER => 1, FORMAT => 'ALL'));