Re: How does Oracle set the High Water Mark?

From: Vasu Balla <vballa_at_triniti.com>
Date: Wed, 10 Mar 2004 19:38:39 +0530
Message-ID: <404F2167.6080905@triniti.com>

i find out HWM the following way

select
    table_name ,
    (us.blocks - ut.empty_blocks -1) as HWM,
    us.blocks - 1 BLOCKS,
    ut.empty_blocks EMPTY
from
    user_segments us,
    user_tables ut
where
    us.segment_name= ut.table_name
order by
    HWM desc
    EMPTY desc
/

hope this helps you understand HWM.

Vasu Balla

Feighery Raymond wrote:

Oracle 9.2.0.4 Solaris 8

How does Oracle set the HWM? In the example below, I create a table and
insert one row. I would expect only one block to be used, instead Oracle
seems to have pushed the HWM up to 13 blocks. I can imagine that this might
be done to "pre-mark" rows as used during bulk inserts, but how does Oracle
decide where to set the HWM?

SQL> select * from dba_tablespaces where tablespace_name = 'EXAMPLE'; 
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS 
------------------------------ ---------- -------------- -----------
----------- 
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN 
----------- ------------ ---------- --------- --------- --------- ---
---------- 
ALLOCATIO PLU SEGMEN DEF_TAB_ 
--------- --- ------ -------- 
EXAMPLE 4096 65536 1 
2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL 
SYSTEM NO AUTO DISABLED 

SQL> create table t (col1 number(1)); 
Table created. 
SQL> analyze table t compute statistics; 
Table analyzed. 
SQL> insert into t values(1); 
1 row created. 
SQL> commit; 
Commit complete. 
SQL> select blocks, empty_blocks from user_tables where table_name = 'T'; 
BLOCKS EMPTY_BLOCKS 
---------- ------------ 
0 16 
SQL> analyze table t compute statistics; 
Table analyzed. 
SQL> select blocks, empty_blocks from user_tables where table_name = 'T'; 
BLOCKS EMPTY_BLOCKS 
---------- ------------ 
13 3 

___________________________________________________________________________ 
  

---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- Received on Wed Mar 10 2004 - 08:15:09 CST