Script to Report Table Fragmentation (Doc ID 1019716.6)


Applies to:

Oracle Database - Enterprise Edition - Version 7.3.0.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Goal

 Provide a script to report table fragmentation.  Note: This script is still useful at 10.2 in that it gives a detailed report of block usage etc. This note makes no claims about how to remedy table fragmentation. The methods you can use to do this vary depending on whether ASSM is in use etc. A simpler note is Note 337651.1 How to find Objects Fragmented below High water mark.

Solution

 

 
Abstract
 
This script will report table fragmentation.  
 

Product Name, Product Version

Oracle Server, 7.3 to 9.2.0
PlatformPlatform Independent
Date Created16-Jul-1996
 
Instructions
Execution Environment:
     SQL, SQL*Plus

Access Privileges:
     Requires user with SELECT privileges on DBA_TABLES, DBA_SEGMENTS, DBA_EXTENTS
and ANALYZE privilege on table.

Usage:
     sqlplus <user>/<password> @<script_name>
	where <script_name> is one of the following scripts.

Instructions:
     Copy the scripts to different files and execute them from SQL*Plus.


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
 
Description
The first script, TFSLDTFR.SQL, gathers table fragmentation characteristics 
and inserts it into the newly created TFRAG table for subsequent reporting.  
 
TFSLDTFR performs a single "analyze table 't' compute statistics" command. 
You may want to change the compute to estimate for speed and usability. 
  
Currently the following characteristics are gathered:  
 
  - Owner of table.  
  - Name of table  
  - Number of data blocks with rows  
  - Number of table extents   
  - Number of chained rows  
  - Number of blocks that have ever contained a row (high water mark)  
  
Scripts two and three display table fragmentation information. The  
information is queried from the TFRAG table which is created during  
script one.
 
References
 
Script
=============
First Script: 
============= 
 
SET ECHO off 
REM NAME:   TFSLDTFR.SQL 
REM USAGE:"@path/tfsldtr table_owner table_name" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    ANALYZE on table, SELECT on DBA_TABLES, DBA_SEGMENTS, DBA_EXTENTS 
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Oracle US      
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Load the tfrag table with a given table's fragmentation stats. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM    N/A 
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
  
set feedback on  
set echo on  
set verify off
  
def towner=&1  
def tname=&2  
  
rem *******************************************************************  
rem * Goal: Analyze table to gather statistics  
rem *******************************************************************  
rem Specifically we are looking for:  
rem - blocks ABOVE the hwm, i.e. empty blocks (dba_tables.blocks)  
rem - average row length (dba_tables.blocks)  
  
analyze table &towner..&tname compute statistics  
/  
col val1 new_value blks_w_rows noprint  
col val2 new_value blks_above noprint  
select blocks val1,  
  empty_blocks val2  
from   dba_tables  
where  owner = upper('&towner') and  
    table_name = upper('&tname')  
/  
rem *******************************************************************  
rem * Goal: Get the number of blocks allocated to the segment  
rem *******************************************************************  
rem Specifically we are looking for:  
rem - allocated blocks dba_segments.blocks  
  
col val1 new_value alloc_blocks noprint  
select blocks val1  
from   dba_segments  
where owner        = upper('&towner') and  
      segment_name = upper('&tname')  
/  
  
rem *******************************************************************  
rem * Goal: Calculate the HWM  
rem *******************************************************************  
rem Specifically we are looking for:  
rem HWM = dba_segments.blocks - dba_tables.empty_blocks - 1  
rem HWM = allocated blocks - blocks above the hwn - 1  
col val1 new_value hwm noprint  
select &alloc_blocks-&blks_above-1 val1   
from dual  
/  
  
rem *******************************************************************  
rem * Goal: Get the Number of Fragmented Rows or Chained Frows (cr)  
rem *******************************************************************  
col val1 new_value cr noprint  
select chain_cnt val1   
from dba_tables  
where owner        = upper('&towner') and  
      table_name   = upper('&tname')  
/  
  
rem ***********************************************************  
rem * Goal :  Determine the Segment Fragmentation (sf)  
rem ***********************************************************  
col val1 new_val sf noprint  
select count(*) val1  
from   dba_extents  
where  owner        = upper('&towner') and  
       segment_name = upper('&tname')  
/  
rem ***********************************************************  
rem ***********************************************************  
rem * Load the TFRAG table with the just gathered information.  
rem ***********************************************************  
rem ***********************************************************  
rem *  
rem * Create the tfrag table if it does not exist.  
rem *  
drop table tfrag;

create table tfrag  
(  
  owner				char(30),  
  name				char(30),  
  hwm				number,  
  blks_w_rows			number,  
  avg_row_size			number,  
  possible_bytes_per_block	number,  
  no_frag_rows			number,  
  no_extents			number  
)  
/  
create unique index tfrag_u1 on tfrag (owner,name)  
/  
rem *  
rem * Delete and insert the new stats.  
rem *  
delete   
from  tfrag  
where owner='&towner' and  
      name='&tname'  
/  
insert into tfrag values   
('&towner','&tname',&hwm,&blks_w_rows,0,0,&cr,&sf)  
/  
commit;  
  
set echo off 
set verify on 



==============
Second Script: 
==============  

SET ECHO off 
REM NAME:    TFSTFRAG.SQL 
REM USAGE:"@path/tfstfrag" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM     SELECT on TFRAG 
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Oracle USA       
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    This script displays summary table  fragmentation  information.  The  
REM    information is queried from the tfrag table which  is  loaded via the  
REM    ldtfrag script.  Once the ldtfrag script has been run for a given  
REM    table, this report displays the  following information:  
REM 
REM      - Table owner  
REM      - Table name  
REM      - Segment fragmentation (number of extents)  
REM      - Number of table rows  
REM      - Table block fragmentation (1.0 bad, 0.0 good)  
REM      - Row fragmentation (chains)  
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM                          Table Fragmentation Characteristics 
REM 
REM    Owner    Table Name                               Exts Omega1  Chains 
REM    -------- ---------------------------------------- ---- ------ ------- 
REM    scott    s_emp                                       1  0.000       0 
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM  The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
  
col towner	heading 'Owner'			format a8 	trunc 
col tname	heading 'Table Name'		format a40 	trunc 
col exts	heading 'Exts'			format 999 	trunc 
col omega1	heading 'Omega1'		format 0.999 	trunc 
col chains	heading 'Chains'		format 99,990 	trunc 
  
ttitle - 
  center  'Table Fragmentation Characteristics'   skip 2 
  
select owner						towner,  
       name						tname,  
       no_extents					exts,  
  (hwm - blks_w_rows)/(hwm + 0.0001) 		omega1,  
       no_frag_rows					chains  
from   tfrag  
order by 1,2  
/  


 
===========================================
Sample Output from the tfstfrag.sql script: 
===========================================
 

Table Fragmentation Characteristics 
 
     
Owner    Table Name                 Exts Omega1  Chains 
-------- ---------------------------------------- ---- ------ ------- 
scott    s_emp1                         0.000       0 



=============
Third Script: 
============= 

SET ECHO off 
REM NAME:    TFSDTFRG.SQL 
REM USAGE:"@path/tfsdtfrg" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on TFRAG table created by TFSLDTFR.SQL 
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM     Oracle US      
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Detailed report of table fragmentation characteristics based on the  
REM    data in the tfrag table. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM                      Detailed Table Fragmentation Characteristics 
REM 
REM 
REM    Table Owner         : scott 
REM          Name          : s_emp 
REM    Extents             : 1  
REM    High water mark     : 1  
REM    Blocks with rows    : 1 
REM    Block frag: Omega1  : 0  
REM    Migrated rows       : 0 
REM  
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
 
col towner	format a70  
col tname	format a70  
col exts	format 999  
col omega1	format 90.9999  
col chains	format 99,990  
col rpb		format 999  
col hwm		format 9,999,999  
col bwr		format 9,999,999  
  
ttitle - 
  center  'Detailed Table Fragmentation Characteristics'  skip 2  
  
set heading off  
  
select 'Table Owner         : '||owner		towner,  
       '      Name          : '||name		tname,  
       'Extents             : '||no_extents		exts,  
       'High water mark     : '||hwm			hwm,  
       'Blocks with rows    : '||blks_w_rows		bwr,  
       'Block frag: Omega1  : '||(hwm - blks_w_rows)/(hwm + 0.0001) omega1,  
       'Migrated rows       : '||no_frag_rows		chains  
from   tfrag  
order by 1,2  
/  
  
set heading on  
 


===========================================
Sample Output from the tfsdtfrg.sql script: 
===========================================

 
 Detailed Table Fragmentation Characteristics 
 
 
Table Owner : scott 
 Name : s_emp 
Extents : 1 
High water mark : 1 
Blocks with rows : 1 
Block frag: Omega1 : 0 
Migrated rows : 0
 
 
Disclaimer
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.
 
Limitation of Liability
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.
 

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more