Oracle Scratchpad

December 22, 2014


Filed under: Non-technical — Jonathan Lewis @ 5:28 pm GMT Dec 22,2014

I received an email a few weeks ago asking me if I would look at a series of three posts on adaptive dynamic sampling in 12c – (part 1, part 2, part 3). I took a note of the topic and URLs, and read through them fairly rapidly, and they seemed to be perfectly reasonable articles describing the authors thoughts, tests, and observations. Inevitably, though, several questions ran through my mind as I read – typically along the lines of “what would happen if …”, “did you restart the instance before …”, “did you flush the shared pool between …”. It’s very hard to create, run, and report a set of tests that allow you to make solid inferences about how Oracle and the optimizer behave – and whatever you do someone else will find a way of asking some questions that push the envelope a little further.

I got a follow-up email a week or so later, asking if I’d had time to look at the article because the author wanted to present the topic at a local (Azerbaijan) user group event – and the follow-up email prompted me to write this blog note.

Given that it’s taken about three weeks for me to get around to writing this note you might appreciate that I don’t have a lot of time to spare on a topic that I’m not actively pursuing for a good reason. I accumulate a lot of information from around the internet, from books, and from presentations, and I’ll invariably attach lots of questions and conjectures to that information but I won’t necessarily be able to have any confidence in its utility or correctness. When I have to know an answer I may go back to a source I’ve noted and use it as a basis for doing some further goal-oriented investigations – but until I need to (or unless I’m particularly curious) I don’t have the time to do arbitrary research. This means, of course, that I don’t have time to get into a dialogue with people about the work they have done and the information they have presented.

On the other hand, of course, I thoroughly approve of anyone who takes the time to do the experiment and write up the results. And I heartily approve of anyone who is prepared to stand up in front of a user group and share their observations and want to encourage people to do this; so this is the reply I sent which, I hope, is suitable positive:

There are a number of questions that I would probably want answered if I were to examine the posts in detail, and I would want to repeat the tests that you have done to check that I got the same results and to see if there were other observations I could make that might lead me to different conclusions. This is something that takes far too much time to do properly, and I have no inside information that would allow me to say very quickly whether your comments are right, wrong, or simply incomplete.

The important point, though, is that you have set up some tests, documented the results, and offered some conclusions. That is sufficient for your presentation to the user group.
  • This is what I did
  • This is what I observed
  • This is what I concluded
 At worst someone will ask some questions like: “Did you check …”, or “What happens if …”
You may need to answer “I don’t know”, or “I didn’t think of doing that”, or “That’s a good idea, I’ll try it”; but whatever happens everyone learns something, and everyone has the opportunity to learn more based on what you have given them.
In passing, at the very first presentation I did for the UK Oracle User Group about 25 years ago I got about half way through some comments on an odd performance pattern I had observed when modifying the SQL*Plus arraysize when someone stuck up their hand and asked: “Did you check the tcp packet size?” The only response I could make, after a few seconds pause to think, was: “I should have thought of that. No.”


  1. hi Jonathan, this post is a nice christmas gift. You’re one of this unforgetable teachers anyone is lucky to meet in his student life. Just because you not only share your knowledge but because you know how to encourage people to get the best of themselves. I still remember this very special moment in time, that was many years ago, during one of your seminars at Oracle university (France). I came to you because i did not understand sort/merge and one/multi path sorting algorithm. You took a piece of white paper, and you started to draw and explain each single step of the algorithm. That was just brilliant, and like with this post, you unlocked the small room i was stuck in.
    Thanks for all this invaluable help and advices you give to all of us. You’re the best Oracle teacher i’ve ever met.
    Merry christmas and happy new year !

    Comment by Olivier Bernhard — December 24, 2014 @ 6:17 am GMT Dec 24,2014 | Reply

  2. Hi Jonathan.
    Thank you for your valuable advices and for supporting oracle community.

    Comment by Chinar Aliyev — December 26, 2014 @ 7:04 pm GMT Dec 26,2014 | Reply

  3. Hi Jonathan,

    I find following very interesting string of words in your note. It is interesting since we hardly find the usage of words ‘inference’, ‘conjecture’, ‘confidence in utility’, ‘dialogue’ in Technical blogs/discussion/documents.

    make solid inferences
    attach lots of questions and conjectures
    confidence in its utility or correctness
    go back to a source
    goal-oriented investigations
    get into a dialogue

    Do you suggest DBAs learning (getting familiarized/taking a course/reading) the Philosophy of Natural World also ?


    Comment by Jayant — December 31, 2014 @ 5:10 pm GMT Dec 31,2014 | Reply

    • Jayant,

      It hadn’t occurred to me that I was using so many expressions that could prompt such a Popperesque thought. I think a course would be a little excessive, but it would certainly be a good idea if DBAs were reminded every now and again that they are working in an area where there are many gaps in the available knowledge and that much of what we read about Oracle is really only a set of tentative conclusions based on observations.

      Unfortunately many articles about Oracle are written as if the content was absolute fact – and that’s understandable since everything we read would otherwise be peppered with “I believe …”, “it seems likely that …”, “the results suggest …” – which would make for very tedious reading. So (in an expression that Tom Kyte uses frequently) “trust by verify” is a good habit to acquire, and simply remembering that what you read is at best a good approximation and not necessarily accurate and complete is critical.

      Comment by Jonathan Lewis — January 3, 2015 @ 1:34 pm GMT Jan 3,2015 | Reply

      • Hi Jonathan,

        Your comment about the gaps in the available knowledge has prompted me to think more about the gaps in how we seek, construct and test the knowledge.

        Thank you for sharing your thoughts and as always demonstrating the processes thoroughly leading to the results.


        Comment by Jayant — January 4, 2015 @ 11:24 am GMT Jan 4,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by