Want to Know More about Oracle’s Core? October 19, 2011

I had a real treat this summer during my “time off” in that I got to review Jonathan Lewis’s up-coming new book. I think it’s going to be a great book. If you want to know how Oracle actually holds it’s data in memory, how it finds records already in the cache and how it manages to control everything so that all that committing and read consistency really works, it will be the book for you.

{Update, Jonathan has confirmed that, unexpected hiccups aside, Oracle Core: Essential Internals for DBAs and Developers should be available from October 24, 2011}

Jonathan got in touch with me around mid-May to say he was working on the draft of his new book, one that would cover “how does Oracle work”, the core mechanics. Would I be willing to be one of his reviewers? Before anyone comments that there is not likely to be much about core Oracle that I know and Jonathan does not, he did point out that he had already lined up someone to be his technical reviewer, ie someone he expected to know as much as he and help spot actual errors. The technical reviewer is the most excellent Tanel Poder, who posted a little mention of it a couple of months back.

I was to act more like a typical reader – someone who knew the basics and wanted to learn more. I would be more likely to spot things he had assumed we all know but don’t, or bits that did not clearly explain the point if you did not already know the answer. ie an incomplete geek. I figured I could manage that :-).

It was a lot harder work than I expected and I have to confess I struggled to supply back feedback as quickly as Jonathan wanted it – I was not working but I was very busy {and he maybe did not poke me with a sharp stick for feedback soon enough}. As anybody who has had to review code specifications or design documents will probably appreciate, you don’t just read stuff when you review it, you try and consider if all the information is there, can it be misunderstood and, if you find that you don’t understand a section, you need to work out if the fault is with you, with the way it is written or with what is written. When I read a technical {or scientific} document and I do not fully understand it, I usually leave it a day, re-read it and if it still seems opaque, I just move on. In this case I could not do that, I had to ensure I understood it or else tell Jonathan why I thought I did not understand it. If there are sections in the end book that people find confusing, I’ll feel I let Jonathan down.

Just as tricky, on the one hand, as I’ve been using Oracle for so long and I do know quite a lot about Oracle {although clearly not enough in the eyes of the author 🙂 } I had to try and “not know” stuff to be able to decide if something was missing. On the other, when I wanted to know more about something was I just being a bit too nerdy? I swung more towards the opinion that if I wanted to know more, others would too.

I have to say that I really enjoyed the experience and I learnt a lot. I think it might change how I read technical books a little. I would run through each chapter once to get the feel of it all and then re-read it properly, constantly checking things in both version 11 and 10 of Oracle as I read the drafts and would not let myself skip over anything until I felt I really understood it. As an example, I’ve never dug into internal locks, latches and mutexes much before and now that I’ve had to learn more to review the book, I have a much better appreciation of some issues I’ve seen in the wild.

Keep an eye out for the book, it should be available by the end of this year and be called something like “Oracle Core” {I’ll check with Jonathan and update this}. I won’t say it will be an easy read – though hopefully a little easier as a result of my input – as understanding things always takes some skull work. But it will certainly be a rewarding read and packed full of information and knowledge.

One more thing, it’s listed on APress as “beginner to intermediate”. From your description, it’s more advanced than that. Could you comment if the content has plenty of expert content? I do have his book on the optimizer and that’s not exactly what I’d call “beginner to intermediate”. So I can’t quite expect that based on their “rating” but that might be “downgraded” to sell more books and that’s why I’m asking.

To get the best out of the book, you need to already appreciate SQL, you need to have some idea of commit and also of point-in-time view of data – ie, you see the data as it is at the millisecond your query starts, irrespective of any data changed or loaded after your query started. The book assumes you know that sort of thing. It also assumes you want to learn and you might have to put some effort into thinking. If you want a book of Oracle Cookery Instructions, this is not the book for you. It helps if you have had some exposure to database theory or management, but in my experience, good Oracle developers and DBAs have this, often without realising. More importantly, you need to have a desire to know how Oracle works – from a logical level up. This book tells you how things work. The fact that you already have Jonathan’s book on the CBO indicates you want to learn. If you read chapter one of CBO fundamentals and gave up, this new book is not for you. If you kept with it beyond chapter one {and I don’t mean understood it all}, then it is for you. I’d say the new book is a little easier to read than Cost Based Fundamentals.

As an example, as I indicated in my post, I have never got to grips with low level locks before. It’s an area where I knew roughly how it worked, had an appreciation it was important and if I read a good article on it, I’d follow the drift. Jonathan’s book fully explained about hash algorithms, hash chains and latches. In fact, I wish I’d had something like this to read back in 1993 when I had been doing Oracle for 2 years. When I started with Oracle I learnt a lot in the first couple of years as I was on a major Oracle Corp project. By the end of two years I had a good understanding of SQL, of relational theory, of what commit meant and what point-in-time view of the data meant. I’ve met people since who have used Oracle for 10 years and don’t have that knowledge. That is not because I am bright and they are not, I just had to know that stuff due to my working environment – and I was interested. I’ve also met people since who just get all of this in six months. That is what I mean about relative.

All the above boils down to the fact that I think the book is intermediate and takes you to advanced. Intermediate means you can make a punt at answering questions like “what is commit” and “what is a block of information in a table”. If you don’t know that, you are not ready. If you do, the book tells you enough to build on it and go towards advanced. Part of my remit was to try and help Jonathan ensure that the book did not assume too much pre-knowledge and explained things enough so you would not be going “ah but”. I would have really appreciated this book after those two years of hard introduction. I still appreciated it after 20 years or experience, because it really does explain the fundamentals so well.

Thanks for taking the time to write an extended reply. You get one in return.

I broke down and bought the Alpha edition and am looking it over. I should have explained that I’m one of the cursed consultants who work on SAP and do so on a wide variety of platforms and databases. I’ve been doing this since 1995 and have a fair bit of experience (practical and book) from 7.3.2 and up. At least as much as you can have where the system uses the DB in terms of what is portable to all databases rather than design it around the DB. As such, I tend to use the advanced features less frequently than I’d like. It also means I get stretched rather thin and don’t quite get the luxury to specialize on just the DB or only one DB. So I don’t write PL/SQL constructs but can write my own scripts and the like.

So right now, I get to work on such lovely systems as (sob!) Sql Server and (gack) DB2 also and have learned to appreciate the tracing and diagnostic features in Oracle which are sorely lacking elsewhere. Jonathan, Cary, Tanel and others have guided my views towards what performance should measure as well. So when I get to see performance metrics presented as aggregates I tend to wonder why the heck the 10046 trace isn’t present in the other DBs.

As for Jonathan’s optimizer book, I worked through most of the examples on versions 9 & 10 which has helped me a great deal with 11g on some of the failings of SAP with Oracle. I even managed to get through Steve Adams book on internals a long while back and I’d like to think I retained the majority of it. But both of those books showed their work and explained their reasoning and methods rather than made assertions at the reader.

I’m a long way past looking for canned solutions, “silver bullets” or hacks. I’ve learned enough, through (mostly) the mistakes of others, to realize that taking canned advice at face value is silly. I take a bit of pride in being lazy. That means I’ll put in the effort up front to get it right rather than have to keep redoing it or maintaining it. That’s why I appreciate those who show their work, explain their methodology and welcome questions.

So you’ve answered my question, I’ve also taken a quick glance to confirm that APress has this labelled wrongly. (Trust, but verify!)

This book, from my quick perusal of it in pre-published form anyway, has a slightly different perspective which is always welcome and handy to have as a portable electronic reference. Once of the things which makes supporting Oracle a joy is there is no shortage of quality books, documentation and expertise to explain the methodology of how Oracle works, how to find the issues and diagnose them and how to take advantage of what it offers.

Well, from what I know of SAP it can be a bit of a trial to work with, but it pays very well 🙂

I’m glad I get to work on a wide spread of the oracle DB feature set but I wish I had more exposure to other DBs {I know/knew MySQL quite well at one point}, so we are probably either ends of the spectrum from that perspective. Where we are very similar is (a) how long we have been in this game and (b) we both want to know how things work and create solutions that last. That makes Jonathan’s book ideal for you. Especially as you like Cary, Tanel and Steve’s work and the whole scientific, show-why-you-know attitude.

BTW Tanel was the tech reviewer for Jonathan’s book, I’m just updating the body of the text to reflect that.

The “wide exposure” has a bit of a downside. The perks are getting to work with newer technologies like in memory DBs (SAP’s HANA and LiveCache are pretty interesting.but are essentially black boxes). But getting to work with other DBs just reinforces how far ahead the overall Oracle architecture is in its implementation. As I started as a COBOL programmer and moved off to having to support the SAP applications and other Oracle applications, I learned to appreciate the engineering involved in getting SAP to work on multiple platforms and databases.

So I’ve shorted Tom Kyte here as his books on (mostly) application design and architecture (from a standpoint of wanting the business logic in the DB) have also done a magnificent job in getting the entire picture and helped greatly in sorting out the applications written expressly to work in Oracle.

Why the authors make a difference is that they encourage and instruct the reader to think critically and question even the authors assumptions, thus books like Steve’s and James Morle’s, though written for 8i, are still on my bookshelf.

As for my remuneration: SAP = Salary Advancement Package <- old but still valid joke.

Thanks for mentioning the book – and thanks for your contribution.
I think it’s worth mentioning particularly that the number of diagrams I put into the book probably doubled thanks to your input – and some of the descriptions are probably a little clearer and less likely to be misintrpreted because of your comments.

The dates seem to have slipped at the last minute, though. Apress still lists publication as 24th Oct, but Amazon UK has 31st Oct and Amazon US has 9th Nov … and both have a picture of a cover with the title “iPhone and iPad Apps for Absolute Beginners” !