Submit a ticketCall us

Announcing NPM 12.2
With NPM 12.2 you can monitor your Cisco ASA firewalls, to monitor VPN tunnels for basic visibility and troubleshooting tunnels. NPM 12.2 also uses the SolarWinds Orion Installer so you can easily install and upgrade one or more Orion Platform products simultaneously.
See new features and improvements.

Home > Success Center > Database Performance Analyzer (DPA) > Oracle bug 5166445 causes DPA plan capture to fail

Oracle bug 5166445 causes DPA plan capture to fail

Table of contents
No headers
Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 45 Votes: 0 Revisions: 11

 

This article applies to DPA 6.5 and later.

Oracle Bug #5166445 - STATSPACK SOMETIMES FAILS BY ORA-7445[MSQSEL][SIGSEGV].

Oracle states this issue is fixed in 10.2.0.5 but any 10g versions prior may be affected. The problem manifests itself as an ORA-7445 error when when querying the V$SQL_PLAN table. The workaround is to set a hidden parameter named "_cursor_plan_unparse_enabled" to false.

DPA will turn off the Plan Poll Feature if it receives these errors when querying V$SQL_PLAN table in the monitored database. When it turns off the feature, there will be an entry in the DPA error log that references the Oracle Bug #5166445. Example of error log entry is listed below:

ORA-03113 querying plan text. This is typically due to Oracle bug 5166445 (see Note:420481.1 on Metalink)

To enable DPA plan collection, we are advising our customers to install a logon trigger in the monitored database that will set the hidden parameter for the Ignite account only. Setting this parameter will allow Ignite to collect the execution plans for all SQL statements. The trigger code and installation instructions are listed below:

1. On the monitored database, install this trigger:

CREATE OR REPLACE TRIGGER &IgniteUser..IGNITE_LOGON_ALT_SESS
 AFTER LOGON ON &IgniteUser..SCHEMA
 BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET "_cursor_plan_unparse_enabled" = false';
 END;
 /


2. Update the CONPRM table in the Ignite repository to turn on plan collections. In the repository database as the DPA user, issue the following commands:

SELECT id, name FROM cond ORDER BY name;   -- use the name column value in the UPDATE statement below

UPDATE conprm
 SET v = 'Y'
 WHERE p LIKE '&dbname%PLAN_POLL_ENABLED%';
 COMMIT;

3. Restart the DPA monitor to enable plan poll collection.

Last modified
19:16, 22 Jun 2016

Tags

Classifications

Public