Redo logs switching frequency

To monitor redo switching below script will generate a report for last two weeks activity of redo switching. It is quite helpful



set linesize 300
set pagesize 5000
set trimspool on
column 00 format 99 heading "00:00"
column 01 format 99 heading "1am"
column 02 format 99 heading "2am"
column 03 format 99 heading "3am"
column 04 format 99 heading "4am"
column 05 format 99 heading "5am"
column 06 format 99 heading "6am"
column 07 format 99 heading "7am"
column 08 format 99 heading "8am"
column 09 format 99 heading "9am"
column 10 format 99 heading "10am"
column 11 format 99 heading "11am"
column 12 format 99 heading "12:00"
column 13 format 99 heading "1pm"
column 14 format 99 heading "2pm"
column 15 format 99 heading "3pm"
column 16 format 99 heading "4pm"
column 17 format 99 heading "5pm"
column 18 format 99 heading "6pm"
column 19 format 99 heading "7pm"
column 20 format 99 heading "8pm"
column 21 format 99 heading "9pm"
column 22 format 99 heading "10pm"
column 23 format 99 heading "11pm"
column 24 format 99 heading "12pm"
column "Day" format a3

prompt
prompt Redo Log Switches
prompt

SELECT trunc (first_time) "Date",
to_char (trunc (first_time),'Dy') "Day",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 0, 1)) "00",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 1, 1)) "01",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 2, 1)) "02",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 3, 1)) "03",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 4, 1)) "04",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 5, 1)) "05",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 6, 1)) "06",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 7, 1)) "07",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 8, 1)) "08",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 9, 1)) "09",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 10, 1)) "10",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 11, 1)) "11",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 12, 1)) "12",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 13, 1)) "13",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 14, 1)) "14",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 15, 1)) "15",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 16, 1)) "16",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 17, 1)) "17",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 18, 1)) "18",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 19, 1)) "19",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 20, 1)) "20",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 21, 1)) "21",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 22, 1)) "22",
sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 23, 1)) "23"
from v$log_history
where trunc (first_time) >= (trunc(sysdate) - 14) -- last X days. 0 = today only. 1 = today and yesterday
group by trunc (first_time)
order by trunc (first_time) DESC
/





Second script is to find out average log switch per minute

COLUMN avg_log_switch_min format 9,999.99 heading "Average
Log
Switch
Times
(Min)"

SET lines 132 pages 60 feedback off verify off echo off
TTITLE 'Average Log Switch Time Report'
PROMPT 'Note that high rates of Redo Log switching can cause performance problems'
PROMPT 'If this report indicates average switch times of less than 10 minutes'
PROMPT 'you should consider increasing the size of your redo logs.'
WITH redo_log_switch_times AS
(SELECT sequence#, first_time,
LAG (first_time, 1) OVER (ORDER BY first_time) AS LAG,
first_time
- LAG (first_time, 1) OVER (ORDER BY first_time) lag_time,
1440
* (first_time - LAG (first_time, 1) OVER (ORDER BY first_time)
) lag_time_pct_mins
FROM v$log_history
ORDER BY sequence#)
SELECT AVG (lag_time_pct_mins) avg_log_switch_min
FROM redo_log_switch_times;


Third script is to monitor Redo latch statistics

COLUMN name FORMAT a30 HEADING Name
COLUMN percent FORMAT 999.999 HEADING Percent
COLUMN total HEADING Total
rem
SELECT
l2.name,
immediate_gets+gets Total,
immediate_gets "Immediates",
misses+immediate_misses "Total Misses",
DECODE (100.*(GREATEST(misses+immediate_misses,1)/
GREATEST(immediate_gets+gets,1)),100,0) Percent
FROM
v$latch l1,
v$latchname l2
WHERE
l2.name like '%redo%'
and l1.latch#=l2.latch# ;

In my opinion by using the combination of these scripts you should be able to identify problem with redo logs.

Deleting Duplicate Rows

It is not very common to delete duplicate rows but when you do then it has to be quick.

Here is an example of finding duplicate rows and then script to delete them.

To find the Duplicate rows

select article,art_version,medasset,med_version,count(*) from articleasses
group by article,art_version,medasset,med_version
 having count(*) >1

Delete Duplicate Rows

Once you are happy with the results of above query and backup the table then use this procedure to delete duplicate rows.

declare
v_rowid     varchar2(100);
i_count     number(3);
cursor c_dup
is
select medasset,med_version ,count(*) from medasset
group by medasset,med_version
having count(*) >1;
begin
    i_count := 0;
    i_count := 0;
    for r_dup in c_dup loop
        select max(rowid)
        into v_rowid
        from medasset
        where medasset = r_dup.medasset
 and med_version = r_dup.med_version;
        delete from medasset
        where medasset = r_dup.medasset
  and med_version=r_dup.med_version
        and rowid = v_rowid;
        i_count := i_count + 1;
        dbms_output.put_line('Record = '||i_count);
    end loop;
 end;
/

Check the results again.

Auditing and Restricting User Connections

This tip was published on Oracle website in 2003. It looks like it is archived now by oracle but some other website has copied it to their own websites. I have now copied it to my blog.

Tip of the Week

Tip for Week of June 14, 2004

This tip comes from Ajwat Ul-Hasan, Database Administrator/System Analyst, The Carphonewarehouse in London, United Kingdom.



In Oracle9i, this script can restrict users logging into the database. In one table you provide information about which users are allowed to log into the database. You can modify this script to allow or block a user with respect to application, ip address, username, osuser, terminal, etc. It is a very easy and efficient way of implementing security in database.







create table valid_users (

valid_ora VARCHAR2(30),

valid_os varchar2(30)

);

and another table

CREATE TABLE USER_AUDIT

(

SID NUMBER NULL,

SERIAL# NUMBER NULL,

TIMESTAMP DATE NULL,

USERNAME VARCHAR2(30) NULL,

OSUSERID VARCHAR2(30) NULL,

MACHINENAME VARCHAR2(64) NULL,

PROGRAM VARCHAR2(48) NULL

)





And then create this trigger:







CREATE OR REPLACE TRIGGER test_audit

AFTER LOGON ON database

DECLARE

machinename VARCHAR2(64);

osuserid VARCHAR2(30);

ora_username VARCHAR2(30) DEFAULT NULL;

os_username VARCHAR2(30);

v_sid NUMBER;

v_serial NUMBER;

v_program VARCHAR2(48);

v_numuser NUMBER;

CURSOR c1 IS

SELECT sid, serial#, osuser, machine, program

FROM v$session

WHERE audsid = userenv('sessionid');

BEGIN

OPEN c1;

FETCH c1 INTO v_sid, v_serial, osuserid, machinename,

v_program;

CLOSE c1;

SELECT count(*)

INTO v_numuser

FROM valid_users

WHERE valid_ora = user;



IF v_numuser > 0 then

INSERT INTO user_audit

VALUES (v_sid, v_serial, sysdate,

user, osuserid, machinename,v_program);

ELSE

INSERT INTO user_audit

VALUES (v_sid, v_serial, ! sysdate,

user, osuserid, machinename,'IN ELSE');

raise_application_error(-20001,'You are not allowed to connect

to the database');

END IF;

END;

/





In the valid_user table you have to insert information about valid users, otherwise no user can log in to the database. You can generate information about user connection by using information in user_audit table.

UNDO TABLESPACE MANAGEMENT

This document intends to provide information about UNDO tablespace that is not commonly available. I have not included syntax of tablespace creation, dropping etc commands as this can be easily available in books, manuals and websites.


WHAT IS UNDO?

Word UNDO means to reverse or erase the change. In oracle world, UNDO allows to reverse the transaction so that database looks like before the transaction started. Also it provides read consistent image of data i.e. if during a transaction if one session is changing a data where as at the same time other session wants to read that data then UNDO will provide the state of data before the transaction starts.


USE OF UNDO

There is few main usage of UNDO in oracle database.

* UNDO is used to reverse the uncommitted transaction on the issue of ROLLBACK command.
* It is used to provide read consistent image of a record
* It is used to during database recovery to apply any uncommitted transaction in redo logs to datafiles.
* Flashback Query also uses UNDO to get the image of data back in time.

UNDO MANAGEMENT

Oracle needs UNDO tablespace to create undo segments. If undo tablespace is not created then it will use SYSTEM tablespace as UNDO and not recommended. To create UNDO tablespace you have to use UNDO keyword in create tablespace command. Most common configuration of UNDO_MANAGEMENT is AUTO (Default is MANUAL in 8i & 9i).

One can have multiple UNDO tablespace in a database but only one can be active at any given time.

In AUTOMATIC undo management oracle create and manages undo itself and DBAs don’t have to worry about its management. Oracle attempts to assign one undo segment to each transaction. When it cannot, it creates and assigns additional undo segments. Multiple transactions can write to one undo segment if space in undo tablespace is depleted which can cause contention. In order to avoid this situation add more space in undo tablespace. Later this article will provide scripts to monitor fragmentation in UNDO tablespace and method to overcome it.

From Oracle documentation

The following is a summary of the initialization parameters for automatic undo management mode:

Initialization Parameter Description
UNDO_MANAGEMENT If AUTO, use automatic undo management mode. If MANUAL, use manual undo management mode. The default is MANUAL.


UNDO_TABLESPACE An optional dynamic parameter specifying the name of an undo tablespace to use. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.

UNDO_RETENTION A dynamic parameter specifying the minimum length of time to retain undo. The default is 900 seconds. The setting of this parameter should take into account any flashback requirements of the system.


TYPES OF AUTOMATIC UNDO MANAGEMENT

When oracle introduced UNDO management it was based on segment management. Segment management is expensive on CPU, memory and IO so oracle add a new feature in later version of oracle 10g to do undo management in memory and called it in-memory Undo (IMU).

a) IN-MEMORY UNDO

Main benefit of IMU is that as there is no block level change of UNDO segment involves here so IMU will not generate any redo entry. Hence IMU improve the Undo Header contention and undo block contention by doing UNDO management in memory called IMU Node. Rather than writing a change to undo buffer ,undo is written to IMU Node. On the other hand memory structure require latches to maintain serial execution so make sure that enough IMU latches are created by changing processes parameter. Oracle use ‘In memory Undo Latch’ to access IMU structures in shared pool. So if you have high waits on this latch then you can increase the number of latches by increasing processes parameter or switch in-memory undo by setting in_memory_undo to false.

The following is a summary of the initialization parameters for automatic undo management mode:

Initialization Parameter Description

_in_memory_undo Default is TRUE and enabled. To disable it change parameter to FALSE.

_imu_pools Default is 3 on some system. This sets the number of IMU pools. It is not related to memory allocation for IMU.

_recursuve_imu_transactions This enables Oracle’s own SQL to use IMU. Default is FALSE.

_db_writer_flush_imu Allows Oracle the freedom to artificially age a transaction for increased automatic cache management. Default is

Initialization Parameter Description TRUE


There is no parameter that allow us to change memory allocation for IMU Node but changing shared_pool_size parameter can help adjusting the IMU memory allocation.

To findout how much memory is allocated run the command

On  UAT database

SQL> select * from v$sgastat where name like 'KTI-UNDO';


POOL NAME BYTES
------------ -------------------------- ----------
shared pool KTI-UNDO 8522272

SQL> select
2 n.name statistic,
3 v.value statvalue,
4 v.statistic# statnum
5 from
6 v$statname n,
7 v$sysstat v
8 where
9 n.statistic# = v.statistic# and
10 n.name like '%commit%'
11 order by
12 1 asc
13 /

Statistic Value Stat #
------------------------------------------------------- ---------------- ------
IMU commits 987,170 267



WARNING : Understand before taking action

As mentioned earlier that huge wait on “in memory Undo Latch “ and high amount of cpu usage shows that you have contention latch contention with IMU. As they are very CPU hungry if waiting for latch so you would like to resolve this issue as early as possible.

There are few reasons that you might have contention with IMU

* Few latches than actually require
* Not enough memory allocated for IMU Node. Check v$latch_children view.
* Not enough CPU core available i.e. if your system is already suffering from high CPU utilisation.

Few suggestions to try in these cases

* Increase the number of processes parameter so that it you can increase number of latches. Be careful and understand the other impact of increasing processes parameter.

* Increase the shared_pool_size. Again also understand the impact of increasing shared_poo_size on over database performance.

* Increase the number of CPU core if possible.

* Last resort is to disable the IMU and force oracle to use segment management by changing _in_memory_undo to FALSE.

b) SEGMENT BASED UNDO

Segment based Undo management is same as any table or index segment management and normally costly than IMU but on the other hand Oracle has more experience in handling segment based undo management than IMU. IMU is released in later version of oracle 10g.

Oracle also provides a tool cal UNDO advisor which checks the information in AWR and advice and helps you on setting up undo environment. Sample example of Undo advisor from oracle documentation is mentioned below

DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.execute_task(tname);
end;
/

Where start_snapshot is 1 and end_snapshot is 2. This information is also available in DBA_ADVISOR_* data dictionary views.

Oracle document further discusses the formula of calculating undo space requirement.

UndoSpace = UR * UPS + overhead
where:
UndoSpace is the number of undo blocks
UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
UPS is undo blocks for each second
overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)
As an example, if UNDO_RETENTION is set to 2 hours, and the transaction rate (UPS) is 200 undo blocks for each second, with a 4K block size, the required undo space is computed as follows:

(2 * 3600 * 200 * 4K) = 5.8GBs
Such computation can be performed by using information in the V$UNDOSTAT view. In the steady state, you can query the view to obtain the transaction rate. The overhead figure can also be obtained from the view.

DEFRAGMENT UNDO TABLESPACE

I have already touched this topic in other white paper “ Free Space Fragmentation by Tablespace” but I feel that it is appropriate to mention it here again.

As oracle create different size segments in UNDO tablespace normally and also extend and shrink according to the need of system, this cause fragmentation of UNDO tablespace. Oracle manages defragmentation of UNDO tablespace by SMON processes but if system is highly transactional then it is unlikely that SMON will have a chance to properly defragment UNDO tablespace. It is a common observation then whenever we receive error message of snapshot too old or run out of space from undo tablespace, DBAs add more space as resolution. I have done one test to defragment undo tablespace and happy to share the results.


On one UAT database , UNDO tablespace was fragmented by measuring FSFI and size of tablespace was 60Gb. I created secondary undo tablespace and switch new undo tablespace (UNDO2) as default undo tablespace. This allow oracle (SMON) to clear down all unused segments down on the first undo tablespace (UNDO). Also I found that we are now using 20Gb of space for undo compare to 60Gb this is 2/3rd of space back to the system. Once original undo tablespace (UNDO) is clear down then you can switch it back to default undo tablespace and drop the other undo tablespace (UNDO2).

To switch undo tablespace use
ALTER SYSTEM SET UNDO_TABLESPACE = ‘UNDO2’;


If you are using spfile then you can add spfile clause also. If you are using pfile then change it manually and bounce the database.

To monitor tablespace fragmentation
rem
rem Name: free_space.sql
rem
rem FUNCTION: Provide data on tablespace extent status
rem FUNCTION: Use 0 = OK , 1= Warning , 2= Critical
rem FUNCTION: includes fsfi from DBA Handbook
rem
SET FEED OFF
SET FLUSH OFF
SET VERIFY OFF
set pages 58 LINES 132

COLUMN tablespace HEADING Name FORMAT a25
COLUMN files HEADING '#Files' FORMAT 9,999
COLUMN pieces HEADING 'Frag' FORMAT 9,999
COLUMN free_bytes HEADING 'Free
Byte' FORMAT 9,999,999,999,999
COLUMN free_blocks HEADING 'Free
Blk' FORMAT 999,999,999
COLUMN largest_bytes HEADING 'Biggest
Bytes' FORMAT 9,999,999,999,999
COLUMN largest_blks HEADING 'Biggest
Blks' FORMAT 999,999,999
COLUMN ratio HEADING ' Percent' FORMAT 999.999
COLUMN average_fsfi HEADING 'Average
FSFI' FORMAT 999.999
COLUMN alert_level HEADING 'Alert
level' FORMAT 99
SELECT
tablespace,
files,
pieces,
free_bytes,
free_blocks,
largest_bytes,
largest_blks,
ratio,
average_fsfi,
case when average_fsfi < 30 then 2
when average_fsfi between 30 and 40 then 1
else 0
end alert_level
FROM
(SELECT
tablespace,
COUNT(*) files,
SUM(pieces) pieces,
SUM(free_bytes) free_bytes,
SUM(free_blocks) free_blocks,
SUM(largest_bytes) largest_bytes,
SUM(largest_blks) largest_blks,
SUM(largest_bytes)/sum(free_bytes)*100 ratio,
SUM(fsfi)/COUNT(*) average_fsfi
FROM
(SELECT tablespace_name tablespace, file_id, COUNT(*) pieces,
SUM(bytes) free_bytes, SUM(blocks) free_blocks,
MAX(bytes) largest_bytes, MAX(blocks) largest_blks,
SQRT(MAX(blocks)/SUM(blocks))*(100/SQRT(SQRT(COUNT(blocks)))) fsfi
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id, relative_fno)
GROUP BY tablespace)
/

CONCLUSION

UNDO tablespace tuning is one of the areas commonly missed by DBAs as most of the time tuning is fire fighting activity. This document will help you to analyse your undo tablespace management and tuning scientifically on the basis of some analysis. There is so much more to add on this topic that maybe I’ll add in later version of this document.

Key point is when you do tuning is to “Understand what you doing” and look at the bigger picture.

Supplemental logging for Golden Gate

I have submitted my post in another forum where discussion for supplemental logging was going on.
Here is the my reply but I am intending to write a separate blog on Golden Gate.



There are two level of supplemental logging mentioned in oracle documentation

1. Database level supplemental logging
2. Table level supplemental logging


WHY WE NEED SUPPLEMENTAL LOGGING?

One of the main reason to enable supplemental logging is to generate logs for update statement. As insert and delete involves change to whole row (either addition or deletion of complete row) and all columns change in it where as update can happen on a column of a row. Supplemental logging make sure that enough information is captured during this update that can be used by any method based on logminer technology.

Other reasons could be to capture chained rows etc.

MINIMUM LEVEL OF SUPPLEMENTAL LOGGING

Minimum level of supplemental logging that is required as per oracle documentation is

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

By default, Oracle only logs changed columns for update operations. Normally,this means that primary key columns are not logged during an update operation.However, Replicat requires the primary key columns in order to apply the update on the target system. The ADD TRANDATA command in GGSCI is used to cause Oracle to log primary key columns for all updates

For Golden gate replication minimum level of supplemental logging require is at primary key level to uniquely identify each row for replication purpose.

Golden gate command
ADD TRANDATA scott.DEPT

actually enable supplemental logging at table level on primary key and running this command in the background
SQL ALTER TABLE "SCOTT"."DEPT" ADD SUPPLEMENTAL LOG GROUP "GGS_DEPT_1668166" ("DEPT_ID") ALWAYS

I was initially not sure why we have to enable supplemental logging twice but now my understanding is that

1. From oracle 10.2 onwards minimum level of supplemental logging at database level is required before enabling supplemental logging at table level.


2. Golden gate require minimum primary key supplemental logging which is expensive if enabled at database level when only one schema or few tables are configured for replication.

Hence basic supplemental logging enable at database level and specific primary key level on table level via Golden gate.

If you enable only table level supplemental logging without database level then oracle will not capture all changes.

LogMiner is not Showing the DML Activity When Table-Level Supplemental Logging is Enabled [ID 760897.1]

 

same is the case with if you only enable database level supplemental logging then
Can I Turn On The Oracle Supplemental Log At The DB Level Only? [ID 970903.1]

It is a logical pre-requisite to enable database level supplemental logging before enabling table level supplemental logging. This is due to bug in some oracle versions. Above mentioned article shows that table level supplemental logging can be enabled without enabling at database level.

Free Space Fragmentation by Tablespace

DESCRIPTION
Tablespaces in Oracle are the logical storage unit as it is not physically exists like datafiles. In other words, it is a logical link between tables and indexes to datafiles as each tablespace must have at least one datafile. There is one to many relationship between tablespaces and datafiles so one tablespace can have more than one datafile but one to one relationship between a datafile and a tablespace i.e. One datafile can only belong to one tablespace.
Tablespaces gets fragmented if they are created as Dictionary managed tablespace or ASSM without using AUTO and UNIFORM extent option. Other reason of fragmentation is that even though tablespace is created properly with auto extent on and using uniform size but tables and indexes are created with storage parameters. Using storage parameters with tables and indexes overrides the tablespace storage settings and allow different size and number of extents in database.
Due to regular DML operation tablespaces gets fragmented in two ways. One is honeycomb fragmentation and second is Bubble fragmentation.

Honeycomb fragmentation occurs when more than one extent next to each other becomes empty so it is easier to fix this problem by coalescing space together. You can do it manually or leave it to SMON process to do this job automatically.
Bubble fragmentation is difficult to fix. Best option is to used locally managed tablespace and don’t use storage parameters with tables and indexes so that oracle manages extent allocation and deallocation by itself.
There is a famous model that employs an arbitrary scoring system to establish if a tablespace needs a free space rebuild. The system used is that defined by Loney in Oracle DBA Handbook by Oracle Press, ISBN 0-07-881182-1. This Free Space Fragmentation Index (FSFI) calculates the size of the largest extent as a
percentage of the total free space and also considers the number of extents in a tablespace. This index is not a monitor of the amount of free space available. It indicates the structure of the free space.
Over time, free space within a tablespace can become fragmented. Use this resource model to determine if any tablespace has a high free space fragmentation index. A fragmented tablespace can lack the contiguous free space needed to allocate new extents. Identifying and coalescing tablespaces that have become fragmented can enhance database performance.
The best-case response from this resource model is 100%. As the total number of extents increase for a tablespace, the FSFI rating drops accordingly. In general, a tablespace with sufficient free space and an FSFI rating of above 30 should not experience free space availability problems.

select TABLESPACE_NAME,
 SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS)))) Fsfi
from dba_free_space group
by tablespace_name order by 1;

In mathematical terms, this is:

           100(m/s)1/2                          ( m )
 FSFI = ----------------      =        100 √(--------)
            c1/4                                   ( s x √c )


where m = max(blocks)
           s = sum(blocks)
           c = count(blocks)




On the other hand if tablespace is locally managed tablespace with Segment management auto and uniform extents then there is no need to defragement a tablespace as all extents will be of same size and oracle use bitmap to manage each extent so it does not really matter much if the extent is at the beginning, end or in the middle of a tablespace from performance point of view.

DEFRAGMENTING UNDO TABLESPACE


It is difficult to defragment UNDO tablespace as it does not have any tables and indexes that we can be re build or shrink. Oracle SMON process normally defrags UNDO tablespace but in busy system it is become literally impossible. So best way to defrag UNDO tablespace is to create a secondary UNDO tablespace e.g UNDO2 and switch it as default UNDO tablespace and allow oracle to clear down extents in original UNDO tablespace. Once original UNDO is free then you can switch back default undo to UNDO tablespace and drop the new UNDO2 tablespace.

This method does not require any outage and completely safe from DBA point of view. I have just implemented it in UAT database where we have allocated around 57Gb of Undo tablespace
which gets fragmented overtime. FSFI was 14% which is now 40% percent so we have defrag 26% of space so far and notice that we are using 20Gb of space at the moment compare to 57Gb.



SCRIPTS


This script will create the mapping for a tablespace and provides information about

1) Owner of an objects
2) Name of Object
3) Starting block id
4) Number of blocks used
5) Amount of space in MB


-------------------------------------------------------------------------------------------------------------

UNDEF ENTER_TABLESPACE_NAME
set heading off
select 'Tablespace Fragmentation Report ' from dual;
set heading on
COLUMN value NEW_VALUE dbblksiz NOPRINT
COLUMN owner format a20
COLUMN segment_name format a20
SELECT value FROM v$parameter WHERE name='db_block_size';
select ' *** FREE SPACE ***' owner , ' ' segment_name, file_id ,
block_id , blocks blocks_cnt,(blocks*&dbblksiz)/(1024*1024) meg
from sys.dba_free_space
where tablespace_name = '&&ENTER_TABLESPACE_NAME'
union
select substr(owner,1,12), substr(segment_name,1,32), file_id, block_id,
blocks block_cnt,(blocks*&dbblksiz)/(1024*1024) meg
from sys.dba_extents
where tablespace_name = '&&ENTER_TABLESPACE_NAME'
order by 3,4
/
-------------------------------------------------------------------------------------------------------------
Script below will use FSFI as criteria to calculate the level of fragmentation. If FSFI is lower than 30% then it will use alert_level 2 (Critical) and if it is between 31-40 then alert_level is 1 and greater than 40% is OK.


-------------------------------------------------------------------------------------------------------------


rem
rem Name: free_space.sql
rem
rem FUNCTION: Provide data on tablespace extent status
rem FUNCTION: Use 0 = OK , 1= Warning , 2= Critical
rem FUNCTION: includes fsfi from DBA Handbook
rem
SET FEED OFF
SET FLUSH OFF
SET VERIFY OFF
set pages 58 LINES 132
COLUMN tablespace HEADING Name FORMAT a25
COLUMN files HEADING '#Files' FORMAT 9,999
COLUMN pieces HEADING 'Frag' FORMAT 9,999
COLUMN free_bytes HEADING 'Free
Byte' FORMAT 9,999,999,999,999
COLUMN free_blocks HEADING 'Free
Blk' FORMAT 999,999,999
COLUMN largest_bytes HEADING 'Biggest
Bytes' FORMAT 9,999,999,999,999
COLUMN largest_blks HEADING 'Biggest
Blks' FORMAT 999,999,999
COLUMN ratio HEADING ' Percent' FORMAT 999.999
COLUMN average_fsfi HEADING 'Average
FSFI' FORMAT 999.999
COLUMN alert_level HEADING 'Alert
level' FORMAT 99
SELECT
tablespace,
files,
pieces,
free_bytes,
free_blocks,
largest_bytes,
largest_blks,
ratio,
average_fsfi,
case when average_fsfi < 30 then 2
when average_fsfi between 30 and 40 then 1
else 0
end alert_level
FROM
(SELECT
tablespace,
COUNT(*) files,
SUM(pieces) pieces,
SUM(free_bytes) free_bytes,
SUM(free_blocks) free_blocks,
SUM(largest_bytes) largest_bytes,
SUM(largest_blks) largest_blks,
SUM(largest_bytes)/sum(free_bytes)*100 ratio,
SUM(fsfi)/COUNT(*) average_fsfi
FROM
(SELECT tablespace_name tablespace, file_id, COUNT(*) pieces,
SUM(bytes) free_bytes, SUM(blocks) free_blocks,
MAX(bytes) largest_bytes, MAX(blocks) largest_blks,

SQRT(MAX(blocks)/SUM(blocks))*(100/SQRT(SQRT(COUNT(blocks)))) fsfi
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id, relative_fno)
GROUP BY
tablespace)
/
 ------------------------------------------------------------------------------------------------------------
HOW TO FIND TABLES FOR DEFRAGMENTATION


The logical next question is that how to find out tables those are candidate defragmentation? If you have hundreds of table on a tablespace then you are not going to rebuild all of them to get space. First we need to calculate who the best candidates for defragmentation are.

Script below provides a list of tables that has more than 10 blocks allocated and using less than 30% of allocated space i.e. below HWM. There are two calculation of space usage has been done in this script. One is logical space calculation used by dbms_stats and other is physical space usage by using dba_segments.

This script also generates script to shrink the table by using table shrinking method which we will discuss later.

-------------------------------------------------------------------------------------------------------------
UNDEF ENTER_OWNER_NAME

UNDEF ENTER_TABLE_NAME
SET pages 56 lines 132 newpage 0 verify off echo off feedback off
SET serveroutput on
-- when the table is > 10 blocks and
-- total space usage up to the HWM is < 30% of the size of the HWM, we will consider the table
-- a candidate for shrinking.
-- In dba_tables, the "blocks" are calculated when you run dbms_stats, while in dba_segments, "blocks" is the actual number of blocks used by the object on disk.

BEGIN
DBMS_OUTPUT.put_line ('This report produces a list of tables that may benefit from compacting.');
DBMS_OUTPUT.put_line (CHR (13));
FOR space_usage IN
(SELECT /*+ FIRST_ROWS */
dba_tables.owner, dba_tables.table_name,
dba_tables.blocks blocks_below,
dba_segments.blocks total_blocks,
dba_tables.num_rows * dba_tables.avg_row_len total_data_size,
ROUND (( 100 * (dba_tables.num_rows * dba_tables.avg_row_len) / (GREATEST (dba_tables.blocks,1)* v$parameter.VALUE) ), 3 ) hwm_full,
ROUND (( 100 * (dba_tables.num_rows * dba_tables.avg_row_len) / (GREATEST(dba_segments.blocks,1) * v$parameter.VALUE ) ), 3 ) space_full
FROM dba_tables, v$parameter, dba_segments
WHERE dba_tables.owner NOT IN ('SYS', 'SYSTEM')
AND dba_tables.owner LIKE UPPER ('&&ENTER_OWNER_NAME')
AND dba_tables.table_name LIKE UPPER ('&&ENTER_TABLE_NAME')
AND dba_tables.owner = dba_segments.owner
AND dba_tables.table_name = dba_segments.segment_name
AND v$parameter.NAME = LOWER ('db_block_size')
AND ( 100 * (dba_tables.num_rows * dba_tables.avg_row_len)
/ (GREATEST (dba_segments.blocks, 1) * v$parameter.VALUE) ) < 30 AND dba_segments.blocks > 10)
LOOP
DBMS_OUTPUT.put_line ( 'Candidate table is ' space_usage.owner '.' space_usage.table_name );
DBMS_OUTPUT.put_line ( 'Which is using ' space_usage.space_full '% of allocated space. ' );
DBMS_OUTPUT.put_line ( 'Which is using ' space_usage.hwm_full '% of allocated space to the HWM. ' );
DBMS_OUTPUT.put_line ('You can use this script to compact the table:');
DBMS_OUTPUT.put_line ( 'alter table ' space_usage.owner '.' space_usage.table_name ' enable row movement; ');
DBMS_OUTPUT.put_line ( 'alter table ' space_usage.owner '.' space_usage.table_name ' shrink space cascade; ');
DBMS_OUTPUT.put_line (CHR (13));
END LOOP;
END;
/


-------------------------------------------------------------------------------------------------------------

ALTER TABLE MOVE OR ALTER TABLE SHRINK


In 9i, oracle introduced this option of alter table move command. Before that options available were export /import and truncate and re-insert data but both of them make table unavailable and are candidates of to human error.

ALTER TABLE  will cause that

* the rows will have new physical addresses
* High water mark will be adjusted.

But the drawback is that you need a full (exclusive) table lock and indexes will become unusable (due to old rowids) so need to be rebuilt.

Segment Shrinking was introduced from oracle 10g and is only available for tablespaces that are using ASSM (Automatic Segment Space Management).

ASSM has two types of HWM. One is HHWM (high HWM) and LHWM( low HWM). HHWM referred as a point above which all blocks are unformatted where LHWM represent a point below which all blocks are formatted.


Shrink command runs in two steps:

First step makes the segment compact by moving rows further down to free blocks at the beginning of the segment. This is basically DML operation of deleting from one blocks and reinserting into another. That is why you have to make sure that you have enough Temporary and UNDO space available for Shrink command.

Second step adjust the HWM. For this oracle needs an exclusive table lock for a short amount of time.


Benefits of Segment Shrinking


* will adjust HWM

* can be done online

* Will cause on row level locking and table level is at the end for a very small amount fo time.

* Indexes will be maintained and remain usable.

* can be made in one go

* Can be made into two steps.

Restriction on Segment Shrinking

* Only possible in tablespaces with ASSM

* Not allowed on UNDO segments, Temporary segments, clustered tables, tables with a column 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

INDEX MANAGEMENT DURING SHRINK COMMAND



In the first phase Oracle scans the segment from the back to find the position of the last row. Afterwards it scans 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 segment. Now Oracle scans the segment 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 part of SHRINK TABLE statement.




ACTUAL COMMAND

There are few ways to Shrink a table

a) Shrink and move HWM in one step

ALTER TABLE SHRINK SPACE;

b) Shrink and move HWM in one step for a table and all dependent objects

ALTER TABLE SHRINK SPACE CASCASDE;

c) Shrink and move HWM in two steps

ALTER TABLE SHRINK SPACE COMPACT; ---- Only moves the rows

ALTER TABLE SHRINK SPACE; --- now this will move HWM down


CONCLUSION

This document should provide necessary information about how to find fragmentation and further discusses a method to fix it in 10g database onwards. This does not conclude that SHRINK command is the best way to defragment a table as it depend case by case basis. In some case you may want to consider combination of both alter table move and alter table shrink command.