Anniversary

I missed my fifth blogging anniversary by a month – my first post was dated 24th Oct 2006 on the topic of the parameter optimizer_index_cost_adj.

Since then I’ve posted about 670 articles (and deleted a few temporary ones) and drafted another 110; I’ve acquired 750 followers;  and the blog has inspired a little  over 6,000 comments. It’s amazing how time passes and the numbers accumulate.

To celebrate the event I’ve created a new category (see top right) of “Site History” so that I could label that first post and the various posts I’ve made about viewing figures and popular posts.

Oracle Core

28th November 2011

I’ve just received an email telling me that Apress is having a “Happy Cyber Monday”. Translated into English this means that for today only they’re selling every eBook at  $15.00 each. If you were planning to get an eBook version of Oracle Core, today might be the best day to do it. Here’s a link to the specific page on the Apress site. I’ve repeated this link in the picture of the book at the top-right of this page.

Note – the book is available in ePub, Mobi, and pdf formats.

Note 2 – it’s possible that some local sales tax will be added dependent on where you are purchasing from.

Update 9th Dec

I’ve uploaded the source code library to my blog as a zip file. Instructions for download are on my index page for the book.

Update 10th Dec

Although the book still isn’t available directly from Amazon UK, it’s been on their rankings as the number 1 Oracle book for the last 24 hours. That’s nice, of course, but the reason I wrote this update wasn’t to brag, it was to point out that one of the other categories that Amazon has assigned to the book to is Home Office Databases!

IOT Answer

It was good to see the answers to the last Quiz Night accumulating. The problem posed was simply this: I have two IOTs and I’ve inserted the same data into them with the same “insert as select” statement. Can you explain the cost of a particular query (and it’s the same for both tables) and extreme differences in work actually done. Here’s the query, the critical stats on the primary key indexes, the shared plan, and the critical execution statistic for running the plan.

Quiz Night

Inspired by Martin Widlake’s series on IOTs, I thought I’d throw out this little item. In the following, run against 10.2.0.3, tables t3 and t4 are index organized tables, in the same tablespace, with a primary key of (id1, id2) in that order.

Index Hints

A new form of index hint appeared in 10g – and it’s becoming more common to see it in production code; instead of naming indexes in index hints, we describe them. Consider the following hint (expressed in two ways, first as it appeared in the outline section of an execution plan, then cosmetically adjusted to look more like the way you would write it in your SQL):

INDEX(@"SEL$1" "PRD"@"SEL$1" ("PRODUCTS"."PRODUCT_GROUP" "PRODUCTS"."ID"))
index(@sel$1 prd@sel$1(product_group  id))

IOTs

That’s index organized tables, of course. Searching back through my blog I find that I’ve only written one article about IOTs- although I’m very keen on taking advantage of them, and have made a few references to them in other articles. Rather than addressing this oversight myself, I thought I’d direct you to a series on IOTs by Martin Widlake.

• Part 1: The basics
• Part 2: Examples and Proofs
• Part 3: Greatly reduced I/O
• Part 4: Boosting buffer cache efficiency
• Part 5: Primary Key issues
• Part 6a: Inserts and upates slowed down
• Part 6b: OLTP inserts into an IOT

Planet Earth

I know it’s another post that’s not about Oracle, but someone sent me this video link a couple of days ago and it’s too wonderful not to share. (I’ve just got back from Iceland, so the Aurora Borealis at 1:05 is particularly relevant)

Hinting

As I’ve often pointed out, this blog isn’t AskTom, or the OTN forum, so I don’t expect to have people asking me to solve their problems; neither do I answer email questions about specific problems. Occasionally, though, questions do appear that are worth a little public airing, and one of these came in by email a couple of weeks ago. The question is longer than the answer I sent, my contribution to the exchange doesn’t start until the heading: “My Reply”.

Irrational Ratios

I’ve pointed out in the past how bad the Instance Efficiency ratios are in highlighting a performance problem. Here’s a recent example from OTN repeating the point. The question, paraphrased, was:

After going through AWR reports (Instance Efficiency Percentages) I observed they have low Execute to Parse % but high Soft Parse %.
Please share if you had faced such issue and any suggestions to solve this