Oracle Scratchpad

June 30, 2010

Getting Help

Filed under: Philosophy — Jonathan Lewis @ 6:31 pm BST Jun 30,2010

I hope people won’t take this as a suggestion that I want them to start using this blog like a forum – but I’d like to highlight a note written some time ago by Randolf Geist on the OTN DBA Forum: HOW TO: Post a SQL statement tuning request – template posting It’s worth following his link to the related posting by Rob van Wijk.

If you want to post a question on the Oracle forums, or newsgroups, or the list servers, (or even raise an SR) you need to think a little carefully about the information that you know but aren’t telling everyone else about. Even following the suggestions from Randolf and Rob it’s still likely that someone will ask you for more information – but at least with their guideline you’ve given other people a possible starting point for understanding your problem.


  1. Jonathan,

    I am tired working on a query tuning just now and than I read your post…I think a big issue for me has been that how do I communicate to a forum a test case that doesn’t involve the actual business related confidential table names , column names and other information and still provide a test case that has all the 7 or 8 table join query that is not doing well. I have a demon of a query with a 7 table join unioned to a four table join one of which is a view and my attempts to tame it haven’t yielded much and I can’t think of how to post an ‘innocent’ test case for it!


    Comment by Nirav — June 30, 2010 @ 6:46 pm BST Jun 30,2010 | Reply

    • Well you can create tables with different name and different columns names. So that security is not violated also you can copy stats to that newly created tables so that you will get exact same plan and you can insert sample data and can try.

      Comment by Taral — June 30, 2010 @ 7:15 pm BST Jun 30,2010 | Reply

      • Thanks Taral but what baffles me is that there are just millions of rows in the real life scenario, 8 tables having union with all these tonnes of data – I don’t know how I can match the data characteristics of actual scenario in the test case (data skewness etc)? How the test case can be a truly representative one…that is the issue for me.


        Comment by Nirav — July 1, 2010 @ 8:21 am BST Jul 1,2010 | Reply

    • Nirav,

      Two points about your question:
      a) If you have a 7 table join unioned to a four table join where one of the tables is a view then you have two simpler queries to worry about and the performance problem may be in just one of them, both of them, or neither of them. If it’s in neither of them then the performance problem is in the volume of data they produce and the need to sort it for a UNION. So “the problem” can obviously be simplified and handled in parts.

      b) Text editors allow you to do global find and replace. Create a text file with the query, the execution plan, the DDL that creates the view, and the DDL to create the indexes on all the tables. Then change the names of all the tables, indexes and columns that you think are “business related confidential”.

      Hint: if you have a table called “ORDERS” it’s probably safe to leave it unchanged; if you have a table called (e.g.) “BOEING_ORDERS” then a generic name like “ORDERS” is probably a good idea. Generic names are better than simple TAB1, COL1 – since meaning in names can be very helpful in identifying problems.

      Comment by Jonathan Lewis — July 2, 2010 @ 10:33 am BST Jul 2,2010 | Reply

  2. Posting questions as described will enhance the chances of getting a useful answer. But even better, it seems likely that following the structured process of assembling the information for the community will often result in making the answer obvious. And when a mystery remains, it seems likely indeed that the community will need the full details clearly stated to help. Jonathan, Randolf, and Rob: Well done!

    Comment by Mark W. Farnham — June 30, 2010 @ 10:05 pm BST Jun 30,2010 | Reply

  3. ++ to Mark’s comment. All these fora have a mix of experience levels, it is necessary to guide the newbies in the right direction. Sometimes they simply need to be told, other times they need a deeper understanding of how to interact. The latter can be fairly intractable, will always happen, and may worsen as technological mores evolve. MOS especially needs to remember that it is their job to determine where their customer is coming from.

    Re Nirav’s comment, it’s true, modeling a complex problem can be a lot of work. Sometimes they reduce to something that can be emulated with select * from all_objects or a simple data generation scheme, sometimes they can’t. When they can’t, it probably is not something for a general forum, except perhaps when it is solved and just the description is given (but therein lies the potential for myths). The various tools and methodologies available give clues as to which way to go when simplifying.

    In the past you could often write data scrambling routines for your large data sets, but when dealing with data distribution aware optimizer problems on top of all the other possible problems… I dunno.

    Comment by joel garry — July 1, 2010 @ 6:34 pm BST Jul 1,2010 | Reply

    • Thanks Joel…so in that case assuming that we have a complex issue of say 8 table join with millions of rows of data in them and views and histograms and you want to seek help, than how to go about? Should I just rename those tables as tab1, tab2…tab8 and columns as col1,col2 etc and view1,view2 and load data in it using select * from dba_objects where rownum<1000000 etc and assume that my 'testcase' is good? I mean at least is this a starting point? honestly I am facing this issue right now and I haven't posted a single thing to any forum as I am rather paralyzed by just the complexity of the demonic query and large number tables and view involved.

      Comment by Nirav — July 1, 2010 @ 11:07 pm BST Jul 1,2010 | Reply

      • “I am rather paralyzed by just the complexity of the demonic query and large number tables and view involved”

        OK, I’ll say it, if no-one else will:
        At this point you could call in a consultant: no need to anonymize anything.
        Or, you can learn Jonathan’s “visual tuning approach” – it helps a lot with complex queries – and help yourself.


        Comment by Flado — July 2, 2010 @ 11:20 am BST Jul 2,2010 | Reply

      • Following up Flado’s comments:
        i) A good consultant should be able to work out how to fix at least a couple of “demonic” statements in a day, and explain to you (and a small group of DBAs) at the same time how to repeat the exercise on your own – so it would be money well spent.

        ii) For a good paper on the basic steps to take, there’s the paper I published at SimpleTalk, and the webinar I gave for Embarcadero.

        Comment by Jonathan Lewis — July 3, 2010 @ 7:43 am BST Jul 3,2010 | 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