OCIPlogon &amp; Oracle Processes

OCIPlogon & Oracle Processes

Anon

hi,

I'm using php 4.04, apache 1.3.14 and oracle 8i under windows NT.....

I started to use OciPlogon to make the connections to the oracle database (I have a connection on each page for php 4 session handling).

I saw on Oracle that each time I connect (OCIPlogon), I have a new Inactive process generated ?

does anyone have the same problem ? ....

it works fine with Ocilogon.....

thanks

Anon

The last I checked, Oracle on WindowsNT didn't support Oracle's MTS (Multi-Threaded Server) technology. If that is still the case (or if your Oracle DBA has decided not to use MTS), then Oracle will create a separate dedicated server-side shadow process to execute the SQL statements PHP is sending it. If you were using MTS on a platform that supported it (examples: Unix, OpenVMS, etc.), then parameters in your init.ora, tnsnames.ora and listener.ora files would control whether incoming connections got a dedicated server process or shared an pre-existing MTS server process. All of this is independent of your use of OCIPLogon() or OCILogon().

If you are running PHP as an Apache module, then the "P" in OCIPLogon() will get you a "persistent connection" to either the dedicated or shared server process. What this means is that the PHP/Apache process will keep the database connection open to the dedicated/shared server process for a while after the PHP page is done with it. When subsequent pages try to connect to the database using the same username/password@service combination, the existing connection will be reused. This improves performance of each subsequent page by reducing the amount of code executed to connect to the database.

If you are running PHP as a CGI, then the OCIPLogon() defaults back to the normal OCILogon() style of operation. Namely, a fresh connection is established for each OCILogon() and that connection is closed either implicitly at the end of the PHP page, or when an OCILogOff() is encountered. While the connection exists, you should see either a dedicated database server-side process or a active/busy MTS shared server process.

Dedicated server processes should be automatically cleaned up by Oracle when they are no longer in use (ie: after PHP/Apache finally closes the connection). If they aren't being cleaned up properly, then your Net8 configuration needs to be corrected. Your Oracle DBA can fix this problem by changing a parameter or two in your Oracle configuration. MTS shared server processes may continue to exist after a connection is closed depending on a number of Net8 configuration parameters.

-- Michael
Darkstreak Consulting
http://www.darkstreak.com

gofrm

i have exactly the same problem!
I get an inactive connection added in the sessions-list, but it seem that these sessions are working fine, as long as the following PLogin is sent within the next 15 seconds.

Has this anything to to with the timeout-settings in the Net8-config?

although i get data from this connection it is set inactive!
Why isn't the session set active?

Anon

An Oracle session is only listed as "ACTIVE" when it is processing a SQL statement. At all other times it will show as "INACTIVE".

Your message is unclear whether you are running into problems with server processes being left around after they should have terminated. This can happen if a client terminates their connection abnormally. A symptom of this problem would be a lot of idle dedicated server processes during times of low usage. If you are experiencing this problem (common under *nix), then discuss the following excerpt with your Oracle DBA.

From Chapter 8 of Oracle's Net8 Administrator's Guide - Release 8.1.6: (Similar exists all the way back to at least early V7.x releases)

Configuring Advanced Profile Information

The sqlnet.ora file can be configured with the following advanced parameters:

sqlnet.ora File Parameter Net8 Assistant Field/Option Description
SQLNET.EXPIRE_TIME TNS Time Out Value
This option can be used only on the server.

Sets a dead connection time-out value. Net8 sends a probe periodically to
verify that a client-server connection is still active. This ensures that
connections are not left open indefinitely, due to an abnormal client
termination. If the probe finds a dead connection, or a connection that is no
longer in use, it returns an error, causing the server process to exit.

Limitations on using the dead connection detection feature are:

Dead connection detection is not allowed on bequeathed connections.

Though very small, a probe packet generates additional traffic that may
downgrade network performance.

The server may need to perform additional processing to distinguish the
connection probing event from other events that occur, depending on which
operating system is in use. This may also result in downgrading network
performance.

Please let me know if this helps solve your problem.

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

gofrm

thanks for answering, but i have still questions.
i try to described my problem in an other way:

PLogon takes about 6 sec to connect to oracle and creates a new session as shown in Oracle-DBA-Studio.
Any further request (a simple insertion) will be processed in < 1 sec, only as long as this new db-access
takes places within the next 15 seconds. Otherwise, again, a new session will be created.
That means: If I make a request more then 15 seconds after the last request, OCIPLogin would not use
the existing connection, but creates a new one. The old session remains listed in the session-list in
DBA-Studio. I have to kill such sessions manually.

If it wouldn't take about 6 sec every time a make a connect i would use the normal
OCILogon/OCILogOff-Functions with every request, but 6 seconds is too long...

Questions:
Is there a possibility to increase the logon speed? if not -
How can i fix the problem i described above, how can i increase a sessions lifetime?

Anon

You wrote:

Is there a possibility to increase the logon speed? if not -
How can i fix the problem i described above, how can i increase a sessions lifetime?

1) Yes, depending on what server-side OS you are using. One way to reduce the logon time is to have the server processes be already available when a client tries to connect to the database. Two Oracle options exist for this: "Multi-Threaded Server" (MTS) and "Prespawned dedicated server processes". Use MTS if your server OS supports it. MTS is designed to handle the situation where clients frequently connect/disconnect or where they just want to run some quick(=short runtime) SQL statements. MTS precreates server processes and then shares them across multiple concurrent clients. MTS can handle, but is not really designed for, clients which execute long running SQL statements (example: where each SQL statement takes many minutes/hours to run). Luckily long running SQL is rarely found in websites since fast response is highly valued by web clients.

"Prespawned dedicated server processes" does exactly what its name implies. It starts a number of dedicated (not shared like MTS) server-side processes. Since the processes already exist when the client tries to connect in, you don't run into a delay waiting for the server OS to start a new server process.

Also make sure that you have plenty of physical RAM (not just swap space) and that the server is tuned to use it. Sizing the Oracle SGA incorrectly can cause the OS to page it to death yielding in poor response time. MTS can also reduce the amount of RAM you need since the server processes are shared by multiple concurrent clients.

I assume that you have already verified that it is the actual database connect that it taking the 6+ seconds rather than just something slowing down Net8/SQL*Net getting to the database in order to log on. For example, slow DNS can make the total connection process take a long time and you can spend hours/weeks trying to tune the logon process only to find that it isn't the database's fault. I know this from personal experience.

2) It sounds like PHP's PLogon is the one creating new sessions when it doesn't need to. Could you post some of your code so it can be used for testing and debugging?

The 'expire time' from my previous message will cause Oracle to drop dead connections. Just make sure that you don't set it too low, or your non-PHP database clients may be disconnected too soon as well.

Performance tuning Oracle is some where between an art form and a science. When you add in OS and network tuning to the mix, it can get really hairy really fast even for a skilled DBA.

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

Anon

I try to work with oracle 8.1.6, php 4.0.6 and apache 1.3.20 on windows 2000
but I can't. Could you tell me how.

Thanks