rem tracesql.sql
rem
rem CALL from sqlplus:
rem  @tracesql <sqlfile>
rem 
rem tracesql produces a trace output with actual execution plan and actual
rem row count (like tkprof) FOR A SINGLE SQL.
rem For multiple SQL's the procedure has to be repeated
rem The SQL is executed - so beware of DML (insert,delete,update and merge)
rem and long running selects
rem 
rem PARAMETERS:
rem   sqlfile : File name (with optional path) of text file with a single SQL 
rem             Total filename (including path) most not exceed 64 chars.
rem
rem REQUIREMENTS:
rem  - Oracle version 9i release 2 or later (9.2)
rem  - Select priviledge on V$SQL and v$SQL_PLAN_STATISTICS_ALL
rem  - Alter session
rem
rem WRITTEN BY Martin Berg, Martin Berg Consult 
rem Log    Date           Comment
rem        21-NOV-2005    Initial creation
rem
rem CONTACT the author by email: martin AT berg.dk
rem 
rem COPYING, USAGE, CHANGING RULES
rem You may use this code as you need - in its original form, as part of
rem something else, or just changed to your needs.
rem It would be nice if you kept the credits for the origin along with the code
rem 
rem WARRANTS
rem This code is delivered as is and no guarantees are given for correct
rem results or unwanted sideeffects

column "Row Source Operation" format a69
column "Rows" format 999999999

variable filename varchar2(64);
variable hash_value number;
variable plan_hash_value number;
variable child_number number;

set termout off
begin
  :filename := '&1';
  dbms_application_info.set_module('getdbtrace',null);
end;
/
alter session set statistics_level = ALL;
begin
  dbms_application_info.set_module('getdbtrace',:filename);
end;
/

start &&1

begin
  dbms_application_info.set_module('getdbtrace',null);
end;
/
alter session set statistics_level = TYPICAL
/

set feedback off
set termout on
begin
  select hash_value, plan_hash_value, child_number
  into :hash_value, :plan_hash_value, :child_number
  from (select hash_value, plan_hash_value, address, child_number
        from v$sql
        where module = 'getdbtrace'
          and action = :filename
          and command_type != 47
        order by last_load_time desc, child_number desc)
  where rownum = 1;
exception
  when NO_DATA_FOUND then
    raise_application_error(-20001,
                'Could not find statement in V$SQL, this is likely caused '||
                'by an identical statement having been parsed by another '||
                'user. To solve, change statement a little (like adding a '||
                'comment) and rerun (sorry for the inconvenience)');
    :hash_value := null;
    :plan_hash_value := null;
    :child_number := null;
end;
/

select 'Multiple SQL''s with identical Hash and Child - '||
       'execution plan may not make sense' "Warning"
from (select '1'
      from v$sql
      where hash_value = :hash_value
        and child_number = :child_number
      having count(*) > 1)
/

select ss.last_output_rows "Rows", 
       substr(lpad(' ',level),2)||
       ss.operation ||
       decode(ss.options, null, '', ' ('|| ss.options ||')') ||
       decode(ss.object_name, null,'', ' OF '|| ss.object_name) ||
       ' (r='||to_char(last_disk_reads)||' w='||to_char(last_disk_writes) ||
       ' e='||to_char(last_elapsed_time)||'uS)' "Row Source Operation"
from v$sql_plan_statistics_all ss
connect by prior ss.id           = ss.parent_id
       and prior ss.hash_value   = ss.hash_value
       and prior ss.address      = ss.address
       and prior ss.child_number = ss.child_number
start with ss.id=1
       and ss.hash_value = :hash_value
       and ss.child_number = :child_number
/

set feedback on