Oracle – Pinning table data in the Buffer Cache

As I write, Euro 2016 is in full swing.
England have managed to get out of the Group Stage this time, finishing second to the mighty…er…Wales.
Fortunately Deb hasn’t mentioned this…much.

In order to escape the Welsh nationalism that is currently rampant in our house, let’s try something completely unrelated – a tale of Gothic Horror set in an Oracle Database…

It was a dark stormy night. Well, it was dark and there was a persistent drizzle. It was Britain in summertime.
Sitting at his desk, listening to The Sisters of Mercy ( required to compensate for the lack of a thunderstorm and to maintain the Gothic quotient) Frank N Stein was struck by a sudden inspiration.
“I know”, he thought, “I’ll write some code to cache my Reference Data Tables in a PL/SQL array. I’ll declare the array as a package header variable so that the data is available for the entire session. That should cut down on the amount of Physical IO my application needs to do !”

Quite a lot of code later, Frank’s creation lurched off toward Production.
The outcome wasn’t quite what Frank had anticipated. The code that he had created was quite complex and hard to maintain. It was also not particularly quick.
In short, Frank’s caching framework was a bit of a monster.

In case you’re wondering, no, this is not in any way autobiographical. I am not Frank (although I will confess to owning a Sisters of Mercy album).
I am, in fact, one of the unfortunates who had to support this application several years later.

OK, it’s almost certain that none of the developers who spawned this particular delight were named after a fictional mad doctor…although maybe they should have been.

In order to prevent others from suffering from a similar misapplication of creative genius, what I’m going to look at here is :

  • How Oracle caches table data in Memory
  • How to work out what tables are in the cache
  • Ways in which you can “pin” tables in the cache (if you really need to)

Fortunately, Oracle memory management is fairly robust so there will be no mention of leeks

Continue reading

Caching in – adventures in Oracle Tuning

Given the job of tuning a problem statement, I’ll usually try to work on it on a lightly used database. More importantly, where practical, I’ll execute the statement twice, one after the other, and use the response time for the second run as the starting point – the timing that my tuning changes to the statement need to improve upon.

My reason for doing this is simple – I want to filter out the effects of database caching on performance of this individual statement.

Obviously, this approach tends to work best for relatively short running queries. If you’re lumbered with a 12-hour Batch behemoth, then this isn’t going to be an appealing plan of attack. Continue reading