HOW TO CONFIGURE THE EXPLAIN PLAN(PLAN_TABLE)

How to CONFIGURE explain Plan(PLAN_TABLE)?

First of all let us study some basic things in query tuining and some definitions
OPTIMIZER: Means the most efficient way to process a query.
COST BASE OPTIMIZER: Oracle uses an optimizer to choose the best execution plan for queries based on the cost of the query interms of resource use called Cost Based Optimizer(CBO).
CBO is more and most efficient optimizer mode than Rule Based Optimizer(RBO).
HOW ORACLE FIGURE OUT THE BEST EXECUTION PLAN FOR THE QUERY?
1. Oracle collects statistics on tables and indexes, which includes count of number of rows or the data distribution or “data skew”
2. The Physical Storage statistics and data distribution statistics for all database Tables , Indexes, Columns and partitions collected from data dictionary.

...........................................................................................................................

HOW TO CREATE A PLAN_TABLE FOR EXPLAIN PLAN?
SQL> CONN / AS SYSDBA

EXEC THIS .SQL PACKAGE

SQL>@?/rdbms/admin/utlxplan.sql
TABLE CREATED
SQL>GRANT ALL ON SYS.PLAN_TABLE TO PUBLIC
SQL>CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE

...............................................................

NOW LET US LEARN HOW TO SET THE PLAN_TABLE FOR PARALLEL USERS QUERIES.
SESSION 1:SCOTT
SQL>EXPLAIN PLAN SET STATEMENT_ID=’SCOTT’ FOR (*****type your query******)
SESSION2:JOHN
SQL> EXPLAIN PLAN SET STATEMENT_ID=’JOHN’ FOR (*****type your query******)

"STATEMENT ID"--> If multiple people are accessing the same plan_table or you would like to keep a history of execution plans you should use STATEMENT_ID

...............................................................................................................................................

Now let us learn How to view the plan_table in clear manner.

SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

OR

SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));