Search

OakieTags

Who's online

There are currently 0 users and 39 guests online.

Recent comments

Affiliations

Oracle

FBI Skip Scan

A recent posting on the OTN database forum highlighted a bug (or defect, or limitation) in the way that the optimizer handles index skip scans with “function-based” indexes – it doesn’t do them. The defect has probably been around for a long time and demonstrates a common problem with testing Oracle – it’s very easy for errors in the slightly unusual cases to be missed; it also demonstrates a general principle that it can take some time for a (small) new feature to be applied consistently across the board.

The index definitions in the original posting included expressions like substr(nls_lower(colX), 1, 25), and it’s possible for all sorts of unexpected effects to appear when your code starts running into NLS  settings, so I’ve created a much simpler example. Here’s my table definition, with three index definitions:

OTN Yathra 2014 : Hyderabad

I literally got no sleep last night before the Hyderabad event today. My hotel room overlooked what looked like the busiest road in the world and Indian drivers have a love affair with their car horns. Every time I was about to nod off, a different tone of horn would wake me. :)

So this morning, with a breakfast of coffee in me, we headed off to the huge Oracle campus in Hyderabad! The event was split into a technical track and an apps track. Not surprisingly, I spent the day in the technical track.

Oracle Certified Professional (OCP) : I’m in a promotional video!

I received a Facebook comment from Václav Kozák to say I was in a promotional video for the Oracle Education’s OCP Program, rockin’ an oraclenerd t-shirt I might add… :)

That was a 2 second soundbite from a 10+ minute interview I filmed with Oracle a couple of years ago. I am a fan of the OCP program, but my opinions are not necessarily in line with Oracle Education’s marketing message. Even so, I am happy I was included in the video, even if it was a small soundbite. :)

OTN Yathra 2014 : Pune

The Pune event took place in the local Oracle office, which is a very impressive building.

I did three sessions at this event. I was in the first slot with a virtualization presentation, then spent most of the next session talking to some of the attendees. After lunch I had a session on PL/SQL performance, then watched two APEX sessions by Raj Mattamal. After that it was my final session for the day, which was on analytic functions. I spent a long time talking to some of the attendees after my last session, which made us quite late leaving. :)

It was a really enjoyable event, with some great interaction with the audience!

OTN Yathra 2014 : Mumbai

The Mumbai event took place in the local Oracle office and was attended by a straight technical crowd, which is much easier to present to than a mixed audience. :)

In addition to my own talks, I got to see some others too.

Indexing LOBs

Many years ago, possibly when most sites were still using Oracle 8i, a possible solution to a particular customer problem was to create a function-based index on a CLOB column using the dbms_lob.getlength() function call. I can’t find the notes explaining why this was necessary (I usually have some sort of clue – such as the client name – in the script, but in this case all I had was a comment that “the manuals say you can’t do this, but it works provided you wrap the dbms_lob call inside a deterministic function”).

Index Compression – aargh

The problem with telling people that some feature of Oracle is a “good thing” is that some of those people will go ahead and use it; and if enough people use it some of them will discover a hitherto undiscovered defect. Almost inevitably the bug will turn out to be one of those “combinations” bugs that leaves you thinking: “Why the {insert preferred expression of disbelief here} should {feature X} have anything to do with {feature Y}”.

Here – based on index compression, as you may have guessed from the title – is one such bug. I got it first on 11.1.0.7, but it’s still there on 11.2.0.4 and 12.1.0.1

EM12c Creating a New Agent Registration Password

I know, I know… Just answering questions that I keep receiving from folks repeatedly, so if you know this one, love ya, if you need this answer, here it is! :)

Proper Resync of an Agent

When an agent reports that it’s blocked and needs to be resync’d, most DBAs are going to log into the Enterprise Manager 12c console and attempt a resynchronization to have it fail.  A resync isn’t required very often, but if you do run into “Agent Blocked”, here are the initial steps that should be performed to have a resync complete successfully.

Averages

Recently while observing AWR reports, I’ve seen a very good example of how average value hides important pattern.

Here is a Workload Comparison section from an AWR diff report (generated with $ORACLE_HOME/rdbms/admin/awrddrpt.sql):