Re: Optimising the Query

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 06 Nov 2006 10:06:42 -0800
Message-ID: <1162836403.121713@bubbleator.drizzle.com>

Charles Hooper wrote:

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

>> Thanks for the replies.
>>
>> Here's the explain plan generated by the query:
>>
>> Execution Plan
>> ----------------------------------------------------------
>>
>> -------------------------------------------------------------------------------
>> -------------------------------
>>
>> | Id  | Operation                              | Name                 |
>> Rows  |
>> Bytes | Cost  | Pstart| Pstop |
>>
>> -------------------------------------------------------------------------------
>> -------------------------------
>>
>> |   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|
>>   246M| 10858 |    28 |    28 |
>>
>> |   9 |          PARTITION HASH ALL            |                      |
>>  9583K|
>>   246M| 10858 |     1 |     4 |
>>
>> |  10 |           TABLE ACCESS FULL            | TABLE2           |
>> 9583K|
>>   246M| 10858 |   109 |   112 |
>>
>> |  11 |       VIEW                             |                      |
>>  9434K|
>>   611M|   109K|       |       |
>>
>> |  12 |        WINDOW SORT PUSHED RANK         |                      |
>>  9434K|
>>   224M|   109K|       |       |
>>
>> |  13 |         PARTITION RANGE SINGLE         |                      |
>>  9434K|
>>   224M| 44772 |    29 |    29 |
>>
>> |  14 |          PARTITION HASH ALL            |                      |
>>  9434K|
>>   224M| 44772 |     1 |     4 |
>>
>> |  15 |           TABLE ACCESS FULL            | TABLE3           |
>> 9434K|
>>   224M| 44772 |   113 |   116 |
>>
>> |  16 |     PARTITION RANGE SINGLE             |                      |
>>  1527K|
>>    52M|   269K|    30 |    30 |
>>
>> |  17 |      PARTITION HASH ALL                |                      |
>>  1527K|
>>    52M|   269K|     1 |    16 |
>>
>> |  18 |       TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1 |  1527K|
>>    52M|   269K|   465 |   480 |
>>
>> |  19 |        INDEX RANGE SCAN                | TABLE1_P_UK1     |
>> 46M|
>>       | 20636 |   465 |   480 |
>>
>> -------------------------------------------------------------------------------
>> -------------------------------
>>
>>
>> Note
>> -----
>>    - 'PLAN_TABLE' is old version
>>
>>
>> Statistics
>> ----------------------------------------------------------
>>        8199  recursive calls
>>        8695  db block gets
>>     1568210  consistent gets
>>     1429061  physical reads
>>           0  redo size
>>       10581  bytes sent via SQL*Net to client
>>        1203  bytes received via SQL*Net from client
>>          36  SQL*Net roundtrips to/from client
>>          51  sorts (memory)
>>           2  sorts (disk)
>>         514  rows processed
> 
> 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.

And, in addition, do your future explain plans using DBMS_XPLAN ... not whatever script you are using. DBMS_XPLAN will give the amount of TEMP space used and other valuable information. Look at $ORACLE_HOME/rdbms/admin/utlxplp.sql.

Note also the "note" about plan_table being the old version. You should run catplan.sql (if available) and use the newest version.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Nov 06 2006 - 12:06:42 CST