Kerry Osborne's Oracle Blog

Thanks to everyone that submitted abstracts for our upcoming E4 conference. Unfortunately, there were more quality submissions than we had room for. Maybe next year we should expand the event to 3 days. 🙂 But in the meantime, we have assembled what I believe is an excellent line up of speakers. I’ll just mention a few highlights here:

Tom Kyte will be doing the keynote. Enough said!

Maria Colgan and Roger MacNicol will be doing a 3 hour combined session on smart scans. Maria will attack the topic from the top down (optimizer) point of view (since she is the product manager for the optimizer) and Roger will be attacking it from the bottom up (since he is the lead developer for the smart scan code). This should be an awesome session and Tanel Poder has already said he was going to line up the night before.

Ferhat Sgonul will be talking about Turkcell’s usage of Exadata. Turkcell is one of the earliest adopters of Exadata and has had great success with it over the last several years, so this should be a very interesting case study.

Karl Arao and Tyler Muth will do a joint presentation on visualization techniques for performance data from Exadata environments. The plan is for them to compare and contrast their approaches using the same data set. Tyler usually uses R and Karl likes Tableu – may the best violin chart win.

Tyler Muth will also be doing a deep dive presentation on bloom filters and how they can be offloaded with smart scans. This is a topic about which there is little information, so it should be quite interesting.

Frits Hoogland will be doing a deep dive on how Oracle does multi-block i/o. This is of special interest with regard to Exadata because the direct path mechanism for doing multi-block i/o is a requirement for enabling smarts scans. So understanding how it works is one of the keys to getting the most out the platform.

Sue Lee (product manager for resource manager) will be doing a session on how to deal with mixed workloads. I’m really interested in this session as IORM and DBRM are critical for managing Exadata, particularly when it is used as a consolidation platform.

There are many other well known speakers including Martin Bach, Andy Colvin, Gwen Shapira, Mark Rittman, Tim Fox and Tanel Poder.

Here’s a link to see the complete line up of E4 speakers.

While we’re on the subject, I should mention that there will be several talks on hadoop related topics and the increasingly expanding role it is playing in our industry. The idea of pushing the work to the storage is not unique to Exadata. It is also the main driver behind hadoop. So I’m extremely pleased to announce that Doug Cutting will be speaking at E4 as well.

So that’s all for the marketing related stuff on E4. I hope you can join us in Dallas.

Just a quick note to remind you that the call for papers for E4 is closing in a few days (on April 30).  So if you have anything you think is interesting related to Exadata that you’d like to share we’d love to hear from you. By the way, you don’t have to be a world renowned speaker on Oracle technology to get accepted. You just need to have an interesting story to tell about your experience. Implementations, migrations and consolidation stories are all worthy of consideration. Any interaction between Exadata and Big Data platforms will also be of great interest to the attendees. Of course the more details you can provide the better.

Here’s a link to the submission page:

Submit an Abstract for Enkitec’s Extreme Exadata Expo

Feel free to shoot me an email if you have ideas for a talk that you want to discuss before formally submitting an abstract.

I got an email a few days ago . . .

Subject: Exadata / your blogs / you aren’t saying to put HINTS on everything, Are you…

. . . A peer of mine found a couple of your blogs listed below which discuss Profiles and Hints. The position he is inferring upon you is that you are stating unequivocally that we should create lots of indexes, add hints, create profiles and outlines on our Exadata machine. Having read your book and achieve actual results in line with that book, I know that pretty much the opposite is true. . . .

Here’s what I said in my reply (edited a bit to clean up typos and to be a bit more precise):

I do not advocate widespread usage of hints or hint based mechanisms (Outlines, Patches, Profiles). However, because I have written quite a bit about using these techniques, I fear I may have inadvertently left people with the impression that I think hints should be used everywhere. I do not. I view them as expedient ways to change plans or lock in specific plans that I want, but in most cases I use them as bandaids to temporarily cover up other problems. I haven’t really written a blog post specifically on my view of the proper usage of these techniques, so maybe I should do that. When I do presentations on hint based mechanisms to control execution plans, I always have a slide with a picture of a vial of morphine. I use that metaphor to caution people not to over use them. There is a presentation on my blog in the whitepapers/presentations section called “Controlling Execution Plans” (or something to that effect) that you can download which has that slide in it. Also I wrote a chapter on controlling execution plans in a book called Pro Oracle SQL that probably has some prose on when and why to use them (and not use them). But in general – I think they should only be used to give temporary relief until underlying problems can be fixed or in cases where the optimizer just can’t deal with a specific situation (like correlated columns for example) or in cases where you’re using a packaged app that has badly written code that you can’t change. The newest version of the optimizer (which should be released soon) actually stores plans and not just hints with Baselines, so I will leave out the case where you want to manage plans for stability sake.

Since you mentioned Exadata I’ll add that the issue of mixed workload DB’s on Exadata and when the optimizer uses indexes vs when it doesn’t is not straight forward. In general, the current version of the optimizer doesn’t know it’s running on Exadata so it tends to cost FTS’s too high, meaning it often picks indexes when a full scan would be faster. There are some things that can be done to push the optimizer towards full scans. Think of anything that pushes the optimizer in that direction – large db_file_multiblock_read_count for example. Also, System Stats has recently been updated to have an “Exadata” mode which basically just updates the MBRC setting to 128 which does the same sort of thing. If that’s not enough to get most of the statements behaving the way you want then exceptional measures can be taken such as making indexes invisible and using alter session for specific processing to enable the optimizer to see invisible indexes, or doing away with some indexes altogether. Hints (or hint based mechanisms) can be used as well. But my goal would be to get the optimizer to pick the right plan on it’s own in the vast majority of cases before resorting to the more extreme measures.

Hope this helps.

Here’s a copy of the slide I referenced in the email and have been using recently:

Continue reading ‘Bad Advice?’ »

I was doing a little research for an upcoming presentation on Oracle 12c Adaptive Optimization and I came across a series of posts on Dynamic Sampling by Randolf Geist (one of my favorite Oracle smart guys). I could not find a complete list of the series of posts either through Google or the search function on the site where they were posted. I guessed at a couple of the url’s and got lucky. Thought it might be useful at some point in the future so I wrote them down on my blog (which is where I often keep private notes). But decided it might be useful to others so decided to go ahead and publish it.

Dynamic Sampling – Intro – Part 1
Dynamic Sampling – Intro – Part 2
Dynamic Sampling – Controlling the Activity – Part 1
Dynamic Sampling – Controlling the Activity – Part 2
Dynamic Sampling – Real Life Data – Part 1
Dynamic Sampling – Real Life Data – Part 2

Here are a couple of other posts Randolf has done on the topic of Dynamic Sampling.

Volatile Data, Dynamic Sampling and Shared Cursors
Dynamic Sampling on Multiple Partitions – Bugs

Note these posts are all referring to 11gR2 or earlier releases.

I got a look a new prototype for the next generation Exadata last week while doing some work with a company in Europe. Apparently there is a big push to be environmentally friendly there now and so Oracle is trying to come up with a model that uses less power and is biodegradable. The word on the street is that it won’t be available until after release 2 of the 12c database.

The new model has a few drawbacks though. For one thing, it only lasts a few weeks before you must either replace it or higher some rocket surgeon consultants to come in and tune it. From the early version of the prototype I saw, it does appear to be smaller and more tasty than previous models though.

That’s a picture of the lead designer (JP) showing off the prototype. The code name for the project is “Exanana” by the way. The new model should be available in select supermarkets after lunch (err launch).  Here’s another picture of JP and one of the other designers (Paul) hamming it up for the camera.

I probably should have saved this post for April 1st!

I know no one really likes the term “tuning” these days, but it’s a short catchy word that gets the idea across. So I’ll just stick with it for the title of this post.

Note that this is one of those posts that’s not really supposed to be about how to solve a particular problem. It’s really just a story about a distraction that I ran into and I how I thought about getting around the issue and then ultimately resolving the root cause. Maybe you will find it instructive to see the process.

So I have this script that I use occasionally (paramon.sql) to see what parallel query slaves are doing. Unfortunately the script doesn’t have a header in it, but I’m pretty sure I lifted it from Randolf Geist. I can’t find it on his blog anywhere, but it looks like his style of writing SQL, and PX Query is something he’s written a lot about, so I’m pretty sure that’s where I got it. (Update: see Jonathan Lewis’s comment below attributing the script to Andy Brooker) Anyway, the script has worked great for me in the past but I recently noticed that it was really sluggish on a couple of 11gR2 DB’s running on Exadata. Here’s an example:

-bash-3.2$ !sql
sqlp

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 14 12:23:15 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


INSTANCE_NAME    STARTUP_TIME               CURRENT_TIME                  DAYS    SECONDS
---------------- -------------------------- -------------------------- ------- ----------
dbm1             09-JAN-2013 03:25          14-JAN-2013 12:23             5.37     464246

SYS@dbm1> set SQLPROMPT "11.2.0.3> "
11.2.0.3>
11.2.0.3> @paramon
Enter value for status: 

 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     35
      P001 AVAILABLE     36
      P002 AVAILABLE     37
      P003 AVAILABLE     38
      P004 AVAILABLE     39
      P005 AVAILABLE     40
      P006 AVAILABLE     41
      P007 AVAILABLE     42
      P008 AVAILABLE     43
      P009 AVAILABLE     44
      P010 AVAILABLE     45
      P011 AVAILABLE     46
      P012 AVAILABLE     47
      P013 AVAILABLE     48
      P014 AVAILABLE     49
      P015 AVAILABLE     50
      P016 AVAILABLE     51
      P017 AVAILABLE     52
      P018 AVAILABLE     53
      P019 AVAILABLE     54
      P020 AVAILABLE     55
      P021 AVAILABLE     56
      P022 AVAILABLE     57
      P023 AVAILABLE     58
      P024 AVAILABLE     59
      P025 AVAILABLE     60
      P026 AVAILABLE     61
      P027 AVAILABLE     62
      P028 AVAILABLE     63
      P029 AVAILABLE     64
      P030 AVAILABLE     65
      P031 AVAILABLE     66


32 rows selected.

Elapsed: 00:00:23.11

So on this 11g DB it took 23 seconds to run the query. On one of my 10g DB’s though the performance was stellar.

Continue reading ‘Tuning paramon.sql’ »

Just a quick note to let you know that I updated the presentations section of my blog. Most of the talks I’ve done over the last 3 years, including Hotsos Symposium, RMOUG Training Days, Oracle Open World, UKOUG Conference and Enkitec Extreme Exadata Expo (E4) are now on the site.

Just a quick note to point out that the official white paper version of the TCO study I talked about in a previous post (Exadata vs. IBM P-Series) is now available on the Oracle web site here:

Cost Comparison – Oracle Exadata Database Machine vs. IBM Power Systems

The most interesting part of the study (in my opinion) is the quotes from the participants that are using Exadata. These quotes provide some insight into how people feel about the platform after having systems in production for a while (note that all the interviews were done prior to the release of the X3-2). I should also point out that these customers are not typical Oracle reference customers. They were interviewed by a third party and promised anonymity as part of the study.

Here’s a link to a video of Mogens Norgaard discussing the upcoming OakTable World conference (click on the picture of Mogens below). Note that the name of the conference has been changed from Oracle Closed World to OakTable World.

Fellow Enkitecies Karl Arao and Tanel Poder will both be presenting at the event along with a bunch of other well known OakTable members. I may even do a 10-15 minute TED style talk at lunch time on Monday. Hope to see you there.

The 2012 Enkitec Extreme Exadata Expo is behind us now. Our video guy (Bob) has been working diligently for the last week or so to get the presentations edited. They will be made available to the attendees shortly. We have already posted a video of the opening session. It is me interviewing Cary Millsap about his impressions of Exadata. One of the things I have found most interesting about Exadata is how it makes very experienced Oracle performance guys re-think things. It’s fun watching them being exposed to Exadata in an intimate way (not just Power Point). The reactions are interesting. There is usually a desire to try to break it although  it’s generally harder than it appears, at least initially. I got to watch Cary for a few days (along with several of his Method-R guys) when he had his first exposure and we talk about that during the video. Cary made a profound impact on me and my career and I think his insight into the Exadata architecture is worth watching. Hopefully the fact that I have a great face for radio will not deter you (too much). The video has the obligatory Enkitec logo and endless loop, non-descript music intro, but other than that, I certify it as marketing free! 😉

Be sure and check out enkitec.tv for other videos too.