Re: help: How do you set event 10046 in this situation

joe bayer (no-spam) wrote:
> One of our applications, runs much slower than before, I want to set
10046
> event on it. The problem is, I can not change the source code, which
means
> I can not use "alter session set event...", and the application seems
to log
> in and log off as several users during the whole process, the worse
thing
> is, the application is using connection poo(it is a java
application), so it
> is difficult for me to determine the sid and serial# of the
application
> connection, could any one tell me how to set 10046 event in this
case?
>
> Thanks for your help.

Yes, it's possible to set event 10046 using dbms_support and dbms_system, however these require two rathr important bits of information, namedly the SID and SERIAL% of the session in question. And there is one piece of information almost everyone posting so far has missed, quoted below:

> the application seems to log
> in and log off as several users during the whole process, the worse
thing
> is, the application is using connection poo(it is a java
application), so it
> is difficult for me to determine the sid and serial# of the
application
> connection

If several sessions are being used by this application before the entire task is completed, and if the OP cannot determine any of the SID/SERIAL# combinations whcih apply to these sessions, I cannot understand how teling him/her that using dbms_support or dbms_system is of any real use.

If someone knows of a method of setting event 10046 for an unknown session or fleeting sessions without knowing the SID or SERIAL# that is, I believe, what the OP is looking for. One poster suggested the Pete Finnigan website, and I must admit this is probably the best advice given, as this poster probably has understood the situation. Quoted from the website below is am after logon trigger so set event 10046 for the current session:

SQL> create or replace trigger set_trace after logon on database 2 begin
3 if user not in ('SYS','SYSTEM') then 4 execute immediate 'alter session set timed_statistics=true'; 5 execute immediate 'alter session set max_dump_file_size=unlimited';
6 execute immediate 'alter session set sql_trace=true'; 7 end if;
8 exception
9 when others then
10 null;
11 end;
12 /

This is, most likely, the best solution to the OP's problem, as he/she needn't know the SID/SERIAL# of the myriad connections this application is creating. Simply create the trigger before the application runs, try to ensure no one else is connected to the database during the run, then drop the trigger after the application is finished. The resulting trace files should be from the application, and can be run through tkprof and examined.

It isn't that the advice given by most responders has been bad, it has simply been inappropriate given the situation reported by the OP. And my intent is only to bring this to everyones attention. David FItzjarrrell Received on Fri Dec 24 2004 - 09:32:49 CST