RE: Need advice on dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
Date: Fri, 9 Dec 2011 15:15:11 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4454E4764_at_LITIGMBCRP02.Corp.Acxiom.net>
> 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_at_acxiom.com 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 09 2011 - 09:15:11 CST