Standard tablespace management

• storage parameters
• change defaults for tables
• rebuild indexes
• analyze schema

It is recommended, to put the indexes of all tables on a different tablespace than the data. For best performance on select-statements, it should be on a different disk (and a different controller), so that the database can check the index on disk1 while getting the data on disk2. You can easily change the index-tablespaces using the 'alter index' command. But first, let's have some thoughts about storage parameters!

A lot of databases are using standard parameters for the storage clause. The most important storage parameters are minextents, maxextents, initial, next and pct_increase. So what does it mean?

So what to do now is changing the storage parameters of all tables. For example, we change all small tables to next 100k and pctincrease 0 , all medium tables (which already have the next extent higher than 1 MB) to next 1 MB and pctincrease 0 and all big tables to next 10MB and pctincrease 0. Modify the following script that it'll fit your system. Then run the script with SQL-Plus.

Script MAKE_STORAGE.SQL

SET FEEDBACK OFF 
SET HEADING OFF 
SET TERMOUT OFF 
SET LINESIZE 200 


SPOOL ALTER_TABLE_STORAGES.SQL  
  

select 'alter table '||table_name||' storage '|| 
'(NEXT 100k pctincrease 0 maxextents    unlimited minextents 1);' 
from user_tables where next_extent <= 100*1024; 

select    'alter table '||table_name||' storage '|| 
'(NEXT 1M pctincrease 0 maxextents    unlimited minextents 1);
' from user_tables where next_extent < 10*1024*1024    
and next_extent > 100*1024 ; 

select 'alter table '||table_name||' storage '||    
'(NEXT 10M pctincrease 0 maxextents unlimited minextents 1);' 
from user_tables    where next_extent >= 10*1024*1024; 

SPOOL OFF; 

SET FEEDBACK ON 
SET HEADING ON    
SET TERMOUT ON 

@ALTER_TABLE_STORAGES

After having changed the storage parameters of all tables, we now can create a new tablespace on a different disk than the data tablespace and rebuild all indexes with the right storage parameters.

So first, let's create a new tablespace wit size 100 MB and give the tablespace the default storage parameters.

Create tablespace INDX datafile='C:\DATABASE\ORCL\indx.ora' size 100M storage   
(initial 100k next 100k maxextents unlimited minextents 1 pctincrease 0);

So all new crated indexes will get these storage parameters. If you want the same parameters as default for the data you can modify the data tablespace using

alter tablespace DATA storage 
(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);

Having created a new tablespace called INDX, we no can rebuild all indexes to use that tablespace and new storage parameters. We do the same thing as we did for the tables, small indexes will get 100k, medium 1 MB and big ones 10 MB as next extent.

Script MAKE_INDEX_REBUILD.SQL

SET FEEDBACK OFF 
SET HEADING OFF 
SET TERMOUT OFF 
SET LINESIZE 200 


SPOOL ALTER_INDEX_STORAGES.SQL  
  

select 'alter index '||index_name||' rebuild storage '|| 
'(INITIAL 100k NEXT 100k pctincrease 0 maxextents unlimited minextents 1)'||
'tablespace INDX;' 
from user_indexes where next_extent <= 100*1024; 


select 'alter index '||index_name||' rebuild storage '|| 
'(INITIAL 1M NEXT 1M pctincrease 0 maxextents unlimited minextents 1)'||
'tablespace INDX;' 
from user_indexes where next_extent < 10*1024*1024 
and next_extent > 100*1024 ; 


select 'alter index '||index_name||' rebuild storage '|| 
'(INITIAL 10M NEXT 10M pctincrease 0 maxextents unlimited minextents 1)'||
'tablespace INDX;' 
from user_indexes where next_extent > 10*1024*1024 ;

SPOOL OFF; 

SET FEEDBACK ON 
SET HEADING ON    
SET TERMOUT ON 

@ALTER_INDEX_STORAGES

If you have tables or indexes which doesn't fit in th 100k, 1MB, 10MB size, you can easily change the storage parameter using the syntax in the created files ALTER_TABLE_STORAGES and ALTER_INDEX_STORAGES.

For best performance, we now have to analyze tables and indexes to let the optimizer work properly. In the following script, you can exchange 'compute' with 'estimate' if you have really big tables. The script will, depending on how much data is in the tables, run several minutes. If you use 'compute', it runs longer.

The script only makes sense, if you have data in your tables. If you have a lot of traffic in your db, you should start the script once a week (or day).

Script ANALYZE.SQL

SET FEEDBACK OFF 
SET HEADING OFF 
SET TERMOUT OFF 
SET LINESIZE 200 


SPOOL ANALYZE_SCHEMA.SQL
    

select 'analyze table '||table_name||' compute statistics'|| 
' for all indexed    columns;
' from user_tables; 


select 'analyze table '||table_name||' compute statistics;
'    from user_tables; 


SPOOL OFF; 


SET FEEDBACK ON 
SET HEADING ON 
SET TERMOUT ON 


@ANALYZE_SCHEMA

You can use the utility dbms_utility.analyze_schema('SCHEMA_NAME','COMPUTE')
as well as the script but due to an Oracle8 - bug, the statistics for the indexes might not be filled properly.