I could be mistaken, but the query appears odd. Isn't the condition "and p.business_country_id in ( select countryabbrev from c )" nonsensical/superfluous in light of the condition "and p.business_country_id=c.countryabbrev" ?
select countryname, e.lastupdatedate
from e e, p p, c c
where p.pid = e.pid
and p.hsbc_user_category='GIB'
and p.business_country_id=c.countryabbrev
and e.userstatusid in ( select userstatusid from userstatus )
and p.business_country_id in ( select countryabbrev from c )
order by countryname, e.lastupdatedate descAt 06:59 PM 12/2/2003, you wrote:
Hi,
It is spending a lot of time waiting for IO and something like that.
If you want to see what is the session waiting for ,just do:
alter session set timed_statistics = true; (ignore it if it is already true)
alter session set events '10046 trace name context forever,level 8';
--do your sql here.
find the trace file and tkprof(use oracle 9.2 tkprof if your oracle version is not 9.2, not sure 9.0 will work)it like:
tkprof file=your_tracefile waits=y
For your SQL, I think more hash_join should be used instead of nested loop. Try it.
regards Zhu Chao
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
