Re: oracle-l Digest V4 #21

It appears that most of the time is spent in the first line of the plan.

> STAT #1 id=1 cnt=13516 pid=0 pos=1 obj=0 op='SORT GROUP BY (cr=1395707 r=30863
> w=13376 time=141370825 us)'
> STAT #1 id=2 cnt=458474 pid=1 pos=1 obj=0 op='HASH JOIN (cr=21565 r=30850
> w=13376 time=51353476 us)'

To aggregate 458,000 rows down to 13,000 you record an increment of about 90 seconds and 1,374,000 logical I./Os, for a change of only 16 physical reads.

Given the SQL you've shown us, I can't think of a good reason for this. It looks almost as if there is a scalar subquery lurking somewhere in the final steps of your query - but unless it's hidden in some way in one of your views I can't see how that would occur.

You might get a further clue if you start an SQL*Plus session, run the query, and then see what your session stats look like.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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

• Original Message -----
  >
  > Date: Sat, 20 Jan 2007 02:40:05 -0800 (PST)
  > From: walid alkaakati <walid_alkaakati_at_yahoo.com>
  > Subject: Re: Db Sequential Read
  >
  > Hi Ghassan Again !
  >
  > My db is Oracle9i Release 9.2.0.4.0 - on window 2000 server .
  > pga_aggregate_target : 177209344
  > db_file_multiblock_read_count :16
  > hash_area_size : 10048576
  > sort_area_size :524288 .
  > And begining and end of trace output is :
  >
  > SELECT a.ARTICLE_CODE, b.BRAND,
  > WAIT #1: nam='SQL*Net message from client' ela= 10872969 p1=675562835 p2=1
  > p3=0
  > STAT #1 id=1 cnt=13516 pid=0 pos=1 obj=0 op='SORT GROUP BY (cr=1395707 r=30863
  > w=13376 time=141370825 us)'
  > STAT #1 id=2 cnt=458474 pid=1 pos=1 obj=0 op='HASH JOIN (cr=21565 r=30850
  > w=13376 time=51353476 us)'
  > STAT #1 id=3 cnt=458474 pid=2 pos=1 obj=0 op='VIEW (cr=20239 r=27762 w=11609
  > time=45480186 us)'
  > STAT #1 id=4 cnt=458474 pid=3 pos=1 obj=0 op='SORT GROUP BY (cr=20239 r=27762
  > w=11609 time=44759719 us)'
  > STAT #1 id=5 cnt=462439 pid=4 pos=1 obj=0 op='HASH JOIN (cr=20239 r=18778
  > w=2625 time=29307158 us)'
  > STAT #1 id=6 cnt=239595 pid=5 pos=1 obj=9599 op='TABLE ACCESS FULL FGINV
  > (cr=7688 r=7596 w=0 time=8660167 us)'
  > STAT #1 id=7 cnt=462547 pid=5 pos=2 obj=9620 op='TABLE ACCESS FULL FGLIINV
  > (cr=12551 r=8557 w=0 time=15336905 us)'
  > STAT #1 id=8 cnt=129623 pid=2 pos=2 obj=9447 op='INDEX FAST FULL SCAN
  > FGART_IDX_SEC (cr=1326 r=1321 w=0 time=1813319 us)'
  >
  

--
http://www.freelists.org/webpage/oracle-l