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

From: Nigel Thomas <>
Date: Tue, 26 Feb 2008 08:00:39 -0800 (PST)
Message-ID: <>

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

Received on Tue Feb 26 2008 - 10:00:39 CST

Original text of this message