This discussion is archived
5 Replies Latest reply: Mar 17, 2008 5:24 AM by 626729 RSS

SQL_TRACE help to newbie in oracle

626729 Newbie
Currently Being Moderated
Hello!

I have an application and management wants me to performance test it's database against some requirements. I have several use-cases wich performance should be validated with different loads and concurrent users.

How do I track down the testable code? I Could read it from application-side, but if i want exactly the SQL what application executes on Oracle server, how do I capture it?
(there are dynamic SQL and O/R spesific Query language)

I've heard about SQL_TRACE and TKPROF and other tools, but I understand that these are only valid and usable in same session. Should I add code (instrument) to the application under test to set the SQL_TRACE on? or Can I set tracing outside a applications session?? How do I know the witch database user am I logged in? Is that the appserver's DB pool user?

The Oracle's manual is talking about

1) session level tracing
( is this possible in my case ?)

2) instance level tracing
(could be possible, but probably the tracing data confuses with other tunctional tests)

3) Client identifier tracing
WHAT IS THIS???
How do I get the Client identifier?? I have tried v$session, but it gave me nothing.



4) gathering for service,module and action
Is this meaning PL/SQL stored procs and not code in app side??




THANK YOU FOR YOUR HELP!!!!!
  • 1. Re: SQL_TRACE help to newbie in oracle
    CharlesHooper Expert
    Currently Being Moderated
    If you have access to the source code, it should be possible to execute a SQL statement like this to enable a 10046 extended trace at level 12 (waits and binds):
    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
    If you do not have access to the source code, you can remotely enable an extended trace for another session with:
    * Logon trigger that executes the above SQL statement for a specific user/application.
    * System wide 10046 extended trace (change SESSION in the above SQL statement to SYSTEM).
    * Session specific 10046 trace remotely enabled using:
      EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');
    -or-
      EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(SID,SERIAL#,WAITS=>TRUE,BINDS=>FALSE);
    -or-
      EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)
    -or-
      EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);
    The SID and SERIAL# may be retrieved from V$SESSION.

    The SYS.DBMS_SYSTEM.SET_EV method is unsupported, but it works fine on older versions of Oracle where the other methods do not exist.

    Oracle will create the trace files in the server's udump directory.

    Before executing any of the above (especially the system wide method), determine how to disable a 10046 trace, depending on how it was started:
      ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
    -or-
      EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,'');
    -or-
      EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(SID,SERIAL#);
    -or-
      EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,FALSE);
    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 2. Re: SQL_TRACE help to newbie in oracle
    119642 Explorer
    Currently Being Moderated
    One method I like so that I don't have to modify code but get the trace remote as a DBA is to use the DBMS_SYSTEM supplied package provided by Oracle.

    First you have to get the SID and the SERIAL of the session from v$session.

    Then do "execute dbms_system.set_sql_trace_in_session(&1,&2,TRUE);"

    do what ever process in the app you want to trace....

    and then "execute dbms_system.set_sql_trace_in_session(&1,&2,FALSE);"

    This may not help if the application connects and disconnects..............
  • 3. Re: SQL_TRACE help to newbie in oracle
    626729 Newbie
    Currently Being Moderated
    Thank you for your help!

    I'm having a little performance problems and I must find about what would be the exact best database design tackling for these problems.

    We have a three tier architecture...

    Database connections are pooled in the application server and whenever I need connection the application server gives it to me. Our pool is established at the morning, so all the sessions logon_time is at morning.

    So i get the SID, from connection pools connection and trace that SID?
    Is it possible to set the above tracing statements to Connection Pools initialization parameters?

    How do I handle tracing of the XA-transactions to multible databases?? Is it possible to trace them??? I have understood that the XA-driver controls the overall connection and there are multiple Oracle database connections involved and multiple sessions???

    How the XA-performance should be validated? Should I check every single database design how they fit to part- of overall Query???

    Please help me!
  • 4. Re: SQL_TRACE help to newbie in oracle
    626729 Newbie
    Currently Being Moderated
    Does anyone have experience about these kind of problems??
  • 5. Re: SQL_TRACE help to newbie in oracle
    626729 Newbie
    Currently Being Moderated
    hello!

    Does anyone know how to set TRACEFILE_IDENTIFIER in another session.

    SYS.DBMS_SYSTEM - has only 2 procedures for setting init.ora parameters in session level. One is SET_BOOL_PARAM_IN_SESSION and other is SET_INT_PARAM_IN_SESSION.

    Tracefile_identifier is character or string parameter, how do I set that?

    In my own session I can set it like this. ALTER SESSION SET TRACEFILE_IDENTIFIER, but how to set it in another session??