Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > Database Performance Analyzer (DPA) > Latching problems in Oracle

Latching problems in Oracle

Overview

SolarWinds discovered rare occasions where a documented Oracle bug can cause latching problems that affect system performance when using DPA to monitor Oracle 9.2.0.6, 9.2.0.7, and 10.1 databases. The problem can occur when any program queries against v$sql, v$sqlarea, or the underlying x$ tables. DPA queries this information to collect performance data and may 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 DPA 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 DPA 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 DPA Monitor for the affected database immediately. Notify SolarWinds support that this problem is suspected. SolarWinds support will assist you in restarting the DPA Monitor in a mode that omits the jobs affecting the latching problems. SolarWinds recommends that you update Oracle to 9.2.0.8 or 10.2.0.1. Once applied, Monitors can be restarted normally.

DPA alert

You can configured a custom alert to watch for this problem and warn your DBA. The alert will run inside the repository and monitor timings of DPA modules. The alert will not execute any code on the monitored databases.

To configure this alert, create the alert in DPA. and then create the SQLStats_Alert procedure in the repository database.

  1. In DPA, click Alerts > Manage Alerts.
  2. Click Custom under Alert Category.
  3. Select Custom Procedure Alert - Single Alert Status Return as the Alert Type, and click Create Alert.
  4. Enter the following information:
    • Procedure Call

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

    • Description

      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.

  5. Log in to the repository as the DPA user.
  6. 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;
     /
    
Last modified
19:15, 22 Jun 2016

Tags

Classifications

Public