Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Db Sequential Read

Re: Db Sequential Read

From: Ghassan Salem <salem.ghassan_at_gmail.com>
Date: Sat, 20 Jan 2007 10:29:08 +0100
Message-ID: <411d50f60701200129u488c70e9n2476b363463c096c@mail.gmail.com>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jan 20 2007 - 03:29:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US