DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, December 29, 2016

Query to get bind variable, eplan using sql_id and eplan using sql text with table format





Please find the queries below.


1)  BIND VARIABLE USING SQL_ID
    =============================


column name format a30
column datatype_string format a30
column value_string format a30
set lines 120 pages 200
accept v_sql_id prompt 'Enter SQL_ID->'
Select name, datatype_string, value_string
    from v$sql_bind_capture
  where sql_id = '&v_sql_id';




2)  EPLAN USING SQL_ID
  =======================


set lines 160
set pages 1000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));




3)  EPLAN USING SQL TEXT
 =======================


set lines 150
set echo on
set pages 500
set serveroutput off
set define off
delete from  plan_table where statement_id='abc';
commit;
spool eplan11g
explain plan for
Give the select statement here for ex: (select * from saleslog where Status = 'Pending';)
select * from table(dbms_xplan.display());
spool off



No comments: