High Water Mark in Oracle

Many a time in performance issues High Water Mark is termed as a culprit. So what is this “High Water Mark” (HWM)? Let us take a look.
If you have been to the shores, you would have noticed junk (mostly sea weeds) making a sort of line along the length of the shore. This is the highest level in the shore to which sea water has ever risen. Similarly, in an Oracle table, High Water Mark can be termed as the highest level to which data was ever occupied in a table. Given below is a simple pictorial representation of the same.
Assume that you have created a table “example”, and have not inserted any rows. At this moment of time, the HWM will be the first extent.
Now let’s assume that you have inserted 10000 rows to this table. The HWM is incremented for each block used. The current HWM is depicted in red.
If you delete say 7000 records from the examples table, the amount of blocks used will come down, but the HWM will be at the same position i.e. the highest level to which data was ever present.
The following query will give you the empty blocks below HWM for a table.
  WHERE TABLE_NAME = ‘:table_name’
(SELECT COUNT( DISTINCT dbms_rowid.ROWID_RELATIVE_FNO (rowid)||'_'|| dbms_rowid.ROWID_BLOCK_NUMBER(rowid))
   FROM :table_name
A very high value for HWM can be a performance issue in many cases. A custom program written by the IT team of my client was taking days to finish. The performance went from bad to worse day by day. I was asked to fix this. The execution plan showed me that a Full Table Scan (FTS) on a custom table was the bottleneck. When I checked the table for stats and indexes present, I was surprised to see that it contained 0 rows! I immediately checked the HWM and it was a very unrealistic value. On checking the code for the program, I found that the developer inserted data into this table and then deleted them by the end of the execution. However, the HWM for this table would have kept on increasing with every run of this program.
The above program took hours to complete because when Oracle does a FTS on any table, it will scan from the first block up to the HWM, so that it goes thro all the blocks which could potentially hold data.
With each run of the program, the HWM was getting higher and higher and oracle had to scan that many blocks. This issue was solved by replacing DELETE at the end of the program with TRUNCATE.

Oracle resets all the stats related to table when the table is truncated. The indexes on this table too are affected. In this process, the HWM is also reset. Another option to reclaim the empty space below HWM is to MOVE the table using “ALTER TABLE table_name MOVE;

If you are in 10g, you could use the shrink command to reclaim the unused space
I have not discussed in detail how HWM can be cured not\r have I dealt with the high HWM and low HWM. I will be covering these in another post shortly


Gather Schema Stats in Oracle EBS 11.5.10

Oracle E-Business Suite (EBS) has a concurrent program called “GATHER_SCHEMA_STATISTICS” which is used to gather stats for the smooth functioning of the CBO (Cost Based Optimizer). It uses FND_STATS, which is basically a wrapper around DBMS_STATS. In this article, I will explain the basic working of the FND_STATS and different options available. I have omitted the explanation on the input parameters which this procedure/program takes and instead have concentrated on the logic which it uses.

The details specified are takes from a 11.5.10 EBS version. But AFSIK, it is equally applicable in 11.5.9 as well.


If options = GATHER

Select list of tables which satisfy the following criteria:

1) dba_tables.iot_type ‘IOT_OVERFLOW’ or iot_type is null

2) Table is not a temporary table

3) It is not an excluded table (determined from fnd_stats_hist and fnd_exclude_table_stats)

It then calls the FND_STATS.GATHER_TABLE_STATS. The functioning of this procedure is described towards the end of this write-up.

If options = GATHER AUTO

1) For db_versions above 8.1 flush db monitoring info from SGA to the dba_tab_monitoring tables. Else use whatever data is available in dba_tab_monitoring tables

2) Select a list of table satisfying the following criteria:

a. dba_tab_modifications.table_owner = upper(schema name)

b. (nvl(dtm.inserts,0)+nvl(dtm.updates,0)+nvl(dtm.deletes,0)) > modpercent*nvl(dt.num_rows,0))/100

Where modpercent (default value = 10) is the threshold level of staleness after which a table should be picked for Stats gathering. This value can be passes from the concurrent program. This is how the wrapper can control the threshold levels as opposed to the DBMS_STATS package where threshold is fixed at 10% .

The logic of the select query is such that there can be some redundant stats gathering – one for the table and another for the partition, when both have stale data. A note says that such scenario will be very rare and the code will be revisited in future if needed.

3) It submits the FND_STATS.GATHER_TABLE_STATS for each table, partition in the list of tables in step 2.

4) GATHER AUTO includes GATHER EMPTY, so stats are gathered on any unanalyzed tables and/or indexes. This is determined from the dba_tab_modifications.last_analyzed = NULL. In case of indexes, it is dba_indexes.last_analyzed = NULL

5) Check if there are any tables in the schema which does not have monitoring enabled. If yes, gather stats for them using 10% and enable monitoring for such tables so that we have data for them in dba_tab_modifications for next time.

For options = LIST AUTO

It writes the list of tables in step 2 to the log file

For options = GATHER EMPTY

It does gather stats for any unanalyzed tables and/or indexes which are not excluded into the log file.

For options = LIST EMPTY

It writes the list of any unanalyzed tables and/or indexes which are not excluded into the log file

If the schema_name = ALL

It will recursively submit FND_STATS.GATHER_SCHEMA_STATS for all schemas satisfying the below conditions:

1) The schema should have a valid entry in the fnd_product_installations table. This means people migrating from 11.5.8, 11.5.9 to 11.5.10, will have to re-register their custom applications in fnd_product_installations for them to be picked by Gather Schema Statistics & schema name = ALL

If schema_name = SYS

It will give error saying – Gathering statistics on the SYS schema using FND_STATS is not allowed. Please use DBMS_STATS package to gather stats on SYS objects.


1) For better performance, tables smaller than small_tab_for_par_thold should be gathered in serial

2) For better stats, tables smaller than small_tab_for_est_thold should be gathered at 100%.

3) If db_versn >= 9.2 then Build up the method_opt if histogram cols are present.

method: = method ||’ ‘|| x.column_name ||’ SIZE ‘||x.hsize — this will give something like FOR ALL COLUMNS SIZE 1

4) If no histogram cols then nullify method, else call it with histogram cols.

5) If version is pre 9.2, use the old method of calling twice.

There are a few other checks which do not have any significance in the current context. The procedure then calls another procedure XXX, which is a Private package that now calls DBMS_STATS.GATHER_TABLE_STATS dynamically depending upon the version of the database. For 8i, dbms_stats is called as before, for higher versions, it is called with the no_invalidate flag.

So, in short, the Gather Schema Statistics concurrent program internally submits DBMS_STATS.GATHER_TABLE_STATS for each eligible table in that particular schema.

Batch script to Check internet availability and renew adapter(s) if doesn’t exist


My PC is on 24 x 7 x 365 & I am a huge downloader. Of late, my internet connection gets disconnected after a while and I have to ipconfig /release and ipconfig /renew to resume my torrents. I wanted to automate this. So, with a little bit of help from google, I figured this out.

create a file r_r.bat in %systemroot% with the following contents

@echo off
ipconfig /release *Hath*
echo The IP address has been released. Waiting to renew…
echo waiting for a minute
ping xyz.boogvgd.bam -w 5000>NUL
ipconfig /renew *Hath*
echo The IP address has been renewed

Here, replace *Hath* with you connection name. For ex. if you internet connection name is “Local Area Connection 1”, you could use *Local Area* OR *Connection 1* etc.
Now create another file chk_sts.bat in %systemroot% with the following contents.

ping -n 1 http://www.google.com > NULL
if errorlevel 1 r_r.bat

Save both the files.

Now go to Start–>Settings–>Control Panel –>Scheduled Tasks –>Add Scheduled Tasks.
Add a new task which will repeat itself every 10 minutes(the timing is upto you) using the chk_sts.bat file as the executable. Voila!!

Incase anyone wants to know the details, leave a comment and i’ll try my best.