EXTENTS Details

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

An extent is a contiguous set (side-by-side) of Oracle data blocks allocated to a segment within a tablespace.
The size of an extent is controlled by storage parameters used when you CREATE or ALTER the segment (INITIAL, NEXT and PCT_INCREASE) and tablespace parameters.

Extents are often blamed for performance problems; however, their impact on performance is minimal and can be completely avoided. In fact, careful use of extents can improve your response time by distributing your I/O operations across multiple devices. To understand the impact of extents on performance, you need to consider the two different methods of reading data from a table: by RowID and by full table scan.

Oracle's preferred method of reading records for OLTP applications is by the row's RowID value.
For example, an index range scan may generate a list of RowIDs that match a query's limiting condition.

Impact of Extent Sizes on Full Table Scans:

1. If the extents are properly sized, the number of extents has no impact on the number of reads required by table scans.
2. If the extents are not properly sized, the number and size of the extents can greatly increase the amount of work performed by the database during a full table scan.
Proper sizing of extents is a key factor in managing the performance of full table scans. To eliminate the potential impact of multiple extents on performance, you need to make sure that the size of each extent is a multiple of the number of blocks read during each multiblock read.
In many systems, 64KB or 128KB is read during each read. Therefore, size your extents.

-- Identify Indexes/Tables near to MAX EXTENTS

Step 1. Connect as System user to list the tables/indexes reaching to max extent

SELECT DS.OWNER, DS.SEGMENT_NAME, DS.SEGMENT_TYPE, DS.MAX_EXTENTS, DS.EXTENTS AS EXTENTS_FILLED
FROM DBA_SEGMENTS DS
WHERE DS.SEGMENT_TYPE IN ('TABLE','INDEX')
  AND DS.OWNER NOT IN ('SYS','SYSTEM')
  AND ( DS.MAX_EXTENTS - DS.EXTENTS ) < 50
ORDER BY 1,3

Step 2. What to do if a table reaches its MAXEXTENTS

If its EXTENTS parameter is nearing to the current MAX EXTENTS value you can increase it with the ALTER TABLE command,
For example:

ALTER TABLE <OWNER.TABLE_NAME> STORAGE (MAXEXTENTS UNLIMITED);
ALTER INDEX <OWNER.INDEX_NAME> STORAGE (MAXEXTENTS UNLIMITED);

-- Finding out table name to ALTER LOBS max extents
SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_LOBS WHERE SEGMENT_NAME = '&SEGMENT_NAME';

-- Modifying LOB Segments (EXAMPLES)
ALTER TABLE <OWNER.TABLE_NAME> MODIFY LOB (<LOBCOLUMN>) (STORAGE (MAXEXTENTS UNLIMITED));

-- Allocating a new extent (EXAMPLES)
ALTER INDEX <OWNER.INDEX_NAME> ALLOCATE EXTENT;
ALTER TABLE <OWNER.TABLE_NAME> ALLOCATE EXTENT;

-- To alter tablespace max extent clause one can use:
-- To resolve the Max extents issue for tablespace one can either "1) Add a data file 2) or, resize current data file 3) or, change the storage / extent configuration".
 ALTER TABLESPACE <tablespace_name> DEFAULT STORAGE (MAXEXTENTS n);
(OR)
ALTER TABLESPACE <tablespace_name> DEFAULT STORAGE (MAXEXTENTS UNLIMITED);

-- Identify all objects in the instance that do NOT have max extents set to “UNLIMITED”
-- Applicable to all database versions:
SET LINES 120

COL SEGMENT_NAME FORMAT A30
COL OWNER FORMAT A20
COL SEGMENT_TYPE FORMAT A20

SELECT S.SEGMENT_NAME, S.OWNER, S.SEGMENT_TYPE, S.MAX_EXTENTS
FROM DBA_SEGMENTS S , DBA_TABLESPACES T
WHERE S.TABLESPACE_NAME=T.TABLESPACE_NAME AND
      T.EXTENT_MANAGEMENT <> 'LOCAL' AND
      S.SEGMENT_TYPE NOT IN ('CACHE','DEFERRED ROLLBACK','SPACE HEADER') AND
      S.OWNER NOT IN ('SYS','SYSTEM') AND
      S.MAX_EXTENTS <> 2147483645
ORDER BY S.OWNER,S.SEGMENT_TYPE
/

-- Script to generate  "alter table" and "alter index" statements to set max extents to unlimited

SET LINES 120
SET HEADING OFF

SPOOL MAXEXTENT_FIX.SQL

SELECT
'ALTER '||S.SEGMENT_TYPE||' '||S.OWNER||'.'||S.SEGMENT_NAME||' STORAGE (MAXEXTENTS UNLIMITED);' "ALTER STATEMENT"
FROM DBA_SEGMENTS S , DBA_TABLESPACES T
WHERE S.TABLESPACE_NAME=T.TABLESPACE_NAME AND
      T.EXTENT_MANAGEMENT<>'LOCAL' AND
      S.SEGMENT_TYPE IN ('TABLE','INDEX') AND
      S.OWNER NOT IN ('SYS') AND
      S.MAX_EXTENTS<>2147483645
ORDER BY S.OWNER,S.SEGMENT_TYPE
/

SPOOL OFF

--
-- List tables with less than 10 extents.
--
SET PAGESIZE 60
SET LINESIZE 300

SELECT T.TABLE_NAME,
       COUNT(E.SEGMENT_NAME) EXTENTS,
       T.MAX_EXTENTS,
       T.NUM_ROWS "ROWS",
       TRUNC(T.INITIAL_EXTENT/1024) "INITIAL K",
       TRUNC(T.NEXT_EXTENT/1024) "NEXT K"
FROM   ALL_TABLES T,
       DBA_EXTENTS E
WHERE  E.SEGMENT_NAME = T.TABLE_NAME
AND    E.OWNER        = T.OWNER
AND    T.OWNER        = UPPER('&OWNER')
GROUP BY T.TABLE_NAME,
         T.MAX_EXTENTS,
         T.NUM_ROWS,
         T.INITIAL_EXTENT,
         T.NEXT_EXTENT
HAVING COUNT(E.SEGMENT_NAME) < 10
ORDER BY COUNT(E.SEGMENT_NAME) DESC
/

--
-- List the Number of Extents for all Indexes for a Given Table.  (All Tables if 'ALL' is entered for Table_name).
--
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF

SELECT I.INDEX_NAME,
       COUNT(E.SEGMENT_NAME) EXTENTS,
       I.MAX_EXTENTS,
       T.NUM_ROWS "ROWS",
       TRUNC(I.INITIAL_EXTENT/1024) "INITIAL K",
       TRUNC(I.NEXT_EXTENT/1024) "NEXT K",
       T.TABLE_NAME
FROM   ALL_TABLES T,
       ALL_INDEXES I,
       DBA_EXTENTS E
WHERE  I.TABLE_NAME   = T.TABLE_NAME
AND    I.OWNER        = T.OWNER
AND    E.SEGMENT_NAME = I.INDEX_NAME
AND    E.OWNER        = I.OWNER
AND    I.TABLE_NAME   = DECODE(UPPER('&&TABLE_NAME'),'ALL',I.TABLE_NAME,UPPER('&&TABLE_NAME'))
AND    I.OWNER        = UPPER('&&OWNER')
GROUP BY T.TABLE_NAME,
         I.INDEX_NAME,
         I.MAX_EXTENTS,
         T.NUM_ROWS,
         I.INITIAL_EXTENT,
         I.NEXT_EXTENT
HAVING   COUNT(E.SEGMENT_NAME) > 5
ORDER BY COUNT(E.SEGMENT_NAME) DESC
/