Over a million developers have joined DZone.

Logon Triggers: The Oracle Database Magic Bullet

· Java Zone

Learn more about how the Java language, tools and frameworks have been the foundation of countless enterprise systems, brought to you in partnership with Salesforce.

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 11.2.0.2.0

SQL> alter system set statistics_level = all;

System altered.

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


Discover how the Force.com Web Services Connector (WSC) is a code-generation tool and runtime library for use with Force.com Web services, brought to you in partnership with Salesforce.

Topics:

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}