Re: Best course to understand why a bad plan is chosen by optimizer

Cost of a SQL statement is the amount of IO that the instance will perform to execute the SQL. Costs on the same system can be compared but the comparison is often pointless because the statistics is frequently wrong. That is the basis for cardinality feedback approach to optimizations.

On 9/23/19 8:30 PM, Chris Taylor wrote:
> Then we get I to the whole "what does COST even mean" when costing the
> same query but yet costs can't be compared.
>
> I honestly think [sometimes?  Often?] that Oracle has 'lost the plot'
> when it comes to the CBO.
>
> Chris
>
>
>
> On Mon, Sep 23, 2019, 7:08 PM Clay Jackson (cjackson)
> <Clay.Jackson_at_quest.com <mailto:Clay.Jackson_at_quest.com>> wrote:
>
> As usual, Mark "hit the nail on the head!".  If all Cost
> Accountants thought and acted as he proposes, I would
> wholeheartedly agree.   Unfortunately, reality and human nature
> often intrude, and I think THAT's where the issues come in.  Far
> too many times, I've seen decisions made on the basis of "least
> hard dollar cost", w/o considering the "hidden" or "soft" costs.
>
> One great example of that, WITHOUT starting a whole discussion on
> "outsourcing" was the decision a former employer of mine made to
> outsource "Account Provisioning" and "Security".
>
> They told the displaced employees, "You have six months to train
> your replacements REMOTELY" (i.e., they did NOT spend the money to
> bring the "new" employees from the "low cost location" (the
> specific location, other than the fact that it was 8 timezones
> away, is really irrelevant) to the location of the current
> employees or vice-versa).   When they executed the switch, the
> time to provision a new account went from 6 hours to 3 days, as
> the new folks learned all of "tribal knowledge" "the hard way"';
> and the error rate went from less than 1% rework to close to 75%
> rework.
>
> Clay Jackson
> Database Solutions Sales Engineer
> clay.jackson_at_quest.com <mailto:clay.jackson_at_quest.com>
> office  949-754-1203  mobile 425-802-9603
>
>
> -----Original Message-----
> From: Mark W. Farnham <mwf_at_rsiz.com <mailto:mwf_at_rsiz.com>>
> Sent: Monday, September 23, 2019 2:35 PM
> To: 'Mladen Gogala' <gogala.mladen_at_gmail.com
> <mailto:gogala.mladen_at_gmail.com>>; Clay Jackson (cjackson)
> <Clay.Jackson_at_quest.com <mailto:Clay.Jackson_at_quest.com>>;
> oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> Subject: RE: Best course to understand why a bad plan is chosen by
> optimizer
>
> CAUTION: This email originated from outside of the organization.
> Do not follow guidance, click links, or open attachments unless
> you recognize the sender and know the content is safe.
>
>
> Blush (okay, I don't really do that, but, thanks.)
>
> Clay: Permission for re-use granted. I at least think that was
> original to me.
>
> Mladen, likewise, and further I suspect that *mostly* when we
> disagree we're looking at different pieces of the elephant.
>
> As for cost accounting the only thing I'm sure about is that it
> would be done better if we didn't tax corporations and have to
> legally mesh those views of the books.
> But I don't want to get into an argument about how corporate taxes
> are paid by customers on this technical forum, so I'll drop that
> without further wind.
>
> Of course meshing strategic provisioning with cost accounting as a
> check on predictions is a useful component of achieving the
> business goal of computer systems: 1) Reliable and quick enough
> most of the time to meet service delivery promises to customers,
> safe and recoverable enough to provide transaction integrity
> (which can be quite expensive to lose), and all done reasonably
> close to the minimum cost (where reasonably is where it would have
> cost more to plan better to reach a lower cost than the difference.)
>
> Experience guides toward erring a bit on the side of extra
> engineering to avoid expensively retooling too much too often. I
> think that newfangled method calls that "re-work." Experience also
> guides toward getting started sometime well before the last
> possible outcome is modelled and tested in a simulator (which is
> probably only justified rarely.)
>
> I'll take both of you as my allies if ever the opportunity crops
> up. And Lothar, too.
>
> mwf
>
> -----Original Message-----
> From: Mladen Gogala [mailto:gogala.mladen_at_gmail.com
> <mailto:gogala.mladen_at_gmail.com>]
> Sent: Monday, September 23, 2019 2:45 PM
> To: Clay Jackson (cjackson); mwf_at_rsiz.com <mailto:mwf_at_rsiz.com>;
> oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> Subject: Re: Best course to understand why a bad plan is chosen by
> optimizer
>
> Hi Clay!
>
> Comments in line:
>
> On 9/23/19 1:19 PM, Clay Jackson (cjackson) wrote:
> > I've known Mark for a LONG time, and have ALWAYS been impressed
> with his elegant "turn of phrase"!
>
> I have never met Mark in my life and I am deeply sorry about it. I
> am following his contributions on various Oracle forums with a
> sort of fascination.  I respect hist opinion deeply, even when I
> disagree with him.
>
>
> >
> >
> > I'm  going to save this; and, with permission of course, may use
> it a few talks (I'm doing one in Dallas in a few weeks on "The
> Future of the DBA in a World of Autonomous Databases").
>
> Databases are extremely complex, even if autonomous. I don't
> envision being able to utilize a serious business database without
> having a DBA anytime soon. Also, databases are getting larger.
> Backup and recovery of
> 50+ TB database is not as simple as it may look. Look at the databases
> as modern airplanes. No robot can do what Sully Sullenberger has done.
> Auto-pilot is available for the long time but I would be very
> hesitant to fly an airplane which doesn't have a pilot on board.
>
> >
> > I think sometimes we forget the "Engineering" ; and I'm 100%
> certain my mother was correct, when, in 1970, she predicted "Cost
> Accounting will be the ruin of civilization".
>
> With all due respect, I disagree with your mother. Cost accounting
> is a necessary evil. You can call me crazy, but I prefer the money
> to be spent on my bonus over buying unnecessary software. My bonus
> is never unnecessary.
>
>
> >
> > Clay Jackson
> > Database Solutions Sales Engineer
> > clay.jackson_at_quest.com <mailto:clay.jackson_at_quest.com>
> > office  949-754-1203  mobile 425-802-9603
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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