• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

Oracle 11g - SQL Tuning

Please help me in tuning the below sql:

SELECT
   TRUNC(SYSDATE, 'IW') -7 AS START_DATE,
   TRUNC(SYSDATE, 'IW') -1 AS END_DATE,
   ACCT_NBR,
   HERITAGE,
   GUID,
   BORROWER_SIGN_DATE,
   IMAGE_DATETIME,
   ATTR_CRE_DT
FROM
   (SELECT  
        D.CLIENT_LOAN_NUMBER AS ACCT_NBR,
        (CASE WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'C' THEN 'CHASE'                        
              WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'W' THEN 'WAMU'                        
              WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'E' THEN 'EMC'                        
              ELSE 'UNKNOWN'                        
         END) AS HERITAGE,
        D.GUID,
        to_date(da.doc_attr_val, 'mm/dd/yyyy') AS borrower_sign_date,
        DA.CRE_DT as ATTR_CRE_DT,
        D.IMAGE_DATETIME,
        ROW_NUMBER() OVER(PARTITION BY  (CASE WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'C' THEN 'CHASE'                        
                                              WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'W' THEN 'WAMU'                        
                                              WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'E' THEN 'EMC'                        
                                              ELSE 'UNKNOWN'                        
                                           END),
                                         D.CLIENT_LOAN_NUMBER
                          ORDER BY D.GUID DESC, to_date(da.doc_attr_val, 'mm/dd/yyyy') DESC ) AS RK
     FROM
        ditmgr.DOCMOD_GRD_DAILY_IMAGES_TMQ D
     JOIN ditmgr.DOCMOD_GRD_DAILY_IMAGE_ATTRS DA
        ON  D.GUID = DA.LLNGUID  
      WHERE D.IMAGE_DATETIME >= TRUNC(SYSDATE) -90
        AND D.UPDATE_DT >= TRUNC(SYSDATE) -14
        AND D.DOCUMENT_TYPE = 'Signed Final Mod Agreement'
        AND D.DOCUMENT_NAME = 'Modification Document from Borrower'
        AND DA.DOC_ATTR = 'Signature Date'  
        AND DA.CRE_DT >= TRUNC(SYSDATE, 'IW') -7  -- Borrower Signature Created Date is within last week.
        AND DA.CRE_DT < TRUNC(SYSDATE, 'IW')
        )
where rk =1;

Here is the information on the tables:
1) ditmgr.DOCMOD_GRD_DAILY_IMAGES_TMQ has 103 million records
2) ditmgr.DOCMOD_GRD_DAILY_IMAGE_ATTRS has 90 million records
3) There is no Primary key or unique index on these tables.
4) The non unique indexes are present on the following columns of DOCMOD_GRD_DAILY_IMAGES_TMQ:
GUID
CLIENT_LOAN_NUMBER
LINE_OF_BUSINESS_NM
DOCUMENT_TYPE
IMAGE_DATETIME
5) The non unique index is present on only one column:LLNGUID of  DOCMOD_GRD_DAILY_IMAGE_ATTRS:
6) Both tables are partitioned by hash: DOCMOD_GRD_DAILY_IMAGE_ATTRS using LLNGUID and DOCMOD_GRD_DAILY_IMAGES_TMQ using GUID
Please find attached the explain plan in "Explain_plan_old_sql.xls"

I did the following things:
1) After seeing the execution paths, I created the non-unique index on DOCMOD_GRD_DAILY_IMAGE_ATTRS.
I created non-unique index on DOC_ATTR and CRE_DT. Please find attached the new explain plan output in Explain_plan_new_sql.xls

2) Changed the order of tables,join, filters  to influence the optimizer decision on the driving table:
SELECT
   TRUNC(SYSDATE, 'IW') -7 AS START_DATE,
   TRUNC(SYSDATE, 'IW') -1 AS END_DATE,
   ACCT_NBR,
   HERITAGE,
   GUID,
   BORROWER_SIGN_DATE,
   IMAGE_DATETIME,
   ATTR_CRE_DT
FROM  
   (SELECT  
        D.CLIENT_LOAN_NUMBER AS ACCT_NBR,
        (CASE WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'C' THEN 'CHASE'                        
              WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'W' THEN 'WAMU'                        
              WHEN SUBSTR(TRIM(D.LINE_OF_BUSINESS_NM),1,1)= 'E' THEN 'EMC'                        
              ELSE 'UNKNOWN'                        
         END) AS HERITAGE,
        D.GUID,
        to_date(da.doc_attr_val, 'mm/dd/yyyy') AS borrower_sign_date,
        DA.CRE_DT as ATTR_CRE_DT,
        D.IMAGE_DATETIME, ROW_NUMBER() OVER(PARTITION BY D.CLIENT_LOAN_NUMBER ORDER BY D.GUID DESC, to_date(da.doc_attr_val, 'mm/dd/yyyy') DESC ) AS RK
     FROM
             ditmgr.DOCMOD_GRD_DAILY_IMAGES_TMQ D, ditmgr.DOCMOD_GRD_DAILY_IMAGE_ATTRS DA
      WHERE D.DOCUMENT_TYPE = 'Signed Final Mod Agreement'
        AND D.DOCUMENT_NAME = 'Modification Document from Borrower'        
        and D.IMAGE_DATETIME >= TRUNC(SYSDATE) -90
        AND D.UPDATE_DT >= TRUNC(SYSDATE) -14
        and D.GUID = DA.LLNGUID  
        and DA.DOC_ATTR = 'Signature Date'  
        AND DA.CRE_DT >= TRUNC(SYSDATE, 'IW') -7  -- Borrower Signature Created Date is within last week.
        AND DA.CRE_DT < TRUNC(SYSDATE, 'IW')
        )        
where rk =1;
3) Also used Ordered hint to make Oracle to evaluate the tables in the order present in the WHERE clause.
4) Tables and indexes are also analyzed.
5) Inside the row_number function, there is no need for partitioning by line_of_business(with case statement). Partition by client_loan_number should be sufficient. Therefore case statement on LINE_OF_BUSINESS is applied only once(was twice before) in the sql now

After making these changes also, the sql is not running in production DB(Oracle 11g). I had let it run for more than 3 hours and then terminated.

Please let me know your suggestions in tuning the above sql.
Regards
Siva
 Explain-plan-old-sql.xls Explain-plan-new-sql.xls Explain-plan-old-sql.xls
0
sivi_3883
Asked:
sivi_3883
1 Solution
 
sivi_3883Author Commented:
Please let me know if I need to provide anymore information.
0
 
AkenathonCommented:
The cardinalities and the field search_columns are missing, among many others. Also, what makes you think this SQL can be made to run "fast" (whatever that means to you)?

Also, the spreadsheets are not ordered by ID (of course I took care of that), and they seem truncated at id=9:

ID      PARENT_ID      OPERATION      OPTIONS      OBJECT_NAME
9      4                TABLE ACCESS      BY GLOBAL INDEX ROWID      DOCMOD_GRD_DAILY_IMAGES_TMQ

That's a table accessed from an index... but it doesn't have a child index lookup node.
0
 
gajmpCommented:
if it is possible to make the NESTED LOOP join to HASH JOIN then try that and test it. if its not workout then then we will create MV and refresh some scheduled time. that will be good option
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now