REBUILDING THE TABLE BY SHRINKING ITS FREE SPACE

Starting with Oracle 10g , there is a very nice feature called 'SHRINK SPACE'.

As of this release, tables can be reorganized online, without affecting the users functionality.
That is:

* The table itself must be in an ASMM tablespace
* The table must have row movement enabled
* At the end of the action, a short table level lock takes place to adjust the HWM (Highwater Mark) of the table.

And it's easy!!
Just enable 'row movement' and start shrinking tables:

SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT;

  This enables row movement of the table. This is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed.

SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT;

This shrinks the used space of the table, but does not update the HWM. This is useful if you don't want to have a table-level lock during business hours.

SQL> ALTER TABLE MYTABLE SHRINK SPACE;

This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with 'compact' option has been done, just to update the HWM.

SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE;

This command shrinks the contents of the table and all dependent objects like indexes.

High Water Mark:

• The HWM is the pointer to that location of a table where on any point in history the table has grown to. 
•  
•  If a lot of rows are deleted from a table, a lot of free space exists before the HWM. 
•  
• Doing a full table scan Oracle will not only read the actual rows, but also the empty space up to the HWM.
   
• This is wasted time and is worsening the performance of getting data from that table.
•  
• So if from a large table a lot of rows are deleted, e.g. a count(*) will still take as long as before the delete.

Triggers and indexes:

In contradiction to moving a table, or exporting/importing its data, triggers and indexes are NOT affected by a shrink space command. They will remain valid and functional during and after the operation.

Wasted space:

How to determine if a table is a candidate for a reorganization ?

Use the below script to determine if a table has a lot of wasted space. 

If it substantial in relation to the total size of the table, then a reorganization ( shrink ) is recommended.

set lines 200;
column owner format a15;
column segment_name format a30;

select
a.owner,
a.segment_name,
a.segment_type,
round(a.bytes/1024/1024,0) MBS,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED
from dba_segments a, dba_tables b
where a.owner=b.owner
and a.owner not like 'SYS%'
and a.segment_name = b.table_name
and a.segment_type='TABLE'
group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)
having round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc ;

The above script is only working if the tables involved have statistics gathered on them

PCTFREE

An extra remark about PCTFREE.  

• Tables with a high value of PCTFREE show also a higher value of wasted space.
• The space reserved for PCTFREE in a block can not be shrink-ed. So even after a shrink of a table a lot of wasted space can remain.
• The effect of PCTFREE also seen before the shrink is executed when looking at the wasted space using the above script.

In an example we have three identical tables with the following structure:

SQL> CREATE TABLE T1 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 40;
SQL> CREATE TABLE T2 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 10;
SQL> CREATE TABLE T3 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 50;

All tables are loaded with the same data, approx. 5.5 million rows.
When running the above script it shows:

SEGMENT_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	5064	2348
T2	TABLE	3333	617
T3	TABLE	6144	3428

Just because of the variation in the value of PCTFREE, the total size and wasted space are different per table.

The effect of PCTFREE after the shrink is that not all "wasted space" is released. 

The term "wasted space" therefore is not really valid with a high PCTFREE. It is wasted space + the reserved space directly available for updates. 

Of course above, just after table creation there is no wasted space. It is only reserved (pctfree) space. Therefore - for this example - we are going to delete approx 30% of the rows of all tables.

SQL> delete from t1 where length(val)< 300;

SQL> delete from t2 where length(val)< 300;

SQL> delete from t3 where length(val)< 300;

As soon as the delete is finished we compute stats on these tables in order for the wasted-space script to have a valid calculation base.

Running the script again now shows:

SEGMENT_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	5064	2789
T2	TABLE	3333	1058
T3	TABLE	6144	3869

After this we are going to 'shrink' the available wasted space, using:

SQL> ALTER TABLE T1 SHRINK SPACE CASCADE;
SQL> ALTER TABLE T2 SHRINK SPACE CASCADE;
SQL> ALTER TABLE T3 SHRINK SPACE CASCADE;

The result is:

SEGMENT_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	4245	1970
T2	TABLE	2779	505
T3	TABLE	5292	3018

This test clearly shows that even if Oracle or its tools like ADDM inform you about wasted space in a table, it might be that this is because you have a high value for PCTFREE. Shrinking these table might afterwards look like if nothing happened. 

Check in that case if PCTFREE is high and if the value of 'wasted space' divided by the 'total size' is equal to that value:

Example:

Table T1: PCTFREE = 40% ==> 1970 / 4245= 46%
Table T3: PCTFREE = 50% ==> 3018/5292 = 57%

So, actually the 'wasted space' of these tables is respectively 6% and 7 %

Table Fragmentation

Identify and Remove Table Fragmentation

In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into user_tables.

Steps to Check and Remove Fragmentation 

1. Gather table states:

• To check exact difference in table actual size (user_segments) and stats size (user_tables). 
• The difference between these value will report actual fragmentation to DBA. 
• So, We have to have updated stats on the table stored in user_tables. 
• Check LAST_ANALYZED value for table in user_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats('schema_name','table_name');

2. Check for Fragmentation in table:

Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.

select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", 

round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',

((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " 

from dba_tables 

where table_name =’ table_Name’ 

AND OWNER LIKE 'schema_name';

Note: This query fetch data from user_tables, so the accuracy of result depends on user_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

Ways to Remove Fragmentation:

There are two ways to remove Table fragmentation

a. Export and import the table:- (difficult to implement in production environment)

b. Move table in to different or same tablespace:- (Depends upon the free space available in the tablespace)


Here I am following 2b option by keeping table availability in mind.

3. Collect status of all the indexes on the table:

We will record Index status at one place, So that we get back them after completion of this exercise, 

 select index_name,status from user_indexes where table_name like 'table_name';

status may be valid or unusable.

4. Move table in to new tablespace:

In this step we will move fragmented table from one tablespace to another tablespace to reclaim fragmented space. Find Current size of you table from user_segments and check if any other tablespace has same free space available. So, that we can move this table to new tablespace. Before this DBA has to enable row movement for table.

alter table table_name enable row movement;

alter table table_name move tablespace new_tablespace_name;

5. Move table in to old tablespace:

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

If we have as free space available as of table size in same tablespace which contain the table. Then we can replace step 4 and 5 by

alter table table_name enable row movement;

alter table table_name move ;

6. Rebuild all indexes:

We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

select index_name from user_indexes where table_name like 'table_name';

Use this command for each index.

alter index index_name rebuild online;

7. Cross Check Index Status:

select index_name,status from user_indexes where table_name like 'table_name';

Here, value in status field must be valid.

8. Check Table size:

Now again check table size using and DBA will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from user_table where table_name='table_name';

If, Remote DBA will again execute query in #2, He will find same result because stats of the table is still old. So He has to collect table status. You can also observe same amount of extra free space in current tablespase which was reclaimed after removing table fragmentation.

9. Gather table states:

exec dbms_stats.gather_table_stats('schema_name','table_name');