Doug's Oracle Blog

(Otherwise known as #E4)

Another late post, but a few weeks ago I had the pleasure of attending and speaking at Enkitec's dedicated Exadata conference, E4. The stand-out impressions that I've been babbling away to people about since getting home were :-

1) The uniformly good quality of the presentations I was able to attend. It was an excellent agenda and speakers.

2) The great organisation, particularly for Enkitec's first attempt at this. In particular they took very good care of the speakers.

3) The small but very informed and friendly group of attendees. It's often the case that the smaller conferences feel the best because of the more relaxed atmosphere and extra interaction

4) The heat! Although the air conditioning obviously helped.

---

My conference experience started off when I was met at the airport by Jacob Osborne. That name might be a little familiar and when I saw his face I could spot the family resemblance to Kerry Osborne straight away. Then again, you need to be careful with family resemblances, as I spent the short trip from the airport discussing his brother Kerry with him, only to find out a day later that Kerry is, in fact, Jacob's dad! My only excuse is that I always forget just how old Kerry is, because he doesn't look it. (See what I did there? Sucking up might get me off the hook ...)

The next 24 hours were all about just resting up a bit after a long trip. There was some slide-polishing work involved, of course, but Enkitec had a pre-conference material deadline which I sort of met so I didn't have as much work remaining to do as usual and my conference was a lot better for it. Once everyone started turning up from the airport, delivered by various Enkitec employees and volunteers, it was the usual few drinks and then on to a really enjoyable dinner for the speakers and Enkitec folk. With some gentle arm-twisting I believe I managed to lead a few people astray. Or maybe it was others leading me astray? At least I vaguely recall Andy Klock (@andyklock) and I meandering back to our rooms at around 2am, dead on our feet! Then again, I also vaguely remember him believing that the drinking could continue?

That was me still living on UK time as planned, then! Sigh ...

---

The conference kicked off the next morning with a fun interview session with Kerry Osborne interviewing Cary Millsap about his early experiences and thoughts on Exadata. Informal and less structured than Death-By-Powerpoint, it was the ideal way to get the conference underway as it felt like the start of what would be an extended two-day conversation rather than a sit-back-and-take-the-pain conference ;-)  Best of all, don't listen to my views of what they had to say (although I found myself nodding in agreement frequently and re-focussing the points I wanted to make later in my own presentation) ... you can actually check it out for yourself!

Although I saw a little of Tim Fox's presentation it probably wasn't enough for me to comment sensibly on it. I was, as usual, a little freaked-out about my own upcoming presentation and I find all of the presentations before that awfully difficult to concentrate on properly. But a conference wouldn't be a conference if I skipped hearing Jonathan Lewis - in this case talking about - Due Diligence with Exadata.

As part of the almost obligatory funny introduction (although it has to be said JL's usually carry an important message, too) Jonathan mentioned that he'd gone on holiday during the Olympics and that had probably been a good idea as he lives in London. Mmmm, noted ....

But most of his presentation was about the down-right unpredictably of Exadata performance when you view it through the prism of Storage Indexes and HCC. As usual, he went into a lot of detail but also talked about the high-level Corporate reasons why people might plump for the platform, whilst emphasising that - like all such platforms - the detail can be important. You won't be surprised to hear that it was great stuff.

After which, although I did manage a little lunch (Texas usually means Fajitas at least one lunchtime, which is great, but I really can not eat properly when I'm fired-up pre-presentation). So after some pre-presentation tweaking and pacing, it was time for me to present on Parallel Query on Exadata.

I'd been kind of worried about this presentation (so what else is new?), specifically the content, but I think it went ok and was a good presenting day. My personal highlight of the presentation was probably this slide :-



This referred to Jonathan's earlier statement that he lives in London. I was convinced he doesn't, didn't check my facts carefully enough and then he pointed out that he lived in Surbiton. Surbiton?! I'd hardly call that London. Sadly, when I checked on Wikipedia afterwards it is just about London but I suppose just about is enough. To his credit, Jonathan later confessed that he lives a tiny distance away from the boundary. So I was right, but only just. All good knock-about fun, anyway.

As usual, I was so determined to wind-down after speaking that I missed the start of Andy Mendelsohn's keynote and, by the time I went to the room it was kneeling-in-the-aisles room only so I decided that I would skip it and check out his slides later. Which was a shame, because Enkitec persuading him to be the Keynote Speaker was quite a coup and his presentation certainly inspired a lot of conversation afterwards!

After the keynote there was a drinks reception which offered plenty of opportunity for good techie conversations with other attendees about their experiences. I finally got to speak face-to-face with Alex Fatkulin (which I'd been looking forward to) and to catch up with old friends like Mark Brady and Andy Klock. It was an earlier end to the evening, which was a damn good job with the tiredness and (cough) jetlag rapidly kicking in.

---

I had always intended it to be just a short trip to Texas so I wouldn't spend too much time out of the office, but that meant I had a lot to cram into my last day before heading to the airport. What a cracking day it was, though ...

First up for me was Karen Morton talking about Tuning SQL for Exadata. A little like my Parallel Query presentation, she focussed on the fact that most tools and techniques and existing knowledge that apply to SQL Tuning in other environments apply equally well to Exadata but, being 11g environments, the tool of choice is almost definitely Real Time SQL Monitoring. She was as entertaining and thought-provoking as always and even made a point of laying down the challenge to Maria Colgan that she'd be expecting to hear later how to get decent SQL performance on Exadata without using hints, optimizer_index_cost_adj etc. There was even some talk on Twitter about a mud-wrestling challenge, but let's not go there!

I was aware of Tyler Muth through the Tom Kyte/Apex connection but also through his blog so I was looking forward to him speaking about "Lessons learned from 2 years of Exadata benchmarks" in his current role, but I hadn't realised what an amazingly fun guy and utterly dynamic presenter he would be! I could only be this critical of someone who I *loved* watching present (if someone was useless I would probably be polite) but, truth be told, at times it was hilariously wild and chaotic but *man*, not only does Tyler captivate your attention but he was talking about exactly the stuff I'm interested in. I tweeted during his presentation that UKOUG need to get him over to Birmingham and you can be sure that whenever I see Tyler's name on an agenda now, I'll be first in the queue.

Even though I don't think I've ever heard Richard Foote speak, you expect some things when you attend one of his presentations. It will be factual, accurate and very detailed and "Indexing in Exadata" was no exception. All those things are important, of course. But what he also brought to this presentation was a lot of teaching experience with Oracle University. As I've said before, teaching a wide variety of course attendees on a regular week-in/week-out basis really polishes a person's presentation skills and I loved the combination of rigour and clear communication. Top-notch stuff, Richard!

So I just had time to hear the first 2/3 of Maria Colgan on "Exadata and the Oracle Optimizer: The Untold Story" before heading to the airport. As anyone who has heard Maria present will tell you, she's always pretty great but she was on fire at E4 ... Hilarious, to the point, bantering with and winding up the audience (particularly a certain Mr. Lewis, who gave as good as he got) and talking about some of the fascinating history behind the optimiser on Exadata and what we might expect to see in future. I understand she came top in the evaluations, which was little surprise to me. That woman is an utter *swot*!

Heading to the airport meant that I ended up missing both Tanel Poder and Kerry Osborne present but, as the whole conference was videoed, I hope to catch up with those later.

---

Everyone seemed to agree that it was an excellent conference so congratulations to Enkitec on doing a great job of the first one and long may it continue, as long as there are still Exadata-specific subjects that warrant the attention, of course!

A good one. I can't wait to go back.

I received an email that the online Schedule Builder is live. I might regret saying this but, based on an initial glance, it's an improvement on previous efforts. (Which wouldn't be difficult!)

For anyone who is interested in seeing some of the cool OEM12c Performance Page stuff, my session is :-

Session ID: CON5389
Session Title: Oracle Enterprise Manager 12c Cloud Control Performance Pages: Falling in Love Again
Venue / Room: Moscone West - 3014
Date and Time: 10/2/12, 17:00 - 18:00

I'm looking forward to it!

(Updated Later - wow, it really is a vastly improved Schedule Builder!)

Thanks to all those that attended the last Webinar organised by James Murtagh (@allthingsoracle) of Red Gate Software. It was very well attended and the feedback was all pretty good although, as I've said before, ASH Analytics presentations always seem to go down well and I suspect that says as much about the feature as it does about the presenter, if not more! If you missed out and fancied checking it out, the video is available here.

Because there have been a number of interesting questions that have cropped up during the post-presentation Q&A sections of both Webinars, James and I decided that a third and final session might be a good idea during which I'll try to work through maybe a handful of the more detailed questions or show other examples and case studies that I've used in the past. At this stage the plans are pretty flexible although the schedule and registration details are here. (and yes, that is the day I get back from Dallas after the Enkitec E4 conference but I'm hoping that the adrenalin rush will help with the jet-lag )

I already have some questions in mind from the earlier webinars, but if you have any OEM Performance Page related questions that you'd like to see covered, feel free to post them in the comments section below. Even if you asked a question during the Q&A and it wasn't answered at the time, it might be worth posting a detailed version below because this can be quite a wide subject!

I guess that James will also invite questions in advance of the webinar. Clearly I can't hope to cover everything in a limited time period so apologies if your question isn't covered but I'll do my best to get to them all somehow.

A small problem that cropped up on a client site this week which might warrant a quick post to help any Google desperadoes that might find themselves in the same spot, not least because there's an easy workaround.

The existing application schema creation scripts used the following type of syntax to create Primary Key constraints and the underlying index in one shot against Interval Partitioned Tables. (Note, this is a simple test case I created for the Service Request that you should be able to try in other environments.)

CREATE TABLE TEST (PK_COLUMN NUMBER, NON_PK_COLUMN VARCHAR2(30))
PARTITION BY RANGE (PK_COLUMN)
INTERVAL( 1)
(
PARTITION RUN_0 VALUES LESS THAN (1)
);

ALTER TABLE TEST
ADD CONSTRAINT TEST_PK PRIMARY KEY (PK_COLUMN)
USING INDEX (CREATE INDEX TEST_PK ON TEST (PK_COLUMN)
LOCAL);

If you try to run this on an 11.2.0.2 database instance, it works fine. Run it on 11.2.0.3 and you'll get the following error.

ORA-00600: internal error code, arguments: [kkpoxPaxdInit0], [], [], [], [], [], [], [], [], [], [], []

A hunt on My Oracle Support didn't yield much apart from Bug 14230768 which looks like it's at a very early stage of discovery and being fixed. As far as I can tell from my own playing around, this only happens if you're using Interval Partitioning (which might explain why this hasn't been spotted at more sites) and the simple workaround is to split the constraint work into two steps.

CREATE TABLE TEST (PK_COLUMN NUMBER, NON_PK_COLUMN VARCHAR2(30))
PARTITION BY RANGE (PK_COLUMN)
INTERVAL( 1)
(
PARTITION RUN_0 VALUES LESS THAN (1)
);

CREATE INDEX TEST_PK ON TEST (PK_COLUMN)
LOCAL;

ALTER TABLE TEST
ADD CONSTRAINT TEST_PK PRIMARY KEY (PK_COLUMN)

Some features in this post require a Diagnostics Pack license.

Just a small tip that could make things a little easier for you one day when you are trying to work out the underlying cause of a SQL execution plan change that leads to degraded performance, after the problem has occurred.

There are plenty of more technical blog posts out there referring to the contents of the OTHER_XML column in DBA_HIST_SQL_PLAN and other dictionary views. For example, this Jonathan Lewis post and the follow-up comments focus on the peeked values of bind variables.

However, it occurred to me one day that the content of the OTHER_XML column, as well as containing potentially very useful information to help understand the plan difference is also, well, erm XML. Which means that rather than trying to decipher text output in sqlplus or TOAD or whatever you use, you can just spool it to a file and open it in a browser, where the implicit structure makes for an easier read. To show you a specific example from a real performance issue where I knew the SQL_ID of the problematic statement already (although I don't have the statement outputs any more) :-

1) Identify the various execution plans :-

SELECT     snap_id, sql_id, plan_hash_value 
FROM     dba_hist_sqlstat 
WHERE     sql_id='a01f43qrd6a7g' 
ORDER BY snap_id; 

2) Drag out the various contents of the OTHER_XML column for this statement :-

SELECT other_xml
FROM dba_hist_sql_plan 
WHERE sql_id='a01f43qrd6a7g' 
AND other_xml is not null; 

By spooling individual results to different XML files, you end up with a couple of files like this.

test1.xml
test2.xml

Opening these in two different browser tabs allows you to flick between the two and, as well as seeing useful information about peeked binds, optimiser version and configuration, you should be able to spot quite quickly that Cardinality Feedback was used in generating one of the execution plans. That was the cause of the performance degradation in this case.

Of course this is just a simple example, but the main point of the post was that there might be some pretty detailed information about the optimiser environment for old execution plans in your AWR repository and that viewing it in a browser might make it a little easier to interpret if you're not absolutely married to parsing text files.

P.S. Yes, that optimizer_index_cost_adj value is for real, too