Feeds:
Posts
Comments

Posts Tagged ‘Fragmentation’

Yesterday my junior team member was confused about fragmentation and High water mark concepts.Also there was good comment on my fragmentation post, so it inspire me to write something about the High Watermark and the Oracle 10gR1 New Feature SEGMENT SHRINKING.About fragmentation I have already disscused in my previous post.

The High Watermark is the maximum fill-grade a table has ever reached.
Above the high watermark are only empty blocks.
These blocks can be formatted or unformatted.

First let’s have a look at the question when space is allocated

– when you create a table at least one extent (contiguous blocks) is allocated to the table
– if you have specified MINEXTENTS the number of MINEXTENTS extents
will be allocated immedaitely to the table
– if you have not specified MINEXTENTS then exactely one extent
will be allocated .

Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made.

When you insert rows into the table the high watermark will be bumped up step by step.
This is done by the server process which makes the inserts.

Now let us take a look at when space is released again from a segment like a table or index:

Let’s asume that we have filled a table with 100’0000 rows.
And let’s asume that we deleted 50’000 rows afterwards.
In this case the high watermark will have reached the level of 100’000 and will have stayed there. Which means that we have empty blocks below the high watermark now.
Oracle has a good reason this: it might occur that you delete rows and immediately this you insert rows into the same table. In this case it is good that the space was not released with the deletes, because it had to be get reallocate again for the following inserts, which would mean permanent changes to the data dictionary
(=> dba_free_space, dba_extents, dba_segements …) .
Furthermore the physical addresses of the deleted row get recycled by new rows.

These empty blocks below the high watermark can get annoying in a number of situations because they are not used by DIRECT LOADs and DIRECT PATH LOADs:

1. seriell direct load:
INSERT /*+ APPEND */
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;

2. parallel direct load:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLLEL(hr.employees,2)
INTO hr.employees
NOLOGGING
SELECT *
FROM oe.emps;

3. direct path loads:
sqlldr hr/hr control=lcaselutz.ctl … direct=y (default is direct=n)

All the above actions case that the SGA is not used for the inserts but the PGA:
there wil be temporary segements filled and dumped into newly formatted blocks above the high watermark.

So we might want to get high watermark down before we load data into the table in order to use the free empty blocks for the loading.

So how can we release unused space from a table?

There are a number of possible options which are already available before Oracle 10g:
– What we always could do is export and import the segment.
After an import the table will have only one extent.
The rows will have new physical addresses and
the high watermark will be adjusted.
– Another option would be to TRUNCATE the table.
With this we would loose all rows which are in the table.
So we cannot use this if we want to keep existing records.

With Oracle 9i another possibilty was implemented:
ALTER TABLE emp MOVE TABLESPACE users;
This statement will also cause that
– the rows will have new physical addresses and
– the high watermark will be adjusted.
But for this:
– we need a full (exclusive) table lock
– the indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management.
In such a tablespace a table does not really have a High watermark!
It uses two watermarks instead:
– the High High Watermark referred to as HHWM, above which alle blocks ar unformatted.
– the Low High Watermark referred to as LHWM below which all blocks are formatted.
We now can have unformatted blocks in the middle of a segment!

ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.
With the table shrinking feature we can get Oracle
to move rows which are located in the middle or at the end of a segment
further more down to the beginning of the segment and by
this make the segment more compact.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing
ALTER TABLE emp ENABLE ROW MOVEMENT;
ROWIDs are normally assigned to a row for the life time of the row at insert time.

After we have given Oracle the permission to change the ROWIDs
we can now issue a shrink statement.
ALTER TABLE emp SHRINK SPACE;

This statement will procede in two steps:
– The first step makes the segment compact
by moving rows further down to free blocks at the beginning of the segment.
– The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,
but for a very short moment only.

Table shrinking…
– will adjust the high watermark
– can be done online
– will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
– indexes will be maintained and remain usable
– can be made in one go
– can be made in two steps
(this can be usefull if you cannot get a full table lock during certain hours:
you only make the first step and adjust the high watermark later
when it is more conveniant:

– ALTER TABLE emp SHRINK SPACE; – only for the emp table
– ALTER TABLE emp SHRINK SPACE CASCADE; – for all dependent objects as well

– ALTER TABLE emp SHRINK SPACE COMPACT; – only makes the first step (moves the rows)

How are the indexes maintained?
In the first phase Oracle scans the segment from the back to find the position of the last row.
Afterwards it scan the segment from the beginning to find the position of the first free slot in a block in this segment. In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes the row from the back and inserts it into the free position at front of the segement. Now Oracle scan the segement from the back and front again and again until it finds that the two positions are the same.
Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first pase of SHRINK TABLE statement.

The following restrictions apply to table shrinking:

1.) It is only possible in tablespaces with ASSM.
2.) You cannot shrink:
– UNDO segments
– temporary segments
– clustered tables
– tables with a colmn of datatype LONG
– LOB indexes
– IOT mapping tables and IOT overflow segments
– tables with MVIEWS with ON COMMIT
– tables with MVIEWS which are based on ROWIDs

The Oracle 10g Oracle comes with a Segment Advisor utility.
The Enterprise Manager, Database Control, even has a wizzard which can search for shrink candidates.

This advisor is run automatically by an autotask job on a regular basis in the default maintainance window.

You can use the built in package DBMS_SPACE to run the advisor manually as well.
I will blog about this later on some time.

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/20/table-fragmentation-how-to-avoid-same/

Advertisements

Read Full Post »

When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don’t have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

Small example to find the table fragmentation.

SQL> select count(*) from big1;

1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;

300000 rows deleted.

SQL> commit;

Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;

342226 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BIG1′);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              72952          30604.2

Note = 72952 – 30604 = 42348 Kb is wasted space in table
 

The difference between two values is 60% and Pctfree 10% (default) – so, the table has 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table … move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

Option: 1 “alter table … move + rebuild indexes”

SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes
2 where table_name = ‘BIG1’;

STATUS INDEX_NAME
——– ——————————
UNUSABLE BIGIDX

SQL> alter index bigidx rebuild;

Index altered.

SQL> select status,index_name from user_indexes
2 where table_name = ‘BIG1’;

STATUS INDEX_NAME
——– ——————————
VALID BIGIDX

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BIG1′);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              38224           30727.37

Option: 2 “Create table as select”

SQL> create table big2 as select * from big1;

Table created.

SQL> drop table big1 purge;

Table dropped.

SQL> rename big2 to big1;

Table renamed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BIG1′);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              85536            68986.97

SQL> select status from user_indexes
2 where table_name = ‘BIG1′;

no rows selected

SQL> –Note we need to create all indexes.

Option: 3 “export / truncate / import”

SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;

TABLE_NAME size
—————————— ——————————————
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1’;

TABLE_NAME size
—————————— ——————————————
BIG1 42535.54kb

SQL> select status from user_indexes where table_name = ‘BIG1′;

STATUS
——–
VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:>exp scott/tiger@Orcl file=c:big1.dmp tables=big1

Export: Release 10.1.0.5.0 – Production on…..

Export terminated successfully without warnings.

SQL> truncate table big1;

Table truncated.

imp scott/tiger@Orcl file=c:big1.dmp ignore=y

Import: Release 10.1.0.5.0 – Production on….

Import terminated successfully without warnings.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;

TABLE_NAME size
—————————— ——————————————
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1’;

TABLE_NAME size
—————————— ——————————————
BIG1 42535.54kb

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BIG1′);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;

TABLE_NAME size
—————————— ——————————————
BIG1 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1’;

TABLE_NAME size
—————————— ——————————————
BIG1 42542.27kb

SQL> select status from user_indexes where table_name = ‘BIG1’;

STATUS
——–
VALID

SQL> exec dbms_redefinition.can_redef_table(‘SCOTT’,’BIG1′,-
> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 4 “dbms_redefinition”….Which we will disscus afterwords….

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Table fragmentation – when?

If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.

“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.

Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.

SQL> create table test as select * from dba_tables; — Create a table

Table created.

SQL> analyze table test compute statistics; — Analyze it

Table analyzed.

SQL> select blocks “Ever Used”, empty_blocks “Never Used”, num_rows “Total rows”
2 from user_tables where table_name=’TEST’; — The number of blocks used/free

Ever Used Never Used Total rows
———- ———- ———-
49 6 1680

SQL> delete from test where owner=’SYS’; — Im deleting almost half the number of rows.

764 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics; — Analyze it again

Table analyzed.

SQL> select blocks “Ever Used”, empty_blocks “Never Used”, num_rows “Total rows”

2 from user_tables where table_name=’TEST’; — No difference in blocks usage

Ever Used Never Used Total rows
———- ———- ———-
49 6 916

PL/SQL procedure successfully completed.

Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.

Reasons to reorganization

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.

Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to reorganize?

Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table
a) Export, drop, and import.
b) Alter table move (to another tablespace, or same tablespace).

Oracle 10g provides us a new way of reorganizing the data.

Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.

Before using this command, you should have row movement enabled.

SQL> alter table test enable row movement;

Table altered.

There are 2 ways of using this command.

1. Break in two parts: In first part rearrange rows and in second part reset the HWM.

Part 1: Rearrange (All DML’s can happen during this time)

SQL> alter table test shrink space compact;

Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table sa shrink space;

Table altered.

2. Do it in one go:

SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)

Table altered.

Few advantages over the conventional methods

1. Unlike “alter table move ..”, indexes are not in UNUSABLE state. After shrink command, indexes are updated also.

2. Its an online operation, So you dont need downtime to do this reorg.

3. It doesnot require any extra space for the process to complete.

Conclusion

Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

Read Full Post »

Tablespace Fragmentation And Defragmentation
Oracle Tablespace Fragmentation
Honeycomb fragmentation
Bubble fragmentationis
convert DMT to LMT:
convert LMT to DMT:
Detecting Oracle Tablespace Fragmentation
Oracle Tablespace Fragmentation
Honeycomb fragmentation
Which is easy to fix. All that needs to be done is to combine adjacent free segments into one by issuing a coalesce statement:

alter tablespace USERS coalesce;

Bubble fragmentationis
Which is more difficult to handle. Of course, the best course of action is to prevent it in the first place. And as discussed earlier, the best weapon for this is to use locally-managed tablespaces. It may sound too simple, but in reality, implementing these storage structures in your database can just about remove the need to perform full tablespace reorganizations.

However, what do you do if you are in a situation where you have many databases that were set up with dictionary-managed tablespaces? You have two options, at least if you are running Oracle 8i (the version that gave birth to locally-managed tablespaces). First, you can create new locally-managed tablespaces in your database and then perform one last, full tablespace reorganization of all the database objects into the new tablespaces.

Needless to say, this can be a difficult task if you have large databases and no third-party reorganization tool. However, it will be worth the effort as you will likely never have to reorganize your tablespaces again, and your objects should never again encounter a maximum extent limit.

If you are using Oracle 8.1.6 or higher, you can convert any current dictionary-managed tablespaces to locally-managed tablespaces. Buried in the Oracle documentation is a procedure for converting a tablespace’s extent management from dictionary to local or vice-versa.

convert DMT to LMT:
sys.dbms_space_admin .tablespace_migrate_to_local(‘USERS’)

If you are afraid of how long this procedure might take on large tablespaces, do not be. It actually runs very fast.

convert LMT to DMT:
sys.dbms_space_admin .tablespace_migrate_from_local(‘USERS’)

Detecting Oracle Tablespace Fragmentation
How can you tell if your tablespaces are suffering from fragmentation problems and then identify the type of fragmentation? The detection and diagnosis is not hard to make at all. To determine if your tablespaces are having a problem with fragmentation, you can use the tsfrag.sql script:

select

tablespace_name, count(*) free_chunks, decode( round((max(bytes) / 1024000),2), null,0, round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2), 0) fragmentation_index

from

sys.dba_free_space

group by

tablespace_name

order by 2 desc, 1;

Output would be like following.

TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX
—————————— ———– ————- ——————-
C00_DATA 27811 6.85 .17
C00_INDEX 6933 1.02 .2
UNDOTBS1 330 165.82 6.5
SYSAUX 144 1483.58 28.43
C00_SECURITY 34 97.54 40.97
C00_MEDIA 2 2113.47 69.66
C00_REPORT 2 99.26 83.74
SYSTEM 2 505.79 84.06
USERS 2 4.61 83.51

When you examine the script output, you need to hone in on a couple of columns in particular. First, notice the fragmentation index column. This will give your tablespace an overall ranking with respect to how badly it is actually fragmented. A 100% score indicates no fragmentation at all. Lesser scores verify the presence of fragmentation.

The free chunks count column will tell you how many segments of free space are scattered throughout the tablespace. One thing to keep in mind is that tablespaces with multiple datafiles will always show a free chunk count greater than one because each datafile will likely have at least one pocket of free space.

Read Full Post »

Fragmentation is use to happen for tabl as well as for tablespace.Fragmentation is nothing but waisted space(After deletion of data).

set linesize 150
column tablespace_name format a20 heading ‘Tablespace’
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading ‘Total| Size(Mb)’
column Tot_Free format 999,999,999 heading ‘Total Free(MB)’
column Pct_Free format 999.99 heading ‘% Free’
column Chunks_Free format 9999 heading ‘No Of Ext.’
column Max_Free format 999,999,999 heading ‘Max Free(Kb)’
set echo off
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by pct_free;

How to avoide tablespace fragmentation please reffer

How to find and avoide table fragmentation please reffer

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »