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

Sent: Monday, March 11, 2002 9:58 PM
To: Multiple recipients of list ORACLE-L

Gopal, Ganesh, ...  

Small correction:

1. v$session_event does not have file#, block# and blocks. They are in v$session_wait, which may be hard to catch.
2. 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).
4. This give accurate info to the file level, not segment level.
 

Alex.  

• Original Message ----- To: Multiple <mailto:ORACLE-L_at_fatcity.com> recipients of list ORACLE-L Sent: Monday, March 11, 2002 11:07 AM

Ganesh,  

You can find FTS in the following methods:  

1. 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.
2. 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
3. THen the normal SQL Trace with TKPROF
4. 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-----

Sent: Monday, March 11, 2002 1:12 AM
To: LazyDBA.com Discussion

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

Sent: Monday, March 11, 2002 1:02 PM
To: LazyDBA.com Discussion

USE analyze table with compute statistcs.... Thanks and regards,
Vipin Jain
----- Original Message -----

To: LazyDBA.com Discussion <mailto:oracledba_at_lazydba.com> Sent: Monday, March 11, 2002 2:20 PM

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: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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