How often should Oracle database statistics be run

Question

In your experience  how often should Oracle database statistics be run  Our team of developers recently discovered that statistics hadn t been run our production box in over 2 1 2 months  That sounds like a long time to me  but I m not a DBA

User · Answer

Make sure to balance the risk that fresh statistics cause undesirable changes to query plans against the risk that stale statistics can themselves cause query plans to change.

Imagine you have a bug database with a table ISSUE and a column CREATE_DATE where the values in the column increase more or less monotonically. Now, assume that there is a histogram on this column that tells Oracle that the values for this column are uniformly distributed between January 1, 2008 and September 17, 2008. This makes it possible for the optimizer to reasonably estimate the number of rows that would be returned if you were looking for all issues created last week (i.e. September 7 - 13). If the application continues to be used and the statistics are never updated, though, this histogram will be less and less accurate. So the optimizer will expect queries for "issues created last week" to be less and less accurate over time and may eventually cause Oracle to change the query plan negatively.

User · Answer

What Oracle version are you using  Check this page which refers to Oracle 10   http   www acs ilstu edu docs Oracle server 101 b10752 stats htm  It says      The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics  Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job

User · Answer

What Oracle version are you using  Check this page which refers to Oracle 10   http   www acs ilstu edu docs Oracle server 101 b10752 stats htm  It says      The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics  Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job

User · Answer

When I was managing a large multi-user planning system backed by Oracle  our DBA had a weekly job that gathered statistics   Also  when we rolled out a significant change that could affect or be affected by statistics  we would force the job to run out of cycle to get things caught up

User · Answer

Whenever the data changes  significantly    If a table goes from 1 row to 200 rows  that s a significant change   When a table goes from 100 000 rows to 150 000 rows  that s not a terribly significant change   When a table goes from 1000 rows all with identical values in commonly-queried column X to 1000 rows with nearly unique values in column X  that s a significant change   Statistics store information about item counts and relative frequencies -- things that will let it  guess  at how many rows will match a given criteria   When it guesses wrong  the optimizer can pick a very suboptimal query plan

User · Answer

With 10g and higher version of oracle  up to date statistics on tables and indexes are needed by the optimizer to make  good  execution plan decision   How often you collect statistics is a tricky call   It depends on your application  schema  data rate and business practice   Some third party apps which are written to be backward compatible with older version of oracle do not perform well with the new optimizer   Those application require that tables have no stats so that the db resorts back to rule base execution plan   But on the average oracle recommends that stats be collected on tables with stale statistics   You can set tables to be monitor and check their state and have them analyze if when stale   Often that is enough  sometime it is not   It really depend on your database   For my database we have a set of OLTP tables that need nightly stats collection to maintain performance   Other tables are analyze once a week   On our large dw database  we analyze as needed as the tables are too large for regular analysis without affecting overall db load and performance   So the correct answer is  it depends on the application  data change and business needs

User · Answer

At my last job we ran statistics once a week   If I remember correctly  we scheduled them on a Thursday night  and on Friday the DBAs were very careful to monitor the longest running queries for anything unexpected    Friday was picked because it was often just after a code release  and tended to be a fairly low traffic day    When they saw a bad query they would find a better query plan and save that one so it wouldn t change again unexpectedly    Oracle has tools to do this for you automatically  you tell it the query to optimize and it does    Many organizations avoid running statistics out of fear of bad query plans popping up unexpectedly   But this usually means that their query plans get worse and worse over time   And when they do run statistics then they encounter a number of problems   The resulting scramble to fix those issues confirms their fears about the dangers of running statistics   But if they ran statistics regularly  used the monitoring tools as they are supposed to  and fixed issues as they came up then they would have fewer headaches  and they wouldn t encounter them all at once

User · Answer

What Oracle version are you using  Check this page which refers to Oracle 10   http   www acs ilstu edu docs Oracle server 101 b10752 stats htm  It says      The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics  Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job

User · Answer

At my last job we ran statistics once a week   If I remember correctly  we scheduled them on a Thursday night  and on Friday the DBAs were very careful to monitor the longest running queries for anything unexpected    Friday was picked because it was often just after a code release  and tended to be a fairly low traffic day    When they saw a bad query they would find a better query plan and save that one so it wouldn t change again unexpectedly    Oracle has tools to do this for you automatically  you tell it the query to optimize and it does    Many organizations avoid running statistics out of fear of bad query plans popping up unexpectedly   But this usually means that their query plans get worse and worse over time   And when they do run statistics then they encounter a number of problems   The resulting scramble to fix those issues confirms their fears about the dangers of running statistics   But if they ran statistics regularly  used the monitoring tools as they are supposed to  and fixed issues as they came up then they would have fewer headaches  and they wouldn t encounter them all at once

User · Answer

When I was managing a large multi-user planning system backed by Oracle  our DBA had a weekly job that gathered statistics   Also  when we rolled out a significant change that could affect or be affected by statistics  we would force the job to run out of cycle to get things caught up

User · Answer

Whenever the data changes  significantly    If a table goes from 1 row to 200 rows  that s a significant change   When a table goes from 100 000 rows to 150 000 rows  that s not a terribly significant change   When a table goes from 1000 rows all with identical values in commonly-queried column X to 1000 rows with nearly unique values in column X  that s a significant change   Statistics store information about item counts and relative frequencies -- things that will let it  guess  at how many rows will match a given criteria   When it guesses wrong  the optimizer can pick a very suboptimal query plan

User · Answer

Whenever the data changes  significantly    If a table goes from 1 row to 200 rows  that s a significant change   When a table goes from 100 000 rows to 150 000 rows  that s not a terribly significant change   When a table goes from 1000 rows all with identical values in commonly-queried column X to 1000 rows with nearly unique values in column X  that s a significant change   Statistics store information about item counts and relative frequencies -- things that will let it  guess  at how many rows will match a given criteria   When it guesses wrong  the optimizer can pick a very suboptimal query plan

User · Answer

Generally it s not recommended to gather statistics so frequent on the whole database unless you have a strong justification for that  such as a bulk insert or big data change happen frequently on the database  gathering statistics on the database in this frequency MAY change the queries execution plan to a new poor execution plans  the thing may cost you much time trying to tune every query affected by the new poor plans  this is why you should test the impact of gathering new statistics on a test database  or in case you don t have the time or the man power for that  at least you should keep a fallback plan by backing up the original statics before you gather new ones  so in case you gather a new statistics and then the queries didn t perform as expected  you can easily restore back the original statistics   There is a very useful script can help you backup original statistics and gather new ones and provide you with SQL command you can use to restore back the original statics in case the thing didn t go as expected after gathering new statistics  You can find the script in this link  http   dba-tips blogspot com 2014 09 script-to-ease-gathering-statistics-on html

User · Answer

When I was managing a large multi-user planning system backed by Oracle  our DBA had a weekly job that gathered statistics   Also  when we rolled out a significant change that could affect or be affected by statistics  we would force the job to run out of cycle to get things caught up

User · Answer

In the case of a data warehouse-type system you can consider collecting no statistics at all  and relying on dynamic sampling  setting optimizer dynamic sampling to level 2 or above

User · Answer

Since Oracle 11g statistics are gathered automatically by default   Two Scheduler windows are predefined upon installation of Oracle Database    WEEKNIGHT WINDOW starts at 10 p m  and ends at 6 a m  every Monday through Friday   WEEKEND WINDOW covers whole days Saturday and Sunday    When statistics were last gathered   SELECT owner  table name  last analyzed FROM all tables ORDER BY last analyzed DESC NULLS LAST  --Tables  SELECT owner  index name  last analyzed FROM all indexes ORDER BY last analyzed DESC NULLS LAST  -- Indexes    Status of automated statistics gathering   SELECT   FROM dba autotask client WHERE client name    auto optimizer stats collection     Windows Groups   SELECT window group name  window name FROM dba scheduler wingroup members    Window Schedules   SELECT window name  start time  duration FROM dba autotask schedule    Manually gather Database Statistics in this Schema    EXEC dbms stats gather schema stats ownname  gt NULL  cascade  gt TRUE   -- cascade  gt TRUE means include Table Indexes too    Manually gather Database Statistics in all Schemas   -- Probably need to CONNECT   AS SYSDBA EXEC dbms stats gather database stats

User · Answer

Whenever the data changes  significantly    If a table goes from 1 row to 200 rows  that s a significant change   When a table goes from 100 000 rows to 150 000 rows  that s not a terribly significant change   When a table goes from 1000 rows all with identical values in commonly-queried column X to 1000 rows with nearly unique values in column X  that s a significant change   Statistics store information about item counts and relative frequencies -- things that will let it  guess  at how many rows will match a given criteria   When it guesses wrong  the optimizer can pick a very suboptimal query plan

User · Answer

Make sure to balance the risk that fresh statistics cause undesirable changes to query plans against the risk that stale statistics can themselves cause query plans to change.

Imagine you have a bug database with a table ISSUE and a column CREATE_DATE where the values in the column increase more or less monotonically. Now, assume that there is a histogram on this column that tells Oracle that the values for this column are uniformly distributed between January 1, 2008 and September 17, 2008. This makes it possible for the optimizer to reasonably estimate the number of rows that would be returned if you were looking for all issues created last week (i.e. September 7 - 13). If the application continues to be used and the statistics are never updated, though, this histogram will be less and less accurate. So the optimizer will expect queries for "issues created last week" to be less and less accurate over time and may eventually cause Oracle to change the query plan negatively.

User · Answer

At my last job we ran statistics once a week   If I remember correctly  we scheduled them on a Thursday night  and on Friday the DBAs were very careful to monitor the longest running queries for anything unexpected    Friday was picked because it was often just after a code release  and tended to be a fairly low traffic day    When they saw a bad query they would find a better query plan and save that one so it wouldn t change again unexpectedly    Oracle has tools to do this for you automatically  you tell it the query to optimize and it does    Many organizations avoid running statistics out of fear of bad query plans popping up unexpectedly   But this usually means that their query plans get worse and worse over time   And when they do run statistics then they encounter a number of problems   The resulting scramble to fix those issues confirms their fears about the dangers of running statistics   But if they ran statistics regularly  used the monitoring tools as they are supposed to  and fixed issues as they came up then they would have fewer headaches  and they wouldn t encounter them all at once

User · Answer

In the case of a data warehouse-type system you can consider collecting no statistics at all  and relying on dynamic sampling  setting optimizer dynamic sampling to level 2 or above

User · Answer

Make sure to balance the risk that fresh statistics cause undesirable changes to query plans against the risk that stale statistics can themselves cause query plans to change.

Imagine you have a bug database with a table ISSUE and a column CREATE_DATE where the values in the column increase more or less monotonically. Now, assume that there is a histogram on this column that tells Oracle that the values for this column are uniformly distributed between January 1, 2008 and September 17, 2008. This makes it possible for the optimizer to reasonably estimate the number of rows that would be returned if you were looking for all issues created last week (i.e. September 7 - 13). If the application continues to be used and the statistics are never updated, though, this histogram will be less and less accurate. So the optimizer will expect queries for "issues created last week" to be less and less accurate over time and may eventually cause Oracle to change the query plan negatively.

User · Answer

Generally it s not recommended to gather statistics so frequent on the whole database unless you have a strong justification for that  such as a bulk insert or big data change happen frequently on the database  gathering statistics on the database in this frequency MAY change the queries execution plan to a new poor execution plans  the thing may cost you much time trying to tune every query affected by the new poor plans  this is why you should test the impact of gathering new statistics on a test database  or in case you don t have the time or the man power for that  at least you should keep a fallback plan by backing up the original statics before you gather new ones  so in case you gather a new statistics and then the queries didn t perform as expected  you can easily restore back the original statistics   There is a very useful script can help you backup original statistics and gather new ones and provide you with SQL command you can use to restore back the original statics in case the thing didn t go as expected after gathering new statistics  You can find the script in this link  http   dba-tips blogspot com 2014 09 script-to-ease-gathering-statistics-on html

User · Answer

With 10g and higher version of oracle  up to date statistics on tables and indexes are needed by the optimizer to make  good  execution plan decision   How often you collect statistics is a tricky call   It depends on your application  schema  data rate and business practice   Some third party apps which are written to be backward compatible with older version of oracle do not perform well with the new optimizer   Those application require that tables have no stats so that the db resorts back to rule base execution plan   But on the average oracle recommends that stats be collected on tables with stale statistics   You can set tables to be monitor and check their state and have them analyze if when stale   Often that is enough  sometime it is not   It really depend on your database   For my database we have a set of OLTP tables that need nightly stats collection to maintain performance   Other tables are analyze once a week   On our large dw database  we analyze as needed as the tables are too large for regular analysis without affecting overall db load and performance   So the correct answer is  it depends on the application  data change and business needs

User · Answer

When I was managing a large multi-user planning system backed by Oracle  our DBA had a weekly job that gathered statistics   Also  when we rolled out a significant change that could affect or be affected by statistics  we would force the job to run out of cycle to get things caught up

User · Answer

What Oracle version are you using  Check this page which refers to Oracle 10   http   www acs ilstu edu docs Oracle server 101 b10752 stats htm  It says      The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics  Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job

User · Answer

At my last job we ran statistics once a week   If I remember correctly  we scheduled them on a Thursday night  and on Friday the DBAs were very careful to monitor the longest running queries for anything unexpected    Friday was picked because it was often just after a code release  and tended to be a fairly low traffic day    When they saw a bad query they would find a better query plan and save that one so it wouldn t change again unexpectedly    Oracle has tools to do this for you automatically  you tell it the query to optimize and it does    Many organizations avoid running statistics out of fear of bad query plans popping up unexpectedly   But this usually means that their query plans get worse and worse over time   And when they do run statistics then they encounter a number of problems   The resulting scramble to fix those issues confirms their fears about the dangers of running statistics   But if they ran statistics regularly  used the monitoring tools as they are supposed to  and fixed issues as they came up then they would have fewer headaches  and they wouldn t encounter them all at once

User · Answer

Make sure to balance the risk that fresh statistics cause undesirable changes to query plans against the risk that stale statistics can themselves cause query plans to change.

Imagine you have a bug database with a table ISSUE and a column CREATE_DATE where the values in the column increase more or less monotonically. Now, assume that there is a histogram on this column that tells Oracle that the values for this column are uniformly distributed between January 1, 2008 and September 17, 2008. This makes it possible for the optimizer to reasonably estimate the number of rows that would be returned if you were looking for all issues created last week (i.e. September 7 - 13). If the application continues to be used and the statistics are never updated, though, this histogram will be less and less accurate. So the optimizer will expect queries for "issues created last week" to be less and less accurate over time and may eventually cause Oracle to change the query plan negatively.

User · Answer

Since Oracle 11g statistics are gathered automatically by default   Two Scheduler windows are predefined upon installation of Oracle Database    WEEKNIGHT WINDOW starts at 10 p m  and ends at 6 a m  every Monday through Friday   WEEKEND WINDOW covers whole days Saturday and Sunday    When statistics were last gathered   SELECT owner  table name  last analyzed FROM all tables ORDER BY last analyzed DESC NULLS LAST  --Tables  SELECT owner  index name  last analyzed FROM all indexes ORDER BY last analyzed DESC NULLS LAST  -- Indexes    Status of automated statistics gathering   SELECT   FROM dba autotask client WHERE client name    auto optimizer stats collection     Windows Groups   SELECT window group name  window name FROM dba scheduler wingroup members    Window Schedules   SELECT window name  start time  duration FROM dba autotask schedule    Manually gather Database Statistics in this Schema    EXEC dbms stats gather schema stats ownname  gt NULL  cascade  gt TRUE   -- cascade  gt TRUE means include Table Indexes too    Manually gather Database Statistics in all Schemas   -- Probably need to CONNECT   AS SYSDBA EXEC dbms stats gather database stats

[database] How often should Oracle database statistics be run?

Examples related to database

Examples related to performance

Examples related to oracle

Examples related to statistics