About this blog

As with my website, my blogs espouse the philosophy “not all performance problems are technical” and that in most stable environments user behaviour / usage patterns are typically the source of most correctable performance problems. So if you are into bits, bytes, locks, latches etc this is not the place for you, but if you are after ways to genuinely enhance performance and work with your systems and customers to achieve better, more predictable performance, read on.

Tuesday, September 21, 2010

A workflow Case Review on how to lower the number of Workflow Background Processes

Having encountered a number of sites running upward of over 5,000 workflow background processes per day, in some cases over 15,000 per day, it never ceases to amaze me how so many sites get themselves into so much trouble running far too many background processes. Not only does this thrash your application but it also has an impact on your concurrent request tables as each request adds a record in the fnd_concurrent_requests table for each run.


Just recently I have been corresponding with a site that contacted me regarding issues with their workflow application and this has prompted me to put together a case review to articulate how I would look to resolve the issue of too many workflow background processes for the particular scenario outlined.

The full case review can be found on my web site:
http://www.piper-rx.com/pages/case_reviews/case_wf_bgp.pdf

It’s pointless looking for poor performing code with this issue…
This is a good example of my statement “Not all performance issues are solved by code tuning”.


-Gary

Monday, September 13, 2010

Shortening large columns in user facing reports

Where a database column and its content is larger than the size you wish to show in a report, it is easy to just substring the column value to fit your report column size. However, this often does not look that good in user facing reports.

To demonstrate this concept below I have used the application users description column in applsys.fnd_user which is a varchar2(240)...

SELECT fu.user_id user_id,
               fu.user_name user_name,
               fu.description description
   FROM applsys.fnd_user fu;



In most cases when you write a report you substring the column to the size of the column in your report. Whilst sub stringing the column value is the easy solution and in your mind acceptable, you should remember your reports are aimed at end users wouldn’t it look more professional if you added three (3) full stops to a sub stringed value to clearly indicate the column has been truncated.

SELECT fu.user_id user_id,
                fu.user_name user_name,
                substr(fu.description, 1, 30)
                decode(sign(length(fu.description) - 30), 1, '...') description
FROM applsys.fnd_user fu;


If your report column size is 30, set the two (2) values in the example SQL to 27, that is 27 characters plus 3 for the full stops.

A very simple piece of code but what a difference. You should consider using this for any user report where a data column that has been truncated.

Remember:
“Always make life easier for your target user”


- Gary