RE: Performance problem .... HELP :-(

Did they rebuild their indexes after this "reorg"? It could be that they simply exported/imported the table without rebuilding the appropriate indexes?

Just a thought..

Mark

-----Original Message-----
Ian
Sent: 19 December 2001 12:55
To: Multiple recipients of list ORACLE-L

Hi Stephane,

Thanks for writing back, I would normally look at some hints or something like that but as far as I can tell it's going through the tables in the correct way. My problem is when we run it on a Production copy on my server we don't get that big number against that table. The tkprof explain shows the access path as the same in both cases. Could it be doing it differently when it actually executes?

The only difference is their dba has done some sort of reorg since we got our copy of the database.

Thanks again
Ian

-----Original Message-----
Sent: Wednesday, 19 December 2001 21:55
To: Multiple recipients of list ORACLE-L

> "Biddell, Ian" wrote:
>
> Hi all,
> Hoping someone can shed some light on a problem I have.
> We a particular cursor in a batch program running in production at a
> client site which has suddenly decided to work really badly.
>
> The program hasn't been changed but I think the customer has done some

> sort of reorg on the database. I traced the program on their server
> and also on a copy of the database on our server (our copy taken
> before the reorg) As can be seen from the tkprof output from a trace
> on the program for about an hour theirs does a lot of buffer IO for
> few rows returned compared to ours.
>
> The execution path in the explain is the same but the row counts down
> the side are different.
>
> Does anyone have any idea why this would be happening or what further
> investigation I can do. All access is via PK so it should be flying
> like the second example.
>
> Thanks, Ian
>

Ian,

   It's wrong to believe that because you are using PKs everything should fly. What blinks before my eyes is this :

 179385326 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RATE_SCHEDULE_LINK_PK' (UNIQUE)    Oracle chooses to access a PK, but as an alternative to scanning what looks like a table implementing a (n-n) relationship of death.

   In other words, it is taking the query by the wrong end. Check what you want (the select list), what you feed in (criteria), and try to coax Oracle in doing it logically, starting from the table for which the best, in terms of selectivity, criterion has been provided. In this kind of case, the /*+ ORDERED */ hint often proves helpful.

--
HTH,

Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Biddell, Ian
  INET: Ian.Biddell_at_compaq.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Leith
  INET: mark_at_cool-tools.co.uk

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