Re: Help on tkprof output

I'm not so sure. The query returns no rows and the second to last nested loop already has only 1 row in the resultset. I'd try to determine what the most limiting condition is - or set of conditions - those that eliminate most rows early on and make sure the optimizer starts with that.

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 desc

At 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: breitliw_at_centrexcc.com

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