RE: Dba_tab_modifications question

Chris, I remember from Metalink that you cannot use the 'gather stale' option in dbms_stats.gather_schema_stats. There is a bug in 8i and supposed to be fixed in 9i. So they still advise to run a job daily to gather the correct statistics.

Thank you Gopalakrishnan, Kirti and John for clearing my doubt!

Prakash

-----Original Message-----
Sent: Thursday, May 16, 2002 10:23 AM
To: Multiple recipients of list ORACLE-L

So DBA/ALL/USER_TAB_MODIFICATIONS cannot be used to determine an accurate count of how many records were updated, but it can used to determine if the table has been updated, and give you a general feel of how much has been updated.

AND is used by the GATHER STALE parameter in DBMS_STATS.GATHER_SCHEMA_STATS to determine which tables have been updated, and therefore may have stale statistics and need to have their stats refreshed.

With these features setup you can basically throw away your nightly "analyze everything" process and use a more intelligent approach. Very cool.

-----Original Message-----
Sent: Wednesday, May 15, 2002 9:28 PM
To: Multiple recipients of list ORACLE-L

True, there is such a latch (so, Gopal is right :) However, this latch is to protect the hash table structure where these modification counts are kept.
The updating of these counters is still done without acquiring any other latches (so, John is right :)
Also, a transaction can be rolled back, but the affected modification counts from this hash table can not be rolled back. So the modification counts can be different due to rolled back transactions and updating counters without latch protection (as John explained).

Cheers !

• Kirti

-----Original Message-----
Sent: Wednesday, May 15, 2002 6:18 PM
To: Multiple recipients of list ORACLE-L

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: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.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: 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).