Submit a ticketCall us

Cloud Workloads: Meet Your New Hybrid IT Reality
Have you found yourself in that evolving, hybrid IT grey area and wondering if cloud workloads are now part of your purview? And if so, will monitoring cloud workloads require a new set of dedicated cloud monitoring tools? Your answers: yes, they should be, and no, they don’t.

Find out how SolarWinds® Server & Application Monitor (SAM) can help you monitor your cloud workloads side by side with your on-premises workloads. Register Now.

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: 63 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

Tags

Classifications

Public