Oracle-L: Re: how to know ,the tables which are accessed via FTS?

From: Alex Feinstein <alexf1_at_san.rr.com>
Date: Thu, 14 Mar 2002 03:26:00 -0800
Message-ID: <F001.00428C9D.20020314032600@fatcity.com>

Ian,

There is no relation between data block in the buffer cache and statement that actually read it. For example, if you are about to read 8 blocks (scattered read), and block #3 already in the buffer cache in consistence state, your I/O request will be broken up for 2(1-2) and 5(4-8) blocks. Interesting exercise will be found segments, which experience both type of I/O (sequential and scattered). You may found state of the block (CR or CUR), except segment header which always read in CUR mode, "SELECT" read in CR, while "INSERT/UPDATE/DELETE" in CUR. Not much help, I know.

Alex.

Original Message ----- From: MacGregor, Ian A. To: Multiple recipients of list ORACLE-L Sent: Tuesday, March 12, 2002 10:48 PM Subject: RE: how to know ,the tables which are accessed via FTS?

Thanks Alex! The second method is great. However knowing what tables are undergoing a full table scan is only a part of what's needed. One also needs to know the size of the table; i.e., blocks up to the highwater mark, and the statement which caused the scan in the first place. The first I can determine. Can the second be determined without explaining the statements in the sql cache?

  Ian MacGregor
  Stanford Linear Accelerator Center
  ian_at_SLAC.Stanford.edu
    -----Original Message-----
    From: Alex Feinstein [mailto:alexf1_at_san.rr.com]     Sent: Monday, March 11, 2002 9:58 PM     To: Multiple recipients of list ORACLE-L     Subject: Re: how to know ,the tables which are accessed via FTS?

Gopal, Ganesh, ...

Small correction:

v$session_event does not have file#, block# and blocks. They are in v$session_wait, which may be hard to catch.
Absolute best methode. But field name is "flag". not "class", you can use bitand(flag,power(2,19) > 0 as a condition. When join to dba_objects(user_objects) one should use field data_object_id, not object_id. Take care about clusters, as all object in cluster share the same data_object_id. Anothe (but slower) way is join with dba_extents(user_extents).
This give accurate info to the file level, not segment level.

Alex.

Original Message ----- From: K Gopalakrishnan To: Multiple recipients of list ORACLE-L Sent: Monday, March 11, 2002 11:07 AM Subject: RE: how to know ,the tables which are accessed via FTS?

Ganesh,

You can find FTS in the following methods:

THe existence of the 'db file scattered read' in the V$session_event. From this we can find the file#, Block# and #of Blocks. Using this info we can get the segment name from the dictionary.
ANy block read using Sequential Scan (Full Table Scan) will be flagged as 0x80000 in the X$BH.CLASS. So existence of i0x80000 in X$.BH tells there is an FTS in the buffer cache. So from X$BH we can get the object name from the user_object by joining X$.BH.OBJ
THen the normal SQL Trace with TKPROF
If the KCFIOPBR>KCFIOPYRin the X$KCFIO (exposed as V$FILESTAT as PHYSICAL READS and PHYSICAL BLOCK READS) then some of the segments in that datafile is read by FTS. (THis may not give 100% accurate info) 5.. Any other thoughts?

      Best Regards,
      K Gopalakrishnan
      Bangalore, INDIA



        -----Original Message-----
        From: Ganesh Raja [mailto:ganesh_at_gtfs-gulf.com]
        Sent: Monday, March 11, 2002 1:12 AM
        To: LazyDBA.com Discussion
        Subject: RE: how to know ,the tables which are accessed via FTS?


        What will that Acheive... this will not give u Tables that have FTS Done on them.

        Best Regards, 
        Ganesh R 
        Tel  : +971 (4)  397 3337  Ext 420 
        Fax : +971 (4)  397 6262 
        HP  : +971 (50) 7456019 
          -----Original Message-----
          From: vipin jain [mailto:vipin.jain_at_tatainfotech.com]
          Sent: Monday, March 11, 2002 1:02 PM
          To: LazyDBA.com Discussion
          Subject: Re: how to know ,the tables which are accessed via FTS?


          USE analyze table with compute statistcs....
          Thanks and regards,
          Vipin Jain
          ----- Original Message ----- 
            From: Atul Gupta 
            To: LazyDBA.com Discussion 
            Sent: Monday, March 11, 2002 2:20 PM
            Subject: how to know ,the tables which are accessed via FTS?


            Hi Gurus  ,

              One little question 
            how will i get to know what all tables are accessed via full table scan ?

            any help will be highly appreciated 


            Thnax in advance
            Atul Gupta

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Alex Feinstein
INET: alexf1_at_san.rr.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 Mar 14 2002 - 05:26:00 CST