How many checkpoints in Oracle database ?

Published on September 7, 2015 by Advait4 Comments

This Question came to my mind when I was discussing “direct path reads” with DBA candidates. I was surprised that many DBAs were not aware of object level checkpoint that happens while doing direct path read. So I thought many DBAs may not be knowing different level of checkpoints that happens in the database and hence the question.

Well, the answer is 7 (as per my knowledge). Oracle does 7 different checkpoints at various stages. Lets check out what are those checkpoints and at what point they happen

Full checkpoint
Thread checkpoint
File level checkpoint
Object level checkpoint
Parallel query checkpoint <– BTW. This is the checkpoint that happens during direct path reads
Incremental checkpoint
Checkpoint during log switch

Full Checkpoint

This happens when DBA explicitly issues checkpoint command using “alter system checkpoint”. When this happens, all dirty blocks from (all instances in case of RAC) db_cache are written to disk. This includes committed as well as uncommitted data blocks.

This checkpoint also happens when DBA shutdown database cleanly using “shutdown immediate” or puts entire database in begin backup mode using “alter database begin backup”

Thread Checkpoint

Thread checkpoint is basically a full checkpoint in single instance database. So redo thread is associated with an instance. Thread checkpoints basically write all dirty blocks of a single thread or instance to a database. In case of RAC when a checkpoint is done for a specific single instance, its called thread checkpoint. This is done using “alter system checkpoint local”

File level Checkpoint

File level checkpoint is writing dirty blocks associated with set of files belonging to a tablespace. This happens when we put a tablespace in begin backup mode or when we take a tablespace offline or when we make tablespace readonly. Oracle writes all dirty blocks associated with datafiles of that tablespace to database before it changes the status of that tablespace.

Object level Checkpoint

All the dirty blocks that belong to specific object is written to database by DBWR process. This happens when you perform following action on the object (example table or index):

Drop table
truncate table
drop index
drop table purge

You might be thinking if we are dropping an object, why Oracle has to write its blocks to database. 2 reasons

Oracle writes these blocks to database “before” performing above DDL tasks
It’s required for recovery purpose. In future if you have to restore and recover database, Oracle needs to have its previous blocks so that it can roll forward and rollback.

Parallel query checkpoint

Whenever you are reading queries using parallel workers, Oracle does direct path reads and reads data from a datafile directly into PGA bypassing SGA. Starting from 11g, direct path reads also happens for full table scans where table size is larger than _small_table_threshold parameter.

Imagine we have a session which connected prior and did some DML on a table in SGA (buffer cache) and committed the changes. Since checkpoint doesn’t happen as soon as we commit the changes what happens when another session connects immediately and do a full table scan or parallel scan of that table. How will it see the latest data ?

This is where parallel query checkpoint comes in. When you run full table scan or parallel query scan, you will see direct path reads wait event but in the beginning, you will also see enq: KO fast object checkpoint wait event. This will checkpoint any blocks that belong to the object you are doing direct path read so that latest change goes into datafile.

Incremental Checkpoint

Prior to Oracle 8i, Oracle used to do checkpoint during log switch and nothing before that. So during log file switch, Oracle has to write lot of blocks to disk and we will see sudden spike in IO. Also, this has an effect of increasing recovery time if checkpoint hasn’t happen for until we are at the end of log file and database has crashed.

Starting 8i, Oracle started doing incremental checkpoints time to time. This logic has also evolved from Oracle 8i version till now and different parameters control this behavious in latest versions compared to older version.

In prior releases, we used to have log_checkpoint_interval and log_checkpoint_timeout parameters which used to control duration of incremental checkpoints that should happen in order to meet recovery SLAs. In later release, Oracle provided fast_start_mttr_target and fast_start_io_target parameters. These parameters takes our SLA timings and internally Oracle decides how frequently it has to take incremental checkpoints.

We have another situation where Oracle has to go for incremental checkpoint even though above parameters (fast_start_mttr_target or fast_start_io_target) has not met the condition yet. This happens when Oracle is not able to find any free blocks in buffer cache. At this point Oracle has to flush least recently used blocks to datafiles in order to make room for new buffers comming in. By default Oracle scans _db_block_max_scan_pct of blocks before it decides to flush LRU blocks to datafiles. If its not able to find required number of free blocks even after scanning _db_block_max_scan_pct blocks, it will go for incremental checkpoint starting with LRU blocks.

Checkpoint logic is much more complex that what I explained here. But objective of this article was to just introduce different types of checkpoints so I am not covering details of checkpoint algorithm here.

Checkpoint during log switch

This is the most obvious checkpoint. It happens whenever log switch happens. But note that during log switch only dirty blocks whose information is protected by that log file will be written to datafiles. So not all dirty blocks are written during log switch.

Hope this helps !!

Reference

https://bdrouvot.wordpress.com/2015/04/30/direct-path-read-and-enq-ko-fast-object-checkpoint/

Redefining tables online – Oracle 11g

Published on October 28, 2012 by AdvaitLeave a comment

Introduction:

One of the many challenges that we face in production environment is make changes to big tables.
If you consider a case of any OLTP systems, its easy to have tables whose size is beyond 10G.

This again depends on the nature of the database and kind of transactions happening on the table.

So lets consider a case where you have a big table which is also a very hot table having very high number of transactions / sec and something that continues 24X7.
Its difficult to take downtime on such tables and making DDL changes to such tables could be a nightmare.

Oracle has a great feature introudcued since Oracle 9i, but many DBAs doesnt seem to be aware of this feature – Online table redefiniation.

Online table redefinition allows you to make DDL changes to the table definition and requires very little downtime (less than a minute).
Techinically its not the same table that gets modified, but its another copy of the same table which has the required modification made.

You might question if we are making a new copy of the table we can as well use CTAS (Create Table as Select) and make the required changes.
But its not just about creating new object and copying the data. Online redefinition does lot more than that.

I will briefly explain you the features of online redefinition, followed by process and then we will straight way get to the examples which will help you to understand better.

Features – What it can do:

Online table redefinition enables you to:

Modify the storage parameters of a table or cluster
Move a table or cluster to a different tablespace
Add, modify, or drop one or more columns in a table or cluster
Add or drop partitioning support (non-clustered tables only)
Change partition structure
Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
Re-create a table or cluster to reduce fragmentation

Process – How its done:

To briefly explain the process, it involves following steps

1) Check if the table can be refined by running DBMS_REDEFINITION.CAN_REDEF_TABLE procedure.

We have following restrictions on redefining the table

One cannot redefine Materialized Views (MViews) and tables with MViews or MView Logs defined on them.
One cannot redefine Temporary and Clustered Tables
One cannot redefine tables with BFILE, LONG or LONG RAW columns
One cannot redefine tables belonging to SYS or SYSTEM
One cannot redefine Object tables
Table redefinition cannot be done in NOLOGGING mode (watch out for heavy archiving)

2) Create a new table with all of the desired logical and physical attributes.

If you want to change non-partition table to partition, you can create a new partition table. Structure of the table should be exactly the way you want to convert to.

3) Start redefinition process using DBMS_REDEFINITION.START_REDEF_TABLE procedure.

Be careful before running this command. If you must know, this command will start populating new table from the data in old table.
So if your old table is very big, then you need to have same amount of space available in the tablespace where new table is created.
Also, this command might take very long time if the size is big, so make sure you don’t have any disconnection in between.

If needed you can enable parallel before starting redefinition using following commands

alter session force parallel dml parallel degree-of-parallelism;
alter session force parallel query parallel degree-of-parallelism;

4) Sync new table on regular basis till cut off time

You should use DBMS_REDEFINITION.SYNC_INTERIM_TABLE and keep new table in sync with changes that happens on current production table.
This will reduce the cut off time. Cut off time if when you are going to point everything to new table and services will start writing to new table.
The more you keep new table and current production table in sync, lesser will be cut off time and downtime.

5) Copy dependent objects (such as triggers, indexes, grants, and constraints)

Copy dependent objects (such as triggers, indexes, materialized view logs, grants, and constraints) and statistics from production table to new table.
You should make sure that all dependents are copied.
You can do this manually by creating each dependent object or you can do it automatically using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

6) Complete redefinition process by using DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure

This will complete the redefinition process. This needs exclusive lock on production table which you want to redefine.
So you need to arrange for short downtime. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.

Example:

Lets take an example:

We have a table T as shown below.
We have a primary key on OBJECT_ID column.
We have a public synonym for table T.

SQL>desc T
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                            VARCHAR2(30)
 OBJECT_NAME                        VARCHAR2(128)
 SUBOBJECT_NAME                     VARCHAR2(30)
 OBJECT_ID                        NUMBER
 DATA_OBJECT_ID                     NUMBER
 OBJECT_TYPE                        VARCHAR2(19)
 CREATED                        DATE
 LAST_DDL_TIME                        DATE
 TIMESTAMP                        VARCHAR2(19)
 STATUS                         VARCHAR2(7)
 TEMPORARY                        VARCHAR2(1)
 GENERATED                        VARCHAR2(1)
 SECONDARY                        VARCHAR2(1)
 NAMESPACE                        NUMBER
 EDITION_NAME                        VARCHAR2(30)

SQL>

Currently this is not a partitioned table

SQL>select table_name, partitioned from user_tables where table_name = 'T';

TABLE_NAME               PAR
------------------------------ ---
T                   NO

Lets try to convert this into partition table.

You can check the meaning of every parameter supplied to below procedures at – http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_redefi.htm
Step 1) Check if the table can be refined by running DBMS_REDEFINITION.CAN_REDEF_TABLE procedure.

SQL>set serveroutput on
SQL>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user,'T',DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

SQL>

If the table is not a candidate for online redefinition, an error message is raised.

Step 2) Create a new intrim table with all of the desired logical and physical attributes.

For table T lets try to partition by CREATED which is a date column. I am planning to partition by year so we can get 10 partitions

SQL>select to_char(CREATED,'YYYY') from T group by to_char(CREATED,'YYYY');

TO_C
----
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012

10 rows selected.

CREATE TABLE "T_INTRIM"
   (    "OWNER" VARCHAR2(30),
    "OBJECT_NAME" VARCHAR2(128),
    "SUBOBJECT_NAME" VARCHAR2(30),
    "OBJECT_ID" NUMBER,
    "DATA_OBJECT_ID" NUMBER,
    "OBJECT_TYPE" VARCHAR2(19),
    "CREATED" DATE,
    "LAST_DDL_TIME" DATE,
    "TIMESTAMP" VARCHAR2(19),
    "STATUS" VARCHAR2(7),
    "TEMPORARY" VARCHAR2(1),
    "GENERATED" VARCHAR2(1),
    "SECONDARY" VARCHAR2(1),
    "NAMESPACE" NUMBER,
    "EDITION_NAME" VARCHAR2(30),
     CONSTRAINT "T_PK_INTRIM_01" PRIMARY KEY ("OBJECT_ID")
    )
PARTITION BY RANGE(CREATED)
(
PARTITION T_2003 VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY')),
PARTITION T_2004 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')),
PARTITION T_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')),
PARTITION T_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')),
PARTITION T_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION T_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')),
PARTITION T_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
PARTITION T_2010 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
PARTITION T_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
PARTITION T_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY'))
);

3) Start redefinition process using DBMS_REDEFINITION.START_REDEF_TABLE procedure.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
   uname        => user,
   orig_table   => 'T',
   int_table    => 'T_INTRIM',
   col_mapping  => NULL,
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID
   );
END;
/

After this both table should have near about same amount of record

SQL>select count(1) from T_INTRIM;

  COUNT(1)
----------
     61536

SQL>select count(1) from T;

  COUNT(1)
----------
     61536

SQL>

If you have continuous inserts going on your original table than you might have little more records in original table than intrim table.

4) Sync new table on regular basis till cut off time

BEGIN 
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
   uname      => user, 
   orig_table => 'T', 
   int_table  => 'T_INTRIM'
   );
END;
/

The way this sync works is, online redefinition will automatically create a MLOG table on original table.
In any of the above step we didn’t create any MLOG table on table T.

But if you check now, you will see MLOG table created automatically.

SQL>select log_table from user_snapshot_logs where master = 'T';

LOG_TABLE
------------------------------
MLOG$_T

This is required for syncing changed made to table T.

5) Copy dependent objects (such as triggers, indexes, grants, and constraints)

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname               => user,
    orig_table          => 'T',
    int_table           => 'T_INTRIM',
    copy_indexes        => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers       => TRUE,
    copy_constraints    => TRUE,
    copy_privileges     => TRUE,
    ignore_errors       => TRUE,
    num_errors          => num_errors);
END;
/

PL/SQL procedure successfully completed.

Before we finish online redefinition you can check if table is partition and data is distributed to all partitions

SQL>select table_name, partitioned from user_tables where table_name in ('T','T_INTRIM');

TABLE_NAME               PAR
------------------------------ ---
T                   NO
T_INTRIM               YES

SQL>select to_char(created,'YYYY'), count(1) from T_INTRIM group by to_char(created,'YYYY');

TO_C   COUNT(1)
---- ----------
2003       7902
2005       1820
2009       2742
2010       6765
2008       2612
2007       1016
2011      10474
2004        756
2012      23474
2006       3975

10 rows selected.

Once we finish redefinition table T will become partition table and T_INTRIM will become non-partition table.
For this it needs exclusive lock.

6) Complete redefinition process by using DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_INTRIM');
END;
/

PL/SQL procedure successfully completed.

SQL>select table_name, partitioned from user_tables where table_name in ('T','T_INTRIM');

TABLE_NAME               PAR
------------------------------ ---
T                   YES
T_INTRIM               NO

SQL>

so now table T is partitioned table.

References:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_redefi.htm

Hope this helps !!

Materialized view Concepts – Discussion Series 3

Published on October 24, 2012 by Advait5 Comments

We have seen Materialized view Concepts – Discussion Series 1 and Materialized view Concepts – Discussion Series 2.
This is the third article about Materialized views.

In this article we are going to discuss “How fast refresh works ?”

How fast refresh works ?

As we know in case of fast refresh only the changes that happened on master site (or master table) will be applied to MView on target site.
So the changes that happens on master table will be stored in MLOG table created on top of master table.
This is more efficient way than doing complete refresh.

As we know MLOG can be based on primary key or ROWID. ROWID MLOG is uncommon and is not used because if master table gets moved then ROWID will get changed and changes saved in MLOG will be invalid.
So to identify the changes on master table usually primary key based MLOG is created on top of master table.

Only 1 MLOG can be created on master table even if we have multiple sites refreshing from 1 master table.

Before we check on how fast refresh works, lets understand some of the components of fast refresh

MLOG$ table and its important columns

SNAPTIME$$            – This is a date columns and holds the date of 1st Mview refresh time. Example if we have 3 Mviews registered on 1 master table, than this column will hold oldest refresh date among the 3 Mviews
DMLTYPE$$             – This column tells you the type of DML ( U – Update, D – Delete, I – Insert )
OLD_NEW$$            – This column allow the fast-refresh mechanism to distinguish between rows inserted at the mview site and rows with modified primary key values.
CHANGE_VECTOR$$    – Used for subquery and LOB MViews

Apart from above standard columns in MLOG$ table, there will also be primary key columns. These columns are same as primary key columns of master table

AFTER ROW trigger on the master table

From Oracle8 and onward this trigger is kernelized and is no longer visible in the data dictionary. The same trigger can support both ROWID and primary key MViews and will populate the MLOG$_<table_name> with the proper values. It also populates the SNAPTIME$$ column (indicating the latest refresh time so far a particular row) and the DMLTYPE$$ column. The snaptime$$ column is populated based on the value of the snaptime column in snap$ table at the MView site and is not updated until the log is first used by a MView refresh.

Registration of Mview on master site

Oracle automatically tries to register a materialized view at its master site or master materialized view site when you create the materialized view, and unregisters when you drop it. The same applies to materialized view groups. Registration of fast refreshable materialized view logs in the master database (SYS.SLOG$) is needed to perform fast refreshes. This information is also used to maintain the materialized view log of the master table.

Fast refresh operation

Fast refresh operation consists of 3 phases

Setup Phase
Refresh Phase
Wrap-up Phase

1) Setup Phase:

Setup has to check if the Mview being refreshed is ROWID based Mview or Primary key based Mview.
After that it has to verify if fast-refresh can be performed for this MView. An MView can perform a fast refresh only if it can use the MView log. This can be determined by checking entry in SYS.SLOG$ table on master site.


DB1>select MASTER, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPID SNAPTIME
------------------------------ ---------- ----------------
T                    15119 2012-10-14:03:01
T                    15120 2012-10-17:09:47

If an entry is present in this table for that SNAPID, then it can be fast refreshed.
Its possible that you have created a complete refreshable MView on some master table which didn’t had MLOG. Later point of time you created MLOG and may be wondering why fast refresh not happening.
You can check the entry in this table and verity. Also in such cases you need to drop and recreate Mview on prebuilt table and make it fast refreshable.

Once its confirmed that fast refresh is possible for MView in question, snaptime$$ column is updated in the MLOG$ table of the altered rows to its own refresh date and time for the first MView that refreshes. This value does not change until the rows are eventually purged from the log.

2) Refresh Operation:

After setup phase, a second check is made to see if fast refresh can be done. This time its the date comparison that is done to ensure their is no mismatch of data.
For fast refresh to work, timestamp of oldest/oldest_pk column in mlog$ must be older than the time of last refresh.

How fast refresh work

Lets check out in detail how the date changes happen.
We will keep an eye on 3 tables

1) SYS.SLOG$ on master
2) SYS.MLOG$ on master
3) MLOG$_<master_table> on master (This is out MLOG$ table)

I will explain you with live example.

Master table name – T (on DB1 database)
MLOG table name – MLOG$_T (no DB1 database)

We have 2 snapshot sites and each has fast refreshable MView created.

MView Name – T_REP (on DB2 database)
MView Name – T_REP (on DB3 database)

On Master site:


DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME
------------------------------ ---------------- ---------- ----------------
T                             15119 2012-10-24:01:16
T                             15120 2012-10-24:01:17

Above output represents 2 snapshot site registered on Master table/site

DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER                   OLDEST_PK    YOUNGEST     LOG                MTIME         LAST_PURGE_DATE
------------------------------ ---------------- ---------------- ------------------------------ ---------------- ----------------
T                   2012-10-24:01:16 2012-10-24:01:17 MLOG$_T            2012-10-13:22:25 2012-10-24:01:17

In SYS.MLOG$ table you will have 1 record for each MLOG$ that you create. Since a master table can have only 1 MLOG you will have 1 record for each master table.

There are no records in MLOG$_T table

DB1>select count(1) from MLOG$_T;

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

Lets make changes in master table T and see what happens in above 3 tables

DB1>update T set DATA_OBJECT_ID = 0 where OBJECT_ID = 2300;

1 row updated.

DB1>commit;

Commit complete.

DB1>

With above modification, no change seen in SYS.SLOG$ table and SYS.MLOG$ table

DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME
------------------------------ ---------------- ---------- ----------------
T                             15119 2012-10-24:01:16
T                             15120 2012-10-24:01:17

DB1>
DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER                   OLDEST_PK    YOUNGEST     LOG                MTIME         LAST_PURGE_DATE
------------------------------ ---------------- ---------------- ------------------------------ ---------------- ----------------
T                   2012-10-24:01:16 2012-10-24:01:17 MLOG$_T            2012-10-13:22:25 2012-10-24:01:17

However we see new entry in MLOG$_T table corresponding to the row changed

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

 OBJECT_ID SNAPTIME$$        D O
---------- ---------------- - -
      2300 4000-01-01:00:00 U U

OBJECT_ID is the primary key column and value in MLOG$_T table represent the row we changed

Observe the value of SNAPTIME$$ – 4000-01-01. This is a date in future which will not be reached in lifetimes. This date tells us that none of the Mview sites has done a fast refresh of this change.

So lets rewind our statement about second check done during “Refresh Phase”

For fast refresh to work, timestamp of oldest/oldest_pk column in mlog$ must be older than the time of last refresh.

It means oldest_pk <= last refresh time on Mview sites

We can check last refresh time of Mviews by checking DBA_SHAPSHOTS table or DBA_SNAPSHOT_REFRESH_TIMES table on Mview site. But same thing can be checked on master site using SYS.SLOG$ table

We know oldest_pk timestamp – 2012-10-24:01:16
Min(last_refresh time) – 2012-10-24:01:16

So since oldest_pk <= last refresh time on Mview sites, refresh can proceed

If one of the site does a fast refresh we can see that MLOG$_T.SNAPTIME$$ timestamp gets updated to refresh time

DB2>exec dbms_snapshot.refresh('T_REP','F');

PL/SQL procedure successfully completed.

DB2>

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

 OBJECT_ID SNAPTIME$$        D O
---------- ---------------- - -
      2300 2012-10-24:01:52 U U

DB1>

We are yet to refresh second site which is registered for this master table and so the record from MLOG$_T is not deleted.

Also, 1 note with respect to SYS.MLOG$ table


DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER                   OLDEST_PK    YOUNGEST     LOG                MTIME         LAST_PURGE_DATE
------------------------------ ---------------- ---------------- ------------------------------ ---------------- ----------------
T                   2012-10-24:01:17 2012-10-24:01:52 MLOG$_T            2012-10-13:22:25 2012-10-24:01:52

YOUNGEST column represent the latest refresh time. If you have 5 sites, the one you refresh latest will have that timestamp updated in YOUNGEST column
OLDEST_PK column represent the oldest refresh time. If you have 5 sites, the one you refresh first will have that timestamp updated in OLDEST_PK column
OLDEST column is used in ROWID based MLOG

3) Wrap-up Phase

In this phase it checks if all Mviews are refreshed and if the changes in MLOG$_T table has gone to all site and if the entries in MLOG$_T table can be purged.

Again Oracle checks dates in above 3 tables to determine which records in MLOG$ table can be purged.

How MLOG$ purge works

Oracle automatically tracks which rows in a MView log have been used during the refreshes of MViews, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple mviews can use the same MView log, rows already used to refresh one MView may still be needed to refresh another MView. Oracle does not delete rows from the log until all MViews have used them. In the wrap-up phase of the refresh process, the master MView log is purged. It deletes all unnecessary rows from the MView log. Rows in the MView log are unnecessary if their refresh timestamps MLOG$_<table_name>.SNAPTIME$$ are older or equal than the oldest entry in SLOG$.SNAPTIME for this log.

It means rows in MLOG$_<master_table> will be deleted whose MLOG$_<master_table>.SNAPTIME$$ <= min(SYS.SLOG$.SNAPTIME)

Lets take an example

We have a live example going where we have updated 1 record in master table and we have refreshed 1 site (out of 2 sites registered)

Here are the outputs of 2 required tables


DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

OBJECT_ID SNAPTIME$$        D O
---------- ---------------- - -
2300 2012-10-24:01:52 U U

DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME
------------------------------ ---------------- ---------- ----------------
T                             15119 2012-10-24:01:52
T                             15120 2012-10-24:01:17

As you can see SNAPTIME$$ timestamp has the time when this record was first refresh by any site. If we have 3 sites and 1st site refreshes the records this timestamp will get updated.
But if 2nd site refreshes the record, this timestamp will NOT change. However refresh of second site will change the SNAPTIME column in SYS.SLOG$ table. SNAPTIME column in SYS.SLOG$ table always has the latest refresh time for corresponding site.

So we can see that MLOG$_T.SNAPTIME$$ – 2012-10-24:01:52 (this is the time when 1st site got refreshed)
min(SYS.SLOG$.SNAPTIME) – 2012-10-24:01:17

Since MLOG$_T.SNAPTIME$$ > min(SYS.SLOG$.SNAPTIME), row will not be deleted.

If I refresh 2nd site than SYS.SLOG$.SNAPTIME corresponding to that site will get updated with refresh time and in that case MLOG$_T.SNAPTIME$$ <= MIN(SYS.SLOG$.SNAPTIME). When this condition happens it will delete the record from MLOG$ table.

Logically this means that all the sites registered for this master table is been refreshed.

Lets try to refresh 2nd site. This should purge the record from MLOG. We will also see how the dates are updated in 3 tables on master site

After refreshing second site

No rows in MLOG$_T table

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

no rows selected

SNAPTIME for second site got updated in SYS.SLOG$ table

DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME
------------------------------ ---------------- ---------- ----------------
T                             15119 2012-10-24:01:52
T                             15120 2012-10-24:02:56

In SYS.MLOG$ table, Previous YOUNGEST became OLDEST_PK and new YOUNGEST is the latest refreshed MView timestamp.
Also, LAST_PURGE_DATE gets updated when a record gets purged in MLOG$_T table


DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER                   OLDEST_PK    YOUNGEST     LOG                MTIME         LAST_PURGE_DATE
------------------------------ ---------------- ---------------- ------------------------------ ---------------- ----------------
T                   2012-10-24:01:52 2012-10-24:02:56 MLOG$_T            2012-10-13:22:25 2012-10-24:02:56

In next article (probably last one of this discussion series), we will understand problems associated with MView Log, Modifying primary key on master table and what is “I am refresh”.

Hope this helps !!

Materialized Views Concepts – Discussion Series 2

Published on October 16, 2012 by Advait2 Comments

We have seen Discussion Series 1 of materialized view concepts and we know how to create materialized view and also what each clause of Mview creation mean.

In this article we will see all backend tables that can be accessed to check the details of materialized view.

We will begin with identifying materialized view

DBA_SNAPSHOTS (On MView site)

Most important table for checking MView info is DBA_SNAPSHOTS.

We need to query this table on snapshot site (where MView is created)

SQL>select name, table_name, MASTER, MASTER_LINK, REFRESH_METHOD, UPDATABLE , LAST_REFRESH, STATUS, PREBUILT, REFRESH_MODE from dba_snapshots where name = 'T_REP';
NAME TABLE_NAME MASTER MASTER_LINK REFRESH_MET UPD LAST_REFRESH STATUS PRE REFRESH_
---------- ---------- -------------------- ------------------------------ ----------- --- ---------------- ------- --- --------
T_REP T_REP T @"DB1.AMAZON" PRIMARY KEY NO 2012-10-13:22:26 UNKNOWN YES DEMAND

DBA_REFRESH_CHILDREN (On Mview Site)

we can group multiple materialized views into a group and refresh all materialized view all at once.

This can be done by creating a refresh group.

You can see all steps to create refresh group at http://docs.oracle.com/cd/B28359_01/server.111/b28327/rarmviewgroup.htm

Once you create refresh group and add MView to it, you can see the info in MVIEW_REFRESH_GROUPS table

BEGIN
   DBMS_REFRESH.MAKE (
      name => 'TEST_REF_GROP',
      list => '', 
      next_date => SYSDATE, 
      interval => 'SYSDATE + 1/24');
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'TEST_REF_GROP',
      list => 'T_REP',
lax => FALSE );
END;
/
SQL>select name, RNAME, REFGROUP, INTERVAL, TYPE from dba_refresh_children where name = 'T_REP';

NAME       RNAME REFGROUP INTERVAL TYPE
------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------
T_REP       TEST_REF_GROP     3693 SYSDATE + 1/24 SNAPSHOT

SQL>

DBA_SNAPSHOT_LOGS (On Master site):

If we want to check information about MLOG table, we can view this table.

SQL>select master, LOG_TABLE, ROWIDS, PRIMARY_KEY, SNAPSHOT_ID, CURRENT_SNAPSHOTS from dba_snapshot_logs where master = 'T';
MASTER       LOG_TABLE       ROW PRI SNAPSHOT_ID CURRENT_SNAPSHOT
------------------------------ ------------------------------ --- --- ----------- ----------------
T       MLOG$_T       NO  YES     15119 2012-10-13:22:26

Since this is primary key based MLOG, we can see YES for primary key column. CURRENT_SNAPSHOT gives when was this last refreshed. This is same as LAST_REFRESH column in DBA_SNAPSHOTS

DBA_REGISTERED_SNAPSHOTS (On Master site):

To Check how many sites are registered for 1 master table, we can query DBA_REGISTERED_SNAPSHOTS

This table has a column called name which is basically the name of Mview on MVIEW site. Since each MVIEW site can have a different name we cannot compare this column to get list of sites registered for 1 master table.

But we don’t have any master column in this table so we join this table with DBA_SNAPSHOT_LOGS to get list of sites which are registered for a master table

SQL>select a.master, b.name, b.snapshot_site from dba_snapshot_logs a, dba_registered_snapshots b
  2  where a.snapshot_id = b.snapshot_id
  3  and a.master = 'T';

MASTER       NAME       SNAPSHOT_SITE
------------------------------ ------------------------------ ------------------------------
T       T_REP       DB1.AMAZON

To check which snapshots has delay

We can use following query to check which snapshots has refresh delay in mins

select a.master, b.name, b.snapshot_site, (sysdate - a.CURRENT_SNAPSHOTS)*24*60 "delay Mins"
from dba_snapshot_logs a, dba_registered_snapshots b
where a.snapshot_id = b.snapshot_id
and (sysdate - a.CURRENT_SNAPSHOTS)*24*60 > &delay;

This will ask for delay and you can enter delay in mins.

After that this will list down all snapshots which are having delay more than what you entered.

Example, if you want to list down all snapshots having delay of more than 10 mins, you need to enter 10.

x$knstmvr

This is another internal table which can be used to check the progress of snapshots.

Columns in this table is self understood.

Following query will provide you the details of snapshot progress

column mvowner format a10 
Column mvname format a30 
column refmode format a8 
column refstate format a12 
column inserts format 99999999 
column updates format 999999999 
column deletes format 999999999 
column event format a30 
column spid format a6 
select  currmvowner_knstmvr mvowner, 
currmvname_knstmvr mvname, 
decode( reftype_knstmvr, 0, 'FAST', 1, 'FAST', 2, 'COMPLETE', REFTYPE_KNSTMVR ) refmode, 
decode(groupstate_knstmvr, 1, 'SETUP', 2, 'INSTANTIATE',3, 'WRAPUP', 'UNKNOWN' ) refstate, 
total_inserts_knstmvr inserts, 
total_updates_knstmvr updates, 
total_deletes_knstmvr deletes, 
b.spid,c.event 
from x$knstmvr X, v$session a, v$process b, v$session_wait c 
WHERE type_knst=6 
and a.paddr = b.addr 
and a.sid = c.sid 
and x.SID_KNST = a.sid 
and x.SERIAL_KNST = a.serial#;

In the next article about MView discussion series we will discuss about MLOG table and some internal details about fast refresh and how it works.

Hope this helps !!

Materialized Views Concepts – Discussion Series 1

Published on October 14, 2012 by Advait5 Comments

Materialized view concept: Why do we need materialized view?

Materialized views are nothing but views created on the base table and having data which is extracted from the base table.
How is materialized view different from the normal view.

Difference # 1:

Normal view does not contain data. It is just a transparent layer on the top of base

Materialized view contains data and additional space is required to create materialized view.
Difference # 2:

To use normal view, a user needs to provide the view name in the query

To use materialized view user does not need to provide materialized view name (although a user can, but its not required.)

Materialized views are required mainly for 2 reasons

1) Advanced replication of data from one location (database) to another location (database)

2) Summarizing the data in the table

When we are replicating the table from remote location to local location our queries can access the same data from local location which can lead to improved query performance.
Replication of data is possible using materialized views.

You can use materialized views to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables, aggregations such as SUM, or both. These operations are expensive in terms of time and processing power. The type of materialized view you create determines how the materialized view is refreshed and used by query rewrite.

Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response time.

Based on the above definitions we have 3 situations where materialized views can be used:

1) Materialized Views for Data Warehouses

In data warehouses, you can use materialized views to precompute and store aggregated data such as the sum of sales. Materialized views in these environments are often referred to as summaries, because they store summarized data. They can also be used to precompute joins with or without aggregations. A materialized view eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries.

2) Materialized Views for Distributed Computing

In distributed environments, you can use materialized views to replicate data at distributed sites and to synchronize updates done at those sites with conflict resolution methods. These replica materialized views provide local access to data that otherwise would have to be accessed from remote sites. Materialized views are also useful in remote data marts.

3) Materialized Views for Mobile Computing

You can also use materialized views to download a subset of data from central servers to mobile clients, with periodic refreshes and updates between clients and the central servers.

Its difficult to understand everything before we create a materialized view. So lets create a MView and understand every clause that can be given in creating MVIew.

Creating Materialized View:

Here goes the syntax of creating materialized view

CREATE MATERIALIZED VIEW SCHEMA.NAME 
(COL1, COL2, COL3 … )
ON PREBUILT TABLE / BUILD IMMEDIATE / BUILD DEFERRED
WITH / WITHOUT REDUCED PRECISION
USING INDEX / USING NO INDEX
TABLESPACE <TS NAME>
FOR UPDATE
REFRESH FAST / COMPLETE /  FORCE / ON DEMAND / ON COMMIT / START WITH .. NEXT / 
REFRESH WITH PRIMARY KEY / ROWID
ENABLE / DISABLE QUERY REWRITE
AS <SELECT QUERY ON MASTER TABLE>

This syntax is no way complete and there are many more clause related to storage, constraints, physical properties etc. But this is a basic DDL statement that is used most of the time to create materialized view.
If you want to see complete details of creating MView with all clauses, check reference section to get documentation link.

Lets discuss about each clause and what these values means to us.

ON PREBUILT TABLE

When you create a materialized view you can create the same on pre-built table. For example, before creating MView you were managing data replication using software and you were keeping a local table updated with latest data from some other master site. Now when you are implementing MView its a good option to use same table as prebuilt table and create MView on top of that. This way you can stop software replication and start using Mview.

If you don’t have any prebuilt table you can skip that clause along with “WITH REDUCED PRECISION” clause. If you are not using “ON PREBUILT TABLE” clause than you can use with “BUILD IMMEDIATE” or “BUILD DEFERRED” clause.
BUILD IMMEDIATE will immediately fetch the data info Mview. BUILD DEFERRED clause will defer fetching of data unless you do complete refresh manually.

Following things to be considered in case of PREBUILT Table

Materialized view name should be same as prebuilt table name. Master table can have different name
PREBUILT table can have extra column or less columns compared to master table, but Mview definition should include only those columns which are present in both master table and prebuilt table and corresponding columns must have matching datatypes.
If you don’t have any prebuilt table, create materialized view statement will create a table but it won’t be a prebuilt table. Meaning that if you drop Mview, your table will also be dropped (unless you use “preserve table” clause). But if its a prebuilt table, table will not be dropped even if you drop MView. This way you can retain data. So its essential to have a table marked as prebuilt table.

If you created materialized view without prebuilt table, it will create a table and there is a way to change that table as prebuilt table. So that if you drop materialized view some time in future, table will stay. Following update statement will convert a table into prebuilt table

update sys.snap$ set flag = 2228321 where vname = '<Mview Name>';
commit;

Above statement will convert a table into prebuilt table.

After that if you run the statement “drop materialized view <MView Name>;” it won’t drop prebuilt table.

Alternatively if you don’t have prebuilt table and you want to drop snapshot preserving the table you can use following statement

drop materialized view <MView Name> preserve table;

WITH / WITHOUT REDUCED PRECISION

Specify WITH REDUCED PRECISION to authorize the loss of precision that will result if the precision of columns in materialized view does not match with precision of column result returned by subquery. Remember that subquery is build on table on master site.
So this clause makes sense if you are using different precision of columns on Mview site than on master site.
WITHOUT REDUCED PRECISION will require the precision of columns in Mview to be exactly same as that of columns in table on master site (precision retuned by subquery).

USING INDEX

Using index clause can be used to specify the storage clause for index that will be created on materialized view. You can also specify the tablespace that should be used for all indexes on materialized view.
If you are creating ROWID based materialized view then “USING INDEX” clause will create default index like I_SNAP$_<table_name> which will be useful in internal MView management.
Using “NO INDEX VLAUSE” will prevent creating this default index and also you cannot specify storage clause and tablespace clause.

TABLESPACE <TS NAME>

This specifies the tablespace in which to create MVIEW.

ENABLE / DISABLE QUERY REWRITE

This clause will specify whether to enable or disable query rewrite on this MView.
One of the advantages of having materialized view is that our queries will automatically start using it (just like index)without doing any changes to the SQL. This capability is provided by query rewrite functionality.
The optimizer uses two different methods to recognize when to rewrite a query in terms of a materialized view. The first method is based on matching the SQL text of the query with the SQL text of the materialized view definition. If the first method fails, the optimizer uses the more general method in which it compares joins, selections, data columns, grouping columns, and aggregate functions between the query and materialized views.

You can find more details about query rewrite feature in link http://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm

REFRESH FAST / COMPLETE / FORCE / ON DEMAND / ON COMMIT / START WITH .. NEXT /

Fast refresh can be based on primary key or it could be based on ROWID.
If fast refresh if based on primary key then, master table should have primary key defined. MView log will have primary key included. For fast refresh to work, you must have MLOG created on master table on master site using following commands

create materialized view log on <Master table name>;

Fast refresh is most efficient way of maintaining data as it brings only the changed data from master site to local MView. It maintains the changes in MLOG.
Not all Mviews can be fast refreshed. You can check the restrictions on creating fast refreshable materialized view at http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1007028

Complete refresh does not need any MLOG. When we do complete refresh, complete data of master table is brought into MView. This method can be used for small tables.

Refresh Force will first try to refresh the Mview using fast method and if it doesn’t work, it will go for complete refresh.

Refresh On Demand will need DBAs to schedule a job which will do the refresh of Mview on periodic basis. If you don’t want to setup a job, you have to decide the schedule and refresh Mview yourself.

Refresh On commit will refresh Mview if a transaction gets committed on master site. We have to be careful with this option, if master site is located far away and commit rate is high, this is not a good option. Everytime a commit happens, it will try to refresh Mview, which is going to take time and eventually your transactions on master site will slow down.

Refresh Start with .. Next is used when we want to define the schedule in Mview definition while creating MView itself. This way Mview gets refreshed as per defined schedule

I tried to give basic overview of Materialized view and creating materialized view. Below are some examples of creating materialized view using above clause

Examples of Mview Creation

Example 1:

create materialized view T_REP
 ( OWNER,
   SUBOBJECT_NAME ,
   OBJECT_ID,
   DATA_OBJECT_ID ,
   OBJECT_TYPE,
   CREATED,
   LAST_DDL_TIME,
   TIMESTAMP,
   STATUS ,
   TEMPORARY,
   GENERATED,
   SECONDARY,
   NAMESPACE,
   EDITION_NAME)
 BUILD IMMEDIATE
 REFRESH ON DEMAND
 as 
 select OWNER,
        SUBOBJECT_NAME ,
        OBJECT_ID,
        DATA_OBJECT_ID ,
        OBJECT_TYPE,
        CREATED,
        LAST_DDL_TIME,
        TIMESTAMP,
        STATUS ,
        TEMPORARY,
        GENERATED,
        SECONDARY,
        NAMESPACE,
        EDITION_NAME
 from t@DB1;

Example 2:

Assuming we have primary key on master table and materialized view log on master site

create materialized view T_REP
 ( OWNER,
   SUBOBJECT_NAME ,
   OBJECT_ID,
   DATA_OBJECT_ID ,
   OBJECT_TYPE,
   CREATED,
   LAST_DDL_TIME,
   TIMESTAMP,
   STATUS ,
   TEMPORARY,
   GENERATED,
   SECONDARY,
   NAMESPACE,
   EDITION_NAME)
 ON PREBUILT TABLE
 WITH REDUCED PRECISION
 USING INDEX TABLESPACE OPS_IDX
 REFRESH FAST ON DEMAND
 ENABLE QUERY REWRITE
 as 
 select OWNER,
        SUBOBJECT_NAME ,
        OBJECT_ID,
        DATA_OBJECT_ID ,
        OBJECT_TYPE,
        CREATED,
        LAST_DDL_TIME,
        TIMESTAMP,
        STATUS ,
        TEMPORARY,
        GENERATED,
        SECONDARY,
        NAMESPACE,
        EDITION_NAME
 from t@DB1;

I hope this clears few things. This article is no way a replacement of Oracle documentation. I just tried to provide a concise information on creating materialized views.
My reference has links to Oracle Docs which provides detailed information.

In my next article of materialized view, I am going to provide some backend DBA tables which can be used to view information about materialized views.

Hope this helps !!

References:

Basic Mview creation – http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm
Basics of Query rewrite – http://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm
Oracle By Example. Creating Mview – http://st-curriculum.oracle.com/obe/db/10g/r2/prod/bidw/mv/mv_otn.htm

Sessions and Processes Parameters – Oracle 11g

Published on June 28, 2012 by Advait7 Comments

Oracle has changed the way it derives sessions and processes parameters in database.

In 10g, oracle used to derive sessions parameter from processes parameter using following formula

(1.1 * PROCESSES) + 5

In 11g R1 onwards it changed to

(1.5 * PROCESSES) + 22

It has another rule though:

If we set lower value of sessions parameters than derived value, Oracle will automatically bump it to above derived value.
If we set higher value of sessions parameters than derived value, Oracle will consider our set value

So it always takes which ever is higher

SQL>select name, value from v$spparameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               5000

SQL>select name, value from v$parameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               6776

We can see derived value (4500*1.5)+22=6776 is greater than set value of 5000 in spfile. So its taking derived value for this parameter

Lets change the value in spfile to 7000

SQL> alter system set sessions = 7000 scope=spfile;

System altered.

<< Bounce DB >>
SQL>select name, value from v$spparameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               7000

SQL>select name, value from v$parameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               7000

Now you can see its taking the set value, because set value of more than derived value of 6776.

Hope this helps !!

References:

Sessions parameter in 10.2 – http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams230.htm#REFRN10197

Sessions parameter in 11.1 – http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams191.htm#i1133629

Sessions parameter in 11.2 – http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams230.htm#i1133629

Oracle SQL Plan Management – Part 1

Published on June 2, 2011June 7, 2011 by Advait4 Comments

SQL Plan Management – Oracle Database 11g

SQL Plan Management or SPM is a new feature introduced in 11g. We will take a detailed look at SPM in the following sessions.
Before starting with practical example lets see some theory.

What is SQL Plan Management?

SPM is a new feature introduced in Oracle database 11g which stores the baseline (Plans + Hints) inside database.
SPM allows an Oracle DBA to capture and preserve the most efficient execution plans for any SQL statement, thus limiting the impact of refreshed optimizer statistics, changes to existing applications, and even upgraded database versions

Why do we need SPM?

SPM is basically used for plan stability. If you have a database environment with several queries running, you always want your queries to run the way they are running irrespective of the changes that you make in your environment.
Example, If you upgrade your database, your queries might behave differently because of the change in optimizer or change in environment and at initial run you have to fix many queries in your production DB because of the upgrade or change in the env.
To avoid this, SPM is introduce to give you plan stability. So even after change in the environment, your queries will use same plan as it was before. More plans (Better or worse) could exists in the new environment, but optimizer is not allowed to use those plans without you confirming the plan change.

What was wrong with outlines?

Well, outlines are more about fixing the plan for a query that optimizer will use. So if there is change in environment, the plan would still be the same.
There are certain situations where you want the plan to change with change in lets say data. What if your data gets skewed over time.

Intially you had 100 of entries for month “DEC” out of total 150 entries. So it was using FTS. Now with increase in the data, there are 1 million records and entries for month “DEC” are around 1000. In that case it makes sense for optimizer to change plan and start using Index scan instead of FTS.
But since you used outline, you are forcing optimizer to go for a FTS. This is just an example situation and things can change in your environment and so outline is not a very good approach for such situations.

What was wrong with profile?

Profile is another feature introduced in 10g and can be implemented at SQL level. Profiles are better than outlines in that they are not fixing a plan through out the life of SQL. With change in the environment the plans can change even if the profile is used.

So whats the difference between profile and SQL plan baselines?

Profile is more of a advisors. They give advice to optimizer while executing the query. They provide optimizer with all the precise estimates. Profiles are more abount correcting optimizer to use correct plan when the underlying data is skewed and changed drastically.
The goal is to create best execution plan for the SQL by giving the very precise data to the optimizer. Its the optimizer who will decide what should be the explain plan based on the information/hints it has received from profile.

SQL Plan baselines works differently. It enforces the plan to be used for a SQL. Baseline does not provide and estimates or it does not help optimizer in anyway. It just tells optimizer to ignore everything and use the plan that we are giving you.
Optimizer just followes the plan provided by baselines. So here baseline is the driving mechanism for getting the correct plan.

Finally how baselines are different then outline then?

Well, baseline always keeps the optimized plans for your SQL. If there is a change in the environment you will have a new baseline created for the same SQL. Oracle will stop that baseline in the baseline history.
Depending on the baseline parameters setting in your environment, new plan will be automatically used or you have to evolve the new plan for optimizer to use it.

So in a way SQL baselines are combination of outlines and profiles. It gives the stability of plan similar to outlines and it also allows capturing better plans in case the environment changes.

Not only that, SQL Baselines give the complete control to the DBA on

Whether to capture the new and better plans?
Whether to use the new plans automaticallly without DBA’s intervention.

So DBAs have complete control of the environment now. Far better than profiles and outlines.

SQL Plan Baseline Parameters:

Before we check how to use SQL Plan baselines, lets consider the significance of 2 important baseline parameters

1) optimizer_capture_sql_plan_baselines – Default “FALSE”

This parameter is responsible for automatic capturing the baselines for SQLs. If we set this parameter to TRUE we are asking oracle to automatically gather the baselines for the SQL.
When you run the query for the first time (and parsed version is not present in shared_pool), oracle consider that as a fresh new query and does not create a baseline.
When you run the query for second time, oracle will consider the query as repetative and will automatically create the baseline.
These baseline are stored in DBA_SQL_PLAN_BASELINES table.

If this parameter is set to FALSE, then we (DBA) has to create baselines for the SQL manually. There are 2 procedure available for creating baselines manually.

1) Using DBMS_SPM.LOAD_PLANS_FROM_SQLSET

This procedure is usually used when we create SQL tuning set and store our SQLs into the tuning sets. Normally done before we upgrade the database to 11g. In our 10g database we create SQL tuning set and store all our SQL. Once we upgrade to 11g, we can create baselines for all our plans in SQL tuning set. That why what ever plans were effective in 10g, same will be used in 11g and there wont be any plan flips.

2) Using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

This is used when we want to fix one of the SQL currently running in our 11g database. We just load the plans from cursor cache (shared_pool) and create baseline out of that. We need to give SQL ID as input to this procedure.

We will see how to create baseline using manual method at later point of time.

2) optimizer_use_sql_plan_baselines – Default “TRUE”

This parameter will allow optimizer to use the baselines present in DBA_SQL_PLAN_BASELINES table. If you set this parameter to FALSE, then your 11g DB will start behaving same as 10g DB.
If there is any change in environment then it might flip the plan. Keeping this parameter TRUE is important in 11g.

How to use SQL Plan Management baselines ?

Lets take an example in a test database.

Table T with 1540 records.

SQL> select count(1) from t;

  COUNT(1)
----------
      1540

Data is skewed and distribution is as given below.

SQL> select col1, count(1) from t group by col1;

      COL1   COUNT(1)
---------- ----------
         1          1
         2          3
         3       1536

Gather the stats on the table

SQL> exec dbms_stats.gather_table_stats(OWNNAME=> 'ADVAITD_DBA',TABNAME => 'T', DEGREE => 6, GRANULARITY => 'ALL' ,CASCADE => TRUE , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254');

SQL>

Currently I dont have any baseline.

SQL> select count(1) from dba_sql_plan_baselines;

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

SQL>

My baseline parameters setting is as below.

SQL> show parameters baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>

Let us first consider the auto capture utility for baselines.

AUTO Capture of baseline

SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;

Session altered.

SQL>

As mention earlier, we need to run the query 2 times in order to automatically create the baseline.

SQL> select * from t where col1 = 1;

      COL1 COL2                                               COL3
---------- -------------------------------------------------- -----------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011

SQL> select * from t where col1 = 1;

      COL1 COL2                                               COL3
---------- -------------------------------------------------- -----------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011

If we check DBA_SQL_PLAN_BASELINES we will see a sql baseline created.

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

Turning off auto SQL plan baseline capture

SQL> alter session set optimizer_capture_sql_plan_baselines=FALSE;

Session altered.

SQL>

Following statement gives the plan stored in the baseline. DBMS_XPLAN has a new procedure DISPLAY_SQL_PLAN_BASELINE which will display the baseline.

SQL> set line 999
SQL> set pagesize 999
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920f94ecae5c'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from t where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920f94ecae5c
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   735 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    15 |   735 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

24 rows selected.

SQL>

Origin: AUTO-CAPTURE shown above tell us that this baseline is captured automatically.

Lets now create an index and gather stats over index.

SQL> create index t_idx on t(col1);

Index created.

SQL> exec DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => 'ADVAITD_DBA', INDNAME=>'T_IDX');

Run the same query now, since the index is created, we expect the query to use the index.

SQL> explain plan for
  2  select * from t where col1 = 1;

Explained.

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  |      |    15 |   735 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    15 |   735 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920f94ecae5c" used for this statement

17 rows selected.

SQL>

The reason we are seeing full table scan is because of the NOTE at the end, which says “SQL plan baseline “SYS_SQL_PLAN_1d83920f94ecae5c” used for this statement”

Since we have a baseline created for this SQL, it will not allow the plan to be changed. This is the kind of stability that SQL Plan baseline gives.
But using an index will be beneficial in our case.

If we check DBA_SQL_PLAN_BASELINES we can see a new plan has been created (PLAN_NAME = SYS_SQL_PLAN_1d83920fae82cf72), but it is not yet ACCEPTED. The plan is enabled though.

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO

We can check what the new plan looks like using dbms_xplan.display_sql_plan_baseline

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from t where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920fae82cf72
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

25 rows selected.

SQL>

As seen above, new plan uses index. Lets evolve this plan now.
Evolving a plan includes evaluating the cost of the plan and accepting if the plan seems to be better than all accepted plan for this query.

SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1447ba3a1d83920f') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_1447BA3A1D83920F')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_1447ba3a1d83920f
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_1d83920fae82cf72
-----------------------------------
  Plan was verified: Time used .01 seconds.
  Passed performance criterion: Compound improvement ratio >= 7.33
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):               0              0
  CPU Time(ms):                   0              0
  Buffer Gets:                   22              3              7.33
  Disk Reads:                     0              0
  Direct Writes:                  0              0
  Fetches:                        0              0
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.

Sometimes, your plan may not get evolved because oracle see that there are other already ACCEPTED plans which are better than the plan you are trying to evolve.
But if you know your plan will be better and still want to deploy the same, you can do so by manually changing the attributes ACCEPTED and ENABLED as shown below.

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
    attribute_name  => 'ENABLED',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
    attribute_name  => 'ACCEPTED',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

You can also set the value of attribute FIXED using the above function. Here is the meaning of ENABLED, ACCPETED and FIXED

ENABLED – ‘YES’ means the plan is available for use by the optimizer. It may or may not be used depending on accepted status.
ACCPETED – ‘YES’ means the plan will be used by optimizer while running the query. ‘NO’ means optimizer will not use the plan.
FIXED – ‘YES’ means the SQL plan baseline is not evolved over time. A fixed plan takes precedence over a non-fixed plan.

Once you evolve the plan, you can see that plan is ACCEPTED now.

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO

Now if you run the explain plan you can see Index T_IDX is getting used.

SQL> explain plan for
  2  select * from t where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

18 rows selected.

SQL>

Hope this helps.

Part 2 can be viewed at –https://avdeo.com/2011/06/07/oracle-sql-plan-management-%E2%80%93-part-2/

References:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optplanmgmt.htm

http://www.comp.dit.ie/btierney/oracle11gdoc/appdev.111/b28419/d_spm.htm

Exchange Partition in Oracle

Published on March 30, 2011May 23, 2011 by Advait5 Comments

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

Published on March 23, 2011May 23, 2011 by AdvaitLeave a comment

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

Published on March 17, 2011May 23, 2011 by Advait10 Comments

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 !!