SAP processes and Oracle 10g SESSIONS Parameter

[Former Member]

Hello,

we have a problem with our SAP ECC 6.0 and the Oracle 10.2.0.4 database.

We thought that ever SAP process open EXACTLY one session to database.... that's wrong (btw: since when ?).

SAP say every process can open 2 or more session to database.

But what EXACTLY is 2 or more ? We need to resize our SESSIONS parameter in the INIT.ORA File.

We need a formula to calculate the max. SESSIONS. SAP say SAP processes * 2 but there is a problem.

We have about 50 SAP processes .... multiplied with 2 are 100 Sessions.

Our TNS Listener got at a specified time (e.g. 11:47:36) about 125 connection requests .... so 100 sessions are still too few!

Hope someone can help?!

Thanks in advance

Alex

[Former Member · Accepted solution]

There is always a 1:1 relationship between SAP work processes and Oracle shadow processes. But - as you recognized - there can be multiple sessions handled by a process pair. The amount of secondary sessions depends on the application and can't be avoided. A typical reason for a secondary session is an activity that requires an immediate COMMIT and can't wait for the COMMIT of the primary session. Think about a batch job that needs to generate a report at first (because it was not compiled before). If the compilation is not immediately committed, it will prevent other transactions from using the same report until the batch job is finally committed. This is not acceptable and so the compilation happens in a secondary session of the same work process and is committed immediately.

In BW you may sometimes see several secondary sessions while in OLTP environments there is most of the time only one session.

The recommendation in SAP Note 830576 with the factor 2 between sessions and processes is sufficient to cover secondary sections (you have to take into account that PROCESSES itself is also defined with a factor 2 compared to the SAP work processes, which would strictly speaking not be necessary). If you speak about PROCESSES = 50 and SESSIONS = 100 above, this indicates that you haven't followed the SAP recommendation in SAP Note 830576:

PROCESSES =

#ABAP-Workprozesse * 2 +

#J2EE-Serverprozesse *

<max-connections> +

PARALLEL_MAX_SERVERS + 40

The resulting value must be significantly higher than 50.

[stefan_koehler · Accepted solution]

Hello Martin,

>Think about a batch job that needs to generate a report at first (because it was not compiled before). If the compilation is not immediately committed, it will prevent other transactions from using the same report until the batch job is finally committed

Was this just an example or is it really handled this way?

If a batch job needs to generate a report, then the generation is performed in a DIA process or in the same BTC process (i am not quite sure about that anymore).

Why is a second session needed for that corresponding SAP / oracle shadow process?

If the generation is done, the ABAP code is also able to force a commit in the "first" session and then continue its original request. Why is a second session needed for generation?

Thanks and Regards

Stefan

[Former Member · Accepted solution]

Hello Stefan,

that was just an example - how it happens in reality depends on many factors (what is compiled? Which SAP release? Which parameter settings?). Sometimes a different process is used (e.g. if dynpro/generate_in_separate_luw is activated), but sometimes you want to use a second transaction in the same work process for a quickly committed change. Another situation for a secondary connection is BW writing statistical data to some tables.

Regards

Martin

[Former Member]

Hello,

since when sap work processes do so?

Since which release there open more than one session ?

[Former Member]

Hello Martin,

thanks for this interesting answer !

At the and of your post you wrote the formula for the PROCESSES Parameter, but we don't have problems with the PROCESSES... everthing is okay with them !

We have problems with the SESSIONS ! ORA-00018 Not enough SESSIONS.

We DON'T WANT to but in a SESSIONS parameter in the INIT.ORA as much as we can !

We need to calculate the SESSIONS exactly !!!

As i read in Note 830576 the SESSIONS parameter should be:

SESSIONS 	 2 * PROCESSES

Does this PROCESSES means SAP processes or Oracle processes ?

[stefan_koehler]

Hello Alexander,

> Does this PROCESSES means SAP processes or Oracle processes ?

processes means the oracle parameter processes, which has to be calculated this way (also explained in sapnote #830576):

PROCESSES 	 #ABAP-Workprozesse * 2 +
	#J2EE-Serverprozesse *
	<max-connections> +
	PARALLEL_MAX_SERVERS + 40

Regards

Stefan

[Former Member]

Alexander,

Does this PROCESSES means SAP processes or Oracle processes ?

Processes is Always Oracle Processes. Also, you mentioned you need to calculate exact number of sessions.

I would think, you can always calculate max number of sessions your database needs, but it might exceed also sometimes based on usage level of the application/database.

For example, you have a batch job, which was created such a way that, when it gets kicked off in the application/database, might spawn multiple child processes which needs more sessions during the run time.

Also, are you restricting the application user sessions? How many sessions a single user can create/login parallel?

Default value is 6 sessions per user.

Do you see any sessions not closing/releasing from database, even if user logs out from application?

Thanks

SV

[Former Member]

Don't solve the problem ... sorry !

Is there any way to avoid that one sap process open multiple database sessions ??

That doesn't fit to our corporate policies !!

[Former Member]

Thanks for answer but i know this note.... I need more Information !

[anindya_bose]

Hi

Check the link below if it helps..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671284058977

Each workprocess need a dedicated process in Oracle ( Shadow process) plus there are lots of background processes.

Regards

Anindya

[former_member524429]

Hi,

SAP Work Processes and Oracle sessions are having one to many relationship based on the concurrent request counts and system workload.

-> For every work process, the Listener must create a shadow process and coordinate the connection setup between the work process and the shadow process.

-> If other connection requests reach the Listener while it is still busy setting up the connections for the previous requests, the requests are put into a queue.

=> To increase the size of TCP/IP Queue which is used by TNS Listener , please refer SAP Note 131561 - Work processes sometimes abend during database connect to get more info.

As per this SAP Note 384839 - ora-12540, tns-12560, tns-12500, tns-12546, set the following parameters with the formula mentioned below

processes = (2*number of R/3 work processes+20 )
              session = (1.1*processes) + 5
              transactions = 1.1*sessions
              enqueue_resources = ((sessions - 10)*2) + 55
              dml_locks = 4*transactions
It is basically no problem if the values are higher; this is only to keep space free in the main memory for resources that are never used.

PGA is also a one of the dependent factor , if you increase the value of SESSIONS. Ensure that your are having enough SWAP space as well as RAM space (buffer pool).

Please refer this SAP Note 789011 - FAQ: Oracle memory areas for the determination of PGA specific to your System requirement.

As of Oracle 10g, the parameter SGA_TARGET is also available.If necessary, Oracle configures and then adjusts the individual SGA components within SGA_TARGET.

Regards,

Bhavik G. Shroff

[anindya_bose]

Hi Alex

you can refer to OSS note Note 830576 - Parameter recommendations for Oracle 10g

Regards

Anindya