| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Optimising the Query
pankaj_wolfhunter_at_yahoo.co.in wrote:
> Charles Hooper wrote:
> > I agree with Daniel Mogan, an explain plan would be helpful to see what
> > is happening. Slightly reformatting your SQL statement:
> > SELECT
> > TAB1.P_TDATE,
> > FLOOR(TAB1.STIME/100) T_TIME,
> > SUM (CASE WHEN
> > TAB1.LIND <> 1
> > AND (TAB2.CNAME NOT IN ('PN','AR')
> > OR NVL(TAB3.OMNI,0) <> 7
> > OR TAB1.TIF <> 3)
> > THEN 1
> > ELSE 0
> > END) ORDPOSTMKT,
> > SUM (CASE WHEN
> > (TAB2.CNAME IN ('PN','AR')
> > OR TAB3.OMNI = 7
> > OR TAB1.TIF = 3)
> > THEN 1
> > ELSE 0
> > END) ORDOARS
> > FROM
> > TABLE1 TAB1
> > LEFT OUTER JOIN (
> > (SELECT
> > ID,
> > P_TDATE,
> > SYMBOL,
> > REFNUMBER,
> > CNAME,
> > ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
> > FROM
> > TABLE2) TAB2
> > JOIN
> > (SELECT
> > ID,
> > P_TDATE,
> > SYMBOL,
> > OMNI,
> > REFNUMBER,
> > ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
> > FROM
> > TABLE3) TAB3
> > ON (TAB2.P_TDATE=TAB3.P_TDATE
> > AND TAB2.SYMBOL = TAB3.SYMBOL
> > AND TAB2.ID = TAB3.ID
> > AND TAB2.REFNUMBER = TAB3.REFNUMBER
> > AND TAB3.RANK = 1
> > AND TAB2.RANK = 1))
> > ON (TAB1.P_TDATE = TAB3.P_TDATE
> > AND TAB1.SYMBOL = TAB3.SYMBOL
> > AND TAB1.ID = TAB3.ID)
> > WHERE
> > TAB1.OIND <> 0
> > AND TAB1.OTYPE IN ('MR','GMR')
> > GROUP BY
> > TAB1.P_TDATE,
> > FLOOR(TAB1.STIME/100)
> > ORDER BY
> > T_TIME;
> >
> > You are perfoming two ROW_NUMBER analytical operations, and discarding
> > the results. The ROW_NUMBER operation is sorting quite a few rows and
> > this sort is likely hitting the temporary tablespace quite hard. Your
> > query includes TAB3.RANK = 1 and TAB2.RANK = 1, but not TAB3.RANK =
> > TAB2.RANK. The explain plan would indicate if you are performing a
> > full table scan on TABLE1's 46,697,622 rows.
> >
> > Check the size of your SORT_AREA_SIZE. Try rewriting the SQL statement
> > into an alternate form, similar to this:
> > SELECT
> > TAB1.P_TDATE,
> > FLOOR(TAB1.STIME/100) T_TIME,
> > SUM (CASE WHEN
> > TAB1.LIND <> 1
> > AND (TAB2.CNAME NOT IN ('PN','AR')
> > OR NVL(TAB3.OMNI,0) <> 7
> > OR TAB1.TIF <> 3)
> > THEN 1
> > ELSE 0
> > END) ORDPOSTMKT,
> > SUM (CASE WHEN
> > (TAB2.CNAME IN ('PN','AR')
> > OR TAB3.OMNI = 7
> > OR TAB1.TIF = 3)
> > THEN 1
> > ELSE 0
> > END) ORDOARS
> > FROM
> > TABLE1 TAB1,
> > TABLE2 TAB2,
> > TABLE3 TAB3
> > WHERE
> > TAB1.P_TDATE = TAB3.P_TDATE(+)
> > AND TAB1.SYMBOL = TAB3.SYMBOL(+)
> > AND TAB1.ID = TAB3.ID(+)
> > AND TAB3.P_TDATE = TAB2.P_TDATE(+)
> > AND TAB3.SYMBOL = TAB2.SYMBOL(+)
> > AND TAB3.ID = TAB2.ID(+)
> > AND TAB3.REFNUMBER = TAB2.REFNUMBER(+)
> > AND TAB3.RANK = TAB2.RANK(+)
> > AND TAB3.RANK(+) = 1
> > AND TAB2.RANK(+) = 1
> > AND TAB1.OIND <> 0
> > AND TAB1.OTYPE IN ('MR','GMR')
> > GROUP BY
> > TAB1.P_TDATE,
> > FLOOR(TAB1.STIME/100)
> > ORDER BY
> > T_TIME;
> >
> > Do you need the outer join? If possible, remove that and performance
> > may improve considerably. I could be wrong, but I believe that the
> > CASE statements may not be functioning as expected if an outer join
> > would be required to retrieve all rows, for instance: TAB2.CNAME NOT IN
> > ('PN','AR') - this may need to be rewritten as NVL(TAB2.CNAME,'PN')
> > NOT IN ('PN','AR') to obtain the expected results.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
>
>
>> Rows |
> -------------------------------------------------------------------------------
> -------------------------------
>
> | Id | Operation | Name |
>> 9583K|
> -------------------------------------------------------------------------------
> -------------------------------
>
> | 0 | SELECT STATEMENT | |
> 762K|
> 51M| 520K| | |
>
> | 1 | SORT ORDER BY | |
> 762K|
> 51M| 520K| | |
>
> | 2 | HASH GROUP BY | |
> 762K|
> 51M| 520K| | |
>
> | 3 | HASH JOIN RIGHT OUTER | |
> 1527K|
> 103M| 520K| | |
>
> | 4 | VIEW | |
> 36522 |
> 1248K| 251K| | |
>
> | 5 | HASH JOIN | |
> 36522 |
> 4529K| 251K| | |
>
> | 6 | VIEW | |
> 9583K|
> 539M| 79490 | | |
>
> | 7 | WINDOW SORT PUSHED RANK | |
> 9583K|
> 246M| 79490 | | |
>
> | 8 | PARTITION RANGE SINGLE | |
>> 9583K|
> | 9 | PARTITION HASH ALL | |
> 9583K|
> 246M| 10858 | 1 | 4 |
>
> | 10 | TABLE ACCESS FULL | TABLE2 |
>> 9434K|
> | 11 | VIEW | |
> 9434K|
> 611M| 109K| | |
>
> | 12 | WINDOW SORT PUSHED RANK | |
> 9434K|
> 224M| 109K| | |
>
> | 13 | PARTITION RANGE SINGLE | |
>> 9434K|
> | 14 | PARTITION HASH ALL | |
> 9434K|
> 224M| 44772 | 1 | 4 |
>
> | 15 | TABLE ACCESS FULL | TABLE3 |
>> 1527K|
> | 16 | PARTITION RANGE SINGLE | |
>> 52M| 269K| 465 | 480 |
> | 17 | PARTITION HASH ALL | |
> 1527K|
> 52M| 269K| 1 | 16 |
>
> | 18 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1 | 1527K|
>
> | 19 | INDEX RANGE SCAN | TABLE1_P_UK1 |
> 46M|
> | 20636 | 465 | 480 |
>
> -------------------------------------------------------------------------------
> -------------------------------
> >
> >
Something that I missed in the original post that was made clear by the explain plan: you have full table scans on TABLE2 and TABLE3. All rows of TABLE2 are retrieved into a view (data is 539MB in size), and all rows of TABLE3 are retrived into a view (data is 611MB in size), and then the contents of these views are joined together using a hash join. Only 1.2MB makes it out of the hash join, which is then joined with TABLE1. The views that are generated are likely a result of the (SELECT ... TABLE2) TAB2 syntax that you used. You have two sorts to disk, which is possibly significant. You also have a high percentage of physical reads compared to logical (consistent gets) reads. It is unclear whether these physical reads are a result of the sort to disk.
Compare the explain plan of your query with the explain plan for the modified query that I posted.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Nov 06 2006 - 07:42:22 CST
 |
 |