Side effects of Index Fast Full Scan on OLTP

The goal of this blog is not to analyze the reasons behind working of Oracle optimize as every database implementation is unique. The main objective  is to understand what happens when Oracle optimizer chooses Index Fast Full Scan over Unique Scan. Significantly more occurrences of this behavior was observed after upgrading to Oracle12c.

As per Oracle documentation, An index fast full scan reads the index blocks in unsorted order, as they exist on disk. This scan does not (more...)

My initial experience upgrading database from Oracle 11g to Oracle 12c (Part -1)

This is work in progress document.

Just wanted to share some experience upgrading database from Oracle 11g  to Oracle 12c; One thing I want to avoid is just go over best practices  instead I want to go over some of the stuff that we encountered.. May  be because of  bug ; some of them may be because of undocumented changes to Oracle 12c ;Some of them may be because of lack of in-dept research.

First thing that (more...)

Oracle 12c: DataPump import in NOLOGGING mode

I have not been blogging for quite some time now and thought of ending the year with one last blog. I picked a very simple  yet powerful datapump topic for blogging. Read on …

As is the norm after exhausting all tuning options, we kind of hope that redo is the bottleneck for all performance problems :). So if your datapump performance issues still exists after multiple tuning iterations, As DBA’s you explore ways to run the import in NOLOGGING (more...)

Oracle 12c: Advanced Network Compression

Most of you are aware that Oracle does simple compression when you have a ordered result set by sending the data once along with count for duplicate data. This is prior to Oracle 12c. Starting with Oracle 12c, Oracle supports Advanced Network Compression. Too bad, it is available as part of Advanced Compression option.

Lets forget Oracle for sometime and look at what are the main constraints for networking? It is generally the network bandwidth (more...)

Oracle12c : Truncate Table Cascade

Prior to Oracle 12c truncating tables with dependent children was a  pain. We had to disable the constraint before truncating the parent table followed  by enabling the constraint.  The performance gain achieved using TRUNCATE instead of DELETE  almost disappeared if the constraints were enabled with VALIDATE option.  If enabling the constraint with NOVALIDATE option was not acceptable, then DELETE seemed  to be the only way to go.

Starting with Oracle 12c , Oracle supports truncating (more...)

Oracle 12c RMAN New Features

In Oracle 12c , there are lot of new great RMAN features  focused on reducing recovery time; Some of them to provide better DBA :) experience too .Let us  glance at some of them in this blog.

  1. Support for point in time recovery for tables  and partitions. I would rate this as one of best options of Oracle 12c.
  2. Option to duplicate database with NOOPEN option so that duplicated/cloned database remains in mount state. Prior to (more...)

Oracle 12c : Partial Indexes

Let us assume you have a table with ten columns and four B*Tree indexes. If you analyze the space usage; chances are very high that size of all indexes together is nearly equal to more than the size of the table.  If you end up creating more indexes to support complex business requirements, the size of all indexes together could be 2-4 times(or more) the size of tables. So what is the big deal, (more...)

Oracle 12c Extended Datatypes

Extended Datatypes

Prior to Oracle 12c, VARCHAR2/NVARCHAR2 datatypes  allowed storing variable length characters of up to 4000 bytes whereas RAW data type  allowed storing variable length binary data of up to  2000 bytes.  In Oracle 12c, the maximum size for data types VARCHAR2, NVARCHAR2  and RAW is increased to 32767 bytes.  These data types are now referred to as extended data types. Extended data types are implicitly implemented using concept of LOBs;  Just (more...)

Oracle 12c: How to setup your database to support extended datatypes?

This blog discusses the setup required to support extended datatypes in Oracle12c; For more information about extended datatypes, please refer to

In  nutshell , the following steps are required to enabled extended datatypes. Most of the  steps are familiar except for step 3 and 4. In step 3 , we are modifying initialization parameter MAX_STRING_SIZE from STANDARD to EXTENDED. Once changed , this is a irreversible action.  Step 4  increases the sizes (more...)

PURGE DBA_RECYCLEBIN System Privilege in Oracle 12c

PURGE DBA_RECYCLEBIN  command empties the recyclebin at  database level i.e. purges the recyclebin for all users in the database.  Until 11g , you need to have powerful  SYSDBA system privilege to execute this command.  To some extent it made sense as this option was mainly used for upgrades or migration or to apply DST patch. One of focus areas of Oracle12c is separation of duties resulting in  new roles like SYSKM for (more...)