Oracle Myths

We come across many things in Oracle, which we think them as facts are actually MYTHS. Let us see some of the Myths in Oracle that we come across –

1.) Grid and RAC one and the Same –

Obviously, without any doubt we can say its a MYTH. Let us see how it is –

In general a GRID is a concept of multiple systems that combine together to work as a single unit. Oracle 10g works on the same philosophy, where multiple nodes combine together to work as a single system. Where these nodes will run the databases, applications, webservers etc.,

RAC uses the concept of GRID, where it combines several nodes to work as a single unit. RAC may be part of GRID, but not necessarily required.

So from the above we can sat that “A GRID can build without RAC, whereas a RAC is build using the concept of GRID”.

Thus we can say that the myth “Grid and RAC one and the Same ” is busted.

2.) High Buffer Cache Hit Ratio means good system performance!!!!-

Now we need to see whether this is a Fact or a MYTH. Right!!! Ok … We are not goinig to discuss this in deep, rather in straight forward way.

Lets us see the formula for Database Buffer Cache Hit Ratio (DBHR)

            PIO                       Physical Reads
DBHR  = 1 - ----     =    1 -  ---------------------------------
            LIO                  db block gets + consistent gets

where PIO = Physical Reads i.e reading from disk .
      LIO = Logical Reads i.e reading from buffer.

When it comes to logical reads, data blocks in Buffer cache are in 
different modes. 

      DB BLOCK GETS - reading the blocks that are in CURRENT mode. 
                      Reading these kind of blocks are rather straight 
                      forward and does not require any further processing.
     CONSISTENT GETS- reading the blocks that are in CONSISTENT mode. 
                      Sometimes, to make these blocks(may be a dirty 
                      block perhaps!!) consitent you need to reconstruct 
                      those blocks using the Rollback(UNDO) segments. 
                      This reconstruction requires more resources
                      (more reads infact!!!) and so high CONSISTENT GETS.

From the above formulae we can say that – a high buffer cache hit ratio means good system performance. Right!!! This is what the theory !!! Let us see that in practically and determine whether that theory is correct or not?

As per the above mentioned formulae, High Logical Reads(LIO) implies higher buffer hit ratio, which means good system performance. But in reality, high logical reads(LIO) is not a good sign. Every Oracle LIO call requires a latch-serialized search of the database buffer cache hash table, and a parse of the contents of the multi-kilobyte Oracle block itself. Serialization devices like Latches, inhibit scalability, the more you use them, the less concurrency you get. This leads to cotention.

So having a high database buffer cache hit ratio does not imply that you have an efficient high-performance system. There are many situations in which there is no correlation between system performance and database buffer cache hit ratio.

It is always advised that, to keep LIO’s as low as possible to have better performance.

So from the above discussion we can say that the MYTH “High Buffer Cache Hit Ratio means good system performance” is busted.

3.) If a query doesn’t use an index, then it is considered as broken –

Sometimes it happens like , eventough there is an index, the query doesn’t use it . So naturally we assume that the Index is Broken. Is it a Fact or Myth? Let us see –

We cannot just come into conclusion that the index is broken, as it was not been used by the query. There might be various factors that makes it not to use the index. Some of those factors are like –

      1.) Oracle Optimizer might assume that Full table scan might be 
          cheaper than accessing an Index.
      2.) Bad Statistics, which needs to reanalyze tables and indices.
      3.) or May be not using a correct Optimizer mode.
      ....

These are the some of the factors that ceases a query to use Index, but not necessarily be due to broken Index.

So, from the above discussion it is proved that the MYTH “If a query doesn’t use an index, then it is considered as broken ” is busted.

4.)Using Multi block sizes in OLTP databases improve performance

Lets see whether its a MYTH or FACT.

Oracle9i introduced a new feature called Multi block size that is useful for databases which need the flexibility of using a small block size for transaction processing applications (OLTP); and a larger block size to support batch processing applications, decision support systems (DSS), or data warehousing.

Basically this feature is intended to transport data from a transactional system to a warehouse and to be used only to ETL the OLTP data. This cannot be considered as a Performance/Tuning feature, but rather it is used for transportable tablespaces.

Using this feature on normal transactional databases is not much worthier because the administrative overhead is much bigger than the performance benifit you get by using it. It will end up with either Oversized or undersized cache size(db_XXK_cache_size).

Usually, users want to segregate their data by keeping data segments on tablespaces that have small data block size and Indexes on tablespaces that have large data block size. But it is advised to use the other MULTIPLE(Buffer Keep, Recycle) POOLS, rather than using the Multi block size(s).

Why you need a buffer – to reduce I/O, which means to avoid going to disk everytime you request for a block. Right!!!. Then why not you use ‘Buffer Keep’ that pins the objects in memory. So that you never need to ‘Physical Read’ for those objects that are pinned. Just identify the objects which your applications frequently use and Pin them in Buffer keep pool. In the same way use the ‘Recycle Pool’ for the objects that involve in frequent full-table scans.

Dont use(misuse?) the FEATURES, just because you have it, instead use it for the purpose that it was intended for.

Hence the Myth “Using Multi block sizes in OLTP databases improve performance” Busted.

5.)Keeping indexes in seperate tablespace will improve performance…

We will be encountering this kind of statement, while dealing with improving performance – i.e by seperating Indexes on a seperate tablespace. Is it a Myth or Fact? Let’s see ..

In Oracle, many things that are facts today become Myths of tomorrow – this is mainly because of advancement in technology and improvement/development in Oracle from version to version. The above statement too a ‘victim’ for that.

The statement was true, when it was said during time when there was no Striping, logical volumes, and just have Direct Attached Storage etc., In that situation, keeping index on a seperate tablespace will improve performance as it evenly distrubutes IO between the 2 tablepaces(data & index).

But today, technology imporved a lot , we have RAID striping, logical volumes, SAN storages etc., With these techologies in hand there is no meaning of improving performance just by seperating INDEXes on a seperate tablespaces. Moreover, there is no harm in keeping INDEXes on a seperate tablespace, but this cannot be viewed as a Tuning factor.

Thus we can say that the myth “Keeping indexes in seperate tablespace will improve performance” is busted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s