Home » RDBMS Server » Performance Tuning » SQL Tuning- Need help (Oracle 10g Enterprise Edition Release 10.2.0.3.0 - Prod)
SQL Tuning- Need help [message #438096] |
Thu, 07 January 2010 02:00 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
marlon_loyolite
Messages: 66 Registered: July 2006
|
Member |
|
|
Hi,
Have a query like below,
SELECT cr.credit_request_id AS credit_request_id,
cr.borrower_id AS borrower_id, cr.status_code AS status_code,
1 AS prev_month_count, 0 AS new_count, 0 AS reinitiated_count,
0 AS repaid_cancelled_count, 0 AS change_to_existing,
0 AS prev_year_count, 0 AS fiscal_year_count, 0 AS refer_to_slsb_count,
cph.cms_portfolio_num AS cms_portfolio_num,
cph.branch_cluster_transit_num AS branch_transit_num,
cph.district_bm_transit_num AS district_bm_transit_num,
cph.district_transit_num AS district_transit_num,
cph.market_transit_num AS market_transit_num,
cph.region_transit_num AS region_transit_num,
cph.total_rm_transit_num AS total_rm_transit_num,
NVL (cr.authorized_credit_limit_amt,
cr.proposed_credit_limit_amt
) AS prev_month_total_auth_amt,
ROUND
((SELECT /*+ INDEX(wf,indx_o_fac_id) */ SUM (fac_os_bal_cad) AS facility_balance_amt
FROM mv_rpt_d_wcdw_facility wf, mv_rpt_facility f
WHERE wf.icap_original_facility_id =
NVL (f.original_facility_id, f.facility_id)
AND f.credit_request_id = cr.credit_request_id
AND NVL (f.is_deleted, 0) = 0
AND wf.snapshot_date =
(SELECT MAX (wf1.snapshot_date)
FROM mv_rpt_d_wcdw_facility wf1
WHERE wf1.icap_original_facility_id =
wf.icap_original_facility_id))
+ NVL((SELECT SUM (fac_os_bal_cad) AS facility_balance_amt
FROM mv_rpt_d_wcdw_facility wf
WHERE wf.icap_original_facility_id = -99
AND wf.snapshot_date =
(SELECT MAX (wf1.snapshot_date)
FROM mv_rpt_d_wcdw_facility wf1
WHERE wf1.icap_original_facility_id = wf.icap_original_facility_id
AND wf1.icap_original_facility_id = -99)
AND EXISTS (
SELECT 1
FROM mv_rpt_crdt_rqst_participant crp
WHERE (crp.customer_id IN (
SELECT c.customer_id
FROM mv_rpt_customer c
WHERE c.customer_identity_id = wf.icap_cust_identity_id)
)
AND crp.credit_request_id = cr.credit_request_id
AND crp.credit_request_role_code = 'B')),0)) AS prev_month_os_crdlmt_amt,
NULL AS new_total_auth_amt, NULL AS new_os_crdlmt_amt,
NULL AS reinitiate_total_auth_amt, NULL AS reinitiate_os_crdlmt_amt,
NULL AS repaid_total_auth_amt, NULL AS repaid_os_crdlmt_amt,
NULL AS change_to_exist_auth_amt,
NULL AS change_to_exist_os_crdlmt_amt,
NULL AS prev_year_total_auth_amt, NULL AS prev_year_os_crdlmt_amt,
NULL AS fiscal_year_total_auth_amt, NULL AS fiscal_year_os_crdlmt_amt,
NULL AS refer_to_slsb_total_auth_amt,
NULL AS refer_to_slsb_os_crdlmt_amt,
cph.branch_cluster_name AS branch_cluster_name,
cph.district_bm_name AS district_bm_name,
cph.district_name AS district_name, cph.market_name AS market_name,
cph.region_name AS region_name
FROM mv_rpt_credit_request cr,
cms_portfolio_hierarchy cph,
mv_rpt_cms_portfolio cp,
mv_rpt_borrower b
WHERE cr.borrower_id = b.borrower_id
AND b.ocp_portfolio_id = cp.ocp_portfolio_id
AND cph.cms_portfolio_num = cp.cms_portfolio_num
AND TRUNC (cr.activation_dttm) <= :v_rpt_prev_month_end_date
AND ( cr.status_code = 'ACTIVE'
OR ( cr.status_code = 'INACTIVE'
AND TRUNC (cr.cancel_dttm) > :v_rpt_prev_month_end_date
)
)
AND cr.performing_ind = '1'
Explain Plan
Plan hash value: 1854681316
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 301 | 5345 (2)| 00:01:05 |
| 1 | SORT AGGREGATE | | 1 | 73 | | |
|* 2 | FILTER | | | | | |
| 3 | MAT_VIEW ACCESS BY INDEX ROWID | MV_RPT_D_WCDW_FACILITY | 3 | 84 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 196 | 14308 | 3581 (3)| 00:00:43 |
|* 5 | MAT_VIEW ACCESS FULL | MV_RPT_FACILITY | 57 | 2565 | 3410 (3)| 00:00:41 |
|* 6 | INDEX RANGE SCAN | INDX_O_FAC_ID | 3 | | 0 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 15 | | |
| 8 | MAT_VIEW ACCESS BY INDEX ROWID | MV_RPT_D_WCDW_FACILITY | 3 | 45 | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | INDX_O_FAC_ID | 3 | | 1 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 62 | | |
|* 11 | FILTER | | | | | |
|* 12 | HASH JOIN | | 2572K| 152M| 696 (8)| 00:00:09 |
| 13 | VIEW | VW_SQ_1 | 16038 | 344K| 329 (3)| 00:00:04 |
| 14 | SORT GROUP BY | | 16038 | 328K| 329 (3)| 00:00:04 |
|* 15 | MAT_VIEW ACCESS FULL | MV_RPT_D_WCDW_FACILITY | 16038 | 328K| 329 (3)| 00:00:04 |
|* 16 | MAT_VIEW ACCESS FULL | MV_RPT_D_WCDW_FACILITY | 16038 | 626K| 329 (3)| 00:00:04 |
| 17 | NESTED LOOPS | | 1 | 33 | 5 (0)| 00:00:01 |
|* 18 | MAT_VIEW ACCESS BY INDEX ROWID| MV_RPT_CRDT_RQST_PARTICIPANT | 1 | 14 | 4 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | XPK_CREDIT_REQUEST_PARTICIPA | 2 | | 3 (0)| 00:00:01 |
|* 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV_RPT_CUSTOMER | 1 | 19 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | XPK_CUSTOMER | 1 | | 0 (0)| 00:00:01 |
|* 22 | HASH JOIN | | 1 | 301 | 5345 (2)| 00:01:05 |
| 23 | MAT_VIEW ACCESS FULL | MV_RPT_CMS_PORTFOLIO | 3578 | 93028 | 13 (0)| 00:00:01 |
| 24 | MERGE JOIN CARTESIAN | | 17240 | 4629K| 5331 (2)| 00:01:04 |
| 25 | NESTED LOOPS | | 1 | 111 | 5180 (2)| 00:01:03 |
|* 26 | MAT_VIEW ACCESS FULL | MV_RPT_CREDIT_REQUEST | 1 | 92 | 5179 (2)| 00:01:03 |
| 27 | MAT_VIEW ACCESS BY INDEX ROWID | MV_RPT_BORROWER | 1 | 19 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | XPK_BORROWER | 1 | | 0 (0)| 00:00:01 |
| 29 | BUFFER SORT | | 19622 | 3142K| 5330 (2)| 00:01:04 |
| 30 | TABLE ACCESS FULL | CMS_PORTFOLIO_HIERARCHY | 19622 | 3142K| 151 (2)| 00:00:02 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("WF"."SNAPSHOT_DATE"= (SELECT /*+ */ MAX("WF1"."SNAPSHOT_DATE") FROM "MV_RPT_D_WCDW_FACILITY"
"WF1" WHERE "WF1"."ICAP_ORIGINAL_FACILITY_ID"=:B1))
5 - filter("F"."CREDIT_REQUEST_ID"=:B1 AND NVL("F"."IS_DELETED",0)=0)
6 - access("WF"."ICAP_ORIGINAL_FACILITY_ID"=NVL("F"."ORIGINAL_FACILITY_ID","F"."FACILITY_ID"))
9 - access("WF1"."ICAP_ORIGINAL_FACILITY_ID"=:B1)
11 - filter( EXISTS (SELECT /*+ */ 0 FROM "MV_RPT_CRDT_RQST_PARTICIPANT" "CRP","MV_RPT_CUSTOMER" "C" WHERE
"CRP"."CUSTOMER_ID"="C"."CUSTOMER_ID" AND "C"."CUSTOMER_IDENTITY_ID"=TO_NUMBER(:B1) AND
"CRP"."CREDIT_REQUEST_ID"=:B2 AND "CRP"."CREDIT_REQUEST_ROLE_CODE"='B'))
12 - access("WF"."SNAPSHOT_DATE"="VW_COL_1" AND "ICAP_ORIGINAL_FACILITY_ID"="WF"."ICAP_ORIGINAL_FACILITY_ID"
)
15 - filter("WF1"."ICAP_ORIGINAL_FACILITY_ID"=(-99))
16 - filter("WF"."ICAP_ORIGINAL_FACILITY_ID"=(-99))
18 - filter("CRP"."CREDIT_REQUEST_ROLE_CODE"='B')
19 - access("CRP"."CREDIT_REQUEST_ID"=:B1)
20 - filter("C"."CUSTOMER_IDENTITY_ID"=TO_NUMBER(:B1))
21 - access("CRP"."CUSTOMER_ID"="C"."CUSTOMER_ID")
22 - access("B"."OCP_PORTFOLIO_ID"="CP"."OCP_PORTFOLIO_ID" AND
"CP"."CMS_PORTFOLIO_NUM"=TO_NUMBER("CPH"."CMS_PORTFOLIO_NUM"))
26 - filter("CR"."PERFORMING_IND"='1' AND ("CR"."STATUS_CODE"='ACTIVE' OR "CR"."STATUS_CODE"='INACTIVE' AND
TRUNC(INTERNAL_FUNCTION("CR"."CANCEL_DTTM"))>:V_RPT_PREV_MONTH_END_DATE) AND
TRUNC(INTERNAL_FUNCTION("CR"."ACTIVATION_DTTM"))<=:V_RPT_PREV_MONTH_END_DATE)
28 - access("CR"."BORROWER_ID"="B"."BORROWER_ID")
Suggests me how this can be tuned or else how to restructure
this SQL. The performance got degraded after adding prev_month_os_crdlmt_amt (Two select queries) .
Regards,
Marlon
|
|
|
Re: SQL Tuning- Need help [message #438335 is a reply to message #438096] |
Fri, 08 January 2010 02:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Reported Message
Reported By: marlon_loyolite On: Fri, 08 January 2010 09:24 In: RDBMS Server » Performance Tuning » SQL Tuning- Need help
Reason You can please delete this particular thread. I got the answer. Regards, Marlon
Why don't you post the answer instead?
The forum is not just for you to get an answer but also for everyone to learn from others problem.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Thu Aug 22 03:57:13 CDT 2024
|