FAQ
I am just now getting around to implementing system statistics in my 9.2.0.6 database that runs on an IBM P650 under AIX 5L.

I have found a handful of explain plan differences with and without system statistics and am trying to understand why the plan changes when I import the system statistics. Can someone explain to my why, once I import my system statistics, the CBO chooses a index that has a higher cardinality (X01CLAIM_FORM instead of X04CLAIM_FORM) for the following query:

System Stats:

PNAME PVAL1
------------------------------ ----------
CPUSPEED 508
MAXTHR 14344192
MBRC 4
MREADTIM .672
SLAVETHR -1
SREADTIM 3.244

Without system stats:

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=97)
   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=1 Card=1 Bytes=28)

2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=97)
3 2 NESTED LOOPS (Cost=2 Card=1 Bytes=69)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=1 Card=1 Bytes=39)

5 4 INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)

6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=1 Card=1 Bytes=30)
7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=1 Card=1)
8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=1 Card=1)

With system stats:

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=97)
   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=2 Card=1 Bytes=28)

2 1 NESTED LOOPS (Cost=4 Card=1 Bytes=97)
3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=69)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=2 Card=1 Bytes=39)

5 4 INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)

6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=2 Card=1 Bytes=30)
7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=2 Card=1)
8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=2 Card=1)

Thanks,

Patty

This e-mail is confidential, intended solely for the use of the
recipient(s) to whom it was addressed. If you have received it
in error, please do not copy or distribute this e-mail. We ask
that you notify us immediately by replying to the sender and
then delete this e-mail. E-mail sent or received over the
internet may not be secure. You should use caution when sending
e-mail messages containing private and confidential information
or consider other secure means to send the information. If you
have any questions regarding the authenticity or security of
e-mail you have received from Green Shield Canada please do not
hesitate to contact us at 1-800-265-5615.

Please visit us at our website: http://www.greenshield.ca

Ce message de courriel est confidentiel et s’adresse uniquement
à la personne ou à l’organisme indiqué(e). Si vous l’avez reçu
par erreur, veuillez ne pas copier ni distribuer ce message de
courriel. Nous vous demandons de nous aviser immédiatement en
répondant à l’expéditeur, puis en supprimant ce message. Les
messages envoyés ou reçus par courriel pourraient ne pas être
sécurisés. Vous devriez être prudent lorsque vous envoyez des
messages de courriel contenant des renseignements confidentiels
et privés ou songer à prendre d’autres moyens sécurisés pour
envoyer les renseignements. Si vous avez des questions concernant
l’authenticité ou la sécurité d’un courriel que vous avez reçu de
Green Shield Canada, n’hésitez pas à communiquer avec nous au
1-800-265-5615.

Visitez notre site Web: http://www.greenshield.ca

Search Discussions

  • Mladen Gogala at Jun 17, 2005 at 8:59 am

    On 06/17/2005 08:29:56 AM, Patty.Charlebois_at_greenshield.ca wrote:
    I am just now getting around to implementing system statistics in my 9.2.0.6 database that runs on an IBM P650 under AIX 5L.

    I have found a handful of explain plan differences with and without system statistics and am trying to understand why the plan changes when I import the system statistics. Can someone explain to my why, once I import my system statistics, the CBO chooses a index that has a higher cardinality (X01CLAIM_FORM instead of X04CLAIM_FORM) for the following query:
    The difference in the plans is choice of single index:

    INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)

    vs.

    INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)

    To have any idea why is that, I should know the default values for your system, which I don't.
    Because of larger cardinality, I somehow think that the latter has larger MBRC, which makes
    multiblock reads cheap enough rot the CBO to choose the latter plan. Have you ever tried gathering
    system statistics? Importing it doesn't seem like a very good idea to me, because system stats
    are supposed to calibrate your system. If you import system stats, you are using a calibration
    from another system.
  • Wolfgang Breitling at Jun 17, 2005 at 9:37 am
    Patty,

    are you sure that is the only thing that changed? From all I know, your
    system statistics are ignored by the CBO because mreadtim < sreadtim.
    That is at odds with the CBO's ground rules that scattered reads have to
    be more "expensive" than sequential reads.

    Of course, cardinality is only one aspect of an index' statistics

    Patty.Charlebois_at_greenshield.ca wrote:
    I am just now getting around to implementing system statistics in my 9.2.0.6 database that runs on an IBM P650 under AIX 5L.

    I have found a handful of explain plan differences with and without system statistics and am trying to understand why the plan changes when I import the system statistics. Can someone explain to my why, once I import my system statistics, the CBO chooses a index that has a higher cardinality (X01CLAIM_FORM instead of X04CLAIM_FORM) for the following query:

    System Stats:

    PNAME PVAL1
    ------------------------------ ----------
    CPUSPEED 508
    MAXTHR 14344192
    MBRC 4
    MREADTIM .672
    SLAVETHR -1
    SREADTIM 3.244
    Without system stats:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=97)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=1 Card=1 Bytes=28)
    2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=97)
    3 2 NESTED LOOPS (Cost=2 Card=1 Bytes=69)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=1 Card=1 Bytes=39)

    5 4 INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)

    6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=1 Card=1 Bytes=30)
    7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=1 Card=1)
    8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=1 Card=1)

    With system stats:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=97)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=2 Card=1 Bytes=28)
    2 1 NESTED LOOPS (Cost=4 Card=1 Bytes=97)
    3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=69)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=2 Card=1 Bytes=39)

    5 4 INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)

    6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=2 Card=1 Bytes=30)
    7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=2 Card=1)
    8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=2 Card=1)

    Thanks,

    Patty
    --
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Mladen Gogala at Jun 17, 2005 at 10:48 am

    Wolfgang Breitling wrote:

    Patty,

    are you sure that is the only thing that changed? From all I know,
    your system statistics are ignored by the CBO because mreadtim <
    sreadtim. That is at odds with the CBO's ground rules that scattered
    reads have to be more "expensive" than sequential reads.

    Of course, cardinality is only one aspect of an index' statistics
    Wolfgang, when I do SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) it tells me
    that "CPU costing is ON, despite
    the fact that I fiddled with sys.aux_stats$ and changed sreadtime to be
    mreadtime+1. Is DBMS_XPLAN lying to me? The
    version is 10.1.0.4 on Linux.

    --
    Mladen Gogala
    Oracle DBA
    Ext. 121

    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfgang Breitling at Jun 17, 2005 at 11:14 am
    Mladen,

    That's something I was wondering myself. My statement below may very
    well be a bit too broad. I should rephrase it to
    "From all I know, the CBO reverts back to the prior IO costing model if
    mreadtim < sreadtim".
    The cpu costing part of the new CBO cost algorithm may still be used.
    On the other hand I am not sure how much trust to put into what
    DBMS_XPLAN.DISPLAY is telling me. I have had cases where suddenly it
    reported CPU cost percentages without system stats being present.
    Finally, my experiences are mostly with 9iR2 and things like that could
    work slightly, or not so slightly, differently in 10g.

    Mladen Gogala wrote:
    Wolfgang Breitling wrote: >>
    Wolfgang, when I do SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) it tells me
    that "CPU costing is ON, despite
    the fact that I fiddled with sys.aux_stats$ and changed sreadtime to be
    mreadtime+1. Is DBMS_XPLAN lying to me? The
    version is 10.1.0.4 on Linux.
    --
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Karen Morton at Jun 17, 2005 at 1:15 pm
    If I really don't want to see the CPU cost stuff in explain plan output or if I want the optimizer to ignore system stats, I've been playing around with changing the _optimizer_cost_model parameter to IO vs. choose. I'm still trying to understand all the implications of how the optimizer "choose"s cpu vs. io in its decision making but if you just want to see how the optimizer acts with and without considering system stats, it's worth a test or two to set the parameter to io and test the query and then set it to cpu (or choose) and test again...then compare and see if you learn anything. I know messing with hidden parameters isn't your long term answer, but it may give you a good way to test differences in behavior.


    If the optimizer is reverting back to old IO costing model when mreadtim < sreadtim, you should be able to confirm this if you influence the cost model parameter.


    Just a thought....



    Karen Morton
    Hotsos Enterprises, Ltd.
    www.hotsos.com



    Original Message -----
    From: Wolfgang Breitling
    Sent: Fri Jun 17 10:10:08 CDT 2005
    To: Mladen Gogala
    Cc: Patty.Charlebois_at_greenshield.ca; oracle-l_at_freelists.org
    Subject: Re: System Statistics and the CBO

    Mladen,

    That's something I was wondering myself. My statement below may very
    well be a bit too broad. I should rephrase it to
    "From all I know, the CBO reverts back to the prior IO costing model if
    mreadtim < sreadtim".
    The cpu costing part of the new CBO cost algorithm may still be used.
    On the other hand I am not sure how much trust to put into what
    DBMS_XPLAN.DISPLAY is telling me. I have had cases where suddenly it
    reported CPU cost percentages without system stats being present.
    Finally, my experiences are mostly with 9iR2 and things like that could
    work slightly, or not so slightly, differently in 10g.

    Mladen Gogala wrote:
    Wolfgang Breitling wrote: >>
    Wolfgang, when I do SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) it tells me
    that "CPU costing is ON, despite
    the fact that I fiddled with sys.aux_stats$ and changed sreadtime to be
    mreadtime+1. Is DBMS_XPLAN lying to me? The
    version is 10.1.0.4 on Linux.
    --
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Patty.Charlebois_at_greenshield.ca at Jun 17, 2005 at 9:57 am
    I gathered system statistics over during a typical day and stored them in a table as I do not want them 'turned on' until I have done some testing and can ensure that they will not negatively impact our production OLTP system.

    I then imported the system statistics that I gathered from production into our test system and compared plans before and after the system stats were imported. The production and test server are the exact same hardware and database.

    Where would I find the default values for our system?

    Mladen Gogala Sent by: oracle-l-bounce_at_freelists.org
    2005/06/17 08:52 AM
    Please respond to
    gogala_at_sbcglobal.net

    To
    Patty.Charlebois_at_greenshield.ca
    cc
    oracle-l_at_freelists.org
    Subject
    Re: System Statistics and the CBO
    On 06/17/2005 08:29:56 AM, Patty.Charlebois_at_greenshield.ca wrote:
    I am just now getting around to implementing system statistics in my 9.2.0.6 database that runs on an IBM P650 under AIX 5L. > > I have found a handful of explain plan differences with and without system statistics and am trying to understand why the plan changes when I import the system statistics. Can someone explain to my why, once I import my system statistics, the CBO chooses a index that has a higher cardinality (X01CLAIM_FORM instead of X04CLAIM_FORM) for the following query:
    The difference in the plans is choice of single index:

     INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)

    vs.

     INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)

    To have any idea why is that, I should know the default values for your system, which I don't.
    Because of larger cardinality, I somehow think that the latter has larger MBRC, which makes
    multiblock reads cheap enough rot the CBO to choose the latter plan. Have you ever tried gathering system statistics? Importing it doesn't seem like a very good idea to me, because system stats
    are supposed to calibrate your system. If you import system stats, you are using a calibration
    from another system.

    Mladen Gogala
    Oracle DBA

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

    ----------------------------------------------------------

    This e-mail is confidential, intended solely for the use of the
    recipient(s) to whom it was addressed. If you have received it
    in error, please do not copy or distribute this e-mail. We ask
    that you notify us immediately by replying to the sender and
    then delete this e-mail. E-mail sent or received over the
    internet may not be secure. You should use caution when sending
    e-mail messages containing private and confidential information
    or consider other secure means to send the information. If you
    have any questions regarding the authenticity or security of
    e-mail you have received from Green Shield Canada please do not
    hesitate to contact us at 1-800-265-5615.

    Please visit us at our website: http://www.greenshield.ca

    ----------------------------------------------------------

    Ce message de courriel est confidentiel et s’adresse uniquement
    à la personne ou à l’organisme indiqué(e). Si vous l’avez reçu
    par erreur, veuillez ne pas copier ni distribuer ce message de
    courriel. Nous vous demandons de nous aviser immédiatement en
    répondant à l’expéditeur, puis en supprimant ce message. Les
    messages envoyés ou reçus par courriel pourraient ne pas être
    sécurisés. Vous devriez être prudent lorsque vous envoyez des
    messages de courriel contenant des renseignements confidentiels
    et privés ou songer à prendre d’autres moyens sécurisés pour
    envoyer les renseignements. Si vous avez des questions concernant
    l’authenticité ou la sécurité d’un courriel que vous avez reçu de
    Green Shield Canada, n’hésitez pas à communiquer avec nous au
    1-800-265-5615.

    Visitez notre site Web: http://www.greenshield.ca

    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfgang Breitling at Jun 17, 2005 at 10:16 am
    There are no default values for system statistics. If you want to enable
    cpu costing with - theoretically - zero impact on IO costing you can
    modify your gathered system statistics and set
    MBRC =>
    SREADTIM => 1

    MREADTIM =>
    MBRC MREADTIM
    8 1.21
    16 1.54
    32 1.95
    64 2.48

    For any MBRC value not in the table you can do one of three things:
    a) do a linear extrapolation
    b) plunk the numbers from the table above into excel, draw a chart, draw
    a power trendline, get the formula for the trendline and calculate your
    value. The number you get from this method likely will differ from that
    of a linear extrapolation only in the 3rd or 4th decimal.
    c) e-mail me and I look it up in my chart (obtained with method b from a
    few more than 4 measured values)
    Which reminds me that there is a 4th option - Do tests to discover what
    the number should be.

    Patty.Charlebois_at_greenshield.ca wrote:
    I gathered system statistics over during a typical day and stored them in a table as I do not want them 'turned on' until I have done some testing and can ensure that they will not negatively impact our production OLTP system.

    I then imported the system statistics that I gathered from production into our test system and compared plans before and after the system stats were imported. The production and test server are the exact same hardware and database.

    Where would I find the default values for our system?

    The difference in the plans is choice of single index:


    INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)

    vs.

    INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)


    To have any idea why is that, I should know the default values for your system, which I don't.
    Because of larger cardinality, I somehow think that the latter has larger MBRC, which makes
    multiblock reads cheap enough rot the CBO to choose the latter plan. Have you ever tried gathering system statistics? Importing it doesn't seem like a very good idea to me, because system stats
    are supposed to calibrate your system. If you import system stats, you are using a calibration
    from another system.
    --
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Patty.Charlebois_at_greenshield.ca at Jun 17, 2005 at 10:02 am
    Yes, that is the only thing that has changed. This is the test scenario:

    delete system statistics
    generate explain plan for query (uses x04claim_form)
    import system statistics that were gathered in production
    generate explain plan for query (uses x01claim_form)

    I have tried gathering system statistics several times during the past week and my stats always show mreadtim < sreadtim for our production system. Do you think this means that the CBO will always ignore the system statistics and therefore I shouldn't bother with them?

    Thanks for you help,

    Patty

    Wolfgang Breitling 2005/06/17 09:32 AM

    To
    Patty.Charlebois_at_greenshield.ca
    cc
    oracle-l_at_freelists.org
    Subject
    Re: System Statistics and the CBO

    Patty,

    are you sure that is the only thing that changed? From all I know, your system statistics are ignored by the CBO because mreadtim < sreadtim. That is at odds with the CBO's ground rules that scattered reads have to be more "expensive" than sequential reads.

    Of course, cardinality is only one aspect of an index' statistics

    Patty.Charlebois_at_greenshield.ca wrote:
    I am just now getting around to implementing system statistics in my 9.2.0.6 database that runs on an IBM P650 under AIX 5L. > > I have found a handful of explain plan differences with and without system statistics and am trying to understand why the plan changes when I import the system statistics. Can someone explain to my why, once I import my system statistics, the CBO chooses a index that has a higher cardinality (X01CLAIM_FORM instead of X04CLAIM_FORM) for the following query:
    System Stats:
    PNAME PVAL1
    ------------------------------ ----------
    CPUSPEED 508
    MAXTHR 14344192
    MBRC 4
    MREADTIM .672
    SLAVETHR -1
    SREADTIM 3.244
    Without system stats:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=97)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=1 Card=1 Bytes=28)
    2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=97)
    3 2 NESTED LOOPS (Cost=2 Card=1 Bytes=69)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=1 Card=1 Bytes=39)
    5 4 INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)
    6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=1 Card=1 Bytes=30)
    7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=1 Card=1)
    8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=1 Card=1)
    With system stats:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=97)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=2 Card=1 Bytes=28)
    2 1 NESTED LOOPS (Cost=4 Card=1 Bytes=97)
    3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=69)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=2 Card=1 Bytes=39)
    5 4 INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)
    6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=2 Card=1 Bytes=30)
    7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=2 Card=1)
    8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=2 Card=1)
    Thanks,
    Patty
    -- Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com

    This e-mail is confidential, intended solely for the use of the
    recipient(s) to whom it was addressed. If you have received it
    in error, please do not copy or distribute this e-mail. We ask
    that you notify us immediately by replying to the sender and
    then delete this e-mail. E-mail sent or received over the
    internet may not be secure. You should use caution when sending
    e-mail messages containing private and confidential information
    or consider other secure means to send the information. If you
    have any questions regarding the authenticity or security of
    e-mail you have received from Green Shield Canada please do not
    hesitate to contact us at 1-800-265-5615.

    Please visit us at our website: http://www.greenshield.ca

    Ce message de courriel est confidentiel et s’adresse uniquement
    à la personne ou à l’organisme indiqué(e). Si vous l’avez reçu
    par erreur, veuillez ne pas copier ni distribuer ce message de
    courriel. Nous vous demandons de nous aviser immédiatement en
    répondant à l’expéditeur, puis en supprimant ce message. Les
    messages envoyés ou reçus par courriel pourraient ne pas être
    sécurisés. Vous devriez être prudent lorsque vous envoyez des
    messages de courriel contenant des renseignements confidentiels
    et privés ou songer à prendre d’autres moyens sécurisés pour
    envoyer les renseignements. Si vous avez des questions concernant
    l’authenticité ou la sécurité d’un courriel que vous avez reçu de
    Green Shield Canada, n’hésitez pas à communiquer avec nous au
    1-800-265-5615.

    Visitez notre site Web: http://www.greenshield.ca
  • John Smiley at Jun 17, 2005 at 11:44 am
    Hi Patty,

    When you gather/import system statistics, you provide the CBO with
    more information about your system and allows it to use CPU costing as
    well as I/O costing. System stats should be gathered while the system
    is under the type of load you want to use the stats for. If you have
    differing loads (say transaction processing during the day and batch
    processing at night), you may want to have more than one set of system
    stats to fit the load.

    Your idea of importing the stats from production to your test
    environment was a good move. Many people don't think of this.

    There really isn't enough information in your post to answer your
    question definitively as to why the CBO chose a different index when
    system stats were provided. If you don't mind reading through a trace
    file, you can get a definitive answer by generating a 10053 trace,
    which will show you all of the execution plan choices the CBO
    considered and discarded as well as why it chose the plan it did.

    Here's how to generate the trace:

    Ensure that a PLAN_TABLE exists in the schema of the user that will
    be used to trace the query. If the PLAN_TABLE does not exist then it
    can be created by running the utlxplan.sql script which resides in the
    rdbms/admin under the Oracle home
    ($ORACLE_HOME/rdbms/admin/utlxplan.sql on unix systems).

    Connect to Oracle using SQL*Plus as the appropriate user and issue
    the following series of commands:

    SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

    Session altered.

    SQL> EXPLAIN PLAN FOR --SQL STATEMENT--;

    Explained.

    SQL> exit

    3. A trace file will be generated in the location.

    If you'd care to post the text of the SQL along with the index
    definitions and all relevant object stats, it will make identifying
    the cause for the change in plans easier to determine.

    John Smiley
    Technical Management Consultant
    TUSC, Inc.

    On 6/17/05, Patty.Charlebois_at_greenshield.ca
    wrote:
    I am just now getting around to implementing system statistics in my 9.2.0.6 database that runs on an IBM P650 under AIX 5L.

    I have found a handful of explain plan differences with and without system statistics and am trying to understand why the plan changes when I import the system statistics. Can someone explain to my why, once I import my system statistics, the CBO chooses a index that has a higher cardinality (X01CLAIM_FORM instead of X04CLAIM_FORM) for the following query:

    System Stats:

    PNAME PVAL1
    ------------------------------ ----------
    CPUSPEED 508
    MAXTHR 14344192
    MBRC 4
    MREADTIM .672
    SLAVETHR -1
    SREADTIM 3.244
    Without system stats:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=97)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=1 Card=1 Bytes=28)
    2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=97)
    3 2 NESTED LOOPS (Cost=2 Card=1 Bytes=69)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=1 Card=1 Bytes=39)

    5 4 INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)

    6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=1 Card=1 Bytes=30)
    7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=1 Card=1)
    8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=1 Card=1)

    With system stats:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=97)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CLM_DETAIL' (Cost=2 Card=1 Bytes=28)
    2 1 NESTED LOOPS (Cost=4 Card=1 Bytes=97)
    3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=69)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_FORM' (Cost=2 Card=1 Bytes=39)

    5 4 INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)

    6 3 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM' (Cost=2 Card=1 Bytes=30)
    7 6 INDEX (RANGE SCAN) OF 'XPKCLAIM' (UNIQUE) (Cost=2 Card=1)
    8 2 INDEX (RANGE SCAN) OF 'XPKCLM_DETAIL' (UNIQUE) (Cost=2 Card=1)

    Thanks,

    Patty








    ----------------------------------------------------------

    This e-mail is confidential, intended solely for the use of the
    recipient(s) to whom it was addressed. If you have received it
    in error, please do not copy or distribute this e-mail. We ask
    that you notify us immediately by replying to the sender and
    then delete this e-mail. E-mail sent or received over the
    internet may not be secure. You should use caution when sending
    e-mail messages containing private and confidential information
    or consider other secure means to send the information. If you
    have any questions regarding the authenticity or security of
    e-mail you have received from Green Shield Canada please do not
    hesitate to contact us at 1-800-265-5615.

    Please visit us at our website: http://www.greenshield.ca


    ----------------------------------------------------------

    Ce message de courriel est confidentiel et s'adresse uniquement
    à la personne ou à l'organisme indiqué(e). Si vous l'avez reçu
    par erreur, veuillez ne pas copier ni distribuer ce message de
    courriel. Nous vous demandons de nous aviser immédiatement en
    répondant à l'expéditeur, puis en supprimant ce message. Les
    messages envoyés ou reçus par courriel pourraient ne pas être
    sécurisés. Vous devriez être prudent lorsque vous envoyez des
    messages de courriel contenant des renseignements confidentiels
    et privés ou songer à prendre d'autres moyens sécurisés pour
    envoyer les renseignements. Si vous avez des questions concernant
    l'authenticité ou la sécurité d'un courriel que vous avez reçu de
    Green Shield Canada, n'hésitez pas à communiquer avec nous au
    1-800-265-5615.

    Visitez notre site Web: http://www.greenshield.ca

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Mladen Gogala at Jun 17, 2005 at 12:06 pm

    John Smiley wrote:
    Hi Patty,

    When you gather/import system statistics, you provide the CBO with
    more information about your system and allows it to use CPU costing as
    well as I/O costing. System stats should be gathered while the system
    is under the type of load you want to use the stats for. If you have
    differing loads (say transaction processing during the day and batch
    processing at night), you may want to have more than one set of system
    stats to fit the load.

    John, with all due respect, she was asking about the differences in the
    access path of a query, not how to
    generate optimizer trace. Second, optimizer trace does not show you the
    way that optimizer uses system
    statistics. The values from SYS.AUX_STATS$ are used to calculate the
    costs. Exactly how are they used
    is still not widely known and there is more then one unanswered
    question. Thanks for posting your information, though,
    I'm sure that many people on this group will find it very interesting.
    Something completely unrelated: I know that TUSC was distributing
    Oracle9i Metadata posters and there was
    a poster requesting something like that earlier today? Do you still have
    some?

    --
    Mladen Gogala
    Oracle DBA
    Ext. 121

    --
    http://www.freelists.org/webpage/oracle-l
  • John Smiley at Jun 17, 2005 at 1:03 pm
    Mladen,

    She was asking why the CBO chose a different index with system stats
    than without. By comparing the traces from each case, she will find
    the answer. A 10053 trace is a dump of the decision tree used by the
    CBO to arrive at an execution plan. It is an outstanding tool in
    gaining a better understanding of CBO behaviour.

    Patty,

    If you'll also provide the output from autotrace with statistics from
    each case, that will provide valuable information as well. In fact, a
    10046 level 12 trace for each case would be even better, but may be
    too large to send to the group. Feel free to send them directly to me
    if you like.

    John Smiley
    Technical Management Consultant
    TUSC, Inc
    On 6/17/05, Mladen Gogala wrote:
    John Smiley wrote:
    Hi Patty,

    When you gather/import system statistics, you provide the CBO with
    more information about your system and allows it to use CPU costing as
    well as I/O costing. System stats should be gathered while the system
    is under the type of load you want to use the stats for. If you have
    differing loads (say transaction processing during the day and batch
    processing at night), you may want to have more than one set of system
    stats to fit the load.

    John, with all due respect, she was asking about the differences in the
    access path of a query, not how to
    generate optimizer trace. Second, optimizer trace does not show you the
    way that optimizer uses system
    statistics. The values from SYS.AUX_STATS$ are used to calculate the
    costs. Exactly how are they used
    is still not widely known and there is more then one unanswered
    question. Thanks for posting your information, though,
    I'm sure that many people on this group will find it very interesting.
    Something completely unrelated: I know that TUSC was distributing
    Oracle9i Metadata posters and there was
    a poster requesting something like that earlier today? Do you still have
    some?

    --
    Mladen Gogala
    Oracle DBA
    Ext. 121

    --
    http://www.freelists.org/webpage/oracle-l
  • Wolfgang Breitling at Jun 17, 2005 at 1:38 pm

    John Smiley wrote:

    Mladen,

    She was asking why the CBO chose a different index with system stats
    than without. By comparing the traces from each case, she will find
    the answer. A 10053 trace is a dump of the decision tree used by the
    CBO to arrive at an execution plan. It is an outstanding tool in
    gaining a better understanding of CBO behaviour.

    Patty,

    If you'll also provide the output from autotrace with statistics from
    each case, that will provide valuable information as well. In fact, a
    10046 level 12 trace for each case would be even better, but may be
    too large to send to the group. Feel free to send them directly to me
    if you like.
    But a 10046 trace will not tell you WHY the optimizer chose one plan
    over another. It tells you where the time is spent executing the chosen
    plan.

    --
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 17, '05 at 8:35a
activeJun 17, '05 at 1:38p
posts13
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase