SYS_CONTEXT versus V$ views for getting session information

A thread on Oracle-L today sidetracked into the use of V$ views for getting hold of session information such as username, SID and module. The poster had a logon trigger that was supposed to record these items for each logon from a particular client.

I don't approve of granting access to V$ views willy nilly; best practice is always to grant the minimum privileges necessary to achieve an objective.

Another poster raised the issue of performance. In the past, SYS_CONTEXT was considered slower than direct access to the views.

So here is a test to compare the two:

---

set echo off feedback off
set timing on

set termout off

variable v_loops number;
exec :v_loops := 1000000;

set termout on

prompt Testing sys_context

declare
 l_user varchar2(30);
 l_action varchar2(32);
 l_module varchar2(48);
 l_sid number;
        l_loopcount pls_integer := :v_loops;
begin
    for i in 1..l_loopcount loop
 dbms_application_info.read_module(l_module, l_action);
 l_user := sys_context('userenv', 'session_user');
 l_sid := sys_context('userenv', 'sessionid');
    end loop;
end;
/


prompt Testing mystat

declare
 l_user varchar2(30);
 l_action varchar2(32);
 l_module varchar2(48);
 l_sid number;
        l_loopcount pls_integer := :v_loops;
begin
    -- note this only gets one of the three pieces of information
    for i in 1..l_loopcount loop
 select sid
 into   l_sid
 from   v$mystat
 where rownum = 1;
    end loop;
end;
/

---

And here are the results:

C:\sql>sqlplus testuser/testuser

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 26 22:23:10 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> @sys_context_test
Testing sys_context
Elapsed: 00:00:07.31
Testing mystat
Elapsed: 00:00:38.57
SQL>

I suspect that in the past SYS_CONTEXT issued recursive SQL under the covers (just as the SYSDATE pl/sql functions used to, and the USER function and the 11g assignment from a sequence still do).

Now I assume SYS_CONTEXT gets its information directly.