Monday, May 12, 2008

Count Total Number of Users Connected to ORACLE Applications

Print this post


Want to determine how many users are connected to Oracle apps 11i,V$session does not give the true picture as mostly there are more than 1 oracle session for the same forms connection depending on how many forms the user has opened up.


1:- Can use this SQL statement to count concurrent_users in Oracle apps:

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.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

2:- In the Oracle Applications Manager, go to the site map, and select the "Applications Usage" option in the Activity region. There you will be able to report on many usage questions, such as:

Products Installed
Application Users Per Module Summary
Applications Usage Reports
Purchase Line Items Processed - Internet Supplier Portal
Purchase Line Items Processed - Purchasing Intelligence
Order Entry Lines Processed - Ordering Application
Purchase Line Items Processed - iProcurement
Expense Reports Processed - Internet Expense
Invoice Line Items Processed - Accounts Receivables

Note the Application Users per module summary is based on the number of users that have Active responsibilities for the Application module. I am not aware of there being any check on last usage date for the responsibility.

3:- Also if you are using responsibilities connected to a custom-application, which is standard Consulting practice in some countries, those users will be counted as users of the custom-application and not users of the actual Application-module for the screens they are accessing.

4:- Run the following queries:-
    This will give the number of users on the system in the past 1 hour.
     select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1/24 and user_id != '-1';

    This will give the number of users on the system in the past 1 day.
    select count(distinct user_id) "users" from icx_sessions where  last_connect > sysdate - 1 and user_id != '-1';

    This will show the activity in the last 15 minutes.
    select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where  last_connect > sysdate - 1/96;

5:-  Check the Note:233871.1 which will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.

Surely this will affect the performance but how it affects is based on the number of users logged in and accessing the forms.

Factoring in System Overhead

In planning your organizations Sign On Audit implementation, you should consider the additional system overhead required to precisely User and Data Auditing monitor and audit your users as they access Oracle Applications. The more users you audit and the higher the level of auditing, the greater the likelihood of incurring additional system overhead.


No comments: