Re: OPTIMIZE: help please - simple sql runs slow

Doug wrote:
> I have a very simple query that runs really fast on one server, and
> really slow on another. How can I figure out why they are so
> different?
>
> DETAILS:
>
> The statement is: UPDATE cust_order SET ship_date = '11-SEP-04' WHERE
> order_id = '001' AND line_no = 1;
>
> On the one machine it runs in a half second (or less.)
> On the other machine it runs in just over one minute (even when nobody
> else is hitting that server).
>
> My second machine is updated by "restoring" it from an export from my
> first machine; the schema and data are the same.
>
> Where can I begin to look to track down this problem? Is there a tool
> out there that can track a sql statement and tell me WHAT IN THE WORLD
> that server is doing for an ENTIRE MINUTE?
>
> Thanks,
>
> Doug

Sure, there is. It's called sql_trace although what you really want is sql_trace including waits which can be easily achieved by issuing

sql> alter session set events '10046 trace name context forever, level 4';

sql> UPDATE cust_order SET ship_date = '11-SEP-04' WHERE order_id = '001' AND line_no = 1;

sql> alter session set events '10046 trace name context off';

The resulting tracefile can be found in your user_dump_destination. For better readability you can format it with tkprof, which will also give you the Query Plan.

My guess is that you'll have a completely different plan for the two servers. The usual suspects are differing sort_area_size between the two servers, stats not uptodate or not appropriately gathered, differing settings for optimizer_index_caching, optimizer_index_cost_adj, and then there are always the people using different versions of oracle.

Perhaps you care to give a little bit more details? (OS, Oracle Version, number of CPUs, RAM springs to mind).

HTH Holger Received on Thu Aug 12 2004 - 11:28:38 CDT