DBD::Oracle9 and non-blocking mode

[Jim Cain]

I'm about ready to make available an early copy of my library, but first
I have some questions regarding non-blocking mode. I've never actually
used this feature, so I'd like to hear from those that do how they use
it. Do you need more support for it beyond simply setting/resetting the
mode? What is the most convenient way for you to be notified that an OCI
call is still executing? When a call is still running, would you like to
call Statement#execute again to see if it's finished, or would you like
some other method?

Thanks for the input.

Cheers,
Jim

[Brian Candler]

On Wed, Jul 02, 2003 at 01:04:00AM +0900, Jim Cain wrote:

> I'm about ready to make available an early copy of my library, but first
> I have some questions regarding non-blocking mode. I've never actually
> used this feature, so I'd like to hear from those that do how they use
> it. Do you need more support for it beyond simply setting/resetting the
> mode? What is the most convenient way for you to be notified that an OCI
> call is still executing? When a call is still running, would you like to
> call Statement#execute again to see if it's finished, or would you like
> some other method?

I have used it briefly: setting non-blocking mode on Takehiro's ruby-oci8
library, I was able to get several threads to run concurrent slow Oracle
queries. It didn't need any special support for notification; I just let
each thread block until its particular query had finished. (ruby-oci8 deals
with this by polling the OCI library at increasing intervals until OCI says
the query has completed, and then fetches the results)

However, I wasn't particularly happy with this approach, so in the end I
changed my application to run as multiple processes (in fact running under
FastCGI, so Apache controls the number of processes which are spawned), each
of which is a single thread so I don't care if OCI blocks.

Regards,

Brian.

[KUBO Takehiro]

Brian Candler <B.Candler@pobox.com> writes:

> On Wed, Jul 02, 2003 at 01:04:00AM +0900, Jim Cain wrote:

>> I'm about ready to make available an early copy of my library, but first
>> I have some questions regarding non-blocking mode. I've never actually
>> used this feature, so I'd like to hear from those that do how they use
>> it. Do you need more support for it beyond simply setting/resetting the
>> mode? What is the most convenient way for you to be notified that an OCI
>> call is still executing? When a call is still running, would you like to
>> call Statement#execute again to see if it's finished, or would you like
>> some other method?

>
> I have used it briefly: setting non-blocking mode on Takehiro's ruby-oci8
> library, I was able to get several threads to run concurrent slow Oracle
> queries. It didn't need any special support for notification; I just let
> each thread block until its particular query had finished. (ruby-oci8 deals
> with this by polling the OCI library at increasing intervals until OCI says
> the query has completed, and then fetches the results)

I implemented it in the ruby layer.
In the next release I do it in the C API layer.
See the macro oci_rc2 in the following URL.
[url]http://www.jiubao.org/ruby-oci8/ruby-oci8-v0.2-unusable-2003-0626/oci8.h[/url]
Its license is same with ruby. So you can freely use its code fragment
in your library or application.

> However, I wasn't particularly happy with this approach, so in the end I
> changed my application to run as multiple processes (in fact running under
> FastCGI, so Apache controls the number of processes which are spawned), each
> of which is a single thread so I don't care if OCI blocks.

Hmm. What approach you prefer?

--
KUBO Takehiro

[Brian Candler]

On Wed, Jul 02, 2003 at 01:03:12PM +0900, KUBO Takehiro wrote:

> > However, I wasn't particularly happy with this approach, so in the end I
> > changed my application to run as multiple processes (in fact running under
> > FastCGI, so Apache controls the number of processes which are spawned), each
> > of which is a single thread so I don't care if OCI blocks.

>
> Hmm. What approach you prefer?

It just seemed to work faster when I had five separate processes doing
Oracle queries, than one process with five threads. Also, using
Apache/Fastcgi gave me a ready-made mechanism for creating a pool of
processes and automatically restarting them if one fails. Using threads I'd
have to write my own thread manager to do that.

Cheers,

Brian.

[KUBO Takehiro]

Brian Candler <B.Candler@pobox.com> writes:

> On Wed, Jul 02, 2003 at 01:03:12PM +0900, KUBO Takehiro wrote:

>> > However, I wasn't particularly happy with this approach, so in the end I
>> > changed my application to run as multiple processes (in fact running under
>> > FastCGI, so Apache controls the number of processes which are spawned), each
>> > of which is a single thread so I don't care if OCI blocks.

>>
>> Hmm. What approach you prefer?

>
> It just seemed to work faster when I had five separate processes doing
> Oracle queries, than one process with five threads.

My approach wastes time. Undoubtedly your prospect is correct in
theory. OCI lacks any notification method whether the OCI call is
finished or not. So I use polling as a last resort.

I have one idea:
1. create a native thread per one connection by using OCI thread APIs.
2. when executing a OCI call which may be blocked, request the
associated native thread to execute it, then stop its ruby thread
by calling rb_thread_stop().
3. after OCI call is finished, the associated native thread wakes up
the ruby thread by calling rb_thread_wakeup().
But I hadn't tested it. I don't know whether it works.

> Also, using
> Apache/Fastcgi gave me a ready-made mechanism for creating a pool of
> processes and automatically restarting them if one fails. Using threads I'd
> have to write my own thread manager to do that.

Thanks.

--
KUBO Takehiro

[Brian Candler]

On Thu, Jul 03, 2003 at 01:00:00PM +0900, KUBO Takehiro wrote:

> > Also, using
> > Apache/Fastcgi gave me a ready-made mechanism for creating a pool of
> > processes and automatically restarting them if one fails. Using threads I'd
> > have to write my own thread manager to do that.

>
> Thanks.

And I forgot to add: even if non-blocking mode works in Oracle, it doesn't
work in Mysql. So my code is more portable if I don't rely on this feature.

Regards,

Brian.