modify tablespace allocaton_type from uniform to user
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: modify tablespace allocaton_type from uniform to user

  1. #1
    Join Date
    Aug 2007
    Posts
    5

    modify tablespace allocaton_type from uniform to user

    I am looking into changing a tablespace allocaton_type from uniform to user. The tablespace size has alrady grown to 100 GB. What is the best approach for this?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    do you mean uniform or autoallocate?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Aug 2007
    Posts
    5
    current tablesapce allocation_type is UNIFORM.

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    I am looking into changing a tablespace allocaton_type from uniform to user.
    Why? What's the purpose now?
    Last edited by dbasan; 08-17-2007 at 03:53 PM.
    "What is past is PROLOGUE"

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You can't.
    You have to create a new tablespace and move the objects there.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Aug 2007
    Posts
    5
    thanks for the replies. main reason was to improve performance performance.

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    Are you sure that it would be faster with smaller extents?

  8. #8
    Join Date
    Aug 2007
    Posts
    5
    actually, looking into allowing bigger extent size, thus lowering the number of extents. I have an application that's doing a range scan on an index, and i thought (please let me know if i'm wrong) doing this help with performance.

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    That sounds a little extreme to me.

    Have you traced that specific query?... how about the "wait states"? where is time wasted?

    How large is your index?
    Is your index fragmented? how dense is it today?

    On the other hand, what's your Oracle version? are you running a single instance or a RAC?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Aug 2007
    Posts
    5
    Here's from my latest trace:

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 38499.13 41645.05 1039599939 1116666529 1350923979 6759604
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 2 38499.13 41645.05 1039599939 1116666529 1350923979 6759604

    Based on the above, hit ratio is only 58%.

    Looked into table fragmentation. Calculated fragmentation index using formula:
    ((DBA_SEGMENTS.BLOCKS-DBA_TABLES.EMPTY_BLOCKS-1)-DBA_TABLES.BLOCKS)
    -------------------------------------------------------------------
    ((DBA_SEGMENTS.BLOCKS-DBA_TABLES.EMPTY_BLOCKS-1)+0.0001)

    value is very close to 0...indicating rebuilding will not make a lot of difference. But I actually rebuilt the table and the underlying indexes using current storage values, an improvement of 10 percent - which is not enough.

    This is an isolated case. And it used to perform well. So it's got to be data related...possibly, due to data growth.

    Table size is 50 GB, spread over 1,200 extents.
    The index is 10 GB, spread over 2,200 extents.

    Oracle 9i. single instance.

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