Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: how to know ,the tables which are accessed via FTS?

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

From: <hemantchitale_at_charteredsemi.com>
Date: Sun, 17 Mar 2002 17:43:21 -0800
Message-ID: <F001.0042BAB5.20020317174321@fatcity.com> 





I've rewritten the query as :


select usr.name oowner, ob.name oname


  from


  (


  select obj


  from x$bh


  where to_number(bitand(flag, power(2,19))) > 0
  group by obj


  ) bh,


  obj$ ob,


  user$ usr


  where


  ob.dataobj# = bh.obj


  and ob.owner# = usr.user#


  order by usr.name, ob.name


  /



This query runs successsfully consistently.



I've also given credit to Jonathan in my update on the TAR.  The
Support Analyst might be writing a note on Metalink on the query.



Hemant K Chitale


Principal DBA


Chartered Semiconductor Manufacturing Ltd




CHITALE Hemant Krishnarao/IT/CHRT/ST Group_at_ST Domain    14/03/2002 11:48 AM
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?                                  
                                                                                                               
                                                                                                               
                                                                                                               










Jonathan,



I just saw your email.  I'll try your suggestions.



To report more on the query "crashing" ....



I had logged a TAR on discovering ORA-7445 errors in my alert.log
[For those who have access to Oracle Internal networks, the TAR# is
2220560.999].


The analyst was of t he opinion was that the trace was similar to
Bug #1571059 with a suggested workaround of event 10933 level 4096.
However, he wanted further investigation by me.
I then found tested different queries and found that this particular
query was causing the ORA-7445 and creating a 33MB user_dump


(of course, this is a busy database -- session dumps would be larger).

The analyst is working on it again with the new trace file.



I've retested my original script against less-busy  (Datawarehouse, Portal,
iFS)


databases running 8.1.7.0.0 on Solaris.


As you've pointed out it could be happening in my Apps instance
because of the "in-flux" nature of queries on X$BH  [it shouldn't be
because of 8.1.7.0.0 Solaris v 8.1.7.2.1 Tru64].



Coming back to your suggestion, I'll retry the query as you've suggested.



Hemant K Chitale


Principal DBA


Chartered Semiconductor Manufacturing Ltd




"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>     12/03/2002 05:53 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?










Whilst your query really ought not to crash,
bear in mind that X$ objects are not subject
to any form of normal read consistency, so
you may be hitting some strange side-effect
of in-flux blocks.



You may do better to reduce the run-time
of the query by doing the select and group
by on x$bh only as an inline view, and then
joining to dba_objects - and since you are joining
to x$bh why not join to obj$ ?



Something like:


select


from


    (


    select obj


    from x$bh


    where to_number(bitand(flag, power(2,19))) = 1
    group by obj


    ) bh,


    obj$ ob


where


    ob.dataobj# = bh.obj


;



By restricting the object_type to "TABLE"
you will be missing index fast full scans,
of course.  And won't you also miss


scans on partitioned objects and


clustered objects.





Jonathan Lewis


http://www.jlcomp.demon.co.uk



Next Seminar - UK, April 3rd - 5th


http://www.jlcomp.demon.co.uk/seminar.html



Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Author of:


Practical Oracle 8i: Building Efficient Databases




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


<hemantchitale_at_charteredsemi.com>


To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: 12 March 2002 07:46



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





--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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





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




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


Received on Sun Mar 17 2002 - 19:43:21 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US