Re: Db Sequential Read

From: walid alkaakati <walid_alkaakati_at_yahoo.com>
Date: Sat, 20 Jan 2007 02:40:05 -0800 (PST)
Message-ID: <669104.64044.qm@web61114.mail.yahoo.com>

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_at_gmail.com> 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_at_yahoo.com> 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.

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

Received on Sat Jan 20 2007 - 04:40:05 CST