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

fitzjarrell_at_cox.net wrote:
> 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

ONe bit that just came to me is modifying the trigger to look FOR specific usernames. As and example:

SQL> create or replace trigger set_trace after logon on database 2 begin
3 if user in ('APPUSER1','APPUSER2','APPUSER3') 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 /

Create such a trigger, try to ensure no one else connects to the database using the listed application-specific accounts, and you will have trace files generated by your application to analyse.

This is probably your best solution. Again, a tip of the hat to Pete Finnigan for providing the original trigger.

David Fitzjarrell Received on Fri Dec 24 2004 - 09:53:42 CST