glerma
|
| Posted: 07/16/2003, 4:31 PM |
|
O.K. I am going to start out here in the forum first. Just to dip this problem into the community for some (hopefully) feedback.
Using:
PHP 4.3.1
CCS 2.1
Oracle 8.1.7.0.1
Linux (Redhat 7.1)
Problem:
Oracle sessions being created are not being released properly.
I cannot figure out at this point why my Oracle Sessions table (v$sessions) keeps growing with every page that I go to in my CCS project. It seems that new sessions keep getting opened and they do not clean themselves up. What ends up happening is that I max out my MAX_SESSIONS parameter. This is a huge resource problem for me
I have scoured the db_oci8.php file that is used as the database abstract layer for connecting to Oracle DB's by CCS.
It seems to have some functions built into the class to fire OCIFreeStatement(), however I cannot see any evidence of how it is actually helping out.
Does anyone in the community know what I am talking about and do you have any suggestions, feedback?
I will open a ticket if I don't get any.
Regards,
glerma
|
|
|
 |
RonB
|
| Posted: 07/16/2003, 10:36 PM |
|
Hi,
I have the same problem. Have, so I haven't realy solved it yet. It seems to me Oracle isn't re-using sessions. The persistent connection should make sure that if the session still exists oracle re-uses that session but it doesn't. That's why every page creates a new session. You can prevent getting the max_sesssion error by changing the time a connection can stay idle before it is closed, shorten it. But that would only work on a system that doesn't get a lot of hits. You could also up the number of sessions allowed open but that still doesn't solve the problem, just delays the effects of it.
I'm staying away from persistent connections to oracle at the moment. They don't work anyway. I'm lined up for some Oracle courses this year and the next so maybe I'll get some more insight after that.
Ron
|
|
|
|
glerma
|
| Posted: 07/17/2003, 3:42 PM |
|
Yeah. Thanks for the feedback. I just wanted to pass on that I found a temporary solution, which was the easiest for now. I tried with non-persistant connections, and that allowed the sessions to drop once finished.
It's unkown to me the advantages and dis-advantages of this method, while using Oracle. I guess more testing is required. I am extremely interested in finding some valid measurements or bench-marking figures for both methods. This is extremely important with finding out the intrinsic value of using the included CCS connection classes or just building your own. Especially when you are building large sites with many hundreds or even thousands of users.
Hopes this helps someone else with this same problem.
|
|
|
|
RonB
|
| Posted: 07/19/2003, 8:29 AM |
|
The drawback of non persistent connections is the time it takes to establish a connection. Wich in oracle's case is a long time compared to MySQL. So with non persistent connections you have the overhead of establishing a connection for each page. I hope I'll figure out what is causing this trouble because I have to use oracle extensively for the intranet (management info, production etc.) At the moment I either get the to many sessions warning or I get the delay of connecting to oracle over and over. This is getting unpleasent to say the least. I've serached on the internet but could not find a solution there either.
Ron
|
|
|
|
glerma
|
| Posted: 07/20/2003, 4:48 PM |
|
Yeah. I agree. My solution is just temporary, until I finish my development and can then do some load testing. It works because the overhead to connect is low due to the fact that Oracle sits on the same server as my Web Server. I don't want to use non-persistant connections in a production environment.
This doesn't really give a solutin, but I found this on php dot net:
If anyone ever wonders why the number of idle db process (open connections) seems to grow even though you are using persistent connections, here's why:
"You are probably using a multi-process web server such as Apache. Since
database connections cannot be shared among different processes a new
one is created if the request happen to come to a different web server
child process."
|
|
|
|
RonB
|
| Posted: 07/21/2003, 6:55 AM |
|
Hmm, maybe something in the config file for Apache could help? Time to do some exploring on the apache ste 
Ron
|
|
|
|
Thom
|
| Posted: 07/21/2003, 1:36 PM |
|
Hi there
Patch the server, and patch the client. The current version on the client is 8.1.7.4.11, for the ODBC driver it is 8.01.78.00. I don't know about the server patches, but if you have the chance then patch the server as well (otherwise you will most likely not be able to dump the database).
If you are still experiencing problems: make sure you apply the patches in the appropriate order, one after another. If you run Oracle Reports, install them right after you install the base version of the client.
Always do full backups before patching!
|
|
|
|
RonB
|
| Posted: 07/23/2003, 1:06 AM |
|
My company is on oracle 9i and I still have these problems.
Ron
|
|
|
|
glerma
|
| Posted: 07/23/2003, 10:20 PM |
|
I still think it has to do with how Apache (multi-threading) is used to connect to my Oracle sessions. I don't think that patching is going to do much just yet.
The following link offers some insight:
http://www.php.net/manual/en/features.persistent-connections.php
|
|
|
|
RonB
|
| Posted: 07/24/2003, 4:25 AM |
|
After reading that article I'll probably stop using persistent connections altogether on Oracle. The overhead may increase but hey.... not being able to do any sql because of max connections is worse. On one of our oracle databases the liscence only allows for 10 concurent users. That means if I create 8 connections that are left open.... people will soon start to complain
Ron
|
|
|
|
Tom
|
| Posted: 07/26/2003, 2:35 PM |
|
I don't know much about php but in other languages, like asp.net, they make use of something called connection pooling for your persistent sessions.
Perhaps some searches on that subject and php would help
|
|
|
|
RonB
|
| Posted: 07/27/2003, 9:09 AM |
|
PHP is not the problem. If I understand glerma right the problem is that each child proces under apache is seen as new and there for Oracle decides to create a new connection. If I understand this right you would have the same problem with asp running under a multi threaded webserver.
|
|
|
|
