Menu Close

Oracle Scripts

Extracting a Bind variable from v$sql.  If there is more than one child cursor then the script needs to be modified to reflect that.

declare
   b1 varchar2(1000);
   b2 sys.sql_bind;
   pos number := 1;
begin
   select bind_data into b1 from v$sql where sql_id = '&sql_id';
   loop
      select dbms_sqltune.extract_bind(b1,pos) into b2 from dual;
      exit when b2 is null;
      dbms_output.put_line(pos || ':' || anydata.accesstimestamp(b2.VALUE_ANYDATA) || ':' || b2.value_string);
      pos := pos + 1;
   end loop;
end;
/

Mining redo logs:

exec dbms_logmnr.add_logfile('<log file>');
exec dbms_logmnr.add_logfile('<log file>');
exec DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Viewing bind variables that a timestamps in v$sql_bind_capture

SELECT anydata.accesstimestamp(value_anydata) FROM v$sql_bind_capture WHERE sql_id='&your_sql_id'

Generating a SQL_MONITOR like report from ASH data.  For performance reasons, you need to know the being and end snapshots from the AWR.

col oper format a45
col sample_time format a30
select  /*+ index(h WRH$_ACTIVE_SESSION_HISTORY_PK) */
        p.id, lpad(' ',p.DEPTH) || p.OPERATION || ' ' || p.OPTIONS oper, p.OBJECT_NAME,
        sum(case when h.sql_plan_line_id = p.id then 1 else 0 end) cnt
from sys.WRH$_ACTIVE_SESSION_HISTORY h
right outer join sys.WRH$_SQL_PLAN p
on h.dbid = p.dbid
and h.sql_id = p.sql_id
and h.sql_plan_hash_value = p.plan_hash_value
where h.sql_id = '&sql_id'
and h.dbid = ( select dbid from v$database )
and h.snap_id between &begin_snap_id and &end_snap_id
group by p.id, lpad(' ',p.DEPTH) || p.OPERATION || ' ' || p.OPTIONS, p.OBJECT_NAME
order by 1
/

Flush a single cursor from the shared pool.  You need to be SYSDBA to do this and then run the output SQL.

select distinct 'exec dbms_shared_pool.purge(''' || address || ', ' || hash_value || ''',''C'');' "Run this command"
from v$sql where sql_id = '&SQL_ID';

Exporting a plan from one database and into another.  Note that you need to get the plan name from after the first PL/SQL is executed so that you can use it in the third.

set serveroutput on
declare
        plans_loaded pls_integer;
begin
        plans_loaded := sys.dbms_spm.load_plans_from_cursor_cache('&SQL_ID');
        sys.dbms_output.put_line('Plans Loaded for good_id : '||plans_loaded);
end;
/
BEGIN
        DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => '&&staging_table_name');
END;
/

DECLARE
        my_plans number;
BEGIN
        my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
                table_name => '&&staging_table_name',
                PLAN_NAME=>'&sql_plan_name');
end;
/

# Export and then import to new database

DECLARE
        my_plans number;
BEGIN
        my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
                table_name => '&staging_table_name');
END;
/

Create a SQL Tuning set with all plans used in a given time period.  This can be useful for generating reference sets when making significant changes such as upgrades.

exec dbms_sqltune.create_sqlset(sqlset_name => '&&set_name',description=>'&description');
exec DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (sqlset_name=> '&&set_name', time_limit=>&time_in_seconds);

Sessions holding library cache locks on a specific object.  This was rather useful in the past as it showed a bug in Oracle:

select  s.sid, 
        s.serial#, 
        count(*) 
from    v$libcache_locks l
join    v$session s
on      l.holding_user_session = s.saddr
where object_handle = (select * from sys.x$kglob where kglnaobj = '&object_name';)
group by s.sid, s.serial#;

Enabling and disabling tracing for a single SQL statement.  Note that the only time I have actually seen any performance impact from enabling tracing was when I used it with binds on a SQL statement that did very little but had a large number of binds.

alter system set events 'sql_trace [sql:2n4upggzfrv66] wait=true, bind=false,plan_stat=all_executions,level=8';
alter system set events 'sql_trace [sql:2n4upggzfrv66] off';

Extracting the hints from a baseline:

SELECT extractValue(value(h),'.') AS hint
FROM sys.sqlobj$plan od,
TABLE(xmlsequence(
extract(xmltype(od.other_xml),'/*/outline_data/hint'))) h
WHERE od.other_xml is not null
AND (signature,category,obj_type,plan_id) = (select signature,
category,
obj_type,
plan_id
from sys.sqlobj$ so
where so.name = '&sql_plan_name');

Extracting the hints from a SQL Patch:

select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
from xmltable('/outline_data/hint'
passing (select xmltype(comp_data) xml
from sys.sqlobj$data
where signature = ( select signature from dba_sql_patches where name = '&patch_name' )
and obj_type = 3)) x;

Disabling direct path reads.

alter session set events '10949 trace name context forever, level 1';