Re: Bind variable peeking and Dynamic sampling

11g has some really cool concepts; Leng Leng Tan presented a load of useful information at Collaborate07. It will be nice when these features become more public. Here are some of the notes I took - I am sure others out there have better notes than I:

> There was a lot of information for Automatic SQL tuning as well; pretty
> amazing stuff. They are finally moving down the path of having the database
> automatically tune all the sql that is being thrown at it. I say "finally"
> because they sold 10g as doing this, but the fine print spelled out that 10g
> really just lays the foundation to make automation possible. 11g actually
> implements it. What I consider to be a slightly downside, the automatic
> tuning seems to concentrate on SQL Profiles; while I think that is a
> considerable and effective solution, it is more like putting a band aid on
> the problem, while not fixing the root problem itself. If I recall
> correctly, the new tuning features will be analyzing all SQL statements out
> of the box, and can report on known issues (bad indexes, poorly written
> query, etc). Another aspect of this idea which I thought was a good decision
> is that even though the analytical part of the feature will happen all the
> time, the actual implementation part (ie, making the Profile "real") is
> purely optional. The tuned sql will be stored in AWR, and Robert Freeman
> mentioned that they will be retained for about a year. I find that hard to
> believe, but will be interested in seeing the real thing.
> [...]
>

Along the same lines, there will be an option for SQL Performance Analyzer,
> which works very similarly to Replay, but replays everything linearly, with
> no concurrency. I am confused why this is needed if you are already using
> the Automatic SQL tuner; perhaps to benchmark queries that the auto-tuner
> cannot further tune? I will have to research that a little more.
> [...]
>

Along with the Automagically tuned SQL, Oracle is finally giving us much
> better Plan Management. It is actually this feature that allows plans to be
> stored for a year or so in the AWR. Features support Plan Baseline and
> subsequent comparisons, which ties into the Auto Tuner; apparently the
> auto-tuner will test all new plans and actually verify they are better. Of
> course, we have all been bit by "better" low-cost plans that run slow than
> "worse" high-cost plants. I am curious if that will be addressed. I do not
> want the CBO to globally discard plans simply because the cost is higher.
>

At the opening session, Dr. Ken Jacobs and Andy Mendelsohn called it "SQL Plan Change Control".

On 5/8/07, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
>
> Hi Brandon
>
> > I've filed enhancement request 6030306 to add a hint or some other
> > method of forcing Oracle to always hard-parse and bind-peek for
> > certain queries. If any of you agree this would be a good
> > enhancement, please submit an enhancement request of your own
> > and reference the above ER number and if enough of us ask for it,
> > hopefully they'll add it soon.
>
> FWIW a new plan stability feature that will be introduced in 11g should
> solve such problems. Sorry, I cannot provide more details because of
> NDA.
>
> More generally, IMHO, no hints should be provided for such problems. The
> query optimizer should be able to handle them.
>
>
> Cheers,
> Chris
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l