Re: Weird performance issue ....

> server and over 12 seconds on the customer server. And increasing
> exponentially as new data is added. The 2 servers show the same explain
> plan.

Explain plan is not enough for this.

Here's the steps which help you to understand where is the difference between DEV and customer's servers.

Create a simple script like this:

--
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 8';
< your script here >
alter session set events '10046 trace name context off';
--
and run it both on DEV and on customer servers.
Get your trace files from user_dump_dest and analyze them with my
profiler OraSRP (link in signature).

After that you will have a 'session profile' for both sessions. Compare
them to find where exactly is the difference between DEV server and
customer server.


--
Egor
http://www.oracledba.ru/orasrp/
Free Oracle Session Resource Profiler