Oracle takes 5 seconds to logon in PHP

Anon

This takes 5 seconds to appear, what is wrong? An ASP script I wrote connects instantly.

<?
$db = "newora";
$c2 = ocilogon("cst","cst",$db);
echo "done";
?>

tnsping shows newora to be OK and returns results instantly.

Anon

Tnsping only tests whether the Listener is up. Actually connecting into the database involves many more steps than just being able to talk to the Listener.

How long does it take to connect with SQL*Plus? I assume that your PHP page renders quickly if you comment out the OCILogon(). If not then it is probably a OS problem. If PHP renders the page quickly, then look into how you've performance tuned your database. Also check how you've setup your tnsnames.ora to make sure that it isn't taking a long/slow route to the database.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

Anon

It takes about the same 5 seconds for SQL Plus to connect, but once connected queries are instant. My problem is apparently that PHP needs to connect each time the page loads (or does it?)

If I comment ocilogon() it renders instantly.

what should I be looking for in the tnsnames.ora?

ALSO, MICHAEL, I WOULD APPRECIATE IT IF YOU DID NOT 'SNIFF' MY PORTS, OR AT LEAST INFORMED MY OF WHY YOU WERE DOING SO.

Anon

You wrote:

It takes about the same 5 seconds for SQL Plus to connect, but once connected queries
are instant. My problem is apparently that PHP needs to connect each time the page
loads (or does it?)

PHP doesn't hold sessions open across multiple accesses of a user's session. In other words, active database sessions are not be saved in the session environment as can be done with ASP. The data from the connection can be saved in the session, but the active connection object cannot.

OCILogon() establishes a fresh connection each time. OCIPLogon() will attempt to reuse 'persistent' connections. If you are running in CGI mode, then they are equivalent, but if you are running as a module, then OCIPLogon() may be able to reduce your connect times.

what should I be looking for in the tnsnames.ora?

Sometimes people are routing database connections through the network when they should be using an IPC connection instead. Or they refer to excess ports in their tnsnames.ora which the Listener isn't accepting. Both of those can impact connect times.

ALSO, MICHAEL, I WOULD APPRECIATE IT IF YOU DID NOT 'SNIFF' MY PORTS, OR AT LEAST
INFORMED MY OF WHY YOU WERE DOING SO.

No need to yell. I did not 'sniff' your network traffic, what I only did was probe three ports (1521, 1526, 80) on 141.155.35.116. When I am helping someone out, I make sure that their Oracle Listener IS NOT externally accessible to the Internet. The is done for security reasons, and is especially important if the person posts anything that could be interpreted as a weak password protection scheme (example: cst/cst@whatever). If I find anything, I immediately call or e-mail the person about how to improve the security of their system.

By the way... I note that you did sixty-six (66) port probes on one of my servers:
]2001-08-30 04:04:25, HTTP port probe, 141.155.35.116, pool-141-155-35-116.ny5030.east.verizon.net, 66
I guess that proves we are both more security conscious than most people.

Now back to the PHP/Oracle problem at hand.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

Anon

Geez, 66 probes? I thought I did about 2 or 3 and only through a browser to resolve your ISPs DNS entry to check if it matches your domain (it does.) Maybe you have 30 or so elements on the www.darkstreak.com front page and each hit is counted as a probe?

Thanks BTW, I appreciate your concern over the oracle security (and you educating me to the potential hole in Oracle.) This is my testing server, I have nothing (as of yet anyway) in the database of value.

I think loading PHP as a CGI module might be the issue as you said, although I can't beleive that running PHP as a CGI would make the DB basically unusable...

I changed the iis settings to point to the php4isapi.dll instead of php.exe and now all I get is "The specified module could not be found." What do I do?

Anon

Geez, 66 probes? I thought I did about 2 or 3 and only through a browser to
resolve your ISPs DNS entry to check if it matches your domain (it does.)

The 66 probes (defective TCP SYN frames probes) intermixed with the 137
successful web hits.

I think loading PHP as a CGI module might be the issue as you said, although
I can't beleive that running PHP as a CGI would make the DB basically
unusable...

Running as a CGI won't make the PHP/Oracle combination unusable. It just
makes it run slower since PHP can't pool database connections. The downside
is that there are still some bugs in the PHP-as-a-module approach.

You mentioned that you were seeing slow (~5secs) connect times even with
SQL*Plus. If we can reduce the overall connect time process, then running
in CGI mode might still be sufficient for your situation.

Things you may want to look for in tracking down slow connects: (in no
particular order - I still haven't had my first cup of coffee yet this
morning)
1) tnsnames.ora (client-side) and listener.ora (server-side) have correct
entries. If your webserver and PHP are on the same box as the database,
then use the IPC protocol instead of the TCP protocol. If not on the same
box, then make sure that your DNS is giving fast resolution times for the
database server's name on the client (PHP/IIS) machine.
2) Make sure that the Oracle SGA is sized properly. It should resize
completely in physical memory. Performance can drop significantly if the OS
starts paging the SGA to the swapfile. If low on free physical memory,
either reduce the SGA size, or add more memory.
3) Double check that the database's init.ora parameters are appropriate for
the load being placed on it. Being low on some parameters can cause
sluggishness. Unfortunately being too high can also degrade performance.
4) Check the amount of recursive SQL being generated. High numbers usually
indicate a tuning problem. See #3.
5) If auditting is turned on for the database, make sure that you are not
overkilling on what is being auditted. Also verify that there is enough
space in the SYSTEM tablespace for the SYS.AUD$ table.
6) Pre-spawn a number of server-side session processes. Reduces connect time
since the client doesn't have to wait for the process to start.
7) Use Oracle's Multi-Threaded Server (MTS) parameters if you OS supports them.
Similiar to #6, but can provide even better response times if running a lot
of quick-in-and-out SQL like is commonly found on PHP pages.

Time for that first cup of coffee. Later.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com