Oracle

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 c1.name 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
SQLEXEC "ALTER SESSION SET cursor_sharing='EXACT'";
............
............

Sample Replicat parameter file

REPLICAT rep_x
USERID gguser, PASSWORD temp
SQLEXEC "ALTER SESSION SET cursor_sharing='EXACT'";
............
............

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 10.2.0.4.0 to 10.2.0.4.3. And our team has pointed that querying COMP_ID, COMP_NAME & VERSION from DBA_REGISTRY was showing 10.2.0.4.0 for all components except OWM, Oracle Workspace Manager which was showing 10.2.0.4.3. 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 11.2.0.1.2 Patch Set Update.

--> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 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 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options



SQL> select * from v$version;

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

SQL> select * from product_component_version;


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


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


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

17 rows selected.


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


ACTION_TIME                    ACTION           VERSION         COMMENTS                
------------------------------ ---------------  --------------- --------------------    
09-SEP-10 04.07.34.774652 AM   VIEW RECOMPILE                   view recompilation
09-SEP-10 04.07.34.886572 AM   UPGRADE          11.2.0.1.0      Upgraded from 10.2.0.3.0
09-SEP-10 04.18.11.450891 AM   APPLY            11.2.0.1        PSU 11.2.0.1.2           


--> 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     11.2.0.1.1
9654983  9654983         DATABASE        PSU     11.2.0.1.2

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	.....
	.....
FROM	A, 
	B, 
	C, 
	D, 
	E, 
	tbl	
WHERE	b.CUST_ACCT_NBR = A.CUST_ACCT_NBR 
AND	A.CUST_ACCT_CTGY_ID = c.CUST_ACCT_CTGRY_ID(+) 
AND	A.MARKET_CODE = d.ACC_SUB_CAT(+) 
AND	b.SRVC_TYPE_ID = st.SERVICE_TYPE_ID 
AND	b.SRVC_TYPE_ID = tbl.SERVICE_TYPE 
AND	b.CIRCLE_ID = tbl.CIRCLE_ID
/

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.

select	ds.SEQUENCE_OWNER, ds.SEQUENCE_NAME, 
	CEIL(CEIL(ds.noused/do.noofdays)/1440) usedpermin, 
	ds.CACHE_SIZE
from	
	(select SEQUENCE_OWNER, SEQUENCE_NAME, 
		ROUND(LAST_NUMBER-MIN_VALUE/INCREMENT_BY) noused, 
		CACHE_SIZE
	from  	dba_sequences
	where  	SEQUENCE_OWNER not in 
		('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
	) ds,
	(select	OWNER, OBJECT_NAME, sysdate-CREATED noofdays
	from	dba_objects
	where  	OBJECT_TYPE = 'SEQUENCE'
	and	OBJECT_NAME not in 
		('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
	) do
where	ds.SEQUENCE_OWNER = do.OWNER
and	ds.SEQUENCE_NAME = do.OBJECT_NAME
order	by ds.SEQUENCE_OWNER, ds.SEQUENCE_NAME

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
where  	ss.SEQUENCE_OWNER = se.SEQUENCE_OWNER
and 	ss.SEQUENCE_NAME = se.SEQUENCE_NAME
and	ds.SEQUENCE_OWNER = ss.SEQUENCE_OWNER
and	ds.SEQUENCE_NAME = ss.SEQUENCE_NAME
and	ds.CACHE_SIZE < ROUND( CEIL((se.LAST_NUMBER - ss.LAST_NUMBER) / 30), -2)
and 	se.LAST_NUMBER > ss.LAST_NUMBER
and 	se.SEQUENCE_OWNER not in
	('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
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

Create a free website or blog at WordPress.com.