Consider This...........
A reader, May 31, 2004 - 1:07 pm UTC
Hi Tom,
We used to have 2 large tables which had data for 2003 and 2004. Now these 2 tables have been divided based on the data. The data for 2003 has been shifted to a history table and the 2004 data was shifted to new tables in a separate tablespace. The previous tables were then renamed and the new tables were renamed to the previous ones. Then I truncated the old tables and dropped them. Now the tablespace has a lot of free spaces or fragmentation. The tablespace is LMT, so should I worry about this issue. The database version is 9iR2.
May 31, 2004 - 2:12 pm UTC
it has lots of free space.
you would have to prove to me that it is "fragmented". define "fragmentation".
To me it would be lots of little odd bits and pieces of space, each a different size and shape and each perhaps totally unusable for allocating storage in that tablespace anymore.
In an LMT, that just isn't going to happen.
Here is what i meant.
A reader, May 31, 2004 - 4:53 pm UTC
Sorry Tom if you misunderstood or I failed to explain in a better manner.
The fact is before truncate and drop the tablespace was like this UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUFFFFFFFFFFFFFFF.
As is evident the the free blocks were at the end. But now it goes like this UUUUFFFFFFFFFFFFFFFFFUUUUUUUUUFFFFUUUUUUUUUUFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFUUUUUUUFFFFFFFFFFF and so on.
As the table is LMT should I worry about it.
Hope this helps.
June 01, 2004 - 7:49 am UTC
so, each of the F's are Fully available.
I understand what it looks like. I'm not understanding how it is "fragmented" as there isn't an F in the picture that is not available for use.
A fragmented tablespace would look like this:
UUUUUUUfUUUUUUUfUUUUUUUUfUUUUUUUUfUUUUfufufufufufufufufufufufufufufufufufufuf
and it would take at least two ff's to allocate a next extent for anyone. That tablespace has lots of free space, but none of it is *usable*. An LMT removes that issue.
For "A Reader"
Riaz Shahid, June 01, 2004 - 2:24 am UTC
Please prove that your tablespace is fragmented as Tom said. Come with a real example (as Tom shown). Post here a complete test case for us...Don't just claim.
And if you can't then please please please don't waste Tom's Precious time...please be fair. Give others a chance to ask question.
June 01, 2004 - 8:26 am UTC
it is a matter of defining "fragmented" here really -- my point is
"fragmentation is when you have 500meg free in the tablespace, yet are unable to allocate a next extent because your tablespace is like swiss cheese, full of different sized holes -- none of which are usuable"
With an LMT, you don't get into that circumstance, hence the tablespace is not fragmented, it simply has free space in it that can be used for other stuff.
Reorganisation of LMT required ?
parag jayant patankar, June 01, 2004 - 8:58 am UTC
Tom,
If I am having LMT with uniform size so there will be no fragmentation of data.But does this LMT requires reorganisation for performance reason ?
( Reorganisation I mean my some method arrange table data in continuous extents by methods such as exp/imp, alter table ..etc )
thanks & regards
June 01, 2004 - 9:00 am UTC
No. Not it does not.
extents are never ever contigous -- even if you put them right next to eachother "logically" (we are on a file system, file systems are not contigous, you have raid striping and other stuff in addition to the fact that file systems are not contigous. Even if they were, you achieve nothing by having the data contigous from a performance perspective)
Perormance LMT
parag jayant patankar, June 01, 2004 - 9:25 am UTC
Tom,
if I have data of one table located in LMT which is having 50 files. This table data is spread across all 50 files. Then according to you there will not be any perfomance issue ? Am I correct Sir ?
thanks & regards
parag jayant patankar
June 01, 2004 - 11:49 am UTC
no, i never said that.
if ( all 50 files are on the same mount point )
and
(you have many many other mount points on different controllers)
and
(this is your most active data)
then perhaps
your layout is suboptimal
end if
you asked about fragmentation -- you might have an IO problem out the waazoo for all I know.
But,
o using an LMT
o having 50 files
o having data in all 50 files
is perfectly OK as standalone facts.
Thanks Tom
A reader, June 01, 2004 - 9:29 am UTC
Thanks Tom for your reply.
As for Mr. Riaz Shahid from PRAL, Lahore, Pakistan
I know what is "Fragmentation". Just wanted to clear my queries as this is what this forum is all about: For Learning and Discussions.
No hard feelings chap,
Cheers. :D ;)
LMT reorganisation
pjp, June 11, 2004 - 2:17 am UTC
Hi Tom,
Suppose I have LMT with only 1 or 2 files ( on different mount points and it is normal data). Then I understood correctly we do not have to reorganise data. Am I correct ? if yes can you give some sql example to support your argument.
regards & thanks
pjp
June 11, 2004 - 8:06 am UTC
no, you give *me* a case where you think a reorg would be necessary.
you do not need to reorg a LMT in order to reclaim space.
You may need to reorg a table, you may need to reorg an index, you will not need to reorg a tablespace.
LMT
pjp, June 11, 2004 - 9:50 am UTC
Hi Tom.
Thanks for your answer. I am puttning another question : - I am having one LMT having 2 datafiles. Does I required to reorganised only 1 table + 1 index for performance reason in LMT tablespace ? does i require to reorganised all tables + indexes for perfomance reason ?
thanks & regards
June 11, 2004 - 4:20 pm UTC
not sure i understand what you are asking.
reader
A reader, September 11, 2004 - 2:17 pm UTC
In there any way to find the NEXT_EXTENT size for segments
created in LMT tablespace with allocation type = SYSTEM
It keeps showing as NULL and difficult to pro-actively
decide if the free space is large enough
Thanks
September 11, 2004 - 2:54 pm UTC
nope, it is definitely undocumented and unpredicable.
Might I propose this:
assume the next is going to be the same as the last.
Make sure you have enough space for each segment in that tablespace to have 2 more extents of that size.
change "2" to a number that makes you feel "safe"
Reader
A reader, September 11, 2004 - 9:15 pm UTC
Might not work I guess
1 select segment_name, extent_id, bytes/1024/1024 mb from dba_extents where segment_name in
2 (
3 select segment_name from dba_segments where tablespace_name = 'SOME_TABLESPACE'
4 )
5* order by 1,2
....
UNGDED_SEARCH 251 .625
UNGDED_SEARCH 252 1
UNGDED_SEARCH 253 8
UNGDED_SEARCH 254 8
....
Any suggestion
September 12, 2004 - 10:56 am UTC
The suggest would be to compare the sum of N times the last allocated extent for each segment in a tablespace.
ops$tkyte@ORA9IR2> select sum( to_number( substr( max(to_char(extent_id,'fm000000000')||bytes), 10 ) ) ) * 2
2 sum_of_max_extent
3 from dba_extents
4 where tablespace_name = 'USERS'
5 group by owner, segment_name, partition_name, segment_type
6 /
SUM_OF_MAX_EXTENT
-----------------
655360
<b>That is one technique of grabbing the max extent id and the bytes allocated to it by owner, segment_name.... and summing it up. so in my case, taking the last extent of each allocated segment in users and summing it up and doubling it was 655,360 bytes..</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sum(bytes)
2 from dba_free_space
3 where tablespace_name = 'USERS'
4 /
SUM(BYTES)
----------
13369344
<b>that is pretty straightforward...
We could combine into a view:</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view space
2 as
3 select a.tablespace_name,
4 (
5 select sum( to_number( substr( max(to_char(extent_id,'fm000000000')||bytes), 10 ) ) ) * 2 6 sum_of_max_extent
7 from dba_extents
8 where tablespace_name = a.tablespace_name
9 group by owner, segment_name, partition_name, segment_type
10 ) two_time_last_extents,
11 (
12 select sum(bytes)
13 from dba_free_space
14 where tablespace_name = a.tablespace_name
15 ) free_bytes
16 from dba_tablespaces a
17 /
View created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from space where tablespace_name = 'USERS';
TABLESPACE_NAME TWO_TIME_LAST_EXTENTS FREE_BYTES
------------------------------ --------------------- ----------
USERS 655360 13369344
<b>and there you go...
But for me, it would never work, since:</b>
ops$tkyte@ORA9IR2> select * from space where tablespace_name = 'SYSTEM';
TABLESPACE_NAME TWO_TIME_LAST_EXTENTS FREE_BYTES
------------------------------ --------------------- ----------
SYSTEM 235667456 1441792
Would seem to indicate "red flag, warning, danger..." but not really, my system tablespace is only about 1.3% filled on this system -- the files haven't autoextended out themselves -- it'll grow as it needs to grow. </b>
Thanks Very much
A reader, September 12, 2004 - 9:41 pm UTC
Is table fragmented?
Shailesh, November 08, 2004 - 4:07 am UTC
Hi Tom,
At tablespace level we have taken care and locally managed tablespace, But still on 10g on Windows 2000. We are Facing performace issue while SELECTing records from a table.
To SELECT (without WHERE clause) 400 records from this table it takes 2 minutes. Every day we INSERT few Hundereds of thousands records in this table and after processing DELETE them.
Is this causing a problem? Fragmentation?High water Mark? Do we need to use TRUNCATE instead of DELETE?
As I said we use locally managed tablespace with DB block size 8K.
Table Structure:
Name Null? Type
----------------------------------------- -------- ----------------
EVENTID NOT NULL NUMBER(38)
SUBSCRIPTIONID NOT NULL NUMBER(38)
EXPIRYDATETIME NOT NULL DATE
EVENTCODE NOT NULL VARCHAR2(12)
EVENTSCHEDULEID NUMBER(38)
TRANSACTIONID NUMBER(38)
STATUS NOT NULL NUMBER(38)
EVENTCATEGORY NOT NULL VARCHAR2(12)
PARAMETERS1 VARCHAR2(4000)
PARAMETERS2 VARCHAR2(4000)
PARAMETERS3 VARCHAR2(4000)
CREATOR NOT NULL VARCHAR2(12)
PRIORITY NOT NULL NUMBER(38)
TRIGGEREDBY NOT NULL VARCHAR2(2)
EVENTQUEUINGTIME NOT NULL DATE
PARAMETERS4 VARCHAR2(4000)
PARAMETERS5 VARCHAR2(4000)
PARAMETERS6 VARCHAR2(4000)
CLASSNAME NOT NULL VARCHAR2(80)
RULENAME NOT NULL VARCHAR2(80)
ASSEMBLYNAME NOT NULL VARCHAR2(254)
TRANSACTEIN VARCHAR2(4)
LOGGEDINEIN VARCHAR2(4)
LOGGEDINOE VARCHAR2(12)
LOGGEDINUSER VARCHAR2(12)
LOGGEDINGROUP VARCHAR2(12)
----------------------------------------------
Trace information:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13404 Card=60 Byte
s=7860)
1 0 TABLE ACCESS (FULL) OF 'TABLENAME' (TABLE) (Cost=1340
4 Card=60 Bytes=7860)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
69213 consistent gets
69021 physical reads
0 redo size
180810 bytes sent via SQL*Net to client
787 bytes received via SQL*Net from client
27 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
377 rows processed
What can be the reason? How can solve such issues?
Please guide us.
Thanks & Regards,
Shailesh
November 08, 2004 - 10:19 am UTC
it is that the table is filled -- high water mark goes "up" -- and a delete cannot reduce the hwm.
So -- why are you full scanning this particular table? perhaps an index to access the records to be processed would be called for.
You can
a) truncate the table if the table is to be totally emptied, that'll reduce the HWM
b) in 10g, you can shrink a table
c) use indexes to get "N" records from this table quickly, regardless of the size of the table itself.
Highwater Mark?
shailesh, November 09, 2004 - 3:53 am UTC
Thanks Tom for an answer.
But would like to ask one question
Why should this become a users problem to solve HWM and why does Oracle not do this automatically?
Regards,
Shailesh
November 09, 2004 - 8:54 am UTC
have you thought of the expense of doing so?
in 10g, you can shrink a table.
in 10g, the database will periodically -- if you allow it -- take a look at objects and suggest "perhaps we want to shrink this, would you like me to do it now"
The shrink is not "free" -- it comes at a price (in resource utilization -- it has to read the entire table pretty much, from the bottom up, looking for rows to delete and re-insert at the top of the table, then invalidate plans in the shared pool since the data object has changed and so on and so forth)
Sometimes you have to understand how things work, in order to properly design and implement your systems. Why is it the users problem to understand that in windows a single process architecture is "correct" but you will be limited by the amount of memory you can now address unless you jump through hoops backwards and sideways to indirectly address memory?
A table is a complex data structure -- Oracle doesn't (it would not be anything you would remotely call efficient) keep track of the "last row" -- that would imply some serious level of serialization going on -- eg: lack of concurrency.
Once you understand how it works however, the implementations available to you to do this efficiently and effectively literally abound in the database.
Very nice
Ravi, November 09, 2004 - 6:45 am UTC
Hello Tom,
Can we use Index organized tables to avoid HWM?
Ravi
November 09, 2004 - 9:00 am UTC
they have them too -- if you fast full scan an IOT, it has a HWM
ops$tkyte@ORA9IR2> create table t1 as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> create table t2 (
2 OWNER,
3 OBJECT_NAME,
4 SUBOBJECT_NAME,
5 OBJECT_ID primary key,
6 DATA_OBJECT_ID,
7 OBJECT_TYPE,
8 CREATED,
9 LAST_DDL_TIME,
10 TIMESTAMP,
11 STATUS,
12 TEMPORARY,
13 GENERATED,
14 SECONDARY
15 ) organization index as select * from t1;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from t1;
28713 rows deleted.
ops$tkyte@ORA9IR2> delete from t2;
28713 rows deleted.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> select * from t1;
no rows selected
ops$tkyte@ORA9IR2> select * from t2;
no rows selected
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select * from t1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
399 consistent gets
0 physical reads
0 redo size
918 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
ops$tkyte@ORA9IR2> select * from t2;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=128)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_33758' (UNIQUE) (Cost=5 Card=1 Bytes=128)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets<b>
413 consistent gets</b>
0 physical reads
0 redo size
918 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t1 move;
Table altered.
ops$tkyte@ORA9IR2> alter table t2 move;
Table altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
no rows selected
ops$tkyte@ORA9IR2> select * from t2;
no rows selected
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select * from t1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
918 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
ops$tkyte@ORA9IR2> select * from t2;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8168 Bytes=1045504)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_33758' (UNIQUE) (Cost=5 Card=8168 Bytes=1045504)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets<b>
3 consistent gets</b>
0 physical reads
0 redo size
918 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
QUERY UNDERSTANDING
abc, February 09, 2005 - 1:07 pm UTC
Please help me to understand this queyr
select * from dba_free_space where tablespace_name = '&&Tablespace_name'
and bytes <= ( select min(next_extent)
from dba_segments
where tablespace_name = '&&tablespace_name')
order by block_id
February 09, 2005 - 3:05 pm UTC
query dba_free_space
for the tablespace = &&tablespace-name
for each row you find run the query
select min(..........)
if the bytes are less than or equal to that -- output the row
it is what it reads as really.
show me all of the free extents in tablespace &&tablespace_name such that the size of the free extent is SMALLER than the size of the minimum next extent in that tablespace.
that is it in "english"
:)
A reader, February 09, 2005 - 4:47 pm UTC
Thanks sweet Tom :)
query
A reader, February 09, 2005 - 4:49 pm UTC
hOW IT RELATED TOFRAGMENTATION????
February 10, 2005 - 12:52 am UTC
if all of your NEXT EXTENTS in a dictionary managed tablespace are larger than the largest free extent you have......
then you might have 500meg of freespace (according to dba_free_space, summing it up) in that tablespace but still not be able to allocate a next extent for any table in that tablespace simply because all of the free chunks you have are too small and fragmented to be used.
Is above query related to fragmentation
A reader, February 09, 2005 - 4:49 pm UTC
a small doubt
reader, February 10, 2005 - 4:20 am UTC
Dear Tom,
a small doubt here, as you have mentioned the query will display all of the free extents in the tablespace such that the size of the free extent is smaller than the size of the minimum next extent size required by any object on the tablespace, the query is using <=, so same of the free extents displayed will still be usable or not, please shed some light on this.
best regards.
February 11, 2005 - 2:47 am UTC
sure, drop the =
Fragmentation due to drop and create tables
Moses, August 01, 2007 - 12:12 am UTC
Hi Tom,
We have an app that constantly creates, inserts records and finally drops tables during the regular batch process (these are NOT global temporary tables). These tables they use to store intermediate results before data is updated to the final table.
We disabled dropping of these tables in the application properties and found that the tablespace grew twice in size in a single day. Grew by 6 GB.
We use 9iR2 LMT, system managed, pctincrease=0. Does this kind of activity create fragmentation?
Thanks for your valuable answers.
August 05, 2007 - 9:53 am UTC
depends on how you load these tables.
direct path always writes above the high water mark, tables would only grow (unless you TRUNCATE THEM - which is what you should do - do not revert to DROP TABLE, use TRUNCATE)
Moses Christopher, August 09, 2007 - 10:19 pm UTC
Thanks Tom I got what I need.
Sparsely Populated Blocks
Santosh Kumar, September 26, 2010 - 9:24 pm UTC
Sir,
Does LMT takes care of Sparsely Populated Blocks also?
Regards,
Santosh
September 27, 2010 - 12:05 pm UTC
define what you mean by the terminology "Sparsely Populated Block"
for block management you have either
manual segment space management - you set freelist, freelist groups, pctused and pctfree to decide how space is managed within blocks
automatic segment space management (ASSM) - you set pctfree to decide how space is managed within blocks.
Unless you have good reason not to - I would use ASSM
Sparsely Populated Blocks
Santosh Kumar, September 28, 2010 - 5:55 am UTC
Sir,
I got the term from the below links:
jonathanlewis.wordpress.com/2010/07/19/fragmentation-3/
jonathanlewis.wordpress.com/2010/07/22/fragmentation-4/
I just wanted to understand how to recognize such fragmentation and what is the impact as far as performance is concerned.
Regards,
Santosh
September 28, 2010 - 9:10 am UTC
locally managed tablespaces take care of extent management.
block management - when to reuse a block, when to stop using a block - is controlled either
a) manually - using freelists, freelist groups, pctfree, pctused
b) automatically - you just set pctfree, everything else is ignored, this is called automatic segment space management ASSM.
and Jonathan's articles talk about them - in depth? What other questions do you have.
But in short - LMT's have nothing to do with block space management.
more explain on tablespace fragmentation
A reader, March 26, 2012 - 3:12 am UTC
Hi Tom,
Still a little bit confused about 'tablespace fragmentation', hope you can find some time to elaborate(I am on 11g with LMT).
Basically my question is if i see below, can i say there is 'tablespace fragmentation'?
should be file 6 has one entry like file 5 be considered as 'non-tablespace fragmentation'?
Also during my testing in terms of tablespace fragmentation, i got some other questions, hope you can help to answer at the same time.They are inside my below test script, started with Q(Q1,Q2...).
select file_id, block_id, blocks from dba_free_space where tablespace_name='TEST';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
6 176 464
5 128 512
6 128 8
6 136 8
6 144 8
6 152 8
6 160 8
6 168 8
Below is my test script.
create tablespace test datafile '/home/oracle/test_1.dbf' size 5M;
alter tablespace test add datafile '/home/oracle/test_2.dbf' size 5M;
create table test(id int, a char(2000), b char(2000), c char(2000), d char(2000)) tablespace test;
insert into test select rownum,'a','b','c','d' from dual connect by level<=10;
commit;
select dbms_rowid.ROWID_RELATIVE_FNO(rowid), id from test;
Q1. all located inside the file test_2.dbf, does oracle always use the last data file of tablespace firstly?
Q2. will oracle use them one by one, after one is full switch to another data file?
Or it is possible to allocate extent in different file even they both have free space?
select file_id, block_id, blocks from dba_free_space where tablespace_name='TEST';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
6 152 488
5 128 512
insert into test select rownum,'a','b','c','d' from dual connect by level<=10;
select file_id, block_id, blocks from dba_free_space where tablespace_name='TEST';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
6 176 464
5 128 512
Q3: here the free space reduced from 488 to 464, but i have not commited, why?
rollback;
select file_id, block_id, blocks from dba_free_space where tablespace_name='TEST';
Q4: even after i rollback, why it still got 464 but not 488? There is no data inserted, should not take space i think.
drop table test;
select file_id, block_id, blocks from dba_free_space where tablespace_name='TEST';
Q5: is it a tablespace fragmentation?
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
6 176 464
5 128 512
6 128 8
6 136 8
6 144 8
6 152 8
6 160 8
6 168 8
create table test as select * from dba_objects where 1=2;
alter table test move tablespace test;
Q6: how to combine above 2 into 1?
'create table test as select * from dba_objects where 1=2 tablespace test' does not work
alter database datafile '/home/oracle/test_2.dbf' autoextend on;
insert into test select * from dba_objects;
commit;
select file_id, block_id, blocks from dba_free_space where tablespace_name='TEST';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
6 128 48
5 176 80
select file_name, bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
/home/oracle/test_1.dbf
5
/home/oracle/test_2.dbf
7
Q7: 7 is bigger than original 5M, so file test_2.dbf got autoextended.
But why still have free space in file 'test_1.dbf'?
Should not it be used up?
I know it is somehow due to i create some small table before and drop it, but not quite clear.
Because if i did not create small table before, but just create a big table(as below script), when check free space, no rows return
So it really make me believe it is 'tablespace fragmentation' in Q5.
create tablespace test datafile '/home/oracle/test_1.dbf' size 5M;
alter tablespace test add datafile '/home/oracle/test_2.dbf' size 5M;
alter database datafile '/home/oracle/test_2.dbf' autoextend on;
select file_id, block_id, blocks from dba_free_space where tablespace_name='TEST';
create table test as select * from dba_objects where 1=2;
alter table test move tablespace test;
insert into test select * from dba_objects;
select file_id, block_id, blocks from dba_free_space where tablespace_name='TEST';
no rows return
March 26, 2012 - 7:07 am UTC
it all depends on how you define fragmentation - doesn't it.
My definition would be:
it is 'fragmented' when it has say X-MB of free space and I need to have a next extent of Y-MB and Y < X and the next extent fails to allocate because there isn't sufficient contiguous free space available. This typically only happens with dictionary managed tablespaces. It can never happen with locally managed tablespaces with uniform extent sizes and rarely happens with locally managed tablespaces with system allocated extents.
q1) we use a round robin method of allocating extents typically - with the exception of a locally managed tablespace with system allocated extents. For that one, we tend to put the first N extents "together" if possible so that if the segment is dropped later - we get a bigger chunk of free space together (they are all 64k extents in many cases)
q2) round robin.
ops$tkyte%ORA11GR2> create tablespace test datafile '/tmp/test_1.dbf' size 5M;
Tablespace created.
ops$tkyte%ORA11GR2> alter tablespace test add datafile '/tmp/test_2.dbf' size 5M;
Tablespace altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table test(id int, a char(2000), b char(2000), c char(2000), d char(2000)) tablespace test;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
2 for i in 1 .. 1000000
3 loop
4 insert into test values ( i, 'a', 'b', 'c', 'd' );
5 commit;
6 end loop;
7 end;
8 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table OPS$TKYTE.TEST by 128 in tablespace TEST
ORA-06512: at line 4
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select extent_id, file_id, blocks
2 from dba_extents
3 where owner = user
4 and segment_name = 'TEST'
5 order by extent_id
6 /
EXTENT_ID FILE_ID BLOCKS
---------- ---------- ----------
0 12 8
1 12 8
2 12 8
3 12 8
4 12 8
5 12 8
6 12 8
7 12 8
8 12 8
9 12 8
10 12 8
11 12 8
12 12 8
13 12 8
14 12 8
15 12 8
16 13 128
17 12 128
18 13 128
19 12 128
20 13 128
21 12 128
22 13 128
23 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> drop tablespace test including contents and datafiles;
Tablespace dropped.
see how the first couple of tiny 8 block (64k total) extents are all in file 12 - they are next to each other logically, if we drop that table - we'll get a nice sized bit of contiguous free space. But when the extent size bumps up to 128 blocks - we start going round robin to spread the data out.
q3) because we had to put the data somewhere didn't we.... think about it, if we didn't actually allocate the space - where did it all go?
q4) once allocated, space remains allocated until you reorganize the object. If you insert a billion rows into a table and rollback - you'll have had to of allocated space for one billion rows. And since a rollback is a logical operation (it'll delete each row inserted, it is not like recovery) - it cannot release allocated space.
q5) not by my definition
q6) ? not sure what you mean. what is 2 and what is 1?
what didn't work?
q7) because we round robin allocate space and you allowed the datafile to autoextend so we did.
A reader, March 26, 2012 - 10:41 am UTC
Hi Tom,
Regarding your last response above....
(1) in above example don't there will be row chaining happen since there are 4 column with char(1000)?
(2) why you commit after each insert in for loop?
Thanks
March 26, 2012 - 11:23 am UTC
(1) yes, so what?
(2) because I knew it was going to blow up when I filled up the tablespace. it was a lazy way of getting the table to extend and fill up with data - without having to figure out how many rows exactly to insert. I just put in a row, commit and so on.
this was not a demo of the best way to load a table ;) it was a demo of how extents are allocated in a round robin fashion when you have more than one data file (poor mans striping - before striping was cool)
for point 6
A reader, March 26, 2012 - 10:07 pm UTC
Hi tom,
For point 6, i mean
create table test as select * from dba_objects where 1=2;
alter table test move tablespace test;
with above two command, i create a table named 'test' in tablespace test, whose strucute is same as dba_ojects.
But how can i only use one command to do so like below
'create table test as select * from dba_objects where 1=2 tablespace test', it is blamed to be syntax error.
March 27, 2012 - 7:35 am UTC
ops$tkyte%ORA11GR2> create table test <b>tablespace users</b> as select * from dba_objects where 1=2;
Table created.
the stuff describing the structure of the table goes completely before the "AS SELECT" part.
Tablespace fragmentation Oracle 10.2.0.4
Nadeesh, May 09, 2012 - 10:16 am UTC
INITIAL=NEXT EXTENT and pctincrease=0, LMT (10.2.0.4)
When more space is required in segment then extents are allocated to segment and when tables are dropped then all the extents are released back to tablespace. This is clear to me.
But can fragmentation happen in case of segment updates, deletes and truncates.
E=extent
B=blocks
1 extent=4 blocks
EEEEBBEEEEBBEEEBB
Can we have free blocks less then extent size in the tablespace which can lead to tablespace fragmentation e.g EEEEBBEEEEBBEEEBB as above?
When segment is looking for next extent then it will find there is no contiguous free space available.
Also why coalesce is not done when pctincrease=0 or this is not required at all considering INITIAL=NEXT EXTENT and pctincrease=0, LMT (10.2.0.4)
May 10, 2012 - 7:27 am UTC
initial and next and pctincrease do not count in a locally managed tablespace really - only on the initial creation of the segment (we figure out how much space it would have allocated in a dictionary managed tablespace and allocate that much). After that - they are meaningless.
Your mixing of E and B is incorrect. An extent is a collection of blocks, it doesn't make sense to mix them like that.
an extent would either be:
a) entirely free
b) entirely not free
there would be no "partial" extents.
An extent either belongs to a segment (and is entirely NOT free) or it does not (and is entirely free)
Tablespace fragmentation Oracle 10.2.0.4
Nadeesh, May 09, 2012 - 10:36 am UTC
Correction in above B=1 block
INITIAL=NEXT EXTENT and pctincrease=0, LMT (10.2.0.4)
When more space is required in segment then extents are allocated to segment and when tables are
dropped then all the extents are released back to tablespace. This is clear to me.
But can fragmentation happen in case of segment updates, deletes and truncates.
E=extent
B=1 block
1 extent=4 blocks
EEEEBBEEEEBBEEEBB
Can we have free blocks less then extent size in the tablespace which can lead to tablespace
fragmentation e.g EEEEBBEEEEBBEEEBB as above?
When segment is looking for next extent then it will find there is no contiguous free space
available.
Also why coalesce is not done when pctincrease=0 or this is not required at all considering
INITIAL=NEXT EXTENT and pctincrease=0, LMT (10.2.0.4)
May 10, 2012 - 7:28 am UTC
see above.
pctincrease means nothing in a locally managed tablespace
locally managed tablespaces do not ever require any coalescing, they are automatically coalesced by design.
vinayak solunke, August 03, 2012 - 6:26 am UTC
HI tom
We have table PICK size of 4 gb and inital extent is also 4gb. Table have only 60 rows so I want to remove fragmentation so I moved table within tablespace but fragmentation not removed. It showing still same size 4gb.
My question is Can I COALESCE used block?
August 16, 2012 - 8:23 am UTC
moving without a storage clause won't change the storage allocated. (and it is NOT fragmented, all of the data will be at the head of the table after a move).
You don't say what type of tablespace this is - but I doubt the extent is 4gb, maybe the initial allocation is - but it would not be common for the initial to be 4gb.
is it locally managed with uniform extents? You will not change the inintial allocation no matter what you do. It is uniform.
is it locally managed with system allocated? alter table t move storage( initial 1k next 1k); will create the smallest object possible.
is it dictionary managed? create a new locally managed tablespace NEWTS with system allocated extents and alter table t move tablespace newts storage ( initial 1k next 1k );
understanding fragmentation
Lazyboy, August 31, 2012 - 11:30 am UTC
Thanks for helping people.
I understand from this thread that with LMT fragmentation is not possible and if any small space/hole is left in table space is something which can't be extended i.e can't be reusable so no need to do anything for that(which is not going to impact performance any way with that).
lets say table having 10 million records in that we have deleted 1 million records. Blocks which was holding this data now can't be used until we reorg the table(which removes the high water mark)
most of the times this was misunderstand as fragmentation. Please advise
September 10, 2012 - 6:39 pm UTC
if you have 10,000,000 records and you delete 1,000,000 of them - the blocks that have sufficient free space on them will be reusable immediately after you commit.
we *reuse* space, as soon as you insert in that table, we'll reuse that.
A reader, March 04, 2013 - 8:47 am UTC
Hi Tom - We have a tablespace that has a used space of around 650GB. I ran the following query to determine the usage by table and I got only 350 GB. Why is the other 300gb unaccounted for ?
SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
t.NAME "Tablespace Name", s.growth/(1024*1024) "Growth in MB",
(SELECT sum(bytes)/(1024*1024)
FROM dba_segments
WHERE segment_name=o.object_name) "Total Size(MB)"
FROM DBA_OBJECTS o,
( SELECT TS#,OBJ#,
SUM(SPACE_USED_DELTA) growth
FROM DBA_HIST_SEG_STAT
GROUP BY TS#,OBJ#
HAVING SUM(SPACE_USED_DELTA) > 0
ORDER BY 2 DESC ) s,
v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
AND owner = '<ownername>'
ORDER BY 6 DESC
/
March 04, 2013 - 4:39 pm UTC
maybe because of your and owner = '<ownername>' bit.
select owner, segment_name, segment_type, bytes/1024/1024 mbytes from dba_segments
where tablespace_name = 'USERS'
something like that would be much easier to type and will show you the segments in a given tablespace.
if you want, add "and segment_name not like 'BIN$%'" to filter out recycle bin objects... they will be reported in dba_free_space as free space
Fragmentation While LMT uniform Extent
A reader, May 30, 2013 - 9:25 pm UTC
Hello Tom
We are using LMTs with uniform extent size:
CREATE TABLESPACE "<TABLESPACE_NAME>" DATAFILE
...
...
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
As you can see the extent size is pretty small. Also, we've some tables which override this default behaviour (size) an duse 4MB extents, all uniform:
CREATE TABLE "<OWNER>"."<TABL_NAME>"
...
...
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "<TABLESPACE_NAME>";
Now after a long period of use, we are getting monitor alerts:
SQL> @cannot_extend.sql
This script will show objects that cannot extend 1 time in their tablespace.
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME Free Chunk NEXT_EXTENT
-------------------- ---------------------------------------- -------------------- ------------------------------ ---------- -----------
<Owner> <TableName> TABLE <TablespaceName 655360 4194304
1 row selected.
SQL>
So it's stilla cse of LMT with uniform size and available space in tablespace, however object cannot extend, so it's fragmented?