RE: Dba_tab_modifications question

Hi KG,

There isn't much docs on the internals of DBA_TAB_MODIFICATIONS. Even the MetaStink Analysts don't seem to know... So I have to depend on Steve 'Guru' Adams who says and I quote:

The modification counts are maintained in an efficient hash table in the SGA, and are updated without the protection of a latch (although the structure of the hash table itself is protected by the hash table modification latch). Even in heavy OLTP environments, the cost of maintaining the modification counts is likely to be less than 1% of additional CPU usage. However, because of the latch-free nature of the feature, the modification counts are not guaranteed to be accurate. Another source of potential inaccuracy is that if a transaction is rolled back, its changes to the modification counts are not rolled back as well. These inaccuracies have been allowed by Oracle to keep the performance overhead of this feature minimal. Therefore, you can use modification monitoring with confidence that it will not affect performance significantly, while giving you a very helpful indication of which tables may have stale statistics.

I.e. the hash table structure is protected by this latch and not the contents. If I assume correctly (and you may correct me here!) this latch is obtained only when adding to or removing from (or altering in any other way) the modifications counter table. Hence, when the kernel wants to update the counts, it is not latched and thus will not further degrade DML activity. And because it is not latched, the counts may be inaccurate if two processes were modifying the same table at the same time....

I hope this makes it clearer!

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: K Gopalakrishnan [mailto:kaygopal_at_yahoo.com]
> Sent: Wednesday, May 15, 2002 4:18 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Dba_tab_modifications question
>
>
> John:
>
> Not being so choosy, MONITORING is subject to latching. There
> is a latch
> called 'hash table allocation/modification latch'
> which keeps the modification in the shared pool and SMON periodically
> flushes to the disk.
>
>
> Best Regards,
> K Gopalakrishnan
> Bangalore, INDIA
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, May 16, 2002 4:23 AM
>
>
> > Prakash,
> >
> > My understanding is that the updation of counts for
> MONITORed tables is
> done
> > without using latching, so that normal DML is not held up by some
> additional
> > latches. This will explain the small difference that you
> are seeing, i.e.
> > the counting of some INSERTs were missed due to race
> conditions that could
> > have otherwise been prevented by latches.
> >
> > Am I as clear as mud or what!
> >
> > John Kanagaraj
> > Oracle Applications DBA
> > DBSoft Inc
> > (W): 408-970-7002
> >
> > The manuals for Oracle are here: http://tahiti.oracle.com
> > The manual for Life is here: http://www.gospelcom.net
> >
> > ** The opinions and statements above are entirely my own
> and not those of
> my
> > employer or clients **
> >
> >
> > > -----Original Message-----
> > > From: Grabowy, Chris [mailto:cgrabowy_at_fcg.com]
> > > Sent: Wednesday, May 15, 2002 12:33 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: Dba_tab_modifications question
> > >
> > >
> > > Hey Prakash,
> > >
> > > I never knew about that dictionary table, so I looked it up
> > > and found...
> > >
> > > These views describe tables that have been modified since the
> > > last time
> > > table statistics were gathered on them. The views are
> > > populated only for
> > > tables with the MONITORING attribute. They are not populated
> > > immediately,
> > > but after a time lapse (usually 3 hours).
> > >
> > > Perhaps that explains the diff. Check it out.
> > >
> > > Chris
> > >
> > > -----Original Message-----
> > > Sent: Wednesday, May 15, 2002 1:03 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Hello,
> > >
> > > Oracle 8.1.6 on HP-UX 11.0
> > >
> > > WFM_ADMIN_at_VGRAFO> select num_rows,last_analyzed,monitoring
> > > from user_tables
> > > where table_name = 'NOTES_LOG';
> > >
> > > NUM_ROWS LAST_ANAL MON
> > > ------------------- ---------------- -------
> > > 1585697 14-MAY-02 YES
> > >
> > > Last night, Informatica inserted rows into this table.
> > >
> > > 1 select inserts,updates,deletes from dba_tab_modifications
> > > 2* where table_name = 'NOTES_LOG'
> > > WFM_ADMIN_at_VGRAFO> /
> > >
> > > INSERTS UPDATES DELETES
> > > -------------- -------------- ---------------
> > > 6509 0 0
> > >
> > > WFM_ADMIN_at_VGRAFO> select count(*) from notes_log;
> > >
> > > COUNT(*)
> > > ----------
> > > 1592488
> > >
> > > The difference between yesterday's and today's count is 6791
> > > which does not
> > > match the number in dba_tab_modifications.
> > >
> > > Does this mean that I cannot rely on dba_tab_modifications?
> > >
> > >
> > > TIA
> > > Prakash
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: BALA,PRAKASH (Non-HP-USA,ex1)
> > > INET: prakash_bala_at_non.hp.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> > > San Diego, California -- Public Internet access /
> Mailing Lists
> > >
> --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed
> from). You may
> > > also send the HELP command for other information (like
> subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Grabowy, Chris
> > > INET: cgrabowy_at_fcg.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> > > San Diego, California -- Public Internet access /
> Mailing Lists
> > >
> --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed
> from). You may
> > > also send the HELP command for other information (like
> subscribing).
> > >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: John Kanagaraj
> > INET: john.kanagaraj_at_hds.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: K Gopalakrishnan
> INET: kaygopal_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).