Re: What stupid mistake am I making with this onlogin trigger?
- From: "Roman Podshivalov" <roman.podshivalov@xxxxxxxxx>
- To: mark.powell@xxxxxxx
- Date: Tue, 26 Feb 2008 10:10:44 -0500
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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
