Re: How does Oracle set the High Water Mark?

  • From: Vasu Balla <vballa@xxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 10 Mar 2004 19:38:39 +0530


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 

___________________________________________________________________________ 
  

--
-----------------------------------------------------
Vasu Balla
* email : vballa@xxxxxxxxxxx
( phone :+91 40 27893939 X 1291
-----------------------------------------------------
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------

Other related posts: