Skip to Main Content
  • Questions
  • opinion about new auto segment management

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 02, 2002 - 2:41 pm UTC

Answered by: Tom Kyte - Last updated: September 28, 2009 - 1:41 pm UTC

Category: Database - Version: 9.0.1

Viewed 1000+ times

You Asked

Hi tom

What is your opinion on 9i's new auto segment management feature? I think it's not good at all since if we now distribute inserts on more blocks it may be good for DMLs but for queries it would have a negative impact isnt it? Since we would have to read more blocks (and waste buffer cache)

What do you think?

cheers

and we said...

Auto segment management just automates the process by which you used to have to manually tune using multiple freelists. In the past, you would have had to of determine the number of concurrent modifiers of data (inserts/updates that happen exactly at about the same time) and configure multiple freelists in order to satisfy these concurrent requests in a timely fashion.

With auto management you don't have to do this anymore. It'll not distribute across multiple blocks any more then multiple freelists would have in the past -- only you need not waste your time and energy attempting to determine the exact correct number of freelists to have.

This new feature is more efficient then multiple freelists:

<quote>
Free lists have been the traditional method of managing free space within segments. Bitmaps, however, provide a simpler and more efficient way of managing segment space. They provide better space utilization and completely eliminate any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS attributes
for segments created in the tablespace. If such attributes should be specified, they are ignored.
</quote>


a very informal, quick and dirty test I've done shows that automatic segment space mgmt gives us almost all of the benfit of finely tuned freelists -- without the pain of finely tuning freelists (something that is virtually impossible to do in the "real world" anyway).

Go for it -- on a heavy OLTP type table -- this is very beneficial.

and you rated our response

  (63 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

What more about 9i?

March 04, 2002 - 12:01 am UTC

Reviewer: Yogeeraj from Mauritius

Hello,

..more about 9i

I have am currently moving my database from Oracle 7.2.3 to Oracle 8.1.7 (9i was not there when i started). I have currently a performance gain of about 10 fold (well, my reports runs much faster!!) without any major change in my application design. (I am happy except that i once had a data block corruption with one of my Index Tablespaces)

I have the possibility of replacing my 8i with 9i before the completion of my full migration. (I will rearranging my storage internal disks to a new RAID BOX and at the same time converting my tablespaces to LMT). Should i go for 9i?

I run a 24x7 OLTP application which is very critical.

Thank you for your precious time
Best Regards
Yogeeraj

Tom Kyte

Followup  

March 04, 2002 - 8:02 am UTC

I would go with 9i if you are going through the upgrade process right now. Why upgrade to software that will be obsoleted before other software that exists right now?

March 05, 2002 - 3:36 am UTC

Reviewer: A reader from usa

if it´s very critical app dont use 9i, too buggy or at least wait till release 2 but I doubt it would be stable neither... nowdays us the users are the official free beta testers for software companies sigh

Tom Kyte

Followup  

March 05, 2002 - 8:27 am UTC

awk, sputter, gag,

WHAT. You're KIDDING right.

Really big SIGH from me. Beta tester indeed. This is not me being "corporate", this is me as an official long time user of 9i.

You know -- if it were too buggy, you wouldn't even be able to file a bug against it since the entire bug db (which is a pretty large thing as it has everything in it for many many years and is used by thousands of people) has been running on 9i. Same with emailing me -- you wouldn't be able to send or receive mail to/from me since it's been running on 9i as well (and that is a LARGE database with 10's of thousands of users using the most advanced features of 9i).

Ok, if you say it's "too buggy", give me that bug number or description of the show stopping bug. Once you do that, I'll give you a bug number for 817, when 9iR2 comes out -- I'll give you one for that release. Point being -- there be some bug ("product feature") in every release probably, as to whether it effects you or not remains to be seen.

I don't usually say this but ... you -- you can just stay away from here if you like. Or -- don't hide behind "a reader from usa", tell us who you are and what exactly you've run into.

sigh indeed. well, at least my blood pressure is up for the day, gets me going in the morning anyway.

auto segment management

April 24, 2003 - 12:03 pm UTC

Reviewer: A reader from Houston, TX

Tom, if I have segment management auto clause, how the pctfree, pctused are managed internally? Is it like, the bitmaps will track of how "full" the block is, is the block 1/2 or 1/3 full and depending on how much it is full, the system will chose the block for inserts. Could you please explain how it is happening in Oracle at the bitmap level. My understaning is the each bit in segment header corresponds to a block. With in the bit how Oracle manages or knows how full the block is. you are the best in explaining concepts. I appreciate your time.

Tom Kyte

Followup  

April 24, 2003 - 12:38 pm UTC

the algorithm is complex, not fully documented, and subject to radical change. In short

o we remember if a block is 75% or more empty, 50%, 25% or full.

o we remember if a block is formatted or not (been used or not been used)

o we spread the data out to reduce contention as it is being added (so we don't always goto the first block available as a list would, we hash around and use space "haphazzardly" but in a controlled fashion so it only appear haphazzard)



April 24, 2003 - 12:52 pm UTC

Reviewer: A reader

So I would guess you would not use this everywhere. For example, Large tables that do not get updated. We have three denormilize tables in our OLTP application used for reporting. We have PCTFREE set to 0 on the table and indexes to try and limit the block reads.

Can you give a kinda rule-of-thumb when you would and when you would not use this?

Thanks.

Tom Kyte

Followup  

April 24, 2003 - 1:34 pm UTC

well, the pctfree is still used here on the intial load up (it is pctused that is obsoleted totally)....

I would use this on my oltp style tables.

I don't see the advantage to using this on a warehouse type table (don't necessary see the disadvantage either).

See also Metalink

April 24, 2003 - 4:58 pm UTC

Reviewer: Jan van Mourik from Houston, TX USA

Do a search on Metalink for "assm". There's an interesting thread with subject "Re : automatic segment space management in 9.2"> Scroll down and read what Richard Foote has to say.

There is something strange with assm and deletes though. I played around with assm a while ago. I created a table in a lmt with assm and inserted some rows until I had some full blocks. Then I started deleted them, and looked at the bitmap. It seems that the bitmap won't be updated until there is '25-50% free' space. It doesn't go from 'FULL' to '0-25% free'! Even when doing updates I didn't see it being changed to '0-25% free', it will change to '25-50% free' though. Did anybody else notice this?

Segment dump output

April 25, 2003 - 10:59 am UTC

Reviewer: Jan van Mourik from Houston, TX USA

To clarify my comment above, this is what I ran:

Db_block_size is 8192.
create table bmb (id number, col1 char(2000), col2 char(1)) pctfree 0 tablespace users01;

begin
execute immediate 'truncate table bmb';
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (1, '1', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (2, '2', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (3, '3', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (4, '4', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (5, '5', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (6, '6', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (7, '7', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (8, '8', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (9, '9', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
insert into bmb values (10, '10', '1');
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);

delete from jvmo.bmb where id = 10;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 9;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 8;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 7;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 6;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 5;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 4;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 3;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 2;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);
delete from jvmo.bmb where id = 1;
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);

execute immediate 'truncate table bmb';
commit;
sys.dbms_space_admin.segment_dump('USERS01',2, 475);

end;
/

This is extracted from the dump file:

execute immediate 'truncate table bmb';
0:Metadata 1:Metadata 2:Metadata 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted

insert into bmb values (1, '1', '1');
0:Metadata 1:Metadata 2:Metadata 3:75-100% free
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free
8:75-100% free 9:75-100% free 10:75-100% free 11:75-100% free
12:50-75% free 13:75-100% free 14:75-100% free 15:75-100% free

insert into bmb values (2, '2', '1');
12:25-50% free 13:75-100% free 14:75-100% free 15:75-100% free

insert into bmb values (3, '3', '1');
12:0-25% free 13:75-100% free 14:75-100% free 15:75-100% free

insert into bmb values (4, '4', '1');
12:0-25% free 13:75-100% free 14:75-100% free 15:75-100% free

insert into bmb values (5, '5', '1');
12:FULL 13:50-75% free 14:75-100% free 15:75-100% free

insert into bmb values (6, '6', '1');
12:FULL 13:25-50% free 14:75-100% free 15:75-100% free

insert into bmb values (7, '7', '1');
12:FULL 13:0-25% free 14:75-100% free 15:75-100% free

insert into bmb values (8, '8', '1');
12:FULL 13:0-25% free 14:75-100% free 15:75-100% free

insert into bmb values (9, '9', '1');
12:FULL 13:FULL 14:50-75% free 15:75-100% free

insert into bmb values (10, '10', '1');
12:FULL 13:FULL 14:25-50% free 15:75-100% free

delete from jvmo.bmb where id = 10;
12:FULL 13:FULL 14:50-75% free 15:75-100% free

delete from jvmo.bmb where id = 9;
12:FULL 13:FULL 14:75-100% free 15:75-100% free

delete from jvmo.bmb where id = 8;
12:FULL 13:FULL 14:75-100% free 15:75-100% free

delete from jvmo.bmb where id = 7;
12:FULL 13:25-50% free 14:75-100% free 15:75-100% free

delete from jvmo.bmb where id = 6;
12:FULL 13:50-75% free 14:75-100% free 15:75-100% free

delete from jvmo.bmb where id = 5;
12:FULL 13:75-100% free 14:75-100% free 15:75-100% free

delete from jvmo.bmb where id = 4;
12:FULL 13:75-100% free 14:75-100% free 15:75-100% free

delete from jvmo.bmb where id = 3;
12:25-50% free 13:75-100% free 14:75-100% free 15:75-100% free

delete from jvmo.bmb where id = 2;
12:50-75% free 13:75-100% free 14:75-100% free 15:75-100% free

delete from jvmo.bmb where id = 1;
12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free

execute immediate 'truncate table bmb';
0:Metadata 1:Metadata 2:Metadata 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted

oracle@houhpt24=> grep 'Freeness Status:' ids9i_ora_2444.trc
truncate Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
insert 1 Freeness Status: nf1 0 nf2 0 nf3 1 nf4 12
insert 2 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 12
insert 3 Freeness Status: nf1 1 nf2 0 nf3 0 nf4 12
insert 4 Freeness Status: nf1 1 nf2 0 nf3 0 nf4 12
insert 5 Freeness Status: nf1 0 nf2 0 nf3 1 nf4 11
insert 6 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 11
insert 7 Freeness Status: nf1 1 nf2 0 nf3 0 nf4 11
insert 8 Freeness Status: nf1 1 nf2 0 nf3 0 nf4 11
insert 9 Freeness Status: nf1 0 nf2 0 nf3 1 nf4 10
insert 10 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 10
delete 10 Freeness Status: nf1 0 nf2 0 nf3 1 nf4 10
delete 9 Freeness Status: nf1 0 nf2 0 nf3 0 nf4 11
delete 8 Freeness Status: nf1 0 nf2 0 nf3 0 nf4 11
delete 7 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 11
delete 6 Freeness Status: nf1 0 nf2 0 nf3 1 nf4 11
delete 5 Freeness Status: nf1 0 nf2 0 nf3 0 nf4 12
delete 4 Freeness Status: nf1 0 nf2 0 nf3 0 nf4 12
delete 3 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 12
delete 2 Freeness Status: nf1 0 nf2 0 nf3 1 nf4 12
delete 1 Freeness Status: nf1 0 nf2 0 nf3 0 nf4 13
truncate Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Notice how after the deletes start, the status doesn't go from "FULL" to "0-25% free". It does't get updated until there's "25-50% free"!


Space Management in Locally Managed Temporary Tablespaces

May 17, 2003 - 4:00 am UTC

Reviewer: A Reader of AskTom from Pakistan

Hi Tom!

The introduction of LMTs looks really interesting in new releases of Oracle. It has atleast minimized the space management burden of the DBAs. But I have another experience... I have created a Locally Managed Temporary Tablespace (1 gig initially) and now its size is more than 3 gig and I am losing free space on my disk. With the Oacle8i.. it was very easy in case of Dictionary Managed Temporary Tablespaces, we took temporary tablespace offline and then took it online to free the used blocks. Isn't there any method to release the used space by the Locally Managed Temporary Tablespaces in Oracle9i Release 2 ? Please tell us in detail if there is any way. Thanks...

Tom Kyte

Followup  

May 17, 2003 - 10:13 am UTC

(new meaning the last 5 releases of course ;)


you know, it is just going to grow again. why did you get into this predicament in the first place.

do you remember how many hours that took to shrink with DMT's (could have been days actually). do you remember how hot SMON got during that?

do this

create new temp -- making sure to disable autoextend this time as you obviously don't want it; this'll take a fraction of a second regardless of the size of temp.

alter users to new temp;

drop old temp;



No way to release the used space..?

May 17, 2003 - 11:49 am UTC

Reviewer: A Reader from Pakistan

Well Boss! It means there is no way to release the used space in LM temporary tablespaces until the latest release of Oracle.. Isn't?

Tom Kyte

Followup  

May 17, 2003 - 3:13 pm UTC

well boss?

no, it doesn't mean that at all -- does it.

I told you how to do it after all first.

Second, the space need not, i would go as far to say -- should not -- be "released". you are just going to get it again.


The space IS in fact released -- it is dedicated to temp. It is just a waste of cycles on your part and the systems part to "release" it.

opinion about new auto temp segment management

May 20, 2003 - 10:08 am UTC

Reviewer: A Reader from Pakistan

Boss is just to give you honour as i am learning from you :)
I am really thankful to you becoz of ur help in understanding the logic of auto temporary segment management in Oracle.

Potential issues with ASSM and LMT

July 25, 2003 - 3:32 pm UTC

Reviewer: Peter Tran from Houston, TX USA

Tom,

Have you encountered this problem with LMT and ASSM?

When using "ANALYZE TABLE <table_name> with estimate statistics;", you can potentially get a ORA-00600 [KCBGTCR_12].

See </code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=BUG&p_id=2619867 <code>

The work around is to either use compute statistics or the DBMS_STATS package.

I wonder if you've encountered this problem. I ran into it when I did a tablespace reorg moving tables and indexes from 60+ tablespaces into 6.

Thanks,
-Peter

Tom Kyte

Followup  

July 25, 2003 - 5:10 pm UTC

whether i've "run into it" or not doesn't seem particularly "relevant" (what would it mean that I've hit it or not?)

dbms_stats is the method we are basically saying is "the way to go" so the workaround is pretty good as it gets you there.

September 29, 2003 - 1:03 pm UTC

Reviewer: A reader from orlando,FL

Tom,
Your explanation of freelist in your new book was the best. The concept was explained without complicating things. Great teacher. The water cooler example *and* the diagram finally drove home the point for me. Now,

a) when we create a LMT using ASSM, oracle does the freelist management for us. What is the algorithm - does it create freelists or freelist groups?

b) In page 229 you mention that buffer busy waits(bbw) is *one* indication of free list contention. what are the *other* situations ?

c)Can we derive:
*all* freelist contention should show up as bbw, but all bbw are not freelist contentions

d) Am i correct in assuming multiple freelist groups is nothing but just many master freelists

Thanks as always

Tom Kyte

Followup  

September 29, 2003 - 1:30 pm UTC

a) trick question -- neither. it uses bitmaps stored in blocks to manage space. no freelists at all.

b) other causes of buffer busy waits could be -- you are waiting for the block to be read, someone else has the buffer and makes you wait, we are waiting for some other session to read the block in for us...

c) yes

d) more or less, yes.

JP Lewis's Comments - Taken from Metalink

October 14, 2003 - 12:04 pm UTC

Reviewer: JP Lewis's Comments from USA

From: J P Lewis 31-Jan-02 13:26
Subject: Re : Re : Connection pool much slower in 9i than in 8i



Very interesting.

Thanks for the feedback. I had noticed that
the number of bitmap blocks allocated for
Automatic Segment Space management was rather
high. Typical figures seem to be 1 bitmap block
per 64 data blocks for segment sizes up to 64MB,
and one bitmap block per 256 data blocks thereafter
up to the next limit (wherever that is).

For large databases with partitioned objects this
seems to suggest that you could have a very large
fraction of the db_block_buffers dedicated to bitmap
blocks, and some of the side effects of having a large
number of concurrent processes include a very wide
scattering of I/O - including sparsely populated data
blocks - across the table. My most 'noticeable' experiment
(WHICH SHOULD NOT BE EXTRAPOLATED to a full size system)
was to discover a single row insert to a newly created
table resulting in a 16 block tablescan.

I suspect that there may be some significant strategy,
or implementation, changes pretty soon for this feature.



Jonathan Lewis
Author: Practical Oracle 8i

Now running intensive seminars
</code> http://www.jlcomp.demon.co.uk/seminar.html <code>

Hosting the Oracle usenet FAQ
www.jlcomp.demon.co.uk/faq/ind_faq.html




February 10, 2004 - 3:24 am UTC

Reviewer: Dillip

Tom,

In one database I have LMT and the other DMT.

LMT
---
DATA8K          PERMANENT ONLINE    LOGGING   LOCAL      UNIFORM   AUTO



DMT
---
DATA            PERMANENT ONLINE    LOGGING   DICTIONARY USER      MANUAL

The inserts on LMT takes more time compared to DMT, what is the issue ?

LMT
---
SQL> insert into test1 select * from test;

25953 rows created.

Elapsed: 00:00:08.08

Execution Plan
------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'




Statistics
------------------------------------------------------
         68  recursive calls
       3127  db block gets
        764  consistent gets
          0  physical reads
    2523660  redo size
        380  bytes sent via SQL*Net to client
        312  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      25953  rows processed

DMT
---
SQL> insert into test1 select * from test;

21911 rows created.

Elapsed: 00:00:03.00

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
         86  recursive calls
       1456  db block gets
        613  consistent gets
          0  physical reads
    2133304  redo size
        368  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      21911  rows processed 

Tom Kyte

Followup  

February 10, 2004 - 6:39 am UTC

slower disks perhaps, different cache size perhaps, smaller logs leading to checkpionts perhaps -- dozens of reasons, not of them related to LMT vs DMT of course.  


do it in the same database, a couple of times, average it out:

ops$tkyte@ORA817DEV> create table t1 tablespace testing as select * from all_objects where 1=0;
 
Table created.
 
Elapsed: 00:00:00.27
ops$tkyte@ORA817DEV> create table t2 tablespace users as select * from all_objects where 1=0;
 
Table created.
 
Elapsed: 00:00:00.17
ops$tkyte@ORA817DEV> select tablespace_name, extent_management, allocation_type
  2  from dba_tablespaces
  3  where tablespace_name in ( 'USERS', 'TESTING' )
  4  /
 
TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
TESTING                        DICTIONARY USER
USERS                          LOCAL      UNIFORM
 
Elapsed: 00:00:00.03
ops$tkyte@ORA817DEV> set timing on
ops$tkyte@ORA817DEV> insert into t1 select * from all_objects;
 
26886 rows created.
 
Elapsed: 00:00:09.56
ops$tkyte@ORA817DEV> insert into t2 select * from all_objects;
 
26886 rows created.
 
Elapsed: 00:00:09.53
ops$tkyte@ORA817DEV>


(you need to do it a couple of times to remove things like "one got caught in a checkpoint, one waited for PIO, etc)

But as you can see -- pretty much "the same" there. 

February 12, 2004 - 12:45 pm UTC

Reviewer: Dillip

Thanks Tom. I did a test as you suggested on DMT and all possible LMT configuration in the same box here is the result.

I created a tablespace with 100MB space for each type.
case insert 23k rows from dba_objects.

Why the db block gets is high in case of auto segment space management ? and higher recursive calls in the 2nd 3rd iteration in LMT in general ?

Type Time Rec Calls DB bocks Cons Gets Phy Reads
DMT It-1 7.11 1061 1704 108953 0
It-2 5.31 9 1653 104256 0
It-3 5.31 9 1664 104260 0

LMT It-1 5.40 349 1930 104371 0
It-2 5.42 49 1696 104259 0
It-3 5.38 78 1721 104281 0
(System
Manual)

LMT It-1 5.36 58 1711 104266 0
It-2 5.36 49 1696 104260 0
It-3 5.37 78 1719 104280 0
(Uniform
Manual)

LMT It-1 5.36 58 3022 104357 0
It-2 5.36 58 3057 104400 0
It-3 5.38 69 3056 104430 0
(Uniform
Auto)

Tom Kyte

Followup  

February 12, 2004 - 12:53 pm UTC

you can enable sql_trace=true to see the recursive calls (they will be quota related I believe -- hard to say as I don't see your actual test case so I cannot tell if you truncated, deleted, whatever -- but the recursive sql is probably quota checks and you had more extents in your LMTS than DMTS and hence did the check more often)

the db block gets are part of the automation of ASSM - it is just the "way it works", it is doing space mgmt in a much more sophisticated fashion to alleviate the burden of your having to set freelists, freelist groups, pctused and so on.



February 13, 2004 - 3:05 pm UTC

Reviewer: Dillip

Thanks Tom. I do not know if this info is enough for you to comment but want to give a try. In a database under load
I see a high enqueue waits (TX).

SQL> select * from v$enqueue_stat order by CUM_WAIT_TIME desc;

   INST_ID EQ TOTAL_REQ# TOTAL_WAIT#  SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME       
---------- -- ---------- ----------- ---------- ----------- -------------       
         1 TX    3506702      363001    3505068        1629     373683590       
         1 FB      33216        1433      33216           0       8839433       
         1 HW      45198       14252      45198           0       6281436       
         1 SQ     396713      347108     396713           0        538687       
         1 US     136839         628     136838           0         11124       
         1 CF      47720           0      47720           0             0       
         1 CI      16246           0      16246           0             0       
         1 CU        570           0        570           0             0       
         1 IA          1           0          1           0             0       
         1 MR      19480           0      19480           0             0       
         1 SR          1           0          1           0             0

and simple inserts are just slowwwww

ADDRESS                HASH      CHILD       EXEC      ETIME/exec    CPUTIME/exec SQL_TEXT
---------------- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
C0000001C8D981B0 2424192392          0     704379 28043.3578     102.29   INSERT INTO test.table  (       col1, col2,col3, col4, col5, col6, col7, col8, col9), VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9)

What could be the issue, could this be because of LMT with auto segment space management ? 

Tom Kyte

Followup  

February 13, 2004 - 3:12 pm UTC

not with enqueue waits, no.

You have application logic at fault here. You have blockers and blockees.


Maybe you have a unique index on that table that people are trying to stuff the same values into.

Maybe you have foreign keys that are not indexed.

You have simple "blocking/locking" problems. Look to your application logic. As you execute that simple insert -- see who is blocking you and what they are doing.

February 13, 2004 - 4:43 pm UTC

Reviewer: Dillip

Thanks a lot Tom for the clarification.

SQL> @getseswait 10

       SID EVENT                     P1TEXT                                 P1 P1RAW    SECONDS_IN_WAIT
---------- ------------------------- ------------------------------ ---------- -------- ---------------
        10 enqueue                   name|mode                      1415053318 54580006              18 

assm

March 18, 2004 - 5:36 pm UTC

Reviewer: reader

(1) Is there a view to find how many BMBs a segment has been allocated?
(2) What is the wait event asscoiated with contention for BMB that I can look for in the statspack report for example?

Thanks.

Tom Kyte

Followup  

March 18, 2004 - 6:01 pm UTC

dbms_space can do that.

dbms_space.space_usage will count up the number of fs1..fs4 and full blocks.

dbms_space.unused_space will tell you how many blocks are "there" (sum blocks from dba_extents) and how many are unused.


total_blocks - unused_blocks - sum(fs1..fs4,full blocks) = our blocks used to manage the space.


"buffer busy waits"

unformatted blocks

March 18, 2004 - 10:32 pm UTC

Reviewer: David

During FTS of a table in ASSM tablespace, does oracle read unformatted blocks below HWM? Thanks.

Tom Kyte

Followup  

March 19, 2004 - 8:14 am UTC

it reads around them.

there is the lhwm (low high water mark) where it just reads all

then there is the hhwm (high hwm) where it reads "more carefully" if you will, using the bmb's.

client wants to keep using RBS

April 05, 2004 - 11:33 am UTC

Reviewer: David from Washington, DC

I'm a DBA supporting government agency (the client). App vendor (IQ) asked me to "add a rollback segment called RB01 to the database," because their stored procedures are hard-coded to use RB01.

Our 9.2.0.3 database was created to use automated space management/undo. My inkling is to tell the customer "no, U can't" but since saying "no" to clients is frowned upon, I want to be sure I have the correct answer. I am going to recommend that the developer remove the "set transaction use rollback segment ..." statements from the packages. To add a rollback segment is a HUGE configuration change, and I might even have to recreate the datbase(?) in order to implement it. Is this correct?


Tom Kyte

Followup  

April 05, 2004 - 12:02 pm UTC

you can set undo_suppress_errors instead to placate that application (the use rollback segment request will succeed and they will be none the wiser)

you would not have to recreate the database - you would just create rollback segments, add them to the init.ora (or make them public), turn off undo management a restart the database.



Initrans in ASSM

May 18, 2004 - 11:51 am UTC

Reviewer: Vivek Sharma from Bombay, India

Dear Tom,

I am planning to convert my Tablespaces to make use of Automatic Segment Space management feature of oracle 9i. We have specified high PCTFREE values for many tables which were having frequent Row Migration problem and many of them (small tables) have been specified with high values of initrans. After moving these to ASSM tablespaces, shall I specify the Initrans and high PCTFREE (to avoid ro migration) or these are automatically taken care of by Oracle.

Thanks and Regards
Vivek



Tom Kyte

Followup  

May 18, 2004 - 6:16 pm UTC

initrans and pctfree are still things you set, pctused, freelists, freelist groups -- they are what you don't have to set.

assm and LONG/LOB columns

July 28, 2004 - 12:42 pm UTC

Reviewer: Alex from Chicago, IL

Hi Tom,
in "Oracle 9i New Features" book by Oracle Press I read that LOB columns cannot be created in LMT with assm. Can you please explain whether they meant the LOB must be created out-of-line, but the table can be created in LMT with assm or the whole table must be put into separate tablespace without assm. Also, is there a similar restriction on columns with LONG datatype? I did not find anything in documentation regarding this, but want to be sure.

Thanks.

Tom Kyte

Followup  

July 28, 2004 - 1:36 pm UTC

it was true in 9ir1:

ops$tkyte@ORA9IR1> alter user ops$tkyte default tablespace assm;
 
User altered.
 
ops$tkyte@ORA9IR1> create table t ( x int, y clob );
create table t ( x int, y clob )
*
ERROR at line 1:
ORA-03001: unimplemented feature
 
 
ops$tkyte@ORA9IR1> alter user ops$tkyte default tablespace tools;
 
User altered.
 
ops$tkyte@ORA9IR1> create table t ( x int, y clob );
 
Table created.


#but it no long is#b



  1* select extent_management, allocation_type, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces
ops$tkyte@ORA9IR2> /
 
EXTENT_MAN ALLOCATIO SEGMEN
---------- --------- ------
LOCAL      SYSTEM    AUTO


  1* select segment_name, segment_type, tablespace_name from user_segments
ops$tkyte@ORA9IR2> /
 
SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
T                              TABLE              USERS
SYS_IL0000039654C00002$$       LOBINDEX           USERS
SYS_LOB0000039654C00002$$      LOBSEGMENT         USERS


ops$tkyte@ORA9IR2> select  * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
 



 

converting to auto

September 13, 2004 - 7:59 am UTC

Reviewer: dxl from uk

Tom

In the process of upgrading from 8.1.7.4 to 9iR2, we have converted out tablespaces to lmts in 8i so that when we upgrade the 9i ones are lmt. But the lmts from 8i have been only manual segment management. Having read a lot about the auto feature i believe this is the best way for us to go.

1) how should i best go about converting our new 9i lmts to use auto segment management?

Can it be done with an alter tablespace command?
(I am thinking probably not as it is going to mean reorganizing the whole tablespace but I thought i'd ask??)

If not, then what is the best way to do it? export/import ? or create new lmt with auto and use alter table move??

2) For future upgrades instead of using the upgrade tool to go from 8.1.7.4 to 9i, should we use export/import so that the new 9i lmt can be precreated with auto segment management??

(this is for 9iR2)

Many thanks

Tom Kyte

Followup  

September 13, 2004 - 8:46 am UTC

1) you have to create new, alter table move/alter index rebuild to move the segments into these new tablespaces.

2) no, because you can do this move stuff over time -- as you like, bit by bit.

In fact, if you wanted to -- you could use dbms_redefinition to perform much, if not all, of this "online"

assm and dbms_space

October 19, 2004 - 2:31 pm UTC

Reviewer: Alex from Chicago

Tom,
I have a script that checks HWM for a given owner/segment type/segment name and uses dbms_space package (I've taken one of your scripts and modified it long time ago). When I run it for segments that are created in assm tablespaces I get an error "ORA-10618: Operation not allowed on this segment". Can you please tell me what is wrong? This script works fine for objects created in tablespaces without assm. Here is the script:

PROMPT
ACCEPT own PROMPT 'Enter segment owner (press ENTER for ALL).......................: '
ACCEPT type PROMPT 'Enter segment type (press ENTER for ALL or use wild card ("%")): '
ACCEPT name PROMPT 'Enter segment name (press ENTER for ALL or use wild card ("%")): '
PROMPT
PROMPT Working on it. Please wait...
PROMPT

SET TERMOUT OFF

VARIABLE v_own VARCHAR2(200)
VARIABLE v_type VARCHAR2(200)
VARIABLE v_name VARCHAR2(200)
EXECUTE :v_own := UPPER('&own')
EXECUTE :v_type := UPPER('&type')
EXECUTE :v_name := UPPER('&name')

SET TERMOUT ON
SET FEEDBACK OFF

DECLARE
TYPE name_type IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
v_own name_type;
v_type name_type;
v_name name_type;

CURSOR c_get_tab
IS
SELECT owner
,segment_type
,segment_name
FROM dba_segments
WHERE (owner = :v_own OR :v_own IS NULL)
AND (segment_type LIKE :v_type OR :v_type IS NULL)
AND (segment_name LIKE :v_name OR :v_name IS NULL)
ORDER BY 1, 2 DESC, 3;

v_free_blks NUMBER;
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_lastusedextfileid NUMBER;
v_lastusedextblockid NUMBER;
v_last_used_block NUMBER;

PROCEDURE p(p_label IN VARCHAR2, p_num IN NUMBER )
IS
BEGIN
dbms_output.put_line(RPAD(p_label,55,'.')||p_num );
END;

BEGIN
OPEN c_get_tab;
FETCH c_get_tab BULK COLLECT INTO v_own,v_type,v_name;

FOR i IN v_name.FIRST..v_name.LAST
LOOP
dbms_space.free_blocks (segment_owner => v_own(i),
segment_name => v_name(i),
segment_type => v_type(i),
freelist_group_id => 0,
free_blks => v_free_blks
);

dbms_space.unused_space(segment_owner => v_own(i),
segment_name => v_name(i),
segment_type => v_type(i),
total_blocks => v_total_blocks,
total_bytes => v_total_bytes,
unused_blocks => v_unused_blocks,
unused_bytes => v_unused_bytes,
last_used_extent_file_id => v_lastusedextfileid,
last_used_extent_block_id => v_lastusedextblockid,
last_used_block => v_last_used_block
);

dbms_output.put_line(v_own(i)||'.'||v_name(i)||' - '||v_type(i));
dbms_output.put_line(RPAD('-',LENGTH(v_own(i)||'.'||v_name(i)||' - '||v_type(i)),'-'));
p('TOTAL BLOCKS',v_total_blocks);
p('USED BLOCKS',v_total_blocks-(v_unused_blocks+v_free_blks));
p('UNUSED BLOCKS (never been used)',v_unused_blocks);
p('FREE BLOCKS (contained data, but on FREE LIST now)',v_free_blks);
-- dbms_output.put_line(RPAD('-',65,'-'));
dbms_output.put_line(CHR(10));

p('TOTAL K_BYTES',v_total_bytes/1024);
p('USED K_BYTES',(v_total_bytes-v_unused_bytes)/1024);
p('UNUSED K_BYTES',v_unused_bytes/1024);
dbms_output.put_line(RPAD('-',65,'-'));
-- p('LAST USED EXT FILEID', l_lastusedextfileid);
-- p('LAST USED EXT BLOCKID', l_lastusedextblockid);
-- p('LAST USED BLOCK', l_last_used_block);

dbms_output.put_line(CHR(10));
END LOOP;

EXCEPTION
WHEN OTHERS THEN
-- NULL;
dbms_output.put_line(SQLCODE||','||SQLERRM);

END;
/

PROMPT
UNDEFINE own name type


unused vs. unformatted blocks

October 21, 2004 - 3:13 pm UTC

Reviewer: Alex from Chicago

Tom,
is there a difference between unused and unformatted blocks meanings? Why are the values different for the same segment?

Tom Kyte

Followup  

October 22, 2004 - 2:56 pm UTC

unused are way above the high water mark.

unformatted are below the hwm but have not yet been used.

ASSM and insert I/O

December 21, 2004 - 2:39 pm UTC

Reviewer: Steve

There was a posting earlier quoting J Lewis saying a single row insert took 16 blocks. In our production environment we are experiencing extremely high I/O on insert statements (against tables in ASSM tablespaces). Some statements show as high as 2000 logical i/o per insert (for that table avg row length is 300 bytes, 3 indexes on the table with blevel of 3). It doesn't happen to all tables, only a handful in our situation. But others show 300, 500, 600, 900, 1000 I/O per insert (all are single row inserts). There's no FK constraints, triggers, or anything else that could contribute to the extra I/O.

I was able to find a table in a development environment where I dropped all indexes on the table and inserted 1 row. I/O was 128 to insert 1 row. I bounced the db and traced with wait events so I could find the blocks it was scanning- it showed 120 physical reads against the table blocks. When I dumped the blocks most of them were FIRST LEVEL BITMAP BLOCKS (I dumped them in the order the trace file showed them being accessed). It is interesting to see that ALL of the FIRST LEVEL BITMAP BLOCKS it shows getting scanned "control" many many blocks that show free space- but the last block it shows being read before the actual data block showed all blocks as FULL except 1. It's almost like Oracle wants to use the most full blocks first. We are in a RAC environment- oracle documentation clearly recommends using ASSM for RAC and even boasts 30% performance gains when using ASSM vs freelists. I strongly doubt that their testing showing the 30% performance increase had inserts doing 1000+ logical I/O per insert.

For a couple tables in question I mapped the average I/O per insert per hour over a 24 hour period. It was interesting to see that the average I/O was 600 between 2am and 11am, then from 11am - midnight it went down to 130. We have a purge routine that purges data older than 6 months that happens to start at 2am and completes around 10am. When I did a count of the number of records purged it was about 350k by 10am (let's not go into why it takes 8 hours to purge 350k records...purchased app, black box code). It was interesting to see that by around 10-11am we had inserted about 350k records. For this table it's almost like the deletes make the bitmaps longer and oracle scans through to the end of the link. But as soon as we fill those blocks it scans less and less. But like I said above- this does not happen to every table in the ASSM tablespaces. There are known bugs related to deleting and inserting in the same transactions but in our case there's different sessions doing the work.

One other thing to note- for the test where I show 128 i/o per insert- if I export the table import into a different schema or create table as select from it the inserts into the new tables show 5 i/o. The problem is definately related to the current layout of the bitmaps.

Tom- if you're intested in seeing more about the issue let me know- I can give you the TAR I have opened on the issue which contains all of the dumps and additional details on the problem. This is a major performance concern for us.


BMBs in ASSM

December 26, 2004 - 9:51 pm UTC

Reviewer: reader

Is it true that for every extent that is allocated, the first 2 blocks will be used for BMBs? For example, in a LMT, if my extent size is UNIFORM and it is 1 MB and db_block_size = 8kb (1024kb/8kb = 128 blocks), will the first two blocks out of 128 blocks be used for BMBs? If so, if the segment has grown to become say, 2000 extents, then, 2000*2 = 4000 blocks will be used for BMBs? Thanks.

Tom Kyte

Followup  

December 27, 2004 - 9:52 am UTC

No, not true.  Consider:


ops$tkyte@ORA9IR2> create tablespace assm_test
  2  datafile size 1664k
  3  extent management local uniform size 64k
  4  segment space management auto; 
Tablespace created.

<b>that has room for precisely 25 extents.  25 extents would imply "50 blocks" using your theory.  Let's test that theory:</b>

 
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int, y char(25) ) tablespace assm_test;
Table created.
 
ops$tkyte@ORA9IR2> begin
  2          loop
  3                  insert into t values ( 1, 1 );
  4                  commit;
  5          end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table OPS$TKYTE.T by 8 in tablespace ASSM_TEST
ORA-06512: at line 3

<b>we've filled the table out as big as it is going to get in 25, 64k extents..</b>
 
 
ops$tkyte@ORA9IR2> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             189
Total Blocks............................             200
Total Bytes.............................       1,638,400
Total MBytes............................               1
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              10
Last Used Ext BlockId...................             200
Last Used Block.........................               8
 
PL/SQL procedure successfully completed.

<b>we have 200 blocks allocated to the table  (I'm using an 8k block, 25 extents * 64k (or 8 blocks) = 200 blocks)...

189 of them are "full blocks" -- these are blocks with our data.  Therefore 11 blocks are being used here for "something else"</b>

ops$tkyte@ORA9IR2> select sum(blocks) from user_segments where segment_name = 'T';
 
SUM(BLOCKS)
-----------
        200

<b>just showing 200 blocks..</b>
 
ops$tkyte@ORA9IR2> select count(distinct dbms_rowid.rowid_block_number(rowid)) from t;
 
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                189

<b>and we have rows on 189 of them -- leaving 11 used by Oracle to manage the space</b>
 
ops$tkyte@ORA9IR2> select count(*) from user_extents where segment_name = 'T';
 
  COUNT(*)
----------
        25
 
ops$tkyte@ORA9IR2>
 

BMBs cont

December 27, 2004 - 12:37 pm UTC

Reviewer: reader

That was very good. Thanks. How do I know beforehand how many blocks Oracle would use for "something else"? If I have a 10G table in LMT/ASSM, I want to know how many extra blocks Oracle needs to manage space using BMBs so that I can make sure I have enough space on the disk? Is there a formula or ROT? I don't want to load 10G data and query as you did to find out how many additional blocks for BMBs? Thanks. You are awesome.

Tom Kyte

Followup  

December 27, 2004 - 12:49 pm UTC

undocumented, uncontrollable, will vary with different releases as the technology grows over time.


how do you know for non-ASSM? a number such as 999999999999 takes more storage than 999999999999+1 does. the expansion of ITL's will affect the number of rows per block you get, etc.


I load a representative amount of data (representative -- lots of representative numbers, strings, etc) -- somewhere between 0.01 and 10% of the data (bigger the set, the less you need). gather stats on it and mulitply.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:406873025890 <code>

show_space

December 27, 2004 - 1:47 pm UTC

Reviewer: reader

Could you paste your script for show_space proc that you used above? thanks.

Tom Kyte

Followup  

December 27, 2004 - 2:49 pm UTC

search for

"show_space"

might want to read from bottom up, it's been enhanced over time.

I found the link for show_space script.... never mind

December 27, 2004 - 1:50 pm UTC

Reviewer: reader


uniform vs assm,

February 23, 2005 - 4:02 pm UTC

Reviewer: sns from austin,tx

I have a small doubt between locally managed tablespace with uniform extent growth
and locally managed tablespace with ASSM.

CASE I:
create tablespace USERS
datafile '/fsys3/ORACLE/USERS/users01.dbf' size 50M
extent management local uniform SIZE 1M;

CASE II:
create tablespace ts_name
datafile '/fsysx/ORACLE/SID/ts_name01.dbf' size 100M
extent management local -- enable LMT
segment space management auto -- enable ASSM


In the first case, I am sure the extent growth is always 1M and chances of tablespace
becomming fragmented is zero.
In the second case, does Oracle determines the next extent size depending on the object growth?
Is there a chance tablespace becoming fragmented in the second case?

Are there any differences between the two?

Can the value of "bytes" in dba_free_space be less than "next_extents" in dba_segments
for a locally managed ASSM tablespace?

Say I have a big table (10Million rows) and I keep doing delete and insert often (busines
requirement). Deletes and Inserts happens in only one session.

In case 1: After delete operation completes, I believe lot of blocks become empty. Does
oracle uses the space that was released for future inserts? If yes, what percentage of blocks
will be used?

Same question for case II.

For the above situation what kind of tablespace is good CASE I or CASE II?

Thanks a lot,


Tom Kyte

Followup  

February 24, 2005 - 5:27 am UTC

<quote>
I have a small doubt between locally managed tablespace with uniform extent
growth and locally managed tablespace with ASSM.
</quote>

I'm quoting that before I read the rest -- the two are not "comparable", they are unrelated concepts.

a Locally managed tablespace (LMT) can either use MANUAL segment space management or AUTO segment space management.

an LMT can be either MSSM or ASSM.


You can however compare:

UNIFORM extents vs
System allocated extents.

If you have access to "Effective Oracle by Design", I go into much more detail there -- but basically, I really like system allocated extents when I don't know precisely how an object will grow or how big it'll get. It'll take about 300 and some odd extents by the time it is 10gig in size (at which point in time, I'm thinking about partitioning anyway so the largest segments would be 10gig and about 300 and some odd extents in size)

with system allocated extents there will be a few extent sizes (not an infinite number of them) and they are all multiples of eachother so the concept of fragementation is mostly avoided. It really won't happen unless you drop and create and drop and create and drop and create and ...... over and over. And even then, it is unlikely you would suffer from any sort of massive fragmentation over time.


Oracle will reuse free space in a table - definitely. We'll either have you tell us how to do it (MSSM -- you set pctfree, pctused, freelists, freelist groups, etc) or we do it ourselves (ASSM). All or none of the blocks may be available for reuse, totally depends on how you've set it up.








gaozhiwen from china

May 09, 2005 - 10:43 pm UTC

Reviewer: gaozhiwen from china

Power tom!I am a senior dba!I read some article about freelists in your site !So I know some about it!Before Oracle9i,When I notice some "data block " in V$WAITSTAT ,I can try to use "alter table tname storage (freelists n)"!But My database version is 9.2.0.3!I create some tablespace and use "auto segment management auto" feature except "system,temp,undo tablespace"(because until now I learn much about segment management auto,I can't to rebuild db)!I notice a very high digit in V$WAITSTAT about "data block",I want to know whether I should investigate it !
I know my english expression is very pool!But I hope I can make you understand my question!

SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
SYSTEM                         MANUAL
UNDOTBS1                       MANUAL
TEMP                           MANUAL
DRSYS                          AUTO
INDX                           AUTO
TOOLS                          AUTO
USERS                          AUTO
XDB                            AUTO
BASEDATA                       AUTO
QM                             AUTO
JLW                            AUTO
OTHER                          AUTO
PERFSTAT                       MANUAL
UNDOTBS                        MANUAL
JLW1                           AUTO

SQL> select * from v$waitstat;
data block            5490075    4215767
sort block                  0          0
save undo block             0          0
segment header            710        408

SQL> select startup_time,sysdate from v$instance;
19-APR-05 10-MAY-05

Thanks very much !


 

Your opinion

January 25, 2006 - 7:05 am UTC

Reviewer: Anil Pant

Hello Tom,
I do not know whether this is the correct thread to ask this question. I was looking at our clients database structure and Iam bit skeptical about the structure. I need your opinion on this.
The database has some tables which stores the metadata of the customer related tables. Some of the tables pertaining to a specific type of customer are created when that customer is created in the front-end. Since I did not fully understand why they do so, I cannot write more on this.

My question, is it a better approach to keep the metadata info and the customer tables in the same schema. Instead they could have created a seperate schema for the metadata information like the Oracle's data dictionary tables in SYSTEM tablespace.

Thanks
Anil Pant

Tom Kyte

Followup  

January 25, 2006 - 1:39 pm UTC

I see nothing wrong with using multiple schema's to organize your objects.

What if tablespace has been created with manual option...?

October 23, 2006 - 5:17 pm UTC

Reviewer: Star Nirav from INDIA

Dear TOM,

we are using 92070 and some days back i have found that mine juniors have created tablespace with manual option in automatic_space_management.

Few questions :
1) What is the default value while creating tablespace ?
2) How to convert to auto from manual and vice-versa ?
3) Manual mode is not at all beneficial ??
4) if I compress the tablespace...? is there any impact wrt this clause ?
5) What is DEF_TAB_COMPRESSION used for in dba_tablespaces ?
6) and in all the cases, allocation_type shows SYSTEM only. Whhy and wht is the benefit ?


Anticipation ur response...

Pls. answer...

October 25, 2006 - 7:09 pm UTC

Reviewer: Star Nirav

we are using 92070 and some days back i have found that mine juniors have
created tablespace with manual option in automatic_space_management.

Few questions :
1) What is the default value while creating tablespace ?
2) How to convert to auto from manual and vice-versa ?
3) Manual mode is not at all beneficial ??
4) if I compress the tablespace...? is there any impact wrt this clause ?
5) What is DEF_TAB_COMPRESSION used for in dba_tablespaces ?
6) and in all the cases, allocation_type shows SYSTEM only. Whhy and wht is the
benefit ?


Anticipation ur response...


Tom Kyte

Followup  

October 25, 2006 - 9:47 pm UTC

in manual option with automatic....

my head spins.

I have not a single idea what you might be meaning by that.

And if they are your "juniors", why are they doing things you don't approve of?

They have created using manually command

October 26, 2006 - 4:29 pm UTC

Reviewer: Star nirav from India

Hi Tom,

Actually I have approved and I thought they are now in that position to create one tablespace, i didnt know that they have created with manual mode in auto_space_mangmt.

Would like to know that whether by default is manual while creating tablespace ?

If created then can we change to auto ?


Thanks,
Star Nirav

Tom Kyte

Followup  

October 27, 2006 - 7:24 am UTC

I have NO CLUE what you mean by "they have created with manual mode in auto_space_mangmt"

you are making up terminology here. Or are confused. How can it be "manual" yet "automatic"???

Default option is manual... Right ?

October 31, 2006 - 2:16 pm UTC

Reviewer: Star Nirav from India

Hey Tom,

The default is manual so if we dont explicitly mention, tablespace would created with manual mode.

Now I just wanted to convert into automatic mode... How ?

Regards,

Tom Kyte

Followup  

October 31, 2006 - 4:10 pm UTC

default is auto in current releases.

ops$tkyte%ORA10GR2> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces
  2  where tablespace_name = 'T';

SEGMEN
------
AUTO


help us out here, you have written (this is YOUR writing)

<quote>
i didnt know that they have created with manual mode in 
auto_space_mangmt. 
</quote>

please parse that sentence for us and tell us what it means.


You do not convert manual segment space managed tablespaces into automatic - you have to CREATE a new tablespace with segment space management auto and move the segments you want into it. 

Clarification given...

November 01, 2006 - 4:01 pm UTC

Reviewer: star Nirav from India

Dear Tom,

Pls. find the output of database (9.2.0.7.0).

SQL> select * from v$version;
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production


SQL> select tablespace_name, segment_space_management from dba_tablespaces;

SYSTEM                         MANUAL
UNDOTBS1                       MANUAL
USERS                          MANUAL
UNDOTBS2                       MANUAL
TEMP1                          MANUAL
USER2                          MANUAL
INDX3                          AUTO

Pls. find the version of database and check SYSTEM, UNDOTBS*, USERS and TEMP1 tablespaces are in Manual mode.

Along with that, please provide answers of my few questions.

1) What is the default value while creating tablespace ?
2) How to convert to auto from manual and vice-versa ?
3) Manual mode is not at all beneficial ??
4) if I compress the tablespace...? is there any impact wrt this clause ?
5) What is DEF_TAB_COMPRESSION used for in dba_tablespaces ?
6) and in all the cases, allocation_type shows SYSTEM only. Whhy and wht is the 
benefit ?

hope now, I am able to give you exact output which you have asked for... 

Regards,
Star Nirav 
 

Tom Kyte

Followup  

November 01, 2006 - 6:32 pm UTC

1) documentation is always useful isn't it.... in 9i, it was manual

2) did you read my words above? the ones that answered that, it is the last paragraph.

3) sure it is, else it would not exist. Automatic segment space management is designed to WASTED SPACE to give INCREASED CONCURRENCY. So, for example, lobs might not benefit from it, data warehouses would not typically either.

4) tell me how you compress a tablespace first, no such concept?

5) that is the.... oh wait, documentation to the rescue:
</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4157.htm#sthref2721

6) system allocated extent sizes, instead of UNIFORM, we'll pick the size we want extents to be.  feel free to read around on them:
http://asktom.oracle.com/pls/ask/search?p_string=%22system+allocated+extents%22 <code>



(you never answered my question - nope, not at all. I was very direct with my question and you skipped it. You added lots of new questions never before asked, but I'm sure no one will notice that)

Thanks tom but...

November 03, 2006 - 3:43 pm UTC

Reviewer: Star Nirav from India

Actually, that was spelling mistake, was trying to say that they have created tablespace with segment_space_mangement manual mode. I was actually surprized when I did auditing.
Just wanted to know the business / performance impact with this option. Anyways, thanks.

compress tablespace is 10g concept. I was thinking that if i want to remove fragmentation then which option would help me... ?
you said that in 9i MANUAL is default option then why index3 tbs is in AUTO mode. ?

Rgds,
Star nirav

Tom Kyte

Followup  

November 04, 2006 - 12:16 pm UTC

segment compression was added in Oracle 9ir2.

tablespaces are not compressed

tablespaces have default attributes that segments created in them will inherit if you do not specify otherwise, eg: pctincrease, next, initial and so on.

There is no such thing as a compressed tablespace.



ASSM in 10g

December 05, 2006 - 12:39 pm UTC

Reviewer: Ben from Minneapolis

I came across this article from Burleson spelling out the pros and cons of ASSM, the cons seem to suggest one would not use ASSM in a DW or mart, Since this feature is fairly new I wonder if the cons have been addressed in 10g? we are planning a migration from 9i cooked filesystems to 10gR2 ASM, my initial thought was to go to bigfile tablespaces (ASSM by default) for all the databases but now Im not so sure....

Pros of ASSM:

Varying row sizes: ASSM is better than a static pctused. The bitmaps make ASSM tablespaces better at handling rows with wide variations in row length.

Reducing buffer busy waits X "buffer busy waits" : ASSM will remove buffer busy waits better than using multiple freelists. When a table has multiple freelists, all purges must be parallelized to reload the freelists evenly, and ASSM has no such limitation.

Great for Real Application Clusters: The bitmap freelists remove the need to define multiple freelists groups for RAC and provide overall improved freelist management over traditional freelists.

Cons of ASSM:

Slow for full-table scans: Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM FTS tablespaces are consistently slower than freelist FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications unless partitioning is used with Oracle Parallel Query.

Slower for high-volume concurrent insertS: Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple freelists.

ASSM will influence index clustering: For row ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap freelists are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor and the cost-based optimizer's propensity to favor an index range scan.



Tom Kyte

Followup  

December 06, 2006 - 9:22 am UTC

assm is designed to waste space to increase concurrency. it is an OLTP feature. In a non-concurrent modification environment, such as a warehouse, it's relevancy is reduced.

Thanks for the response

December 06, 2006 - 10:24 am UTC

Reviewer: Ben from Minneapolis

It's too bad I was really looking forward to no more file management with bigfile tablespaces and ASM, after all a file is just a logical entity now with ASM and really has no useful purpose that I can see.

Tom Kyte

Followup  

December 07, 2006 - 8:39 am UTC

eh???



To Ben: ASM and ASSM are two different features

December 06, 2006 - 8:27 pm UTC

Reviewer: Roderick

ASM = Automatic Storage Management
ASSM = Automatic Segment Space Management

misunderstood

December 06, 2006 - 9:02 pm UTC

Reviewer: Ben from Minneapolis

Yes I realize that, my point was I wanted to use bigfile tablespaces which gives you ASSM by default (no choice there) to use BFT I need ASM. But since using ASSM with a DW or mart is probably not advisable, it means I will need to use smallfile tablespaces in my ASM environment, hence I will need to continue to have to use datafiles which in ASM really serve no purpose.

Tom Kyte

Followup  

December 07, 2006 - 9:43 am UTC

you still are not really making sense.

ASM does serve a purpose - regardless of big files or not.

To: Ben

December 07, 2006 - 9:29 am UTC

Reviewer: Tom Fox from Cincinnati, OH

ASM is just a filesystem, no more, no less. You still use datafiles with ASM, but you do not need to specify a datafile name; it can be generated automatically.

You can still create a bigfile tablespace in ASM:

sys@ora10gr2> create bigfile tablespace tom_data datafile '+VGDB001' size 50M;

Tablespace created.

Elapsed: 00:00:01.32

Maybe I'm missing something here.

BFT and ASSM

December 07, 2006 - 10:29 am UTC

Reviewer: Ben from Minneapolis

It is really simple you cannot use BFT without using ASSM

So I cannot use BFT's in my DW since ASSM with a DW is not a good idea.

SQL> create bigfile tablespace junk datafile size 100M segment space management manual;
create bigfile tablespace junk datafile size 100M segment space management manual
*
ERROR at line 1:
ORA-32772: BIGFILE is invalid option for this type of tablespace
 
I don't want to use smallfile tablespaces and several datafiles, don't need them in ASM, don't want them, why would you? 

Tom Kyte

Followup  

December 07, 2006 - 1:12 pm UTC

so, why does that make "asm" useless - that is what I'm not getting.


it appears you are mad, you don't like something - so be it, but that doesn't make stuff useless.

Follow up

December 07, 2006 - 10:46 am UTC

Reviewer: Ben from Minneapolis

I'm not sure how you got that didn't think ASM doesn't serve a purpose I only said the concept of a "file" doesn't serve a purpose. one tablespace one file = BFT having multiple files for one tablespace in ASM really makes no sense anymore.

Tom Kyte

Followup  

December 07, 2006 - 1:14 pm UTC

<quote>
to use BFT I need ASM.
...
hence I will need to continue to
have to use datafiles which in ASM really serve no purpose.
</quote>

I just read what you wrote, that is all

Re

December 07, 2006 - 1:30 pm UTC

Reviewer: Ben from Minneapolis


No not mad just a little frustrated for being misunderstood I guess...

What would be the purpose of having multiple files for one tablespace in ASM? maybe I should take that approach.

useless: several files for one tablespace in ASM
usefull: ASM


Tom Kyte

Followup  

December 07, 2006 - 1:48 pm UTC

the purpose would be to make the tablespace as big as you wanted? Just like any other file system? ASM is just a file system, like UFS is just a file system, like NTFS is just a file system.


useful: several files for one tablespace in ASM, just like UFS, RAW, OCFS, NTFS, .....

Re

December 07, 2006 - 2:32 pm UTC

Reviewer: Ben from Minneapolis

I can't seem to get my point across so I'll just leave it at that it isn't that important.

Thanks for answering the original post.

Let me see if I have Ben's point here.....

December 07, 2006 - 5:27 pm UTC

Reviewer: Mark J. Bobak from Ann Arbor, MI

I think Ben is saying the following:
1.) I have a DW that I'm ready to move to 10g.

2.) I was considering using ASM. One of the reasons I was considering ASM was to be able to combine the feature with bigfile tablespaces, which would allow me to stop having to grow datafiles or add datafiles over time. With bigfile tablespaces, I can have a large pool of disk allocated to ASM, and allow each tablespace to grow on demand, consuming space from that pool of ASM storage. That way, I only need to monitor the space left in the ASM disk group. When it starts to run low, allocate more space for the ASM diskgroup.

3.) In order to use the bigfile tablespaces, I *must* use ASSM (<---note there's two 'S's there.) But, the article referenced mentions some potential down sides to having ASSM with a DW database.

So, what I *think* Ben is saying is:
"I want to use ASM to ease my storage management activities, but the most effective way to do that is to also use bigfile tablespaces, but in order to use bigfile tablespaces, I *have* to use ASSM, but, it sounds like ASSM might be a bad idea for DW."

So, the question is: "If I go to 10g and ASM, can I make my tablespaces ASSM, so that I can use the bigfile tablespace feature, to make storage management easier, or is ASSM really a bad idea for a DW?"

I think the confusion arose cause Ben may have mixed usage of ASM and ASSM inadvertently....

Anyhow, hope that clears up the situation.

-Mark

(And apologies to Ben if I got it wrong.)


Yes I think that was part of it, Thanks Mark

December 08, 2006 - 10:51 am UTC

Reviewer: Ben from Minneapolis

My only point really ( at the risk of opening this really pointless thread again ) was in ASM a file is really only a file because that is what you asked for, it is a really only a meta data entry not a real file in the classical sense i.e. I can't do any of following cp, mv, cmp, dd ( well maybe, if I got the blocking factors correct, but not officially supported ) so really I was only looking forward to not having to do the typical datafile type management anymore. i.e. being able to do everything at the tablespace level (alter tablespace resize xxx ).
Well that's it, nothing grandiose, just one thing to make my day to day life as a DBA a little simpler. Was just a little disappointed I couldn't do it because of a performance factor based on functionality of my database.

I appologize for wasting anyone's time....

Tom Kyte

Followup  

December 09, 2006 - 12:31 pm UTC

it is a file in the real classical sense, just as much as any file in any file system is a real file.

ASM is a FILE SYSTEM.


so what if you cannot do somethings to them you can do on other file systems. You know - I cannot create a sparse file on NTFS - does that mean NTFS is not a filesystem?



I promise to never reopen this thread again

December 09, 2006 - 1:00 pm UTC

Reviewer: Ben from Minneapolis


Tom Kyte

Followup  

December 09, 2006 - 2:43 pm UTC

Ok, not a problem...

ASM is just a file system, a file system usable for files produced and consume by Oracle. It is just a file system none the less. There be files in that there file system. (heck, you can even ftp in and out of it! Put a file in, get a file out - must be a file system there somewhere)

ASSM bad for DW?

December 14, 2006 - 11:46 am UTC

Reviewer: A reader

Do you agree with that analysis?

Tom Kyte

Followup  

December 15, 2006 - 8:25 am UTC

the problems ASSM attempts to solve are not present in a typical data warehouse.

Manual to Automatic

December 18, 2006 - 3:32 am UTC

Reviewer: A reader

I am running oracle 9.2.0.5 with compatible parameter set to 9.2.0. All the tablespaces are with SEGMENT_SPACE_MANAGEMENT set to manual.

[1] Can I alter my tablespaces to start using from manual SEGMENT_SPACE_MANAGEMENT to auto.

[2] If yes, what will be the effect on existing objects in the tablespace.

Tom Kyte

Followup  

December 18, 2006 - 8:22 am UTC

no, you need to rebuild the segments. You have to get rid of the freelists and introduce bitmap blocks in the segment itself.

reader

May 26, 2007 - 6:16 pm UTC

Reviewer: A reader

I have two databases using 11i application

one of them have a table in 9i databasae MANUAL egment Space Management (1)

Another 10g database. The table uses ASSM (2)

The table is very small 20MB in size

1. When inserting a single row into this table from FORM takes 1/2 second in (1). The same insert from FORM takes 4 seconds in (2)

2. In (2), the first time the insert is done it takes 30 seconds, Subsuent insert into (2) takes 4 seconds

3. When I cache the table (2). The time taken is 1/2 second in both first time insert and subsequent inserts. This solution is not viable since the table has a potential to grow very large

I like to know what could cause the difference in performance of MANUAL space management table (1) and ASSM managed same table (2). What type of approach I can take to analyze this situation.




Tom Kyte

Followup  

May 27, 2007 - 9:15 am UTC

1) then you have a serious issue on both systems, if it takes more than a few 1/100's of a seconds, something is wrong.

How are you measuring this?

2) you have something really wrong here - and it goes way beyond MSSM and ASSM. The database is not this slow, something else it.

enable sql_trace, do the insert, you'll see the time spent in the database is very very small.

3) that has nothing to do with it, more likely, you just warmed things up. 'cache' simply changes what happens when we do a full scan on the table and how the blocks are cached, won't affect your single row insert.


Please use tkprof and sql_trace so you can rule out the database and start looking elsewhere - you know, where the problem really lies - which won't be in the database I am confident.

reader

May 27, 2007 - 1:19 pm UTC

Reviewer: A reader

The timing is determined by counting 1001, 1002, 1003 ....

When I set the trace (with waits) at the form itself
help => trace ..., it completes in sub second which is what is mind boggling

I'll try a dbms_system.set_ev quickly enough to capture the trace

ASM

November 22, 2007 - 6:29 am UTC

Reviewer: A reader

Tom,
How do we drop a tablespaces in 10g ASM If I would to drop and the reclaim the space in one of the 10g databases. Currently, we are having a 9i database on storage device. So, if had to drop any tablespace. We could only drop the tablespace and reuse that datafile. Please can you explain this in 10g ASM. Thanks in advance.

Tom Kyte

Followup  

November 26, 2007 - 10:43 am UTC

you can do exactly what you are used to doing right now.

you could drop tablespace t including contents and datafiles; as well - or you can use ASM to 'erase' the files or reuse them.

ASM is just a database file system.


http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/storeman.htm#sthref1814

Unable to save pciture

December 11, 2007 - 1:51 am UTC

Reviewer: Nazmul Hoque from Bangladesh

Dear Tom,

I am unable to save picture in my database (8.1.5), gettinh beloow error :

ORA-01691: unable to extend lob segment HRM.SYS_LOB0000012288C00028$$ by 52596 in tablespace SYSTEM

I am useing this software for this for long time where i can save picture also with other information of a person, recently i am facting problem to save picture , where as i can save data except picture. my dmp file size is 248 MB,

Please help to solve the problem.

Tom Kyte

Followup  

December 11, 2007 - 11:07 am UTC

well, it is fairly obvious what is happening here - you, well, don't have anymore free space in your tablespace (which is unfortunately SYSTEM - you should NOT have used that one, that is our data dictionary, but whatever)

have your DBA correct this. If you are the DBA, you need to add space - there are many ways to do that, from adding a file to the tablespace to resizing an existing file.

A bit of a typo

December 11, 2007 - 10:21 am UTC

Reviewer: Greg from Reston, VA

> (which is unfortunately SYSTEM - you should have used that
> one, that is our data dictionary, but whatever)

Forgot the *not* in there...
Tom Kyte

Followup  

December 11, 2007 - 11:07 am UTC

thanks, fixed!

Alter tablespace of System

December 12, 2007 - 5:47 am UTC

Reviewer: Nazmul Hoque from Bangladesh

Hi Tom,

Yes, Problem is system tablesapce used by oracle only, user are create table as rquired. Please tell me can use same command for system also say

alter tablespace system add datafile 'D:\ORACLE\ORADATA\SCLDATA\system02.dbf' SIZE 200M
AUTOEXTEND ON NEXT 1M;

Please advise

Thanks
Tom Kyte

Followup  

December 12, 2007 - 9:09 am UTC

your sentence:

... Yes, Problem is system tablesapce used by oracle only, user are create table as
rquired....

does not compute.

You say "system is used by oracle only"

But your error clearly shows a non-oracle table in system.

Please work with your DBA to correct this issue, they will know what to do.

Add a data file in the Tablespase

December 13, 2007 - 12:50 am UTC

Reviewer: Nazmul Hoque from Bangladesh

Thanks for reply,

Please note i working as a programmer and side by side I have to solve this type of problems as i don't have any DBA with me from whom i can get support. Can you please help me to find the table which is under system tablespace, but it should be under users tablespace.

It will help me lot to solve the problem.

Thanks
Tom Kyte

Followup  

December 13, 2007 - 9:48 am UTC

just add space to system and continue as you were.

but now I'm worried about your backup scripts and other stuff... Make sure your scripts are flexible enough to get this new file.

Or resize the existing file larger (research the ALTER DATABASE command for that)

Alter Tablespace - System

December 14, 2007 - 1:34 am UTC

Reviewer: Nazmul Hoque from Bangladesh

Dear Tom,

I have alter system tablespace and now my work is going. enclosed a list user/tablespace for your referance

USERNAME TEMPORARY_TABLESPACE DEFAULT_TA
------------------------------ ------------------------------ ----------
SYS SYSTEM SYSTEM
SYSTEM TEMP USERS
OUTLN SYSTEM SYSTEM
DBSNMP SYSTEM SYSTEM
MTSSYS SYSTEM SYSTEM
AURORA$ORB$UNAUTHENTICATED SYSTEM SYSTEM
SCOTT TEMP USERS
DEMO SYSTEM SYSTEM
ORDSYS SYSTEM SYSTEM
ORDPLUGINS SYSTEM SYSTEM
MDSYS SYSTEM SYSTEM
CMM TEMP SCLDATA
CTXSYS SYSTEM SYSTEM
HRM TEMP SCLDATA

Please note i have take bakcup of data by exp only and my dmp file size is 250MB. I am useing one one user name for my software and that is HRM

Please advise to avoide the futher problems.

Thanks

Tom Kyte

Followup  

December 14, 2007 - 1:11 pm UTC

... Please note i have take bakcup of data by exp only ..

you mean "i have never ever taken a backup of my database - ever, I have this dmp file that makes me feel good, but I have no idea if it is even usable"

I advise you consult a DBA and have them review your system and set it up to be recoverable.

ASSM block overhead

April 29, 2008 - 12:52 pm UTC

Reviewer: A reader

Tom,
Is is a fair statement to make that ASSM takes say x% of extra space? I read this thread and you have mentioned a method to find out the ASSM blocks in a table using dbms_space. I wrote a small PL/SQL block and ran it against 100 tables. I found that the percentage of ASSM blocks varied from 60% for tables with 5 blocks or so to 0.1% for tables with hundreds of thousands of blocks. From the results, I would say that making a general statement that ASSM consumes x% of space is totally incorrect. Is that correct or am I totally off the mark?

Thanks

Tom Kyte

Followup  

April 30, 2008 - 7:52 am UTC

... From the results, I would say that making a general statement that ASSM
consumes x% of space is totally incorrect ...


you would be correct in saying that. In fact, you would be pretty correct just saying:

I would say that making a general statement is totally incorrect

:)

Enqueue: HW, Segment High Water Mark - contention

February 17, 2009 - 11:05 am UTC

Reviewer: kg

Hi tom
i was not able to find information about the HWM enqueue wait on your site so posting here.
I read in OEM 10g "Enqueue: HW, Segment High Water Mark" metric help to use LMT to avoid HW enqueue.

Can you explain how LMT is avoiding/reducing HW enqueue?


Regards
Tom Kyte

Followup  

February 17, 2009 - 2:24 pm UTC

How about pointing to the entire thing in context, I don't comment on snippets taken out of context.

I think you mean this:

http://docs.oracle.com/docs/cd/B19306_01/em.102/b25986/oracle_database.htm#sthref1958


Locally managed tablespaces can allocate space (new extents) much more concurrently and rapidly than a dictionary managed tablespace ever could, it reduces the time to bump up the high water mark when a new extent has to be added.

That is why the advice for the old fashioned legacy dictionary managed tablespace was "manually preallocate extents", it was really slow back in the olden days of dictionary managed tablespaces.

ASSM for BLOBs ?

September 23, 2009 - 4:11 am UTC

Reviewer: A reader

Hi Tom

Could you please advice this remark found on one of the OTN Discussion Forums ( http://forums.oracle.com/forums/thread.jspa?messageID=3579688� ):

If you are on 10gR2, don't use ASSM for LOBs until and unless you have 10.2.0.4 -- even then, I believe, there are more patches.

LOB space leakage in 10.2.0.1-10.2.0.3 can be high.

Thx

Stef
Tom Kyte

Followup  

September 28, 2009 - 1:41 pm UTC

well, basically - ASSM (automatic segment space management) is designed to "waste space in order to increase concurrency"

Since you don't really have the type of concurrency issues in lob segments that might have in "regular tables" (space is managed completely differently) - there is something to be said regarding "ASSM and lobs are somewhat at odds with eachother"

As for the posting, it would have been nifty if the poster would have sort of pointed to metalink to back up what they are saying - in short, no, I don't agree with their advice in general - definitely not for the reason stated.

Here is an old note on that
Note 250525.1
affecting 9iR2 but fixed in... 9iR2