As John says, you can check on what Oracle is waiting
on .
It may not solve your problem, but can make the
analyze faster. Since your fact tables are partitioned
and since you're migrating from 8.0.4 that means range
partitioning. If the tables are partioned by date and
you do you allow update/insert in partitions other
than the current one then you can analyze only the
current one.
At a previous client, only the current partition of
the fact tables were analyzed after the daily load.
- Cherie_Machler_at_gelco.com a écrit : >
> 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).
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail :
http://fr.mail.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.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).
Received on Tue Jun 11 2002 - 12:18:35 CDT