RE: CBO irregularity

  • From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 8 Jun 2004 11:51:06 -0500

I still think that a "don't share me" algorithm/directive for a SQL
statement would be a good idea. I hadn't thought of this before, but perhaps
/*+NOSHARE*/ would be a clever implementation of what I'm talking about.

Cary Millsap
Hotsos Enterprises, Ltd.
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 Boston
- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit for schedule details...

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Tuesday, June 08, 2004 8:59 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: CBO irregularity

I clearly was not clear enough regarding pet peeve number one. For over a
decade we've observed situations where parsing piles up for one reason or
another. Each time this becomes an acknowledged problem, the answer is
two-fold: Y'all improve your SQL so we have less trouble avoiding
collisions, and we'll improve our algorithms so we avoid pile-ups. That is
good as far as it goes, and I'm delighted that it continues to improve. But
I've lost count of the promises that it will never happen again.

When the wheels fall off for whatever reason, a time out on the search
followed by a one-time parse should alleviate the pile-up. I'd like that in
place as a fire wall no matter how good it gets. And I believe it should be
a modest piece of code that could be back-ported. I define that "the wheels
have fallen off" if the time to match exceeds the average time to parse and
plan. The reason I mention a one-time parse is that if the problem involves
the latch on the shared area, then waiting for the latch may be the problem
we're avoiding. Sure, another latch will be needed, but it won't be held for
searches, just for a quick give me a place to parse into and move on.

When someone has stomach flu, we don't try to feed him through the stomach;
we use iv glucose until the disease is over.

On peeve two, yes, I believe they have all the data in hand to know when to
re-plan, but I do not believe they do replan even in 10g. I think that is
why you responded "would" and "could" -- I agree. I want them to re-plan
automatically when there is a good probability that it is worth the re-plan
time, and bind variable changes are worth a look. From what we know they
have in hand, it *seems* like it wouldn't be that hard to write and that if
re-planning was only invoked when significant execution time was involved it
would be a trivial overhead when it failed to get a better plan and
potentially a big win for a better plan.

dbms_sqltune -- way cool. I'll have to give that a look.

It's always a pleasure, JL.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Tuesday, June 08, 2004 8:35 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: CBO irregularity

It may be that 10g answers all your peeves.
Notes in-line.


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Optimising Oracle Seminar - schedule updated May 1st

----- Original Message -----
From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 08, 2004 12:52 PM
Subject: RE: CBO irregularity

: The first, which dates back to v7.1 pre-betas when the sql hash algorithm
: was being played around with and often generated huge clumps of sql
: together, is that while it is wonderful to re-use parsed sql and even
: essential in cases where thousands of users just need to quickly
: exactly the same one line lookup with a different key, IF IT TAKES LONGER
: should have a bail out timer that says, hey, stop looking and just parse
: doggone thing. Space in the shared pool is important, but time is more
: important. If you're hung up on a latch, parse to a one-time area. No
: excuses, you've coded up lots harder stuff than this. Yes, writing systems
: intelligently in the first place generally avoids the problem, but that
: not solve the problem for the huge bulk of poorly coded essential
: functionality that is in production around the world (primarily emanating
: from guess where!)

    10g introduces the SQLID all over the place in place of the
    hash_value - this is intended to get "better uniqueness" because
    SQL capture over a long period of time might otherwise cause
    problems to ADDM when different text hashed to the same value.
    "Better Uniqueness" should produce very short collision chains,
    so the 'time to search' should be moot.

: The second, which Jonathan delightfully referenced, is about bind variable
: peeking. Now, if you look at the actual cost elements recorded for some
: that has been executed, you'll have a pretty good handle on cost of
: versus cost of execution. If the execution is really cheap, go ahead,
: bother re-peeking. But if the cost of execution was significant compared
: the cost of a parse, let's take another look. You might also want to mark
: SQL for a re-plan if one of the bind variables drove a histogram based
: choice. So big deal if you get a few copies of the same code. YOU COULD
: PERMUTATION SEARCH for the next value you get.

    Oracle records the 'last execution costs' for a statement,
    so it should be able to compare actual cost with predicted
    cost, and make a dynamic decision to do some more
    permutations.  Since there is an 'optimizer last permutation'
    value for each statement, then Oracle could pick up where it
    left off last time.

    Of course, this should only be applied to statements where
    the run-time cost was sufficiently different from predication.

    There's even scope, I would have thought for keeping a short
    history of bind values which cause significant variation in cost,
    with a mechanism for line by line analysis to determine where
    the most significant change appeared -- (harder, left as exercise
    to reader ;)

: The third, which regards the whole question of finding marginally or
: surprisingly better plans than an existing "good enough and stable plan,"
: why not give us a tool to run in slack time that says: Take a look at my
: most consumptive SQLs and the actual execution cost results, ignore my
: hints, and permute until the cows come home finding me a better plan and
: me know about it. Maybe I'll change my mind on the hints.

    10g - dbms_sqltune - fire in a statement, or complete workload, and
    give it a time limit on working out a single, or co-operative set
    of execution plans.

    It's a useful step forward - but doesn't cope well with bind variables.

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: