Re: System Statistics and the CBO

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:

1. 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).
2. 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 <USER_DUMP_DEST> 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 <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