Inactive users - Time out
Date: Fri, 25 Jun 1999 11:02:33 -0500
From: Dan Lococo Dan.Lococo@marquette.edu
Subject: NCA: Time-out Inactive Users
Good morning,
We are trying to improve performance of our v11.0.2 NCA applications. One
of the approaches we would like to use is to automatically log-off users
who have been inactive for more than 15 minutes.
Q: What parameter(s) do we have to change to automatically time-out
inactive users?
Q: Has this approach been useful at other NCA sites?
Thank you in advance for your help and advice.
Dan,
Dan Lococo
Project Leader
Information Technology Services
Marquette University
(414) 288-7855
Date: Sat, 26 Jun 1999 23:35:37 +0100
From: "Jonathan Stuart" jdstuart@globalnet.co.uk
Subject: Re: Time-out Inactive Users
Dan,
I would question the benefits of timing out inactive users. From my
observations, inactive users account for virtually no CPU on the forms and
database servers and potentially very little memory as well. Memory
consumption will depend on what the users have been doing and what form(s)
is/are open while they are inactive, however this is unlikely to be
impacting your performance unless your servers are undersized in terms of
memory, in which case there is only one option for you. Rather than looking
at the inactive users I would recommend you look at the active sessions and
determine where the performance problems are.
Are the Applications generally slow? In this case you need to be looking at
overall database, server, network and PC tuning - E.g. how utilised are your
CPUs and how much disk I/O is there. To take this one stage further, if you
find you are CPU bound examine what is using this CPU - it is concurrent
processes or forms sessions. If the former, can these jobs be run overnight
instead of during the online day and/or could the jobs benefit from tuning.
If forms sessions are killing your CPU is it the sheer quantity of active
sessions you have or are there a few highly CPU intensive sessions
occurring? If the former is true you probably need to be looking at
upgrading or adding to your servers, while if the latter is true you might
be able to educate your users on how to run more efficient forms queries.
If it is only particular forms and/or concurrent jobs which are slow you
need to look at tuning these individually. There are many areas of the
Applications which underperform in their vanilla state. Some of these issues
are resolved with patches from Oracle which will replace inefficient views,
etc., with more tuned versions, while at other times you will have to do the
tuning yourself with the judicious addition of indexes or purging of
interface tables, etc. Use SQL trace to determine where these sorts of
problems are.
This is an almost open ended subject but I hope this has given you a few
pointers for where to look at to get your Apps running better.
Jonathan.
Number of users logged in
Date: Mon, 28 Jun 1999 13:14:55 -0400
From: louis_avrami@es.adp.com (Louis Avrami)
To: oraapps-dba@cpa.qc.ca
Subject: Number of users logged in
Hello all,
Management has asked me to provide some statistics on the number of
users who log into our Apps database each day.
Are there any canned reports or processes that do this? If so,
please let me know about them.
I've been looking at the FND_LOGINS and FND_USERS tables. It would
appear the FND_LOGINS or something similar would be used ... looking
at our production FND_LOGINS table, I noticed that a good number, a
majority of the entries in the table, have a null END_TIME entry.
Shouldn't this get updated when end-users log out of the application?
Thanks,
Lou Avrami ( louis_avrami@es.adp.com )
Date: Mon, 28 Jun 1999 14:03:03 -0400
From: "Jacques, Pascale (SSC)" pjacques@ssc.knightridder.com
To: "'oraapps-dba@cpa.qc.ca'" oraapps-dba@cpa.qc.ca
Subject: RE: Number of users logged in
The null end_time reflect users that did not log out when the RDBMS went
down or users who had to reboot/abend. I don't know of any canned reports
but I used the fnd_logins, icx_sesions(web users) in conjuction with the
fnd_users.
Date: Tue, 29 Jun 1999 08:50:50 +0100
From: Philip West Philip.West@exco.co.uk
To: oraapps-dba@cpa.qc.ca
Subject: RE: Number of users logged in
Older versions of apps did have a feature of forgetting to end date users
whose sessions were abnormally terminated. You could if you wish (you may
want to chat to Oracle first) tidy up the audit tables with this little
script/s:
-- XXX FND_AUDIT_FIX.sql XXX --
-- script to tidy up 'ghosts' in fnd_logins
update fnd_logins l
set end_time = to_date('01-JAN-1998', 'DD-MON-YYYY')
where end_time is null
and not exists
(select null
from v$process p
where p.pid = l.pid
and p.username = l.login_name)
/
update fnd_login_responsibilities lr
set end_time = to_date('01-JAN-1998', 'DD-MON-YYYY')
where end_time is null
and not exists
(select null
from v$process p, fnd_logins l
where p.pid = l.pid
and p.username = l.login_name
and l.login_id = lr.login_id)
/
update fnd_login_resp_forms lrf
set end_time = to_date('01-JAN-1998', 'DD-MON-YYYY')
where end_time is null
and not exists
(select null
from v$process p, fnd_logins l
where p.pid = l.pid
and p.username = l.login_name
and l.login_id = lrf.login_id)
/
Also remember that user logins may not be the best measure of database usage
- taking into account zooms etc.
I run stats against my apps databases every 5 minutes as follows:
Running concurrent jobs
fnd user_logins
v$sessions
ps -ef|grep aiap
I also do a timed query against a fixed size table
I feel that this gives me some idea of database performance and performance
trends
Would like to hear how other people measure database usage / performance
Come on now, don't be shy !
Best Regards
Phil West - Oracle Financials DBA
Telephone: 44 171 9509385
E-mail: philip.west@exco.co.uk
Date: Tue, 29 Jun 1999 09:38:29 -0600
From: "Rosy Virani" Rosy.Virani@CRHA-Health.Ab.Ca
To: oraapps-dba@cpa.qc.ca
Subject: Re Number of users logged in
In ver 11, there is report called 'Signon Audit Users'. This report gives time
user logged in and logged out.
I hope this helps.
Rosy
Date: Tue, 29 Jun 1999 11:57:23 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
To: "'oraapps-dba@cpa.qc.ca'" oraapps-dba@cpa.qc.ca
Subject: RE: Re Number of users logged in
Rosy, I saw in the sys admin monitor user screen, some of the user(two) have
1150:30 time does it mean that, that user still log in the system?. We did
complete shutdown(apps server as well as db server) on last weekend.
I ran the signon audit user report and I found that this user have two
session with no end date. I took that system process id and did ps -ef|grep
command on my unix boxes but it didn't' find this process id.
Am I missing something?
thanks.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Tue, 29 Jun 1999 11:16:02 -0600
From: "Rosy Virani" Rosy.Virani@CRHA-Health.Ab.Ca
To: oraapps-dba@cpa.qc.ca
Subject: Re re Number of user logged in
Since these users didnt log out properly, you need to run 'Purge Signon
Audit Data' to remove these users from fnd_login tables, so they want
appear in admin monitor form.
I have scheduled this process to run after midnight to purge audit users
data.
I hope this helps.
Rosy.
Date: Tue, 29 Jun 1999 10:42:51 -0800
From: janet_schieber@power-one.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: Re re Number of user logged in
Rosy,
When I run the request 'Purge Signon Audit data', it asks for an
'Audit Date'. What do I put in this field? I would like to schedule
this purge everynight after the backup.
Thanks.....Janet Schieber
Date: Wed, 30 Jun 1999 08:40:41 -0600
From: "Rosy Virani" Rosy.Virani@CRHA-Health.Ab.Ca
To: oraapps-dba@cpa.qc.ca
Subject: Re: ORAAPPS-DBA digest 228
Janet,
I put todays date and anything before todays date will be deleted from the tables.
These are mine findings. You may want to test in your test environment.
Rosy.
Date: Wed, 21 Jul 1999 11:08:34 -0400
From: "Shreter, Hilary" ShreterH@orau.gov
Subject: Monitoring (was RE: re Number of users logged in)
1. "Monitor Users" (16.1SC): I periodically $FND_TOP/sql/fndscats.sql
modified to put end-date 1/1/80 to clean up the ghosts on the hosts. Per
support's suggestion.
2. Philip West asked how folks monitor their Apps databases.
2a. cron job to run vmstat throughout the day, and email results overnight.
I feed the output to Excel with some VBA to clean and chart the CPU usage on
M/Tu/Wed. Results go to management.
2b. SQL and shell to feed it to svrmgrl so that you can sign on, type one
command and see who's on apps and Discoverer with OS PID. Handy when we're
trying to shut down, or see who's showing high CPU usage.
2c. Enabled all DBA alerts (I think I had to fix one, and modify others for
size) with sendmail. I respond daily. We use special size/extents scripts to
check space during big events (e.g. ye close).
2d. Via cron, I mail the alert log to myself, and grep the log for
[A-Z][A-Z][A-Z]- , also emailed. Email myself basic performance tuning
stats every day for periodic review.
2e. For others: grep the export logs for expenditure volume that's charted
and available on the intraweb.
3. Philip didn't ask how I would like to monitor the database
3a. I'd like to modify 2b to count users and ship to me for charting.
3b. I've got export logs going way back and would like to trend volume on
key tables.
3c. I'd like to modify 2a to at least print an average. Also would like to
chart memory, once I understand it.
3d. I think Philip has some good ideas in his note and I may adopt some !
User Control
Date: Tue, 7 Sep 1999 21:11:09 -0700 (PDT)
From: FMT firas70@yahoo.comv
To: oraapps-l@cpa.qc.ca
Subject: SysAdmin : User Control
Hi,
Is there functionality for not allowing all apps users
from logging to the application, Even the db is open ?
Firas
Date: Wed, 8 Sep 1999 13:40:12 -0400
From: "Innamuri, Chakrapani" cinnamuri@btg.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SysAdmin : User Control
Which application you are using......
In 11.0.3 we shut down the Web Listner....
-chakrapani
Date: Wed, 8 Sep 1999 13:22:15 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SysAdmin : User Control
I agree with Chakrapani, If you shutdown the web listener then user can't
get into the system and get applsys username kind of messages.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Wed, 8 Sep 1999 13:28:32 -0800
From: Amos Gingerich agingerich@gci.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: SysAdmin : User Control
I don't think shutting down the web listener will prevent users who already
have a connection from working. This would only work on startup.
Amos
DBA
General Communications Inc.