This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 13, 2007 10:25 AM by dbtoo RSS

System Statistics and optimizer_index_cost_adjust  interaction in 9i

426411 Oracle ACE
Currently Being Moderated
If we are using systems statistics in 9i, is the setting for optimizer_index_cost_adjust completely ignored?

Ditto for optimizer_index_caching. All of my research indicates the system statistics override these setting, but Jonathan Lewis' indicates otherwise in

http://www.jlcomp.demon.co.uk/system_stats.html

I've seen no other reference to these parameters altering the effect of system statistics.
  • 1. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    damorgan Oracle ACE Director
    Currently Being Moderated
    No surprise: Jonathan is correct.
  • 2. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    I've just re-run a little test case against 10.2.0.3 - here's a direct cut-and-paste from the screen:
    SQL> set autotrace traceonly explain
    SQL> select count(n2) from t1 where n1 = 16;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 269862921
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |       |     1 |     8 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("N1"=16)
    
    SQL> alter session set optimizer_index_cost_adj = 50;
    
    Session altered.
    
    SQL> select count(n2) from t1 where n1 = 16;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 269862921
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |       |     1 |     8 |     1   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     1   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("N1"=16)
    
    SQL> spool off
    As you can see from the time column, CPU costing is enabled - and when I change the optimizer_index_cost_adj from the default (100) to 50 the cost of the indexed access path changes from 2 to 1 - as expected.

    If your research was based on test cases(rather than reading other articles) then please post a couple - it's always possible that there are special cases where the optimizer behaves in unexpected ways.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 3. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    426411 Oracle ACE
    Currently Being Moderated
    My research is reading only so far and talking to everybody at the last Collab in Vegas.

    The company I've just joined is running 9i, an OLTP environment, is not running system statistics and is using the default values for optimizer_index_cost_adjust and optimizer_index_caching. (It's amazing how common this scenario is).

    Not surprisingly, CBO is selecting FTS when it should be using indexes. (I hinted one really bad query with INDEX hints and the resulting cost was 2x the original plan with an particularly ugly HASH JOIN/FTS, even though the hinted query runs in a tiny fraction of the original).

    In most cases, there are in fact multiple indexes that can/should be used, and that seems to be where the CBO picks FTS.

    I get my 1st shot at testing on Monday, but it's not clear how much time I'll get.

    If I interpret my reading correctly ("The default value is 100, which means that indexes are evaluated as an access path based on the normal costing model"), if optimizer_index_cost_adjust = 100, then this parameter results in CBO using system statistics in an 'unbiased fashion'. Is that correct?

    Also, what effect, if any, does changing optimizer_index_caching have if system statistics are being used?

    From what I've read, at least initially, I should leave both of these parameters at their default values. Is that correct?

    FWIW, (and I may have missed it), your article is the only place I've seen that mention any effect this (parameter(s) has when system statistics are being used. If it was covered in your CBO Fundamental book, I apologize for missing it, and Kytes book implies they are ignored by CBO if system statistics are being used.

    TIA!
  • 4. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    There's just a little extra here
    http://jonathanlewis.wordpress.com/2006/10/24/optimizer_index_cost_adj/
    including a comment that I forget to mention the combined effect of system stats and these two parameters in the book.

    Optimizer_index_caching can be interpreted in the same way whether or not you enable system stats. Effectively it tells the optimizer what percentage of single block accesses to branch blocks should be "ignored" when doing nested loop joins or in-list iterators. For OLTP systems that use these operations frequently it is often appropriate to have values in the range of 80% to 90% (and occasionally higher) for this parameter whether or not system statistics are enabled. (Default value zero).

    optimizer_index_cost_adj (default value 100). In the absence of system statistics one interpretation of this number is "what percentage of the TIME of a multiblock read does a single block read take"; in which case, for an OLTP systems, a value in the range of 25 - 30 percent is often appropriate.

    If system statistics are enabled that TIME effect is already catered for in the mreadtim, sreadtim, mbrc; so a more realistic interpretation of the number is "the percentage of single block reads due to an indexed access path that are likely to turn into real disc reads", in which case, for an OLTP system, a value in the region of 90 is more likely to be appropriate.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 5. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    There's just a little extra here
    http://jonathanlewis.wordpress.com/2006/10/24/optimize
    r_index_cost_adj/
    including a comment that I forget to mention the
    combined effect of system stats and these two
    parameters in the book.
    Speaking of which - how are books 2 and 3 coming along? Alternately, since book 1 was based on early 10g and older, will we see a Book 1 R2?

    Not that I need them - I'm still learning plenty from book 1. For which I thank you .

    /Hans
  • 6. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    601262 Explorer
    Currently Being Moderated
    The company I've just joined is running 9i, an OLTP environment, is not running system statistics and is
    using the default values for optimizer_index_cost_adjust and
    optimizer_index_caching. (It's amazing how common this scenario is).
    The reason this is so common is (almost always) setting init.ora parameters is not the way to solve suboptimal execution plans. Especially when you do not have any root cause analysis done. Personally I never use system stats and many feel they can cause more challenges then they solve.
    Not surprisingly, CBO is selecting FTS when it should be using indexes.
    Just curious why you feel this isn't a surprise. Why is it the Optimizer choosing a FTS vs. an index? Perhaps it is costing is less? Should it be less? Perhaps not, but maybe the current stats on the table are not representative of the data and thus causing the Optimizer to make poor choices. Before I'd be critical of what execution plans are being chosen, make sure the stats, especially the NDV, are representative of the data. If the stats are not representative, the chances of the Optimizer generating a optimal plan are low. This also could be due to bind peeking on a very popular value when histograms exist.

    I'd strongly recommend checking the cardinality estimates for the row sources and see if they are "in the ball park". If not, then you probably need to check the stats.

    You may find this presentation of use:
    http://download.oracle.com/oowsf2005/961.pdf

    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 7. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    426411 Oracle ACE
    Currently Being Moderated
    Jonathan,

    Thanks! HUGE help!

    According to the documentation (at least 9i), the value of optimizer_index_cost_adj
    is 0 to 10,000 (yep, 10K).

    Would any value > 100 make sense in the real world?
  • 8. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    426411 Oracle ACE
    Currently Being Moderated
    Greg,

    It's "not a surprise" because we have OPTIMIZER_INDEX_CACHING = 0 and optimizer_index_cost_adj =100 and no system statistics. The CBO does consider the cost of the FTS. (And we're still on 9i).

    FWIW, I'm not trying to tune an individual query, but a whole class of application generated queries. One of those 3-table object/attribute/reference generic structures the OO folks are so fond of.

    These queries have been a performance problem for quite some time. Just before I joined, they split the one massive structure into a bunch of equivalent structures, e.g. instead of one giant object table, we now have specific object/attribute/reference structures.

    The queries generated are often identical except for the table names, but some of the tables are still pretty large,

    It is quite possible that the table stats have not been created properly, which is also something I will investigate.

    But this is an OLTP system, so getting the system wide inputs "right" is also pretty important.

    Thanks for the pointer to your presentation!
  • 9. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    601262 Explorer
    Currently Being Moderated
    I just have a concern when people choose to twiddle with optimizer_index_cost_adj and optimizer_index_caching. IMHO those are the two most abused parameters. In almost all cases, the defaults should be fine. I understand you have a "group" of queries that are displaying the same symptoms, but making a global change to remove the symptom of a local issue isn't probably the best way. I suspect the root cause is the NDV may be off, and this query should help diagnose that. I would recommend to investigate this first, before you change any parameters or gather system stats.

    This is the query from slides.
    -- Note: set these before running the original SQL so all stats get populated:
    alter session set statistics_level=all;
    alter session set timed_statistics = true;
    
    -- run your SQL here
    
    SELECT  SUBSTR(lpad(' ',depth-1)||operation||' '||object_name,1,40) operation,
            object_name,
            cardinality,
            last_output_rows,
            last_cr_buffer_gets
    FROM    v$sql_plan_statistics_all p,
            (SELECT *
            FROM
                    (SELECT *
                    FROM    v$sql
                    WHERE   sql_text like '%<some identifying text string>%'
                        AND sql_text not like '%v$sql%'
                        AND PARSING_USER_ID = SYS_CONTEXT ('USERENV', 'CURRENT_USERID')
                    ORDER BY last_load_time desc
                    )
            WHERE   rownum <2
            ) s
    WHERE   s.hash_value   = p.hash_value
        AND s.child_number = p.child_number
    ORDER BY id
    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 10. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    426411 Oracle ACE
    Currently Being Moderated
    We ran our 1st test today, only enabling system statistics. Did not touch the init.ora parameters.

    We achieved a reduction in end to end time of 86-89%, depending on configuration for the load test we ran. (1332 seconds reduced to 142 seconds).

    Thanks everybody!
  • 11. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    426411 Oracle ACE
    Currently Being Moderated
    When we import the object stats (from production) the end to end time improvement was reduced to about 25%, because the test that did not use system statistics improved.

    However, statspack analysis show much more dramatic improvement than the end to end time (which is using a Java based SLAMD scripts :-)
  • 12. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    426411 Oracle ACE
    Currently Being Moderated
    Wrapping this up:

    We also ran with OPTIMIZER_INDEX_CACHING = 90 and ...COST_ADJ = 30,
    both with and without system statistics.

    The differences were negligible.

    In summary, using system statistics clearly lead us to dramatic performance improvement.

    For risk reduction reasons, i.e. not needing to bounce the DB, we are going to leave the init.ora parameters alone, as they are not dynamic, but put system statistics into production.

    Again, thanks to everyone!
  • 13. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Greg,

    I agree - optimizer_index_cost_adj and optimizer_index_caching are a pair of the most abused parameters around.

    But I have to disagree with the statement that the defaults should be fine. Until 9i introduced a way of getting a time component into single and multiblock read costs, the optimizer_index_cost_adj was the only way to fake the balance; and the optimizer almost always put too high a cost on indexed nested loop joins into large tables - and the optimizer_index_caching was (and still is) a sensible way to address that issue.

    Of course, for OLTP systems, the other badly used (i.e. not quite abused) parameter was the db_file_multiblock_read_count - and abusing that one made it almost necessary to use (and abuse) the optimizer_index_cost_adj: so I'd add that parameter to the list of most-suspect parameters.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 14. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    426411 Oracle ACE
    Currently Being Moderated
    Jonathan,

    So, in general, if systems statistics are in effect, would you or would you not make any adjustments to the optimizer... parameters? Under what circumstance?

    FWIW, our decision to not adjust them was "risk" due to need to bounce database to change them; they are not dynamic in in 9i.

    (And so far I'm insanely happy with the results of system statistics :-)
1 2 Previous Next