 |
|
Oracle Tablespace Reorganization
tips
Oracle Data Warehouse Tips by Burleson Consulting |
Tablespace Reorganization
Because they are dynamic, Oracle databases
will always fragment over time and may require a periodic clean-up.
In general, reorganization ensures that all tables and indexes do
not have row fragmentation, and that they reside in a single extent,
with all free space in a tablespace in a single, contiguous chunk.
Reorganizing a tablespace can be accomplished in several ways.
Rather than bring down the entire Oracle database to perform a full
export/import, there are some other options.
Let?s take a look at how a tablespace may
become fragmented. At initial load time, all Oracle tables within
the tablespace are contiguous--that is, only one chunk of free space
resides at the end of the tablespace. As tables extend and new
extents are added to the tablespace, the free space becomes smaller
but it still remains contiguous.
Basically, a table can fragment in two ways:
* A table extends
(without row chaining)--Contrary to popular belief, this is not a
problem and performance will not suffer.
* Rows fragment
within the tablespace (due to SQL UPDATES)--This causes a serious
performance problem, and the offending tables must be exported,
dropped, and re-imported.
Tablespace fragmentation occurs when some
?pockets? of free space exist within the tablespace. So, how do
these pockets of free space appear? If tables are DROPPED and
re-created, or if individual tables are exported and imported, space
that was once reserved for a table?s extent will now be vacant.
To see the fragmentation within a
tablespace, you can run the script shown in Listing 8.16.
Listing 8.16 tsfrag.sql shows a
tablespace map.
rem written by Don Burleson
set
linesize 132;
set pages 999;
rem set
feedback off;
rem set verify off;
rem set heading off;
rem set termout off;
break on
file_id skip page;
break on free skip 1;
compute sum of KB on free;
spool
tsfrag;
column
owner
format a10;
column segment_name format a10;
column tablespace_name format a14;
column file_id
format 99 heading ID;
column end
format 999999;
column KB
format 9999999;
column begin
format 999999;
column blocks
format 999999;
select
tablespace_name,
file_id,
owner,
segment_name,
block_id begin,
blocks,
block_id+blocks-1 end,
bytes/1024 KB,
'' free
see code depot
for full script
from sys.dba_extents
where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
union
select
tablespace_name,
file_id,
'' owner,
'' segment_name,
block_id begin,
blocks,
block_id+blocks+1 end,
bytes/1023 KB,
'F' free
from sys.dba_free_space
where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
order by 1, 2, 5
;
/
spool off;
!cat
tsfrag.lst
Here are the results of the tsfrag.sql
script.
TS_NAME
ID OWNER SEGMENT_NA BEGIN
BLOCKS END
KB F
-------------- --- ---------- ---------- ------- ------- -------
-------- -
DONALD3_DETAILS 15 DON
ZIP_UPS 2
5 6
20
DONALD3_DETAILS 15 DON
ACCC_TY 7
2 8
8
DONALD3_DETAILS 15 DON
BUS_UNIT 9
35 43
140
DONALD3_DETAILS
44 2
45 8 F
DONALD3_DETAILS 15 DON
PLANT 46
3 48
12
DONALD3_DETAILS
49 10 58
40 F
DONALD3_DETAILS 15 DON
DON_TABLES 59
4 62 16
DONALD3_DETAILS 15 DON
ZONE
63 2
64 8
-------- *
252 s
DONALD3_DETAILS 15
65 1216 1282
4869 F
-------- *
4869 s
In the tablespace fragmentation report we
see two discontiguous chunks of free space, as indicated by the ?F?
column on the far right-hand side of the report. Here, we see that
blocks 46-48 are free, as are blocks 49-58. There are other
tools that will provide this information in a graphical format such
as Oracle?s Performance Pack?s Tablespace Manager.
Oracle version 7.3 will automatically detect
and coalesce tablespaces--provided that all affected tablespaces
default storage clause has PCTINCREASE set to 1. The coalesce
mechanism for tablespace coalescing is the SMON process, which
periodically wakes up to coalesce free space. Between SMON
coalesces, any transaction that requires an extent that is larger
than any available free extent, will trigger a coalesce on the
tablespace to move all free space into a single chunk--hopefully
making room for the required extent.
Also in Oracle 7.3 is a new dictionary view,
DBA_FREE_SPACE_COALESCED, that provides details about the number of
extents, bytes, and blocks that have been coalesced in each
tablespace.
The following query will display coalesce
information:
SELECT
tablespace_name,
bytes_coalesced,
extents_coalesced,
percent_extents_coalesced,
blocks_coalesced,
percent_blocks_coalesced
FROM
sys.dba_free_space_coalesced
ORDER BY
tablespace_name;
To change all tablespaces? PCTINCREASE from
0 to 1 so that tables will automatically coalesce, run the script in
Listing 8.17.
Listing 8.17 coalesce.sql changes all
tablespaces with PCTINCREASE not equal to one.
rem written by Don Burleson
set
linesize 132;
set pagesize 999;
set feedback off;
set verify off;
set heading off;
set termout off;
spool
coalesce;
select
'alter tablespace '||
tablespace_name||
' storage ( pctincrease 1 );'
from dba_tablespaces
where
tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
and
pct_increase = 0;
spool off;
set
feedback on;
set verify on;
set heading on;
set termout on;
@coalesce.lst
MANUAL TABLESPACE COALESCING
If you detect that a single tablespace has
fragmented, you can quickly coalesce it with the following
procedures:
1. Alter session by
retrieving the tablespace number from sys.ts$:
select *
from sys.ts$;
2. In SQL*DBA, issue the
following command:
alter
session set events ?immediate trace name coalesce level &tsnum);
(where tsnum is the tablespace number from
step 1)
3. Manual coalesce; from
SQL*Plus enter:
a
lter
tablespace <xxxx> coalesce;
Using Oracle?s Read-Only Tablespaces
In a busy environment where many different
applications require access to a tablespace, it is sometimes
desirable to use the read-only tablespace feature of Oracle 7.3.
With read-only tablespaces, separate instance can be mapped to the
same tablespaces, each accessing the tablespace in read-only mode.
Of course, sharing a tablespace across Oracle instances increases
the risk that I/O against the shared tablespaces may become
excessive. As we can see in Figure 8.13, a read-only tablespace does
not have the same overhead as an updatable tablespace.
Figure 8.13 Oracle read-only
tablespaces.
This approach has several advantages:
* Buffer pool
isolation--The foremost advantage is the isolation of the buffer
pools for each instance that is accessing the tablespace. If user A
on instance A flushes his buffer by doing a full-table scan, user B
on instance B will still have the blocks needed in memory.
* Easy sharing of
table data--Read-only tablespaces offer an alternative to table
replication, and the update problems associated with replicated
tables. Since a read-only tablespace may only be defined as
updatable by one instance, updates are controlled at the system
level.
When we examine the backup and recovery of
an Oracle data warehouse, we generally find that the Oracle
warehouse runs in two modes. As we know, most Oracle data
warehouses are refreshed periodically, and we see different
configurations for Oracle during load processing when compared to
the configuration that is used for during query processing.
During query time, the database is generally optimized for queries,
and it is not uncommon to see Oracle?s recovery mechanisms disabled.
Oracle provides several sophisticated mechanisms for read
consistency and roll forward.
Read consistency is defined as the ability
of Oracle to ?fix? a query such that a 30 minute query will retrieve
data as it existed when the query started, even if information is
being changed while the query is running. Oracle implements
read consistency by referring to the online redo logs whenever a
requested data item has been changed during a query, and the ?old?
value for the data is read from the online redo log. Of
course, using the online redo logs adds to the overhead for oracle.
Another source of update overhead occurs as
Oracle archives the online redo logs. Redo logs store the
before and after images of all rows that have been added, modified
or deleted. In addition, the redo log contains log checkpoints
such as begin-job, end-job, commit, and abort checkpoints for every
transaction that runs under Oracle. As redo logs, fill, Oracle
directs the online log to be archived to a disk file.
Periodically, these archived redo logs are written to tape media in
case the Oracle database administrator need to use them for a
roll-forward.
These features are essential for maintaining
the integrity of an updated Oracle database, but they are not
necessary when your Oracle data warehouse is running in read-only
mode. As such, many Oracle DBA?s configure their data
warehouses to use Oracle?s read-only tablespaces. Read-only
tablespaces prohibit update operations against the tablespace.
The main purpose of read-only tablespaces is to eliminate the need
to perform backup and recovery operations against a static
tablespace. Therefore, their primary purpose for the data
warehouse is to segregate those parts of the warehouse that are
?historic?, and will never be altered from the more recent warehouse
information which may be updated.
Note: A read-only tablespace may only be
online in the database that created the tablespace. The
concept of Oracle read-only tablespaces has not yet been extended to
allow multiple instances to access a read-only tablespace (except,
of course, with Oracle parallel server).
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |