Overuse of views

This one always amazes me. How far people are willing to go to save them selves some tpng. I’m talking about the tendency of many developers to create some sort of a container for everything they do. Specifically I’m talking about the overuse of views. Don’t get me wrong, views have their place in the world but come on! I frequently show up on customer sites where there are performance issues. In most cases the performance boils down to bad sql and in many cases it is a simple statement like:

select col1, col2, col3 from view1 v where v.col5 = :bind1

Simple enough, huh? Except when you look closer view1 is based on 3 other views and each one of those is based on another 3 views and each one of those is based on 3 other views and each one – you get the picture. The worst case I’ve found so far is 9 levels, including user defined functions. What brings me to bitch about this? I just came from a customer who has a statement that has around 50% of all his CPU power, months of total elapsed time and returns only a few rows every time it is run. This of course is based on views. I rewrote that query and unnested it bringing each execution down from 9 – 10 seconds to 0.1 seconds, per execution. If you could 100 fold your systems performance/throughput whatever by simple things like this (which are not simple in most cases, unnesting can be dreadful)…There was no index created, no actual “tuning” except unnesting. The sad part is that Oracle perhaps should be able to do this, but it can’t except in simpler cases.

Unfortunately, you cannot in most cases. These are often third party applications written by professional developers who have no regard for the performance of your database. Get your act together. Write professional code. Do what you are supposed to be doing. Worst thing is that the standard response for performance complaints is that the DB is not performing. Interesting.

Second thing is overuse of PL/SQL functions. A simple example, same as above actually. Each and everyone of these views was using a functon called NVS which was basically a user defined function :

function nvs(p_str in varchar2) return varchar2 is
begin
  return nvl(p_str, ' ');
end nvs;

So, it basically replaces nulls with a space. Same as NVL(p_str,’ ‘). Saves you 4 keystrokes. I measured this, this actually performs 100% worse than using NVL. Double. Do not call these functions unless there are really good reasons to. Saving 4 keystrokes is not a good reason. I just measured the basic PL/SQL, no context switching, nothing. Do not call PL/SQL from within SQL if you don’t have to. Sometimes you do have to. Just think before acting and test your solutions using real data.

Single Sign On Capture

I’ve been working at a customer site lately that has a lot of interesting security requirements.  I’m obviously not getting into detail there but let’s suffice to say that it is probably the most complex such situation that I’ve come across.

One of the simpler requirements was to know who was using each and every Discoverer report that was being run.  Initially I thought this would be simple enough to do through the “The Discoverer V5 EUL” business area that comes with the installation and is intended for just such things.  When looking at it, I found that they do not store the Single Sign On (SSO) username, only the Oracle database username in question.  So if a user authenticates using SSO as John but is actually running the report as Benni, then only Benni gets stored and we have no idea who really ran the report/authenticated.

I spent considerable time looking for a solution on this but to no avail.  I found out some interesting things about Discoverer along the way, for example that a function can be run upon login into Discoverer or when a workbook is saved.  (I may blog on that later).

Anyway, there is a simple, non supported solution to this problem.  I’ve implemented it and it seems to be working fine.  So, to capture this information I made the following changes to the table EUL5_QPP_STATS in the EUL schema (Discoverer owner):

alter table eul5_qpp_stats add  (sso_user varchar2(32) default sys_context('USERENV','CLIENT_IDENTIFIER',32))

This will cause the SSO user to be inserted into this column whenever query statistics are written to the database. Please note that Discoverer does not write to this table until the user logs out so you may not see any records in there until the user has disconnected (it is possible that the writes are done i.e. every 5 minutes). If the Discoverer connection is a regular connection (not SSO) then this column will be NULL.

I had to change the Business Area “The Discoverer V5 EUL” as well adding the new column to the folder EUL5_QPP_STATISTICS. (Do a refresh folder and ignore all suggested changes, except the one with the new column SSO_USER) using the Discoverer Administration utility.

All that remains is to change the reports using USERNAME to use SSO_USER as well. The report used is called EUL_QUERY_STATISTICS.

If Discoverer is upgraded it is possible that these changes will be lost. In that case, the above procedure has to be repeated (unless Oracle starts doing this by default which would be nice).  In any case, a simple way to capture the SSO user.