OraFAQ Forum: Performance Tuning » query optimization

Home » RDBMS Server » Performance Tuning » query optimization (oracle 10gR2 , Suse Enterprise Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query optimization [message #516853]

Thu, 21 July 2011 03:27

mr_wee
Messages: 18
Registered: June 2011

Junior Member

Hi,

I want to sum values of two columns in one table with different conditions just using one time table scanining. this is my query :

select amount1 + amount2 from
   (select 
        ( SELECT sum(nvl(t.sales_amount, 0))
          FROM sales t
          where t.branch = x
            and t.brand = y
            and t.sales_type = 0
            and t.status in (0,63)
            and trunc(t.sales_date) = z) amount1,
      ( SELECT sum(nvl(t.sales_amount, 0))
          FROM sales t
          where t.branch = x
            and t.brand = y
            and t.sales_type = 2
            and t.status in (0,23)
            and trunc(t.sales_date) = z) amount2
       from dual);

I tried to rewrite it as follows:

SELECT 
   sum (CASE WHEN  t.sales_type = 0 and t.status  in (0,63)  THEN t.sales_amount ELSE 0  END) +
   SUM   (CASE WHEN  t.sales_type = 2 and t.status  in (0,23)  THEN t.sales_amount ELSE 0  END) 
   FROM sales t
          where t.branch = x
                and t.brand = y
                and trunc(t.sales_date) = z;

but cost of query increased from 2 to 7. Anyone else have any better ideas? I appreciate any assistance.

Regards,
Wee

Report message to a moderator

Re: query optimization [message #516857 is a reply to message #516853]

Thu, 21 July 2011 03:41

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: query optimization [message #516863 is a reply to message #516853]

Thu, 21 July 2011 03:56

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

mr_wee wrote on Thu, 21 July 2011 09:27

Hi,

but cost of query increased from 2 to 7.

Those are both tiny figures. Are you testing this on a table with a very small amount of data?
If so I strongly suggest you get a representative amount of data to test against.

Report message to a moderator

Re: query optimization [message #517154 is a reply to message #516863]

Sat, 23 July 2011 02:19

mr_wee
Messages: 18
Registered: June 2011

Junior Member

Hi all,

I tested 2 query on a large amount of data ( about 11,000,000 records ) and these are explain plans for them :

First Query :

SQL> select amount1 + amount2 from
  2     (select 
  3          ( SELECT sum(nvl(t.sales_amount, 0))
  4            FROM sales t
  5            where t.branch = 11111111
  6              and t.brand = '1826'
  7              and t.sales_type = 0
  8              and t.status in (0,63)
  9              and trunc(t.sales_date) = '23-JUL-11') amount1,
 10        ( SELECT sum(nvl(t.sales_amount, 0))
 11            FROM sales t
 12            where t.branch = 11111111
 13              and t.brand = '1826'
 14              and t.sales_type = 2
 15              and t.status in (0,23)
 16              and trunc(t.sales_date) = '23-JUL-11') amount2
 17         from dual); 

AMOUNT1+AMOUNT2
---------------
       24000000

Elapsed: 00:00:07.17

Execution Plan
----------------------------------------------------------
Plan hash value: 2894230999

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |       |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |       |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS FULL  | SALES | 18367 |   412K| 19514   (2)| 00:03:55 |
|   3 |    SORT AGGREGATE    |       |     1 |    23 |            |          |
|*  4 |     TABLE ACCESS FULL| SALES | 18367 |   412K| 19514   (2)| 00:03:55 |
|   5 |  FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."BRAND"='1826' AND "T"."SALES_TYPE"=0 AND
              ("T"."STATUS"=0 OR "T"."STATUS"=63) AND
              TRUNC(INTERNAL_FUNCTION("T"."SALES_DATE"))='23-JUL-11' AND
              "T"."BRANCH"=11111111)
   4 - filter("T"."BRAND"='1826' AND "T"."SALES_TYPE"=2 AND
              ("T"."STATUS"=0 OR "T"."STATUS"=23) AND
              TRUNC(INTERNAL_FUNCTION("T"."SALES_DATE"))='23-JUL-11' AND
              "T"."BRANCH"=11111111)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     141590  consistent gets
     141570  physical reads
          0  redo size
        342  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Second Query:

SQL> SELECT 
  2     sum (CASE WHEN  t.sales_type = 0 and t.status  in (0,63)  THEN t.sales_amount ELSE 0  END) +

  3     SUM   (CASE WHEN  t.sales_type = 2 and t.status  in (0,23)  THEN t.sales_amount ELSE 0  END)
 
  4     FROM sales t
  5            where t.branch = 11111111
  6                  and t.brand = '1826'
  7                  and trunc(t.sales_date) = '23-JUL-11'
  8  /

SUM(CASEWHENT.SALES_TYPE=0ANDT.STATUSIN(0,63)THENT.SALES_AMOUNTELSE0END)+SUM(CAS
--------------------------------------------------------------------------------
                                                                        24000000

Elapsed: 00:00:06.84

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    23 | 19605   (2)| 00:03:56 |
|   1 |  SORT AGGREGATE    |       |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES | 55100 |  1237K| 19605   (2)| 00:03:56 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."BRAND"='1826' AND
              TRUNC(INTERNAL_FUNCTION("T"."SALES_DATE"))='23-JUL-11' AND
              "T"."BRANCH"=11111111)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      70795  consistent gets
      70785  physical reads
          0  redo size
        472  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Any better idea for rewriting query? I appreciate any assistance.

Regards,

Report message to a moderator

Re: query optimization [message #517160 is a reply to message #517154]

Sat, 23 July 2011 07:52

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I tested 2 query on a large amount of data ( about 11,000,000 records )

Quote:

TABLE ACCESS FULL| SALES | 18367 |

Quote:

TABLE ACCESS FULL| SALES | 55100

Not the correct number of rows, not the same number of rows, nothing to compare.

Quote:

Any better idea for rewriting query?

What is the query intending to do?

Regards
Michel

[Updated on: Sat, 23 July 2011 10:06]

Report message to a moderator

Re: query optimization [message #517162 is a reply to message #517160]

Sat, 23 July 2011 09:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>and trunc(t.sales_date) = '23-JUL-11'

With Oracle characters between single quote marks are STRINGS!
'This is a string, 23-JUL-11, not a date'
When a DATE datatype is desired, then use TO_DATE() function.

Also it is unwise to use only 2 digits for year. What date is '07-08-09'?

Report message to a moderator

Re: query optimization [message #517175 is a reply to message #517160]

Sat, 23 July 2011 22:42

mr_wee
Messages: 18
Registered: June 2011

Junior Member

Dear Michel,

I tested 2 query on the same number of records and posted the explain plans. Oracle shows What you have seen in the previous post!

As I explained at the first post I need to sum values of two columns in one table with different conditions, but it is important for me to optimize query as best as possible.

Regards,
Wee

Report message to a moderator

Re: query optimization [message #517176 is a reply to message #517162]

Sat, 23 July 2011 23:02

mr_wee
Messages: 18
Registered: June 2011

Junior Member

Dear BlackSwan,

You're right. I should put the date in to_date function. I tested again this situation and the result was the same!
(However, I posted query and explain plan using constants, in fact these parameters are input arguments in my function)

Regards,
Wee

Report message to a moderator

Re: query optimization [message #517177 is a reply to message #517175]

Sat, 23 July 2011 23:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>it is important for me to optimize query as best as possible.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof

Report message to a moderator

Re: query optimization [message #517178 is a reply to message #517177]

Sun, 24 July 2011 00:01

mr_wee
Messages: 18
Registered: June 2011

Junior Member

I hope this information makes the problem clear:

Table structure :

create table SALES
(
  ID           NUMBER(15) not null,
  BRANCH       NUMBER(8) not null,
  BRAND        VARCHAR2(30) not null,
  SALES_AMOUNT NUMBER(15),
  SALES_DATE   DATE not null,
  SALES_TYPE   NUMBER(1) not null,
  STATUS       NUMBER(2),
  ACTUAL_DATE  DATE
)

Table index :

create index SALES_INDX on SALES (BRANCH, BRAND, SALES_TYPE)

And these are trace file output for 2 query :

SQL ID: 4kvc1pjyh5n7x
Plan Hash: 0
ALTER SESSION SET SQL_TRACE=TRUE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.02          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.02          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87  
********************************************************************************

select amount1 + amount2 from
   (select
        ( SELECT sum(nvl(t.sales_amount, 0))
          FROM sales t
          where t.branch = 11111111
            and t.brand = '1826'
            and t.sales_type = 0
            and t.status in (0,63)
            and trunc(t.sales_date) = to_date('23/07/2011','DD/MM/YYYY')) amount1,
      ( SELECT sum(nvl(t.sales_amount, 0))
          FROM sales t
          where t.branch = 11111111
            and t.brand = '1826'
            and t.sales_type = 2
            and t.status in (0,23)
            and trunc(t.sales_date) = to_date('23/07/2011','DD/MM/YYYY')) amount2
       from dual)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.19          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4     20.89      57.39     283144     283180          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8     20.90      57.58     283144     283180          0           2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=70795 pr=70789 pw=0 time=0 us)
4000000   TABLE ACCESS FULL SALES (cr=70795 pr=70789 pw=0 time=19455356 us cost=19499 size=422441 card=18367)
      1  SORT AGGREGATE (cr=70795 pr=70785 pw=0 time=0 us)
2000000   TABLE ACCESS FULL SALES (cr=70795 pr=70785 pw=0 time=3615230 us cost=19499 size=422441 card=18367)
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

SQL ID: 4kvc1pjyh5n7x
Plan Hash: 0
ALTER SESSION SET SQL_TRACE=TRUE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87  
********************************************************************************

SELECT
   sum (CASE WHEN  t.sales_type = 0 and t.status  in (0,63)  THEN t.sales_amount ELSE 0  END) +
    SUM   (CASE WHEN  t.sales_type = 2 and t.status  in (0,23)  THEN t.sales_amount ELSE 0  END)
    FROM sales t
           where t.branch = 11111111
                 and t.brand = '1826'
                 and trunc(t.sales_date) = to_date('23/07/2011','DD/MM/YYYY')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      7.25       7.51      70785      70795          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.26       7.51      70785      70795          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=70795 pr=70785 pw=0 time=0 us)
6000000   TABLE ACCESS FULL SALES (cr=70795 pr=70785 pw=0 time=7040638 us cost=19570 size=138000000 card=6000000)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      7.25       7.51      70785      70795          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      7.26       7.51      70785      70795          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    2  user  SQL statements in session.
    0  internal SQL statements in session.
    2  SQL statements in session.
********************************************************************************
Trace file: /opt/oracle/diag/rdbms/as/as/trace/as_ora_10519.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       2  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
      49  lines in trace file.
      27  elapsed seconds in trace file.

I am still awaiting a better solution for rewriting query.
Thanks all

Report message to a moderator

Re: query optimization [message #517179 is a reply to message #517175]

Sun, 24 July 2011 01:12

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

mr_wee wrote on Sun, 24 July 2011 05:42

Dear Michel,

I tested 2 query on the same number of records and posted the explain plans. Oracle shows What you have seen in the previous post!

As I explained at the first post I need to sum values of two columns in one table with different conditions, but it is important for me to optimize query as best as possible.

Regards,
Wee

I don't believe Oracle thinks there are 2 different number of rows in the same table in 2 subsequent queries.
Anyway, your statistics are not up to date, the first thing to do is to gather them.

Regards
Michel

Report message to a moderator

Re: query optimization [message #517186 is a reply to message #517179]

Sun, 24 July 2011 02:13

mr_wee
Messages: 18
Registered: June 2011

Junior Member

Quote:

I don't believe Oracle thinks there are 2 different number of rows in the same table in 2 subsequent queries.
Anyway, your statistics are not up to date, the first thing to do is to gather them.

I'm sure the table statistics was up to date when I was runnnig the queries, but for confidence I gather statistics again but the explain plan did not change! it is strange for me too why oracle works differently in 2 situation!!!

Regards,

--------------------------------------------------------
I think that I found why oracle works in this way. In second Query, oracle read 18367 rows in first "case when" and reads 18367 rows in second "case when" and then does sum operation on 18367 rows. as a result 18367*3 = 55101.
In fact 55100 rows is as a result of this condition :

  where t.branch = 11111111
                and t.brand = '1826'
                 and trunc(t.sales_date) = to_date('23/07/2011','DD/MM/YYYY')

Bests,
Wee

[Updated on: Sun, 24 July 2011 02:44]

Report message to a moderator

Re: query optimization [message #517187 is a reply to message #517186]

Sun, 24 July 2011 02:38

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The tkprof output shows you have 70795 blocks in your table.
I don't see that Oracle works differently, it uses the same plan for the similar part of your queries: FULL TABLE SCAN which is most likely the correct path (althought we have no information about your indexes and their statistics).
It is obvious the second query is the best one as it only scan the table once.

Regards
Michel

[Edit: Missing word "blocks"]

[Updated on: Sun, 24 July 2011 08:28]

Report message to a moderator

Re: query optimization [message #517200 is a reply to message #517187]

Sun, 24 July 2011 07:26

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

70795 consistent reads not rows.

Report message to a moderator

Re: query optimization [message #517202 is a reply to message #517200]

Sun, 24 July 2011 08:28

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I know I missed "blocks" in the sentence (which does not contain "rows").
(By the way, this does not change the main points which are the rest of the post.)

Regards
Michel

[Updated on: Sun, 24 July 2011 08:30]

Report message to a moderator

Re: query optimization [message #517209 is a reply to message #517202]

Sun, 24 July 2011 11:06

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Perhaps I missunderstand something, but why not simply so:

SELECT /*+ NO_EXPAND (? depends on kind of table access)*/ sum(nvl(t.sales_amount, 0))
FROM sales t
where t.branch = 11111111
and t.brand = '1826'
and ((t.sales_type = 0 and t.status in (0,63)) or (t.sales_type = 2 and t.status in (0,23)) 
and trunc(t.sales_date) = to_date('23/07/2011','DD/MM/YYYY');

Report message to a moderator

Re: query optimization [message #517244 is a reply to message #516853]

Mon, 25 July 2011 01:18

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

I would try:
1. Create a new index:

CREATE INDEX ... ON SALES ( BRANCH, BRAND, SALES_DATE ) COMPUTE STATISTICS ...

2. Rewrite the statement as:

SELECT sum(t.sales_amount)
FROM sales t
where t.branch = 11111111
and t.brand = '1826'
and ((t.sales_type = 0 and t.status in (0,63)) or
     (t.sales_type = 2 and t.status in (0,23)) 
and t.sales_date) BETWEEN
  to_date('23/07/2011 00:00:00','DD/MM/YYYY HH24:MI:SS') AND
  to_date('23/07/2011 23:59:59','DD/MM/YYYY HH24:MI:SS')

3. Verify that the new index is used or enforce it's usage with hint.

HTH

[Updated on: Mon, 25 July 2011 01:18]

Report message to a moderator

Previous Topic:	big table partitioning
Next Topic:	BitMap Index Issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 21:31:22 CST 2024