Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Dba_tab_modifications question

RE: Dba_tab_modifications question

From: Kathy Duret <KathyD_at_belkin.com>
Date: Thu, 16 May 2002 09:48:07 -0800
Message-ID: <F001.0046293C.20020516094807@fatcity.com>


You need to check dba_part% views to see information regarding partitioned tables.

VIEW_NAME                                                                       
------------------------------                                                  
DBA_PARTIAL_DROP_TABS                                                           
DBA_PART_COL_STATISTICS                                                         
DBA_PART_HISTOGRAMS                                                             
DBA_PART_INDEXES                                                                
DBA_PART_KEY_COLUMNS                                                            
DBA_PART_LOBS                                                                   
DBA_PART_TABLES       

To turn on monitoring on Partions it is the same... ALTER TABLE "MSC"."MSC_BOMS" MONITORING Regards,

Kathy

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

Chris,

Do you know anything about monitoring and gathering stale statistics on table partitions?
I am able to monitor and gather stale statistics on partitioned tables at the table level
but don't seem to be doing so at the partition level.

I can't figure out how to alter my partitions to put them in monitor mode. Have you
ever done that? The documentation and metalink only shows the command for altering the table, there seems to be no partition clause for that command. I've
guessed at various syntaxes for the clause and none of them work.

Thanks,

Cherie Machler
Oracle DBA
Gelco Information Network

                                                                                                                 
                    "Grabowy,                                                                                    
                    Chris"               To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>     
                    <cgrabowy_at_fcg.       cc:                                                                     
                    com>                 Subject:     RE: Dba_tab_modifications question                         
                    Sent by:                                                                                     
                    root_at_fatcity.c                                                                               
                    om                                                                                           
                                                                                                                 
                                                                                                                 
                    05/16/02 09:23                                                                               
                    AM                                                                                           
                    Please respond                                                                               
                    to 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 !

-----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

> 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: 
  INET: Cherie_Machler_at_gelco.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).

Confidential
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed.  If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kathy Duret
  INET: KathyD_at_belkin.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).
Received on Thu May 16 2002 - 12:48:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US