5 Replies Latest reply: Jan 29, 2013 4:00 AM by moreajays RSS

    Question regarding trace generation !

    AnkitV
      Hi All

      I wanted to trace a Cognos report session which generates report via querying Oracle 9i database.
      When the report was run at Cognos end, I noticed a new session in "INACTIVE" state with SID 15.
      Soon there was be another session SID 12. Then there were 4 more sessions (9,17,16,13) with P000, P001, P002 and P004 appended to program column in v$session table, probably parallel processes.

      Below is o/p of v$session for these Cognos sessions.
      select sid,program,logon_time from v$session where username is not null and status <> 'KILLED' order by logon_time desc;

      9     oracle@vnxhamdbipmt01 (P003)     29-JAN-13 02.55.57 AM
      13     oracle@vnxhamdbipmt01 (P001)     29-JAN-13 02.55.57 AM
      17     oracle@vnxhamdbipmt01 (P002)     29-JAN-13 02.55.57 AM
      16     oracle@vnxhamdbipmt01 (P000)     29-JAN-13 02.55.57 AM
      12     BIBusTKServerMain@vnxhamapcogt01 (TNS V1-V3)     29-JAN-13 02.40.58 AM
      15     BIBusTKServerMain@vnxhamapcogt01 (TNS V1-V3)     29-JAN-13 02.40.39 AM

      After sometime, 15 disappeared, rest all stayed but the P00* SIDs kept on changing, i believe old ones got over, and new ones got spawned.
      Session 12 stayed whole time.

      My question is, for which session(s) trace should have been enabled, using SYS user, to get all the executing queries into the trace file for analysis.

      Thanks a lot
        • 1. Re: Question regarding trace generation !
          Helios-GunesEROL
          Hi;

          Please review:
          "How to Trace Specific Database Users to Collect Full DML Statements Executed [ID 309798.1]"
          How To Trace User Session Details [ID 489589.1]

          Regard
          Helios
          • 2. Re: Question regarding trace generation !
            AnkitV
            The first one suggests to create an AFTER LOGON trigger for enabling trace for a particular user when ever it logs in !

            But I think it will case unnecessary trace generation also when several connections are being made using a single user. Isn't it ?
            • 3. Re: Question regarding trace generation !
              Osama_Mustafa
              did you consider audit as option ?
              • 4. Re: Question regarding trace generation !
                AnkitV
                No no, I want to see what all queries the reporting session executes and the performance stats. That's why trace is needed.
                • 5. Re: Question regarding trace generation !
                  moreajays
                  Hi Ankit,

                  I would suggest to trace session 12 here. Along with this you should keep serial# also in v$session query as sid,serial# identifies the uniqueness of the db session
                  Apart from this you may trace the sessions based on client_identifier of v$session using DBMS_MONITOR
                  If you client is not supplying client_id to session & its null then you can use different approach to trace the sessions e.g. module,action etc.
                  Refer this
                  http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm

                  Use of DBMS_MONITOR based on client_identifier :
                  SQL> exec dbms_session.set_identifier('<client_id>');
                  SQL> alter session set timed_statistics = true;
                  SQL> alter session set max_dump_file_size = unlimited;
                  SQL> alter session set tracefile_identifier = '<client_id>';
                  SQL> exec dbms_monitor.client_id_trace_enable(client_id=>'<client_id>');
                  Logon trigger to trace all connections connecting to DB , you may make some changes in code to consider required sessions only
                  CREATE OR REPLACE TRIGGER trace AFTER logon ON database begin execute immediate 'Alter session set tracefile_identifier=''apps_traces''';
                  execute immediate 'Alter session set events ''10046 trace name context forever, level 16'''; end;
                  /
                  Thanks,
                  Ajay More
                  http://www.moreajays.com