Explain Plan in oracle

Explain Plan in Oracle:-

When a sql query is fired the server process ill check the query and generate a excution plan.
excution plan is nothing but,the fatest way to get the data from database(fastest way to get output).
we ill see what going in internal of a query.(like what type of join,index is going or not,plan,etc).

How ill get a Explain plan of a queryp;-
       Plan_table ill store the execution plan information.

1.set autotrace on
2.explain plan for <query>

1.set autotrac on (is the easy way to get explain plan)

if you cant able to run a autotrace command check plustrace role set ot not,follow the below steps.

$ORACLE_HOME/sqlplus/admin/plustrce.sql
GRANT PLUSTRACE TO user;

SET AUTOTRACE TRACEONLY  -set this session level and rerun a query..


2.EXPLAIN PLAN for
The EXPLAIN PLAN method not show query output shows only plan.
it reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE.

Note :- You dont have plan table excute below sql file.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

explain plan for select * from emp where id=2;
select * from table(dbms_xplan.display);

Plan table information come from here:-
V$sql_plan
dba_hist_sqlstat----------to get snapid
dba_hist_active_sess_history

Without run sql-quries we can get old query plan:-

select * from table(dbms_xplan.display_awr('sql_id'));---------------------to plans in awr history
select * from table(dbms_xplan.display_cursor('sql_id'));---------------to get plans in current session using cursor


Note:-
What ill see in Explain Plan:-
right top most of a query..
that query have filter or not if it filter means check index or not.
check cost.
check join-compare to probing size and driving size.
...check access methods...


Note on QUERY PLAN

FTS or Full Table Scan(* from tab)

• Whole table is read upto high water mark
• Uses multiblock input/output
• Buffer from FTS operation is stored in LRU end of buffer cache

Index Unique Scan(select id from )

• Single block input/output

Table access by full scan(select \* from where)

index Range scan(select * from <10)

Index Fast Full Scan(select id >10)

• Multi block i/o possible
• Returned rows may not be in sorted order

Index Full Scan

• Single block i/o
• Returned rows generally will be in sorted order