RE: awrsqlrpt
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "niall.litchfield@xxxxxxxxx" <niall.litchfield@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 5 Jan 2010 11:30:40 -0700
I'm not sure if I understand the situation exactly, but it sounds like you're
just wondering why the CBO is giving an estimated cost/time of 1 hour and
actually running for 17 hours in one case, and giving an estimate of 12 hours
but running in only 5 in the other. Is that correct? If so, then I think the
answer is that the CBO cost/time is only an estimate, which is based on the
statistics available to the optimizer and its assumptions about how many rows
will be returned, how many blocks will have to be visited, etc. so it's no
secret that the CBO can come up with some extremely inaccurate estimates
sometimes. The best way to troubleshoot such problems is usually with what's
known as "Tuning by Cardinality Feedback", which is described here by Wolfgang
Breitling:
http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf
This method can be expedited in 10g+ by using dbms_plan.display_cursor with the
'ALLSTATS LAST' option as described by Jonathan Lewis here:
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/
Regards,
Brandon
________________________________
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
Other related posts:
