Re: dba_tab_mod view

From: Ram Raman <veeeraman_at_gmail.com>
Date: Wed, 13 May 2015 15:25:43 -0500
Message-ID: <CAHSa0M2Dd4Jspak+aHvguuaV6m7xOYGLGnfAPCJneeKvs=ZrsA_at_mail.gmail.com>

Thanks Scott and Mladen. Flushing the DB_monitoring_info did it. (why didnt I think of it before?)

The monitoring is on (dbatables.monitoring=YES) for the table, stats level is 'typical'. As an aside, I remember reading somewhere (Martin wildlake's blog?) that even if you set the monitoring off for the tables, it will be ignored from 10g onwards and the update stats will be collected. Anyway, I can now see the updates in DTM view.

On Wed, May 13, 2015 at 1:54 PM, Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:

> Yes, the manual does say so, and I am aware of that. However, the manual
> is not always correct:
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
> Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
> SQL> select count(*) from dba_tables where monitoring='YES';
>
> COUNT(*)
> ----------
> 2802
>
> SQL> select count(*) from dba_tables;
>
> COUNT(*)
> ----------
> 2945
> SQL> select count(*) from dba_tables where monitoring='NO';
>
> COUNT(*)
> ----------
> 143
>
> There is a significant number of tables without the monitoring attribute,
> despite the fact that it is 11G and that the statistics level is "ALL"
> (development database, I want plan statistics for every plan).
>
> SQL> show parameter statistics_level
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> statistics_level string ALL
> SQL>
>
>
>
> There is also a possibility that the table was created by using DDL
> generated from Oracle 10G, with the "NOMONITORING" clause, in which case an
> explicit setting will take precedence over the default. Long story short, I
> always check the "MONITORING" attribute when dealing with
> DBA_TAB_MODIFICATIONS. One reason to make sure this is set is playing with
> the stale percent attribute. If you don't want a table frequently analyzed
> for any reason whatsoever, you can set stale percent to 99% and, in case of
> a very large table, have it always skipped. I did that because I have
> manually set clustering factor for one of the indexes, to make range scans
> less expensive. I know all about the best practices and am fully aware that
> this is not one of them.
>
>
>
> On 05/13/2015 09:46 AM, Deas, Scott wrote:
>
> Since OP mentioned that he was on 11.2, the setting of this value at a
> table level has been deprecated and is now controlled via the
> STATISTICS_LEVEL of the database -
> http://docs.oracle.com/cd/E25054_01/server.1111/e16638/stats.htm#autoId16
>
>
>
> If the STATISTICS_LEVEL is set to TYPICAL (default) or ALL, monitoring is
> enabled by default. Only if the OP had his database’s STATISTICS_LEVEL
> parameter set to BASIC would he not have any monitoring enabled.
>
>
>
> Thanks,
> Scott
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Mladen Gogala
> *Sent:* Tuesday, May 12, 2015 9:38 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: dba_tab_mod view
>
>
>
> Also, that will only work for tables that have MONITORING attribute set.
>
> On 05/12/2015 10:48 AM, Deas, Scott wrote:
>
> Ram,
>
>
>
> After you issue DML, but before you run statistics, you can run
> dbms_stats.flush_database_monitoring_info to update the views.
>
>
>
>
> http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68568
>
>
>
> Thanks
>
> Scott
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Ram Raman
> *Sent:* Monday, May 11, 2015 7:13 PM
> *To:* ORACLE-L
> *Subject:* dba_tab_mod view
>
>
>
> List,
>
>
>
> After seeing lots of global services enqueue deadlock errors in our rac, I
> was looking up the trace files. I was able to see the sql; the trace files
> are huge (in GBs) . The sql was simple and was updating one table only
> repeatedly. I wanted to know how many updates had happened in the table,
> but the dba_tab_mod view is empty for that table. The table was last
> analyzed last night. Does this mean that the updates did not happen at all
> due the deadlock errors?
>
>
>
> To my knowledge, the DTM view is emptied out after a stats collection. Is
> there anything else that would cause the no rows selected for a table from
> that view. v11.2
>
>
>
> Ram.
>
>
>
> Notice of Confidentiality: **This E-mail and any of its attachments may
> contain
> Lincoln National Corporation proprietary information, which is privileged,
> confidential,
> or subject to copyright belonging to the Lincoln National Corporation
> family of
> companies. This E-mail is intended solely for the use of the individual or
> entity to
> which it is addressed. If you are not the intended recipient of this
> E-mail, you are
> hereby notified that any dissemination, distribution, copying, or action
> taken in
> relation to the contents of and attachments to this E-mail is strictly
> prohibited
> and may be unlawful. If you have received this E-mail in error, please
> notify the
> sender immediately and permanently delete the original and any copy of
> this E-mail
> and any printout. Thank You.**
>
>
>
> --
> Mladen Gogala
> Oracle DBAhttp://mgogala.freehostia.com
>
>

-- 

You can become a doctor and then websearch for solutions; You cannot
websearch and become a doctor

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

Received on Wed May 13 2015 - 22:25:43 CEST