cbo_helper.sql

-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

begin
    for i in (select value from v$parameter where name = 'user_dump_dest') loop
        execute immediate 'create or replace directory e2sn_udump as '''||i.value||'''';
    end loop;
end;
/


create or replace package e2sn_monitor as
    function get_trace_file (file_name in varchar2) return dbms_debug_vc2coll pipelined;
    function get_session_trace ( p_sid in number default sys_context('userenv','sid') ) return dbms_debug_vc2coll pipelined;
    procedure cbo_trace_on;
    procedure cbo_trace_off;
    procedure sql_trace_on (p_waits in boolean default true, p_binds in boolean default true);
    procedure sql_trace_off;

    procedure set_tracefile_identifier(p_text in varchar2);
    function trace_dump (p_exec_statement in varchar2) return dbms_debug_vc2coll pipelined;
    function test (p_select_statement in varchar2 default 'select count(*) from dba_segments') return dbms_debug_vc2coll pipelined;
end e2sn_monitor;
/


create or replace package body e2sn_monitor as

    procedure sql_trace_on (p_waits in boolean default true, p_binds in boolean default true)
    as
    begin
        execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
        --dbms_monitor.session_trace_enable(waits=>p_waits, binds=>p_binds);
    end; -- sql_trace_on

    procedure sql_trace_off
    as
    begin
        execute immediate 'alter session set events ''10046 trace name context off''';
        --dbms_monitor.session_trace_disable;
    end; -- sql_trace_off

    procedure cbo_trace_on
    as
    begin
        --dbms_output.put_line('setting 10053');
        execute immediate 'alter session set events ''10053 trace name context forever, level 1''';
        --execute immediate 'alter session set "_optimizer_trace"=all';
        --dbms_output.put_line('event 10053 set');
    end cbo_trace_on;

    procedure cbo_trace_off
    as
    begin
        execute immediate 'alter session set events ''10053 trace name context off''';
        --execute immediate 'alter session set "_optimizer_trace"=none';
    end cbo_trace_off;

    procedure set_tracefile_identifier(p_text in varchar2)
    as
    begin
        dbms_output.put_line('trci='||p_text);
        execute immediate 'alter session set tracefile_identifier='||p_text;
    end;

    function trace_dump (p_exec_statement in varchar2) return dbms_debug_vc2coll pipelined
    as
        j number;
        l_prefix varchar2(100);
    begin
        l_prefix := upper('CBOHELP_'||to_char(sysdate, 'YYYYMMDD_HH24_MI_SS'));

        set_tracefile_identifier(l_prefix);

        --cbo_trace_on;
        --sql_trace_on;

        execute immediate p_exec_statement ||' /* E2SN CBO helper: '||l_prefix||'*/ ';
        dbms_output.put_line(j);

        --sql_trace_off;
        --cbo_trace_off;

        for i in (select column_value from table(e2sn_monitor.get_session_trace)) loop
            pipe row (i.column_value);
        end loop;
    end trace_dump;

    function test (p_select_statement in varchar2 default 'select count(*) from dba_segments') return dbms_debug_vc2coll pipelined
    as
        j number;
        l_prefix varchar2(100);
    begin
        l_prefix := upper('CBOHELP_'||to_char(sysdate, 'YYYYMMDD_HH24_MI_SS'));

        set_tracefile_identifier(l_prefix);

        --cbo_trace_on;
        --sql_trace_on;

        execute immediate p_select_statement ||' /* E2SN CBO helper: '||l_prefix||'*/ ' INTO j;
        dbms_output.put_line(j);

        --sql_trace_off;
        --cbo_trace_off;

        for i in (select column_value from table(e2sn_monitor.get_session_trace)) loop
            pipe row (i.column_value);
        end loop;
    end test;
   
    function get_trace_file (file_name in varchar2) return dbms_debug_vc2coll pipelined
    as
	    invalid_file_op exception;
	    pragma exception_init(invalid_file_op, -29283);

        f     utl_file.file_type;
        line  varchar2(32767);
    begin

      dbms_output.put_line('opening file='||file_name);
      f := utl_file.fopen('E2SN_UDUMP', file_name, 'R', 32767);

      loop
        begin
            utl_file.get_line(f, line);
        exception
            when no_data_found then utl_file.fclose(f) ; exit;
            when others then utl_file.fclose(f) ; raise;
        end;

        if length(line) > 1000 then
            for i in 0..trunc(length(line)/1000) loop
                pipe row(substr(line,i*1000+1,1000));
            end loop;
        else
            pipe row(line);
        end if;

      end loop;

      return;

    exception
        when invalid_file_op then raise_application_error(-20000, 'ERROR: Unable to open tracefile. Maybe it does not exist');
    end get_trace_file;


    function get_session_trace ( p_sid in number default sys_context('userenv','sid') ) return dbms_debug_vc2coll pipelined
	as
		tracefile_name       varchar2(4000);
		tracefile_name_lower varchar2(4000);
    begin

        begin
            select par.value ||'/'||(select instance_name from v$instance) ||'_ora_'||s.suffix|| '.trc' into tracefile_name
            from 
                v$parameter par
              , (select spid||case when traceid is not null then '_'||traceid else null end suffix
                 from v$process where addr = (select paddr from v$session
                                              where sid = p_sid
                                            ) 
                ) s
            where name = 'user_dump_dest';

            select par.value ||'/'||(select lower(instance_name) from v$instance) ||'_ora_'||s.suffix|| '.trc' into tracefile_name_lower
            from 
                v$parameter par
              , (select spid||case when traceid is not null then '_'||traceid else null end suffix
                 from v$process where addr = (select paddr from v$session
                                              where sid = p_sid
                                            ) 
                ) s
            where name = 'user_dump_dest';

        exception
            when no_data_found then raise_application_error(-20000, 'ERROR: No matching SID/SERIAL# combination found');
        end;

        begin
            for i in (select column_value from table(get_trace_file( tracefile_name ))) loop
                pipe row(i.column_value);
            end loop;

    	    return;

        exception
            when others then 
            begin
                for i in (select column_value from table(get_trace_file( tracefile_name_lower ))) loop
                    pipe row(i.column_value);
                end loop;

                return;
            exception
                when others then raise_application_error(-20000, 'Unknown error: '||sqlerrm||chr(10)||dbms_utility.format_error_backtrace);
            end;
        end;

    	return;
    	
    end get_session_trace;

end e2sn_monitor;
/
show err;


-- grant execute on e2sn_monitor to public;
-- create public synonym e2sn_monitor for e2sn_monitor;