how to improve TRUNCATE process
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: how to improve TRUNCATE process

  1. #1
    Join Date
    May 2002
    Posts
    163

    how to improve TRUNCATE process

    Truncate process is taking more than expected time. I can not use 'REUSE STORAGE' option. Which are the other areas or parameters I must look into?

    Regards
    Nwcomer
    Student

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    too many extents?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    May 2002
    Posts
    163
    Let me give u guys the details

    In production these tables(which I am truncating) is spread over 4 tablespace segments with total 4768 extents.

    Where as in my test database these tables are associated with only one TS with 4896 extents. And in Test DB it is taking hardly 1 minutes with more or less same volume od records.
    Nwcomer
    Student

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It sounds like you're using dictionary managed TS, not Locally Managed TS, right?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    May 2002
    Posts
    163
    how does that matter in such scenario? If it does, then it is same in 2 databases.
    Nwcomer
    Student

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    If you are using Oracle Rel 9.1.0.3 and LMT, then truncating table that has a large number of extents will take long time.

    If you are using 8.1.6 and DMT, then also truncate will take long time.

    Bugs are very common in Oracle. Test your Oracle Rel before you choose LMT or DMT.

    Tamil

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... but in general LMT's are much faster for this kind of operation, due to lower overhead in extent management.

    So how long is it taking in production? Got any indexes on there?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #8
    Join Date
    May 2002
    Posts
    163
    Yes there is. But the setup is same in test database too. But it is taking hardly 1 minute in the test server.

    What can be the other reasons!!
    Nwcomer
    Student

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Well, there's obviously the potential for a bug -- is this partitioned by the way, since it's on multiple TS's?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Clone the prod into test and do the test again.

    Did you analyze sys schema by any chance?

    Tamil

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