FAQ
I'm trying to improve my knowledge of tuning and the performance of my =
9i Ver 9.2.0.4 data warehouse by creating histograms on columns used in =
where clauses with skewed data values. I have extracted the columns =
included in where clauses, determined the distinct number of values and =
count using a "select distinct (value), count(*), group by statement" =
and think I have identified which are some good candidates for =
histograms. The documentation and articles about this issue I have read =
make it clear that histograms should only be used on columns being used, =
having skewed data. However, the dbms_stats.gather_schema_stats =
procedure with method_opt seems to create histograms whether the column =
is being used or the data is skewed. The 'gather_schema_stats' procedure =
is the only one demonstrated and seems to violate the rule. The =
'gather_table_stats" procedure seems like it would give you more =
selectivity. I'm having a little trouble with this topic. When I =
checked, there are values for all tables in dba_histograms although I =
have never run schema_stats with the method_opt set to anything but =
default. =20

Am I on the right track here? How do I use this feature without =
violating the 'rules?' I'm guessing if I run it and screw up my =
performance I can reset my histograms by rerunning dbms_stats with the =
default method_opt. Any advice useful or otherwise is welcome.

Thanks,

Don Freeman
Database Administrator 1
Pennsylvania Dept of Health
Bureau of Information Technology

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

Search Discussions

  • David at Jul 21, 2004 at 1:09 pm
    I believe there is a method_opt of skewonly, which will build histograms
    only where skewed data is found. That does'nt satisfy the check for
    column usage, but does take care of determining if the data is skewed or
    not. Note that I found there to be a pretty big overhead in terms of how
    long this process takes. Perhaps if you indexed the columns that are used
    and skewed and then specified to create buckets on indexed columns
    only....you may want to have indexes for other purposes other than driving
    this histogram analysis, but it's a DW, so maybe not...

    --
    ..
    David
    I'm trying to improve my knowledge of tuning and the performance of my 9i
    Ver 9.2.0.4 data warehouse by creating histograms on columns used in where
    clauses with skewed data values. I have extracted the columns included
    in where clauses, determined the distinct number of values and count using
    a "select distinct (value), count(*), group by statement" and think I have
    identified which are some good candidates for histograms. The
    documentation and articles about this issue I have read make it clear that
    histograms should only be used on columns being used, having skewed data.
    However, the dbms_stats.gather_schema_stats procedure with method_opt
    seems to create histograms whether the column is being used or the data is
    skewed. The 'gather_schema_stats' procedure is the only one demonstrated
    and seems to violate the rule. The 'gather_table_stats" procedure seems
    like it would give you more selectivity. I'm having a little trouble
    with this topic. When I checked, there are values for all tables in
    dba_histograms although I have never run schema_stats with the method_opt
    set to anything but default. =20

    Am I on the right track here? How do I use this feature without
    violating the 'rules?' I'm guessing if I run it and screw up my
    performance I can reset my histograms by rerunning dbms_stats with the
    default method_opt. Any advice useful or otherwise is welcome.

    Thanks,

    Don Freeman
    Database Administrator 1
    Pennsylvania Dept of Health
    Bureau of Information Technology


    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Juan Carlos Reyes Pacheco at Jul 21, 2004 at 1:12 pm
    Hi Donald I was in the same process, for the advanced tuning course I'm
    doing, t his is what I found, I hope be useful, but what I found, is that
    CBO is smarter (or bugged) and in more than one test I couldn't get to use
    the histogram.
    Any comment about this will be appreciated.
    From my new (and more serious ) paper, not like the previous
    1.A Histograms
    Histograms could affect nevatively performance, you have to verify they have
    a positive effect in your system.
    To create histograms you execute the following command, the size parameter
    specifies the number of buckets, depending of the amount of distinct values
    you give distinct value to bucket.
    EXEC DBMS_STATS.GATHER_TABLE_STATS ('scott','emp', METHOD_OPT => 'FOR
    COLUMNS SIZE 10 sal');
    You can view histogram information with the following views DBA_HISTOGRAMS,
    DBA_PART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS, DBA_TAB_COLSTATISTICS

    1.A.i Bucket Size
    Oracle divides the distinct value in bands, called buckets.
    From Documentation
    “If the number of frequently occurring distinct values in a column is
    relatively small, then set the number of buckets to be greater than that
    number. The default number of buckets for a histogram is 75, This valuesp
    rovides an appropiate level of detail for most data distributions however,
    because the number of buckets in the histograms, and the data distribution,
    bot affect a histogram’s usefulness, you might need to experiment with
    different number of buckets to obtain optimal results.”
    For example

    We will create a test table, gather statistics and create two groups one
    small of 3 records and other of 88713 records, we will run a test with and
    without histograms.
    create table test as

    select * from dba_objects UNION ALL
    select * from dba_objects UNION ALL
    select * from dba_objects;

    create index idxtest on test (OWNER);

    SQL> UPDATE TEST SET OWNER = 'XXX' WHERE NOT OWNER = 'CACHITO';
    SQL> COMMIT;
    SQL> SELECT OWNER,COUNT(*) FROM TEST GROUP BY OWNER;

    OWNER COUNT(*)

    ------------------------------ ----------
    CACHITO 3

    XXX 88713

    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ADM',TABNAME=>'TEST'

    CASCADE=>TRUE);

    SQL> SELECT * FROM TEST WHERE OWNER = 'CACHITO';

    Execution Plan

    SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=44358 Bytes=4036578)
    1 0
    TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=70 Card=44358 Bytes=4036578)
    2 1
    INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=93 Card=44358)
    Statistics

    0 recursive calls
    0 db block gets
    6 consistent gets
    0 physical reads
    0 redo size
    1213 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    3 rows processed
    SQL> SELECT * FROM TEST WHERE OWNER = 'XXX';

    88713 filas seleccionadas.
    Execution Plan

    SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=44358 Bytes=4036578)
    1 0
    TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=70 Card=44358 Bytes=4036578)
    2 1
    INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=93 Card=44358)
    Statistics

    0 recursive calls
    0 db block gets
    13132 consistent gets
    0 physical reads
    0 redo size
    4304202 bytes sent via SQL*Net to client
    65557 bytes received via SQL*Net from client
    5916 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    88713 rows processed
    SQL>

    SQL> SELECT NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS WHERE OWNER = 'ADM' AND

    TABLE_NAME = 'TEST' AND COLUMN_NAME = 'OWNER';

    NUM_BUCKETS

    1

    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('ADM','TEST', METHOD_OPT => 'FOR

    COLUMNS SIZE 75 OWNER');

    Procedimiento PL/SQL terminado correctamente.
    SQL> SELECT NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS WHERE OWNER = 'ADM' AND

    TABLE_NAME = 'TEST' AND COLUMN_NAME = 'OWNER';

    As you can see there is only one bucket, because there are too few values in
    the column.
    NUM_BUCKETS

    1

    Now we will try again
    SQL> SELECT * FROM TEST WHERE OWNER = 'CACHITO';

    Execution Plan

    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=273)
    1 0
    TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=3 Bytes=273)
    2 1
    INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=1 Card=3)
    Statistics

    0 recursive calls
    0 db block gets
    6 consistent gets
    0 physical reads
    0 redo size
    1213 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    3 rows processed

    SQL> SELECT * FROM TEST WHERE OWNER = 'XXX';

    Execution Plan

    SELECT STATEMENT Optimizer=CHOOSE (Cost=140 Card=88713 Bytes=8072883)
    1 0
    TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=140 Card=88713 Bytes=8072883)
    2 1
    INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=186 Card=88713)
    Statistics

    0 recursive calls
    0 db block gets
    13132 consistent gets
    0 physical reads
    0 redo size
    4304202 bytes sent via SQL*Net to client
    65557 bytes received via SQL*Net from client
    5916 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    88713 rows processed

    Maybe this is a error in the CBO, so we will force a full scan in the table

    SQL> SELECT /*+ FULL(TEST) */ * FROM TEST WHERE OWNER = 'XXX';

    88713 filas seleccionadas.
    Execution Plan

    SELECT STATEMENT Optimizer=CHOOSE (Cost=188 Card=88713 Bytes=8072883)
    1 0
    TABLE ACCESS (FULL) OF 'TEST' (Cost=188 Card=88713 Bytes=8072883)
    Statistics

    0 recursive calls
    0 db block gets
    7060 consistent gets
    0 physical reads
    0 redo size
    4304202 bytes sent via SQL*Net to client
    65557 bytes received via SQL*Net from client
    5916 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    88713 rows processed

    But it seems not to be, a full scan cost 40 more, so it seems this is not a
    good place to use histograms.-
    Meanwhile the CBO is becoming smarter, the more test one have to develop to
    verify the things one do, are really improving the performance.

    More about histograms you can read
    http://www.dba-oracle.com/art_otn_cbo_p4.htm

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Goulet, Dick at Jul 21, 2004 at 1:20 pm
    Wolfgang,

    Could you post the link to your wonderful paper/presentation of the CBO =
    fallacies? It's right to the point here.

    Dick Goulet
    Senior Oracle DBA
    Oracle Certified 8i DBA

    -----Original Message-----
    From: Juan Carlos Reyes Pacheco
    Sent: Wednesday, July 21, 2004 2:08 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Creating Histograms

    Hi Donald I was in the same process, for the advanced tuning course I'm
    doing, t his is what I found, I hope be useful, but what I found, is =
    that
    CBO is smarter (or bugged) and in more than one test I couldn't get to =
    use
    the histogram.
    Any comment about this will be appreciated.
    =20
    From my new (and more serious ) paper, not like the previous
    1.A Histograms
    Histograms could affect nevatively performance, you have to verify they =
    have
    a positive effect in your system.
    To create histograms you execute the following command, the size =
    parameter
    specifies the number of buckets, depending of the amount of distinct =
    values
    you give distinct value to bucket.
    EXEC DBMS_STATS.GATHER_TABLE_STATS ('scott','emp', METHOD_OPT =3D> 'FOR
    COLUMNS SIZE 10 sal');
    You can view histogram information with the following views =
    DBA_HISTOGRAMS,

    DBA_PART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS, DBA_TAB_COLSTATISTICS

    1.A.i Bucket Size
    Oracle divides the distinct value in bands, called buckets.
    From Documentation
    "If the number of frequently occurring distinct values in a column is
    relatively small, then set the number of buckets to be greater than that
    number. The default number of buckets for a histogram is 75, This =
    valuesp
    rovides an appropiate level of detail for most data distributions =
    however,
    because the number of buckets in the histograms, and the data =
    distribution,
    bot affect a histogram's usefulness, you might need to experiment with
    different number of buckets to obtain optimal results."
    For example
    =20
    We will create a test table, gather statistics and create two groups one
    small of 3 records and other of 88713 records, we will run a test with =
    and
    without histograms.
    create table test as=20

    select * from dba_objects UNION ALL=20
    select * from dba_objects UNION ALL=20
    select * from dba_objects;

    create index idxtest on test (OWNER);

    SQL> UPDATE TEST SET OWNER =3D 'XXX' WHERE NOT OWNER =3D 'CACHITO';
    SQL> COMMIT;
    SQL> SELECT OWNER,COUNT(*) FROM TEST GROUP BY OWNER;

    OWNER COUNT(*)

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

    CACHITO 3

    XXX 88713

    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=3D>'ADM',TABNAME=3D>'TEST'

    CASCADE=3D>TRUE);

    =20
    SQL> SELECT * FROM TEST WHERE OWNER =3D 'CACHITO';

    Execution Plan

    SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D70 Card=3D44358 =
    Bytes=3D4036578)
    1 0
    TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D70 Card=3D44358 =
    Bytes=3D4036578)
    2 1
    INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=3D93 Card=3D44358)
    Statistics

    0 recursive calls
    0 db block gets
    6 consistent gets
    0 physical reads
    0 redo size
    1213 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    3 rows processed
    SQL> SELECT * FROM TEST WHERE OWNER =3D 'XXX';

    88713 filas seleccionadas.
    Execution Plan

    SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D70 Card=3D44358 =
    Bytes=3D4036578)
    1 0
    TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D70 Card=3D44358 =
    Bytes=3D4036578)
    2 1
    INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=3D93 Card=3D44358)
    Statistics

    0 recursive calls
    0 db block gets
    13132 consistent gets
    0 physical reads
    0 redo size
    4304202 bytes sent via SQL*Net to client
    65557 bytes received via SQL*Net from client
    5916 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    88713 rows processed
    SQL>

    SQL> SELECT NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS WHERE OWNER =3D =

    'ADM' AND

    TABLE_NAME =3D 'TEST' AND COLUMN_NAME =3D 'OWNER';

    =20
    NUM_BUCKETS

    1
    =20
    SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('ADM','TEST', METHOD_OPT =3D> =

    'FOR

    COLUMNS SIZE 75 OWNER');

    Procedimiento PL/SQL terminado correctamente.
    SQL> SELECT NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS WHERE OWNER =3D =

    'ADM' AND

    TABLE_NAME =3D 'TEST' AND COLUMN_NAME =3D 'OWNER';

    =20
    As you can see there is only one bucket, because there are too few =
    values in
    the column.
    NUM_BUCKETS

    1
    =20
    Now we will try again
    SQL> SELECT * FROM TEST WHERE OWNER =3D 'CACHITO';

    Execution Plan

    SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D3 Bytes=3D273)
    1 0
    TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D2 Card=3D3 Bytes=3D273)
    2 1
    INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=3D1 Card=3D3)
    Statistics

    0 recursive calls
    0 db block gets
    6 consistent gets
    0 physical reads
    0 redo size
    1213 bytes sent via SQL*Net to client
    503 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    3 rows processed
    =20
    SQL> SELECT * FROM TEST WHERE OWNER =3D 'XXX';

    Execution Plan

    SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D140 Card=3D88713 =
    Bytes=3D8072883)
    1 0
    TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3D140 Card=3D88713 =
    Bytes=3D8072883)
    2 1
    INDEX (RANGE SCAN) OF 'IDXTEST' (NON-UNIQUE) (Cost=3D186 Card=3D88713)
    Statistics

    0 recursive calls
    0 db block gets
    13132 consistent gets
    0 physical reads
    0 redo size
    4304202 bytes sent via SQL*Net to client
    65557 bytes received via SQL*Net from client
    5916 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    88713 rows processed
    =20
    Maybe this is a error in the CBO, so we will force a full scan in the =
    table
    =20
    SQL> SELECT /*+ FULL(TEST) */ * FROM TEST WHERE OWNER =3D 'XXX';

    88713 filas seleccionadas.
    Execution Plan

    SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D188 Card=3D88713 =
    Bytes=3D8072883)
    1 0
    TABLE ACCESS (FULL) OF 'TEST' (Cost=3D188 Card=3D88713 Bytes=3D8072883)
    Statistics

    0 recursive calls
    0 db block gets
    7060 consistent gets
    0 physical reads
    0 redo size
    4304202 bytes sent via SQL*Net to client
    65557 bytes received via SQL*Net from client
    5916 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    88713 rows processed
    =20
    But it seems not to be, a full scan cost 40 more, so it seems this is =
    not a
    good place to use histograms.-
    Meanwhile the CBO is becoming smarter, the more test one have to develop =
    to
    verify the things one do, are really improving the performance.
    =20
    =20
    More about histograms you can read=20
    http://www.dba-oracle.com/art_otn_cbo_p4.htm

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • David at Jul 21, 2004 at 1:26 pm
    Unless I'm mistaken I believe the concensus on here is that a higher cost
    does not necessarily indicate a poorer plan.
    Just wanted to point that out in reference to the cost being higher
    mentioned below...+

    --
    ..
    David
    But it seems not to be, a full scan cost 40 more, so it seems this is not
    a
    good place to use histograms.-
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Juan Carlos Reyes Pacheco at Jul 21, 2004 at 1:49 pm
    Hi David, then how do you get cbo uses the histogram and do a full scan,
    when this is more than 90% of the data
    changing the optimizer index parameter?
    If you can't get CBO use the histogram by himself, you get nothing.


    -------Original Message-------


    From: oracle-l_at_freelists.org
    Date: 07/21/04 14:26:32
    To: oracle-l_at_freelists.org
    Subject: RE: Creating Histograms


    Unless I'm mistaken I believe the concensus on here is that a higher cost
    does not necessarily indicate a poorer plan.
    Just wanted to point that out in reference to the cost being higher
    mentioned below...+

    --
    .
    David
    But it seems not to be, a full scan cost 40 more, so it seems this is not
    a
    good place to use histograms.-
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • David at Jul 21, 2004 at 1:56 pm
    My point was only that "choosing" lowest cost plan of a given statement is
    not necessarily indicative of the "best" plan.

    --
    ..
    David
    Hi David, then how do you get cbo uses the histogram and do a full scan,
    when this is more than 90% of the data
    changing the optimizer index parameter?
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Juan Carlos Reyes Pacheco at Jul 21, 2004 at 2:10 pm
    ok;)


    -------Original Message-------


    From: oracle-l_at_freelists.org
    Date: 07/21/04 14:56:28
    To: oracle-l_at_freelists.org
    Subject: RE: Creating Histograms


    My point was only that "choosing" lowest cost plan of a given statement is
    not necessarily indicative of the "best" plan.

    --
    .
    David
    Hi David, then how do you get cbo uses the histogram and do a full scan,
    when this is more than 90% of the data
    changing the optimizer index parameter?
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Juan Carlos Reyes Pacheco at Jul 21, 2004 at 1:45 pm
    I am reading
    http://www.centrexcc.com/Fallacies%20of%20the%20Cost%20Based%20Optimizer.pdf
    is very good, I´ll add a link to it in my paper

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Ryan.gaffuri_at_comcast.net at Jul 21, 2004 at 2:21 pm
    Cost is irrelavent. Ignore it. Its strictly internal to Oracle. You cannot compare costs of two different plans. This is all over asktom.
    The primary stats to look at are:(though other stats have uses).

    consistent gets (logical IO) -- 99.99% of the time the query with the lower value is better. Every once in a while I found that a 30% increase in logical IOs when doing a faster full scan results in a 30% or more reduction in response times. This is on large batch processes only and do not have to stand up under concurrency. I do not have a repeatable case and no idea why...
    Cardinality/Rows -- this one is more important in OLTPs, since you want to primary reduce 'rows' at each operation of your query. The earlier in the plan you can weed out unnecessary rows, the better the performance.
    Bytes -- this is typically more useful with full table scans, hash joins, and sort merges, since you are reading all the bytes in the block. Methodology with this one is the same as Cardinality/Rows. You will use this when doing batch processes and reporting. Less often with OLTPs.
    My point was only that "choosing" lowest cost plan of a given statement is
    not necessarily indicative of the "best" plan.
    --
    .
    David
    Hi David, then how do you get cbo uses the histogram and do a full scan,
    when this is more than 90% of the data
    changing the optimizer index parameter?
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jonathan Lewis at Jul 21, 2004 at 2:42 pm
    Note in-line

    Regards

    Jonathan Lewis

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ

    http://www.jlcomp.demon.co.uk/seminar.html
    Optimising Oracle Seminar - schedule updated July 20th

    Original Message -----
    From:
    To:
    Sent: Wednesday, July 21, 2004 8:25 PM
    Subject: RE: Creating Histograms

    Cost is irrelavent. Ignore it. Its strictly internal to Oracle. You cannot
    compare costs of two different plans. This is all over asktom.
    The primary stats to look at are:(though other stats have uses).

    [JL] This is one of the very few points where I disagree with Tom,
    [JL] In priniciple the cost of a query "ought" to be directly correlated
    [JL] to the run-time of a query. There are various reasons why theory
    [JL] and practice do not agree - but when this is the case, you need to
    [JL] work out if
    [JL] a) You have found a bug in the CBO
    [JL] b) You have some bad statistics
    [JL] c) You have a situation that the CBO cannot recognise.
    [JL] Addressing the cause of the CBO's failure will give you a generic
    [JL] solution to many problems. Ignoring the error on a specific SQL
    [JL] statement will lead you to tweak lots of statements without why a
    [JL] particular fix works in some cases but not in others.

    consistent gets (logical IO) -- 99.99% of the time the query with the lower
    value is better. Every once in a while I found that a 30% increase in
    logical IOs when doing a faster full scan results in a 30% or more reduction
    in response times. This is on large batch processes only and do not have to
    stand up under concurrency. I do not have a repeatable case and no idea
    why...
    Cardinality/Rows -- this one is more important in OLTPs, since you want to
    primary reduce 'rows' at each operation of your query. The earlier in the
    plan you can weed out unnecessary rows, the better the performance.
    Bytes -- this is typically more useful with full table scans, hash joins,
    and sort merges, since you are reading all the bytes in the block.
    Methodology with this one is the same as Cardinality/Rows. You will use this
    when doing batch processes and reporting. Less often with OLTPs.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Cary Millsap at Jul 21, 2004 at 6:34 pm
    Jonathan, would you agree that it's fair to say that, "Cost is =
    irrelevant
    for virtually every purpose other than comparing it with other costs?"

    I know that it may be possible to find some factor f for which (response
    time) =3D f * cost, but I think you and Tom would both say that cost is
    predominantly of value as a measure that allows CBO to RANK the expected
    performance of competing query execution plans. Yes?

    Cary Millsap
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
    * Nullius in verba *

    Upcoming events:
    - Performance Diagnosis 101: 7/20 Cleveland, 8/10 Boston, 9/14 San =
    Francisco
    - SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =
    Hartford
    - Hotsos Symposium 2005: March 6-10 Dallas
    - Visit www.hotsos.com for schedule details...

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org =

    On Behalf Of Jonathan Lewis
    Sent: Wednesday, July 21, 2004 2:41 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Creating Histograms

    Note in-line

    Regards

    Jonathan Lewis

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ

    http://www.jlcomp.demon.co.uk/seminar.html
    Optimising Oracle Seminar - schedule updated July 20th

    Original Message -----=20
    From:
    To:
    Sent: Wednesday, July 21, 2004 8:25 PM
    Subject: RE: Creating Histograms

    Cost is irrelavent. Ignore it. Its strictly internal to Oracle. You =
    cannot
    compare costs of two different plans. This is all over asktom.
    The primary stats to look at are:(though other stats have uses).

    [JL] This is one of the very few points where I disagree with Tom,
    [JL] In priniciple the cost of a query "ought" to be directly =
    correlated
    [JL] to the run-time of a query. There are various reasons why theory
    [JL] and practice do not agree - but when this is the case, you need to
    [JL] work out if
    [JL] a) You have found a bug in the CBO
    [JL] b) You have some bad statistics
    [JL] c) You have a situation that the CBO cannot recognise.
    [JL] Addressing the cause of the CBO's failure will give you a generic
    [JL] solution to many problems. Ignoring the error on a specific SQL
    [JL] statement will lead you to tweak lots of statements without why a
    [JL] particular fix works in some cases but not in others.

    consistent gets (logical IO) -- 99.99% of the time the query with the =
    lower
    value is better. Every once in a while I found that a 30% increase in
    logical IOs when doing a faster full scan results in a 30% or more =
    reduction
    in response times. This is on large batch processes only and do not have =
    to
    stand up under concurrency. I do not have a repeatable case and no idea
    why...
    Cardinality/Rows -- this one is more important in OLTPs, since you want =
    to
    primary reduce 'rows' at each operation of your query. The earlier in =
    the
    plan you can weed out unnecessary rows, the better the performance.
    Bytes -- this is typically more useful with full table scans, hash =
    joins,
    and sort merges, since you are reading all the bytes in the block.
    Methodology with this one is the same as Cardinality/Rows. You will use =
    this
    when doing batch processes and reporting. Less often with OLTPs.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jonathan Lewis at Jul 23, 2004 at 11:47 am
    Note in-line

    Regards

    Jonathan Lewis

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ

    http://www.jlcomp.demon.co.uk/seminar.html
    Optimising Oracle Seminar - schedule updated July 20th

    Original Message -----
    From: "Cary Millsap"
    Jonathan, would you agree that it's fair to say that, "Cost is irrelevant
    for virtually every purpose other than comparing it with other costs?"
    No. Actually I tend to use the cost to identify

    problems with statistics
    deficiences in the CBO
    bugs in CBO
    in that order.

    Apart from that, I tend to assume that "cost = predicted
    run time", with a fudge factor thrown in for known deficiencies
    in the CBO. (I used to say cost = csecs to completion as a
    rough guide, but that's just an approximation to the now-published
    statement that the cost is the completion time measured in units of
    the single-block read-time)

    In fact, when cost != run-time, that's my first clue to the
    presence of a CBO problem.
    I know that it may be possible to find some factor f for which (response
    time) = f * cost, but I think you and Tom would both say that cost is
    predominantly of value as a measure that allows CBO to RANK the expected
    performance of competing query execution plans. Yes?
    No. The most significant difference of opinion that we have (or perhaps had)
    was that Tom used to state quite firmly that you could not compare the
    cost of one query with the cost of a different query and assume that the
    comparison had any meaning. I believe that two costs reported at the
    same time from the same system mean the same thing - even if they come
    from the execution paths from two different queries.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Wolfgang Breitling at Jul 21, 2004 at 2:40 pm
    You create histograms on individual columns with gather_table_stats.
    Gathering histograms is a very selective process - both, on which column(s)
    and with what number of buckets. It is not something you ought to do with a
    broad stroke like gather_schema_stats. One size (pun intended) certainly
    does not fit all.

    Once you gathered the histograms in this way for the columns where it
    benefits performance, you can use method_opt=>'for all columns size repeat'
    in gather_schema_stats to re-gather the histograms - according to the
    documentation, I have not verified that myself.

    All that provided you insist on doing the regular gather_schema_stats
    russian roulette.

    The values for all (analyzed) tables in dba_histograms are OK. Capturing
    min and max column values can be viewed as a 1-bucket histogram (bounded by
    lowest and highest column value), which is what the default
    method_opt=>'for all columns size 1' also implies - a histogram of size one
    for all columns..

    PS. An easy way to find which columns are used in predicates, and one which
    Oracle uses when you use method_opt=>'... size auto', is to query
    sys.col_usage$.

    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    http://www.centrexcc.com

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Freeman, Donald at Jul 21, 2004 at 3:17 pm
    Ok, so let's say I've done all that: =20

    I've identified my target columns that are being used as predicates.
    Next, create the histograms on these columns by using =
    gather_table_stats
    The number of buckets I need is determined by the number of distinct =

    values in the column. I want to have a 'value' histogram instead of =
    'height' so I need at least as many buckets as I have distinct values..

    I now have to use method_opt repeat because if I don't it will default =
    back to one bucket min/max value band the next time I run =
    gather_schema_stats, right?=20

    But, now you've scared me ~=20

    I could be mistaken but 'russian roulette' usually refers to a bad =
    thing but maybe I'm doing it wrong. I'm not aware of any bad things =
    related to the use of gather_schema_stats. Would you elucidate on that =
    comment a little? Are there other, better, options for maintaining the =
    histograms once they are created?

    I'm thinking you've politely answered my questions but you are thinking, =
    "That's not the way I would do it." =20

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Wolfgang Breitling
    Sent: Wednesday, July 21, 2004 3:40 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Creating Histograms

    You create histograms on individual columns with gather_table_stats.=20
    Gathering histograms is a very selective process - both, on which =
    column(s)=20
    and with what number of buckets. It is not something you ought to do =
    with a=20
    broad stroke like gather_schema_stats. One size (pun intended) certainly =

    does not fit all.

    Once you gathered the histograms in this way for the columns where it=20
    benefits performance, you can use method_opt=3D>'for all columns size =
    repeat'=20
    in gather_schema_stats to re-gather the histograms - according to the=20
    documentation, I have not verified that myself.

    All that provided you insist on doing the regular gather_schema_stats=20
    russian roulette.

    The values for all (analyzed) tables in dba_histograms are OK. Capturing =

    min and max column values can be viewed as a 1-bucket histogram (bounded =
    by=20
    lowest and highest column value), which is what the default=20
    method_opt=3D>'for all columns size 1' also implies - a histogram of =
    size one=20
    for all columns..

    PS. An easy way to find which columns are used in predicates, and one =
    which=20
    Oracle uses when you use method_opt=3D>'... size auto', is to query=20
    sys.col_usage$.

    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    http://www.centrexcc.com=20

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/

    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org

    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Juan Carlos Reyes Pacheco at Jul 21, 2004 at 3:31 pm
    Based in my few experience in histograms I decided to run this command
    EXEC DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME=>'DAZ', ESTIMATE_PERCENT=>100

    METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');

    with the only goal of giving more information to CBOI
    didn't tested if this really gives a improvement, but I read something in
    some place that says it does.
    I don't remember where.


    Any comment about the previous command?

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Wolfgang Breitling at Jul 21, 2004 at 4:16 pm
    The russian roulette remark does not have anything to do with the gathering
    of histograms but with the widespread practice of gathering statistics on a
    schedule - every weekend, or every night, or whatever, without having
    established a need for it. The new statistics can have unexpected negative
    effects on performance (nobody would ever complain about unexpected
    positive effects), hence my term russian roulette. If you are gathering
    statistics on a schedule - by whatever method - at least back up the
    current statistics first ( and have a grandfather-father-son hierarchy of
    saved statistics ) so that you can restore them if necessary.

    Why the restriction to value based histograms (aka frequency histograms in 9i)?
    At 02:13 PM 7/21/2004, you wrote:
    Ok, so let's say I've done all that: =20

    I've identified my target columns that are being used as predicates.
    Next, create the histograms on these columns by using =
    gather_table_stats
    The number of buckets I need is determined by the number of
    distinct =
    values in the column. I want to have a 'value' histogram instead of =
    'height' so I need at least as many buckets as I have distinct values..
    I now have to use method_opt repeat because if I don't it will
    default =
    back to one bucket min/max value band the next time I run =
    gather_schema_stats, right?=20

    But, now you've scared me ~=20

    I could be mistaken but 'russian roulette' usually refers to a bad =
    thing but maybe I'm doing it wrong. I'm not aware of any bad things =
    related to the use of gather_schema_stats. Would you elucidate on
    that =
    comment a little? Are there other, better, options for maintaining the =
    histograms once they are created?

    I'm thinking you've politely answered my questions but you are thinking, =
    "That's not the way I would do it." =20
    That's essentially how I do it, except for the subsequent
    gather_schema_stats bit. I don't in general analyze tables very often and
    certainly not the entire schema, only individual tables and only if it is
    necessary. Otherwise I practice "plan stability" meaning "if the statistics
    don't change, the plans won't".

    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jonathan Lewis at Jul 21, 2004 at 4:22 pm
    Note in-line.

    Regards

    Jonathan Lewis

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ

    http://www.jlcomp.demon.co.uk/seminar.html
    Optimising Oracle Seminar - schedule updated July 20th

    Original Message -----
    From: "Wolfgang Breitling"
    Otherwise I practice "plan stability" meaning "if the statistics
    don't change, the plans won't".
    Beg to differ - for example:

    The fixed predicate:

    last_update_date >= "calculated constant of 28 days ago"
    is likely to have a selectivity that changes as time passes,
    with a possible change in plan, precisely BECAUSE
    the statistics haven't changed.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Wolfgang Breitling at Jul 21, 2004 at 5:02 pm
    There are always exceptions but I firmly believe that in the majority of
    cases the plans do not change as long as the statistics do not. You have to
    know those cases where it is necessary to refresh the statistics after
    certain events.

    Even in your extreme case I am not certain that the plan does change.
    Potentially if there is a rather small range of last_update_dates or if
    there is a histogram on last_update_date.
    Without a histogram, the selectivity of "col >= value" is (col.hi - value)
    / (col.hi - col.lo) + 1/col.ndv (there may be exceptions but generally
    that's the selectivity). Unless value is close to col.lo - i.e. as long as
    the range extents well beyond the 28 days, the selectivity will change only
    marginally as value approaches col.hi (which doesn't change without
    refreshing the statistics). Granted, even a small change in selectivity can
    cause a change of plan, but it is not very likely. Once value is >= col.hi,
    the CBO just uses 1/col.ndv as selectivity, as far as I could determine,
    which won't change at all without statistics refresh and thus the plan
    won't change if you make it through the 28 days without statistics refresh.
    At 03:25 PM 7/21/2004, you wrote:

    Otherwise I practice "plan stability" meaning "if the statistics
    don't change, the plans won't".
    Beg to differ - for example:

    The fixed predicate:
    last_update_date >= "calculated constant of 28 days ago"
    is likely to have a selectivity that changes as time passes,
    with a possible change in plan, precisely BECAUSE
    the statistics haven't changed.
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    http://www.centrexcc.com

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Mark W. Farnham at Jul 21, 2004 at 5:19 pm
    Ah, now here is a challenge to throw down to the Oracle auto everything
    group: Sample my database and use thereof over time to calculate when the
    workshift weighted value of resources is effectively devoted to statistics
    recalculation.

    So, in a given operational window, do I have cycles to burn, and if so, are
    they well burned bringing certain statistics up to date?

    In the meantime, thankfully, there remains a task requiring sense:
    Determining which statistics it is useful to recalculate, to what level of
    computation, and histogramization, and how often.

    Statistics generations can range from being the defrag extents for the sake
    of defragging of our day to an essential operation depending on the case in
    hand.

    mwf
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Wolfgang Breitling
    Sent: Wednesday, July 21, 2004 6:06 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Creating Histograms

    There are always exceptions but I firmly believe that in the majority of
    cases the plans do not change as long as the statistics do not. You have to
    know those cases where it is necessary to refresh the statistics after
    certain events.

    Even in your extreme case I am not certain that the plan does change.
    Potentially if there is a rather small range of last_update_dates or if
    there is a histogram on last_update_date.
    Without a histogram, the selectivity of "col >= value" is (col.hi - value)
    / (col.hi - col.lo) + 1/col.ndv (there may be exceptions but generally
    that's the selectivity). Unless value is close to col.lo - i.e. as long as
    the range extents well beyond the 28 days, the selectivity will change only
    marginally as value approaches col.hi (which doesn't change without
    refreshing the statistics). Granted, even a small change in selectivity can
    cause a change of plan, but it is not very likely. Once value is >= col.hi,
    the CBO just uses 1/col.ndv as selectivity, as far as I could determine,
    which won't change at all without statistics refresh and thus the plan
    won't change if you make it through the 28 days without statistics refresh.
    At 03:25 PM 7/21/2004, you wrote:

    Otherwise I practice "plan stability" meaning "if the statistics
    don't change, the plans won't".
    Beg to differ - for example:

    The fixed predicate:
    last_update_date >= "calculated constant of 28 days ago"
    is likely to have a selectivity that changes as time passes,
    with a possible change in plan, precisely BECAUSE
    the statistics haven't changed.
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    http://www.centrexcc.com

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Wolfgang Breitling at Jul 21, 2004 at 5:49 pm
    I said that I consider it unlikely, even in this case, that the plan will
    change if the statistics are not refreshed - unless the range of the
    last_update_date values is rather narrow or it has a histogram. I found
    that histograms often need to be refreshed much more frequently ( this not
    an exception to my rule of not analyzing unless a benefit is established
    but a confirmation: the benefit is established). It very much depends on
    the column and its usage. If, for example, you have a status column with
    two values: 'P' for processed and 'N' for not processed. One of your batch
    processes loads rows into the table with a status of 'N', typically
    ~200,000 (into a multi-million row table). Another batch process picks out
    those 200,000 not-yet-processed rows (where status = 'N') and, when done
    changes the status to 'P'. The next day the process repeats. This is a case
    where an index on status and a histogram makes a big difference. But to the
    optimizer it does not matter that the 200,000 status 'N' rows are different
    rows every day. It is only interested in the fact that they are a small
    minority and that the use of the index makes sense. In this case it is not
    necessary to refresh the histogram every day.
    At 04:06 PM 7/21/2004, you wrote:
    There are always exceptions but I firmly believe that in the majority of
    cases the plans do not change as long as the statistics do not. You have to
    know those cases where it is necessary to refresh the statistics after
    certain events.

    Even in your extreme case I am not certain that the plan does change.
    Potentially if there is a rather small range of last_update_dates or if
    there is a histogram on last_update_date.
    Without a histogram, the selectivity of "col >= value" is (col.hi - value)
    / (col.hi - col.lo) + 1/col.ndv (there may be exceptions but generally
    that's the selectivity). Unless value is close to col.lo - i.e. as long as
    the range extents well beyond the 28 days, the selectivity will change only
    marginally as value approaches col.hi (which doesn't change without
    refreshing the statistics). Granted, even a small change in selectivity can
    cause a change of plan, but it is not very likely. Once value is >= col.hi,
    the CBO just uses 1/col.ndv as selectivity, as far as I could determine,
    which won't change at all without statistics refresh and thus the plan
    won't change if you make it through the 28 days without statistics refresh.
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Freeman, Donald at Jul 22, 2004 at 9:36 am
    OK, I understand your point about gathering on schedule. I'm moving into =
    taking over a turn-key contractor developed system. We are doing =
    stats/computed every day. We only add, at most, a few thousand records =
    a day. This is much, much less than 10%. We converted a few million =
    records, about five years worth of records, from four or five other =
    public health databases but our daily accrual is relatively small. I =
    probably wouldn't have to run stats once in a month. We also don't =
    collect system stats. I'm hoping to get enough information here to 'have =
    a meeting' and get all of that changed, the method and rate of =
    collection. I think I want to go to monitoring/stale and that our =
    performance will improve a lot. Right off the top we'll save a couple =
    hours a day of process time that we collect stats.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Wolfgang Breitling
    Sent: Wednesday, July 21, 2004 5:19 PM
    To: oracle-l_at_freelists.org
    Subject: RE: Creating Histograms

    The russian roulette remark does not have anything to do with the =
    gathering=20
    of histograms but with the widespread practice of gathering statistics =
    on a=20
    schedule - every weekend, or every night, or whatever, without having=20
    established a need for it. The new statistics can have unexpected =
    negative=20
    effects on performance (nobody would ever complain about unexpected=20
    positive effects), hence my term russian roulette. If you are gathering=20
    statistics on a schedule - by whatever method - at least back up the=20
    current statistics first ( and have a grandfather-father-son hierarchy =
    of=20
    saved statistics ) so that you can restore them if necessary.

    Why the restriction to value based histograms (aka frequency histograms =
    in 9i)?
    At 02:13 PM 7/21/2004, you wrote:
    Ok, so let's say I've done all that: =3D20

    I've identified my target columns that are being used as =
    predicates.
    Next, create the histograms on these columns by using =3D
    gather_table_stats
    The number of buckets I need is determined by the number of=20
    distinct =3D
    values in the column. I want to have a 'value' histogram instead of = =3D
    'height' so I need at least as many buckets as I have distinct values..
    I now have to use method_opt repeat because if I don't it will =
    default =3D
    back to one bucket min/max value band the next time I run =3D
    gather_schema_stats, right?=3D20

    But, now you've scared me ~=3D20

    I could be mistaken but 'russian roulette' usually refers to a = bad =3D
    thing but maybe I'm doing it wrong. I'm not aware of any bad things = =3D
    related to the use of gather_schema_stats. Would you elucidate on=20
    that =3D
    comment a little? Are there other, better, options for maintaining the = =3D
    histograms once they are created?

    I'm thinking you've politely answered my questions but you are =
    thinking, =3D
    "That's not the way I would do it." =3D20
    That's essentially how I do it, except for the subsequent=20
    gather_schema_stats bit. I don't in general analyze tables very often =
    and=20
    certainly not the entire schema, only individual tables and only if it =
    is=20
    necessary. Otherwise I practice "plan stability" meaning "if the =
    statistics=20
    don't change, the plans won't".

    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com=20

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Niall Litchfield at Jul 22, 2004 at 2:52 pm
    Comments as always
    On Thu, 22 Jul 2004 10:37:21 -0400, Freeman, Donald
    wrote:
    OK, I understand your point about gathering on schedule. I'm moving into =
    taking over a turn-key contractor developed system. We are doing =
    stats/computed every day. We only add, at most, a few thousand records =
    a day. This is much, much less than 10%. We converted a few million =
    records, about five years worth of records, from four or five other =
    public health databases but our daily accrual is relatively small. I =
    probably wouldn't have to run stats once in a month.
    take note of how many records you add to relatively *small* tables. 13
    rows added to one of our tables caused hell until we gathered stats
    again (and it took ages for anyone to admit that anything ahd
    changed). That would be 13 rows in the sense of another financial year
    to add to the 2 existing ones - so hardly significant at all :).

    I guess I'm saying different objects might have different stats needs.
    We also don't =
    collect system stats. I'm hoping to get enough information here to 'have =
    a meeting' and get all of that changed, the method and rate of =
    collection.
    Test system stats carefully (I'm probably too cautious on this), but
    system stats are likely to make quite a noticeable difference to
    execution times. It is, I'm increasingly convinced, the *right* thing
    to do. It doesn't mean that you may not have adverse effects. Overall
    system stats have been positive for our test financial environmentenough
    so that they get introduced with the next software upgrade that
    is running there - but there has been the odd hiccup.

    --
    Niall Litchfield
    Oracle DBA
    http://www.niall.litchfield.dial.pipex.com
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Wolfgang Breitling at Jul 22, 2004 at 5:31 pm

    At 01:55 PM 7/22/2004, you wrote:
    take note of how many records you add to relatively *small* tables. 13
    rows added to one of our tables caused hell until we gathered stats
    again (and it took ages for anyone to admit that anything ahd
    changed). That would be 13 rows in the sense of another financial year
    to add to the 2 existing ones - so hardly significant at all :).
    Can you give more details on that and why 13 more rows caused hell until
    stats were gathered again. What were the execution plans before and after?
    Were there histograms involved?
    I guess I'm saying different objects might have different stats needs.
    Absolutely.
    We also don't =
    collect system stats. I'm hoping to get enough information here to 'have =
    a meeting' and get all of that changed, the method and rate of =
    collection.
    Test system stats carefully (I'm probably too cautious on this), but
    system stats are likely to make quite a noticeable difference to
    execution times. It is, I'm increasingly convinced, the *right* thing
    to do. It doesn't mean that you may not have adverse effects. Overall
    system stats have been positive for our test financial environment-
    enough so that they get introduced with the next software upgrade that
    is running there - but there has been the odd hiccup.
    Whenever you drastically change your operations - going from RBO to CBO,
    going from nightly/weekly gather to no-gather with exceptions, going from
    no system stats to system stats, or vice-versa is always a big risk and
    should be tested very thoroughly.

    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    www.centrexcc.com

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Niall Litchfield at Jul 23, 2004 at 4:06 am

    On Thu, 22 Jul 2004 16:34:17 -0600, Wolfgang Breitling wrote:
    At 01:55 PM 7/22/2004, you wrote:

    take note of how many records you add to relatively *small* tables. 13
    rows added to one of our tables caused hell until we gathered stats
    again (and it took ages for anyone to admit that anything ahd
    changed). That would be 13 rows in the sense of another financial year
    to add to the 2 existing ones - so hardly significant at all :).
    Can you give more details on that and why 13 more rows caused hell until
    stats were gathered again. What were the execution plans before and after?
    Were there histograms involved?
    I knew someone would ask that. Unfortunately this will have to be
    anecdotal evidence since it was 18 months ago. I was trying to be
    ironic about the significance of the change. The table was a table of
    valid periods it did have 26 rows in it before the change, after the
    change it had 39 rows in it ( 3 years worth of financial periods not
    2). I think it ought to be obvious that adding 50% more records to a
    table is worth telling the optimizer about. My point is that the
    administrators of the system who made this change swore blind that
    nothing had changed and there was a big problem with the database.
    From their point of view I think this was not unreasonable, it was a
    small routine change. Now I'm arguing out of experience rather than
    theory here, but it seems to me that this pattern of thinking a
    significant change is insignificant is likely to happen with small
    tables a lot more frequently than with large ones. Hence my suggestion
    to pay special attention to the small tables.

    I think Jonathan has already mentioned that small stats errors will
    likely have a larger effect when made on small tables rather than
    large ones. I'd be a little wary of monitor/gather stale which is what
    Donald was suggesting for small tables when we know that these are
    sensitive to changes, that gathering accurate stats on them is really
    cheap.
    Whenever you drastically change your operations - going from RBO to CBO,
    going from nightly/weekly gather to no-gather with exceptions, going from
    no system stats to system stats, or vice-versa is always a big risk and
    should be tested very thoroughly.
    Fortunately there are the export stats procedures in dbms_stats to
    alleviate this risk somewhat.

    --
    Niall Litchfield
    Oracle DBA
    http://www.niall.litchfield.dial.pipex.com
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jonathan Lewis at Jul 22, 2004 at 6:15 pm
    It's an quirky little paradox that big tables
    are usually fine with a small sample, but
    small tables need the best possible stats.

    An error of 'a couple of rows' in a small
    table is, after all, likely to be a significant
    percentage error.

    Regards

    Jonathan Lewis

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ

    http://www.jlcomp.demon.co.uk/seminar.html
    Optimising Oracle Seminar - schedule updated July 20th

    Original Message -----
    From: "Niall Litchfield"

    take note of how many records you add to relatively *small* tables. 13
    rows added to one of our tables caused hell until we gathered stats
    again (and it took ages for anyone to admit that anything ahd
    changed). That would be 13 rows in the sense of another financial year
    to add to the 2 existing ones - so hardly significant at all :).

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Wolfson Larry - lwolfs at Jul 22, 2004 at 6:25 pm
    13 isn't some kind of unlucky number for Oracle, is it?

    One of the application teams added a "code" table (I'm pretty sure it was
    indexed, but definitely analyzed) that only had 13 rows and it killed the
    application.

    Code re-write solved the issue. After it was in production, of course.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jonathan Lewis
    Sent: Thursday, July 22, 2004 6:19 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Creating Histograms

    It's an quirky little paradox that big tables
    are usually fine with a small sample, but
    small tables need the best possible stats.

    An error of 'a couple of rows' in a small
    table is, after all, likely to be a significant
    percentage error.

    Regards

    Jonathan Lewis

    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ

    http://www.jlcomp.demon.co.uk/seminar.html
    Optimising Oracle Seminar - schedule updated July 20th

    Original Message -----
    From: "Niall Litchfield"

    take note of how many records you add to relatively *small* tables. 13
    rows added to one of our tables caused hell until we gathered stats
    again (and it took ages for anyone to admit that anything ahd
    changed). That would be 13 rows in the sense of another financial year
    to add to the 2 existing ones - so hardly significant at all :).

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    The information contained in this communication is
    confidential, is intended only for the use of the recipient
    named above, and may be legally privileged.
    If the reader of this message is not the intended
    recipient, you are hereby notified that any dissemination,
    distribution, or copying of this communication is strictly
    prohibited.
    If you have received this communication in error,
    please re-send this communication to the sender and
    delete the original message or any copy of it from your
    computer system. Thank You.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Juan Carlos Reyes Pacheco at Jul 23, 2004 at 8:38 am
    In the antique 13 had always been a good luck number, because christ died
    this tried to become a bad luck numbre,
    but for me is still a good luck number;
    -------Original Message-------


    From: oracle-l_at_freelists.org
    Date: 07/22/04 19:26:19
    To: 'oracle-l_at_freelists.org'
    Subject: RE: Creating Histograms


    13 isn't some kind of unlucky number for Oracle, is it?


    One of the application teams added a "code" table (I'm pretty sure it was
    indexed, but definitely analyzed) that only had 13 rows and it killed the
    application.


    Code re-write solved the issue. After it was in production, of course.


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jonathan Lewis
    Sent: Thursday, July 22, 2004 6:19 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Creating Histograms




    It's an quirky little paradox that big tables
    are usually fine with a small sample, but
    small tables need the best possible stats.


    An error of 'a couple of rows' in a small
    table is, after all, likely to be a significant
    percentage error.



    Regards


    Jonathan Lewis


    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    The Co-operative Oracle Users' FAQ


    http://www.jlcomp.demon.co.uk/seminar.html
    Optimising Oracle Seminar - schedule updated July 20th





    Original Message -----
    From: "Niall Litchfield"

    take note of how many records you add to relatively *small* tables. 13
    rows added to one of our tables caused hell until we gathered stats
    again (and it took ages for anyone to admit that anything ahd
    changed). That would be 13 rows in the sense of another financial year
    to add to the 2 existing ones - so hardly significant at all :).




    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html




    The information contained in this communication is
    confidential, is intended only for the use of the recipient
    named above, and may be legally privileged.
    If the reader of this message is not the intended
    recipient, you are hereby notified that any dissemination,
    distribution, or copying of this communication is strictly
    prohibited.
    If you have received this communication in error,
    please re-send this communication to the sender and
    delete the original message or any copy of it from your
    computer system. Thank You.


    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/

    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org

    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Freeman, Donald at Jul 23, 2004 at 10:34 am
    I have Wolfgang's presentation from =
    http://www.centrexcc.com/SQL%20Tuning%20with%20Statistics.ppt.pdf but =
    have a question about exporting statistics. I understand that before I =
    start mucking about I need to save them. And, I'm gathering that each =
    time before we rerun them in our current Russian Roulette system we =
    should save them, just 'in case.'=20

    When creating the stats table to whom should I grant ownership? I =
    found an article with an example creating it under SYS schema in the =
    SYSTEM tablespace. Wolfgang's example creates it in Scott's schema and =
    tablespace. If this is going to be a production process should I set it =
    up under an application schema owner? Or does each application schema =
    owner need their own stats table? Can I keep multiple sets of statistics =
    for a single schema? Is that what the statid is for? Or, do I need to =
    export them to save them? I'm a little bit foggy on the big picture =
    here since I'm not currently doing any of this. Sorry if this is =
    sounding ignorant!

    I want to verify that if I export my production statistics to a test =
    machine I will get the same execution plan regardless of the fact that I =
    only have 10% of the data on that system. Right?

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Riyaj Shamsudeen at Jul 23, 2004 at 10:58 am
    Hi Donald,
    If this is going to be a production process should I set it up under an
    application schema owner?
    Yes. I would and have a schema which stores the system level information
    such as stats, space usage etc..It is also easier for me to export this
    schema and stick it in another database, for further analysis.
    Or does each application schema owner need their own stats table?
    Not necessarily..
    Can I keep multiple sets of statistics for a single schema? Is that
    what the statid is for ?
    Yes. You can keep multiple sets, with statid..

    Here is a procedure I use :This is called for various app schemas, every
    week ( Statistics are collected per table level in somewhat
    'intelligent' fashion, at the most, stats can change once per week and
    storing stats every week is sufficient for us.).

    Believe it or not, I have rolled back stat changes for one table already
    using these stored stats..

    Cut---
    procedure save_schema_stats (
    v_schema IN VARCHAR2,
    load_status OUT NUMBER,
    load_error OUT VARCHAR2,
    load_comment OUT VARCHAR2
    )
    AS

    Author: Riyaj Shamsudeen
    To store the image of schema stats so that we can rollback
    the stat changes, in case, we have an issue.
    Also, can be used to see the change pattern..
    v_date date;
    l_script_name VARCHAR2(50) := 'save_schema_stats';
    begin
    select to_char(sysdate,'DD-MON-YY') into v_date from dual;
    dbms_stats.export_schema_stats (
    ownname=>v_schema,
    stattab=>'STATTAB',
    statid=>v_schema||'_'||v_date,
    statown=>'DBMON'
    );
    EXCEPTION

    when others then
    load_status:=1;
    load_comment:='Error occurred stats collection package';
    load_error:=SQLCODE||'-'||SQLERRM;
    raise_application_error(-20001, 'Unexpected error.' ||
    '; ' || SQLERRM);

    end;
    END;

    /

    cut---

    Thanks
    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman, Donald
    Sent: Friday, July 23, 2004 10:38 AM
    To: oracle-l_at_freelists.org
    Subject: RE: Creating Histograms

    I have Wolfgang's presentation from =
    http://www.centrexcc.com/SQL%20Tuning%20with%20Statistics.ppt.pdf but =
    have a question about exporting statistics. I understand that before I
    = start mucking about I need to save them. And, I'm gathering that each
    = time before we rerun them in our current Russian Roulette system we =
    should save them, just 'in case.'=20

    When creating the stats table to whom should I grant ownership? I =
    found an article with an example creating it under SYS schema in the =
    SYSTEM tablespace. Wolfgang's example creates it in Scott's schema and
    = tablespace. If this is going to be a production process should I set
    it = up under an application schema owner? Or does each application
    schema = owner need their own stats table? Can I keep multiple sets of
    statistics =
    for a single schema? Is that what the statid is for? Or, do I need to
    =
    export them to save them? I'm a little bit foggy on the big picture =
    here since I'm not currently doing any of this. Sorry if this is =
    sounding ignorant!

    I want to verify that if I export my production statistics to a test =
    machine I will get the same execution plan regardless of the fact that I
    = only have 10% of the data on that system. Right?

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org put
    'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

    -- Attached file included as plaintext by Ecartis --
    -- Desc: Signature

    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Riyaj Shamsudeen at Jul 23, 2004 at 11:19 am
    Pushed that send button too early...
    I want to verify that if I export my production statistics to a test
    machine I will get the
    same execution plan regardless of the fact that I only have 10% of the
    data on that system. Right?

    That depends upon many factors. For the execution plans to be the same,
    following need to be considered:

    Various optimizer specific parameters should match. Event
    10053 trace will show you the relevant optimizer parameters.
    DB block size should match.( I don't even want to think about
    throwing in multiple block size here).
    If you are collecting system stats, then the execution plans
    can be different if your system stats are different(9i). So, you might
    have to copy those also.
    Of course, object statistics should match. (Assumption is
    that the objects have same structures).
    If you don't have stats on few objects, but CBO is being used
    anyway, then the size of the object can cause optimizer to select
    different execution plan.
    If you are in 9i, then dynamic sampling can change the
    execution plan depending upon the level of sampling you have setup.
    Software versions should match. Hopefully, you won't get hit
    with Port specific bugs.

    So, I guess, you will have to consider all the above and check
    out the explain plans between production and test databases. Granted, no
    easy task.

    HTH

    Thanks
    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Riyaj Shamsudeen
    Sent: Friday, July 23, 2004 11:00 AM
    To: oracle-l_at_freelists.org
    Subject: RE: Creating Histograms

    Hi Donald,
    If this is going to be a production process should I set it up under an
    application schema owner?
    Yes. I would and have a schema which stores the system level information
    such as stats, space usage etc..It is also easier for me to export this
    schema and stick it in another database, for further analysis.
    Or does each application schema owner need their own stats table?
    Not necessarily..
    Can I keep multiple sets of statistics for a single schema? Is that
    what the statid is for ?
    Yes. You can keep multiple sets, with statid..

    Here is a procedure I use :This is called for various app schemas, every
    week ( Statistics are collected per table level in somewhat
    'intelligent' fashion, at the most, stats can change once per week and
    storing stats every week is sufficient for us.).

    Believe it or not, I have rolled back stat changes for one table already
    using these stored stats..

    Cut---
    procedure save_schema_stats (
    v_schema IN VARCHAR2,
    load_status OUT NUMBER,
    load_error OUT VARCHAR2,
    load_comment OUT VARCHAR2
    )
    AS

    Author: Riyaj Shamsudeen
    To store the image of schema stats so that we can rollback
    the stat changes, in case, we have an issue.
    Also, can be used to see the change pattern..
    v_date date;
    l_script_name VARCHAR2(50) := 'save_schema_stats';
    begin
    select to_char(sysdate,'DD-MON-YY') into v_date from dual;
    dbms_stats.export_schema_stats (
    ownname=>v_schema,
    stattab=>'STATTAB',
    statid=>v_schema||'_'||v_date,
    statown=>'DBMON'
    );
    EXCEPTION

    when others then
    load_status:=1;
    load_comment:='Error occurred stats collection package';
    load_error:=SQLCODE||'-'||SQLERRM;
    raise_application_error(-20001, 'Unexpected error.' ||
    '; ' || SQLERRM);

    end;
    END;

    /

    cut---

    Thanks
    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman, Donald
    Sent: Friday, July 23, 2004 10:38 AM
    To: oracle-l_at_freelists.org
    Subject: RE: Creating Histograms

    I have Wolfgang's presentation from =
    http://www.centrexcc.com/SQL%20Tuning%20with%20Statistics.ppt.pdf but =
    have a question about exporting statistics. I understand that before I
    = start mucking about I need to save them. And, I'm gathering that each
    = time before we rerun them in our current Russian Roulette system we =
    should save them, just 'in case.'=20

    When creating the stats table to whom should I grant ownership? I =
    found an article with an example creating it under SYS schema in the =
    SYSTEM tablespace. Wolfgang's example creates it in Scott's schema and
    = tablespace. If this is going to be a production process should I set
    it = up under an application schema owner? Or does each application
    schema = owner need their own stats table? Can I keep multiple sets of
    statistics =
    for a single schema? Is that what the statid is for? Or, do I need to
    =
    export them to save them? I'm a little bit foggy on the big picture =
    here since I'm not currently doing any of this. Sorry if this is =
    sounding ignorant!

    I want to verify that if I export my production statistics to a test =
    machine I will get the same execution plan regardless of the fact that I
    = only have 10% of the data on that system. Right?

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org put
    'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

    -- Attached file included as plaintext by Ecartis --
    -- Desc: Signature

    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any
    review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

    -- Attached file included as plaintext by Ecartis --
    -- Desc: Signature

    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.

    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Wolfson Larry - lwolfs at Jul 23, 2004 at 4:41 pm
    We tend to create a stats table for each application

    STATID COUNT(*)
    ------------------------------ ----------
    APR11SUN2000 6738
    APR12MON2000 6738
    APR13TUE2000 6738
    APR14WED2000 7461 Analyze -client requirement
    APR15THU2000 7423
    APR16FRI2000 7423
    APR17SAT2000 7423
    APR18SUN2000 6793
    APR19MON2000 6793
    APR20TUE2000 6793
    APR21WED2000 7461 Analyze -client requirement
    APR22THU2000 7423
    APR23FRI2000 7423
    APR24SAT2000 7423

    D D T
    A A I
    T Y M
    E E
    Hmmmm, we seem to loose stats on Thursdays and Sundays.

    "Can I keep multiple sets of statistics for a single schema? Is that what
    the statid is for?"
    YES

    You export(wish they used different term like "save") them to a stat
    table and then you can exp them to a file you can use on another DB.

    If you create under SYS you could make application name part of the
    STATID.

    I think someone explained you may not always get same plan. One
    reason is CBO still looks at table HWM. But it's still worth doing.

    We've taken stats from test systems (close to production) and put
    them in production.

    May save time on upgrade or if you're changing the way you generate
    them.

    Normally an upgrade is not best time to change your methods.
    Of course, you back up your current stats first.

    Larry

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Freeman, Donald
    Sent: Friday, July 23, 2004 10:38 AM
    To: oracle-l_at_freelists.org
    Subject: RE: Creating Histograms

    I have Wolfgang's presentation from =
    http://www.centrexcc.com/SQL%20Tuning%20with%20Statistics.ppt.pdf but =
    have a question about exporting statistics. I understand that before I =
    start mucking about I need to save them. And, I'm gathering that each =
    time before we rerun them in our current Russian Roulette system we =
    should save them, just 'in case.'=20

    When creating the stats table to whom should I grant ownership? I =
    found an article with an example creating it under SYS schema in the =
    SYSTEM tablespace. Wolfgang's example creates it in Scott's schema and =
    tablespace. If this is going to be a production process should I set it =
    up under an application schema owner? Or does each application schema =
    owner need their own stats table? Can I keep multiple sets of statistics =
    for a single schema? Is that what the statid is for? Or, do I need to =
    export them to save them? I'm a little bit foggy on the big picture =
    here since I'm not currently doing any of this. Sorry if this is =
    sounding ignorant!

    I want to verify that if I export my production statistics to a test =
    machine I will get the same execution plan regardless of the fact that I =
    only have 10% of the data on that system. Right?

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    The information contained in this communication is
    confidential, is intended only for the use of the recipient
    named above, and may be legally privileged.
    If the reader of this message is not the intended
    recipient, you are hereby notified that any dissemination,
    distribution, or copying of this communication is strictly
    prohibited.
    If you have received this communication in error,
    please re-send this communication to the sender and
    delete the original message or any copy of it from your
    computer system. Thank You.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Tanel Põder at Jul 24, 2004 at 6:26 am

    I think someone explained you may not always get same plan. One
    reason is CBO still looks at table HWM. But it's still worth doing.
    CBO doesn't look at table's real HWM when optimizing a query, it uses the
    stats from the point in time when they were gathered. When stats (including
    system stats), optimizer parameters (including *_area_sizes) and real values
    in predicates stay constant, then the execution plan should stay the same
    for a given query, unless you encounter some bugs...

    Tanel.

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

Related Discussions

People

Translate

site design / logo © 2022 Grokbase