RE: What stupid mistake am I making with this onlogin trigger?

I repeated your test using loops to simulate the cost of multiple
connections over the day.  When executed repeatedly the select on
v$mystat appears to take about 2/3 the time of the sys_context call.
 
UT1 > set echo on
UT1 > set timing on
UT1 > declare
  2  v_ctr number := 10000;
  3  v_sid number := 0;
  4  begin
  5  For I in 1..v_ctr loop
  6    v_sid:=sys_context('userenv','sessionid');
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.73
UT1 > declare
  2  v_ctr number := 10000;
  3  v_sid number := 0;
  4  begin
  5  For I in 1..v_ctr loop
  6    select sid into v_sid from v$mystat where rownum = 1;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.56
UT1 > declare
  2  v_ctr number := 10000;
  3  v_sid number := 0;
  4  begin
  5  For I in 1..v_ctr loop
  6    select sid into v_sid from v$mystat where rownum = 1;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.42
UT1 > declare
  2  v_ctr number := 10000;
  3  v_sid number := 0;
  4  begin
  5  For I in 1..v_ctr loop
  6    v_sid:=sys_context('userenv','sessionid');
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.66
UT1 >

On one execution of the database event trigger this time difference is
not going to matter and even for 10,000 calls the difference isn't much
of anything.  I would say that it seems Oracle has improved the
sys_context call since the initial version.
 
 
-- Mark D Powell -- 
Phone (313) 592-5148 
 


________________________________

        From: Roman Podshivalov [mailto:roman.podshivalov@xxxxxxxxx] 
        Sent: Tuesday, February 26, 2008 10:11 AM
        To: Powell, Mark D
        Cc: oracle-l@xxxxxxxxxxxxx
        Subject: Re: What stupid mistake am I making with this onlogin
trigger?
        
        
        Not really a big difference:
         
        10.2.0.3:
         
        SQL> declare
          2  l_sid number;
          3  begin
          4  l_sid:=sys_context('userenv','sessionid');
          5  end;
          6  /

        PL/SQL procedure successfully completed.

        Elapsed: 00:00:00.01
        SQL> select sid from v$mystat where rownum = 1;

               SID
        ----------
              1094

        Elapsed: 00:00:00.01
        


        --romas
         
        On 2/26/08, Powell, Mark D <mark.powell@xxxxxxx> wrote: 


                I have not tested it recently but the sys_context call
used to be
                significantly slower than just issuing
                select sid from v$mystat where rownum = 1
                
                This form of the query will eliminate the need to read
all 370 or so
                rows and then through away the duplicates.
                
                -- Mark D Powell --
                Phone (313) 592-5148
                
                
                -----Original Message-----
                From: oracle-l-bounce@xxxxxxxxxxxxx
                [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Nigel Thomas
                Sent: Tuesday, February 26, 2008 7:14 AM
                To: Jay.Miller@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
                Subject: Re: What stupid mistake am I making with this
onlogin trigger?
                
                Small extra point:
                
                IMHO it's not best practice to use v$ views when you
could use
                SYS_CONTEXT and DBMS_APPLICATION_INFO instead - so you
don't have to
                have too many users with unrestricted read access to
some or all of the
                v$ views. It's always a good idea to minimise the scope
of any grants.
                
                (select unique(sid) from v$mystat)
                
                can be replaced with:
                SYS_CONTEXT('userenv','sessionid')
                
                which has the added benefit of avoiding a hash(unique)
on v$mystat (370
                rows in my case).
                
                To demonstrate:
                
                declare
                l_module varchar2(48);
                l_action varchar2(32);
                l_username varchar2(30);
                l_sid integer;
                begin
                dbms_application_info.read_module_info(l_module,
l_action);  l_username
                := sys_context('userenv','session_user');
                l_sid := sys_context('userenv','sessionid');
                
                dbms_output.put_line('User:'||l_user||',
SID:'||l_sid||',
                Module:'||l_module);
                
                end;
                /
                
                Shame that DBMS_APPLICATION_INFO doesn't have usable
GET_xxx functions,
                but forces the use of a procedure with output
parameters. How 1960s is
                that? Also, in some circumstances it might be
appropriate to cover
                DBMS_APPLICATION_INFO with a package that only allows
gets, not sets.
                
                NB I haven't compared the cost of these with accessing
the views.
                
                
                Regards Nigel
                
                --
                http://www.freelists.org/webpage/oracle-l
                
                
                --
                http://www.freelists.org/webpage/oracle-l

Other related posts: