Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Logon Triggers: The Oracle Database Magic Bullet

DZone's Guide to

Logon Triggers: The Oracle Database Magic Bullet

· Java Zone ·
Free Resource

Bring content to any platform with the open-source BloomReach CMS. Try for free.

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!


BloomReach CMS: the API-first CMS of the future. Open-source & enterprise-grade. - As a Java developer, you will feel at home using Maven builds and your favorite IDE (e.g. Eclipse or IntelliJ) and continuous integration server (e.g. Jenkins). Manage your Java objects using Spring Framework, write your templates in JSP or Freemarker. Try for free.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}