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.

Ignite 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 Ignite's 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 Ignite 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 Ignite 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 Ignite monitor to enable plan poll collection.