Overview

Confio has found rare occasions where a documented Oracle bug can cause latching problems that impact system performance when using Ignite for Oracle to monitor Oracle 9.2.0.6, 9.2.0.7 and 10.1 databases. The problem originates from an Oracle bug and can occur when any program queries against v$sql, v$sqlarea or the underlying x$ tables. Ignite for Oracle queries this information to collect performance data and could trigger the bug. This problem appears to be fixed in Oracle 9.2.0.8 and 10.2.0.1. For more information on this issue, please reference the following Oracle bugs:

• Bug 4339128 – Heavy latch contention from queries against library cache views
• Bug 4368358 – Unknown source of latch contention

Symptoms of the Problem

A symptom of the problem can be seen from a SYSTEMSTATE dump. If many sessions, including the network session being used by Ignite to monitor the database, are waiting for “library cache”, you may be experiencing the problem. Another symptom of the problem is finding
many sessions in the V$SESSION_WAIT system view waiting on “library cache” wait events that all point back to the Ignite for Oracle session.  The symptoms have been identified in only a few situations, and do not affect the majority of sites running 9.2.0.6, 9.2.0.7 or 10.1.

Suggested Action

If the problem is detected, based on the symptoms above or the Alert below, stop the Ignite Monitor for the affected database immediately. Notify support@confio.com that this problem is suspected. Confio support will assist you in re-starting the Ignite Monitor in a mode that omits the jobs affecting the latching problems.  Confio suggests that Oracle patches be applied to raise the release level to 9.2.0.8 or 10.2.0.1.  Once these have been applied, Monitors can be restarted normally.

Ignite for Oracle Alert

A custom alert can be configured in Ignite for Oracle to watch for this problem and proactively warn the DBA team. The alert will run inside the repository and monitor timings of specific Ignite for Oracle modules. The alert will not execute any code on the monitored databases.

To configure this alert, there are two steps: 1) Create the alert in Ignite for Oracle; and 2) Create
the SQLStats_Alert procedure in the repository database.

1. Create the alert - click on the Alerts > New Alert > Oracle Admin > Custom and enter information similar to the following:

Procedure Text
SQLSTATS_Alert (#DBID#, 60, #ALERTVALUE#, #ALERTSTRING#)

Description Text
This alert will check rows from the CONTIME table that have an activity name containing SQLSTATS. If the value of the TOTALTIME column (measured in seconds) for any row exceeds the value of the second parameter, an alert message will be sent as configured
below.

Use the “Manage Contact/Groups” to add contact information and create groups, i.e. distribution lists. In the Monitored Database Selection area, check all databases that Ignite should watch for this problem.

2. Create the SQLStats_Alert procedure

    a. Log into the repository as the Ignite user
    b. Run the following PL/SQL code to create the procedure

CREATE OR REPLACE PROCEDURE SQLSTATS_Alert (
               pDBID                         in number,
               pSeconds                    in number,
               pAlertLevel                  out varchar2,
               pError                          out varchar2)
AS
               sDBName                              varchar2(100);
               sSQL                           varchar2(500);
               sError                          varchar2(2000) := NULL;
BEGIN
-- initialize the alert values to FALSE, i.e. nothing wrong
pAlertLevel := 'FALSE';

-- loop through the latest rows and determine if any
-- SQLSTATS executions have gone over the threshold
FOR r IN (select activity, totaltime from contime c1
                where activity like '%SQLSTATS%'
                and totaltime >= pSeconds
                and dbid = pDBID
                and d = (
                      select max(d) from contime c2
                      where c2.activity = c1.activity
                      and c2.dbid = c1.dbid
                      and c2.seq = c1.seq)
                order by activity, totaltime) LOOP
        sError := sError || 'The activity ' || r.activity ||
                       ' executed in ' || r.totaltime || ' seconds.';
        sError := sError || chr(13) || chr(10);
END LOOP;

IF sError IS NOT NULL THEN
        pAlertLevel := 'TRUE';
        pError := 'The following SQLSTATS activities have exceeded the
                       specified threshold of ' || pseconds || '.' || chr(13) || chr(10);
        pError := pError || chr(13) || chr(10) || sError;
END IF;

END SQLSTATS_Alert;
/

 For further information and assistance contact support@confio.com.