Re: Bind variable peeking and Dynamic sampling
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: Christian.Antognini@xxxxxxxxxxxx
- Date: Tue, 8 May 2007 14:52:03 -0500
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@xxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
Charles Schultz
Other related posts:
