Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Free space fragmented

Re: Free space fragmented

From: Mark Powell <Mark.Powell_at_eds.com>
Date: 23 Jun 1998 14:19:12 GMT
Message-ID: <01bd9eb1$b9136d20$a12c6394@J00679271.ddc.eds.com>


>> Roy Varghese posted request for help with index fragmentation and Saar
replied
mentioning minextents to pre-allocate space to index and setting pctincrease to
zero to limit fragmentation caused by unequal sized extents. Roy re-posted with request for information on odd sizes of existing extents and asked about eliminating the existing fragmentation (Ver is 7.2)<<

Roy, the only way you are going to be able to get rid of the existing fragmentation in the tablespace is to drop and re-create some of the indexes already fragmented. You can union sys.dba_extents to sys.dba_free_space to find the indexes that will give you your most gain in the least amount of time (Script posted below). To force coalescing of contiguous extents in version 7.2 use the following command: alter session set events 'immediate trace name coalesce level ##'; where ## is the file number of the Oracle data file that makes up the tablespace. See script below.

About your odd size extents, two points, 1) even though the pctincrease for the indexes is set to zero now it may not have been set to zero over the entire life of the index so that may explain some of your odd size extents, and 2) Oracle rounds extent allocations to even multiples of 5 extents and when allocating if the number of Oracle blocks left in a free extent is under 5 when finding space to allocate they are added on the allocation. Example, the request is for 7 Oracle blocks. Oracle rounds this to 10 blocks and the free extent is 13 blocks large so Oracle gives all 13 blocks to the index since the three remaining blocks would leave a free extent under 5 blocks in size. The Concepts manual does a better job of explaining this. I hope you find the two attached scripts useful.

set echo off
rem
rem file: tblspc_coal.sql
rem
rem Cause smon to coalesce all the files that make up a tablespace rem
rem 10/17/1996 s3537 m d powell new script rem
define tblspc_nm = &tblspc_name
set heading off
set feedback off
set termout off
set verify off
set pagesize 0
spool /tmp/tblspc_coal.sql

select 'alter session set events ''immediate trace name coalesce level '||

        file_id||''';'
  from sys.dba_data_files
 where tablespace_name like upper('%&tblspc_nm%') /
spool off
set heading on

Here is the tablespace mapping script:
rem
rem filename: MapTblSpc.sql
rem SQL*Plus script to map the contents of a tablespace. m d powell rem
set pagesize 60
set linesize 80
column owner format A10
column object format A30
set verify off
spool $HOME/MapTblSpc
undefine TS
select 'Free Space' owner, ' ' object, file_id, block_id, blocks from sys.dba_free_space
where tablespace_name = upper('&&TS')
union
select substr(owner,1,20), substr(segment_name,1,32), file_id, block_id,

       blocks
from sys.dba_extents
where tablespace_name = upper('&&TS')
order by 3, 4;
spool off
undefine TS Received on Tue Jun 23 1998 - 09:19:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US