RSS Feed

Method R Discussion Group

Hi
  We have a performance issue with a process that runs and generates about 200 sessions.  I found a way to trace the sessions by using a login trigger.
  My question is how to easily run this though the profile and get a single report.  I cannot use trcsess because there is not a clientid just a username that is common between the sessions.
  Any suggestions?

Thank you
  Neil Chakrabarty
  West Virginia Department of Envrionmental Protection.


CREATE OR REPLACE TRIGGER SYS.set_trace
  AFTER LOGON ON DATABASE
  WHEN (USER =  'SYNC_USER')
  DECLARE
      lcommand varchar(200);
  BEGIN
      EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
      EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
      EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END set_trace;
  /

Hi Neil,

Thank you for writing.

As you've probably noticed, we don't get much activity here on our FogBugz discussion group. It's a little busier at http://method-r.com/forums. You might want to re-post there for some more visibility.

Now, to your question. The answer depends on something I can't determine from your post. If the task you traced executes serially, with the flow of control shifting from one session to another, then concatenating the relevant segments of trace data in time-sequence order will give you a trace file that the Profiler will help you with.

In your logon trigger, you might consider using dbms_session.set_identifier to set the client id for the task that you're tracing. That will make it possible for you to use trcsess on the trace files.

If there are intervals during your task execution when multiple sessions are executing instructions for you in parallel with each other, then it's harder. It'll be some manual work to lay out the pieces so that you can pick out the critical path (in exactly the sense of Gantt Chart optimization) through your task.

If this "parallel"-ish case is what you have, then you might consider doing some cross-file analysis with tools like the p5skew command that came with your Profiler distribution (check your Profiler User Guide), or its big brother, the mrskew tool that you can read about at http://method-r.com/component/content/article/117 .

If you re-post this question over at our other forum, I'll be glad to re-post this answer, which might cause some other people to help out, too.

If you need help, don't hesitate to let us know.