Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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:

alter 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.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational