2010/10/08

Preparing a tablespace to become read only

Now I am reading the Oracle Database Administrator's Guide 11g Release 2 (11.2). And I have found some interesting detail here. That chapter says about preparing a tablespace to move in readonly state. And it proposes to take some action for doing blocks cleanout. But documentation suggests using something like this "select count(*) from table". It is not enough! Make sure Oracle really scans tables (not only some indexes) to give you row count.
I will use "test" table to demonstrate my point.
SQL> select * from v$version where rownum=1;
BANNER                                      
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          

SQL> sho sga
Total System Global Area  209235968 bytes                                       
Fixed Size                  1373600 bytes                                       
Variable Size             184552032 bytes                                       
Database Buffers           16777216 bytes                                       
Redo Buffers                6533120 bytes                                       

SQL> sho parameter db_block_size
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192                          

SQL> select 16777216/8192 from dual;
16777216/8192                                                                  
-------------                                                                  
         2048

SQL> create table test as select * from all_objects;
Table created.

SQL> desc test
 Name                          Null?    Type
 ---------------------------- -------- ----------------------------------------
 ...
 OBJECT_NAME                  NOT NULL VARCHAR2(30)
 OBJECT_ID                    NOT NULL NUMBER
 ...

SQL> create index idx_test_oid on test(object_id);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true)
PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name='TEST';
    BLOCKS                                                                     
----------                                                                     
      1051                                                                     

SQL> select x.name,y.value
  2  from v$statname x, v$mystat y
  3  where x.statistic# = y.statistic#
  4  and x.name like '%cleanout%'
  5  and value > 0;
NAME                                                                  VALUE    
---------------------------------------------------------------- ----------    
...
cleanouts only - consistent read gets                                     4 
...
10 rows selected.

SQL> update test set last_ddl_time=sysdate;
71858 rows updated.

SQL> commit;
Commit complete.

SQL> select x.name,y.value...

NAME                                                                  VALUE    
---------------------------------------------------------------- ----------    
cleanouts only - consistent read gets                                     4    

SQL> select count(1) from test;
  COUNT(1)                                                                                                                                                                                              
----------                                                                     
     71858                                                                     

SQL> select x.name,y.value...
NAME                                                                  VALUE    
---------------------------------------------------------------- ----------    
cleanouts only - consistent read gets                                     4    

SQL> select /*+full(test)*/ count(1) from test;
  COUNT(1)                                                                     
----------                                                                     
     71858                                                                     

SQL> select x.name,y.value...
NAME                                                                  VALUE    
---------------------------------------------------------------- ----------    
cleanouts only - consistent read gets                                   963

Furthermore, make sure Oracle scans all indexes for given table! They should be cleaned too.

SQL> create index idx_test_oname on test(object_name);
Index created.

SQL> update test set object_name = substr(object_name,1,25);
71858 rows updated.

SQL> commit;
Commit complete.

SQL> select x.name,y.value...
NAME                                                                  VALUE    
---------------------------------------------------------------- ----------    
cleanouts only - consistent read gets                                  2881    

SQL> set autotrace on explain

SQL> select /*+index(test idx_test_oname)*/ count(object_name) from test;
COUNT(OBJECT_NAME)                                                             
------------------                                                             
             71858                                                             

Execution Plan
----------------------------------------------------------                     
Plan hash value: 1651596932                                                    
                                                                               
---------------------------------------------------------------------------    
| Id  | Operation        | Name           | Rows  | Cost (%CPU)| Time     |    
---------------------------------------------------------------------------    
|   0 | SELECT STATEMENT |                |     1 |   358   (1)| 00:00:05 |    
|   1 |  SORT AGGREGATE  |                |     1 |            |          |    
|   2 |   INDEX FULL SCAN| IDX_TEST_ONAME | 71858 |   358   (1)| 00:00:05 |    
--------------------------------------------------------------------------- 

SQL> select x.name,y.value...
NAME                                                                  VALUE    
---------------------------------------------------------------- ----------    
cleanouts only - consistent read gets                                  3553    

SQL> select /*+full(test)*/ count(*) from test;
  COUNT(*)                                                                     
----------                                                                     
     71858                                                                     

Execution Plan
----------------------------------------------------------                     
Plan hash value: 1950795681                                                    
                                                                               
-------------------------------------------------------------------            
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |            
-------------------------------------------------------------------            
|   0 | SELECT STATEMENT   |      |     1 |   287   (1)| 00:00:04 |            
|   1 |  SORT AGGREGATE    |      |     1 |            |          |            
|   2 |   TABLE ACCESS FULL| TEST | 71858 |   287   (1)| 00:00:04 |            
-------------------------------------------------------------------            

SQL> select x.name,y.value...
NAME                                                                  VALUE    
---------------------------------------------------------------- ----------    
cleanouts only - consistent read gets                                  4578

Conclusion: I think, it is worth to use dbms_stats.gather_table_stats with cascade = > true and estimate_percent => 100 to achieve full blocks cleanout for each table in tablespace which is going to become readonly.