Re: multiprocessing in perl

I have been using perl with Oracle (via DBI) for many years, and your initial problem statement (timeout) sounds wrong. Please post more information about what error messages you get from example perl code. I have never had timeout issues where perl was the problem, and thus the solution. I have had many "timeout" failures where the root cause of the problem was webserver (apache) or Oracle database related.

For perl CGI script called via the webserver, I have had the webserver timeout because the CGI script was not completing within a short enough time period. I detected this by looking at the webserver error log. I had to configure apache to wait longer.

For long-running Oracle SQL operations, I have had "timout-like" issues where the Oracle session had resource limits setup such that my session would be terminated. In these instances, the Oracle DBI statement would return an error message to perl which I would print. Then, I would work on adjusting my Oracle resources to allow my long queries to work (or, I would break my queries into shorter bits, perhaps using intermediate tables or PL/SQL routines).

Whenever a perl script runs DBI to connect to an Oracle instance, a "session" is created on the Oracle server. In all my years of working with Oracle, I have only been able to have a single Oracle session run a single SQL statement at one time...never multiple SQL statements simultaneously. (Oracle can do "parallel" query execution, but that is still a single SQL statement, just broken into bits and executed in parallel). I think that if you tried to use perl threading or forking, you would be forced to create two separate DBI database handles, which would result in two separate Oracle sessions. The sessions would be completely independent, so that you may be executing "keep_alive" SQL on one session, while the other session keeps running until it terminates.

There may be some part of perl+DBI+Oracle which I don't know about, but based on what I DO know, the keep_connection_alive() approach you are suggesting is incorrect.

Feel free to e-mail me more details on the SQL issues you are running into and we can try to work on that bit. Also please specify more details about the environment you are running and look for any error logs and messages which would shed more light on the exact nature of your problem.

Comment on Re: multiprocessing in perl

Replies are listed 'Best First'.
Re^2: multiprocessing in perl by shijumic (Novice) on Apr 15, 2009 at 14:35 UTC
Hi fzellinger, Is there any way I can ping the oracle server at regular intervals.Will that resolve this timeout issue. This scripts are running in windows boxes, the loading jobs what exactly does is save the files in a work server and report those details to oracle database using SQL loader method. The problem is that loading job takes much time and the oracle connection has to wait long time to get this done and report it to the database. That is why i thought of another method that pings the oracle server or run a simple query at regular intervals to keep the connection alive while the loading job also runs. I will provide you the exact timeout error message once i get that from the other team. Thanks.	[reply]
Re^3: multiprocessing in perl by fzellinger (Acolyte) on Apr 15, 2009 at 18:02 UTC
Shijumic, thanks for explaining your problem further. If I understand correctly, your perl program is periodically posting small bits of information to the Oracle server, but doing a lot of other tasks on the local computer in between posts to Oracle. So, your Oracle session is not performing some long running task, but it instead is sitting idle for long time periods. from your initial post, I had assumed that your perl script had submitted some large, time-consuming SQL statement to Oracle, and that Oracle failed to complete it and disconnected your session. Assuming that your Oracle connection/session is sitting idle for long time periods, then yes, you could try to keep the session alive by periodically performing small SQL tasks just to do something. However, this may not work anyway. Oracle user accounts can have various resource limits set which may have a FIXED time window set...so that even if you perform keep_alive() type ping queries, your session may still be terminated after a fixed connect time. If you can connect to Oracle (using SQLPLus) you should be able to query your USER_RESOURCE_LIMITS. Here is what I see for my account on Oracle 10.2: SELECT * FROM USER_RESOURCE_LIMITS; RESOURCE_NAME LIMIT -------------------------------- ---------------------------------------- COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME 480 CONNECT_TIME UNLIMITED PRIVATE_SGA UNLIMITED Most of my settings are UNLIMITED, but the IDLE_TIME is set to 480 (minutes?). If you have limits on CPU usage, or CONNECT_TIME, you may be kicked out even if you are not idle. You could perform some keep_alive() queries if your session is being terminated because of your IDLE_TIME setting, but I would recommend something else: voluntarily disconnect your Oracle session, and re-connect when you need to use it again. Here is why: When you connect to Oracle, the Oracle server creates an Oracle session for you. This Oracle session consumes various server resources like memory and processes. If your session is idle 99% of the time, then you are consuming session resources which cannot be used by other users. If many users create sessions which sit idle, then the Oracle administrator may have to get more resources (hardware) to support the demand. If you are spending creating Oracle sessions that last more than 2 minutes, and are spending more than 50% of your time idle, then I would recommend disconnecting and reconnecting as needed. These are of course just a randomly guessed rule of thumb...you can decide what guidelines to follow for your own situation.	[reply]