December 25, 2010

Goldengate error 1008-ORA-01008: not all variables bound

Filed under: Uncategorized — srivenu @ 6:34 am

I was trying to implement Oracle goldengate solution in one of our databases. I decided to use the latest version of Oracle GoldenGate – 11g Release 1 (11.1.1).
When I tried out a simple extract i faced the following error.
ERROR OGG-00665 OCI Error executing statement (status = 1008-ORA-01008: not all variables bound), SQL< SELECT c.constraint_name, c.column_name FROM all_cons_columns c WHERE c.owner = owner1 AND c.table_name = :table1 AND c.constraint_name = (SELECT FROM>

The SQL could vary depeding on your extract parameters but the error is the same. Setting NLS_LANG=”.WE8ISO8859P1″ (same as DB) did not resolve the issue. 

Found one note relating to  the error on the net, but i had already added supplementary logging.

Searching Metalink revealed a note which pointed out to a bug in Goldengate if CURSOR_SHARING parameter is not set to EXACT. Adding the following SQLEXEC command to the EXTRACT and REPLICAT parameter files resolved the issue.

Sample extract parameter file

EXTRACT extr_x
USERID gguser, PASSWORD temp

Sample Replicat parameter file

USERID gguser, PASSWORD temp

December 24, 2010

Identifying the complete Oracle Database Software Release

Filed under: Uncategorized — srivenu @ 2:58 am

I recently found out that identifying the complete Oracle software release is not just as simple as querying V$VERSION or DBA_REGISTRY.

One of our databases was recently upgraded from to And our team has pointed that querying COMP_ID, COMP_NAME & VERSION from DBA_REGISTRY was showing for all components except OWM, Oracle Workspace Manager which was showing We raised an SR and was pointed to Metalink Note 861152.1. Looks like we need to query REGISTRY$HISTORY or use the opatch command “lsinv -bugs_fixed | grep PSU” to get the PLATFORM RELEASE NUMBER.

The following is an example from one of our 11g databases (on SunOS 5.10 Sun-Fire-15000) where we applied the Patch Set Update.

--> sqlplus / as sysdba

SQL*Plus: Release Production on Fri Dec 24 17:02:54 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE      Production
TNS for Solaris: Version - Production
NLSRTL Version - Production

SQL> select * from product_component_version;

PRODUCT                                  VERSION    STATUS
---------------------------------------- ---------- --------------------
NLSRTL                          Production
Oracle Database 11g Enterprise Edition 64bit Production
PL/SQL                          Production
TNS for Solaris:                Production

SQL> select COMP_ID, COMP_NAME, VERSION, STATUS, MODIFIED from dba_registry;

COMP_ID    COMP_NAME                                VERSION    STATUS    
---------- ---------------------------------------- ---------- ----------
EM         Oracle Enterprise Manager       VALID     
AMD        OLAP Catalog                    VALID     
SDO        Spatial                         VALID     
ORDIM      Oracle Multimedia               VALID     
XDB        Oracle XML Database             VALID     
CONTEXT    Oracle Text                     VALID     
ODM        Oracle Data Mining              VALID     
EXF        Oracle Expression Filter        VALID     
RUL        Oracle Rules Manager            VALID     
OWM        Oracle Workspace Manager        VALID     
CATALOG    Oracle Database Catalog Views   VALID     
CATPROC    Oracle Database Packages and Types VALID     
JAVAVM     JServer JAVA Virtual Machine    VALID     
XML        Oracle XDK                      VALID     
CATJAVA    Oracle Database Java Packages   VALID     
APS        OLAP Analytic Workspace         VALID     
XOQ        Oracle OLAP API                 VALID     

17 rows selected.

SQL> Select ACTION_TIME, ACTION, VERSION, COMMENTS from registry$history;

ACTION_TIME                    ACTION           VERSION         COMMENTS                
------------------------------ ---------------  --------------- --------------------    
09-SEP-10 AM   VIEW RECOMPILE                   view recompilation
09-SEP-10 AM   UPGRADE      Upgraded from
09-SEP-10 AM   APPLY          PSU           

--> opatch lsinv -bugs_fixed | grep PSU|awk '{print $1,"",$2,"\t",$9,"\t",$10,"\t",$11}'
8974548  9654983         BACKOUT         BUG     7438445
9352237  9654983         DATABASE        PSU
9654983  9654983         DATABASE        PSU

Parallel Query – events in waitclass Other – PX Deq Credit: send blkd

Filed under: Uncategorized — srivenu @ 2:16 am

This entry relates to a create table statement. The table in question is created every day in the report database by joining several mviews.
The table creation which normally completed in a hour hasn’t completed even after several hours yesterday.
The sql is something like this

CREATE 	TABLE 	User.abcd nologging parallel 8 AS
	with 	tbl as (
		select	service_type, circle_id 
		from 	User.F
		group 	by service_type, circle_id
SELECT	.....

One of the Mview tables was missing statistics. The initial plan was showing an index access on that table. I added the full & use_hash hints and without properly observing the resulting plan ran the hinted sql. After 20 mins of execution, when i monitored one of the parallel slave sessions (V$SESSION_EVENT), i observed that almost 18 mins of elapsed time was spent on “events in waitclass Other”. I immediately suspected CPU exhaustion (violating the cardinal rule of tuning – “Never assume anything”). But sar showed 30% cpu free. I ruled out CPU exhaustion, and a short duration continuous monitoring of session waits showed a steady stream of “PX Deq Credit: send blkd” waits. (For more complex situations, you could use the excellent waitprof script from Tanel Podar). Again i violated the cardinal principle and checked PARALLEL_EXECUTION_MESSAGE_SIZE. It was set to a decent size of 8k. I then looked at the plan and found that the table with missing stats was being Broadcast to the HASH Join. It was a 16GB table and Broadcasting it had caused all those waits. I added the PQ_DISTRIBUTE HASH HASH hint on that table to rectify the issue.

But what puzzled me even more was what I had observed during the next run. The plan for the create table session was showing full scans and HASH joins for all tables involved. But after 1 hour of execution, when i monitored, all the parallel slaves in one set were showing a steady stream of continuous “db file sequential read” waits. When i checked the blocks, I found them to be of another table H which was not at all referred to in the sql. All the Mviews A, B, C, D, E, F referred in the sql were against remote tables across a DBlink and get refreshed periodically. I could not find the reason for the access to H (which is again a Mview against another remote table). There is no sort of dependency among any of these tables. There is also no fine-grained access control setup.
After stopping the periodic Mview refreshes and restarting the create table, i did not find the access to table H in the next run.
I want to resolve this mystery and blog about it.

December 9, 2010

BITMAP index and Locking issue

Filed under: Uncategorized — srivenu @ 11:00 am

While analyzing a performance issue, i observed several insert and some update sessions waiting on TX enqueue. Further digging showed that the table involved had a BITMAP index.

It was an OLTP system and the table in question was a sort of QUEUE table having large concurrency. The DBA considered a BITMAP index to be more suitable in this case as the column (some sort of STATUS) had low cardinality. Which turned out to be a really bad decision in this case!

There are several notes and articles on the web unwrapping the myths and facts of BITMAP indexes.

The following article by Jonathan Lewis – Understanding Bitmap indexes is a must read.

So are these from Richard Foote – Bitmap Index Degradation Since 10g (Fix You), Bitmap Index Degradation After DML Prior To 10g (Beauty and the Beast)

Determining appropriate Sequence Cache value

Filed under: Uncategorized — srivenu @ 10:21 am

While monitoring one of the oracle instances, i observed a lot of “row cache lock” events. Several consurrent insert sessions were waiting on this event and p1 pointed out to the sequence cache. The insert statement showed which sequence it was and setting the proper cache value for that sequence (it was set to 0) resolved the issue.

The question that many of us face is the appropriate value to set for the sequence cache.

I normally use one of the following procedures to set the cache values for the sequences (presuming that i dont need ORDER and don’t mind loosing some of the cached values).

Procedure 1 – I assume that all sequences are used from the day of creation.

You can use sql like this to find the sequence usage per minute. You can increase the CACHE value for those sequences where USEDPERMIN exceeds present CACHE value.

	CEIL(CEIL(ds.noused/do.noofdays)/1440) usedpermin, 
	from  	dba_sequences
	where  	SEQUENCE_OWNER not in 
	) ds,
	(select	OWNER, OBJECT_NAME, sysdate-CREATED noofdays
	from	dba_objects
	and	OBJECT_NAME not in 
	) do

You can modify the value 1440 to 720, if you want to CACHE 2 mins worth of sequence data.
Beware of RAC instances where the sequence might be used from several instances which might need a reduced CACHE per instance.

Procedure 2 – In this i try to be more scientific.

During peak hours i create a table

create table seq_start as select * from dba_sequences;

After some time, say 1 hour i create another table (you could put the whole thing in a script)

create table seq_end as select * from dba_sequences;

Then if i decide to cache atleast 2 mins worth of sequence data, i use this to generate the DDL.

select  'alter sequence '||ss.SEQUENCE_OWNER||'.'||
	ss.SEQUENCE_NAME||' cache '||
 	ROUND( CEIL((se.LAST_NUMBER - ss.LAST_NUMBER) / 30), -2)||
	' ;'
from  	seq_start  ss,
 	seq_end  se,
	dba_sequences ds
and	ds.CACHE_SIZE < ROUND( CEIL((se.LAST_NUMBER - ss.LAST_NUMBER) / 30), -2)
and 	se.SEQUENCE_OWNER not in
order 	by (se.LAST_NUMBER - ss.LAST_NUMBER) / 30

Just keep a note of these things

– RAC instances, which might need a reduction in the above cache’s as the sequence might be used by sessions from all or some of the RAC sessions.

– Sequences that might not be used during the time we monitored (in which case we might increase the time between the creation of seq_start & seq_end).

– Sequence usage might not be consistently equal over the time interval

Blog at