Exchange Partition in Oracle

Some times we faced a need to convert our existing table from non-partition table to a partition table or vice-versa. Or moving a partition of one partition table to another partition. Or make one of the table as a partition of some partition table. Or make a partition of one of the partition table as a separate table.

To deal with this kind of situation, oracle has introduced exchange partition functionality long time a go (I guess from 8i onwards).
We will see a simple example of how to convert a simple non-partition table to a partition table.

1) Create a table, create index on table and gather stats on table and index


ORCL1>create table t as select * from dba_objects;

Table created.

ORCL1>create index t_idx on t(owner, object_name);

Index created.

ORCL1>exec dbms_stats.gather_table_stats('ADVAITD_DBA','T',cascade=>true);

PL/SQL procedure successfully completed.

ORCL1>select count(1) from t;

 COUNT(1)
----------
 14966

2) Create partition table with basic partition


ORCL1>create table pt (
 2  OWNER           VARCHAR2(30),
 3  OBJECT_NAME     VARCHAR2(128),
 4  SUBOBJECT_NAME  VARCHAR2(30),
 5  OBJECT_ID       NUMBER,
 6  DATA_OBJECT_ID  NUMBER,
 7  OBJECT_TYPE     VARCHAR2(19),
 8  CREATED         DATE,
 9  LAST_DDL_TIME   DATE,
 10  TIMESTAMP       VARCHAR2(19),
 11  STATUS          VARCHAR2(7),
 12  TEMPORARY       VARCHAR2(1),
 13  GENERATED       VARCHAR2(1),
 14  SECONDARY       VARCHAR2(1),
 15  NAMESPACE       NUMBER,
EDITION_NAME    VARCHAR2(30)
 16   17  )
 18  partition by range (CREATED)
 19  (PARTITION PT_2011 VALUES less than (MAXVALUE));

Table created.

ORCL1>select count(1) from pt;

 COUNT(1)
----------
 0

3) Exchange the partition with table


ORCL1>alter table pt exchange partition PT_2011 with table T without validation;

Table altered.

ORCL1>select count(1) from pt;

 COUNT(1)
----------
 14966

ORCL1>select count(1) from t;

 COUNT(1)
----------
 0

4) Split the partition and check

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2006', 'DD-MON-YYYY'))
INTO (PARTITION PT_2006,
 PARTITION PT_2011)

Analyze the table

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

Check the number of rows in each partition


ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             6047

Like that split again and check the records

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2007', 'DD-MON-YYYY'))
INTO (PARTITION PT_2007,
 PARTITION PT_2011)

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2007                        TO_DATE(' 2007-12-31 00:00:00'        110
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             5937

Do for rest of the partitions


ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2008', 'DD-MON-YYYY'))
INTO (PARTITION PT_2008,
 PARTITION PT_2011)

ALTER TABLE pt
SPLIT PARTITION PT_2011 AT (TO_DATE('31-DEC-2010', 'DD-MON-YYYY'))
INTO (PARTITION PT_2010,
 PARTITION PT_2011)

exec dbms_stats.gather_table_stats('ADVAITD_DBA','PT',cascade=>true);

ORCL1>select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'PT';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
PT                             PT_2006                        TO_DATE(' 2006-12-31 00:00:00'       8919
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2007                        TO_DATE(' 2007-12-31 00:00:00'        110
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2008                        TO_DATE(' 2008-12-31 00:00:00'        250
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                       NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------

PT                             PT_2009                        TO_DATE(' 2009-12-31 00:00:00'        312
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2010                        TO_DATE(' 2010-12-31 00:00:00'        163
 , 'SYYYY-MM-DD HH24:MI:SS', 'N
 LS_CALENDAR=GREGORIA

PT                             PT_2011                        MAXVALUE                             5212

6 rows selected.

Regarding the index, we can create the similar index on partition table as well. We will create it a local index

ORCL1>create index PT_IDX on PT(owner, object_name) local;
Index created.
ORCL1>

This step can be done before as well, but doesnt matter.

Now lets verify the results


ORCL1>select count(1) from dba_objects where created < to_date('2006-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 8919

ORCL1>select count(1) from dba_objects where created < to_date('2007-12-31','YYYY-MM-DD') and created > to_date('2006-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 110

ORCL1>select count(1) from dba_objects where created < to_date('2008-12-31','YYYY-MM-DD') and created > to_date('2007-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 250

ORCL1>select count(1) from dba_objects where created < to_date('2009-12-31','YYYY-MM-DD') and created > to_date('2008-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 312

ORCL1>select count(1) from dba_objects where created < to_date('2010-12-31','YYYY-MM-DD') and created > to_date('2009-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 163

ORCL1>select count(1) from dba_objects where created < to_date('2011-12-31','YYYY-MM-DD') and created > to_date('2010-12-31','YYYY-MM-DD');

 COUNT(1)
----------
 5220

RCATLTN1>

So this is how we can convert a simple non-partition table to a partition table.
Don’t forgot to gather status again after creating all partitions and indexes.

Hope this helps !!

Virual Index and Invisible Index

Oracle has come up with a feature called virtual index in oracle 9i. This feature allow us to test an index on a table without actually adding an index on the table. The table will be visible only in our session and will be used by our queries only (if optimizer decide it to use). So basically the index will be visible to optimizer only in our sessions. Optimizer running query in other sessions won’t be able to see this index.

Virtual Index in Oracle 9i

Utility of using virtual index is that, suppose we have a table having huge number of rows and it is getting joined with other tables. If we see that optimizer is creating a plan which is costly and SQL tuning advisor suggest us to create an index on a column, then in case of production database we cannot simply create an index and test the changes. We need to make sure that creating that index wont have any negative impact on the execution plan of other queries running in this database.

So there is where we can use virtual index. Here is how virtual index works.

1) Creating a table

SQL> create table test as select * from dba_objects;

Table created.

2) Try selecting a value from test table

SQL> select * from test where object_name = 'STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------

3) Create a virtual Index on test table

SQL> create index test_idx_1 on test(object_name) nosegment;

Index created.

In order to create a virtual index, we need to give NOSEGMENT at the end of the create index statement. This will just create a index on the object_name column of test table. But it will not create an index segment in database.

You can check this by querying dba_objects and dba_indexes tables.

SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_IDX_1';

no rows selected
SQL> col OBJECT_NAME format a30;
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_IDX_1';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST_IDX_1                     INDEX

So, object exists in database, but we dont have segment for the same.

Now if you try to run the same select command on test table, still optimizer will NOT use virtual index.

SQL> select * from test where object_name = 'STANDARD';
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------

In order for optimizer to use virtual index, you need to set a parameter called _USE_NOSEGMENT_INDEXES in your session

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.

Once you set this hidden parameter, optimizer will start using the virtual index you created on this table

SQL> select * from test where object_name = 'STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1221747299

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |   354 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     2 |   354 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX_1 |    46 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

If you run this query from some other session, it wont use virtual index.

you can analyze virtual indexes

SQL> analyze index TEST_IDX_1 compute statistics;

Index analyzed.

You CANNOT rebuild a virtual index

SQL> alter index TEST_IDX_1 rebuild;
alter index TEST_IDX_1 rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

Finally, if the index doesn’t seem to satisfy your requirement, then you can drop the virtual index.

SQL> drop index TEST_IDX_1;

Index dropped.

Invisible Index in 11g

We have a similar concept in Oracle database 11g called invisible index.

In case of invisible index, we can check if creating a new index is actually going to improve the performance or not. This index will not be visible to any other session and it will not be used by any other existing query run on same table.

SQL>drop table t;

Table dropped.

SQL>create table t as select * from dba_objects;

Table created.

SQL>create index t_ind1 on t(object_name) invisible;

Index created.

You can also make existing index as invisible using alter index command. You can make existing invisible index as visible. As soon as you make index visible, your existing queries will start using new index.

TDCLTN1>alter index t_ind1 visible;
TDCLTN1>alter index t_ind1 invisible;

We have a new column in USER_INDEXES called VISIBILITY. This tells whether an index is visible or not.

SQL>select index_name,VISIBILITY from user_indexes where index_name='T_IND1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
T_IND1                         INVISIBLE

So how does INVISIBLE index works ?

Now that we have created an INVISIBLE index, lets try to run a query on new table T and see if it uses the index.

SQL>explain plan for
 2  select * from t where object_name='STANDARD';

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T    |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 1 - filter("OBJECT_NAME"='STANDARD')

So we can see its doing a full table scan.

In order to use the invisible index, we have a new parameter introduced in 11g – OPTIMIZER_USE_INVISIBLE_INDEXES

This parameter can be set to either TRUE or FALSE

If set to true, we are asking optimizer to use the invisible index if it can make a better plan using that.

Lets try to set this parameter to TRUE and run the same query.

SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     2 |   202 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     2 |   202 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND1 |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='STANDARD')

Now it has used the index and the cost of the query also reduced.

If the index seems to make positive impact on the other query as well, then you can make this index as visible.

Note that if the index is INVISIBLE, then optimizer will ignore the index and 10053 trace will show index as “UNUSABLE”

Difference:

So based on above examples, we can see that the difference is

– In case of virtual index, we dont actually create the index, its just the place holder. In case of invisible index we actually create the index

– In case of virtual index, we can check if the index is getting used or not using explain plan and performance of the query can be mentioned statistically by explain plan. In case of invisible index, we can actually run the query and check the performance benefit.

– We cannot make existing index as virtual, unless we have to drop it and create a no_segment index. We can make any index invisible.

Hope this helps !!

Oracle Index rebuild online – 10g Vs 11g

An index is basically used for faster access to tables. Over a period of time the index gets fragmented because of several DMLs running on table.
When the index gets fragmented, data inside the index is scattered, rows / block reduces, index consumes more space and scanning of index takes more time and more blocks for same set of queries.
To talk in index terminology, we will have a single root block, but as fragmentation increases there will be more number of branch blocks and more leaf blocks. Also the height of index will increase.

To fix the above issue, we go for index rebuild. During index rebuild, the data inside the index is reorganized and compressed to fit in minimum number of blocks, height of the index is reduced to minimum possible level and performance of queries will increase.
Your search becomes faster and your query will read less number of blocks.

There are 2 methods to rebuild the index.

1) Offline index rebuild – alter index <index name> rebuild;
2) Online index rebuild  – alter index <index name> rebuild online;

With offline index rebuild, table and index is locked in exclusive mode preventing any translations on the table. This is most intrusive method and is used rarely in production unless we know for sure that modules are not going to access the table and we have complete downtime.

With online index rebuild, transactions can still access the table and index. Only for very less amount of time the lock is acquired on the table (by index rebuild operation). For rest of the time table and index is available for transactions.
However there is a difference in a way this internal online rebuild mechanism works in 10g and 11g. With 11g things are refined further to minimize the impact.

I will first explain the 10g method of rebuilding the index, after that will see the behavior in 11g

10g behaviour:

The way online index build (OIB) works is by creating an IOT journal table to keep track of changes while the OIB is in progress and merge all the changes from journal table to complete index build operation.

If I have a table with the structure of (A NUMBER, B NUMBER, C CHAR(100), D NUMBER) and to create index on (A, B, C) column, Oracle would create journal table like:

create table “ORACLE”.”SYS_JOURNAL_18155″ (C0 NUMBER, C1 NUMBER, C2 VARCHAR2(100), opcode char(1), partno number, rid rowid, primary key( C0, C1, C2 , rid )) organization index;

Before 11g, OIB will get in the DML queue to lock the table exclusively while preventing the new DML’s to go through, once all the active transactions (ones which were initiated before the OIB) are completed, OIB will create the journal IOT table and release the exclusive table lock (it’ll still keep the share lock on the table to prevent any other DDL’s) for DML’s to continue.

As part of journal table creation, Oracle would create an internal trigger on the primary table to record all the changes to the journal table. Along with using all the index columns, journal table will add “ROWID” to that list to make it as primary key.

“OPCODE” column represents the type of operation like “I” for Insert and “D” for Delete.
“PARTNO” column represents partition number of the underlying table.

Among all the changes to a given row for any of the index key columns, only the most recent change for that record is visible in the journal table. Any update operation of index key columns would be converted to “DELETE” and “INSERT” in the journal table.

While rest of the user sessions populate journal table with the ongoing changes for index key columns, OIB session would be reading the table in consistent mode (as of the time journal table is created) to create the index followed by the final merge operation.

During the merge process, Oracle will read the journal table (IOT) leaf blocks from left to right to merge those changes with the index being built. As the journal table leaf block changes are applied, once a leaf block is fully consumed, it’s reference will be deleted from the branch block.

This process will continue until all leaf blocks are consumed and when it comes to the last leaf block, Oracle would stop all the DML’s again to do the final merge and drop the journal table before releasing the mode 6 exclsuive table lock.

As each leaf block is consumed, Oracle would mark each entry as deleted. If more DML’s happen while Oracle is doing the merge, it’ll do one more pass of walking through the leaf blocks, this process continues until the merge process is all done.

If there is any long running DML before issuing the OIB, Oracle would wait on the long running transaction and it will prevent upcoming DML’s also. This is one of the major problems with OIB. Same thing can happen when it is doing the final merge.

So in the above process lock is acquired 2 times, one during the start of index creation when journal table is created online index rebuild process needs to be take exclusive lock on table to prevent DMLs from changing data. Once journal table is created online index rebuild process will release DML lock and hold a shared lock. Any DMLs happening after this will have entry made into journal table.
Again at the end of the process online index rebuild process will try to take exclusive lock to merge the last block of journal table into the main index.

Following example demonstrate the same:

Create test table and insert huge number of rows

</pre>
SQL>create table test as select * from dba_objects;

Table created.

SQL>insert into test select * from test;

29493 rows created.

SQL>/

58986 rows created.

Like this add more rows till it becomes big

Check the size of table


SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'TEST';

SUM(BYTES)/1024/1024/1024
-------------------------
 .430053711

Create index on the table

SQL>create index I1 on test(OBJECT_NAME);

Index created.

Check the size of index.

SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'I1';

SUM(BYTES)/1024/1024/1024
-------------------------
 .129272461

Now from the session 1, try inserting a record in the TEST table

Session 1 SID: 3204
SQL Text :

SQL>insert into test (owner, object_name) values ('AVDEO','NEW_TEST');
1 row created.
SQL>

From session 2, try rebuilding the index online

Session 2 SID: 3046
SQL text :

alter index I1 rebuild online

From session 3, run another DML command.

Session 3 SID: 3827
SQL Text:

update test set OWNER = ‘DEO2’ where OWNER = ‘DEO’

If we check v$lock table we can see session 2 (online index rebuild) is waiting on session 1 (insert). So unless insert completes, session 2 doing online index rebuild will not get a exclusive lock.


SQL>select
 2      l.SID oracle_id,
 3      decode(TYPE,
 4          'MR', 'Media Recovery',
 5          'RT', 'Redo Thread',
 6          'UN', 'User Name',
 7          'TX', 'Transaction',
 8          'TM', 'DML',
 9          'UL', 'PL/SQL User Lock',
 10          'DX', 'Distributed Xaction',
 11          'CF', 'Control File',
 12          'IS', 'Instance State',
 13          'FS', 'File Set',
 14          'IR', 'Instance Recovery',
 15          'ST', 'Disk Space Transaction',
 16          'TS', 'Temp Segment',
 17          'IV', 'Library Cache Invalidation',
 18          'LS', 'Log Start or Switch',
 19          'RW', 'Row Wait',
 20          'SQ', 'Sequence Number',
 21          'TE', 'Extend Table',
 22          'TT', 'Temp Table', type) lock_type,
 23      decode(LMODE,
 24          0, 'None',
 25          1, 'Null',
 26          2, 'Row-S (SS)',
 27          3, 'Row-X (SX)',
 28          4, 'Share',
 29          5, 'S/Row-X (SSX)',
 30          6, 'Exclusive', lmode) lock_held,
 31      decode(REQUEST,
 32          0, 'None',
 33          1, 'Null',
 34          2, 'Row-S (SS)',
 35          3, 'Row-X (SX)',
 36          4, 'Share',
 37          5, 'S/Row-X (SSX)',
 38          6, 'Exclusive', request) lock_requested,
 39      decode(BLOCK,
 40          0, 'Not Blocking',
 41          1, 'Blocking',
 42          2, 'Global', block) status,
 43      OBJECT_NAME
 44  from    v$locked_object lo,
 45      dba_objects do,
 46      v$lock l
 47  where     lo.OBJECT_ID = do.OBJECT_ID
 48  AND     l.SID = lo.SESSION_ID
 49  /

 ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3204 DML                        Row-X (SX)                               None                                     Blocking             TEST
 3204 Transaction                Exclusive                                None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DML                        Share                                    None                                     Not Blocking         TEST
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3900 DML                        None                                     Row-X (SX)                               Not Blocking         TEST

From the above output we can see that first session is 3204 which is running insert.
Session 2 is 3075 which is running index rebuild and is waiting for “DML share” lock
session 3 is 3900 running update DML and waiting for “DML Row-X” lock

If we see which session is blocking what we see below rows


Logn Ora    SQL/Prev                     OS                                                   Call
><    Sid-Ser-S Time User   Hash       Module            User   Svr-Pgm    Machine      HR Resource           Elap Ctim Locked Object
--------------- ---- ------ ---------- ----------------- ------ ---------- ------------ -- ------------------ ---- ---- --------------------
>  3204,23884-I 0953 ADVAIT 0          SQL*Plus          advait 7924-orac  db-fc-admin- 3  TM:1664558-0       448s 448s 1664558
 < 3075,20427-A 0959 ADVAIT 3645454058 SQL*Plus          advait 15432-orac db-fc-admin-  4                    433s 432s 1664558
 < 3900,30565-A 0959 ADVAIT 4227999514 SQL*Plus          advait 16004-orac db-fc-admin-  3                    408s 408s 1664558

Above output is generated by my custom scripts.

It says that 3204 is parent session and 3204 and 3900 is waiting on 3204.

In case of 10g if we commit session 1 (sid 3204 running insert), it will allow online index rebuild to continue and get the lock.
Session 3 will still continue to wait until session 2 running online index rebuild releases the lock.

After some time session 2 will start with index rebuild, it will take lock for very short period of time and releases the lock.
Session 3 (update DML) will aquire the lock after session 2 (index rebuild) releases the lock.

Session 3 completed while session 2 (index rebuild) does table scan.

SQL>update test set OWNER = 'DEO2' where OWNER = 'DEO'
 2  ;
1 rows updated.
SQL>

Now after session 3 DML completes, we didnt commit session 3. We are waiting for session 2 to complete now.

We can check v$session_longops to see current operation for index rebuild


SQL>select sid, OPNAME, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS from v$session_longops where sid = 3075;

 SID OPNAME                                                                SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
------ ---------------------------------------------------------------- ---------- ---------- -------------- ---------------
 3075 Sort Output                                                           25011      25011              0              15
 3075 Table Scan                                                            56036      56036              0              61

2 rows selected.

So we can see that v$session_longops that table scan for index rebuild completed. But still the session 2 for index rebuild is hanging.

If we run above query to see the locks it gives below output


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         SYS_JOURNAL_1664589
 3075 Temp Segment               Exclusive                                None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DML                        Share                                    None                                     Not Blocking         TEST
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3075 Temp Segment               Exclusive                                None                                     Not Blocking         TEST
 3075 Transaction                Exclusive                                None                                     Not Blocking         TEST
 3900 DML                        Row-X (SX)                               None                                     Blocking             TEST

13 rows selected.

&nbsp;

So sid 3900 is the blocking session. This is session 3 which has run update query but havent commited.
So online index rebuild is waiting to acquire lock second time at the end. This is the time when it has to do the merging.

So after we commit session 3, session 2 doing online index rebuild will acquire the lock and will complete.

11g behaviour:

With 11g, significant changes were introduced to address all these problems.

Oracle will still wait for long running transactions to complete before starting the OIB process, but it will not block the upcoming DML’s. Oracle will not use exclusive table lock (mode 6) for OIB, which will eliminate any DML hang situations.

As soon as OIB process is initiated, Oracle will create IOT journal table and use internal trigger on the underlying table to keep track of the changes.

Once the journal table is created and the long running transactions are completed, Oracle will create the index by reading the table blocks in “CURRENT” mode, which means any committed changes in a block happened before the current SCN are read instead of the old way of accessing the blocks as of journal table creation time. This will virtually eliminate chance of running into rollback segment too small errors.

Oracle will recreate the internal trigger to directly modify the target index structure once the index build is completed (but the merge job is still pending). This means all user sessions will now directly update the target index instead of updating the journal table. Any change coming through would first be checked in the journal table before consolidating the change for the target index (this is best explanied with the example below).

Record “A” was inserted with rowid “RID” before the merge phase and hence it is tracked in the journal table.
Record “A” was deleted during the merge phase. Now the user session will read the journal table by record “A” with rowid “RID” and apply that change to the target index before deleteing the same record. It will also mark that record as consumed (i.e. deleted) in the IOT journal table leaf block.

While the user sessions continue to update the target index with the ongoing changes, OIB session will do the merge of journal table changes into the final index, as there will not be any ongoing DML changes being tracked in the journal table, this process will complete faster than before (i.e. pre 11g).

Lets take an example. In case of 11g we have MYTAB table and MYTAB_IDX index


SQL>select sum(bytes)/1024/1024 from dba_segments where segment_name = 'MYTAB';

SUM(BYTES)/1024/1024
--------------------
 406.5

SQL>select sum(bytes)/1024/1024 from dba_segments where segment_name = 'MYTAB_IDX';

SUM(BYTES)/1024/1024
--------------------
 152.5

In this case also do the following activity

From session 1 run a DML (insert statement) – SID 2213
From session 2 run index rebuild online command – SID 2222
From session 3 run a DML (update statement) – SID 2177

The current status in v$lock shows following


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2213 AE                         Share                                    None                                     Not Blocking         MYTAB
 2213 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2213 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701

Now if you compare it with 10g we see significant differences.

1) We see lot of extra locks in 11g compared to 10g
2) and the most important differences is that in case of 10g if you see last 2 rows in first output of v$lock we see


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3900 DML                        None                                     Row-X (SX)                               Not Blocking         TEST

and same in 11g is


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2213 AE                         Share                                    None                                     Not Blocking         MYTAB
 2213 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2213 Transaction                Exclusive                                None                                     Blocking             MYTAB
 .
 .
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB

In case of 10g if you see 3075 is the one which is doing an index rebuild and is waiting on initial sid 3204. Also 3rd session (3900) running DML is waiting for “Row-X (SX)” lock.
In case of 11g if you see 2222 is the one which is doing an index rebuild and is waiting on initial sid 2213. Also 3rd session (2177) running DML already got “Row-X (SX)” lock and is not waiting for anything.

So in case of 11g if index rebuild is waiting for initial lock to acquire, it does not block incomming DMLs.

After I commit 1st session I see that none of the session is now blocking index rebuild operation and so it can acquire initial lock


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

My session 3 is already complete and I commited that session now. session 3 had no dependency on any session.

After index rebuild start (its doing table scan), I run one more DML from session 3, but I dont commit. In this case index rebuild will hang again at the end waiting for exclusive lock on table.
Unless session 3 gives the lock index rebuild cannot proceed.

Important change here between 10g and 11g is that if we start 4th session while index rebuild waits for 2nd time for lock and if I commit session 3, in case of 10g index rebuild will get precedence and it will acquire lock blocking session 4.
In case of 11g session 4 will get presedence and will acquire lock for DML, where as index rebuild will wait further until all DML sessions are complete and lock is available for it to acquire. So online index rebuild will prioritize all other sessions before him to acquire locks.

From v$session_longops we can see that tablescan operation completed


SQL>select sid, OPNAME, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS from v$session_longops where sid = 2222 and opname not like 'RMAN%';

 SID OPNAME                              SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
---------- ------------------------------ ---------- ---------- -------------- ---------------
 2222 Table Scan                          51736      51736              0              12
 2222 Sort Output                         21422      21422              0               7

Now online index rebuild session is waiting for session 3 (SID 2177), which I started which table scan operation was inprogress


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

Unless I commit that session index rebuild will wait. If I start another session now (session 4) and commit session 3, index rebuild will still wait for session 4 to complete.
Like this it can continue to wait until all transactions are done.

Example I started session 4 (SID 2223 ) and ran DML, commited in session 3

now index rebuild session (SID 2222) is waiting for session 4.


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2223 AE                         Share                                    None                                     Not Blocking         MYTAB
 2223 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2223 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

So main difference in case of online index rebuild procedure is online index rebuild process gives precedence to other DML sessions to acquire locks. The process has become less intrusive now.

Hope this helps !!

WARNING: inbound connection timed out (ORA-3136)

Many times I faced this issue “WARNING: inbound connection timed out (ORA-3136)” in alert log and if you are using data guard, then you can see that dgmgrl status will show failed because standby is not able to sync up with primary.

The “WARNING: inbound connection timed out (ORA-3136)” in the alert log indicates that the client was not able to complete it’s authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

Root cause of this issue could be

1. malicious client is flooding database servers with connect requests that consumes resources

2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified

As a workaround to avoid only this warning messages, you can set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT=0 in $ORACLE_HOME/network/admin/sqlnet.ora file

Once you set this parameter the warnings in alert log will go away. If you are using data guard, may be you want to enable the databases on which you were getting warnings.

Hope this helps !!

Checking Database Growth Trend

One of the simple way to find the growth of the database is using v$datafile view. following is the simple query and its output which gives the growth trend in month and year

</code>

SQL>select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
 2  from   v$datafile
 3  group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')
 4  order by   1, 2;

YEAR MO         GB
---- -- ----------
2004 10         14
2004 11         66
2004 12         47
2005 01         15
2005 02         14
2005 03         34
2005 04         14
2005 05         30
2005 06          5
2005 07          3
2005 08          9

YEAR MO         GB
---- -- ----------
2005 10          4
2005 12         20
2006 01         11
2006 02         15
2006 03         14
2006 04         16
2006 05         13
2006 06          0
2006 07          4
2006 08         17
2006 09          5

YEAR MO         GB
---- -- ----------
2006 10          6
2006 11          0
2006 12         30
2007 01          4
2007 02          4
2007 03          6
2007 04          6
2007 05         12
2007 06         15
2007 07         62
2007 08         29

YEAR MO         GB
---- -- ----------
2007 09         18
2007 11         20
2007 12          8
2008 03          8
2008 05          4
2008 08         11
2008 09         14
2008 10          4
2008 11         36
2008 12         49
2009 01          8

YEAR MO         GB
---- -- ----------
2009 02         38
2009 03         35
2009 04         54
2009 05         73
2009 08          1
2009 09         12
2009 10         78
2009 11        105
2009 12        113
2010 01        109
2010 02         79

YEAR MO         GB
---- -- ----------
2010 03         84
2010 05        243
2010 06        225
2010 07        244
2010 08        152
2010 09         40
2010 10        176
2010 11         76
2010 12        107

64 rows selected.

<code>

Analyze Index Vs. Gather_Index_Stats – Oracle Database 10gp

One of the difference I noticed about analyze index command and gather_index_stats procedure is that when we use analyze index command if updates the index statistics with number of leaf blocks equals the number of leaf blocks below HWM.

However if we use gather_index_stats statistics shows number of leaf blocks equals number of leaf blocks that actually has the data.

Here is the illustration for the same.

 

Create a table

SQL> create table t1 pctfree 99 pctused 1  
2  as  
3  select rownum id,  
4  trunc(100 * dbms_random.normal) val,  
5  rpad('X',100) padding  
6  from all_objects where rownum <= 10000;

Table created.

I intentionally created table with pctfree as 99 so that each block will have 1 row and it uses 10000 blocks to store 10000 rows.

Create an index

SQL> create index t1_i on t1(val) ;
Index created.

Check the number of leaf blocks

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';

 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
1          21           563

We have around 1600 rows whose val > 100

SQL> select count(*) from t1 where val > 100;
 COUNT(*)
----------      
  1598

Delete rows from table, which will also delete entries from index and some of the leaf blocks will get empty.

SQL> delete from t1 where val > 100;
1598 rows deleted.

Analyze index

SQL> analyze index t1_i validate structure;
Index analyzed.

Check again the number of leaf blocks

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';
 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
 1          21           563

Number of leaf blocks reported are still 21

Gather stats on index

SQL> exec dbms_stats.gather_index_stats('ADVAITD_DBA','T1_I');
PL/SQL procedure successfully completed.

Check the number of leaf blocks again

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';
 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
 1          18           379

Now if we see the number of leaf blocks reported are 18. This plays quiet a big role for optimizer in creating plans for queries. If we don’t have correct stats for the index, it may lead to expensive explain plans.

So its better to use gather_index_stats rather than analyze index.

Hope this helps !!

Row Chaining and Migeration

Overview

If you notice poor performance in your Oracle database Row Chaining and Migration may be one of several reasons, but we can prevent some of them by properly designing and/or diagnosing the database. Row Migration & Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance. The main considerations are: What is Row Migration & Row Chaining ? How to identify Row Migration & Row Chaining ? How to avoid Row Migration & Row Chaining ? Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

Oracle Block

The Operating System Block size is the minimum unit of operation (read /write) by the OS and is a property of the OS file system. While creating an Oracle database we have to choose the «Data Base Block Size» as a multiple of the Operating System Block size. The minimum unit of operation (read /write) by the Oracle database would be this «Oracle block», and not the OS block. Once set, the «Data Base Block Size» cannot be changed during the life of the database (except in case of Oracle 9i). To decide on a suitable block size for the database, we take into consideration factors like the size of the database and the concurrent number of transactions expected. The database block has the following structure (within the whole database structure)

oracle_data_block

Header

Header contains the general information about the data i.e. block address, and type of segments (table, index etc). It Also contains the information about table and the actual row (address) which that holds the data.

Free Space

Space allocated for future update/insert operations. Generally affected by the values of PCTFREE and PCTUSED parameters.

Data

Actual row data.

FREELIST, PCTFREE and PCTUSED While creating / altering any table/index, Oracle used two storage parameters for space control.

  • PCTFREE – The percentage of space reserved for future update of existing data.
  • PCTUSED – The percentage of minimum space used for insertion of new row data. This value determines when the block gets back into the FREELISTS structure.
  • FREELIST – Structure where Oracle maintains a list of all free available blocks. Oracle will first search for a free block in the FREELIST and then the data is inserted into that block. The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free space reaches the PCTFREE value.

When the free space reach the PCTFREE value the block is removed from the FREELIST, and it is re-listed in the FREELIST table when the volume of data in the block comes below the PCTUSED value. Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks.

Row Migration

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.

ora_row_migration_1.jpg

Full Table Scans are not affected by migrated rows

The forwarding addresses are ignored. We know that as we continue the full scan, we’ll eventually get to that row so we can ignore the forwarding address and just process the row when we get there. Hence, in a full scan migrated rows don’t cause us to really do any extra work — they are meaningless.

Index Read will cause additional IO’s on migrated rows

When we Index Read into a table, then a migrated row will cause additional IO’s. That is because the index will tell us «goto file X, block Y, slot Z to find this row». But when we get there we find a message that says «well, really goto file A, block B, slot C to find this row». We have to do another IO (logical or physical) to find the row.

Row Chaining

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with long and long raw columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

ora_row_chained_1.jpg

Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that was spread over two blocks, the query: select column1 from table where column1 is in Block 1, would not cause any «table fetch continued row». It would not actually have to get column2, it would not follow the chained row all of the way out. On the other hand, if we ask for: select column2 from table and column2 is in Block 2 due to row chaining, then you would in fact see a «table fetch continued row»

Example

The following example was published by Tom Kyte, it will show row migration and chaining. We are using an 4k block size:

select name,value from v$parameter where name = ‘db_block_size’;

NAME   VALUE
————– ——
db_block_size  4096

Create the following table with CHAR fixed columns:

create table row_mig_chain_demo (
x int primary key,
a char(1000),
b char(1000),
c char(1000),
d char(1000),
e char(1000)
);

That is our table. The char(1000)’s will let us easily cause rows to migrate or chain. We used 5 columns a,b,c,d,e so that the total rowsize can grow to about 5K, bigger than one block, ensuring we can truly chain a row.

insert into row_mig_chain_demo (x) values (1);
insert into row_mig_chain_demo (x) values (2);
insert into row_mig_chain_demo (x) values (3);
commit;

We are not interested about seeing a,b,c,d,e – just fetching them. They are really wide so we’ll surpress their display.

column a noprint
column b noprint
column c noprint
column d noprint
column e noprint

select * from row_mig_chain_demo;

X
———-
1
2
3

Check for chained rows:

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                             VALUE
----------------------------     ----------
table fetch continued row        0

Now that is to be expected, the rows came out in the order we put them in (Oracle full scanned this query, it processed the data as it found it). Also expected is the table fetch continued row is zero. This data is so small right now, we know that all three rows fit on a single block. No chaining.

ora_row_example_1.jpg

Demonstration of the Row Migration

Now, lets do some updates in a specific way. We want to demonstrate the row migration issue and how it affects the full scan:

update row_mig_chain_demo set a = ‘z1’, b = ‘z2’, c = ‘z3’ where x = 3;
commit;
update row_mig_chain_demo set a = ‘y1’, b = ‘y2’, c = ‘y3’ where x = 2;
commit;
update row_mig_chain_demo set a = ‘w1’, b = ‘w2’, c = ‘w3’ where x = 1;
commit;

Note the order of updates, we did last row first, first row last.

select * from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 0

Interesting, the rows came out «backwards» now. That is because we updated row 3 first. It did not have to migrate, but it filled up block 1. We then updated row 2. It migrated to block 2 with row 3 hogging all of the space, it had to. We then updated row 1, it migrated to block 3. We migrated rows 2 and 1, leaving 3 where it started.

So, when Oracle full scanned the table, it found row 3 on block 1 first, row 2 on block 2 second and row 1 on block 3 third. It ignored the head rowid piece on block 1 for rows 1 and 2 and just found the rows as it scanned the table. That is why the table fetch continued row is still zero. No chaining.

ora_row_example_2.jpg

So, lets see a migrated row affecting the «table fetch continued row»:

select * from row_mig_chain_demo where x = 3;

X
———-
3

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 0

This was an index range scan / table access by rowid using the primary key.  We didn’t increment the «table fetch continued row» yet since row 3 isn’t migrated.

select * from row_mig_chain_demo where x = 1;

X
———-
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                      VALUE
————————— ———-
table fetch continued row       1

Row 1 is migrated, using the primary key index, we forced a «table fetch continued row».

Demonstration of the Row Chaining

update row_mig_chain_demo set d = ‘z4’, e = ‘z5’ where x = 3;
commit;

Row 3 no longer fits on block 1. With d and e set, the rowsize is about 5k, it is truly chained.

select x,a from row_mig_chain_demo where x = 3;

X
———-
3

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 1

We fetched column «x» and «a» from row 3 which are located on the «head» of the row, it will not cause a «table fetch continued row». No extra I/O to get it.

ora_row_example_3.jpg select x,d,e from row_mig_chain_demo where x = 3;

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 2

Now we fetch from the «tail» of the row via the primary key index. This increments the «table fetch continued row» by one to put the row back together from its head to its tail to get that data.

Now let’s see a full table scan – it is affected as well:

select * from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 3

The «table fetch continued row» was incremented here because of Row 3, we had to assemble it to get the trailing columns.  Rows 1 and 2, even though they are migrated don’t increment the «table fetch continued row» since we full scanned.

select x,a from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 3

No «table fetch continued row» since we didn’t have to assemble Row 3, we just needed the first two columns.

select x,e from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 4

But by fetching for d and e, we incemented the «table fetch continued row». We most likely have only migrated rows but even if they are truly chained, the columns you are selecting are at the front of the table.

So, how can you decide if you have migrated or truly chained?

Count the last column in that table. That’ll force to construct the entire row.

select count(e) from row_mig_chain_demo;

COUNT(E)
———-
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 5

Analyse the table to verify the chain count of the table:

analyze table row_mig_chain_demo compute statistics;

select chain_cnt
from user_tables
where table_name = ‘ROW_MIG_CHAIN_DEMO’;

CHAIN_CNT
———-
3

Three rows that are chained. Apparently, 2 of them are migrated (Rows 1 and 2) and one is truly chained (Row 3).

Total Number of «table fetch continued row» since instance startup?

The V$SYSSTAT view tells you how many times, since the system (database) was started you did a «table fetch continued row» over all tables.

sqlplus system/<password>

SELECT ‘Chained or Migrated Rows = ‘||value
FROM v$sysstat
WHERE name = ‘table fetch continued row’;

Chained or Migrated Rows = 31637

You could have 1 table with 1 chained row that was fetched 31’637 times. You could have 31’637 tables, each with a chained row, each of which was fetched once. You could have any combination of the above — any combo.

Also, 31’637 – maybe that’s good, maybe that’s bad. it is a function of

  • how long has the database has been up
  • how many rows is this as a percentage of total fetched rows.
    For example if 0.001% of your fetched are table fetch continued row, who cares!

Therefore, always compare the total fetched rows against the continued rows.

select name,value from v$sysstat where name like ‘%table%’;

select name,value from v$sysstat where name like ‘%table%’;

NAME                                                                  VALUE
—————————————————————- ———-
table scans (short tables)                                           124338
table scans (long tables)                                              1485
table scans (rowid ranges)                                                0
table scans (cache partitions)                                           10
table scans (direct read)                                                 0
table scan rows gotten                                             20164484
table scan blocks gotten                                            1658293
table fetch by rowid                                                1883112
table fetch continued row                                             31637
table lookup prefetch client count                                        0

How many Rows in a Table are chained?

The USER_TABLES tells you immediately after an ANALYZE (will be null otherwise) how many rows in the table are chained.

analyze table row_mig_chain_demo compute statistics;

select chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained,
avg_row_len, pct_free , pct_used
from user_tables
where table_name = ‘ROW_MIG_CHAIN_DEMO’;

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
———- ———– ———– ———- ———-
3         100        3691         10         40

PCT_CHAINED shows 100% which means all rows are chained or migrated.

How to avoid Chained and Migrated Rows?

Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand. Reorganizing the table ensures that the main free space is totally empty blocks.

The ALTER TABLE … MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE … MOVE statement with the COMPRESS keyword to store the new segment using table compression.

  1. ALTER TABLE MOVEFirst count the number of Rows per Block before the ALTER TABLE MOVEselect dbms_rowid.rowid_block_number(rowid) “Block-Nr”, count(*) “Rows”
    from row_mig_chain_demo
    group by dbms_rowid.rowid_block_number(rowid) order by 1; Block-Nr        Rows
    ———- ———-
    2066          3Now, de-chain the table, the ALTER TABLE MOVE rebuilds the row_mig_chain_demo table in a new segment, specifying new storage parameters:ALTER TABLE row_mig_chain_demo MOVE
    PCTFREE 20
    PCTUSED 40
    STORAGE (INITIAL 20K
    NEXT 40K
    MINEXTENTS 2
    MAXEXTENTS 20
    PCTINCREASE 0);Table altered. 

    Again count the number of Rows per Block after the ALTER TABLE MOVE

    select dbms_rowid.rowid_block_number(rowid) “Block-Nr”, count(*) “Rows”
    from row_mig_chain_demo
    group by dbms_rowid.rowid_block_number(rowid) order by 1;

    Block-Nr        Rows
    ———- ———-
    2322          1
    2324          1
    2325          1

  2. Rebuild the Indexes for the TableMoving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.analyze table row_mig_chain_demo compute statistics;ERROR at line 1:
    ORA-01502: index ‘SCOTT.SYS_C003228’ or partition of such index is in unusable
    stateThis is the primary key of the table which must be rebuilt.alter index SYS_C003228 rebuild;
    Index altered.analyze table row_mig_chain_demo compute statistics;
    Table analyzed. 

    select chain_cnt,
    round(chain_cnt/num_rows*100,2) pct_chained,
    avg_row_len, pct_free , pct_used
    from user_tables
    where table_name = ‘ROW_MIG_CHAIN_DEMO’;

    CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
    ———- ———– ———– ———- ———-
    1       33.33        3687         20         40If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

Detect all Tables with Chained and Migrated Rows

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.

  1. Create the CHAINED_ROWS tablecd $ORACLE_HOME/rdbms/admin
    sqlplus scott/tiger
    @utlchain.sql
  2. Analyse all or only your Tablesselect ‘analyze table ‘||table_name||’ list chained rows into CHAINED_ROWS;’
    from user_tables
    /analyze table ROW_MIG_CHAIN_DEMO list chained rows into CHAINED_ROWS;
    analyze table DEPT list chained rows into CHAINED_ROWS;
    analyze table EMP list chained rows into CHAINED_ROWS;
    analyze table BONUS list chained rows into CHAINED_ROWS;
    analyze table SALGRADE list chained rows into CHAINED_ROWS;
    analyze table DUMMY list chained rows into CHAINED_ROWS;Table analyzed.
  3. Show the RowIDs for all chained rowsThis will allow you to quickly see how much of a problem chaining is in each table. If chaining is prevalent in a table, then that table should be rebuild with a higher value for PCTFREEselect owner_name,
    table_name,
    count(head_rowid) row_count
    from chained_rows
    group by owner_name,table_name
    /OWNER_NAME                     TABLE_NAME                      ROW_COUNT
    —————————— —————————— ———-
    SCOTT                          ROW_MIG_CHAIN_DEMO                      1

Conclusion

Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

  • Row migration is typically caused by UPDATE operation
  • Row chaining is typically caused by INSERT operation.
  • SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.
  • To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view
  • To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE.