Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: db file scattered read

RE: db file scattered read

From: Justin Cave <justin_at_askddbc.com>
Date: Fri, 6 Aug 2004 19:42:52 -0600
Message-Id: <20040807013103.65ABF72C411@turing.freelists.org>


You have to collect statistics yourself prior to 10g, which defaults to automatic collection.

There are two broad schools of thought on statistics collection. One school, to which I believe folks like Tom Kyte subscribes, is that you should have Oracle regularly gather statistics, since that will generally lead the optimizer to choose the best execution plan. This school of though carried the argument in 10g with automatic statistics collection.

The other school of though, to which most of the folks on this list tend to subscribe, is that the only reason you would want to gather statistics is if you want an execution plan to change and the only time you want an execution plan to change is when a performance problem is identified. When you gather statistics automatically, you run the risk that the CBO will identify a new, poorly performing plan for one of your SQL statements, leading to frantic calls to the DBA about substantial performance problems. The risk here is that plans may grow slowly worse over time as data volumes and distributions change.

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lyndon Tiu
Sent: Friday, August 06, 2004 6:31 PM
To: oracle-l_at_freelists.org
Subject: Re: db file scattered read

On Fri, 6 Aug 2004 19:24:26 -0400 oracle-l_at_freelists.org wrote:
> It means that you have databases to tune before you sleep.
> You'll do so by paying particular attention to the most expensive SQL,
> and look for the full table scans. Something is doing full table scan.

I found the problem, I needed to do a "analyze" on the table.

I always thought oracle will do statistics automatically. I guess I was wrong.

I have to analyze manually all the time?

--
Lyndon Tiu
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------




----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Aug 06 2004 - 20:41:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US