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

Oracle8i 8.1.7.2 on Tru64.

I've been using the query :
SELECT o.owner oowner, o.object_name oname  FROM dba_objects o,x$bh x
 WHERE x.obj=o.data_object_id
 AND o.object_type='TABLE'
 AND standard.bitand(x.flag,524288)>0
 AND o.owner<>'SYS'
 group by owner, object_name
 order by owner, object_name
/

This ends in "ORA-03113: end-of-file on communication channel" 3 out of 4 times
(but 1 out of 4 times I DO get the list of tables).
If I remember correctly it used to work in 8.0.5, except that I was joining x.obj against o.object_id.

However, on another 8.1.7.0 on Solaris 8 database I consistently get the list of Tables. The difference is that this database is less busy. The Tru64 database is my Oracle Applications Database.

Any ideas why I get the ORA-3113 on my Oracle Applications Tru64 database ? [other than that this is a really busy database].

Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd

"Alex Feinstein" <alexf1_at_san.rr.com> 12/03/2002 01:58 PM Sent by: root_at_fatcity.com

Please respond to ORACLE-L

                                                                                                               
             To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                                   
             cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)                                             
             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: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

[This e-mail is confidential and may also be privileged. If you are not the intended recipient, please delete it and notify us immediately; you should not copy or use it for any purpose, nor disclose its contents to any other person. Thank you.]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: hemantchitale_at_charteredsemi.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).