This case is not for tuning a SQL, but quickly spot the problem hit an Oracle bug, and worked out a solution.

Problem

Customer reported there is a page hit 404 error a few days after we upgraded the database to 12.1.0.2

Trouble shooting

Check the Apache log, we saw below errors,

[Thu Aug 24 06:21:17 2017] [error] [client 10.20.30.82] [ecid: 1503552073:10.20.30.80:6986:0:897,0] mod_plsql: /lmt/clmareportsql.prFormEditAction HTTP-404 ORA-00600: internal error code, arguments: [kkqjpdpvpd: No join pred found.], [], [], [], [], [], [], [], [], [], [], []\nORA-06512: at "SYS.DBMS_SQL", line 2068\nORA-06512: at "ILEARN_RPT.LMT_RPRT_MGT", line 342\nORA-06512: at "LMT.CLMA_REPORT_MGT", line 5766\nORA-06512: at "LMT.CLMAREPORTSQL", line 317\nORA-06512: at "LMT.CLMAREPORTSQL", line 496\nORA-06512: at "LMT.CLMAREPORTSQL", line 1560\nORA-06512: at line 31\n

Check the database alert log, we saw the similar errors, it's related to ORA-00600. Obviousely, we hit an Oracle Bug.

Thu Aug 24 06:21:17 2017
Errors in file /u01/app/oracle/diag/rdbms/dbvglt1/dbvglt1/trace/dbvglt1_ora_71144.trc (incident=402267):
ORA-00600: internal error code, arguments: [kkqjpdpvpd: No join pred found.], [], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Aug 24 06:21:18 2017
Sweep [inc][402267]: completed
Thu Aug 24 06:22:07 2017
Errors in file /u01/app/oracle/diag/rdbms/dbvglt1/dbvglt1/trace/dbvglt1_ora_71231.trc (incident=417491):
ORA-00600: internal error code, arguments: [qkkInit:1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/dbvglt1/dbvglt1/incident/incdir_417491/dbvglt1_ora_71231_i417491.trc

Check the incident log, we got the SQL hit the ORA-00600 error,

*** 2017-08-24 06:22:07.519
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=4f1h8upfq1mzt) -----
select * from (with

...

Copy the SQL to SQLBooster, 

Then run it, we got the exactly same ora-00600 that we saw in the alert log.

Regarding to the arguments of this error, it must be related to push predicate transformation.

We then turn push predicate transformation off in this session by setting "_push_join_predicate" to false, and run this SQL again. It works!

However, push predicate transformation is very important oracle internal optimization method, we cannot turn it off. The best way to workaround this problem is to find out which push predicate transformation in this SQL caused this problem, then use hint to turn it off.

Now, the problem we are facing is that this is a very complex SQL, it has more than 2000 rows, we cannot tell which part of it is the root cause.

But don't worry, SQLBooster can analyze each subquery of a SQL, we cannot find out the root cause using SQLBooster.

This is not to analyze the performance of the SQL, so we dont need it to be analyze in full details. We can change it to quick analyze in preference settings.

Change the SQL to a SQL tree by clicking "SQL Tree" tab,  click analyze. After a cup of coffee, we got the analyze result!

We noted there are 2 subqueries involved in the main query. It's high likely one of them caused the problem. After we simply added hint no_push_pred in them one by one, we confirmed that the second one is the root cause!

Then we applied this hint to the original SQL, it worked! No ora-00600 raised in the database any more, and the webpage back to normal!