RE: What stupid mistake am I making with this onlogin trigger? SYS_CONTEXT vs V$ views

> 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
> 

1,000,000 repetitions (within a pl/sql loop) of 2 sys_context calls and one
dbms_application_info.read_module as posted earlier takes 11.91 seconds
(10.2.0.1 on Solaris - I don't have access to tell you the server details, or
to trace it properly). Tried again on Win XP with OracleXE 10.2.0.1 - only 7.5
microsecs per iteration - and the trace file shows no recursive SQL. 

There's no function return cache to flatter my results in this version...

1 million repetions of (just)
> select sid into l_sid from v$mystat where rownum = 1
in a tight pl/sql loop took 39 secs.

Possibly the difference is one or both of
- that you were calling SYS_CONTEXT from SQL, and incurring a context switch?
- that earlier versions of SYS_CONTEXT actually read V$ views under the
counter?

All this is of no impact to the OP for a logon trigger, of course - but it's
important not to be afraid of using these in intensive pl/sql apps (eg when
using Apex).

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

Other related posts: