High Water mark on objects in ASSM Tablespaces

This article discusses about High Water mark on objects in ASSM tablespace and the impact that it can have on database performance.

In order to do a full tablescan ,oracle needs to read all the blocks below high water mark.

In general, when a table or index segment is first created, space for that segment will be preallocated from a datafile but very little of the space will be formatted for use. As data arrives, blocks will be formatted a few at a time.

In the simplest setup, Oracle would format “the next five” blocks from the preallocated space as the need arose, and the object’s high water mark (HWM) would be adjusted to show how many blocks had been formatted and were available for use.

With the arrival of ASSM in 9i, Oracle formats groups of adjacent blocks (typically 16, it seems) at a time.The high water mark still identifies the highest formatted block in the segment, but ASSM randomizes the allocationslightly, so that unformatted holes (16 blocks, or multiples thereof) can appear in the middle of the object.ASSM also allocates one or two bitmap space management blocks per extent.

Creating LMT with extent size uniform
--------------------------------------------------------------
create tablespace TBS_LMT_UNIFORM
datafile '/sbclocal/app/oracle/admin/NADEEM1/data/TBS_LMT_UNIFORM_01.dbf' size 200M reuse
extent management local uniform size 800K segment space management auto;


Create Table Kadhiresan_LMT_UNIFORM
(Empno char(11),
Empname char(20))
tablespace TBS_LMT_UNIFORM;

Creating LMT with Autoallocate
--------------------------------------------------------------
create tablespace TBS_LMT_SYSTEM
datafile '/sbclocal/app/oracle/admin/NADEEM1/data/TBS_LMT_SYSTEM_01.dbf' size 200M reuse
extent management local AUTOALLOCATEsegment space management auto;

Create Table Kadhiresan1_LMT_SYSTEM
(Empno char(11),
Empname char(20))
tablespace TBS_LMT_SYSTEM;

SQL> Analyze table KADHIRESAN_LMT_UNIFORM compute statistics;
Table analyzed.

SQL> Analyze table KADHIRESAN1_LMT_SYSTEM compute statistics;
Table analyzed.

SQL> select Table_name,blocks,empty_blocks from dba_tables where table_name in('KADHIRESAN_LMT_UNIFORM','KADHIRESAN1_LMT_SYSTEM');

TABLE_NAME BLOCKS EMPTY_BLOCKS
============================== ========== ============
KADHIRESAN_LMT_UNIFORM 0 100
KADHIRESAN1_LMT_SYSTEM 0 8

SQL> insert into KADHIRESAN_LMT_UNIFORM values('1','KADHIRESAN');
1 row created.
SQL> insert into KADHIRESAN1_LMT_SYSTEM values('1','KADHIRESAN');
1 row created.
SQL> commit;

SQL> Analyze table KADHIRESAN_LMT_UNIFORM compute statistics;
Table analyzed.

SQL> Analyze table KADHIRESAN1_LMT_SYSTEM compute statistics;
Table analyzed.

SQL> select Table_name,blocks,empty_blocks from dba_tables where table_name in('KADHIRESAN_LMT_UNIFORM','KADHIRESAN1_LMT_SYSTEM');

TABLE_NAME BLOCKS EMPTY_BLOCKS
============================== ========== ============
KADHIRESAN_LMT_UNIFORM 23 77
KADHIRESAN1_LMT_SYSTEM 5 3

SQL> Select * from KADHIRESAN_LMT_UNIFORM;
Statistics
==========================================================
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

1 rows processed

SQL> Select * from KADHIRESAN1_LMT_SYSTEM;
Statistics
==========================================================
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
sorts (memory)
0 sorts (disk)

1 rows processed

After Table has been Analyzed ,the "BLOCKS" field in USER_Tables will contain values.The value represents no of blocks held in the table -that is number of blocks from start of table and the high water mark.The field "Empyt_blocks" holds the number of blocks allocated to the table that are above the high water mark.

Difference Controlfile and Backup Controlfile

Operations during backup controlfile.
--------------------------------------------------------------------------------
1) Enqueue control file resource is held on controlfile so that no changes happen.
2) Checkpoint and SCN is till the time of backup.
3) Copy of Controlfile.

There are mutilple ways to perform backup activity of controlfile.
1) Shutdown database and O/S Copy controlfile in different location.
2) Alter database backup controlfile to trace -- Create a text file.
3) Alter database backup controlfile to '######################';

When Database is offline(normal/Immediate), controlfile backup will be consistent and stop scn will be checkpoint scn.
When Database is offline(abort), controlfile backup will be consistent and stop scn will be set to infinity.
When Database is online , if we take backup of controlfile , controlfile will be consistent till that point in time and stop scn will be infinity.

How does Oracle know, wheather controlfile was backup controlfile.
1) Checkpoint is older then checkpoint value on datafile, if controlfile backup was older than datafile backup.
2) Filetype of backup controlfile is 4 , whereas filetype of controlfile is 1.


Dump of header of controlfiles. Online Controlfile
------------------------------------------------------------
Compatibility Vsn = 169869312=0xa200000
Db ID=1418608916=0x548e4114, Db Name='PRASAD1'
Activation ID=0=0x0
Control Seq=212=0xd4, File size=920=0x398
File Number=0, Blksiz=16384, File Type=1 CONTROL

Backup Controlfile
-------------------------------------------------------------
Compatibility Vsn = 169869312=0xa200000
Db ID=1418608916=0x548e4114, Db Name='PRASAD1'
Activation ID=0=0x0
Control Seq=215=0xd7, File size=920=0x398
File Number=0, Blksiz=16384, File Type=4 BACKUP CONTROL

Just for Info:
---------------------------------------------
FileType=1 === > CONTROLFILE
FileType=2 === > LOGFILE
FileType=3 === > DATAFILE
FileType=4 === > BACKUP CONTROLFILE
FileType=4 === > Futuristic use
FileType=6 === > TEMPFILE