Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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 <tkyte_at_oracle.com>
Date: 6 Jul 2002 06:19:14 -0700
Message-ID: <ag6qoi02g1r@drn.newsguy.com>


In article <84e67408.0207051408.14e2603b_at_posting.google.com>, tromanow_at_hotmail.com 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 (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US