Saturday, September 5, 2015

steps to generate explain pla for sql in oracle DB?

EXPLAIN PLAN command

select plan_table_output   from table(dbms_xplan.display('plan_table',null,'basic'));

V$SQL_PLAN --last slq staement on cursor

select plan_table_output  from table(dbms_xplan.display_cursor(null,null,'basic'));

Automatic Workload Repository (AWR) 

1.note down the specific time
2.generate the awr for the specific  time
2.genarete the awr and pick the sql_id and genrate the explain plan
or
use dba_hist_sqlstat and get the sql_id and genrate the explain plan

SQL Tuning Set (STS)

SQL Plan Baseline (SPM)

--get the sql_handle from the query and generate the plan

 select SQL_HANDLE, PLAN_NAME, ACCEPTED   from dba_sql_plan_baselines
  where sql_text like 'select * from empty%';


select t.* from
table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed0999',format => 'basic')) ;

No comments:

Post a Comment