Home » RDBMS Server » Performance Tuning » Query Rewrite (Oracle 10g, windows)
Query Rewrite [message #542539] Tue, 07 February 2012 22:17 Go to next message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
Hi,

Below query is slow and when I had a discussion with DBA, I was suggested to rewrite the query for tuning.

SELECT cr.customer_id system_customer_id, 
       c.cust_master_src_id, 
       c.customer_id  linked_customer_id, 
       cr.cust_reln_state_cd, 
       ci.salutation_cd, 
       ci.first_nm, 
       ci.middle_nm, 
       ci.last_nm, 
       ba.billing_account_num, 
       ba.billing_master_src_id, 
       pn.phonenumber, 
       ba.source_account_type_cd, 
       ba.source_account_subtype_cd, 
       cr.eff_end_dt 
FROM   customer_relationship cr, 
       customer_individual ci, 
       customer c 
       left outer join (SELECT c.customer_id, 
                               ba.billing_account_id, 
                               ba.billing_account_num, 
                               ba.billing_master_src_id, 
                               bae.source_account_type_cd, 
                               bae.source_account_subtype_cd 
                        FROM   customer c, 
                               billing_account ba, 
                               billing_account_ext bae 
                        WHERE  c.customer_id = ba.customer_id 
                               AND bae.billing_account_id = 
                                   ba.billing_account_id) ba 
         ON ba.customer_id = c.customer_id 
       left outer join (SELECT ba.billing_account_id, 
                               s.prim_srvc_resrc_val_id phonenumber 
                        FROM   billing_account ba, 
                               customer c, 
                               billing_arrangement a, 
                               pay_channel p, 
                               service_instance s 
                        WHERE  c.customer_id = ba.customer_id 
                               AND ba.billing_account_id = a.billing_account_id 
                               AND a.billing_arrangement_id = 
                                   p.billing_arrangement_id 
                               AND p.pay_channel_id = s.dflt_rc_pay_channel_id 
                               AND a.billing_arrngmt_status_cd = 'O' 
                               AND p.pay_chnl_status_cd = 'O' 
                               AND s.srvc_instnc_stat_cd = 'A' 
                        ORDER  BY srvc_instnc_actvn_dt DESC) pn 
         ON ba.billing_account_id = pn.billing_account_id 
WHERE  cr.customer_id = #customerid# 
       AND c.customer_id = cr.related_customer_id 
       AND ci.customer_id = c.customer_id 
UNION 
SELECT cr.related_customer_id system_customer_id, 
       c.cust_master_src_id, 
       c.customer_id          linked_customer_id, 
       cr.cust_reln_state_cd, 
       ci.salutation_cd, 
       ci.first_nm, 
       ci.middle_nm, 
       ci.last_nm, 
       ba.billing_account_num, 
       ba.billing_master_src_id, 
       pn.phonenumber, 
       ba.source_account_type_cd, 
       ba.source_account_subtype_cd, 
       cr.eff_end_dt 
FROM   customer_relationship cr, 
       customer_individual ci, 
       customer c 
       left outer join (SELECT c.customer_id, 
                               ba.billing_account_id, 
                               ba.billing_account_num, 
                               ba.billing_master_src_id, 
                               bae.source_account_type_cd, 
                               bae.source_account_subtype_cd 
                        FROM   customer c, 
                               billing_account ba, 
                               billing_account_ext bae 
                        WHERE  c.customer_id = ba.customer_id 
                               AND bae.billing_account_id = 
                                   ba.billing_account_id) ba 
         ON ba.customer_id = c.customer_id 
       left outer join (SELECT ba.billing_account_id, 
                               s.prim_srvc_resrc_val_id phonenumber 
                        FROM   billing_account ba, 
                               customer c, 
                               billing_arrangement a, 
                               pay_channel p, 
                               service_instance s 
                        WHERE  c.customer_id = ba.customer_id 
                               AND ba.billing_account_id = a.billing_account_id 
                               AND a.billing_arrangement_id = 
                                   p.billing_arrangement_id 
                               AND p.pay_channel_id = s.dflt_rc_pay_channel_id 
                               AND a.billing_arrngmt_status_cd = 'O' 
                               AND p.pay_chnl_status_cd = 'O' 
                               AND s.srvc_instnc_stat_cd = 'A' 
                        ORDER  BY srvc_instnc_actvn_dt DESC) pn 
         ON ba.billing_account_id = pn.billing_account_id 
WHERE  cr.related_customer_id = #customerid# 
       AND c.customer_id = cr.customer_id 
       AND ci.customer_id = c.customer_id 



The thing is same tales & joins are used with only one change at WHERE clause
i.e, WHERE CR.RELATED_CUSTOMER_ID = #customerId#
&
WHERE CR.RELATED_CUSTOMER_ID = #customerId#

Can someone help me on this.

Thanks in Advance
Re: Query Rewrite [message #542540 is a reply to message #542539] Tue, 07 February 2012 22:40 Go to previous messageGo to next message
BlackSwan
Messages: 22798
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
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) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query Rewrite [message #542594 is a reply to message #542540] Wed, 08 February 2012 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Getting rid of the pointless order by would probably help.
Re: Query Rewrite [message #542607 is a reply to message #542540] Wed, 08 February 2012 03:37 Go to previous messageGo to next message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
Execution Plan is attached
Re: Query Rewrite [message #542610 is a reply to message #542607] Wed, 08 February 2012 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
A lot of people can't/won't download files so you need to post that inline in code tags.
Re: Query Rewrite [message #542618 is a reply to message #542607] Wed, 08 February 2012 03:57 Go to previous messageGo to next message
John Watson
Messages: 4577
Registered: January 2010
Location: Global Village
Senior Member
It looks to me as though you are running the same query twice and unioning the results: the only difference being a literal in an equality prodicate. Can't you run it once, and use OR ? Or have I misunderstood?
But anyway, the statistics suggest that the query is doing hardly anything, only 114 consistent gets. What's the problem?

Lastly, I don't think it is a good idea to make some of the joins with ANSI syntax and some with the old Oracle syntax using the predicate. I would re-write to use exclusively ANSI, that will make it easier to understand what is going on.

hth, John.
Re: Query Rewrite [message #542621 is a reply to message #542618] Wed, 08 February 2012 04:06 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
The join between c and cr is done on different columns on each side of the union.
And I imagine that explain plan was generated on an empty dev box, lots of people do that.
Re: Query Rewrite [message #542624 is a reply to message #542621] Wed, 08 February 2012 04:12 Go to previous messageGo to next message
John Watson
Messages: 4577
Registered: January 2010
Location: Global Village
Senior Member
Quote:
The join between c and cr is done on different columns on each side of the union.
I missed that. I would like to think that I wouldn't have, if it were written with ANSI syntax.
Re: Query Rewrite [message #542626 is a reply to message #542624] Wed, 08 February 2012 04:16 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
If so you're too reliant on ansi. The amount of code is about the same either way and you'd still have to do a line by line comparison of the two selects.
When I see something like that I split it into two queries and use a file comparison tool.
Re: Query Rewrite [message #542634 is a reply to message #542626] Wed, 08 February 2012 04:55 Go to previous messageGo to next message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
Same query has been written twice with two changes(mentioned as below), is there a way to rewrite this query -effectively.

1- SELECT cr.customer_id system_customer_id & SELECT cr.related_customer_id system_customer_id,

2- WHERE cr.customer_id = #customerid# & cr.related_customer_id = #customerid#
Re: Query Rewrite [message #542635 is a reply to message #542634] Wed, 08 February 2012 04:57 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maybe, we can't tell unless you posted the requested information in the requested format (inline, in code tags).
Re: Query Rewrite [message #542636 is a reply to message #542635] Wed, 08 February 2012 04:57 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
And run the explain plan against production please.
Re: Query Rewrite [message #542637 is a reply to message #542635] Wed, 08 February 2012 05:04 Go to previous messageGo to next message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
Execution plan is as follows
Execution Plan
   ----------------------------------------------------------
   Plan hash value: 1268268161
   
   --------------------------------------------------------------------------------------------------------------------------------------
   | Id  | Operation                                | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
   --------------------------------------------------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT                         |                           |     4 |   580 |   143  (52)| 00:00:02 |       |       |
   |   1 |  SORT UNIQUE                             |                           |     4 |   580 |   143  (52)| 00:00:02 |       |       |
   |   2 |   UNION-ALL                              |                           |       |       |            |          |       |       |
   |   3 |    NESTED LOOPS OUTER                    |                           |     2 |   290 |    70   (0)| 00:00:01 |       |       |
   |   4 |     NESTED LOOPS OUTER                   |                           |     2 |   218 |    26   (0)| 00:00:01 |       |       |
   |   5 |      NESTED LOOPS                        |                           |     2 |   120 |    14   (0)| 00:00:01 |       |       |
   |   6 |       NESTED LOOPS                       |                           |     2 |    68 |     8   (0)| 00:00:01 |       |       |
   |   7 |        PARTITION LIST ALL                |                           |     2 |    36 |     4   (0)| 00:00:01 |     1 |     2 |
   |   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| CUSTOMER_RELATIONSHIP     |     2 |    36 |     4   (0)| 00:00:01 |     1 |     2 |
   |*  9 |          INDEX RANGE SCAN                | CUSTOMER_RELATIONSHIP_IX2 |     2 |       |     3   (0)| 00:00:01 |     1 |     2 |
   |* 10 |        INDEX UNIQUE SCAN                 | CUSTOMER_PK               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
   |  11 |       PARTITION LIST ALL                 |                           |     1 |    26 |     3   (0)| 00:00:01 |     1 |     2 |
   |  12 |        TABLE ACCESS BY LOCAL INDEX ROWID | CUSTOMER_INDIVIDUAL       |     1 |    26 |     3   (0)| 00:00:01 |     1 |     2 |
   |* 13 |         INDEX RANGE SCAN                 | CUSTOMER_INDIVIDUAL_IX2   |     1 |       |     2   (0)| 00:00:01 |     1 |     2 |
   |  14 |      VIEW PUSHED PREDICATE               |                           |     1 |    49 |     6   (0)| 00:00:01 |       |       |
   |  15 |       TABLE ACCESS BY INDEX ROWID        | BILLING_ACCOUNT_EXT       |     1 |    10 |     2   (0)| 00:00:01 |       |       |
   |  16 |        NESTED LOOPS                      |                           |     1 |    39 |     6   (0)| 00:00:01 |       |       |
   |  17 |         NESTED LOOPS                     |                           |     1 |    29 |     4   (0)| 00:00:01 |       |       |
   |* 18 |          INDEX UNIQUE SCAN               | CUSTOMER_PK               |     1 |     6 |     2   (0)| 00:00:01 |       |       |
   |  19 |          TABLE ACCESS BY INDEX ROWID     | BILLING_ACCOUNT           |     1 |    23 |     2   (0)| 00:00:01 |       |       |
   |* 20 |           INDEX RANGE SCAN               | BILLING_ACCOUNT_IX1       |     1 |       |     1   (0)| 00:00:01 |       |       |
   |* 21 |         INDEX RANGE SCAN                 | BILLING_ACCOUNT_EXT_IX1   |     1 |       |     1   (0)| 00:00:01 |       |       |
   |  22 |     VIEW PUSHED PREDICATE                |                           |     1 |    36 |    22   (0)| 00:00:01 |       |       |
   |* 23 |      TABLE ACCESS BY INDEX ROWID         | SERVICE_INSTANCE          |     1 |    24 |    16   (0)| 00:00:01 |       |       |
   |  24 |       NESTED LOOPS                       |                           |     1 |    64 |    22   (0)| 00:00:01 |       |       |
   |  25 |        NESTED LOOPS                      |                           |     1 |    40 |     6   (0)| 00:00:01 |       |       |
   |  26 |         NESTED LOOPS                     |                           |     1 |    26 |     4   (0)| 00:00:01 |       |       |
   |* 27 |          INDEX UNIQUE SCAN               | BILLING_ACCOUNT_PK        |     1 |       |     1   (0)| 00:00:01 |       |       |
   |* 28 |          TABLE ACCESS BY INDEX ROWID     | BILLING_ARRANGEMENT       |     1 |    14 |     2   (0)| 00:00:01 |       |       |
   |* 29 |           INDEX RANGE SCAN               | BILLING_ARRANGEMENT_IX1   |     1 |       |     1   (0)| 00:00:01 |       |       |
   |* 30 |         TABLE ACCESS BY INDEX ROWID      | PAY_CHANNEL               |     1 |    14 |     2   (0)| 00:00:01 |       |       |
   |* 31 |          INDEX RANGE SCAN                | PAY_CHANNEL_IX1           |     1 |       |     1   (0)| 00:00:01 |       |       |
   |* 32 |        INDEX RANGE SCAN                  | SERVICE_INSTANCE_IX3      |    22 |       |     1   (0)| 00:00:01 |       |       |
   |  33 |    NESTED LOOPS OUTER                    |                           |     2 |   290 |    71   (0)| 00:00:01 |       |       |
   |  34 |     NESTED LOOPS OUTER                   |                           |     2 |   218 |    27   (0)| 00:00:01 |       |       |
   |  35 |      NESTED LOOPS                        |                           |     2 |   120 |    15   (0)| 00:00:01 |       |       |
   |  36 |       NESTED LOOPS                       |                           |     2 |    68 |     9   (0)| 00:00:01 |       |       |
   |  37 |        PARTITION LIST ALL                |                           |     2 |    36 |     5   (0)| 00:00:01 |     1 |     2 |
   |  38 |         TABLE ACCESS BY LOCAL INDEX ROWID| CUSTOMER_RELATIONSHIP     |     2 |    36 |     5   (0)| 00:00:01 |     1 |     2 |
   |* 39 |          INDEX RANGE SCAN                | CUSTOMER_RELATIONSHIP_IX1 |     2 |       |     3   (0)| 00:00:01 |     1 |     2 |
   |* 40 |        INDEX UNIQUE SCAN                 | CUSTOMER_PK               |     1 |    16 |     1   (0)| 00:00:01 |       |       |
   |  41 |       PARTITION LIST ALL                 |                           |     1 |    26 |     3   (0)| 00:00:01 |     1 |     2 |
   |  42 |        TABLE ACCESS BY LOCAL INDEX ROWID | CUSTOMER_INDIVIDUAL       |     1 |    26 |     3   (0)| 00:00:01 |     1 |     2 |
   |* 43 |         INDEX RANGE SCAN                 | CUSTOMER_INDIVIDUAL_IX2   |     1 |       |     2   (0)| 00:00:01 |     1 |     2 |
   |  44 |      VIEW PUSHED PREDICATE               |                           |     1 |    49 |     6   (0)| 00:00:01 |       |       |
   |  45 |       TABLE ACCESS BY INDEX ROWID        | BILLING_ACCOUNT_EXT       |     1 |    10 |     2   (0)| 00:00:01 |       |       |
   |  46 |        NESTED LOOPS                      |                           |     1 |    39 |     6   (0)| 00:00:01 |       |       |
   |  47 |         NESTED LOOPS                     |                           |     1 |    29 |     4   (0)| 00:00:01 |       |       |
   |* 48 |          INDEX UNIQUE SCAN               | CUSTOMER_PK               |     1 |     6 |     2   (0)| 00:00:01 |       |       |
   |  49 |          TABLE ACCESS BY INDEX ROWID     | BILLING_ACCOUNT           |     1 |    23 |     2   (0)| 00:00:01 |       |       |
   |* 50 |           INDEX RANGE SCAN               | BILLING_ACCOUNT_IX1       |     1 |       |     1   (0)| 00:00:01 |       |       |
   |* 51 |         INDEX RANGE SCAN                 | BILLING_ACCOUNT_EXT_IX1   |     1 |       |     1   (0)| 00:00:01 |       |       |
   |  52 |     VIEW PUSHED PREDICATE                |                           |     1 |    36 |    22   (0)| 00:00:01 |       |       |
   |* 53 |      TABLE ACCESS BY INDEX ROWID         | SERVICE_INSTANCE          |     1 |    24 |    16   (0)| 00:00:01 |       |       |
   |  54 |       NESTED LOOPS                       |                           |     1 |    64 |    22   (0)| 00:00:01 |       |       |
   |  55 |        NESTED LOOPS                      |                           |     1 |    40 |     6   (0)| 00:00:01 |       |       |
   |  56 |         NESTED LOOPS                     |                           |     1 |    26 |     4   (0)| 00:00:01 |       |       |
   |* 57 |          INDEX UNIQUE SCAN               | BILLING_ACCOUNT_PK        |     1 |       |     1   (0)| 00:00:01 |       |       |
   |* 58 |          TABLE ACCESS BY INDEX ROWID     | BILLING_ARRANGEMENT       |     1 |    14 |     2   (0)| 00:00:01 |       |       |
   |* 59 |           INDEX RANGE SCAN               | BILLING_ARRANGEMENT_IX1   |     1 |       |     1   (0)| 00:00:01 |       |       |
   |* 60 |         TABLE ACCESS BY INDEX ROWID      | PAY_CHANNEL               |     1 |    14 |     2   (0)| 00:00:01 |       |       |
   |* 61 |          INDEX RANGE SCAN                | PAY_CHANNEL_IX1           |     1 |       |     1   (0)| 00:00:01 |       |       |
   |* 62 |        INDEX RANGE SCAN                  | SERVICE_INSTANCE_IX3      |    22 |       |     1   (0)| 00:00:01 |       |       |
   --------------------------------------------------------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
   
      9 - access("CR"."CUSTOMER_ID"=10001311)
     10 - access("CR"."RELATED_CUSTOMER_ID"="C"."CUSTOMER_ID")
     13 - access("CI"."CUSTOMER_ID"="C"."CUSTOMER_ID")
     18 - access("C"."CUSTOMER_ID"="C"."CUSTOMER_ID")
     20 - access("BA"."CUSTOMER_ID"="C"."CUSTOMER_ID")
     21 - access("BAE"."BILLING_ACCOUNT_ID"="BA"."BILLING_ACCOUNT_ID")
     23 - filter("S"."SRVC_INSTNC_STAT_CD"='A')
     27 - access("BA"."BILLING_ACCOUNT_ID"="BA"."BILLING_ACCOUNT_ID")
     28 - filter("A"."BILLING_ARRNGMT_STATUS_CD"='O')
     29 - access("A"."BILLING_ACCOUNT_ID"="BA"."BILLING_ACCOUNT_ID")
     30 - filter("P"."PAY_CHNL_STATUS_CD"='O')
     31 - access("A"."BILLING_ARRANGEMENT_ID"="P"."BILLING_ARRANGEMENT_ID")
     32 - access("P"."PAY_CHANNEL_ID"="S"."DFLT_RC_PAY_CHANNEL_ID")
          filter("S"."DFLT_RC_PAY_CHANNEL_ID" IS NOT NULL)
     39 - access("CR"."RELATED_CUSTOMER_ID"=57576218)
     40 - access("CR"."CUSTOMER_ID"="C"."CUSTOMER_ID")
     43 - access("CI"."CUSTOMER_ID"="C"."CUSTOMER_ID")
     48 - access("C"."CUSTOMER_ID"="C"."CUSTOMER_ID")
     50 - access("BA"."CUSTOMER_ID"="C"."CUSTOMER_ID")
     51 - access("BAE"."BILLING_ACCOUNT_ID"="BA"."BILLING_ACCOUNT_ID")
     53 - filter("S"."SRVC_INSTNC_STAT_CD"='A')
     57 - access("BA"."BILLING_ACCOUNT_ID"="BA"."BILLING_ACCOUNT_ID")
     58 - filter("A"."BILLING_ARRNGMT_STATUS_CD"='O')
     59 - access("A"."BILLING_ACCOUNT_ID"="BA"."BILLING_ACCOUNT_ID")
     60 - filter("P"."PAY_CHNL_STATUS_CD"='O')
     61 - access("A"."BILLING_ARRANGEMENT_ID"="P"."BILLING_ARRANGEMENT_ID")
     62 - access("P"."PAY_CHANNEL_ID"="S"."DFLT_RC_PAY_CHANNEL_ID")
          filter("S"."DFLT_RC_PAY_CHANNEL_ID" IS NOT NULL)
   
   
   Statistics
   ----------------------------------------------------------
             0  recursive calls
             0  db block gets
           114  consistent gets
            31  physical reads
             0  redo size
          1740  bytes sent via SQL*Net to client
          5284  bytes received via SQL*Net from client
             2  SQL*Net roundtrips to/from client
             1  sorts (memory)
             0  sorts (disk)
             6  rows processed
Re: Query Rewrite [message #542641 is a reply to message #542637] Wed, 08 February 2012 05:14 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is that explain plan taken from production or not?
If it is from production you don't have a problem.
If it isn't it's useless. Plans change due to changes in data volumnes and you've got virtually no data there.
Previous Topic: comparing SQL plans between 2 DB's
Next Topic: Options for querying on the skewed data
Goto Forum:
  


Current Time: Mon Sep 22 13:25:26 CDT 2014

Total time taken to generate the page: 0.09223 seconds