PI V$SQL_PLAN statement caused an ORA-00600 and a trace file in the monitored Oracle database (4307272)

CAUSE 1

There are two Oracle bugs which could cause the issue:

To get an exact analysis which bug is affecting you, please contact the Oracle support.

CAUSE 2

A SQL query issued by PI may cause a ORA-00600 733 error because the query uses "OR" to query "V$SQL_PLAN" which can cause performance issue.

RESOLUTION 1  * This is the preferred option *

If the problematic Oracle database is still on version 11.2.0.2/3 please upgrade to 11.2.0.4 or 12c. 

If an upgrade it not possible or if the database has already version 11.2.0.4, there are 2 options you can do in Foglight to avoid the ORA-600:

• Disable SQL PI extension because here Foglight collects the SQL plan details.
• Disable only the SQL plan collection

RESOLUTION 2

To disable SQL PI go to Database Overview dashboard and select the problematic agent. Click on "Settings | Administration" and select "SQL Performance Investigator" on the left hand side. Now disable the PI Extension.

RESOLUTION 3

NOTE: If the SQL plan collection will be disabled, you will miss metrics about executions plans.

To disable the plan collection download the attached script and unzip it. Before you run it change the first line and enter the agent name there, not the SQL PI agent name, the actual monitored instance name:

agentName = ""

Now follow the steps below:

1. Go to "Administration | Tooling | Script Console"
2. In the lower part of the dashboard select the "Scripts" tab and click on the green "Add" icon
3. Once the popup window appeared, copy the script content into the text field and click on "Run"
4. If the return value is True the script ran successfully

Once Oracle fixed bug 19846328 you can use the script to enable the plan collection again. To do so, you need to change the third line

From:
aspValue = "0";

To:
aspValue = "5";

Again run the script in the Script Console as described above.