Re: Drop Index takes longer in Prod than in Test

If they are global indexes, you can drop the partition,

ALTER INDEX SCOTT.I_EMP_ENAME_GLB_RANGE
DROP PARTITION I_EMP_ENAME_GLB_RANGE_P1; If they are locally partitioned indexes, you can mark those partitions unusable.

ALTER INDEX SCOTT.I_EMP_ENAME
MODIFY PARTITION I_EMP_ENAME_P1 UNUSABLE; In 9.2, you have to set the hidden parameter _skip_unusable_indexes=trueafter you have marked the index(es) unusable.

this will ensure that DML will not return errors when the unusable partition(s) is affected by the statements. Unusable partition(s) are not maintained during your data loads.

In 10g, _skip_unusable_indexes is true by default.

of course, Unique indexes cannot be worked around with this parameter and they will always return an error.

Once you are done with data load, you can rebuild the partition(s) with the PARALLEL
and NOLOGGING clauses to make it faster.

hope that helps,

regards,
anand

On 13/09/06, Khemmanivanh, Somckit <somckit.khemmanivanh_at_weyerhaeuser.com> wrote:
>
> Thanks Mark.
>
> Version is 9207 Oracle on HPUX.
>
> TS is LMT but not ASSM.
>
> The indexes are partitioned, the indexes in question are the foreign key
> indexes on the Fact Table.
>
> Please let me know what other info would be helpful.
>
> Thanks for the suggestion but I can't take the TS offline, other loads are
> occurring concurrently.
> ------------------------------
> *From:* Mark W. Farnham [mailto:mwf_at_rsiz.com]
> *Sent:* Wednesday, September 13, 2006 11:17 AM
> *To:* Khemmanivanh, Somckit; oracle-l_at_freelists.org
> *Subject:* RE: Drop Index takes longer in Prod than in Test
>
> We'll need version information and whether the tablespaces holding the
> indexes are dictionary managed or locally managed.
>
>
>
> Are the indexes partitioned or indexes of partitions, etc., etc..
>
>
>
> It may be possible for you to speed things up by taking the index
> tablespaces offline and doing an offline drop including contents, if the
> problem is the pecimal freeing of index extents from a dictionary managed
> tablespace. If the load process you cannot touch does not specify extent
> allocations, you may be able to speed things up by changing the space
> defaults on the index tablespace(s) to use very few extents (because the
> drop can be slow in adding extents back to the free space on dictionary
> managed tablespaces.) You also may benefit from moving to locally managed
> tablespaces.
>
>
>
> But these are all wild guesses without knowing a lot more.
>
>
>
> Good luck,
>
>
>
> mwf
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Khemmanivanh, Somckit
> *Sent:* Wednesday, September 13, 2006 1:17 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* Drop Index takes longer in Prod than in Test
>
>
>
> Hi,
>
>
>
> We have a DW nightly load that drops indexes as part of the load.
>
>
>
> The index drops can sometimes take minutes in prod -- whereas as test of
> the same drop, in the test environment, takes seconds. Now minutes might not
> seem like much, but there are many indexes so the times add up.
>
>
>
> I can't easily enable tracing on the Oracle side (prod server,
> multi-tiered env, etc...) so I'm looking for some tips for where/what else I
> can look for.
>
>
>
> Oh, the other constraint is that I can't modify the DW load process -- so
> I'm restricted solely to looking at the Oracle side.
>
>
>
> Your help is much appreciated.
>
>
>
> Thank you!
>

-- 
All I need to make a comedy is a park, a policeman and a pretty girl -
Charlie Chaplin

--
http://www.freelists.org/webpage/oracle-l