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

 
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: