modify tablespace allocaton_type from uniform to user

[DofNC]

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?

[PAVB]

do you mean uniform or autoallocate?

[DofNC]

current tablesapce allocation_type is UNIFORM.

[dbasan]

I am looking into changing a tablespace allocaton_type from uniform to user.

Why? What's the purpose now?

[PAVB]

You can't.
You have to create a new tablespace and move the objects there.

[DofNC]

thanks for the replies. main reason was to improve performance performance.

[mike9]

Are you sure that it would be faster with smaller extents?

[DofNC]

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.

[PAVB]

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?

[DofNC]

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.