Ok you guys...
Here's the latest on the 'real' subject of this thread.
You know what an explain plan looks like, right? On our
production DB, it looked like
SELECT STATEMENT CHOOSE
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
Ok...so far so what, right? Well, on our stage server, it looked
like this...
32 SELECT STATEMENT CHOOSE
31 SORT GROUP BY
30 CONCATENATION
3 INLIST ITERATOR
2 TABLE ACCESS BY INDEX ROWID
1 INDEX RANGE SCAN
5 TABLE ACCESS BY INDEX ROWID
4 INDEX RANGE SCAN
7 TABLE ACCESS BY INDEX ROWID
6 INDEX RANGE SCAN
9 TABLE ACCESS BY INDEX ROWID
8 INDEX RANGE SCAN
...
29 TABLE ACCESS BY INDEX ROWID
28 INDEX RANGE SCAN
In each case of Table Access...Index Range Scan, the WHERE clauses
were the same. I had never seen anything like this. Have you?
And these plans are for the same SQL.
However...this was an artifact, not the problem. We found the
problem. It was an Oracle Patch. Yep. If you have Oracle
9.2.0.3, take my advice and DO NOT INSTALL MERGE FIX 2871468.
I accidentally missed installing this patch on the STG server, and
the report in question worked there. In PRD, there was no joy.
When I applied the patch to STG, it failed there, and worked again
when the patch was removed. (There's a vote for 'opatch'!).
So...my problem is resolved, the problem was an Oracle patch.
Thanks to all for your help and suggestions.
Cheers,
Mike
-----Original Message-----
Sent: Tuesday, July 22, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L
Color may be irrelevant, depending on amount.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vergara, Michael (TEM)
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
