Re: Analyze table timing

From: koert54 <koert54_at_nospam.com>
Date: Tue, 18 Sep 2001 14:40:19 GMT
Message-ID: <n1Jp7.60029$6x5.12951624@afrodite.telenet-ops.be>

Hi,
You could do the following :

- analyze table estimate statistics instead of compute statistics
- use a parallel hint when running analyze command
- use dbms_stats (oracle 8.1.5.1, 8.1.6.0 or higher) to collect statistics -

you can give it a
parallel degree
In your case a parallel degree of 10 would be perfect because in Oracle 8i each PQ slave will read
from one partition...
Oracle recommends using dbms_stats because analyze may become obselete and it has many more
functions (like exporting and importing statistics from/to another DB -for example statistics from your production
DB to your development DB ...) However you'll need at least 8.1.5.1 because there's a bug in lower versions that
makes dbms_stat run slower than analyze ...

Oh btw - killing an analyze in 8i often comes with an ORA600 ... it will not crash the DB but, hey, as most
of us I'm not too very fond of those ORA600's in the alert log...

Cheers,
Koert

"broom" <broom_at_voicenet.com> wrote in message news:c948eb61.0109180623.667b533_at_posting.google.com...
> How can I determine how long an 'analyze compute statistics'
> of a table will take?
>
> I've currently got a 3 CPU sun 450 with about 1TB of
> storage across 2 fibrechannel arrays, using Veritas
> DB for Oracle. I've got a table that currently takes
> about 70GB, packed in at about 95% used. Almost all
> varchar fields, typical records length of 1200 bytes
> spread across 157 fields. Currently has about 60 million
> records.
>
> It's been running 10 hours so far, and I'm trying to determine
> how long to let it go before aborting. If I want to run again,
> what variables should I modify in the init, or alter system, etc,
> which would speed things up.
>
> Right now it is only using one cpu (achh, phhtt). I don't see
> a parallel option. The table is broken out by 10 partitions
> in the same tablespace, which indicates to me I might be able
> to run concurrent analyzes on a partition basis.
Received on Tue Sep 18 2001 - 09:40:19 CDT