Re: oracle-l Digest V4 #21
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 21 Jan 2007 07:25:21 -0000
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@xxxxxxxxx>
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)'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: oracle-l Digest V4 #21
