Re: code to load tracefile into CLOB?

Brilliant. Thanks Jeremy. This is one of my favorite topics, but I was
on vacation last week and totally missed this.  What makes MR Trace so great
is that it is so easy to use developers have no excuse not to see how their
code is being executed. Pete Finnigan has a valid point about security, but
I think this kind of access should always be kept far, far away from PROD
and this kind of thinking should be mandatory on DEV databases.

I used to work in a PL/SQL Developer shop and had considered building my
own plugin (Allround Automations plugin documentation is pretty good), but I
no longer work in that shop.

I have some PL/SQL code that I borrorred liberally from:
http://dioncho.wordpress.com/2009/03/19/another-way-to-use-trace-file/
http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_30.shtml
http://www.oracle-base.com/articles/10g/Scheduler10g.php

The code below contains two functions that return the CLOB Jermemy
originally asked about. One that returns the raw trace contents and the
other a tkprof'd version. I like CLOBs because most IDEs can handle them
very well.  They either allow you to export them to a local file or you can
copy and paste somewhere else.

Note this was tested on an 11.2 database on Windows.

select value from v$parameter where name = 'user_dump_dest';
create or replace directory user_dump_dir as '&user_dump_directory';

define schema_owner=andy
grant select on v_$process to &schema_owner;
grant select on v_$session to &schema_owner;
grant select on v_$parameter to &schema_owner;
grant create external job to &schema_owner;
grant read,write on directory user_dump_dir to &schema_owner

create or replace function &schema_owner..gettraceclob(s_id number default
userenv('sid')) return clob
is
   l_filename  varchar2(2000);
   l_bfile     BFILE;
   l_clob   CLOB;
   l_lang_ctx      NUMBER := DBMS_LOB.default_lang_ctx;
   l_charset_id    NUMBER := 0;
   l_src_offset    NUMBER := 1;
   l_dst_offset    NUMBER := 1;
   l_warning       NUMBER;
begin

   select i.value||'_ora_'||p.spid||'.trc' into l_filename
   from v$process p, v$session s,
        (select value from v$parameter where name = 'instance_name') i
   where p.addr = s.paddr
         and s.sid = s_id;

   /*
   for 11g:

   select substr(value,instr(value,'\',-1)+1,1000) into l_filename from
v$diag_info where name  = 'Default Trace File';

   */

   l_bfile:= BFILENAME('USER_DUMP_DIR', l_filename);
   l_clob:='init';
   DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
   DBMS_LOB.loadclobfromfile (l_clob, l_bfile,DBMS_LOB.getlength (l_bfile),
                              l_src_offset, l_dst_offset,l_charset_id,
l_lang_ctx,l_warning);
   DBMS_LOB.fileclose(l_bfile);

   return l_clob;
  end;
  /

begin
DBMS_SCHEDULER.create_program (
    program_name        => 'tkprof_program',
    program_type        => 'EXECUTABLE',
    program_action      => '?/bin/tkprof',
    number_of_arguments => 2,
    enabled             => FALSE,
    comments            => 'Runs tkprof with two arguments. Argument 1:
trace file to be tkprof''d. Argument 2: output file');
DBMS_SCHEDULER.define_program_argument (
    program_name      => 'tkprof_program',
    argument_name     => 'tracefile',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => 'init');

DBMS_SCHEDULER.define_program_argument (
    program_name      => 'tkprof_program',
    argument_name     => 'outputfile',
    argument_position => 2,
    argument_type     => 'VARCHAR2',
    default_value     => 'init');
DBMS_SCHEDULER.enable (name => 'tkprof_program');
DBMS_SCHEDULER.create_job (
     job_name      => 'run_tkprof',
     program_name  => 'tkprof_program',
     enabled       => TRUE,
     comments      => 'Job defined by existing schedule and inline
program.');
end;
/

create or replace function &schema_owner..gettkprofclob(s_id number default
userenv('sid')) return clob
is
   l_filename  varchar2(2000);
   l_bfile     BFILE;
   l_clob   CLOB;
   l_lang_ctx      NUMBER := DBMS_LOB.default_lang_ctx;
   l_charset_id    NUMBER := 0;
   l_src_offset    NUMBER := 1;
   l_dst_offset    NUMBER := 1;
   l_warning       NUMBER;

   l_file_separator varchar2(1):='\'; -- change to '/' on unix/linux


   function getdirpath return varchar2
   is
     l_dirpath varchar2(2000);
   begin
     select DIRECTORY_PATH into l_dirpath from all_directories where
directory_name = 'USER_DUMP_DIR';
   return l_dirpath;
   end;

   function getfilebase(p_filename varchar2) return varchar2
   is
   begin
     return substr(p_filename,0,instr(p_filename,'.',-1)-1);
   end;

begin

   select i.value||'_ora_'||p.spid||'.trc' into l_filename
   from v$process p, v$session s,
        (select value from v$parameter where name = 'instance_name') i
   where p.addr = s.paddr
         and s.sid = s_id;

   /*
   for 11g:

   select substr(value,instr(value,'\',-1)+1,1000) into l_filename from
v$diag_info where name  = 'Default Trace File';

   */

   execute immediate ' begin DBMS_SCHEDULER.define_program_argument ( '||
       'program_name      => ''tkprof_program'','||
       'argument_name     => ''tracefile'','||
       'argument_position => 1,'||
       'argument_type     => ''VARCHAR2'','||
       'default_value     => '''||getdirpath
||l_file_separator||l_filename||'''); end;';

   execute immediate ' begin DBMS_SCHEDULER.define_program_argument ( '||
       'program_name      => ''tkprof_program'','||
       'argument_name     => ''outputfile'','||
       'argument_position => 2,'||
       'argument_type     => ''VARCHAR2'','||
       'default_value     =>
'''||getdirpath||l_file_separator||getfilebase(l_filename)||'.tkp''); end;';

   execute immediate ' begin dbms_scheduler.run_job(''RUN_TKPROF'');end;';

   --l_bfile:= BFILENAME('USER_DUMP_DIR', l_filename);
   l_bfile:= BFILENAME('USER_DUMP_DIR', getfilebase(l_filename)||'.tkp');
   l_clob:='init';
   DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
   DBMS_LOB.loadclobfromfile (l_clob, l_bfile,DBMS_LOB.getlength (l_bfile),
                              l_src_offset, l_dst_offset,l_charset_id,
l_lang_ctx,l_warning);
   DBMS_LOB.fileclose(l_bfile);

   return l_clob;
  end;
  /

A little demo:

SQL>  alter session set events '10046 trace name context forever, level 8';
Session altered.
SQL> select count(*) from scott.emp;
  COUNT(*)
----------
        14
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> var c clob
SQL> exec :c:=gettkprofclob
PL/SQL procedure successfully completed.
SQL> set long 1000
SQL> print c

C
--------------------------------------------------------------------------------
TKPROF: Release 11.2.0.1.0 - Development on Tue Aug 23 23:16:22 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights
reserved.
Trace file:
c:\app\oracle\diag\rdbms\sandboxdb1\sandboxdb1\trace\sandboxdb1_ora_
6240.trc
Sort options: default
********************************************************************************
...
SQL ID: abj9tmfcs15bm
Plan Hash: 2937609675
select count(*)
from
 scott.emp

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.00       0.00          0          1
0           1
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        4      0.00       0.00          0          1
0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 112
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1 pr=0 pw=0 time=0 us)
     14   INDEX FULL SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0
card=14
)(object id 73197)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  SQL*Net message to client                       2        0.00
0.00
  SQL*Net message from client                     2       12.24
12.25
********************************************************************************
...

On Fri, Aug 19, 2011 at 12:33 PM, Jeremy Schneider <
jeremy.schneider@xxxxxxxxxxxxxx> wrote:

> Here you go.  I coded the whole thing up and published it... if anyone
> can't use current plugins on the market but they need to give developers
> access to 10046 trace data without server access, here's a simple block
> of code that will do the trick.
>
> http://www.ardentperf.com/2011/08/19/developer-access-to-10046-trace-files/
>
> -Jeremy
>

Other related posts: