Oracle bug 5166445 causes DPA plan capture to fail

10/22/2018 11:31 AM

10/22/2018 11:31 AM

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)

Database Performance Analyzer

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.