RE: Analyze running twice as long after upgrade to 8.1.7.2

Paula,

Yes, it is a partitioned table.

I am planning on switching over to analyzing it in parallel but first the application owners want to know WHY it's taking twice as long to analyze since the upgrade to 8.1.7.2. What is different about ANALYZE that would cause it to run so much longer.

I'm not sure if I can come up with an answer but I am going to try and give it a shot.

Thanks for your reply,

Cherie

                                                                                                                      
                    Paula_Stankus_at_doh.s                                                                               
                    tate.fl.us                To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>     
                    Sent by:                  cc:                                                                     
                    root_at_fatcity.com          Subject:     RE: Analyze running twice as long after upgrade to 8.1.7.2 
                                                                                                                      
                                                                                                                      
                    06/10/02 05:16 PM                                                                                 
                    Please respond to                                                                                 
                    ORACLE-L                                                                                          
                                                                                                                      
                                                                                                                      

I suggest running in parallel. Also, is the table partitioned?

-----Original Message-----
Sent: Monday, June 10, 2002 4:39 PM
To: Multiple recipients of list ORACLE-L

We just upgraded our data warehouse from version 8.0.4 to version 8.1.7.2 of Oracle. We run on Sun Solaris 2.6 vith Veritas Quick I/O.

We do an analyze compute nightly with a 10% estimate of our large, main fact table. Before the upgrade, the analyze ran for 45 minutes. Since the upgrade, it's run three times at a consistent 90 minutes.

I am trying to research on Metalink but have not had much success. Why would this analyze suddenly run longer? I wouldn't think that analyze code is being modified much in new releases. All the effort would go into

new packages like DBMS_STATS. Is anyone aware of analyze changing in 8i.

Our analyze was of a partitioned table and was single-threaded, not parallel. I can see that it is still single-threaded.

We really didn't change much during the upgrade. I added another datafile

to the SYSTEM tablespace (on the same file system as the previous file).

I also changed parallel_threads_per_cpu from 2 to 0. However, since we never did the analyze in parallel, I don't think it was an issue.

Any other ideas? I'd like to be able to give our application owners some explanation.

Thanks,

Cherie

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


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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: 
  INET: Cherie_Machler_at_gelco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).