Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: EXECUTE CONTEXT USE in multithreaded Oracle applications translates to NOTHING

Re: EXECUTE CONTEXT USE in multithreaded Oracle applications translates to NOTHING

From: Thomas Kyte <>
Date: 6 Jul 2002 06:19:14 -0700
Message-ID: <>

In article <>, says...
>I'm having multiple threads selecting&converting records from Oracle
>9i database on a Solaris 8 box. The performance with 2 and 3 threads
>is roughly the same as with a single thread. The threads seem to
>compete for the same Oracle database connection.
>I'm using PROC 9.0.1 with the THREADS=YES option. I'm executing EXEC
>SQL ENABLE THREADS at the ver beginning and I'm allocating different
>runtime contexts and creating a separate database connection for each
>thread. I've noticed however that the statement
>EXECUTE CONTEXT USE :context_name
>is translatad to NOTHING. Really, I cannot see any place in the
>generated .cpp code where a runtime context could be selected. This
>makes me think I'm using the default runtime context with all the
>threads competing for the same database connection. Right or wrong?
>Anyone know how to make PROC generate code to effectively select a
>runtime context?

the exec sql context use statement tells the precompiler what runtime context to pass to the sqlctx procedure call. If you have a statement like:

    EXEC SQL CONTEXT USE :connectCtx;

Just search forward from that point for connectCtx, you'll find it in generated calls like this:

   sqlcxt(&connectCtx, &sqlctx, &sqlstm, &sqlfpn);

We pass the context around -- the EXEC SQL statement itself is just a directive to the compiler that says "from now on, pass this context when you need to pass a context". It in itself generates no code.

Your code is multi-threaded, but perhaps the underlying processes you are doing serialize at the database layer due to resource contention. Try to isolate what is really going one (eg: call "dummy" procedures that do nothing, or just do a dbms_lock.sleep(1) and see if your multi-threaded code "scales" up -- can run 3x as many in the same time or not. If so, the problem isn't in the C code)

Thomas Kyte (    
Expert one on one Oracle, programming techniques and solutions for Oracle.  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Sat Jul 06 2002 - 08:19:14 CDT

Original text of this message