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

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, 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.

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.

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?

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"!

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...

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?

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.

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,
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

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

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 

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. 

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)

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 ? 

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 

(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.

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

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?

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

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.

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
 



 

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,
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

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

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.

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.

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>
 

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.

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

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,

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 !


 

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

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...

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...

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

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,

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. 

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 
 

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:

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

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.

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.

ASM = Automatic Storage Management
ASSM = Automatic Segment Space Management

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.

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.

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? 

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.

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

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.

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.)

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....

Do you agree with that analysis?

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.

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.

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

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.

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.

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

Forgot the *not* in there...

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

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

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,
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

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

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