Bind peeking and session cached cursor

Bind peeking is a nice feature in Oracle to have many optimized plans for an SQL for various bind values.  DBAs believe that bind peeking happens during a soft parse which will identify an alternate plan. Why do I say that?


Hard Parse: Parsing first time, nothing exists to bind peek


Soft Parse :  SQL cursor is existing and executing not the first time.  Under the soft parse, bind peeking (more...)

CRS Timezone

Interesting problem – database was running normally without any issues and added to OCR.  But the log file started reporting a different time for all activities – for example.

Database and server is showing a time 4:25 while alertlog is ahead of 5 hours @9:25!

oracle@prod # tail alert_xxxxxxx.log

Recovery of Online Redo Log: Thread 2 Group 16 Seq 129057 Reading mem 0

  Mem# 0: +ORAINDEX1/prod/onlinelog/group_16.282.864560747

Mon Jan 12  (more...)

enq: SQ – contention

Yesterday, I have seen huge waits “enq SQ – contention’”  – in every snapshot there were thousands of waits.  But the fix was so simple!  Here is the root cause of the issue –

When you select from a sequence, the NEXTVAL generated from a the seq$ table if it is not cached.  If it is cached, it will be available in a memory structure and no need to generate the value (more...)


Happy New Year!


Oracle supplied various tools to trace the SQL and identify execution plans for the SQLs.  10046 Event, AUTOTRACE, DBMS.XPLAN etc are some of the most used tracing methods in DBAs daily life.  Sometimes, we need to be very careful while using these tools specially using bind variables.  Following are some test cases where wrong plans reported by the above tracing tools.


Most easiest method to get (more...)


APPEND hint in oracle is very useful, there are many benefits

Fragmented data loading – data will clustered above the HWM so that similar data will be together. Otherwise Oracle will place the records in blocks using PCT_FREE rule

  • No undo will be generated – in large direct path insert will free up large amount of data blocks
  • Less CBC latches (because of no UNDO blocks), less CPU usage
  • Readers need not undo the records, (more...)

Consistent gets – How Many?

When you execute an SQL – why there is a difference in Consistent gets on the same set of data for same SQL.    For any SELECT query, oracle need to prepare the consistent data in the buffer cache using undo records then forward the data the requesting session as of a specific SCN.  Touching any block in buffer cache to prepare the blocks for consistent data is known as Consistent Reads.

In an (more...)

Elapsed Checkpoint Time

Adieu to 2013 with this small post!  Happy new year to every one.

Oracle controls the incremental checkpoint frequency or interval based on the parameter FAST_START_MTTR_TARGET  or  LOG_CHECKPOINT_INTERVAL.  But, can you see the real checkpoint interval values?  What is the elapsed time between checkpoints?

Yes, you can. (more...)

log_archive_dest_?? is ISPDB_MODIFIABLE?

In Oracle 12c, some of the parameters can be changed in a pluggable database if the impact is limited to the current container.   However, the global parameters like SGA_TARGET can be set only through root container – CDB$ROOT.    A new column ISPDB_MODIFIABLE is added  in V$PARAMETER view to list (more...)

Memory flushing and PDBs

In a multitenant database environment, PDBs are plugged into a CDB container sharing single SGA.  All the PDB objects are loaded into a single SGA component and there are no ISPDB_MODIFIABLE SGA parameters.  Looking at the V$LATCH_CHILDREN, all the CBC latches defined under CON_ID  1 which is CDB$ROOT.


IMU and CR Reads

IMU and Private strands are interesting new entries in Oracle 10g.  These to private buffer to a transaction will help to avoid many expensive latches both in buffer cache and log buffer.  There are tons of data available in the net about these subjects and many experts spoke (more...)