-
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?
-
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.
-
current tablesapce allocation_type is UNIFORM.
-
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"
-
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.
-
thanks for the replies. main reason was to improve performance performance.
-
Are you sure that it would be faster with smaller extents?
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|