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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 26 Mar 2015 09:44:29 -0400
Message-ID: <0ae301d067ca$fc570340$f50509c0$_at_rsiz.com>


   

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

  1. 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.
  2. 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.  

  1. 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.  

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 26 2015 - 14:44:29 CET

Original text of this message