DB version : 11.2.0.4
OS : RHEL 6.5
The below mentioned query is part of a stored procedure. After fetching these records, the procedure does an UPDATE and an INSERT. Currently, it takes around 10 minutes to fetch 10,000 records. I would like to know if there are any ways of improving this query. I am including the execution plan with the predicate information.
SELECT cust.ou_num,
cust.accnt_name,
cust.address_id,
cust.accnt_type_cd,
cust.cust_stat_cd,
cust.payment_type,
cust.bill_address,
addr.city,
addr.country,
addr.state,
addr.zipcode,
addr.addr_name,
addr.x_street_type,
addr.addr_line_2,
addr.x_unit,
addr.addr
||addr.city
||addr.state
||addr.country
||addr.zipcode
||addr.x_unit
||addr.x_street_type
||addr.addr_line_2 AS addr_concat
FROM (SELECT act.par_row_id,
act.ou_num,
act.loc,
act.name accnt_name,
st.address_id,
act.accnt_type_cd,
act.cust_stat_cd,
p.party_uid,
p.party_type_cd,
CASE
WHEN EXISTS (SELECT bf.row_id
FROM s_inv_prof bf,
s_asset ast2
WHERE bf.row_id = ast2.bill_profile_id
AND ast2.serv_acct_id = act.par_row_id
AND bf.payment_type_cd = 'Postpaid') THEN
'Postpaid'
ELSE 'Prepaid'
END AS Payment_Type,
CASE
WHEN EXISTS (SELECT bf.row_id
FROM s_inv_prof bf,
s_asset ast2
WHERE bf.row_id = ast2.bill_profile_id
AND ast2.serv_acct_id = act.par_row_id
AND bf.payment_type_cd = 'Postpaid') THEN
(SELECT addr.addr_name
FROM s_org_ext ba,
s_asset ast3,
s_inv_prof bf2,
s_addr_per addr
WHERE ast3.serv_acct_id = act.par_row_id
AND ba.pr_addr_id = addr.row_id
AND ast3.bill_accnt_id = ba.par_row_id
AND ast3.row_id = ast3.root_asset_id
AND ast3.prom_integ_id IS NULL
AND ast3.bill_profile_id = bf2.row_id
AND bf2.payment_type_cd = 'Postpaid'
AND ROWNUM < 2)
ELSE 'null'
END AS Bill_Address
FROM s_org_ext act,
address_stats st,
s_party p
WHERE act.pr_addr_id = st.address_id
AND act.par_row_id = p.row_id)cust,
s_addr_per addr
WHERE payment_type = 'Prepaid'
AND addr.row_id = cust.address_id
AND ROWNUM < 10000 ;
Following is the execution plan:
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9999 |00:15:26.20 | 3528K| 426K|
| 1 | NESTED LOOPS | | 10128 | 1 | 129 |00:14:06.27 | 1750K| 401K|
| 2 | NESTED LOOPS | | 10128 | 1 | 438K|00:18:24.58 | 1310K| 384K|
|* 3 | TABLE ACCESS BY INDEX ROWID | S_ASSET | 10128 | 45 | 438K|00:16:32.36 | 485K| 374K|
|* 4 | INDEX RANGE SCAN | S_ASSET_F16 | 10128 | 55 | 532K|00:00:46.17 | 87234 | 14020 |
|* 5 | INDEX UNIQUE SCAN | S_INV_PROF_P1 | 438K| 1 | 438K|00:00:37.24 | 825K| 9246 |
|* 6 | TABLE ACCESS BY INDEX ROWID | S_INV_PROF | 438K| 1 | 129 |00:00:36.57 | 439K| 16946 |
| 7 | NESTED LOOPS | | 10128 | 1 | 129 |00:00:04.76 | 1697K| 0 |
| 8 | NESTED LOOPS | | 10128 | 1 | 438K|00:00:05.17 | 1257K| 0 |
|* 9 | TABLE ACCESS BY INDEX ROWID | S_ASSET | 10128 | 45 | 438K|00:00:02.85 | 432K| 0 |
|* 10 | INDEX RANGE SCAN | S_ASSET_F16 | 10128 | 55 | 532K|00:00:00.55 | 42933 | 0 |
|* 11 | INDEX UNIQUE SCAN | S_INV_PROF_P1 | 438K| 1 | 438K|00:00:01.50 | 825K| 0 |
|* 12 | TABLE ACCESS BY INDEX ROWID | S_INV_PROF | 438K| 1 | 129 |00:00:00.80 | 439K| 0 |
|* 13 | COUNT STOPKEY | | 129 | | 128 |00:00:01.39 | 7081 | 495 |
| 14 | NESTED LOOPS | | 129 | 1 | 128 |00:00:01.39 | 7081 | 495 |
| 15 | NESTED LOOPS | | 129 | 1 | 184 |00:00:01.39 | 6896 | 494 |
| 16 | NESTED LOOPS | | 129 | 1 | 184 |00:00:01.38 | 6214 | 493 |
| 17 | NESTED LOOPS | | 129 | 1 | 288 |00:00:01.20 | 5324 | 414 |
|* 18 | TABLE ACCESS BY INDEX ROWID| S_ASSET | 129 | 1 | 320 |00:00:00.46 | 3611 | 221 |
|* 19 | INDEX RANGE SCAN | S_ASSET_F16 | 129 | 55 | 4659 |00:00:00.01 | 524 | 0 |
|* 20 | TABLE ACCESS BY INDEX ROWID| S_ORG_EXT | 320 | 1 | 288 |00:00:00.63 | 1713 | 193 |
|* 21 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 320 | 1 | 320 |00:00:00.25 | 1090 | 45 |
| 22 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 288 | 1 | 184 |00:00:00.25 | 890 | 79 |
|* 23 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 288 | 1 | 184 |00:00:00.12 | 706 | 26 |
|* 24 | INDEX UNIQUE SCAN | S_INV_PROF_P1 | 184 | 1 | 184 |00:00:00.01 | 682 | 1 |
|* 25 | TABLE ACCESS BY INDEX ROWID | S_INV_PROF | 184 | 1 | 128 |00:00:00.01 | 185 | 1 |
|* 26 | COUNT STOPKEY | | 1 | | 9999 |00:15:26.20 | 3528K| 426K|
| 27 | NESTED LOOPS | | 1 | 10001 | 9999 |00:15:26.18 | 3528K| 426K|
| 28 | NESTED LOOPS | | 1 | 10001 | 9999 |00:15:20.52 | 3518K| 426K|
|* 29 | VIEW | | 1 | 8203K| 9999 |00:15:20.44 | 3517K| 426K|
| 30 | NESTED LOOPS | | 1 | 8203K| 10128 |00:00:47.58 | 62992 | 25383 |
| 31 | NESTED LOOPS | | 1 | 8203K| 10128 |00:00:30.49 | 52563 | 16547 |
| 32 | NESTED LOOPS | | 1 | 8203K| 10128 |00:00:18.62 | 21511 | 9596 |
| 33 | TABLE ACCESS FULL | ADDRESS_STATS | 1 | 759K| 3 |00:00:00.01 | 6 | 5 |
| 34 | TABLE ACCESS BY INDEX ROWID| S_ORG_EXT | 3 | 11 | 10128 |00:00:18.91 | 21505 | 9591 |
|* 35 | INDEX RANGE SCAN | S_ORG_EXT_M3 | 3 | 12 | 10128 |00:00:03.49 | 1323 | 653 |
|* 36 | INDEX UNIQUE SCAN | S_PARTY_P1 | 10128 | 1 | 10128 |00:00:12.65 | 31052 | 6951 |
| 37 | TABLE ACCESS BY INDEX ROWID | S_PARTY | 10128 | 1 | 10128 |00:00:16.19 | 10429 | 8836 |
|* 38 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 9999 | 1 | 9999 |00:00:00.07 | 706 | 0 |
| 39 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 9999 | 1 | 9999 |00:00:00.08 | 9999 | 0 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AST2"."BILL_PROFILE_ID" IS NOT NULL)
4 - access("AST2"."SERV_ACCT_ID"=:B1)
5 - access("BF"."ROW_ID"="AST2"."BILL_PROFILE_ID")
6 - filter("BF"."PAYMENT_TYPE_CD"='Postpaid')
9 - filter("AST2"."BILL_PROFILE_ID" IS NOT NULL)
10 - access("AST2"."SERV_ACCT_ID"=:B1)
11 - access("BF"."ROW_ID"="AST2"."BILL_PROFILE_ID")
12 - filter("BF"."PAYMENT_TYPE_CD"='Postpaid')
13 - filter(ROWNUM<2)
18 - filter(("AST3"."ROW_ID"="AST3"."ROOT_ASSET_ID" AND "AST3"."BILL_PROFILE_ID" IS NOT NULL AND
"AST3"."PROM_INTEG_ID" IS NULL))
19 - access("AST3"."SERV_ACCT_ID"=:B1)
20 - filter("BA"."PR_ADDR_ID" IS NOT NULL)
21 - access("AST3"."BILL_ACCNT_ID"="BA"."PAR_ROW_ID")
23 - access("BA"."PR_ADDR_ID"="ADDR"."ROW_ID")
24 - access("AST3"."BILL_PROFILE_ID"="BF2"."ROW_ID")
25 - filter("BF2"."PAYMENT_TYPE_CD"='Postpaid')
26 - filter(ROWNUM<10000)
29 - filter("PAYMENT_TYPE"='Prepaid')
35 - access("ACT"."PR_ADDR_ID"="ST"."ADDRESS_ID")
filter("ACT"."PR_ADDR_ID" IS NOT NULL)
36 - access("ACT"."PAR_ROW_ID"="P"."ROW_ID")
38 - access("ADDR"."ROW_ID"="CUST"."ADDRESS_ID")
Note
-----
- dynamic sampling used for this statement (level=1)
96 rows selected.