RE: awrsqlrpt
From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Tue, 5 Jan 2010 11:30:40 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E112EFA810E_at_ONEWS06.oneneck.corp>
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:
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.
Date: Tue, 5 Jan 2010 11:30:40 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E112EFA810E_at_ONEWS06.oneneck.corp>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 05 2010 - 12:30:40 CST