inactive session in EBS

[satish]

Dear Erman,

There are few sessions inactive for many days in my our prod ebs system.Though,We didnt face any performance issues,could you please let us know if this is any abnormal situation

SQL>select distinct program,module,last_call_et/3600 from gv$session where status='INACTIVE' order by 3 desc;

PROGRAM              MODULE                                             LAST_CALL_ET/3600
-------------------- -------------------------------------------------- -----------------
JDBC Thin Client     JDBC Thin Client                                          1327.85444
JDBC Thin Client     JDBC Thin Client                                          1327.85417
FNDSM@erpprodapp01.t [e-mail] (TNS V1-V3)                    1080.90278
td.com (TNS V1-V3)

JDBC Thin Client     JDBC Thin Client                                          1080.88194
JDBC Thin Client     JDBC Thin Client                                          1080.88167
JDBC Thin Client     JDBC Thin Client                                          1078.71944
JDBC Thin Client     JDBC Thin Client                                          1078.66278
JDBC Thin Client     JDBC Thin Client                                          1078.62444
JDBC Thin Client     JDBC Thin Client                                          1011.19278

PROGRAM              MODULE                                             LAST_CALL_ET/3600
-------------------- -------------------------------------------------- -----------------
JDBC Thin Client     JDBC Thin Client                                          1011.18667
JDBC Thin Client     JDBC Thin Client                                          1011.18639
JDBC Thin Client     JDBC Thin Client                                          1011.18611
JDBC Thin Client     JDBC Thin Client                                          901.584167
JDBC Thin Client     e:SYSADMIN:gsm:WFMailerOAF:BES Dispatch Thread            901.584167
JDBC Thin Client     JDBC Thin Client                                          692.375556
JDBC Thin Client     JDBC Thin Client                                          692.361667
JDBC Thin Client     JDBC Thin Client                                          340.620556
JDBC Thin Client     JDBC Thin Client                                          340.620278
JDBC Thin Client     JDBC Thin Client                                          334.261111
JDBC Thin Client     FNDCPOPP                                                  334.246667

PROGRAM              MODULE                                             LAST_CALL_ET/3600
-------------------- -------------------------------------------------- -----------------
JDBC Thin Client     JDBC Thin Client                                          334.072222
JDBC Thin Client     e::fwk:fnd.framework.service.lookups.server.Look          315.755833
JDBC Thin Client     e::bes:oracle.apps.icx.security.session.created           315.748056
JDBC Thin Client     e::fwk:fnd.framework.favorites.server.ICXFavorit          315.744722
JDBC Thin Client     JDBC Thin Client                                          310.263333
JDBC Thin Client     JDBC Thin Client                                          268.085278
JDBC Thin Client     JDBC Thin Client                                          220.595278
JDBC Thin Client     e::fwk:fnd.framework.navigate.server.OANavigateP          167.994167
JDBC Thin Client     JDBC Thin Client                                          96.5227778
JDBC Thin Client     JDBC Thin Client                                          96.5102778
JDBC Thin Client     JDBC Thin Client                                          96.4866667

PROGRAM              MODULE                                             LAST_CALL_ET/3600
-------------------- -------------------------------------------------- -----------------
JDBC Thin Client     JDBC Thin Client                                          96.4738889
JDBC Thin Client     e::fwk:fnd.framework.service.lookups.server.Look          95.7033333
frmweb@erpprodapp02. e:SQLAP:frm:FNDSCSGN                                      4.72916667
ttd.com (TNS V1-V3)

frmweb@erpprodapp02. e:SQLAP:frm:FNDSCSGN                                      3.94944444
ttd.com (TNS V1-V3)

frmweb@erpprodapp02. e:SQLAP:frm:APXINWKB                                      3.85583333
ttd.com (TNS V1-V3)

Also,we are not using SQLNET.EXPIRE_TIME = <MINUTES> in sqlnet.ora file and no profile for idle connections.Do we have to use these in EBS?

Thank you

[ErmanArslansOracleBlog]

No you don't have to use sqnet.expire_time in EBS.
EBS uses connection pool.
The connections which will be shared among the EBS processes will be managed by the connection pool of the EBS.
In EBS 12.2, we use weblogic as you know.
So, the connection pools of weblogic manage the connections of EBS according to the defined configuration.
On the other hand; forms screens will create their connections when you use them. So if you open a form, a db connection is created in the backend. Similarly, when you close the form, connection will be closed.
Connection managers will also have their own connections and these connections are handled according to the count of the connection manager processes. (they are always there as long as the connection managers are running)

[satish]

Thanks for the update erman.why there are many inactive sessions lasts for many days. Do I have to check my connection pool? In above output if you see, is this abnormal?

[satish]

FYI

There are few sessions inactive for many days in my our prod ebs system.Though,We didnt face any performance issues,could you please let us know if this is any abnormal situation

SQL>select distinct program,module,last_call_et/3600 from gv$session where status='INACTIVE' order by 3 desc;

PROGRAM              MODULE                                             LAST_CALL_ET/3600
-------------------- -------------------------------------------------- -----------------
JDBC Thin Client     JDBC Thin Client                                          1327.85444
JDBC Thin Client     JDBC Thin Client                                          1327.85417
FNDSM@erpprodapp01.t [e-mail] (TNS V1-V3)                    1080.90278
td.com (TNS V1-V3)

JDBC Thin Client     JDBC Thin Client                                          1080.88194
JDBC Thin Client     JDBC Thin Client                                          1080.88167
JDBC Thin Client     JDBC Thin Client                                          1078.71944
JDBC Thin Client     JDBC Thin Client                                          1078.66278
JDBC Thin Client     JDBC Thin Client                                          1078.62444
JDBC Thin Client     JDBC Thin Client                                          1011.19278

PROGRAM              MODULE                                             LAST_CALL_ET/3600
-------------------- -------------------------------------------------- -----------------
JDBC Thin Client     JDBC Thin Client                                          1011.18667
JDBC Thin Client     JDBC Thin Client                                          1011.18639
JDBC Thin Client     JDBC Thin Client                                          1011.18611
JDBC Thin Client     JDBC Thin Client                                          901.584167
JDBC Thin Client     e:SYSADMIN:gsm:WFMailerOAF:BES Dispatch Thread            901.584167
JDBC Thin Client     JDBC Thin Client                                          692.375556
JDBC Thin Client     JDBC Thin Client                                          692.361667
JDBC Thin Client     JDBC Thin Client                                          340.620556
JDBC Thin Client     JDBC Thin Client                                          340.620278
JDBC Thin Client     JDBC Thin Client                                          334.261111
JDBC Thin Client     FNDCPOPP                                                  334.246667

[ErmanArslansOracleBlog]

You will check the connection pool settings using weblogic console settings.
+
group by the inactive session count using PROGRAM and MODULE names. Get a count of these connections accordingly, then you can understand what kind of PROGRAM/MODULE gets lots of inactive connections..
After doing that, your will narrow down the scope , so that you can easily investigate further..

[satish]

Thanks for the update erman.

As suggested, i have got the details and attached here.

The SR which we have created is progressing very slow

Could you please help us in this regardinactive_sessions.txt

datasource.docx

Thank you

[ErmanArslansOracleBlog]

Don't bother the standard programs/concurrent managers to be listed in inactive. (FNDSM, FND:cp:STANDARD, workflow and etc..)
They are required to be there.. They will be activated while processing your requests.
JDBC connections seem to be coming from oacore, so as the connection pool is configured, there is no need to think about them.

I don't think you have an inactive connection problem in here.
Your connection pool's max capacity is 500, so it is ok.

You can check the inactive forms session like e:SQLAP:frm:FNDSCSGN..
You may consider killing them, if they are really inactive for a long period of time.

You can check OS load average time to time and check any spinning processes which are inceasing the load average on apps Tier. If there is a process spinning there, and if that process has an idle DB session, then you may need to drill down.