Using CURSOR in Oracle 11g database to locate all matches for specifed string in DB

where would I be setting the number of connections?

You can specify the min and max number of connections in the pool using

SQL>execute dbms_connection_pool.configure_pool(null, minsize=>10, maxsize=>100);

If the database servers are unix boxes, can we do any ODBC connection pooling?

I'm not quite sure why you're asking this. You can take advantage of DRCP via OCI in PHP, as long as you have the Oracle 11g client. Just specify your connection string like myhost.dom.com:1521/sales:POOLED and make your connections using oci_pconnect().

Here's a few resources that may be of help:

• Oracle DRCP Whitepaper
• DRCP entry on OraDBPedia
• PHP OCI manual

There's really only a few things that I can think of that might help here, without actually changing the cursor code:

1. Make sure that your tempdb resides on different physical drives from your database files. This won't make the temp/static rowset any smaller, but it will make it faster.

2. Adjust the CURSOR_THRESHOLD configuration setting: This is a tricky thing and not normally recommended, however, it can have a big impact on this very situation (just not always a positive impact). My guess would be that you'd want to adjust it from the default of -1 to something like 1000. But play around with it, and be careful, as it's server instance-wide and can affect other things as well.

3. Plan Guides: I think that you need 2008+ for this, and I am not sure if it works on cursor SELECT's. They are also complicated to use, but if they do work, they can really do amazing things to a repeatable query that is using sub-optimal query plans.

That's all that I can think of ...