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

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Mon, 11 Mar 2002 11:07:04 -0800
Message-ID: <F001.00424CA4.20020311110704@fatcity.com>

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




_________________________________________________________

Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

Received on Mon Mar 11 2002 - 13:07:04 CST