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" <[EMAIL PROTECTED]> 12/03/2002 05:53 PM
Sent by: [EMAIL PROTECTED]
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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-----
<[EMAIL PROTECTED]>
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
