RE: HP-UX 11i/9.2.0.3/Strange Executions

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-----
From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com] Sent: Tuesday, July 22, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L Subject: RE: HP-UX 11i/9.2.0.3/Strange Executions Received on Tue Jul 22 2003 - 15:45:15 CDT