Trace at Appln level

[st2000]

Have a quick question. I want to trace a sql procedure, which is a very small part of a big job and which gets invoked via a process kicked off via the application..

So far, I know how to trace a procedure or a SQL statement, but not this way.

Option 1:
So, if I execute a sql script before the sql procedure, is that enough through the shell script. My sql script would contain 3 lines with
Alter session set sql_trace=TRUE, similarly for Timed_stats and max_dump_file..

Option 2:
Add the same parameters in init.ora file
Bounce the database
Run the job from the application.

I can almost assure there will be no one using the system while the job goes on and that takes about 12 hours..

Tell me the best solution.. If I go for the option 2 and kick off the job, I will be tracing the whole thing DEFINITELY right.. I just want to be sure..

Very urgent. Please respond immediately..

Thanks, ST2000

[Edited by st2000 on 08-14-2002 at 10:19 PM]

[st2000]

Reason I wanted to be very sure or get some clear insight is beacuse we cannot bring the db down too often.. If someone can point me in the right direction, that's fine too...

Thanks, ST2000

[marist89]

You didn't say what version you were on, so I'm assuming 8.1.7.

You can start a trace in various ways:
1. alter session set sql_trace=true
For this, you set sql_Trace on for the current session.

2. sys.dbms_system.set_sql_Trace_in_session(sid, serial#, true)
This allows SYSTEM to set sql_Trace on in somebody else's session.

3. sys.dbms_system.set_ev(sid, serial#, 10046,8,'')
This allows SYSTEM to start a level 8 trace on somebody else's session.
my preferred method


timed_statistics and max_dump_file_size are parameters which can be modified via alter system:
ALTER SYSTEM SET timed_statistics = TRUE;
ALTER SYSTEM SET max_dump_file_size=1000000;

[st2000]

Thanks for the note Jeff..

You are right. It is 8.1.7 on AIX 4.3.3 box..

Looks like I am not clear with my Question.

I know how to switch on trace.. with all options you gave me at the session level for tracing a SQL or a full procedure(s).

What I want to know..

The situation is that, on the application we are using (Its a retail application and not Forms or any oracle software), there is a process to run. When the user checks that box, and press on PROCESS button, the job runs for about 8-10 hours. The process button invokes certain procedures at the backend shell script which contains/calls bunch of SQL procedures.. The IS team has decided to trace the entire process. How would I exactly do that?

My options were 1 and 2 listed in original response..

If I go for option1 (database level), i.e., edit the parameters in init.ora and bounce the database to take effect.

When I do this, will I be tracing everything on the db or not ? I mean when the user logs in and kicks off the process from the front end, would I still be tracing the procedures that are called? (this is the goal)

Can I check any of these, while the process is run?

Would I have a very significant impact on the system?

I might not do the session level since the job is too lengthy and I don't want the session to be tied up till the end..

I hope I am clear now...

Thanks, ST2000

[marist89]

Yes, setting sql_Trace in the init.ora will trace everything going on in the database.

Since your job connects to the database multiple times, you have a couple more options:
1. create a login trigger that alters the session.
2. if all your jobs are running through sqlplus, create a login.sql file or modify the glogin.sql file to start tracing.

[st2000]

Thanks a lot Jeff... I shall look into the other things you proposed as well...

Thx.. ST2000

[pando]

or create a logon trigger which execute alter session set sql_trace

[marist89]

Originally posted by pando
or create a logon trigger which execute alter session set sql_trace

Hey, that's a great idea, I wish I thought of it

[st2000]

Pando or Jeff :

Can you please explain more, on how to create logon triggers
which executes alter session set sql_trace..

Do you have an example..

Please explain in steps..

Thanks, ST2000

[pando]

create or replace trigger trace
after logon on database
begin
  if user = 'BATCH'
  then
    execute immediate 'alter session set  sql_trace=true';
  end if;
end;
/