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...)

ABMR – Automatic Block Media Recovery

ABMR is a cool feature; most of us know about it, so instead of talking about ABMR, want to share the issues I faced.

First one was, Oracle did not bother to start the abmr background process and attempt block recovery.   Alert log exclaimed about the block corruption and (more...)

Soft prase and session_cached_cursors

session_cached_cursors is one of the neglected parameter in oracle environment which specifies the number cursor cached in the PGA of a session.  Any SQL executed multiple times will be cached and executed with a softer soft parsing.  There can be 3 scenarios for any parse request.

SQL parsing (more...)

Need helping hands

This is not the first time I was going to Swanthana (as part of St. Vincent De Paul Society, reach out to the poor)   - a home for mentally challenged and disabled girls abandoned by their families.   Each time I went there, I forgot problems in my life and (more...)

Cursor Invalidation

Got a question: What will happen a running curosr got invalidated?  SQL execution will fail or invalidation is not at all possible?

There are two components for an executing cursor.  A session cached cursor (private area where row source are getting processed) in PGA and sharable execution plan (shared excution (more...)