OraFAQ Forum: SQL & PL/SQL » Removing Table Fragmentation

Home » SQL & PL/SQL » SQL & PL/SQL » Removing Table Fragmentation (Oracle 11g , Windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Removing Table Fragmentation [message #562784]

Sun, 05 August 2012 04:06

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

To remove fragmentation which is the best method.

First one :
-----------------

1) Created a backup table from the Fragmented table (This table is a partitioned one).
2) Analyzed this table.
3) DROP the Fragmented table
4) Inserted the backuped up data from backup table to the Re-created table.
5) Analyze this table.

Second method
-------------------

1) Create a backup table newly, with PCTFREE =0
2) Inserted the data from Fragmented table ( This is a partitioned table) to backup table
3) Analyzed this table.
4) Truncate Fragmented table
4) Did Exchange partition of Fragmented table with Backup table.

The second method is not found to be removing the Fragmentation. Before the fragmentation was 28% after Second method the fragmentation is still the same.

While the first method the fragmentation reduced to 16%.

Query used to find Fragmentation.


select table_name,
round((blocks*8),2) "table size kb", 
round((num_rows*avg_row_len/1024),2) "actual data in table kb", 
round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)  "wasted space kb",
((round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"  
 from dba_tables
where table_name like 'TABLE_NAME%'
                and owner = 'OWNER'
  and blocks != 0
  order by 4 desc

Report message to a moderator

Re: Removing Table Fragmentation [message #562785 is a reply to message #562784]

Sun, 05 August 2012 04:08

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

alter table ... shrink space;

Regards
Michel

Report message to a moderator

Re: Removing Table Fragmentation [message #562786 is a reply to message #562785]

Sun, 05 August 2012 04:17

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Have tried the Alter table ...Shrink space as well.

After that the query for finding Fragmentation shows the same percentage Fragmentation.

Report message to a moderator

Re: Removing Table Fragmentation [message #562789 is a reply to message #562786]

Sun, 05 August 2012 05:21

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Where did you get fragmentation calculation query? How did you come up with things like round((blocks*8),2) "table size kb"? Column blocks in dba_tables is number of blocks (populated only if you collect statistics) which is obviously a whole number. So what is logical meaning of using round? And your calculations do not assount for PCTFREE.

SY.

[Updated on: Sun, 05 August 2012 05:23]

Report message to a moderator

Re: Removing Table Fragmentation [message #562790 is a reply to message #562786]

Sun, 05 August 2012 06:03

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

After that the query for finding Fragmentation shows the same percentage Fragmentation.

So your query is wrong.
Did you think about this point?
Do you understand the query you use?

Regards
Michel

Report message to a moderator

Re: Removing Table Fragmentation [message #562792 is a reply to message #562789]

Sun, 05 August 2012 06:15

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

And example showing SHRINK SPACE works. Table definition:

SQL> select  *
  2    from  v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select dbms_metadata.get_ddl('TABLE','CALL_TBL') from dual
  2  /

DBMS_METADATA.GET_DDL('TABLE','CALL_TBL')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."CALL_TBL"
   (    "ID" NUMBER(12,6),
        "V1" VARCHAR2(10 CHAR),
        "DATA" VARCHAR2(100 CHAR),
         CONSTRAINT "PK_CALL_TBL" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(
  BUFFER_POOL DEFAULT) LOCAL
 (PARTITION "CALL_PARTITION"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" )  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "CALL_PARTITION"  VALUES LESS THAN (MAXVALUE)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS )  ENABLE ROW MOVEMENT



SQL>

Now:

SQL> exec dbms_stats.gather_table_stats('SCOTT','CALL_TBL');

PL/SQL procedure successfully completed.

SQL> select table_name,
  2  round((blocks*8),2) "table size kb", 
  3  round((num_rows*avg_row_len/1024),2) "actual data in table kb", 
  4  round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)  "wasted space kb",
  5  ((round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"  
  6   from dba_tables
  7  where table_name like 'CALL_TBL'
  8                  and owner = 'SCOTT'
  9    and blocks != 0
 10    order by 4 desc
 11  /

TABLE_NAME                     table size kb actual data in table kb wasted space kb percent fragmented
------------------------------ ------------- ----------------------- --------------- ------------------
CALL_TBL                               72616                57128.91        15487.09         21.3273796

SQL> alter table call_tbl enable row movement;

Table altered.

SQL> alter table call_tbl shrink space;

Table altered.

SQL> exec dbms_stats.gather_table_stats('SCOTT','CALL_TBL');

PL/SQL procedure successfully completed.

SQL> select table_name,
  2  round((blocks*8),2) "table size kb", 
  3  round((num_rows*avg_row_len/1024),2) "actual data in table kb", 
  4  round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)  "wasted space kb",
  5  ((round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"  
  6   from dba_tables
  7  where table_name like 'CALL_TBL'
  8                  and owner = 'SCOTT'
  9    and blocks != 0
 10    order by 4 desc
 11  /

TABLE_NAME                     table size kb actual data in table kb wasted space kb percent fragmented
------------------------------ ------------- ----------------------- --------------- ------------------
CALL_TBL                               65536                55587.17         9948.83         15.1807098

SQL>

And, in fact, SHRINK SPACE works, in some cases, better than exp/imp:

SQL> host exp scott/tiger file=c:\temp\call_tbl.dmp log=c:\temp\call_tbl.log tables=call_tbl

SQL> drop table call_tbl purge
  2  /

Table dropped.

SQL> host imp scott/tiger file=c:\temp\call_tbl.dmp log=c:\temp\call_tbl_imp.log full=y

SQL> exec dbms_stats.gather_table_stats('SCOTT','CALL_TBL');

PL/SQL procedure successfully completed.

SQL> select table_name,
  2  round((blocks*8),2) "table size kb", 
  3  round((num_rows*avg_row_len/1024),2) "actual data in table kb", 
  4  round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)  "wasted space kb",
  5  ((round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"  
  6   from dba_tables
  7  where table_name like 'CALL_TBL'
  8                  and owner = 'SCOTT'
  9    and blocks != 0
 10    order by 4 desc
 11  /

TABLE_NAME                     table size kb actual data in table kb wasted space kb percent fragmented
------------------------------ ------------- ----------------------- --------------- ------------------
CALL_TBL                               66608                55127.01        11480.99         17.2366533

SQL> alter table call_tbl shrink space;

Table altered.

SQL> exec dbms_stats.gather_table_stats('SCOTT','CALL_TBL');

PL/SQL procedure successfully completed.

SQL> select table_name,
  2  round((blocks*8),2) "table size kb", 
  3  round((num_rows*avg_row_len/1024),2) "actual data in table kb", 
  4  round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)  "wasted space kb",
  5  ((round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"  
  6   from dba_tables
  7  where table_name like 'CALL_TBL'
  8                  and owner = 'SCOTT'
  9    and blocks != 0
 10    order by 4 desc
 11  /

TABLE_NAME                     table size kb actual data in table kb wasted space kb percent fragmented
------------------------------ ------------- ----------------------- --------------- ------------------
CALL_TBL                               65544                55120.38        10423.62         15.9032406

SQL>

SY.

Report message to a moderator

Re: Removing Table Fragmentation [message #562895 is a reply to message #562792]

Mon, 06 August 2012 12:08

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi ,

Are you saying the above query is wrong. But you have used the same query to find fragmentation is'nt it.
I got this query posted in one of the oracle forums where it was mentioned query to find Fragmentation.

Please let me know if this query is wrong.

Thanks,

Nirmal.

Report message to a moderator

Re: Removing Table Fragmentation [message #562899 is a reply to message #562895]

Mon, 06 August 2012 12:27

John Watson
Messages: 8981
Registered: January 2010
Location: Global Village

Senior Member

The query is wrong. "Fragmentation" refers to whether the segment is distributed into several discrete areas of space. To find out if that is the case, query DBA_EXTENTS which will have one row for every separate bit of the segment. Your query seems to be trying to determine of the segment is bigger than necessary, which is nothing to do with fragmentation.

Report message to a moderator

Re: Removing Table Fragmentation [message #562900 is a reply to message #562895]

Mon, 06 August 2012 12:34

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

ninan wrote on Mon, 06 August 2012 13:08

But you have used the same query to find fragmentation is'nt it.

Your query simply shows the ratio between used and total space, not fragmentation. And I provided example showing that such ratio decreases when you use SHRINK SPACE, while you said it didn't shrink it for you.

SY.

[Updated on: Mon, 06 August 2012 12:36]

Report message to a moderator

Re: Removing Table Fragmentation [message #562901 is a reply to message #562895]

Mon, 06 August 2012 12:52

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ninan wrote on Mon, 06 August 2012 19:08

...
Please let me know if this query is wrong.
...

Michel Cadot wrote on Sun, 05 August 2012 13:03

...So your query is wrong.
Did you think about this point?
Do you understand the query you use?
...

Do NOT use something you do not understand.
First think about it again and again.

Regards
Michel

Report message to a moderator

Previous Topic:	conditional predicates is not working with MERGE
Next Topic:	Regarding inserting CSV files in oracle database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 06 16:53:21 CDT 2025