Monthly Archives: January 2012

Hints , Outlines, SQL Profiles & SQL Plan Management Compared

Oracle January 28, 2012

My last blog was comparison of DataGuard, GoldenGate, Streams and RAC.  While writing my previous blog , the idea of comparing Hints , Outlines, SQL Profiles &  SQL Plan Management stuck me.  I am kind of in a compare-maniac mode and here   goes some comparison of some key features.  I also think  you can understand technology better when you compare with something you know.

I will add more content to this blog as I get more time. The table format had to be changed to fit the data.

Definition
Hints Optimizer is not making decisions regarding the execution plan, Instead you are. The pros are that it provides stable execution plan and also lets you tune vendor client applications without rewriting the SQL. The cons being that you might end up with sub-optimal execution plan. You also may not be able to take advantage of newer optimizer logic as you upgrade.
Outlines Collection of hints stored in OL$ , OL$HINTS and OL$NODES tables. Oracle uses this information in the table to come up with the execution plan.
SQL Profiles Conceptually SQL profiles and outlines are same. Just like outlines, SQL profiles are collection of hints. The main difference between  outlines and  SQL profiles is Outlines have limited  artificial intelligence embed into the feature compared to SQL profiles which means SQL profile may wander  around from the original  purpose  of  “providing  stable plan”  based on additional  data collected.  As always data distribution is the key culprit (generally).
SPM SPM or SQL Plan management support plan stability by kind of guarantying that your query performance will not degrade because of change in execution plan SPM’s also support evolution of execution plan.
Oracle Version
Hints Since Oracle 7
Outlines Since Oracle 9i
SQL Profiles Since Oracle 10g
SPM Since Oracle 11g
Lock Execution Plan
Hints Varies, Oracle can ignore hints
Outlines Yes, Most of the time
SQL Profiles No,  May deviate from  original intended plan based on OPT_ESTIMATE or  COLUMN_STATS or TABLE_STATS
SPM Yes
How to create?
Hints Not Applicable
Outlines CREATE_STORED_OUTLINE, DBMS_OUTLN.CREATE_OUTLINE AND CREATE OUTLINE
SQL Profiles DBMS_SQLTUNE, SQL  Tuning Advisor, AWR
SPM OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES , AWR
How to Use?
Hints /*+ hints */
Outlines Set USE_STORED_OUTLINE at session or system level.This  is not an init.ora parameter
SQL Profiles Enable/Disable the SQL profile
SPM Set OPTIMIZER_USE_SQL_PLAN_BASELINES at system or session level
Support  for Private plans
Hints N/A
Outlines YES
SQL Profiles N/A
SPM YES
Overhead
Hints None
Outlines YES, Additional checks for every statement Oracle parses.
SQL Profiles YES, Minimal
SPM No
Support for Multiple Executions at any point in time
Hints NO
Outlines NO
SQL Profiles NO
SPM YES
Use unstable execution plans.
Hints YES
Outlines YES
SQL Profiles YES
SPM NO

.

Dataguard, Streams, GoldenGate and RAC Compared

Oracle January 15, 2012

Dataguard: Copy of primary database  used mainly for disaster recovery.  There are two types of dataguard

  1. Physical dataguard:  Physically identical to primary database on a block-block basis.
  2. Logical dataguard:  Same logical data as primary; physical organization and structure can be different.

Streams: Propagates data and events from one database to another or within same database

 GoldenGate: Capture, transform, and deliver data. Supports heterogeneous databases.

RAC: Real Application Clusters is cluster database with shared cache architecture to overcome the limitations of traditional shared-nothing and shared-disk. In simple terms, it is multiple instances of Oracle sharing the same database. To avoid confusion, please note an instance is defined as of SGA and Oracle background process.

Dataguard Streams GoldenGate RAC
Main Objective  

-Disaster Recovery

-Data Protection

-High Availability

 

 -Data Replication-Data Sharing -Data Replication

-Data Sharing

 -High Availability

-Scalability
Platform**/OS/Oracle SW  

-Must  be same

 

 -Can be Different -Can be Different -Must  be same
Full Replication  

-Yes

 

 -Yes -Yes -No
Heterogeneous DB Support  

-No

 

 -No -Yes -No
Replicate Data Subset  

-Yes (Logical Only)

 

 -Yes -Yes -No
Near Zero Upgrade  

-Yes

 

 -Yes -Yes -No

**Platform:  Depending on definition of platform, they can be different. For example P550 can be used instead of P560 for Dataguard

Want to know when there is change in query execution plan ?

Oracle January 7, 2012

Want to  get alerted whenever there is  change  in execution plan?  Try the below query.

COLUMN SQL_TEXT FORMAT A100
SET LINESIZE 150 PAGESIZE 2000
SET LONG 300000

SELECT DISTINCT A.SQL_ID ,A.PLAN_HASH_VALUE , B.SQL_TEXT FROM V$SQL_PLAN A, V$SQL B
WHERE A.SQL_ID IN (
SELECT SQL_ID  FROM V$SQL_PLAN
WHERE OBJECT_OWNER=’SCOTT‘
AND TIMESTAMP>SYSDATE-1
GROUP BY SQL_ID
HAVING COUNT (DISTINCT PLAN_HASH_VALUE) >1)
AND A.SQL_ID=B.SQL_ID

You can choose to run the query every hour or  once every day depending on the sensitivity of your applications to plan changes. It is good to change the TIMESTAMP”  accordingly  in the query. You can also ORDER BY SQL_ID if required.

Redo Logs on RAID-6

Oracle January 1, 2012

We migrated our storage from DMX-4 to VMAX.  I was some what uncomfortable  with one of our main architectural decision of going with RAID-6 because of our millisecond SLA’s. Off course I was wrong and our excellent storage gurus were right. I did lot of research about Oracle installations on RAID-6 especially redo logs. Unfortunately I did not find any. The whole purpose of this article to provide some insight into performance of redo logs on RAID-6. Also the intention of this article is not to explain different types of RAID levels, So I will just to cover some basics before introducing redo logs.

RAID stands for  “redundant array of inexpensive disks”  used to provide highly available disks by storing or striping data across multiple hard drives. Storing data across multiple disks also improves read performance.   I will talk about few important RAID levels.

RAID-0:  Striping with no redundancy. Improved performance but no fault tolerance.

RAID-1: Disk Mirroring i.e.. data is duplicated. Improved read performance with minimal impact to writes.

RAID-10: Combination of RAID-0 and RAID-1.

RAID-5:  Striping and parity information stored in a cyclic/rotating fashion. Some impacts to write operation

RAID-6:  Striping and double parity information stored in a cyclic/rotating fashion. Some impacts to write operation

..

Now coming to redo logs. Typically Oracle recommends RAID-1 for redo logs. As mentioned earlier, RAID-1 provides increased read performance with minimal impact on writes . However this always comes with additional  cost because of hard disk mirroring.  Most of the time, apart from high concurrency ,  redo log  writes happen to be the major  bottleneck for a tuned database because transaction is considered  committed when change is written to redo log files.

With advancement of storage technologies, RAID-1 may not be necessarily required for improved redo performance. Most of the vendors provide storage cache with  the cache itself  being  mirrored for fault tolerance. This means the modified data is considered  committed   by the storage vendor when it is written to cache. All this is transparent to  Oracle. Therefore  performance gain with RAID-1 may be  insignificant. compared to RAID-5 or RAID-6.  We did not notice any performance impact with RAID-6 even with synchronous SRDF because of storage cache.