RE: ORA-00600: [xplSetRws:1] and a query with large number of nested ORs

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <mwf@xxxxxxxx>, "'Listserv Oracle'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 26 Mar 2015 10:05:47 -0400

“exploding such as”

 

Now even sure how “exploring features such as” got from my brain to my keyboard 
as “exploding.” Sigh.

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Mark W. Farnham
Sent: Thursday, March 26, 2015 9:44 AM
To: 'Listserv Oracle'
Subject: FW: ORA-00600: [xplSetRws:1] and a query with large number of nested 
ORs

 

 

 

From: Mark W. Farnham [mailto:mwf@xxxxxxxx] 
Sent: Thursday, March 26, 2015 9:44 AM
To: 'dbmangler@xxxxxxxxx'; 'Listserv Oracle'
Subject: RE: ORA-00600: [xplSetRws:1] and a query with large number of nested 
ORs

 

a)    I’m not sure what the bug you’re sure about is or whether analysis is 
reasonable. But sometimes you need a completion path before a known bug can be 
resolved. As far as that goes a pathway that does not trigger the bug must be 
found. As long as you don’t actually run out of memory or exceed maximum string 
length to be parsed the parser runs deep. Somewhere on this list service Jared 
Still showed success running depth of query with one or two character table and 
column names until either his test machine or maximum string length was 
exhausted. So I don’t believe you are actually going to “mangle” things with a 
thousand subqueries. That does not mean you’ll get an especially good plan or 
that the queries would run fast. Further, we cannot begin to analyze whether 
some alternate form of the query such as a string of UNIONs of queries with all 
the AND predicates each and one of the OR predicates each (rendered as an AND) 
would fare better or whether there is even an iso-functional alternative form 
of the query.

b)    Stephan’s answer seems correct regarding memory allocation. We’d have to 
see your entire PL/SQL program and understand its functional requirements to 
really design something for you. Very likely there is a solution far more 
elegant and efficient, but we don’t actually know what your problem is. The 
shape of your problem tends to crop up when folks start by believing they can 
work out everything faster in program memory than by performing set access from 
Oracle, usually without even exploding such as result sets.

 

Good luck. You had our sympathy at “java-based dynamic SQL.”  (if too opaque 
that is a reference to “You had me at ‘Hello.’”)

 

If I were advising your company, I’ll tell them to hire someone to do an 
example piece of your project using Oracle effectively. Of course that might be 
at odds with your email name.

 

mwf

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jeff Thomas
Sent: Wednesday, March 25, 2015 4:25 PM
To: Listserv Oracle
Subject: ORA-00600: [xplSetRws:1] and a query with large number of nested ORs

 

Yesterday we had a java-based dynamic SQL that triggered the following: 

 

ORA-00600: internal error code, arguments: [xplSetRws:1], [0x43D1F3930]

 

After that incident - we started experiencing shared memory issues across a 
4-node cluster.

Killing the query stopped the shared memory problems, but we continued to 
experience performance

degradation until we bounced the instances.   

 

The problematic SQL was one recently redesigned to use bind variables and just 
put into production.  For the

SQL that caused the ORA-600, there were something like over 300 of the 
following nested "OR" subquery fragments, 

and over 1000 of these TABLE(:) bind variables.

 

OR (START_DATE BETWEEN to_date('20150324', 'yyyymmdd')  AND to_date('20180131', 
'yyyymmdd')

 AND ((RCODE NOT IN(SELECT COLUMN_VALUE  FROM TABLE(:4 )  )                 )

 OR (RCODE IN  (SELECT COLUMN_VALUE  FROM TABLE(:5 )  )

AND PID IN (SELECT COLUMN_VALUE  FROM TABLE(:6 ) )))

 

Checking further - I found the TABLE(:) bind was instantiated with the 
following type:

 

TYPE V_VARCHAR_ARR_TYP IS VARRAY(100000) OF VARCHAR2(255);

 

The following are rather basic questions but I was wondering if other DBAs had 
seen similar code fragments and

could provided some guidance as this is a new scenario for our DBA team.    

 

a) I know there is a bug associated with the ORA-600, however - if we were to 
start seeing 1000's of these queries 

every hour, is it reasonable to expect Oracle to be able to handle this load 
(after  fixing the bug)?    The queries

could have a varying number of OR subqueries - anywhere from a few to 100's 
maybe up to a 1000.

 

b) How does Oracle handle the VARRAY in terms of allocating memory?    What 
happens if we have hundreds

of these TABLE(:) variables being instantiated per query and a high query 
volume rate?

 

The problem functionality has been disabled for now - wondered if there were 
more elegant ways to handle this

type of situation.

 

Other related posts: