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

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 26 Feb 2008 10:36:08 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A90201BE6F@usahm208.amer.corp.eds.com>

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_at_gmail.com] 
	Sent: Tuesday, February 26, 2008 10:11 AM
	To: Powell, Mark D
	Cc: oracle-l_at_freelists.org
	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_at_eds.com> 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_at_freelists.org
		[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Nigel Thomas
		Sent: Tuesday, February 26, 2008 7:14 AM
		To: Jay.Miller_at_tdameritrade.com; oracle-l_at_freelists.org
		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
		
		
		

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 26 2008 - 09:36:08 CST