When using DBMS_STATS to gather statistics, we need a method to choose which objects to gather statistics for. This is done by the parameter “OPTIONS”. The possible values for this parameter are


Of the above, GATHER AUTO & GATHER STALE confuse many – let’s see what’s the main difference between the two.

GATHER STALE: Oracle gathers statistics on stale objects as determined by looking at the *_tab_modifications views. The estimate percent & degree have to be provided by the user.

GATHER AUTO : Oracle decides which objects to gather fresh statistics for and also decides how to get them. Oracle determines the estimate percent to be used. The parallelism used is based on the init.ora setting. This also used *_TAB_MODIFICATIONS to decide on the eligible candidates.


In 12c, Oracle has done away with most of these and provides only GATHER and GATHER AUTO


Some interesting facts I never know about rows in Oracle and how they are stored.. In my quest to answer this question, I learnt a lot about rows, row blocks, how they are composed of and some oracle anomalies.
I’m glad that I looked at the question and got hooked. I am yet to reproduce and understand the final bit in this post, but will do it when I get a few hours time.

Oracle Scratchpad

Here’s a question prompted by a recent comment on OTN.

I have a table declared as follows (and the ellipsis means repeat the column definitions according to the simplest and most obvious pattern to give you 1,000 columns all of type number(1)):

View original post 215 more words

Identify Current Active Oracle Events in DB

I needed to find out if a certain event was set at the database level on my instance. The READ_EV procedure of DBMS_SYSTEM came to my help. Below is a quick way to find out what are the currently set events for your session (session level). It will report all instance wide events too as they are applicable to session also.

In the below example, event_level will be zero if event is not not set at all.

set serveroutput on

  event_level NUMBER;
  FOR i IN 10000..10999
    IF (event_level > 0) THEN
      dbms_output.put_line('Event '||TO_CHAR(i)||
                           ' set at level '|| TO_CHAR(event_level));
    END IF;

ORA-02049 Timeout: Distributed Transaction Waiting for Lock

Key Facts:

  • This issue occurs when a session that is involved in a distributed transaction waits for another session’s TX enqueue (row lock) for longer than the distributed_lock_timeout value.
  • Any operation that uses a database link, even a query starts a distributed transaction.

In the below POC, if there was a COMMIT after the [select 1 from dual@database_link], the situation would become a normal lock situation – the second session will proceed once there is a COMMIT in the 1st  session. In a situation which were in a session was waiting on a row lock and involved in distributed transaction, it would timeout after the distributed_lock_timeout period. If the transaction was a normal one, sooner or later a DBA will notice the lock and could kill the holding session.


There are two ways to deal with this problem:

  • Increase the distributed_lock_timeout value hoping that the session holding the lock releases it before the timeout.
  • Code the application to capture this ORA error and then issue a TRY AGAIN directive.
  • Another workaround would be to add more COMMIT’s as they tend to reset the distributed transaction.

Identifying the Root Cause:

To identify the session holding the resources, what oracle suggests is to dump a system state trace BEFORE the ORA-02049 occurs.

However, I have devised another approach based on oracle’s original suggestion. The basic idea will be to monitor v$session for sessions waiting on the event ‘enq: TX – row lock contention’. If the seconds_in_wait value for such sessions approaches the distributed_lock_timeout value and the session is involved in a distributed transaction we can find the session holding the lock by joining v$lock and v$session.


Session 1

SQL> update dummy_table set table_name ='UPDATED_DUMMY';       -->A LOCAL UPDATE STATEMENT

24 rows updated.



Session 2

SQL> select 1 from dual@database_link; ------>THIS STARTS THE DISTRIBUTED TRANSACTION




SQL> update dummy_table set table_name ='UPDATED_DUMMY';  ---->THIS IS A LOCAL UPDATE, BUT ERRORS OUT WITH ORA-02049

update dummy_table set table_name ='UPDATED_DUMMY'


ERROR at line 1:

ORA-02049: timeout: distributed transaction waiting for lock



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