The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Tablespace Fragmentation
Contrary to popular opinion, tables with
noncontiguous extents do not cause performance problems. It is only
the row fragmentation that sometimes accompanies discontiguous
extents that negatively affects performance. In some studies, a
table with discontiguous extents (and no row fragmentation) actually
performed faster than a table that was in a single extent. However,
it is often useful to display the amount of free space within the
tablespace, and the location of the free space pieces within the
tablespace. Listing 8.15 shows two useful script to display free
space within a tablespace. The free_space.sql script needs only to
be run once to create a view that is used by the tsfree.sql script
which generates the report.
Listing 8.15 free_space.sql and tsfree.sql are used together to get
a detail tablespace report
Rem free_space.sql
rem run this script first, to
create the free_space view;
drOP VIEW SYS.FREE_SPACE;
CREATE VIEW SYS.FREE_SPACE AS
SELECT TABLESPACE_NAME TABLESPACE,
FILE_ID,
COUNT(*) PIECES,
SUM(BYTES) FREE_BYTES,
SUM(BLOCKS) FREE_BLOCKS,
MAX(BYTES) LARGEST_BYTES,
MAX(BLOCKS) LARGEST_BLKS
FROM SYS.Remote DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
This is the next script:
rem tsfree.sql - Shows all free
space within tablespaces.
Prompt be sure that you have run free_space.sql prior to this script
clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;
column temp_col new_value
spool_file noprint;
This is an excerpt from "High Performance
Data Warehousing".
