Oracle-L: RE: Bug, tuning issue or bad sql?

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Wed, 27 Feb 2002 09:28:32 -0800
Message-ID: <F001.00419F88.20020227092832@fatcity.com>

Wow. Are you using CURSOR_SHARING=FORCE in your init.ora by any chance? We've had sporadic problems with that while CBO is on. Also, what does the explain plan look like?

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Wednesday, February 27, 2002 9:24 AM To: Multiple recipients of list ORACLE-L

Oracle 8.1.6.0 NT 4.0

I'm having a problem with a query which is basically just a bunch of UNION ALL's that I want sorted in a certain way. The query runs flawlessly when I limit the result set with a where clause, but when I remove it, the query crashes when sorting (in the order by at the far bottom). If I remove the ORDER BY, the query runs beautifully. The max rows returned by this is about 12,000. I am confident that the values returned are consistant across each of the UNION's.

Here's the error returned:

         trans_demographic td
         *

ERROR at line 257:
ORA-00600: internal error code, arguments: [5213], [], [], [], [], [], [], []

In other words:

Query runs with no ORDER BY.
Query runs with ORDER BY, when limiting rows returned.
When limiting rows returned, I can use any value in the full range (in this case poe_assoc_id), and everything works as long as the total rows returned are less than around 3,000.

I have tested every range of data, and I can use any set of valid values as long as I do it in pieces.

So my question is: is the sql bad somehow (I don't think so). Is this a tuning issue on the database itself? Is this a bug in 8.1.6.0?

Thanks in advance!

P.S. Here's the big ole query:

[truncated for brevity]
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Feb 27 2002 - 11:28:32 CST