RE: analyze table takes forever

[Govind . Arumugam]

Title: RE: analyze table takes forever

You mentioned that count(*) took 10 minutes but did not say how many rows are present in that partition; if number of rows is less than 1 million, I would do the following.

 

alter table <owner>.<table_name> move partition <partition_name>  tablespace <tablespace_name> nologging;

 

and then re-analyze the table partition.

 

We have seen a significant improvement in runtimes when we set the sort_area_size to 50Mb while doing these kinds of operations.

 

Also, I read in metalink sometime back that there is a known bug if you estimate less than 20%. 

 

Hope this helps.

 

Govind

-----Original Message-----
From: Nick Wagner [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 29, 2002 1:25 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: analyze table takes forever

what's the structure of the table?  

And try taking an export of this partition and import it into it's own table in a development box... see if you can analyze it there. 

Nick

-----Original Message-----
From: Andrey Bronfin [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 29, 2002 7:49 AM
To: Multiple recipients of list ORACLE-L
Subject: analyze table takes forever

Dear gurus !

We are trying to analyze a table partition (estimate 2 %) and it takes
forever.
analyze table ... partition (P20021022) estimate statistics sample 2
percent;
For instance, select count(*) from the same partition takes 10 minutes , but
the analyze takes literally infinite amount of time , once i left it to run
for half a day and killed it since it did not finish.
Even when i do analyze ... estimate statistics sample 100 rows; , it takes
almost 2 minutes to ananlyze 100 rows.
Any ideas ?
It's Oracle 8.1.6 on an expensive 6 CPUs HP machine with EMC storage.
Thanks a lot in advance.

DBAndrey

* 03-9254520
* 058-548133
* mailto:[EMAIL PROTECTED]

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).