Exlpain plan for SQL Query and SQL ID in Oracle Database

Let us see how to generate explain plan for a SQL Query and SQL ID.

Pre-Req:

Create the plan table if not created already

@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;

Explain Plan for SQL Query:

1. First identify the query for which you want to generate the plan.
2. Create explain plan for the desired query.
3. Display the explain plan generated for your selected query.

1. I have chosen to generate explain plan for the query 'SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c,v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = 'R';'

2. Create explain plan for the above query

3. Display the above generated explain plan.

Explain Plan for SQL ID:

1. Get the SQL ID of a SQL Query you want to generate explain plan.

2. Check the sql query and the run-time memory utilized by the query so far. This is not a mandatory step, but it will give you more info to understand about the query's execution.

3. Display the explain plan which is stored already in the table DBMS_XPLAN.DISPLAY.

1. I have chosen to Get the SQL ID of a SQL Query being ran by a concurrent request

2. Get the text and run time memory of the sql_id.

3. Display the explain plan

Ref : https://oracle-base.com/articles/9i/dbms_xplan

         https://dbaclass.com/article/how-to-generate-explain-plan-in-oracle/

****************************நன்றி****************************