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
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 CorpReceived on Sat Jul 06 2002 - 08:19:14 CDT