Oracle Scratchpad

November 28, 2011

Anniversary

Filed under: Site History — Jonathan Lewis @ 5:31 pm UTC Nov 28,2011

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

Filed under: Advertisements — Jonathan Lewis @ 12:54 pm UTC Nov 28,2011

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!

November 27, 2011

IOT Answer

Filed under: Indexing,Infrastructure,Oracle — Jonathan Lewis @ 10:03 pm UTC Nov 27,2011

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.
(more…)

November 25, 2011

Quiz Night

Filed under: Indexing,Infrastructure,Oracle — Jonathan Lewis @ 5:05 pm UTC Nov 25,2011

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.
(more…)

November 24, 2011

Index Hints

Filed under: Hints,Indexing,Oracle — Jonathan Lewis @ 12:41 pm UTC Nov 24,2011

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))

(more…)

November 22, 2011

IOTs

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 9:51 am UTC Nov 22,2011

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.

November 18, 2011

Planet Earth

Filed under: Non-technical — Jonathan Lewis @ 1:52 pm UTC Nov 18,2011

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

Filed under: Execution plans,Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 12:54 pm UTC Nov 18,2011

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”.
(more…)

November 13, 2011

Irrational Ratios

Filed under: Oracle,Ratios,Statspack — Jonathan Lewis @ 11:23 am UTC Nov 13,2011

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

(more…)

Theme: Rubric. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 819 other followers