Steve Adams is in town – Oracle with a calculator

Today I attended the workshop that Steve Adams is giving in the Netherlands this week. We were with a group of four AMIS employees. The day started in a strange way. We all got a calculator. The reason why became clear during the day. Steve really showed us the internals of the Oracle database. We now know everyting to know about database blocks. We know how the block headers are build up. We know how log headers look like. We know the exact amount of bytes these headers are build of. And of course what these bytes mean.

The calculator was for calculating adresses and offsets that you can find in the Oracle data blocks. My head is still working on all this. But I am very curious about the upcoming two days. This is stuff you will not see (or need) every day. But it will help you when nothing helps you out with problems like performance tuning. We will post a more detailed review of the three days that Steve is in Utrecht soon.

.
.
.
Andre Crone.
.

—//—

So, what did bring this first day?

First of all, that the automatic self tuning new 10g features are great but they have a buildin drawback. Until now, Steve pointed out, the standard (not so OK) way was to create a working application on a database, and then afterwards look for how to tune the database (performance wise) to handle the application performance imperfections. Until now you could, most of the time, find on database level, ways to tune and improve performance. On Oracle 10g this is not so likely anymore – the database will be almost at it’s peak performance. If you have to increase performance, it will be hard to achieve and/or find workarounds for bad architectual design/testing/etc. This could, and will, result in more applications, which won’t perform on the workfloor.

If you want to increase performance, that is before Oracle 1og, the way you arange your columns does matter. Put your most selectable columns first. This will decrease your IO/CPU. Oracle internally jumps from “column pointer” (aka length fields) to “column pointer” to find the correct column it needs for the asked data. Every “jump” is one to many.

You shouldn’t create more than one controlfile. On it’s self this is logical, because every update on the controlfile (SCN’s/log switches/etc) will induce multiple IO’s. If you created more than one controlfile, these updates will then be sequentialy be applied to the other controlfiles. Instead off creating more than one controlfile, we should mirror it on the hardware level . In worse-case-scenario’s we always have the “alter database backup controlfile to trace” statement – right?.

It is possible, and Steve showed us, to show multiple after images of deleted data, which apparently is not so “deleted” as one might think. Not only on harddisks (a format is not enough to delete the old data) but also in the Oracle database, there are traces to be found of residues (flashback was already builtin years ago) 😉

Block corruption, if not detected early (checksum not enabled), can really make a mess of your database. The database will show – initially only simple forms of data corruption – “normal” behaviour. There is of course a trade-off in performance when block checksum is enabled, but you don’t want the mess i have seen today happening.

Oracle sneaky introduced file “external headers” (since 8.1.5/7?), so nowadays there are two headers (the “old” (internal) header and the external one). I really mist/overlooked this new item.

…and for today my last remark…“size does really count”. If one doesn’t give precision to column declarations (“varchar2”, instead of varchar2(2), number(5), etc.) you really create a lot of (empty) space which is often not (logically) wanted and will increase IO, BUT we already knew this – right – and therefore always declare columns correctly.

Tomorrow more goodies to be seen from Steve, so it’s time to go to bed. Let’s see if someone IS faster than Steve, with our new calculators at hand, to give the answer on some HEX to Decimal questions 😉

Marco Gralike.

ps.:
Oh yeah, to see how / what and were click the link