Re: performance question
- From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- To: "Joan Hsieh" <joan.hsieh@xxxxxxxxx>
- Date: Mon, 10 Sep 2007 17:49:48 +0300
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
