Ratios redux

I have got myself embroiled with Don Burleson in another OTN forum thread on cache hit ratios. In it he asks me

'Surely, you don;t(sic) mean that you would dismiss something because it's "not always relevant"?

If it applied on only 5% of the cases, it's still useful then, right?'

I think we can quibble for ever over the usefulness of a metric that is not applicable in 95% of cases. Of course we can use our skill and judgement to evaluate the importance of a low cache hit ratio is meaningful. But how much investigation is required to determine whether it matters? Is that the best use of our time? In most tuning situations I come across these days it doesn't seem relevant. YMMV (Don's obviously does).

Funnily enough one of the other threads I mentioned in my previous article demonstrated a situation where a low cache hit ratio was indeed an indicator of a bad situation. When the OP finally posted the relevant init.ora parameters we discovered

buffer cache size = 32 mb
shared pool = 76 mb

As Laurent Schneider observed, that's barely enough memory for a pocket calculator.

UKOUG Super SIG 13-MAR-2007: the lowdown

And so the sun has set on another UKOUG Combined SIG. This one was something of a landmark for me because it was the first one where the Development Engineering stream had the most registrations. So we turfed the Modelling, Analysis and Development SIG out of the biggest room and they got the one next to the kitchens. The large registration was partly down to my attempt to woo our core constituency with sessions on Oracle Forms and client/server applications, and partly due to the presence of Dr Timothy S Hall, Oracle ACE of the year.

The presentations

Grant Ronald kicked off proceedings with a session on building client/server applications with Java Swing and ADF. Basically Grant whipped through several basic Forms type features showing us how to build them using JDeveloper and Oracle's ADF Swing implementation. Clearly Oracle's decision a few years back to put some Forms-style 4GL into their Java offering is bearing fruit: each time I see a JDeveloper demo it looks better and better. I'm still glad I don't build front-end apps any more but I wish JDev had been even half as good when I had to use it back in the day. It's still not true to say that we can build Forms-style desktop apps without knowing any Java code. Nevertheless JDev looks like the best bet for leveraging old Forms developers' experience to the building Java apps.

Danny Roach from Oracle gave a presentation based on his graduate thesis project, a prototype for an internet-based cheque system. This was largely theoretical - academic research and assumptions formed the basis of the business model rather than actual input from major banking institutions. Nevertheless Danny provided some interesting insights. Firstly he showed how easy it could be to stitch together complex systems out of simple web service components. Secondly Danny explained how the banks and their customers currently handle electronic credit/debit payments. For instance, the banks guarantee any payment made with chip and pin, provided it goes through the full system. Supermarkets often don't use this system online, because it takes too long; instead they queue the transactions for asynchronous processing, taking the risk of fraud on themselves in exchange for faster throughput. Danny's prototype is a neat solution to the age old problem to the conundrum of paying a cheque into a bank account when we have to work but the banks are only open during regular work hours. The problem is getting enough of the banks signed up to make it attractive to potential users.

After all that modern .Net and SOAP stuff it was a relief to get down to some proper programming: PL/SQL. Last year I persuaded Rob Baillie to give his first presentation. This year I plucked Oracle-Base's Tim Hall from the ranks of the blogosphere as my victim. Tim claimed to be nervous about this beforehand, although he had seemed chuffed to do mini-talks at Open World last year (which is why I approached him). On the day Tim was fine. He knew his stuff, he was largely fluent and he was funny too. A natural presenter in fact. Of course, as people who have met Tim will know, he really just likes talking. I asked Tim to expound on Tuning PL/SQL because it is an area which doesn't receive as much exposure as it deserves. Tuning is a topic which usually gets directed at DBAs but it is just as important for developers to understand. Tim spent most of his time talking about the tools for diagnosing PL/SQL problems, both high level (baselines, Statspack and user complaints) and low level (home brewed instrumentation, DBMS_PROFILER, DBMS_DEBUG). Then he whizzed through a number of different optimisations which might fix our problems. Tim had the largest audience of the day (at least in the development stream).

After lunch Grant Ronald went though the Forms messages again:

1. Oracle still has a long term commitment to Forms.
2. Once an application is migrated to a web platform it can be integrated with other apps written in different technologies.
3. Now is a good time to start thinking about Forms modules as components in service-based architectures.

I'm afraid it is difficult for us DE SIG regulars not to feel a bit jaded by this. So Grant proposed that Peter Sechser from PITSS, an Oracle partner, give a quick overview of their product. PITSS.CON is a repository-based tool which uses the API built into Forms to automate the management and upgrade of the entire lifecycle of Forms applications. It offers, auditing, documentation, version control, impact analysis, reverse engineering of the data model, etc. Of particular interest is its potential to automate most if not all of any changes required by an upgrade.

Networking session

Then it was time for the three streams to join for the last session, which was a networking exercise. Most of the delegates took the opportunity to slope off early, and given the state of the M4 in the morning I don't altogether blame them. I ran this session with teh remaining few split into teams around tables. The objective was for each team to come up with a ten word sentence. The scenario: you get into the lift at Oracle's City office and you are surprised to see that the only other person in the lift is Larry Ellison. You can see from the button he's pressed that Larry is getting off at the next floor, so you only have time to say one thing to him.What do you say?

The resulting sentences are quite interesting but it is the process that's most revealing. Jeremy Duggan's table seemed to have the most fun, generating lots of ideas, although the suggestion they finnally agreed on was rather oblique. One group plumped for a (non-rhetorical) question, which rather missed the point that Larry was getting off at the next floor. The team with a representative from Oracle Consulting needed extra time because they couldn't come up with anything within the allotted ten minutes and the team with a representative from Oracle's product division overshot their ten word budget by 20%. It would be unfair to draw any conclusions from this.

Here is the final list of sentences:

• I'm really busy now ... call me in 6 months.
  
• Don't forget to guide the customers who put us where we are now.
  
• All this Fusion is confusing - will you buy my company?
  
• Please de-jargon Oracle - let's be simple.
  
• What do you REALLY think about open source?
  

These were written on giant post-its which were stuck to the walls and everybody voted for their favourite by standing next to it. Unfortunately I forgot to do my Mike Reid "Runaround!" impression. The winner was "All this Fusion is confusing - will you buy my company?" which suggests a hitherto unsuspected desire amongst UKOUG members to become Oracle employees. But all had done well and all deserved prizes. So it was fortunate that the UKOUG had laid on a free bar.

The need for cloning

Cracking though the DE line-up was I must confess that the presentation I would most have liked to hear was one I had to miss because it was in the MAD stream: Oracle's Rob Squire talking about temporal databases. Now I know Chris Date and Hugh Darwen have been doing some theoretical work into this sphere but I am interested to find out what the vendors think of its practicality. Rob's presentation was by all accounts very interesting. Mostly it consisted of demonstration through SQL scripts, some of which were hidden because they contained proprietary ideas. I guess this was a reprise of the session Rob gave at the Temporal Database seminar. Apparently Rob is going to be presenting to senior VPs at Redwood Shores soon. It would require changes to the kernel so it probably take a couple of generations for any implementation to come through. But when/if does happen I expect it will Oracle 13t.

Call for papers

Well that was yesterday and now it's time to start planning for the next one. It's in June, at the Oracle office in Blythe Valley Park (near Salford). So if any of you would like to experience what Tim calls fun then please contact Julius at the UKOUG office. Just ignore the fact that Tim's idea of fun includes sparring with a 3rd Dan Karateka....

Rationalising ratios

Over the last couple of weeks I have participated in some threads in the OTN DB General forum which converge on a pattern. A neophyte starts a thread asking for assistance in understanding buffer cache hit ratios. I join the thread with the suggestion that there are better, more useful ways of tuning the database. The irrepressible Don Burleson weighs in with a defence of ratios and the thread turns into a exercise in Hegelian dialectics as the inestimable Mark Powell provides some contextualisation.

Thesis (me): ratios are not particularly helpful for database tuning
Antithesis (DB): ratios must be useful because Oracle still include them in Statspack and AWR
Synthesis (MP): ratios cannot be used in isolation and at best can only support more detailed diagnostic practices.

It happens here and here, and it may yet happen here.

I usually point people at Connor McDonald's helpful script which allows us to "tune" the SGA by generating enough useless activity to set the buffer cache hit ratio to as high a value as we could wish for. But I have recently discovered that Mogens Nørgaard has published an article he wrote for the UKOUG Oracle Scene magazine in 2005. In the third section of this article he compares and contrasts several different approaches to tuning, including his own MOANS strategy. This requires us to tune a process simply by focusing on the SQL statement which takes the longest chunk of elapsed time.

I recently undertook a tuning exercise which used the MOANS approach. We have a background process which runs a couple of thousand times a day. When we first deployed it the process ran in about six seconds; now the average time was approaching seventy-five seconds. A query against V$SQLAREA quickly identified the most expensive SQL statement by any number of criteria: ELAPSED_TIME, CPU_TIME, DISK_READS. It was a select statement consisting of a three table join which matched a single row in an intersection table to single rows in table #1 and table #2.

An explain plan revealed that the query was executing full table scans against both the intersection table and table #2. This was bad news as the intersection table was the largest table in the schema, with over 4.5 million rows and growing all the time. Inadequate indexing looked to be the reason. I built a composite index on the intersection and the query improved quite a bit. But it was still doing a full table scan on table #2. As it happens, this set up still uses the Rule-Based Optimizer (for sound but irrelevant reasons). The order of the tables in the FROM clause seem to be causing the query to drive off table #2 when it ought to have been driving off the intersection table. A quick re-jigging corrected this...except that now the query was doing a full table scan of the intersection table again. Grrrr. This was fixed by re-ordering the predicates in the WHERE clause.

At this point we had a process which ran in less than seven seconds. This was good enough, so according to the MOANS best practice we stopped tuning. At some point we will need to tweak this process further and it has other statements in the process which look susceptible to MOANS-style tuning. The point is, this approach was focused on identifying the statement which took the longest time to ruin and fixing that. Ratios didn't feature.