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

[K Gopalakrishnan]

Alex:   Thanks for the corrections.   Best Regards, K Gopalakrishnan Bangalore, INDIA -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Alex Feinstein 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: 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 ----- 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:   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----- From: Ganesh Raja [mailto:[EMAIL PROTECTED]] 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:[EMAIL PROTECTED]] 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