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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Analyze

Re: Analyze

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 07 Sep 2001 18:54:50 +0100
Message-ID: <3B9909EA.900@yahoo.com>


Spark wrote:
>
> Hello,
>
> I was wondering whether anyone can give me some guidance regarding Analyze.
> I have an 8.1.6 database, running on NT4 SP 6.
>
> The analze script that I have been using is as follows.
>
> BEGIN
> dbms_utility.analyze_schema(
> schema => 'SCHEMENAME',
> method => 'DELETE');
> dbms_stats.gather_schema_stats(
> ownname => 'OWNERNAME',
> method_opt => 'FOR ALL COLUMNS SIZE 30',
> DEGREE => 4,
> cascade => TRUE);
> end;
> /
>
> This has been fine up to now. I find that it helps enquiries run fine, but
> it has been slowing down other aspects of data input through the app. Was
> wondering if anyone could suggest a more comprehensive script, or whether it
> is better to analyze through dba studio using compute/estimate.
>
> Many thanks for your help
>
> Mark F

Analyze predominantly consists of:

1 for table	- table statistics
2 for all columns	- column stats
3 for all columns size n	- histograms on the columns
4 for indexes	- index stats

  1. I wouldn't bother the delete, a new analyze overwrites anyway
  2. maybe start with just plain old 'compute' which will do 1,2 and 4 above.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
Received on Fri Sep 07 2001 - 12:54:50 CDT

Original text of this message

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