RE: Need advice on dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

  • From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
  • To: "shastry17@xxxxxxxxx" <shastry17@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 9 Dec 2011 15:15:11 +0000

> One of my apps user is faced the problem as the 'USER_TAB_MODIFICATIONS' 
> table is not giving the list of partition names when they are trying to
> analyze the partitions.

I assume you mean that the apps user is running something that performs x 
amount of DML against a table's partition and after that activity is committed, 
he's not seeing updates of those DML counts in the view USER_TAB_MODIFICATIONS? 
 Correct?  First validate that the user is modifying an object in their own 
schema, because if they modify an object in another schema those DML counts 
will never show up in USER_TAB_MODIFICATIONS.

DML stats are flushed periodically by Oracle (MOS doc 762738.1 says every 3 
hrs), so activity similar to what procedure FLUSH_DATABASE_MONITORING_INFO is 
doing is happening regularly anyway.  I guess any potential performance impact 
depends on how often they think they need to run this procedure.  Regardless, 
the apps folks should just be gathering stats, if necessary, whenever any DML 
activity has completed and they're pretty sure new stats are important.

View USER_TAB_STATISTICS has a column STALE_STATS so you don't have to do the 
math off USER_TAB_MODIFICATIONS.  But again, this should really be something 
done as part of regular maintenance jobs vs. an app needing it.

DAVID HERRING
DBA
Acxiom Corporation
EML   dave.herring@xxxxxxxxxx
TEL    630.944.4762
MBL   630.430.5988 
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM  

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 resend this communication to the sender and delete the original message 
or any copy of it from your computer system. Thank you.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Shastry(DBA)
Sent: Thursday, December 08, 2011 6:25 AM
To: oracle-l
Subject: Need advice on dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

Hi Gurus,
One of my apps user is faced the problem as the 'USER_TAB_MODIFICATIONS'
table is not giving the list of partition names when they are trying to
analyze the partitions.



They are requesting whether the following code to be used is useful:

"dbms_stats.FLUSH_DATABASE_MONITORING_INFO();"



As flushing the database monitoring info in the database level will
internally updates the table level modified info in
"USER_TAB_MODIFICATIONS".

 Can the "dbms_stats.FLUSH_DATABASE_MONITORING_INFO();" is used in their
jobs, if it is used does it creates any performance issues, since it is
flushing the entire database monitoring info? Can the ANALYZE_ANY system
privilege be given to execute from other non-sys/system privileged schemas

4) Do we have any other table option in Oracle 11g to use, instead of
'USER_TAB_MODIFICATIONS' which will support the requirement?


Need your advice

Thanks,
shastry


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


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


Other related posts: