FAQ
Here's my stupid question for the week-  I'm sure I'm just missing something here...

Oracle 10.2.0.4 on Linux-  I'm under the impression from all that I've read, that the DBA_TAB_MODIFICATIONS table contains tables, along with row count changes for tables that are considered "stale" and need to have stats gathered.

Should the OCM auto job for gathering stats, that's driven off the DBA_TAB_MODIFICATIONS table, clear ALL objects that are in that table once it runs on it's schedule?

I have tables back from the conception of the database in DBA_TAB_MODIFICATIONS.  No gathering of stats have been taken by the auto stats job.  This is both for the SYS and the application schema, so it's not just at a level where I would look for a setting that says, "don't gather stats on system schema objects".

I've verified that if I gather stats on a table that is in DBA_TAB_MODIFICATIONS, it updates and removes the entry from the table, so it's not an issue of not updating the table and the stats job doing it's part-  it simply is disregarding certain tables in the DBA_TAB_MODIFICATIONS table.  The jobs are scheduled precisely as they should and are running to completion, no issues there.

There isn't a lot of documentation on the OCM stats module, but maybe I'm not searching for the right terms...

Can anyone tell me-  is there a bug in 10g for this feature?  Is this table utilized differently than what I think it is? Is Kellyn just not searching with the right terminology? :)

Thanks!

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com

"Go away before I replace you with a very small and efficient shell script..."

Search Discussions

  • Adam Musch at Feb 19, 2010 at 4:55 pm
    Are the stats on those tables locked (STATTYPE_LOCKED in
    DBA_TAB_STATISTICS)?
    On Fri, Feb 19, 2010 at 10:47 AM, Kellyn Pedersen wrote:

    Here's my stupid question for the week- I'm sure I'm just missing
    something here...

    Oracle 10.2.0.4 on Linux- I'm under the impression from all that I've
    read, that the DBA_TAB_MODIFICATIONS table contains tables, along with row
    count changes for tables that are considered "stale" and need to have stats
    gathered.

    Should the OCM auto job for gathering stats, that's driven off the
    DBA_TAB_MODIFICATIONS table, clear ALL objects that are in that table once
    it runs on it's schedule?

    I have tables back from the conception of the database in
    DBA_TAB_MODIFICATIONS. No gathering of stats have been taken by the auto
    stats job. This is both for the SYS and the application schema, so it's not
    just at a level where I would look for a setting that says, "don't gather
    stats on system schema objects".

    I've verified that if I gather stats on a table that is in
    DBA_TAB_MODIFICATIONS, it updates and removes the entry from the table, so
    it's not an issue of not updating the table and the stats job doing it's
    part- it simply is disregarding certain tables in the DBA_TAB_MODIFICATIONS
    table. The jobs are scheduled precisely as they should and are running to
    completion, no issues there.

    There isn't a lot of documentation on the OCM stats module, but maybe I'm
    not searching for the right terms...

    Can anyone tell me- is there a bug in 10g for this feature? Is this table
    utilized differently than what I think it is? Is Kellyn just not searching
    with the right terminology? :)
    Thanks!

    Kellyn Pedersen
    Multi-Platform DBA
    I-Behavior Inc.
    http://www.linkedin.com/in/kellynpedersen
    www.dbakevlar.blogspot.com

    "Go away before I replace you with a very small and efficient shell
    script..."
    --
    Adam Musch
    ahmusch_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Kellyn Pedersen at Feb 19, 2010 at 5:33 pm
    Good thought, but nope, not in this environment, no stats are locked in this OLTP database...

    I have this problem in all my databases and for the other databases that I manage exclusively, I've moved to my own scripts that utilize this table as it's source, but I really want to know what's up and why the OCM auto stats job doesn't clear this table out..:(

    The other issue I have, is that the job that inserts into DBA_TAB_MODIFICATIONS appears to scan different areas of the database in certain intervals, so it can suddenly populate up to 14,000 rows to gather stats on when there are many partitions involved, (I would think that a partition level trigger to populate would be more efficient...)

    Due to this, I have my script looking at the row count by date and if it reaches a threshold, it will exit and keep that days updates for the weekend run to not impact production.  To me, that just seems like a bug-  to not view the staleness at a partition level vs. table level...

    Kellyn

    On Fri, 2/19/10, Adam Musch wrote:

    From: Adam Musch
    Subject: Re: OCM Stats Module and DBA_TAB_MODIFICATIONS
    To: kjped1313_at_yahoo.com
    Cc: "oracle Freelists"
    Date: Friday, February 19, 2010, 9:55 AM

    Are the stats on those tables locked (STATTYPE_LOCKED in DBA_TAB_STATISTICS)?

    On Fri, Feb 19, 2010 at 10:47 AM, Kellyn Pedersen wrote:

    Here's my stupid question for the week-  I'm sure I'm just missing something here...

    Oracle 10.2.0.4 on Linux-  I'm under the impression from all that I've read, that the DBA_TAB_MODIFICATIONS table contains tables, along with row count changes for tables that are considered "stale" and need to have stats gathered.

    Should the OCM auto job for gathering stats, that's driven off the DBA_TAB_MODIFICATIONS table, clear ALL objects that are in that table once it runs on it's schedule?

    I have tables back from the conception of the database in DBA_TAB_MODIFICATIONS.  No gathering of stats have been taken by the auto stats job.  This is both for the SYS and the application schema, so it's not just at a level where I would look for a setting that says, "don't gather stats on system schema objects".

    I've verified that if I gather stats on a table that is in DBA_TAB_MODIFICATIONS, it updates and removes the entry from the table, so it's not an issue of not updating the table and the stats job doing it's part-  it simply is disregarding certain tables in the DBA_TAB_MODIFICATIONS table.  The jobs are scheduled precisely as they should and are running to completion, no issues there.

    There isn't a lot of documentation on the OCM stats module, but maybe I'm not searching for the right terms...

    Can anyone tell me-  is there a bug in 10g for this feature?  Is this table utilized differently than what I think it is? Is Kellyn just not searching with the right terminology? :)

    Thanks!

    Kellyn Pedersen
    Multi-Platform DBA
    I-Behavior Inc.
    http://www.linkedin.com/in/kellynpedersen
    www.dbakevlar.blogspot.com

    "Go away before I replace you with a very small and efficient shell script..."

    --
    Adam Musch
    ahmusch_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Adam Musch at Feb 19, 2010 at 6:52 pm
    From what I can tell, the OCM job isn't supposed to. In my systems,
    MGMT_STATS_CONFIG_JOB invokes ORACLE_OCM.MGMT_CONFIG.collect_stats, which
    submits this to run on each instance:

    ORACLE_OCM.MGMT_DB_LL_METRICS.collect_stats_metrics('ORACLE_OCM_CONFIG_DIR')

    That' puts out to a file in the directory the results of

    MGMT_DB_LL_METRICS.collect_db_feature_usage;
    MGMT_DB_LL_METRICS.collect_high_water_mark_stats;
    MGMT_DB_LL_METRICS.collect_db_cpu_usage;

    That job is all about monitoring what features have of the database have
    been used, I suppose for automatic licensure compliance. It's not about
    gathering database optimizer stats, that's SYS.GATHER_STATS_JOB.

    That job is subject to the maintenance windows, and if you have too many
    objects which need to have their statistics gathered -- after all, Oracle's
    being helpful with their defaults of gathering histograms all over creation
    according to undocumented logic -- you may have a problem where (defaults in
    parentheses):

    The stats job kicks off at the start of the maintenance window (10pm
    local M-F; 12 AM Saturday)
    The stats job iterates over each table in DBA_TAB_MODIFICATIONS,
    serially -- one segment at a time -- gathering table, table partition, table
    subpartition, index, index partition, and index subpartition statistics,
    including histograms. This appears to be in schema-table order. As each
    table finishes, it's removed from the DBA_TAB_MODIFICATIONS view.
    It gathers what it can until the window closes (6am local; 6AM Monday) .

    The next window, it starts over. However, any tables which didn't get to in
    the previous run didn't get gathered, and if there's enough activity (approx
    10% of table volume inserted/updated/deleted and/or table truncated), a
    table which got gathered yesterday will jump ahead of tables which got
    skipped in the previous run.

    You may wish to issue a call to DBMS_STATS.FLUSH_DATABASE_MONITORING and get
    a snapshot of what those views looked like at the start and end of the
    maintenance window to see what tables getting fresher statistics. Also, the
    LAST_ANALYZED column in DBA_(TABLES | INDEXES | TAB_PARTITIONS |
    TAB_SUBPARTITIONS | IND_PARTITIONS | IND_SUBPARTITIONS) might be useful to
    see what got touched. If the job isn't finishing, you should get rows in
    DBA_SCHEDULER_JOB_LOG where JOB_NAME = 'GATHER_STATS_JOB' and STATUS =
    'STOPPED'.

    The process Oracle delivered here, in short, is awful. It works fine for
    small systems, but for larger systems including partitioning and
    subpartitioning, the histogram defaults require reiterative scanning of
    partitions and tables as it doesn't do any rational digesting. It's
    riduclously underdocumented, to boot. I could go off on how evil histogram
    defaults are, especially in a world where you can't tell Oracle about column
    correlation and how badly that skews cardinality estimates, but that's for
    another day.
    On Fri, Feb 19, 2010 at 11:33 AM, Kellyn Pedersen wrote:

    Good thought, but nope, not in this environment, no stats are locked in
    this OLTP database...

    I have this problem in all my databases and for the other databases that I
    manage exclusively, I've moved to my own scripts that utilize this table as
    it's source, but I really want to know what's up and why the OCM auto stats
    job doesn't clear this table out..:(

    The other issue I have, is that the job that inserts into
    DBA_TAB_MODIFICATIONS appears to scan different areas of the database in
    certain intervals, so it can suddenly populate up to 14,000 rows to gather
    stats on when there are many partitions involved, (I would think that a
    partition level trigger to populate would be more efficient...)

    Due to this, I have my script looking at the row count by date and if it
    reaches a threshold, it will exit and keep that days updates for the weekend
    run to not impact production. To me, that just seems like a bug- to not
    view the staleness at a partition level vs. table level...

    Kellyn


    --- On *Fri, 2/19/10, Adam Musch * wrote:


    From: Adam Musch
    Subject: Re: OCM Stats Module and DBA_TAB_MODIFICATIONS
    To: kjped1313_at_yahoo.com
    Cc: "oracle Freelists"
    Date: Friday, February 19, 2010, 9:55 AM


    Are the stats on those tables locked (STATTYPE_LOCKED in
    DBA_TAB_STATISTICS)?
    On Fri, Feb 19, 2010 at 10:47 AM, Kellyn Pedersen http://us.mc320.mail.yahoo.com/mc/compose?to=kjped1313_at_yahoo.com>
    wrote:
    Here's my stupid question for the week- I'm sure I'm just missing
    something here...

    Oracle 10.2.0.4 on Linux- I'm under the impression from all that I've
    read, that the DBA_TAB_MODIFICATIONS table contains tables, along with row
    count changes for tables that are considered "stale" and need to have stats
    gathered.

    Should the OCM auto job for gathering stats, that's driven off the
    DBA_TAB_MODIFICATIONS table, clear ALL objects that are in that table once
    it runs on it's schedule?

    I have tables back from the conception of the database in
    DBA_TAB_MODIFICATIONS. No gathering of stats have been taken by the auto
    stats job. This is both for the SYS and the application schema, so it's not
    just at a level where I would look for a setting that says, "don't gather
    stats on system schema objects".

    I've verified that if I gather stats on a table that is in
    DBA_TAB_MODIFICATIONS, it updates and removes the entry from the table, so
    it's not an issue of not updating the table and the stats job doing it's
    part- it simply is disregarding certain tables in the DBA_TAB_MODIFICATIONS
    table. The jobs are scheduled precisely as they should and are running to
    completion, no issues there.

    There isn't a lot of documentation on the OCM stats module, but maybe I'm
    not searching for the right terms...

    Can anyone tell me- is there a bug in 10g for this feature? Is this
    table utilized differently than what I think it is? Is Kellyn just not
    searching with the right terminology? :)
    Thanks!

    Kellyn Pedersen
    Multi-Platform DBA
    I-Behavior Inc.
    http://www.linkedin.com/in/kellynpedersen
    www.dbakevlar.blogspot.com

    "Go away before I replace you with a very small and efficient shell
    script..."

    --
    Adam Musch
    ahmusch_at_gmail.com<http://us.mc320.mail.yahoo.com/mc/compose?to=ahmusch_at_gmail.com>

    --
    Adam Musch
    ahmusch_at_gmail.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Kellyn Pedersen at Feb 19, 2010 at 7:42 pm
    I should have clarified-  I meant the ORACLE_OCM- start to finish, up to the stats gathering step which you detailed below

    I think you just gave me my answer though:
    3.  It gathers what it can until the window closes (6am local; 6AM Monday) .

    I am hitting into the close of the window on this database, (and this is the smallest of our databases!!), and it's making, (in my opinion) poor choices on the stats collection, including sample sizes on our very large databases and is never getting to a number of tables.  My scripts that use the DBA_TAB_MODIFICATIONS table as the source,  are able to process through the entire table since I specify more manageable sample sizes and better choices for method options...

    If you ever need anyone to "vent" to about the "evils" of this delivery, let me know, I'm right there with you and have disabled it on all my primary databases-  returning to my own statistics gathering, but utilizing the handy DBA_TAB_MODIFICATIONS table.  I just knew I was missing a piece to the puzzle as to why I have the table retention oddity!

    Thanks!
    Kellyn

    On Fri, 2/19/10, Adam Musch wrote:

    From: Adam Musch
    Subject: Re: OCM Stats Module and DBA_TAB_MODIFICATIONS
    To: "Kellyn Pedersen"
    Cc: "oracle Freelists"
    Date: Friday, February 19, 2010, 11:52 AM

    From what I can tell, the OCM job isn't supposed to.  In my systems, MGMT_STATS_CONFIG_JOB invokes ORACLE_OCM.MGMT_CONFIG.collect_stats, which submits this to run on each instance:

    ORACLE_OCM.MGMT_DB_LL_METRICS.collect_stats_metrics('ORACLE_OCM_CONFIG_DIR')

    That' puts out to a file in the directory the results of


    MGMT_DB_LL_METRICS.collect_db_feature_usage;
    MGMT_DB_LL_METRICS.collect_high_water_mark_stats;
    MGMT_DB_LL_METRICS.collect_db_cpu_usage;


    That job is all about monitoring what features have of the database have been used, I suppose for automatic licensure compliance.  It's not about gathering database optimizer stats, that's SYS.GATHER_STATS_JOB.

    That job is subject to the maintenance windows, and if you have too many objects which need to have their statistics gathered -- after all, Oracle's being helpful with their defaults of gathering histograms all over creation according to undocumented logic -- you may have a problem where (defaults in parentheses):


    1.  The stats job kicks off at the start of the maintenance window (10pm local M-F; 12 AM Saturday)
    2.  The stats job iterates over each table in DBA_TAB_MODIFICATIONS, serially -- one segment at a time -- gathering table, table partition, table subpartition, index, index partition, and index subpartition statistics, including histograms.  This appears to be in schema-table order.  As each table finishes, it's removed from the DBA_TAB_MODIFICATIONS view.
    3.  It gathers what it can until the window closes (6am local; 6AM Monday) .


    The next window, it starts over.  However, any tables which didn't get to in the previous run didn't get gathered, and if there's enough activity (approx 10% of table volume inserted/updated/deleted and/or table truncated), a table which got gathered yesterday will jump ahead of tables which got skipped in the previous run.

    You may wish to issue a call to DBMS_STATS.FLUSH_DATABASE_MONITORING and get a snapshot of what those views looked like at the start and end of the maintenance window to see what tables getting fresher statistics.  Also, the LAST_ANALYZED column in DBA_(TABLES | INDEXES | TAB_PARTITIONS | TAB_SUBPARTITIONS | IND_PARTITIONS | IND_SUBPARTITIONS) might be useful to see what got touched.  If the job isn't finishing, you should get rows in DBA_SCHEDULER_JOB_LOG where JOB_NAME = 'GATHER_STATS_JOB' and STATUS = 'STOPPED'.

    The process Oracle delivered here, in short, is awful.  It works fine for small systems, but for larger systems including partitioning and subpartitioning, the histogram defaults require reiterative scanning of partitions and tables as it doesn't do any rational digesting.  It's riduclously underdocumented, to boot.  I could go off on how evil histogram defaults are, especially in a world where you can't tell Oracle about column correlation and how badly that skews cardinality estimates, but that's for another day.


    On Fri, Feb 19, 2010 at 11:33 AM, Kellyn Pedersen wrote:

    Good thought, but nope, not in this environment, no stats are locked in this OLTP database...

    I have this problem in all my databases and for the other databases that I manage exclusively, I've moved to my own scripts that utilize this table as it's source, but I really want to know what's up and why the OCM auto stats job doesn't clear this table out..:(

    The other issue I have, is that the job that inserts into DBA_TAB_MODIFICATIONS appears to scan different areas of the database in certain intervals, so it can suddenly populate up to 14,000 rows to gather stats on when there are many partitions involved, (I would think that a partition level trigger to populate would be more efficient...)

    Due to this, I have my script looking at the row count by date and if it reaches a threshold, it will exit and keep that days updates for the weekend run to not impact production.  To me, that just seems like a bug-  to not view the staleness at a partition level vs. table level...

    Kellyn

    On Fri, 2/19/10, Adam Musch wrote:

    From: Adam Musch
    Subject: Re: OCM Stats Module and DBA_TAB_MODIFICATIONS
    To: kjped1313_at_yahoo.com
    Cc: "oracle Freelists"
    Date: Friday, February 19, 2010, 9:55 AM

    Are the stats on those tables locked (STATTYPE_LOCKED in DBA_TAB_STATISTICS)?

    On Fri, Feb 19, 2010 at 10:47 AM, Kellyn Pedersen wrote:

    Here's my stupid question for the week-  I'm sure I'm just missing something here...

    Oracle 10.2.0.4 on Linux-  I'm under the impression from all that I've read, that the DBA_TAB_MODIFICATIONS table contains tables, along with row count changes for tables that are considered "stale" and need to have stats gathered.

    Should the OCM auto job for gathering stats, that's driven off the DBA_TAB_MODIFICATIONS table, clear ALL objects that are in that table once it runs on it's schedule?

    I have tables back from the conception of the database in DBA_TAB_MODIFICATIONS.  No gathering of stats have been taken by the auto stats job.  This is both for the SYS and the application schema, so it's not just at a level where I would look for a setting that says, "don't gather stats on system schema objects".

    I've verified that if I gather stats on a table that is in DBA_TAB_MODIFICATIONS, it updates and removes the entry from the table, so it's not an issue of not updating the table and the stats job doing it's part-  it simply is disregarding certain tables in the DBA_TAB_MODIFICATIONS table.  The jobs are scheduled precisely as they should and are running to completion, no issues there.

    There isn't a lot of documentation on the OCM stats module, but maybe I'm not searching for the right terms...

    Can anyone tell me-  is there a bug in 10g for this feature?  Is this table utilized differently than what I think it is? Is Kellyn just not searching with the right terminology? :)

    Thanks!

    Kellyn Pedersen
    Multi-Platform DBA
    I-Behavior Inc.
    http://www.linkedin.com/in/kellynpedersen
    www.dbakevlar.blogspot.com

    "Go away before I replace you with a very small and efficient shell script..."

    --
    Adam Musch
    ahmusch_at_gmail.com

    --
    Adam Musch
    ahmusch_at_gmail.com

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 19, '10 at 4:47p
activeFeb 19, '10 at 7:42p
posts5
users2
websiteoracle.com

2 users in discussion

Kellyn Pedersen: 3 posts Adam Musch: 2 posts

People

Translate

site design / logo © 2022 Grokbase