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.
