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

I didn't think so, but then as I was ignorant of bitand(flag,power(2,19) > 0, I thought I'd check. I was looking for a cheap way of producing a report of full table scans including the statements which produced them. It seems the only way to do so is through explaining the SQL..  

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

-----Original Message-----

Sent: Thursday, March 14, 2002 3:26 AM
To: Multiple recipients of list ORACLE-L

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 ----- To: Multiple recipients of list ORACLE-L <mailto:ORACLE-L_at_fatcity.com> Sent: Tuesday, March 12, 2002 10:48 PM

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 recipients of list <mailto:ORACLE-L_at_fatcity.com> 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).