Re: code to load tracefile into CLOB?
- From: Andy Klock <andy@xxxxxxxxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 23 Aug 2011 23:40:49 -0400
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:
