Re: Trace files for a running process

From: <markp7832_at_my-deja.com>
Date: Wed, 18 Aug 1999 16:35:21 GMT
Message-ID: <7penc8$5rc$1@nnrp1.deja.com>

In article <934901187.26480.0.nnrp-01.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> svrgmrl
> connect internal
> oradebug setospid {o/s process id of shadow process}
> oradebug unlimit
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Duncan Hodson wrote in message
> <934892072.22556.0.nnrp-09.c2debfb5_at_news.demon.co.uk>...
> >Greetings,
> >
> >I have been given the task of converting a company's data from their
old
> >system to our new system, and the main part of the processing to
achieve
> >this is written in PL/SQL (the process imports using SQL*LOADER,
creates
> >indexes, and then fills missing values and creates missing records in
> >PL/SQL). The problem is that it runs slowly - too slow for our
> >customer's liking. I can turn on oracle trace using TOAD (an
essential
> >application!), run tkprof on the trace file that is generated, and
see
> >which parts of the process are running slowly - i can then either
modify
> >the processing and tweak indexes in an attempt to speed things along.
> >
> >The problem is that i set the maximum dump file size to 10 meg in the
> >database initialisation file, and that limit has been reached. Is
there
> >a way to increase the allowed size of a trace file whilst a session
is
> >running? Is there a way to specify a different log filename for a
> >session which is still running?
> >
> >My alternative is to stop the process and restart it (it carries on
from
> >where it got to) but that takes a couple of hours which i can't
afford
> >at the mo.
> >
> >Cheers all
> >
> >Duncan
> >
> >=== Duncan Hodson
> >=== Analyst Programmer
> >=== Welcom Software
> >

Oracle has provided a means to turn trace on and off for a running session since version 7.2. using dbms_system. Here is a cover package that I place on our system. You may want to change the owner. rem
rem PL/SQL script to mask rdbms internal package while allowing access to
rem useful routine -- mdp
rem
grant execute on dbms_system to system
/
connect system/**********
set echo on

create or replace procedure system.trace_sql   (

  v_session   in  varchar2,
  v_serial    in  varchar2,
  v_switch    in  varchar2  default 'START'

  )
is

v_pass_session    v$session.sid%type     ;
v_pass_serial     v$session.serial#%type ;
--
--  Cover procedure for use of Oracle provided package that can only be

-- executed by sys. m d powell 15 Oct. 1997 for ver. 7.2.3 --
begin
v_pass_session := v_session ;
v_pass_serial := v_serial ;
if upper(v_switch) = 'START' then
  sys.dbms_system.set_sql_trace_in_session (v_pass_session,v_pass_serial,TRUE);
else
  sys.dbms_system.set_sql_trace_in_session (v_pass_session,v_pass_serial,FALSE);
end if;
end;
/
grant execute on trace_sql to developer_role /
drop public synonym trace_sql
/
create public synonym

       trace_sql
   for trace_sql
/

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Aug 18 1999 - 11:35:21 CDT