Database Logoff Trigger as a Data Collector


Database Logoff Trigger as a Data Collector

Prior to Oracle8 i Database, database triggers could only be created on tables and views and fired on DML events. In Oracle8 i Database, Oracle extended its database trigger capability so that database triggers can be defined at the database or schema level and can fire on certain database and DDL events. The database events include STARTUP, SHUTDOWN, SERVERERROR, LOGON, and LOGOFF. The DDL events include CREATE, DROP, and ALTER.

The database trigger of interest here is the BEFORE LOGOFF trigger. It can be used to collect summarized session-level wait event data and CPU statistics from the V$SESSION_EVENT and V$SESSTAT views when sessions log off. Among the benefits, you can see all the events that belong to every foreground process that was connected to the database. You can easily discover the worst bottleneck within each session and give an answer to the person who asks why the process ran so slowly. This is a good application for the database logoff trigger, especially if you manage many databases and are only interested in summarized session-level performance data. You may not know what caused the performance problem, but at least you ‚ ll be able to tell what the major symptoms were.

The database logoff trigger as a performance data collector is attractive because it fulfills one of the critical requirements previously established: it ‚ s always on and has a low overhead. This method allows for instance-wide monitoring. You don't need to be selective as to which session to monitor or baby-sit any of them into the wee hours of the morning. The database logoff trigger will automatically fire and collect data when sessions log off, relieving you from time-consuming monitoring. However, bear in mind that the logoff trigger will not fire if the session is killed. This includes sessions that are killed by the operating system, ALTER SYSTEM KILL SESSION and SHUTDOWN IMMEDIATE/ABORT commands, as well as sessions that are sniped for exceeding resource limits and then removed by PMON. The initialization parameter _SYSTEM_TRIG_ENABLED will also prevent the logoff trigger from firing if it ‚ s set to FALSE.

Another nice thing about the database logoff trigger is that there is no overhead until a session logs off because that is the only time the trigger goes to work. Of course, the overhead depends mainly on what you put inside the trigger body. Sessions that are logging off will experience a slight delay because they must wait for the inserts into the repository tables to complete. If you have a busy system, you should expect many concurrent logoffs. This means expect many concurrent inserts into the repository tables. Therefore, you should build the repository tables with multiple INITRANS, FREELIST, and FREELIST GROUPS. By default, INITRANS is set to 2 in Oracle9 i Database even though the DBA_TABLES view shows only one entry. You do not need to set FREELIST and FREELIST GROUPS if the tablespace is created with the Automatic Segment Space Management (ASSM) option. ASSM was introduced in Oracle9i Database.

 -- This script creates a database logoff trigger for the purpose of 
-- collecting historical performance data during logoffs.
-- It is applicable to Oracle8 i Database and above.
-- You must be connected as "/ as sysdba" to create this trigger.

create or replace trigger sys.logoff_trig
before logoff on database
declare
logoff_sid pls_integer;
logoff_time date := sysdate;
begin
select sid
into logoff_sid
from v$mystat
where rownum < 2;

insert into system.session_event_history
(sid, serial#, username, osuser, paddr, process,
logon_time, type, event, total_waits, total_timeouts,
time_waited, average_wait, max_wait, logoff_timestamp)
select a.sid, b.serial#, b.username, b.osuser, b.paddr, b.process,
b.logon_time, b.type, a.event, a.total_waits, a.total_timeouts,
a.time_waited, a.average_wait, a.max_wait, logoff_time
from v$session_event a, v$session b
where a.sid = b.sid
and b.username = login_user
and b.sid = logoff_sid;
-- If you are on earlier releases of Oracle9 i Database, you should check to
-- see if your database is affected by bug #2429929, which causes
-- misalignment of SID numbers between the V$SESSION_EVENT and V$SESSION
-- views. The SID number in the V$SESSION_EVENT view is off by 1.
-- If your database is affected, please replace the above
-- "where a.sid = b.sid" with "where b.sid = a.sid + 1".

insert into system.sesstat_history
(username, osuser, sid, serial#, paddr, process, logon_time,
statistic#, name, value, logoff_timestamp)
select c.username, c.osuser, a.sid, c.serial#, c.paddr, c.process,
c.logon_time, a.statistic#, b.name, a.value, logoff_time
from v$sesstat a, v$statname b, v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and b.name in ('CPU used when call started',
'CPU used by this session',
'recursive cpu usage',
'parse time cpu')
and c.sid = logoff_sid
and c.username = login_user;
end;
/

In order for the preceding script to work, you must precreate two repository tables that are owned by the SYSTEM user. If you choose another user , you must modify the code appropriately. The first table, SESSION_EVENT_HISTORY, stores all the wait events, while the second table, SESSTAT_HISTORY stores the CPU statistics of sessions that are logging off. Again, you may choose another name for the tables and modify the code appropriately. Following are the DDLs to create the repository tables:

 create table system.session_event_history 
tablespace <name>
storage (freelist groups <value>)
initrans <value>
as
select b.sid,
b.serial#,
b.username,
b.osuser,
b.paddr,
b.process,
b.logon_time,
b.type,
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited,
a.average_wait,
a.max_wait,
sysdate as logoff_timestamp
from v$session_event a, v$session b
where 1 = 2;

create table system.sesstat_history
tablespace <name>
storage (freelist groups <value>)
initrans <value>
as
select c.username,
c.osuser,
a.sid,
c.serial#,
c.paddr,
c.process,
c.logon_time,
a.statistic#,
b.name,
a.value,
sysdate as logoff_timestamp
from v$sesstat a, v$statname b, v$session c
where 1 = 2;

Before we discuss more aspects of the database logoff trigger, this is a good place to show you a real-life example of how this trigger can be used. Following are the runtime statistics of a conventional SQL*Loader load job that were collected using the database logoff trigger method:

 EVENT                                   TIME_WAITED 
--------------------------------------- -----------
log file sync 101,232
log file switch completion 21,865
log file switch (checkpoint incomplete) 15,630
SQL*Net message from client 13,019
SQL*Net more data from client 1,021
db file sequential read 68
SQL*Net message to client 39
CPU used when call started 20,481

Clearly, the main bottlenecks were related to commit ( log file sync ) and log switch ( log file switch completion ). The fix was simple and mundane, but the fun part was dealing with a fierce DBA wannabe application developer who came from the bigger-is-better school. This person wasn ‚ t satisfied with the load performance and continually pressured a junior DBA to increase the buffer cache size . Ultimately, the SGA was at 3GB, up from about 256MB originally, and there was no performance improvement. In frustration, this person escalated the issue to the director level and demanded the system administrator to install more memory in the server. A senior DBA assigned to resolve the issue created the database logoff trigger. Using the wait event data collected by the trigger, the senior DBA discovered that the redo log size was only 4MB, and redo logs were switching at the rate of 7 or 8 times per minute. Further investigation also revealed that the SQL*Loader bindsize was only 64K. After resizing the SGA back down to 256MB, replacing the redo logs with larger ones, and increasing the SQL*Loader bindsize to 3MB, the load elapsed time went from 33 minutes to under 4 minutes.

How about that for a few lines of code? You may enhance this application with a data purge routine for the repository tables, and the right partitioning scheme allows you to quickly purge old data. (The Oracle partitioning option is available since Oracle8.0 and it is a separately licensed product.)

On the other hand, we want you to be aware that you may not always be able to nail down the root cause of a performance problem using this method because the data is summarized at the session-level. For example, if the latch free event shows up to be the primary bottleneck, the summarized data will not reveal which latch was contended for, when it happened , the number of tries , and the session that held the latch. Or if the db file scattered read is the primary bottleneck, you will not be able to pinpoint the SQL statement and the object that participated in the full table scans .

Now, some of you may have storage concerns. The logoff trigger disk space requirement depends on the session logoff rate and the amount of history you plan to keep. A seven-day history should be sufficient because if there is a performance problem you should hear about it within a day. The logoff rate depends on the behavior of the application. Some applications spawn multiple sessions aggressively connecting and disconnecting from the database. If you have scheduled background jobs, don't forget to account for the logoff rate of SNP processes. The logoff trigger fires each time an SNP background process completes its scheduled job and goes back to sleep. You may start with a 512MB tablespace and monitor the space usage.

Lastly, we give you yet another application for the database logoff trigger. It is perfect for benchmarking when you are only interested in the end result. For example, say you want to see what kind of impact the Oracle9 i FAST_START_MTTR_TARGET feature has on a process by comparing the summarized session-level wait events of the same process with and without the feature.

In many ways, the database logoff trigger is the opposite of the trace event 10046. Table 4-1 gives a quick comparison. If everything that has been said about the database logoff trigger sounds really good to you but your database version is prior to Oracle8 i Database, then you are unfortunate. As mentioned earlier, Oracle introduced the database logoff trigger in version 8 i .

Table 4-1: Differences Between Database Logoff Trigger and Trace Event 10046

Database Logoff Trigger

Trace Event 10046

Fire once for the lifetime of a session

Continuous tracing

Summarized performance data

Fine-grain performance data

Low overhead

High overhead

Low storage requirement

High storage requirement

Instance-wide monitoring capability

Commonly enabled at session level

Limited root cause analysis capability

Best for root cause analysis




Oracle Wait Interface
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
ISBN: 007222729X
EAN: 2147483647
Year: 2004
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net