Re: performance question

The table row counts may be similar, but but number of rows that satisfy the query joins/filters are vastly different. If you tkprof the trace files you should see this quite easily.

In the QA trace it look like only one join is returning any rows: id=13 cnt=8538 . In the TRN trace there are many rows returned: cnt=12229423, cnt=266950, cnt=32141, etc.

You cant expect the execution performance to be the same because the number of rows in the tables and the plans are the same - the data and the row source numbers must also be the same.

To triage this: verify the Optimizer estimates are accurate (mentioned in first email), and if the estimates are off, regather stats on the involved objects. Then recheck to see if the stats are representative (check NDV counts). Getting representative stats is the first step to getting a good execution plan.

On 9/10/07, Joan Hsieh <joan.hsieh_at_tufts.edu> wrote:
> Hi Greg,
>
> I checked out the rows count, it is not that big gap.
>
> QA TRN
>
> PS_JOB: 264970 ROWS 267084 rows
>
> PS_PERSONAL_DATA: 31967 ROWS 32141 ROWS
> PS_TFTF_JOB: 264836 ROWS 266950 ROWS
> ps_employment: 42893 rows 43121 rows
>
> That's why puzzled me.
>
> Thanks

-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l