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

Yeah I guess I misspoke. Costs for the same identical query can be compared but when cost of a faster (i.e. better) plan is scored higher makes me think there is something screwy in the costing --- even when stats are current and done with histograms or no-histograms.

Chris

On Mon, Sep 23, 2019, 7:35 PM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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> 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
>> office 949-754-1203 mobile 425-802-9603
>>
>>
>> -----Original Message-----
>> From: Mark W. Farnham <mwf_at_rsiz.com>
>> Sent: Monday, September 23, 2019 2:35 PM
>> To: 'Mladen Gogala' <gogala.mladen_at_gmail.com>; Clay Jackson (cjackson) <
>> Clay.Jackson_at_quest.com>; 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]
>> Sent: Monday, September 23, 2019 2:45 PM
>> To: Clay Jackson (cjackson); mwf_at_rsiz.com; 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
>> > office 949-754-1203 mobile 425-802-9603
>>
>>
>> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

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