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