Trace at Appln level
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Trace at Appln level

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    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]

  2. #2
    Join Date
    Oct 2000
    Posts
    449
    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

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Oct 2000
    Posts
    449
    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

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Oct 2000
    Posts
    449
    Thanks a lot Jeff... I shall look into the other things you proposed as well...

    Thx.. ST2000

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    or create a logon trigger which execute alter session set sql_trace

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Oct 2000
    Posts
    449
    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

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    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;
    /


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width