OraFAQ Forum: Server Administration » Will a Bigger Next Extent Results In Smaller Fragmentation

Home » RDBMS Server » Server Administration » Will a Bigger Next Extent Results In Smaller Fragmentation (11.2.0.4 SE, SLES 11 SP 3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Will a Bigger Next Extent Results In Smaller Fragmentation [message #647605]

Wed, 03 February 2016 21:42

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

smaller_next

CREATE TABLESPACE smaller_next DATAFILE '/u02/app/oracle2/oradata/ORCL/smaller_next_01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M extent management local segment space management auto;

bigger_next


CREATE TABLESPACE bigger_next DATAFILE '/u02/app/oracle2/oradata/ORCL/bigger_next_01.dbf' SIZE 1M AUTOEXTEND ON NEXT 10M extent management local segment space management auto;

with the above create tablespace sql, which tablespace will result in a larger fragmentation?

I did the following test:

create 4 tables namely


CREATE TABLE t1_smaller_next (
  id NUMBER,
  description VARCHAR2(1000),
  CONSTRAINT t1_smaller_next_pk PRIMARY KEY (id)
)tablespace smaller_next;

CREATE TABLE t2_smaller_next (
  id NUMBER,
  description VARCHAR2(1000),
  CONSTRAINT t2_smaller_next_pk PRIMARY KEY (id)
)tablespace smaller_next;

CREATE TABLE t1_bigger_next (
  id NUMBER,
  description VARCHAR2(1000),
  CONSTRAINT t1_bigger_next_pk PRIMARY KEY (id)
)tablespace bigger_next;

CREATE TABLE t2_bigger_next (
  id NUMBER,
  description VARCHAR2(1000),
  CONSTRAINT t2_bigger_next_pk PRIMARY KEY (id)
)tablespace bigger_next;

inserting data


INSERT /*+append*/ INTO t1_smaller_next
SELECT rownum, RPAD('x', 1000, 'x')
FROM dual
CONNECT BY level <= 10000;
COMMIT;

INSERT /*+append*/ INTO t2_smaller_next
SELECT rownum, RPAD('x', 1000, 'x')
FROM dual
CONNECT BY level <= 10000;
COMMIT;

INSERT /*+append*/ INTO t1_bigger_next
SELECT rownum, RPAD('x', 1000, 'x')
FROM dual
CONNECT BY level <= 10000;
COMMIT;

INSERT /*+append*/ INTO t2_bigger_next
SELECT rownum, RPAD('x', 1000, 'x')
FROM dual
CONNECT BY level <= 10000;
COMMIT;

before and after truncating t1_smaller_next and t1_bigger_next, run the following script

SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SET TRIMOUT ON
SET VERIFY OFF

DECLARE
  l_tablespace_name VARCHAR2(30) := UPPER('&1');
  l_file_id         VARCHAR2(30) := UPPER('&2');

  CURSOR c_extents IS
    SELECT owner,
           segment_name,
           file_id,
           block_id AS start_block,
           block_id + blocks - 1 AS end_block
    FROM   dba_extents
    WHERE  tablespace_name = l_tablespace_name
    AND    file_id = DECODE(l_file_id, 'ALL', file_id, TO_NUMBER(l_file_id))
    ORDER BY file_id, block_id;

  l_block_size     NUMBER  := 0;
  l_last_file_id   NUMBER  := 0;
  l_last_block_id  NUMBER  := 0;
  l_gaps_only      BOOLEAN := TRUE;
  l_total_blocks   NUMBER  := 0;
BEGIN
  SELECT block_size
  INTO   l_block_size
  FROM   dba_tablespaces
  WHERE  tablespace_name = l_tablespace_name;

  DBMS_OUTPUT.PUT_LINE('Tablespace Block Size (bytes): ' || l_block_size);
  FOR cur_rec IN c_extents LOOP
    IF cur_rec.file_id != l_last_file_id THEN
      l_last_file_id  := cur_rec.file_id;
      l_last_block_id := cur_rec.start_block - 1;
    END IF;
    
    IF cur_rec.start_block > l_last_block_id + 1 THEN
      DBMS_OUTPUT.PUT_LINE('*** GAP *** (' || l_last_block_id || ' -> ' || cur_rec.start_block || ')' ||
        ' FileID=' || cur_rec.file_id ||
        ' Blocks=' || (cur_rec.start_block-l_last_block_id-1) || 
        ' Size(MB)=' || ROUND(((cur_rec.start_block-l_last_block_id-1) * l_block_size)/1024/1024,2)
      );
      l_total_blocks := l_total_blocks + cur_rec.start_block - l_last_block_id-1;
    END IF;
    l_last_block_id := cur_rec.end_block;
    IF NOT l_gaps_only THEN
      DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, ' ') ||
                           ' (' || cur_rec.start_block || ' -> ' || cur_rec.end_block || ')');
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total Gap Blocks: ' || l_total_blocks);
  DBMS_OUTPUT.PUT_LINE('Total Gap Space (MB): ' || ROUND((l_total_blocks * l_block_size)/1024/1024,2));
END;
/

PROMPT
SET FEEDBACK ON

SET ECHO OFF
SPOOL OFF

here's my outcome before truncating table t1_smaller_next


Enter value for 1: smaller_next
Enter value for 2: 10
Tablespace Block Size (bytes): 8192                                             
*** GAP *** (1719 -> 1792) FileID=10 Blocks=72 Size(MB)=.56                     
Total Gap Blocks: 72                                                            
Total Gap Space (MB): .56                                                       
SYS@ORCL>
SYS@ORCL>PROMPT

after truncating t1_smaller_next

Enter value for 1: smaller_next
Enter value for 2: 10
Tablespace Block Size (bytes): 8192                                             
*** GAP *** (39 -> 176) FileID=10 Blocks=136 Size(MB)=1.06                      
*** GAP *** (255 -> 1664) FileID=10 Blocks=1408 Size(MB)=11                     
*** GAP *** (1719 -> 1792) FileID=10 Blocks=72 Size(MB)=.56                     
Total Gap Blocks: 1616                                                          
Total Gap Space (MB): 12.63                                                     
SYS@ORCL>
SYS@ORCL>PROMPT

before truncating t1_bigger_next


Enter value for 1: bigger_next
Enter value for 2: 11
Tablespace Block Size (bytes): 8192                                             
*** GAP *** (1719 -> 1792) FileID=11 Blocks=72 Size(MB)=.56                     
Total Gap Blocks: 72                                                            
Total Gap Space (MB): .56                                                       
SYS@ORCL>

after truncating t1_bigger_next


Enter value for 1: bigger_next
Enter value for 2: 11
Tablespace Block Size (bytes): 8192                                             
*** GAP *** (39 -> 176) FileID=11 Blocks=136 Size(MB)=1.06                      
*** GAP *** (255 -> 1664) FileID=11 Blocks=1408 Size(MB)=11                     
*** GAP *** (1719 -> 1792) FileID=11 Blocks=72 Size(MB)=.56                     
Total Gap Blocks: 1616                                                          
Total Gap Space (MB): 12.63

If u look at the after results, it seems that there's no difference between a tablespace with lower next extent and one with a higher next extent after truncating the table.

maybe I was testing the wrong way, can someone enlighten me on how to verify the following fact:
Will a Bigger Next Extent Results In Smaller Fragmentation

Or if the fact is not true at all.

many thanks in advance!