| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: What impacts index build times on partitioned tables?
Cherie,
Now,
--Yes, we have parallel set to six
Do you have enough physical memory and CPU power/faster drives for writing
to handle this number of parallel process as it is intensive on such
resources specially available memory at that time plus writing capabilty of
your drives. There may be possible bottlenecks. Just check. If you are using
tool 'glance' this will give you a good idea while running this process.
Nologging is fine.
You are checking sort area size right...
What about temp tablespace and target tablespace fragmentation...
Regards
Rafiq
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Fri, 08 Feb 2002 04:43:25 -0800
Rafiq,
Yes, we have parallel set to six. We are using nologging. I am checking on the sort_area_size.
Thanks,
Cherie
"Mohammad
Rafiq" To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
<rafiq9857_at_hot cc:
mail.com> Subject: RE: What impacts
index build times on partitioned tables?
Sent by:
root_at_fatcity.c
om
02/07/02 03:06
PM
Please respond
to ORACLE-L
Please check following....
Are you creating indexes with parallel clause or not?
Are you using unrecoverable/nologging or not
Is your sort_area_size is ok?
What type of TEMP tablespace are you using with temp file or datafile
and sizing of extents are proper?
Is your target tablespace is not that much fragmented?
Regards
Rafiq
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Thu, 07 Feb 2002 08:55:47 -0800
Yechiel,
There is no other activity in this warehouse database while the index
builds are being done.
The users access is shut off, no batch jobs for this database are running,
and the nightly
data loads have already finished.
Thanks,
Cherie
אדר יחיאל
<adary_at_mehish. To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
co.il> cc:
Sent by: Subject: RE: What impacts
index build times on partitioned tables?
root_at_fatcity.c
om
02/07/02 10:20
AM
Please respond
to ORACLE-L
I think that it is related to the activity against the DB. Not only there is more load on the DB but access path that use the index can default to full table scan if the index is not available because you are rebuilding it just now.
Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il
> -----Original Message-----
> From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
> Sent: Thu, February 07, 2002 5:55 PM
> To: Multiple recipients of list ORACLE-L
> Subject: What impacts index build times on partitioned tables?
>
>
> We drop and recreate many indexes on our large partitioned fact table in
> our data warehouse nightly. The amount of data in the table increases
> gradually with time but there aren't huge fluctuations in the amount of
> data in the partitions from day to day.
>
> The problem is that the amount of time that it takes to recreate the
> indexes varies widely. From 40 minutes to almost three hours. I am
> inclined to believe that this is a scheduling issue related to the time
of
> the day and the day of the week. I think we may be experiencing
> competition from other batch jobs from other applications. One
> application, in particular, is sort of a black box because it is not on
> Oracle and it belongs to people who reside in a remote site.
>
> However, I want to make sure I've considered all of the possibilities.
> What factors might influence how long it takes to rebuild indexes on
> existing partitions?
> If I just did some maintenance to split out some data for a new time
> period
> into a new partition, might that make a difference in how long it takes
to
> build the indexes?
>
> These indexes are all locally partitioned indexes.
>
> Thanks for any insight you can lend.
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
>
> --
> 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).
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> This e-mail was scanned by the eSafe Mail Gateway
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: adary_at_mehish.co.il 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). MOHAMMAD RAFIQ _________________________________________________________________ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.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). MOHAMMAD RAFIQ _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.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 Fri Feb 08 2002 - 12:13:33 CST
 |
 |