Re: How to calibrate the CBO

Rick Denoire wrote:
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> >On the plus side, if you have been told of specific jobs,
> >you could enable 10046 level 8 on those jobs. and see
> >where most the time is going. There is a bit of a bug
> >in 9.2, though, which means that sql_trace can really
> >wreck performnace, so an initiall step might be to
> >do a snapshot of the session's wait times (through v$session_event)
> >and resource usage (through v$sesstat or v$mystat) before
> >you do anything more expensive. These may give you a
> >couple of cheap clues.
>
> Yes, I was aware of this bug, I think it didn't hit me here.
>
> >
> >> The developers see this as a DB Admin issue, they did not change the
> >> code and it ran well prior to the migration after all.
> >>
> >
> >Of course, there is the change to the 20M lines or so
> >of Oracle code - but that doesn't really count as a
> >code change ;)
>
> Well, people are so candid to assume that changes *must* be positive.
>
> >Look at the Row Source Operation outputs from tkprof,
> >and compare these with the theoretical plans from explain
> >plan - then check where the ROWS in the row source
> >operation diverges wildly from the CARDINALITY in
> >explain plan. In theory, the ROWS is the number of
> >rows that came out of a row source, and the CARDINALITY
> >is the predicted number of rows.
>
> I have examined quite a few of tkprof output files coping with this
> issue. I must admit that I still miss your point. The way from the
> numbers reported by tkprof to the solution is a little bit too long
> for me to comprehend.
>
> >One of the problems of upgrading is that Oracle fixes bugs in
> >the optimizer so that costs change. I have a query that has
> >a cost of:
> > 34 in 8.1.7.4
> > 34,034 in 9.2.0.4
> > 238 in 10.1.0.1
> >
> >The first is obviously wrong, the second looks as if it
> >is arithmetically correct for the way the plan is reported,
> >and the third is correct, given what actually happens.
>
> I won't dare to tell my coleagues: "Hey, your code was running well in
> 8i just because it was written so bad, now you can't take any
> advantage of the bugs". They will demand a proof.
>
> >There are options for adjusting some fairly significant assumptions
> >in the CBO's strategy - check www.evdbt.com for Tim Gorman's
> >article on optimizer_index_cost_adj (and optimizer_index_caching).
> >I think Richard Foote would agree that it is vastly better than Don
> >Burleson's article. (The title is something like "The search for
> >Intelligent life in the CBO")
>
> I played a bit with these parameters. They seemed to change cost
> calculations somewhat. But I would need to actually run the query in
> order to know.
>
> >Bear in mind that Oracle 9 uses 'system statistics', and I think
> >that the default behaviour in 9.2.0.4 is to invent some values
> >if none have been gathered. Check dbms_stats, in particular
> >the procedure gather_system_stats. This gives the CBO some
> >figures about machine resource costs and timings, and allows
> >it to generate more appropriate execution paths for the current
> >working environment.
>
> I was faster than your advice. I have been working hard on this issue
> and came to the following:
>
> 1) optimizer_max_permutations changed from 80000 (8i) to 2000 (9i). In
> view of the number of tables involved and the complexity of the query,
> a much larger number of combinations is possible and the CBO perhaps
> hit the top of 2000 before considering the optimal one.
>
> 2) I read some papers about the new features of the CBO in 9i and
> realized that system statistics were missing. They are being gathered
> now. Surprisingly, the execution times reported in the tkprof output
> are enormous since then; they seem to be reported in microseconds now
> (why?).
>
> By the way, according to my modest knowledge, if system statistics are
> missing, the CBO does not invent substitute values but falls back to
> the 8i formula (which is a very simplistic one).
>
> 3) I considered gathering histograms but found some warnings that
> doing so could adversely affect performance. I will have to
> consolidate my knowledge on this point yet. At least in 9i, using
> method_opt=>'for all columns skewonly' should allow Oracle to take the
> decision by itself.
>
> 4) I changed the dynamic_sampling system setting to a value of 3, just
> in case. As a result, the queries suddenly could not be *parsed* any
> more (namely the analyzing statement, as shown by tkprof). Now, every
> time the query is tarted, tkprof shows some statistics gathering
> activity as a *first* step. This strikes me a bit, since all relevant
> objects are actually analyzed. I takes only a couple of seconds to
> execute, anyway. I had to flush the shared_pool to solve the parse
> problem.
>
> 5) I changed the session parameter cursor_sharing to "similar" (was
> "force"). The CBO should be allowed some freedom to reconsider a new
> execution plan when something but the SQL code itself changes.
>
> Unfortunately, the query (which is rather a stored procedure composed
> of several complex statements) can't be run completely to the end now.
> I get the error "resource required with nowait" or something, and I
> suspect that some OEM session from an office PC keeps one table
> locked, while I am sitting at home now. But a small portion of the
> procedure that was actually executed runs much faster now...
>
> 6) I have set "use_stored_outline=false" for the time being, otherwise
> all other changes would be ignored.
>
> 7) The most valuable piece of information seems to be that I got hit
> by some kind of misbehavior of Oracle 9i related to the use of the
> parameters pga_aggregate_target and workarea_size_policy=auto.
> According to other reports about a similar situation when migrating
> from 8i to 9i, if disabling those parameters and setting
> sort_area_size manually, the problem disappears.
>
> Unfortunately, this can't be done without recycling the instance, and
> I will have to announce and prepare this step (the DB is used round
> the clock).
>
> 8) One more thought of mine: If nothing helps, setting
> compatible='8.1.7' could help me out until the situation clarifies.
>
> 9) If time permits, I will give the "Outline Manager" delivered with
> the OEM a chance. One can edit the execution plan by changing the
> access paths and join type!
>
> Sorry for the lengthy reply.
>
> Thanks a lot
> Rick Denoire

With regard to your pga_... comments, what have you got them currently set to?

Cheers
Connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"