RE: What stupid mistake am I making with this onlogin trigger? SYS_CONTEXT vs V$ views
Date: Tue, 26 Feb 2008 08:00:39 -0800 (PST)
> 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 NigelReceived on Tue Feb 26 2008 - 10:00:39 CST