Oracle-L: RE: analyze table takes forever

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Tue, 29 Oct 2002 15:08:29 -0800
Message-ID: <F001.004F6E09.20021029150829@fatcity.com>

Andrey,

What does V$SESSION_WAIT values for the session that performs the ANALYZE say? Another though: Do you have Parallel DEGREE set on that table?

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end of your journey in this earth?

The opinions and statements above are entirely my own and not those of my employer or clients **

-----Original Message-----
Sent: Tuesday, October 29, 2002 10:25 AM To: Multiple recipients of list ORACLE-L

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-----
<mailto:andreyb_at_elrontelesoft.com> ]
Sent: Tuesday, October 29, 2002 7:49 AM To: Multiple recipients of list ORACLE-L

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:andreyb_at_elrontelesoft.com <mailto:andreyb_at_elrontelesoft.com>

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com <http://www.orafaq.com>
--

Author: Andrey Bronfin
INET: andreyb_at_elrontelesoft.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com <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: ListGuru_at_fatcity.com (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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: John Kanagaraj
INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------