Health tip #3 – Avoid having too many concurrent managers

Here we go again! After having written and blogged about this topic a fair bit in the past, having too many managers is a problem I still continue to see all too often. A good example is a site I worked with recently; it had 20+ managers just to run the workflow background processes.

Putting aside the need to address the excessive workflow background processes (which is another problem in its own right) there are major issues with the concurrent managers:

Problem #1 – Allowing 20 workflow background processes to run at once competes for the same resources on the same tables.

Problem #2 – Is one of basic queuing theory –i.e. 20 processes all competing for the same CPU and data resources.

In theory, 1 CPU handles 2 concurrent processes so in this case unless the site has 10 CPUs it runs the risk of CPU bottlenecking problems. The sting in the tail is these problems become most evident when you least need them to, like at high processing times such as month end.

What to do? It’s a simple choice. In this case identify why you think you need so many concurrent managers. You need to fix the cause and cut down on the number of managers, not just focus on the symptoms.

Want to know more? Take a look at the paper Concurrent Manager 101 & 202 on the www.piper-rx.com website.  In fact there is heaps of FREE information and tips on all aspects of OEBS Applications Administration at PIPER-Rx.com so why not check it out!

Concurrent Managers Health Tip #2 – Scheduled Request Issues.

Scheduled requests are one of the mainstays of OEBS concurrent processing.

One of the benefits of recurring scheduled requests is they are “set and forget” they just happily run and resubmit without you having to do a thing.

The biggest issue I’ve found, and this applies to almost all sites, is the “forget” component. I regularly find scheduled requests that have been running for years that are no longer required, and also duplicate scheduled requests which have come about because someone doesn’t know the original request exists. Both these issues are a waste of processing resources.

Another issue with scheduled requests is that I often find resource intensive requests originally set to run overnight now creeping into the working day because the request is resubmitting from the END of the prior run.

You should periodically review your scheduled requests to help avoid these issues.

Want to know more?

Download the simple OEBS scheduled requests report ordered by program - TOAD Reports format report.

http://www.piper-rx.com/pages/reports_free.html#_concurrent_requests

Report ID - PRXCMS-002

Using this report you should be able to identify both your duplicates and those requests with a resubmit of END. By multiplying the “Re-submit” value by the “Resub Count” you can get some idea how long the request has been resubmitting.

Refer to PAM tutorial 19 “When Scheduled Requests Go Wrong”

http://www.piper-rx.com/pages/papers.html

Concurrent Managers Health Tip #1 – Rebuild your indexes!

This is really a simple one and you would be surprised how many companies large and small overlook this.

I have seen examples of sites with over 50 million rows in the fnd_concurrent_requests_u1 indexes; a huge waste of space! More importantly however is the problem of poor performance in such a core OEBS activity e.g. 10 concurrent manager processes running with a 30 second sleep time equates to 28,800 hits on your fnd_concurrent_requests table and its indexes (this does not include your users submitting requests and checking if their requests have completed). Get the picture why this is so important.

As part of regular maintenance practices you should rebuild your indexes on routinely purged objects on a regular basis, especially the indexes on fnd_concurrent_requests.

Want to know if you need to rebuild your indexes? The following article includes everything you need to know together with code and there is also a TOAD report. These can be found on the PIPER-Rx “tips” page: http://www.piper-rx.com/pages/tips.html

Rebuilding indexes in an OEBS environment - A guide to rebuilding indexes in an OEBS environment

View and Download associated TOAD report (TRD format) - Report id - FDBA002-10

Also remember some OEBS application monitors provide alerting to remind you when your indexes may need maintenance.

Happy Rebuilding J

Did you know you can change an apps user’s account name?

First and foremost this is done via the normal apps Security > User > Define screen.

Whilst the account name (user_name) has to be unique which is enforced by the fnd_user_u2 index, the internal application key is based on the account ID (user_id) not the name

So why would you change an account name?

• A name change by deed poll or marriage
• A new employee has the same name as an ex-employee
• The user name was entered incorrectly
• You want to implement a user naming standard

Scenario: Once a user is no longer with the company, end date the account and change the account name adding an Z_ to the beginning of the user name e.g. The account GPIPER becomes Z_GPIPER. Now the account name GPIPER can be used by a new employee. This has the added benefit of allowing you to filter reports excluding ex-employees, or getting them to sort last in your reports.

Of course you will need to get this approach signed off by Oracle support and internal audit first. Usually, as long as there is consistent documented evidence of the change audit are generally ok

You are not doing anything dodgy here, this is all done via the standard Oracle application screens (Security > User > Define) just select the user, change the users name and commit.

Note: If you change the name of an active account, you will need to reset that user's password and let them know the password you have set and that they will be required to change their password on first connecting with the new name.

Of course you wouldn’t even attempt to do this without checking with Oracle support and you’re your audit team before changing application user names and then testing, testing etc…..

Rebuilding indexes in an OEBS environment

Index maintenance is all too often overlooked and can, over time, have a serious impact on performance. Ever growing indexes account for large amounts of wasted space and failing to perform regular index rebuilds on dynamic application tables is just throwing away performance.

When APPLSYS has over a thousand indexes the OEBS application has over 60,000 indexes (version and module dependant) it often gets too hard to identify what indexes require rebuilding and they simply don’t get rebuilt. Believe it or not, most sites I visit have not rebuilt indexes on even the basic regularly purged objects such as concurrent requests and sign-on audit tables in years.

Example:

A site I recently reviewed generates approximately 35,900 fnd_concurrent _requests records per day which are being purged nightly holding 7 days history on-line. The indexes had not been rebuilt for over 4 years. Thus in this instance the base table should be holding approximately 251,000 records whereas the primary key index fnd_concurrent_requests_u1 would be holding approximately 52 million rows. 99.9% deleted rows. This is an interesting one when you calculate the space the index is holding, I estimate over 1.4 Gb for fnd_concurrent_requests_u1 alone.

In this paper we will provide a simple list of indexes to start with, and a method of identifying if any of these indexes should be rebuilt.

We also cover why you should exercise caution when using an auto index rebuild script you find on the web, some do not readily translate to an OEBS environment

The full paper can be found at http://www.piper-rx.com/pages/tips.html - Look for "Rebuilding indexes in an OEBS environment" in the tips section

Aged User Accounts

Much like the Accounts Receivable ages debtor accounts function in any business, we can age application user accounts that have never connected to the application or have not connected in some time via the full and self-service screens.

Why would we want to do this? Having people who have long left the business retaining active accounts is a security issue, and all the best managed sites have processes in place to check that such accounts are being closed on a timely basis.

It is possible to generate a list of application user accounts that have either never connected or have not connected in the last 120 days.

The first step is to identify the number of accounts you have that are candidates for review:

SELECT count(*)
  FROM applsys.fnd_user fu
WHERE (fu.last_logon_date is null
               or (sysdate - fu.last_logon_date) >= 120 )
      and (fu.end_date is null
              or fu.end_date > sysdate )
     and fu.user_name not in ('SYSADMIN','GUEST');

The second step is to produce a list of application accounts that are candidates for review:

SELECT fu.user_id,
             fu.user_name,
             substr(fu.description, 1, 40)
             decode(sign(length(fu.description) - 40), 1, '...') description,
             nvl(to_char(fu.last_logon_date, 'DD-Mon-YY HH24:MI'), 'Never Connected')
                       last_connect_date,
             decode(fu.last_logon_date, null, null,
             round( (sysdate - fu.last_logon_date),0) ) days_since_last_connect
  FROM applsys.fnd_user fu
WHERE (fu.last_logon_date is null
               or (sysdate - fu.last_logon_date) >= 120 )
       and (fu.end_date is null
              or fu.end_date > sysdate )
       and fu.user_name not in ('SYSADMIN','GUEST')
ORDER by decode(fu.last_logon_date, null, (sysdate - 10000), fu.last_logon_date),
                fu.user_name;

The output will look something like the following example:

Remember: Not all Aged User Accounts are candidates for end dating:

There are of course exceptions, those accounts that must exist but are never used and those that are used very infrequently. You can exclude these by simply adding these accounts to your SQL statement:

             and fu.user_name not in (‘SYSADMIN’,’GUEST’)

You should run the second report say once per month as part of your normal application maintenance routine and send it to your HR department or whoever manages your application user accounts. It shows the business you are on top of managing the application and the business loves anything to do with security...

Case Review - How did I manage to get 30 million rows in my fnd_logins table?

The site in presented with over 30 million rows in the fnd_logins table, growing at a rate of approximately 25,000 records per day.

Based on the site’s application activity, the estimated number of rows that should be held in the fnd_logins table should be around 800,000 records (i.e. holding 32 days history on-line). It is expected that this number should reduce post concurrent manager activity review.

The site was running the concurrent program Purge Signon Audit data (FNDSCPRG) daily as part of their normal maintenance program to purge the sign-on audit data and was unaware of the high growth rate in the sign-on audit tables.

On review of the site’s scheduled requests it was found that the Purge Signon Audit data was being run daily with the date argument set to 10-Oct-06, however, “the increment date parameter each run” check box had not been checked when the scheduled request was created. As such the data parameter has not been incrementing with each run, thus with each run the program has been purging all sign-on audit records prior to 10-Oct-06. As a result any record added after that date has not been being purged.

So, since 10-Oct-06 (Over 1,200 days at the time of presentation) the program has been running but purging nothing.

Whilst there are several inherent performance issues, the biggest impact to the application would be when the concurrent program runs. The concurrent program Purge Signon Audit data (FNDSCPRG) uses an un-indexed column start_time in its execution. As such the purge program will execute a full scan on each of the target tables in order to determine the rows to delete. So for this site, that would include a daily full scan of a 30 million to not find any rows to delete.

The case review shows how I would “Fix” the issue including deleting the unwanted rows and setting up a new Purge Signon Audit data scheduled program run.

The full case review can be found at -
http://www.piper-rx.com/pages/case_reviews/case_sign_on_audit.pdf

What OEBS Application modules are installed?

The following genreates a list of the OEBS Application modules you have installed including their version and patch levels...

SELECT fa.application_id appn_id,
       decode(fa.application_short_name,
       'SQLAP', 'AP', 'SQLGL', 'GL', fa.application_short_name ) appn_short_name,
       substr(fat.application_name,1,50)
              decode(sign(length(fat.application_name) - 50), 1, '...') Application,
       fa.basepath Base_path,
       fl.meaning install_status,
       nvl(fpi.product_version, 'Not Available') product_version,
       nvl(fpi.patch_level, 'Not Available') patch_level,
       to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI') last_update_date,
       nvl(fu.user_name, '* Install *') Updated_by
  FROM applsys.fnd_application fa,
       applsys.fnd_application_tl fat,
       applsys.fnd_user fu,
       applsys.fnd_product_installations fpi,
       apps.fnd_lookups fl
 WHERE fa.application_id = fat.application_id
   and fat.language(+) = userenv('LANG')
   and fa.application_id = fpi.application_id
   and fpi.last_updated_by = fu.user_id(+)
   and fpi.status = fl.lookup_code
   and fl.lookup_type = 'FND_PRODUCT_STATUS'
UNION ALL
SELECT fa.application_id,
       fa.application_short_name,
       substr(fat.application_name,1,50)
       decode(sign(length(fat.application_name) - 50), 1, '...'),
       fa.basepath,
      'Not Available',
      'Not Available',
      'Not Available',
      to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI'),
      nvl(fu.user_name, '* Install *')
 FROM applsys.fnd_application fa,
      applsys.fnd_application_tl fat,
      applsys.fnd_user fu
WHERE fa.application_id = fat.application_id
  and fat.language(+) = userenv('LANG')
  and fa.last_updated_by = fu.user_id(+)
  and fa.application_id not in
      ( SELECT fpi.application_id
         FROM applsys.fnd_product_installations fpi )
ORDER by 2;

Example output:


Note: For readability I have converted the SQLAP to AP and SQLGL to GL – this looks so much better for users...

Hope this is useful....