Logon Triggers: The Oracle Database Magic Bullet
Imagine you want to collect detailed usage statistics to tune your Oracle database, e.g. if you want to have A-Rows and A-Time values in your execution plans (by default, Oracle only reports E-Rows and E-Time with “E” for “Estimated”. But usually, you will care more about the “A” for “Actual”). All you have to do is connect with a sysdba user and issue the following statement:
C:\> sqlplus "/ as sysdba"
Connected to: Oracle Database 11g Express Edition Release 188.8.131.52.0
SQL> alter system set statistics_level = all;
But of course, you hardly ever have the required privileges to connect as sysdba or to issue
ALTER SYSTEM statements. So how can we get those advanced statistics? It’s easy, by using a logon trigger:
CREATE OR REPLACE TRIGGER logon_actions AFTER LOGON ON DATABASE ENABLE BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL = all'; END; /
This will set your session’s statistics level to
every time you log on to the database. Of course, you will need to have
the privilege to create such a system trigger, but maybe your DBA will
allow you to specify a certain package that provides a debug level for
things like these:
DECLARE v_loglevel VARCHAR2(100); BEGIN v_loglevel := logger_package.loglevel; IF v_loglevel = 'DEBUG' THEN EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL = all'; END IF; END;
To be sure that the statistics_level has been set correctly, run this query:
SELECT SID, NAME, VALUE FROM V$SES_OPTIMIZER_ENV WHERE NAME = 'statistics_level' AND SID = ( SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1 );
To learn about how to get A-Rows and A-Time values in your execution plan, read this article here.
Happy statistics collecting!