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

Improving the business efficiency of OEBS workflow. Part 2: Validating e-mail addresses

This paper is the second of a two (2) part series. Part 2 covers e-mail address validation using a 12 point PLSQL e-mail addresses validation function.

Using the PLSQL function we cover e-mail validation of addresses in both the wf_local_roles and wf_local_users objects and in a number of base application objects that populate the workflow roles and users objects.

The paper and the PLSQL function can be viewed or downloaded at the following addresses

PDF document, 500 Kb
http://www.piper-rx.com/pages/papers/email_validation_part_two.pdf

PLSQL, 8 Kb
http://www.piper-rx.com/pages/papers/piper_rx_email_validation.sql

-Gary

Improving the business efficiency of OEBS workflow. Part 1: Missing e-mail addresses

This paper is part one (1) of a two (2) part series. Part 1 covers null e-mail addresses in your workflow roles objects and in part 2 we covers e-mail address validation including a free 12 point PLSQL function for validating e-mail addresses.

 In this paper we cover the issue of missing (null) workflow e-mail addresses:

     v  The importance of a SYSADMIN e-mail address

v  What happens when an invalid e-mail address is encountered?

v  Assessing the damage in your instance ( Free SQL )

v  And how to rectify the issue

The paper can be viewed or downloaded at the following address:
Part 1: Missing e-mail addresses
PDF document, 300 Kb

In part 2 of the “Improving the business efficiency of OEBS workflow” series we will cover email address validation. Part 2 also includes an 12 point email address validation function.

-Gary

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...

Is the number of concurrent manager processes you have causing performance issues?

All too often I see sites with far too many concurrent manager processes and the site wonders why they have intermittent performance issues. Remember, having more concurrent manager processes does not mean more throughput.

So, how many concurrent manager processes should you have?

Add up the number of standard and custom concurrent manager processes you have. If that value exceeds 2 times the number of CPUs (multi core adjusted) on the box you most likely have too many manager processes. If you are experiencing intermittent performance issues, particularly around high processing times like financial month ends, too many manager processes would most likely be one of your reasons.

The following SQL will list your concurrent managers:

SELECT concurrent_queue_name,
                max_processes,
                running_processes,
                nvl(sleep_seconds,0) sleep_seconds,
               cache_size,
               decode(enabled_flag,
               'Y', 'Enabled', 'N', 'Disabled', 'Unknown' ) status
   FROM applsys.fnd_concurrent_queues
ORDER by decode(enabled_flag, 'Y', 1, 'N', 0, enabled_flag ) DESC,
                    max_processes DESC,
                    decode(concurrent_queue_name,
                                  'FNDICM', 'AA',
                                  'FNDCRM', 'AB',
                                  'STANDARD', 'AC',
                                  concurrent_queue_name);
 

Example Output

A real world example of too many managers:

A site I reviewed had 54 standard manager processes on a 4 CPU box - 54 / 4 = 13.5

In this example there can be up to 54 concurrent requests running through the standard managers at any time which will just plain flood the CPUs. Generally running two (2) concurrent requests per CPU is sufficient to leave enough overhead for normal processing activities; any more than that and the risk increases of CPU flooding. And you would hope this site does run too many FSG’s... as we all know the damage they can do...

Believe it or not I have even seen 108 standard managers on an 8 CPU box..... Hmmm.....

Flooding the CPUs with concurrent requests leaves very little available CPU for normal user requests. As a result users tend to experience poor performance with their forms etc... It’s basic queuing theory... To make matters worse, these intermittent performance issues tend to occur around peak processing times when the concurrent manager load is at its peak; exactly the time users are clearly very busy and don’t want to be experiencing performance issues.

If you have a “problem” with an excessive number of standard and custom concurrent managers processes, what you need to do is lower the number of processes. This is easier said than done, as once they exist the business is very reluctant to let them go. But it is worth persisting as it will make a difference!

For more information refer to the paper I wrote:

http://www.piper-rx.com/pages/papers/cm101.pdf

Believe it or not, and much to my surprise, this is the most down loaded paper on my web site. Even though I wrote this paper in 2004, it still holds true today.... not much changes in OEBS, and for good reason; stability in accounting and business systems is what businesses want.