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

  • December 28, 2016 | 12 views
  • Comments