Re: Db Sequential Read
- From: walid alkaakati <walid_alkaakati@xxxxxxxxx>
- To: Ghassan Salem <salem.ghassan@xxxxxxxxx>
- Date: Sat, 20 Jan 2007 02:40:05 -0800 (PST)
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,
DECODE(a.INVOICE_DATE,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(a.INVOICE_DATE,'YY'),'RR')||'01','RRMM')),
SUM(a.PC_AMT)
FROM FG_ARTICLE_VD b, FG_SALES_VD a
WHERE ( ( b.ARTICLE_CODE = a.ARTICLE_CODE AND b.COMP_CODE = a.COMP_CODE
) )GROUP BY a.ARTICLE_CODE,b.BRAND,
DECODE(a.INVOICE_DATE,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(a.INVOICE_DATE,'YY'),'RR')||'01','RRMM'))
END OF STMT
PARSE
#1:c=15625,e=12153,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=4,tim=18446744072602999482
EXEC #1:c=0,e=230,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744072602999892
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=675562835 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 9415 p1=5 p2=81628 p3=5
WAIT #1: nam='db file scattered read' ela= 1390 p1=5 p2=81633 p3=8
WAIT #1: nam='db file sequential read' ela= 621 p1=5 p2=81642 p3=1
WAIT #1: nam='db file scattered read' ela= 971 p1=5 p2=81644 p3=5
WAIT #1: nam='db file scattered read' ela= 23165 p1=5 p2=81649 p3=8
WAIT #1: nam='db file scattered read' ela= 8570 p1=5 p2=81658 p3=7
WAIT #1: nam='db file scattered read' ela= 11413 p1=5 p2=81665 p3=8
WAIT #1: nam='db file scattered rea
.....................................................................
*** 2007-01-20 12:09:47.899
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)'
View fg_sales_vd has 458484 rows and fg_article has 129628 ,
when a join sales into article the time increases from 22 sec to 3 minutes.
Ghassan Salem <salem.ghassan@xxxxxxxxx> wrote:
Walid,
it would help if you specify the version, but in any case,
what are your pga_aggregate_target and db_file_multiblock_read_count values?
(hoping you're at least on 9i) increasing the first one should help in joins.
Then are you sure of the plan? execute the query with 10046 on (level 8) and
send back the tkprof result
rgds
On 1/20/07, walid alkaakati <walid_alkaakati@xxxxxxxxx> wrote: Hi ,
I need your help on tunning this query :
SELECT a.ARTICLE_CODE, b.BRAND,
DECODE(a.INVOICE_DATE,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(
a.INVOICE_DATE,'YY'),'RR')||'01','RRMM')), SUM(FG_SALES_VD.PC_AMT)
FROM FG_ARTICLE_VD b, FG_SALES_VD a
WHERE ( ( b.ARTICLE_CODE = a.ARTICLE_CODE AND b.COMP_CODE = a.COMP_CODE ) )
GROUP BY a.ARTICLE_CODE,a.BRAND,
DECODE(a.INVOICE_DATE,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(a.INVOICE_DATE,'YY'),'RR')||'01','RRMM'));
Explain plan:
SELECT STATEMENT
SORT GROUP BY
HASH JOIN
VIEW FG_SALES_VD
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL FGINV
TABLE ACCESS FULL FGLIINV
INDEX FAST FULL SCAN FGART_IDX_SEC
The query is taking about 3 minutes when I join article view to the sales
view, i did a trace and found that the following lines :
WAIT #1: nam='db file sequential read' ela= 8228 p1=5 p2=81898 p3=1
WAIT #1: nam='db file sequential read' ela= 7187 p1=5 p2=82226 p3=1
WAIT #1: nam='db file sequential read' ela= 4803 p1=5 p2=82288 p3=1
........
My question how i minimize the time needed for sequential read so that
blocks are read quickly ? or how to increase efficieny of hash join ?
Wating for your help .
---------------------------------
8:00? 8:25? 8:40? Find a flick in no time
with theYahoo! Search movie showtime shortcut.
---------------------------------
Expecting? Get great news right away with email Auto-Check.
Try the Yahoo! Mail Beta.
Other related posts:
