Home » RDBMS Server » Server Administration » Will a Bigger Next Extent Results In Smaller Fragmentation (11.2.0.4 SE, SLES 11 SP 3)
Will a Bigger Next Extent Results In Smaller Fragmentation [message #647605] Wed, 03 February 2016 21:42 Go to next message
juniordbanewbie
Messages: 199
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!
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647606 is a reply to message #647605] Wed, 03 February 2016 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
What metric measures fragmentation?

What is fragmentation?

Why do care about fragmentation?

post URL to any Oracle documentation where fragmentation is discussed.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647607 is a reply to message #647606] Wed, 03 February 2016 23:28 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
used spaces are scattered every where in the file leaving free space between the used spaces.

Why do care about fragmentation?

my end user care about fragmentation,

he claim that the smaller the next extent the bigger the disk fragmentation

this is the closest i came across, https://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT004

https://docs.oracle.com/cd/E11882_01/server.112/e25494/schema.htm#ADMIN01401

[Updated on: Wed, 03 February 2016 23:55]

Report message to a moderator

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647611 is a reply to message #647607] Thu, 04 February 2016 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you saying that you store your data directly on internal disks and do not use SAN/NAS?

Quote:
my end user care about fragmentation,


So ask them why.

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647614 is a reply to message #647611] Thu, 04 February 2016 02:50 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
from https://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT3000

I realize that by setting the next extents to a small value, it won't lead to disk fragmentation.

but since segments can be made up of noncontinuous or continuous extents, it may be possible that having a small next extend could lead to segment fragmentation.


but based on my testing, it definitely does not conclude that having a small next extend can lead to segment fragmentation.

SYS@ORCL>SELECT file_id, bytes/1024/1024 size_in_mb, maxbytes/1024/1024 maxbytes_in_mb, increment_by FROM dba_data_files WHERE file_name='/u02/app/oracle2/oradata/ORCL/smaller_next_01.dbf';

   FILE_ID SIZE_IN_MB MAXBYTES_IN_MB INCREMENT_BY
---------- ---------- -------------- ------------
        10         26     32767.9844          128

SELECT file_id, bytes/1024/1024 size_in_mb, maxbytes/1024/1024 maxbytes_in_mb, increment_by FROM dba_data_files WHERE file_name='/u02/app/oracle2/oradata/ORCL/bigger_next_01.dbf';

FILE_ID SIZE_IN_MB MAXBYTES_IN_MB INCREMENT_BY
---------- ---------- -------------- ------------
        11         31     32767.9844         1280




for the bigger_next tablespace which has a bigger next extend of 100M, it does not have less segment fragmentation compare to smaller_next tablespace.


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                                                     




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                                                     




I mean if bigger_next tablespace will have fewer fragmentation the result would be like the following:


Enter value for 1: bigger_next
Enter value for 2: 11
Tablespace Block Size (bytes): 8192                                             
*** GAP *** (169-> 176) FileID=11 Blocks=1 Size(MB)=0.0625                
*** GAP *** (1667 -> 1664) FileID=11 Blocks=1408 Size(MB)=0.0625                     
*** GAP *** (1665 -> 1792) FileID=11 Blocks=72 Size(MB)=1                
Total Gap Blocks: 1616                                                          
Total Gap Space (MB): 12.63                                                     


again that's only in theory


or am i testing the wrong way?

again I have to ask user do they care about segment fragmentation now that it is clear that having a smaller extent don't lead to disk fragmentation.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647615 is a reply to message #647614] Thu, 04 February 2016 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't answer my simple question.

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647658 is a reply to message #647615] Thu, 04 February 2016 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
post SQL & results that shows the cost of fragmentation.

what is unit of measure of fragmentation?
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647665 is a reply to message #647615] Thu, 04 February 2016 18:05 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
Are you saying that you store your data directly on internal disks and do not use SAN/NAS?
=> I don't know. just being shot an email from my department head on customer complaining that when creating tablespace to be autoextend on next 2m it causes disk defragmentation.
=>it's my job to verify this fact with results.
=> however from my understanding and testing this is not true. It may cause segment fragmentation=> noncontinuous set of extents. Free extents between the used extents may or may not be used depending on whether there's space available in the file
=>now I have to prove the smaller next extents will cause segment fragmentation

https://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT1060

Quote:


The allocation algorithm depends on whether the tablespace is locally managed or dictionary-managed. In the locally managed case, the database searches the bitmap of a data file for adjacent free blocks. If the data file has insufficient space, then the database looks in another data file




post SQL & results that shows the cost of fragmentation.

what is unit of measure of fragmentation? =>bytes

this is my pl sql results for the tablespace with small next extents

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                                                     




this is my pl sql results for the tablespace with bigger next extents

Enter value for 1: bigger_next
Enter value for 2: 11
Tablespace Block Size (bytes): 8192                                             
*** GAP *** (169-> 176) FileID=11 Blocks=1 Size(MB)=0.0625                
*** GAP *** (1667 -> 1664) FileID=11 Blocks=1408 Size(MB)=0.0625                     
*** GAP *** (1665 -> 1792) FileID=11 Blocks=72 Size(MB)=1                
Total Gap Blocks: 1616                                                          
Total Gap Space (MB): 12.63    


it shows no difference between the two type of tablespace=> so am I testing wrongly?

This is how I test

create two types of tablespaces smaller_next, bigger_next
find the file_id of two tablespaces, the file id is required when running the extent map script

create two tables with smaller extent tablespace: t1_smaller_next, t2_smaller_next
create two tables with bigger extent tablespace: t1_bigger_next, t2_bigger_next
populate 4 tables with the same amount of data

view the extent map of smaller extent tablespace before truncating table t1_smaller_next=>results 1.1
view the extent map of smaller extent tablespace after truncating table t1_smaller_next=>results 1.2

view the extent map of bigger extent tablespace before truncating table t1_smaller_next=>results 2.1
view the extent map of bigger extent tablespace after truncating table t1_smaller_next=>=>results 2.2

compare the the result of 1.2 and 2.2

how do I show segment fragmentation difference between two types of tablespace with difference only in next extents? => am I doing the wrong way

many thanks in advance!
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647666 is a reply to message #647665] Thu, 04 February 2016 18:10 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
You need to decide what the problem is.

Is table "fragmented"?
Is tablespace "fragmented"?

What is the difference between free space & fragmented space?
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647670 is a reply to message #647666] Thu, 04 February 2016 19:43 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
I believe my customer is talking about tables fragmentation since there are emails flying around that talk about tables fragmentation.

What is the difference between free space & fragmented space?

free space is free space
fragmented space are free space that lies between the used extents.

Please correct me if I'm wrong.

many thanks

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647671 is a reply to message #647670] Thu, 04 February 2016 20:10 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
juniordbanewbie wrote on Thu, 04 February 2016 17:43
I believe my customer is talking about tables fragmentation since there are emails flying around that talk about tables fragmentation.

What is the difference between free space & fragmented space?

free space is free space
fragmented space are free space that lies between the used extents.

Please correct me if I'm wrong.

many thanks



NONE of you posted SQL deals with space within tables.

DATA1--------|FREE1-----|DATA2-------|FREE2-------|DATA3----------|FREE3---------------------------------|HWM

which of above is FRAGMENTED space?
which of above is FREE space?
Why is FRAGMENTED SPACE a problem & FREE space not a problem?

Realize that the only way to have fragmented space (with data before & after it) is to DELETE row(s).
If application never DELETE rows, then no fragmentation occurs.
Realize that Oracle can & does actually REUSE space that previously had contained data which got deleted.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647672 is a reply to message #647670] Thu, 04 February 2016 20:20 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
juniordbanewbie wrote on Thu, 04 February 2016 17:43
I believe my customer is talking about tables fragmentation since there are emails flying around that talk about tables fragmentation.

What is the difference between free space & fragmented space?

free space is free space
fragmented space are free space that lies between the used extents.

Please correct me if I'm wrong.

many thanks



NONE of you posted SQL deals with space within tables.

DATA1--------|FREE1-----|DATA2-------|FREE2-------|DATA3----------|FREE3---------------------------------|HWM

which of above is FRAGMENTED space?
which of above is FREE space?
Why is FRAGMENTED SPACE a problem & FREE space not a problem?

Realize that the only way to have fragmented space (with data before & after it) is to DELETE row(s).
If application never DELETE rows, then no fragmentation occurs.
Realize that Oracle can & does actually REUSE space that previously had contained data which got deleted.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647674 is a reply to message #647672] Fri, 05 February 2016 00:42 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
DATA1--------|FREE1-----|DATA2-------|FREE2-------|DATA3----------|FREE3---------------------------------|HWM
=>FREE1 and FREE2, Free3 is are free space
but FREE1 and FREE2 are also fragmented space

FREE and fragmented SPACE is a problem when it is too small for new data to be inserted

from https://docs.oracle.com/cd/E11882_01/server.112/e25494/schema.htm#ADMIN01401

Quote:

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

Objects with fragmented free space can result in much wasted space, and can impact database performance.



However I do not really agree with the portion of statement "can impact database performance".

Maybe someone could enlighten me how does fragmented space could impact database performance. As far I know, if I scan data across two noncontinuous extents in the same file, it won't degrade performance unless the two range are in different file.

many thanks

[Updated on: Fri, 05 February 2016 01:03]

Report message to a moderator

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647678 is a reply to message #647674] Fri, 05 February 2016 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

juniordbanewbie wrote on Fri, 05 February 2016 01:05
Are you saying that you store your data directly on internal disks and do not use SAN/NAS?
=> I don't know. just being shot an email from my department head on customer complaining that when creating tablespace


Your question is completely meaningless if you do not use direct disk access.

Quote:
Maybe someone could enlighten me how does fragmented space could impact database performance.


See my previous sentence.

Quote:
As far I know, if I scan data across two noncontinuous extents in the same file, it won't degrade performance unless the two range are in different file.


Who say that extents are made of continuous blocks in a file?
Who say that accessing different extents in different files are slower that accessing different extents or even a single extent in a single file?

The main and PRIME question you have to ask in on what you data are stored.
Without this, all you do is useless and just a waste of time.

[Updated on: Fri, 05 February 2016 01:19]

Report message to a moderator

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647679 is a reply to message #647674] Fri, 05 February 2016 01:39 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
I hesitated before contributing to this topic. But it is early in the morning, and I don't want to start work yet. So I'll try.

"Fragmentation" is generally a meaningless term in the Oracle environment. You must define what you mean by it. You appear to be using it to refer to a segment consisting of multiple extents, where the extents are not contiguous (I think you mean "contiguous" rather than "continuous") in terms of the Oracle block number.
This may result in two issues: one genuine, the other false.
THe genuine problem is that if the free extents between the used extents are different sizes, it may not be possible to re-use the space. To avoid this, make sure that all extents on the tablespace are the same size. Do NOT use different values for auto extension. Better still, create the tablespace with uniform extent size.
The false problem is that distributing a segment across multiple non-contiguous extents will impact adversely on performance. This is rarely true, for at least two reasons. Firstly, the fact that the Oracle blocks are consecutively numbered does not mean that they are physically adjacent. Oracle has no idea how the inode has distributed the file across the disc. One Oracle block may itself be distributed. Secondly, even if the blocks are physically adjacent, this has no benefit for any IO based on single block reads and writes. Singe block read/write is probably the majority of your IO.

You need to tell your client to stop worrying about things that have no meaning in the Oracle context.



Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647680 is a reply to message #647678] Fri, 05 February 2016 01:55 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
Your question is completely meaningless if you do not use direct disk access.
=>I guess I have to ask customer whether they are using direct disk access.
=>but then why must it be direct disk access?


Who say that extents are made of continuous blocks in a file?
=>reference https://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT3000

Quote:

An extent is a set of logically contiguous data blocks allocated for storing a specific type of information.

A single extent can never span data files


=>since single extents can never span data files, then an extent are made of contiguous data blocks in the same data file, right?

Who say that accessing different extents in different files are slower that accessing different extents or even a single extent in a single file?
=> I was wrong, so I presumed that it all boils down to the data I'm talking about

The main and PRIME question you have to ask in on what you data are stored.
Without this, all you do is useless and just a waste of time.
=>again I presumed that I've to look at the data before concluding that having a too small next extent will cause table fragmentation.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647681 is a reply to message #647680] Fri, 05 February 2016 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
=>but then why must it be direct disk access?


It must NOT, this is why all this topic is a waste of energy at 99.999%.

Quote:
Who say that extents are made of continuous blocks in a file?


They are contiguous from Oracle point of view NOT storage one.
Storage lies to those who use it.
And this does not matter.

Quote:
=>since single extents can never span data files, then an extent are made of contiguous data blocks in the same data file, right?


Again this depends on the storage and most of the time, wrong.
see John's answer.

[Updated on: Fri, 05 February 2016 02:09]

Report message to a moderator

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647682 is a reply to message #647680] Fri, 05 February 2016 02:25 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
This,Quote:
logically contiguous data blocks
does not mean "physically adjacent", it means "consecutively numbered". Oracle has no idea of the physcal placement. Unless you use ASM, of course - which is why you should use ASM.

[Updated on: Fri, 05 February 2016 02:25]

Report message to a moderator

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647726 is a reply to message #647682] Sat, 06 February 2016 07:53 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
Is there a way to prove that a lower next extent will result in greater table fragmentation?
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647727 is a reply to message #647726] Sat, 06 February 2016 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why?
Define table fragmentation.

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647728 is a reply to message #647727] Sat, 06 February 2016 09:13 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member
customer complain that it causes disk fragmentation. but since there's email circulating about table fragmenation then it should be table fragmentation.

from https://docs.oracle.com/cd/E11882_01/server.112/e25494/schema.htm#ADMIN11600

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.



=>empty space between used extents in the segment (tables) that is too small for new data to be inserted or updated.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647729 is a reply to message #647728] Sat, 06 February 2016 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
LMT table can not fragment.
Post SQL & results that prove me wrong.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647730 is a reply to message #647729] Sat, 06 February 2016 09:33 Go to previous messageGo to next message
juniordbanewbie
Messages: 199
Registered: April 2014
Senior Member

SYS@ORCL>DECLARE
  2    l_tablespace_name VARCHAR2(30) := UPPER('&1');
  3    l_file_id	 VARCHAR2(30) := UPPER('&2');
  4    l_segment_name VARCHAR2(30) := UPPER('&3');
  5  
  6    CURSOR c_extents IS
  7  	 SELECT owner,
  8  		segment_name,
  9  		file_id,
 10  		block_id AS start_block,
 11  		block_id + blocks - 1 AS end_block
 12  	 FROM	dba_extents
 13  	 WHERE	tablespace_name = l_tablespace_name
 14  	 AND	file_id = DECODE(l_file_id, 'ALL', file_id, TO_NUMBER(l_file_id))
 15  	     AND segment_name = l_segment_name
 16  	 ORDER BY file_id, block_id;
 17  
 18    l_block_size	NUMBER	:= 0;
 19    l_last_file_id	NUMBER	:= 0;
 20    l_last_block_id	NUMBER	:= 0;
 21    l_gaps_only	BOOLEAN := TRUE;
 22    l_total_blocks	NUMBER	:= 0;
 23  BEGIN
 24    SELECT block_size
 25    INTO   l_block_size
 26    FROM   dba_tablespaces
 27    WHERE  tablespace_name = l_tablespace_name;
 28  
 29    DBMS_OUTPUT.PUT_LINE('Tablespace Block Size (bytes): ' || l_block_size);
 30    FOR cur_rec IN c_extents LOOP
 31  	 IF cur_rec.file_id != l_last_file_id THEN
 32  	   l_last_file_id  := cur_rec.file_id;
 33  	   l_last_block_id := cur_rec.start_block - 1;
 34  	 END IF;
 35  
 36  	 IF cur_rec.start_block > l_last_block_id + 1 THEN
 37  	   DBMS_OUTPUT.PUT_LINE('*** GAP *** (' || l_last_block_id || ' -> ' || cur_rec.start_block || ')' ||
 38  	     ' FileID=' || cur_rec.file_id ||
 39  	     ' Blocks=' || (cur_rec.start_block-l_last_block_id-1) ||
 40  	     ' Size(MB)=' || ROUND(((cur_rec.start_block-l_last_block_id-1) * l_block_size)/1024/1024,2)
 41  	   );
 42  	   l_total_blocks := l_total_blocks + cur_rec.start_block - l_last_block_id-1;
 43  	 END IF;
 44  	 l_last_block_id := cur_rec.end_block;
 45  	 IF NOT l_gaps_only THEN
 46  	   DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, ' ') ||
 47  				' (' || cur_rec.start_block || ' -> ' || cur_rec.end_block || ')');
 48  	 END IF;
 49    END LOOP;
 50    DBMS_OUTPUT.PUT_LINE('Total Gap Blocks: ' || l_total_blocks);
 51    DBMS_OUTPUT.PUT_LINE('Total Gap Space (MB): ' || ROUND((l_total_blocks * l_block_size)/1024/1024,2));
 52  END;
 53  /
Enter value for 1: smaller_next
Enter value for 2: 10
Enter value for 3: t1_smaller_next
Tablespace Block Size (bytes): 8192                                             
*** GAP *** (15 -> 24) FileID=10 Blocks=8 Size(MB)=.06                          
*** GAP *** (143 -> 256) FileID=10 Blocks=112 Size(MB)=.88                      
Total Gap Blocks: 120                                                           
Total Gap Space (MB): .94                                                       
SYS@ORCL>



Maybe I was wrong, but again nobody knows every thing in this world. But I believe John have given me the best answer although I only realize he has already reply to me earlier.

thanks
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647731 is a reply to message #647728] Sat, 06 February 2016 09:37 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
If you really want to fix this problem (though whether it IS a problem is debatable) you need to move all the objects into a tablespace created with uniform extent size. When you tell your customer the number of hours you will bill him to do this, he may decide that the problem is not so important.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647733 is a reply to message #647730] Sat, 06 February 2016 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
code above is 100% oblivious to content of any table & is 100% useless to conclude anything about any table content.
Code above reports on tablespace free space which by definition is where no object (TABLE) resides so can NOT report any TABLE fragmentation.

SQL will ALWAYS give some answer.
The challenger is to ask the desired question.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647734 is a reply to message #647733] Sat, 06 February 2016 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
Realize & understand by your definition, every DELETE produces fragmentation.
Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647735 is a reply to message #647734] Sat, 06 February 2016 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
though whether it IS a problem is debatable


Well, given your previous answer it is not. Smile

Quote:
If you really want to fix this problem [...) you need to move all the objects into a tablespace created with uniform extent size.


Which should be of different sizes. Smile
It is not advisable to store a 64KB table in a 10-100MB extent as it is not advisable to store a 1GB table in 64KB extents.
The best is to use SYSTEM managed LMT and forget all stuff about fragmentation which in these days are ALL WRONG.

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647736 is a reply to message #647728] Sat, 06 February 2016 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
customer complain that it causes disk fragmentation.


And then?
Let the client complain.
How a client can complain about fragmentation?
Did you ever call your banker to tell him "Hey your tablespaces are too fragmented"?
Did you ever here someone doing so?

You have to remind one thing: fragmentation does not matter.
You want no more fragmentation, OK, create all tables with initial size of at least 1GB, if the table is bigger then create all tables with the size of the biggest you can have, then ther will be no more "fragmentation", you can even avoid to waste any free space.

Gee! This is the idea┬ž I never thought about it but this is the definitive solution.

Re: Will a Bigger Next Extent Results In Smaller Fragmentation [message #647737 is a reply to message #647730] Sat, 06 February 2016 10:37 Go to previous message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But I believe John have given me the best answer although I only realize he has already reply to me earlier.


Of course, in our answer we assume you have read all previous answers, above all when we say:

Michel Cadot wrote on Fri, 05 February 2016 09:08
...see John's answer.


Previous Topic: AWR reports snapshots is not generating
Next Topic: Importance of oratab
Goto Forum:
  


Current Time: Sat Feb 17 16:36:30 CST 2018

Total time taken to generate the page: 0.01138 seconds