Re^2: multiprocessing in perl

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.

Comment on Re^2: multiprocessing in perl

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

Re^3: multiprocessing in perl
by fzellinger (Acolyte) on Apr 15, 2009 at 18:02 UTC

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

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]