Re: dba_tab_mod view
Date: Wed, 13 May 2015 14:54:19 -0400
Message-ID: <55539DDB.60809_at_yahoo.com>
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] *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>
> [mailto: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 DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 13 2015 - 20:54:19 CEST