| Query Rewrite [message #542539] |
Tue, 07 February 2012 22:17  |
oraranjangmailcom
Messages: 67
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 #542618 is a reply to message #542607] |
Wed, 08 February 2012 03:57  |
John Watson
Messages: 8962
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 |
cookiemonster
Messages: 13962
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 |
John Watson
Messages: 8962
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 |
cookiemonster
Messages: 13962
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 |
oraranjangmailcom
Messages: 67
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 |
cookiemonster
Messages: 13962
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 #542637 is a reply to message #542635] |
Wed, 08 February 2012 05:04 |
oraranjangmailcom
Messages: 67
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 |
cookiemonster
Messages: 13962
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
