Purge Inactive Sessions running from more than 24 Hours. 3266951 records in ICX_SESSIONS

Purge Inactive Sessions running from more than 24 Hours. 3266951 records in ICX_SESSIONS

EBS: 12.2.3
RAC Database: 11.2.0.4

Solution :

1. Backup tables
2. Stop the Apache server : This is required otherwise end users will receive session-expired/lost messages.

3. Execute the below delete statement to purge the ICX_SESSIONS table
delete FROM ICX_SESSIONS
WHERE (nvl(disabled_flag,'N') = 'Y')
OR (nvl(disabled_flag,'N') = 'N'
AND (last_connect + 1 + (fnd_profile.value_specific( 'ICX_LIMIT_TIME', user_id, responsibility_id, responsibility_application_id, org_id))/24)< sysdate);

4. Run the Purge concurrent program.
E-Business suite R12: $FND_TOP/sql/FNDDLTMP.sql

5. Restore any data
6. Restart Apache server


Details:

1. We had data from 2015 and quite enough data to be deleted which was more than Profile option ICX limit time set:

SELECT *
FROM ICX_SESSIONS
WHERE (nvl(disabled_flag,'N') = 'Y')
OR (nvl(disabled_flag,'N') = 'N'
AND (last_connect + 1 + (fnd_profile.value_specific( 'ICX_LIMIT_TIME', user_id, responsibility_id, responsibility_application_id, org_id))/24)< sysdate);


3M Records.

2. In the script FNDDLTMP.sql, it seems it just kept running in the first loop and never moved to the DELETE statements. When tried the delete statements in the scripts, manually, none of 
the rows got updated.



The Dead LOOP in script FNDDLTMP.sql:

CURSOR c_abandoned_sessions
IS
SELECT user_id, login_id, last_connect,limit_time,
responsibility_id,responsibility_application_id, org_id
FROM icx_sessions
WHERE (nvl(disabled_flag,'N') = 'N')
and (last_connect + 1 + (nvl(limit_time,4)/24)) <sysdate;

BEGIN
FOR session_rec in c_abandoned_sessions
LOOP
-- END DATE abandoned session using FND_LOGINS
-- Assume that a session is considered abandoned when it has
-- been without inactivity for 24 hours + ICX_LIMIT_TIME
-- if last_connect is null will update with sysdate.
FND_SIGNON.audit_user_end(session_rec.login_id, session_rec.last_connect +
nvl(session_rec.limit_time,4)/24);
END LOOP;
COMMIT;
END;
/


The deletes statements in script FNDDLTMP.sql:


delete icx_sessions
where (nvl(disabled_flag,'N') = 'Y')
or (nvl(disabled_flag,'N') = 'N' and
(last_connect + 1 + nvl(limit_time,4)/24 <sysdate));

delete icx_session_attributes
where session_id not in (select session_id from icx_sessions);

delete icx_transactions
where session_id not in (select session_id from icx_sessions);

delete icx_text
where session_id not in (select session_id from icx_sessions);

delete icx_context_results_temp
where datestamp < sysdate - 1/24;

-- deleting unsuccesful log information after 30 days.
delete icx_failures
where creation_date < SYSDATE - 30;

delete fnd_session_values
where ICX_SESSION_ID not in (select session_id from icx_sessions);


The actual purge script in FNDDLTMP.sql ( which was never invoked in our case):


begin
fnd_bc4j_cleanup_pkg.delete_transaction_rows(SYSDATE - 4/24);
fnd_bc4j_cleanup_pkg.delete_control_rows(SYSDATE - 4/24);
end;
/


3. VERY Interesting Find was that its a bug in Oracle code and below is the reason:


Based on the SQL ID which got executed by the Purge program (SQL ID 32czjgd3hu7zf), the issue is caused by a bug in the standard
code. This is the execution plan:
================================================================================
Inst: 3
SQL ID: 32czjgd3hu7zf
Child number: 0
Plan hash value: 2599260651

----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 130K(100)| |
| 1 | FOR UPDATE | | | | | |
|* 2 | TABLE ACCESS FULL| FND_LOGINS | 1 | 16 | 130K (9)| 00:26:06 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - (NUMBER): 1234

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter((TO_NUMBER("C_LOG"."SPID")=:B1 AND "END_TIME" IS NULL))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "C_LOG".ROWID[ROWID,10]
2 - "C_LOG".ROWID[ROWID,10], "END_TIME"[DATE,7],
"C_LOG"."SPID"[VARCHAR2,30]

The problem is a full table scan on FND_LOGINS (4.7G in size) which is caused by the fact Oracle pass a numeric bind
variable, but SPID is a VARCHAR2 column. There is an index on SPID (FND_LOGINS_N1), but it can’t be used because
the data type is wrong. The full table scan tables 570 seconds (and I can see it has been executed 254 times), but if I run
the following query (with ‘1234’ passed as a VARCHAR2) it uses the index and runs in under a second and returns
63650 rows:
select count(*) from applsys.FND_LOGINS where spid='1234';

So fix that SQL and at least this piece will run quickly.

Unfortunately this SQL is in the FND_SIGNON package, line 90:
procedure AUTH_LOGOUT_UPD(p_pid number,
p_pend_time date) is

TYPE Ty_rowid IS TABLE OF ROWID
INDEX BY BINARY_INTEGER;

L_ROWID Ty_rowid;

cursor get_upd_rowid is
select c_log.rowid
from fnd_logins c_log
where c_log.spid = p_pid
and end_time is null
FOR UPDATE SKIP LOCKED;

Begin
…

So the AUTH_LOGOUT_UPD procedure either needs to be changed to pass PID as the correct datatype, or
FND_LOGINS table should actually have the correct datatype.



SQL> desc applsys.fnd_logins
Name Null? Type
----------------------------------------- -------- ----------------------------
LOGIN_ID NOT NULL NUMBER
USER_ID NOT NULL NUMBER
START_TIME NOT NULL DATE
END_TIME DATE
PID NUMBER
SPID VARCHAR2(30)
============================

Checking this note:
What is the Relationship Between the ICX_SESSIONS Table and the FND_LOGINS Table? ( Doc ID 358823.1 ) 

It shows this is the relationship between v$process and v$session:
select count(distinct d.user_name)
from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);

So SPID actually refers to v$session.process column which is VARCHAR2.

Given that ‘PID’ on v$process is numeric, SPID is VARCHAR2, THIS is a code defect which Oracle has written.

HOWEVER in EBS 12.2.4  this looks rectified as seen below:

Code has been fixed to pass in p_pid as a varchar2: 
============================== 
$ strings -a AFSCSGN*.pls|grep '$Header' 
/* $Header: AFSCSGNB.pls 120.12.12020000.7 2015/08/19 19:56:18 jwsmith ship $ */ 
/* $Header: AFSCSGNS.pls 120.6.12020000.4 2015/06/18 09:58:15 absandhw ship $ */ 
============================== 
-- 
-- AUTH_LOGOUT_UPD (added for bug 18903648 ) 
-- 
-- 
procedure AUTH_LOGOUT_UPD(p_pid varchar2, 
p_pend_time date) is 

TYPE Ty_rowid IS TABLE OF ROWID 
INDEX BY BINARY_INTEGER; 

L_ROWID Ty_rowid; 
l_tmp_spid FND_LOGINS.SPID%TYPE; 

cursor get_upd_rowid is 
select c_log.rowid 
from fnd_logins c_log 
where c_log.spid = l_tmp_spid 
and end_time is null 
FOR UPDATE SKIP LOCKED; 
==============================