RE: optimizer parameters
- From: Martin Brown <martinfbrown@xxxxxxxxxxx>
- To: <mwf@xxxxxxxx>, <niall.litchfield@xxxxxxxxx>, <eglewis71@xxxxxxxxx>
- Date: Wed, 27 Apr 2011 11:29:34 -0400
To me, this is the Oracle topic with the most varied opinions and without a
definative answer. I use a "gather auto" method and monitor with a "list stale"
procedure I set up, thinking the "gather auto" would be sufficient to maintain
statistics with the least amount of process time. I watch execution plans
change as tables *DO NOT* go stale and I'm at a complete loss to figure out
why. I used the O_I_C and O_I_C_A briefly after watching the contents of V$BH
over a long period of time as Donnie B has recommended. But for my app, they
amounted to fly crap in the pepper shaker.
So I'm still trying to figure out why plans change when stats don't.
From: mwf@xxxxxxxx
To: niall.litchfield@xxxxxxxxx; eglewis71@xxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
Subject: RE: optimizer parameters
Date: Tue, 26 Apr 2011 08:38:43 -0400
That’s right.
And as it happened, more than one configurable off the shelf suite, and
especially including the average case implementation of Oracle’s own Ebiz at
the time, gained a lot more than it lost by using this shotgun. Since any
change to the underlying queries in these off the shelf packages by the
customer was a customization and the cycle time to get individual “performance
bugs” cycled through support was not conducive to a happy work environment, an
indiscriminate swipe across the whole database was a reasonable reaction. The
people who understood it was a win some lose some proposition, but in the case
of Ebiz actually a win most proposition were unfortunately quickly outnumbered
in internet presence by folks advising this particular magic bullet being used
in shotgun pellet form everywhere.
So the evidence of it working broadly for Ebiz and some other suites with very
few important plans being degraded indeed led to a phase of popularity of magic
bullets being applied out of scope. In context, knowing that a suite built by
folks who believed in heavy indexing on balance and rates of insert and update
that scaled within expectations at the time with time to finish month end
report dominated close processes being paramount, tilting plans broadly in
favor of index based plans without changing a lick of code made sense. Out of
context, where an appropriate narrow scope was available, it made no sense but
became popular anyway by dint of being shouted.
Tuning a “big knob” on a test system and measuring the outcome differential is
a reasonable thing to do if you’ve got a pandemic of bad plans. If it is an
overall win, super. That reduces the urgency and head count of remaining bad
plans to address at a smaller scope. If it is a loser, it takes almost nothing
to reverse the decision. But folks have correctly recognized that a shotgun
approach to a narrow problem on a generally healthy system is a risky game.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Niall Litchfield
Sent: Tuesday, April 26, 2011 7:41 AM
To: eglewis71@xxxxxxxxx
Cc: ORACLE-L
Subject: Re: optimizer parameters
Historically (8i and earlier) those two parameters were made available to alter
the cost of execution plans to make index access 'cheaper'. They did this,
broadly, by reducing the cost calculations for certain index access paths.
Prior to 9i this was pretty much the only way to make the CBO artificially
favour indexed plans. Whether this was smart or not is debateable, but it did
become popular (largely because it and other system wide tuning changes via
parameter setting tended to appear on page 1 of google searches at that time).
The introduction of system statistics (which can have similar effects but does
it by increasing calculated costs thus avoiding some of the rounding side
effects of the earlier approach) and the cpu costing model has arguably made
the earlier approach obsolete, but once an approach becomes popular it takes
forever to die!
On Tue, Apr 26, 2011 at 11:05 AM, ed lewis <eglewis71@xxxxxxxxx> wrote:
Hi Dave,
Yes, I can trace the query in both environments,
and compare the results.
I also doubt that the vendor will push to get these parameters implemented,
knowing that they will be accountable.
In the meantime, I plan to go forward, and handle each
sql query on an individual basis.
I'm still curious though, if anyone has modified, and implemented
these parameters. If you were installing a new application, would modifying
these parameters be a general practice, even if it was
not a vendor recommendation ?
thanks
ed
----- Original Message ----- From: "David Mann" <dmann99@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, April 25, 2011 11:06 AM
Subject: Re: optimizer parameters
>Stephane,
Thanks for you input.
I did change these parameters on the session level,
but it did not change the original plan for this
particular query. So even making these changes,
does not guarantee the desired or expected results.
As been mentioned, the focus should be on the
actual query.
Also, this particular query takes 14 seconds on prod,
but 6 seconds in dev. They both generate the same plan.
The environments also differ in data volume and user
activity. The vendor is questioning why the times are
not comparable.
So the plans are the same - I would let the vendor know what you found
- especially that their recommendation did not solve the issue. I
avoid "Big Knob" tuning on established systems as well. Unless the
client has a way to test the system and determine if any performance
got worse because of the changes.
In the meantime anything stopping you from tracing execution (with
WAIT info captured) on Dev and Prod to see differences in what the
query is spending its time on?
-Dave
--
Dave Mann
www.brainio.us
www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
