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
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
Michel Cadot
Messages: 63808
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
Previous Topic: to tune an SQL query
Next Topic: cardinality of join affected by transitivity
Goto Forum:
  


Current Time: Wed Sep 28 21:11:13 CDT 2016

Total time taken to generate the page: 0.13284 seconds