Drop partition (big tabkes) elapsed time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Drop partition (big tabkes) elapsed time

  1. #1
    Join Date
    Feb 2003
    Posts
    8

    Question Drop partition (big tables) elapsed time

    Hello DBAs,

    I was wondering if you could help me with this free space issue.

    Currently we have some big partitioned tables, that have started to being recycled. Each partition has +2GB/+0.5GB of data/index.

    When we started to recycle partitions three weeks ago, the elapsed time to drop one was 15 to 20 minutes. But, since we have continued recycling space, now its taken more than 5 hours.

    The big problem is, since this is taking that long, it has started to affect other processes because of RBS or TEMP segment.

    I have noticed that it's not particular of an specific tablespace, because it's now affecting the whole database. Also, it does seem to affect more to index tablespaces than data's.

    Do you have any ideas about what can I do to solve this?

    Where do you think we might have the first issue to solve?

    Where could be the main problem? tablespaces, smon or dictionary?

    Thanks, Joe.
    Last edited by orajoe; 02-12-2003 at 04:36 AM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    So drop partition is now taking 5 hours? Do you have any global indexes on the table?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2003
    Posts
    8
    None. It has 21 bitmaps a 1 unique index, but all of them are local to each partition.

    I have also noticed that the delete of the index space is much slower than the needed for the data.

    Thanks.
    Last edited by orajoe; 02-12-2003 at 02:06 PM.

  4. #4
    Join Date
    Nov 2001
    Posts
    335
    Unique index mostly like is a global index. Please double check
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  5. #5
    Join Date
    Feb 2003
    Posts
    8
    Double checked. All of them are local. The table is subpartitioned, and the index is partitioned by time (range) and a dimension (hash).

    And none of all index subpartitions have become unusable.

    Thanks

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    are you using the "alter table ... drop partition ..." syntax?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    also, do you have any foreign keys defined against the pk of that table?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Feb 2003
    Posts
    8
    No foreign keys.

    And the complete syntax I'm using is:
    "alter table mytable drop partition mypartition1;"

    Jus FYI, I'm altering the sesion to the enable parallel ddl, each time I drop (or create) a partition for that table., in order to use more than 1 cpu to exec these statements.

    Thanks.

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    well why dont you try

    alter table mytable disable constraint my_constraint_fk
    --disable all constraints on these tables like foreign key
    alter table mytable truncate partition mypartition1
    --truncate the partition
    alter table mytable enable constraint my_constraint_pk

    instead and let us know

    regards
    Hrishy
    Last edited by hrishy; 02-13-2003 at 03:21 AM.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    dropping a partition is nothing to do with parallelism.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width