Home » SQL & PL/SQL » SQL & PL/SQL » Tuning this Query
icon4.gif  Tuning this Query [message #223245] Thu, 08 March 2007 02:42 Go to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Hi,

Pls help me to Tuning this Query... This query is taking long time to execute. approx 160 secs. i need to bring this down to atleast 1 minute.

Query is Attached along with the explain plan..





[Updated on: Thu, 08 March 2007 02:45]

Report message to a moderator

Re: Tuning this Query [message #223246 is a reply to message #223245] Thu, 08 March 2007 02:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
For those of us that are reluctant to download files, see the content below:
SELECT   'C'
       , clr_claim_no "CLR_CLAIM_NO_MIC"
       , clr_location_code "CLR_LOCATION_CODE_MIC"
       , cr_policy_no "clm_policy_no_MIC"
       , cr_policy_no || '-' || cr_policy_renew_no "CR_POLICY_NO_MIC"
       , cr_policy_renew_no "CR_POLICY_RENEW_NO_MIC"
       , cr_loss_date "CR_LOSS_DATE"
       , 'Claim'
       , clr_no_of_vehicles no_of_vehicles_mic
       , b.gtablename "Cover_Desc_MIC"
       , clr_coverage_type "CLR_COVERAGE_TYPE_MIC"
       , clr_col_code "CLR_COL_CODE_MIC"
       , a.gtablename "Cause Of Loss_MIC"
       , (SUM (  (  NVL (cf_ind_reserve, 0)
                  + NVL (cf_ded_refund_paid, 0)
                  + NVL (cf_ind_paid, 0)
                 )
               - (NVL (cf_ind_recovered, 0))
              )
         ) "GROSS_DAMAGE_MIC"
       , SUM (NVL (cf_ded_applied, 0)) - SUM (NVL (cf_ded_refund_paid, 0))
                                                             "DEDUCTIBLE_MIC"
       , SUM (NVL (cf_ind_paid, 0)) "PAID_LOSS_MIC"
       , (  ((  SUM (NVL (cf_salv_reserve, 0))
              + SUM (  NVL (cf_salv_recovered_gross, 0)
                     - NVL (cf_salv_exp_paid_gross, 0)
                     - NVL (cf_salv_exp_retained_gross, 0)
                    )
             )
            )
          + ((  SUM (NVL (cf_subro_reserve, 0))
              + SUM (  NVL (cf_subro_recovered_gross, 0)
                     - NVL (cf_subro_exp_paid_gross, 0)
                     - NVL (cf_subro_exp_retained_gross, 0)
                    )
             )
            )
         ) "RECOVERY_MIC"
       , DECODE (SIGN ((  (SUM (  (  NVL (cf_ind_reserve, 0)
                                   + NVL (cf_ded_refund_paid, 0)
                                   + NVL (cf_ind_paid, 0)
                                  )
                                - (NVL (cf_ind_recovered, 0))
                               )
                          )
                        - (  (  SUM (NVL (cf_salv_reserve, 0))
                              + SUM (  NVL (cf_salv_recovered_gross, 0)
                                     - NVL (cf_salv_exp_paid_gross, 0)
                                     - NVL (cf_salv_exp_retained_gross, 0)
                                    )
                             )
                           + (  SUM (NVL (cf_subro_reserve, 0))
                              + SUM (  NVL (cf_subro_recovered_gross, 0)
                                     - NVL (cf_subro_exp_paid_gross, 0)
                                     - NVL (cf_subro_exp_retained_gross, 0)
                                    )
                             )
                          )
                       )
                      )
               , -1, 0
               , (  (SUM (  (  NVL (cf_ind_reserve, 0)
                             + NVL (cf_ded_refund_paid, 0)
                             + NVL (cf_ind_paid, 0)
                            )
                          - (NVL (cf_ind_recovered, 0))
                         )
                    )
                  - (  (  SUM (NVL (cf_salv_reserve, 0))
                        + SUM (  NVL (cf_salv_recovered_gross, 0)
                               - NVL (cf_salv_exp_paid_gross, 0)
                               - NVL (cf_salv_exp_retained_gross, 0)
                              )
                       )
                     + (  SUM (NVL (cf_subro_reserve, 0))
                        + SUM (  NVL (cf_subro_recovered_gross, 0)
                               - NVL (cf_subro_exp_paid_gross, 0)
                               - NVL (cf_subro_exp_retained_gross, 0)
                              )
                       )
                    )
                 )
                ) "NET_PAID_MIC"
       , TRUNC (cr_reported_date) "CR_REPORTED_DATE_MIC"
FROM     claim_register
       , claimant_register
       , claim_financials
       , answer_table a
       , answer_table b
WHERE    cr_claim_no = cf_claim_no
AND      cr_claim_no = clr_claim_no
AND      clr_claim_no = cf_claim_no
AND      clr_loc_master_code = cf_location_code
AND      clr_coverage_type = cf_coverage_code
AND      a.gtablecode1 = 'CAUSE_OF_LOSS'
AND      b.gtablecode1 = 'COVERAGE_TYPE'
AND      a.gtablecode2 = clr_col_code
AND      b.gtablecode2 = clr_coverage_type
AND      cr_line = a.ans_returned_chr
AND      TRUNC (cr_loss_date) >= TO_DATE ('04/30/2006 ', 'MM/DD/YYYY')
AND      TRUNC (cr_loss_date) <= TO_DATE ('02/08/2007 ', 'MM/DD/YYYY')
AND      TRUNC (cr_reported_date) <= TO_DATE ('03/06/2007 ', 'MM/DD/YYYY')
GROUP BY clr_claim_no
       , clr_location_code
       , clr_coverage_type
       , cr_policy_no || '-' || cr_policy_renew_no
       , cr_policy_renew_no
       , cr_policy_no
       , cr_loss_date
       , 'SOURCE OF CLAIM'
       , clr_no_of_vehicles
       , clr_col_code
       , TRUNC (cr_reported_date)
       , a.gtablename
       , b.gtablename
ORDER BY cr_loss_date




Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	11  	 	      	             	 
  SORT GROUP BY		1  	241  	11  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	RENDEV.CLAIM_FINANCIALS	1  	51  	2  	 	      	             	 
      NESTED LOOPS		1  	241  	9  	 	      	             	 
        NESTED LOOPS		1  	190  	7  	 	      	             	 
          MERGE JOIN CARTESIAN		1  	145  	5  	 	      	             	 
            NESTED LOOPS		1  	98  	4  	 	      	             	 
              INDEX FAST FULL SCAN	RENDEV.DLR_SRH_RPT_IDX	1  	45  	3  	 	      	             	 
              INDEX RANGE SCAN	RENDEV.ANS_DLR_SRCH_IDX	1  	53  	1  	 	      	             	 
            BUFFER SORT		11  	517  	4  	 	      	             	 
              INDEX RANGE SCAN	RENDEV.ANS_DLR_SRCH_IDX	11  	517  	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	RENDEV.CLAIMANT_REGISTER	1  	45  	2  	 	      	             	 
            INDEX RANGE SCAN	RENDEV.CLR_CLAIM_IDX	1  	 	1  	 	      	             	 
        INDEX RANGE SCAN	RENDEV.CF_CLAIM_IDX	1  	 	1  	 	      	             	 


MHE
Re: Tuning this Query [message #223254 is a reply to message #223246] Thu, 08 March 2007 03:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How many rows should this query return?

Are your stats up to date?
Re: Tuning this Query [message #223259 is a reply to message #223254] Thu, 08 March 2007 03:31 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
CLAIM_FINANCIALS - 30531 Rows

CLAIM_REGISTER - 4399 Rows

CLAIMANT_REGISTER - 4103 Rows

ANSWER TABLE is Master table.

Around 8400 rows are satisfing the condition.


RENDEV.DLR_SRH_RPT_IDX is the new Index created by Me while Tuning as CLAIM_REGISTER table was getting Full Scan.

[Updated on: Thu, 08 March 2007 03:33]

Report message to a moderator

Re: Tuning this Query [message #223262 is a reply to message #223259] Thu, 08 March 2007 03:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, I suspect your stats are out of date - the CBO is expecting in the order of 1 row to come out of that query.
Re: Tuning this Query [message #223263 is a reply to message #223259] Thu, 08 March 2007 03:44 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
- Stats are Up To Date

- Desc Claim_Register

CR_ERMX_SLNO NUMBER NOT NULL,
CR_CLAIM_NO VARCHAR2(25 BYTE),
CR_CLAIM_NO_REV NUMBER(2),
CR_LOSS_DATE DATE NOT NULL,
CR_REPORTED_DATE DATE NOT NULL,
CR_DATE_ENTERED DATE NOT NULL,
CR_LINE VARCHAR2(6 BYTE),
CR_SUBLINE VARCHAR2(6 BYTE),
CR_COUNTRY VARCHAR2(3 BYTE),
CR_STATE_CODE VARCHAR2(2 BYTE),
CR_REGION_CODE VARCHAR2(30 BYTE),
CR_COMPANY VARCHAR2(6 BYTE),
CR_POLICY_NO VARCHAR2(15 BYTE),
CR_POLICY_RENEW_NO VARCHAR2(2 BYTE),
CR_MIC_POLICY_NO VARCHAR2(15 BYTE),
CR_POLICY_EFF_DATE DATE,
CR_POLICY_EXP_DATE DATE,
CR_POLICY_STATUS VARCHAR2(2 BYTE),
CR_CLAIM_POLICY_STATUS VARCHAR2(2 BYTE),
CR_LOSS_LOC_CODE VARCHAR2(8 BYTE) NOT NULL,
CR_CATASTROPHE_CODE VARCHAR2(30 BYTE),
CR_COL_CODE VARCHAR2(30 BYTE) NOT NULL,
CR_LOSS_DESCRIPTION VARCHAR2(1000 BYTE),
CR_INSURED_CODE VARCHAR2(8 BYTE),
CR_AGENT_CODE VARCHAR2(8 BYTE),
CR_SUBROGATION_FLAG VARCHAR2(1 BYTE),
CR_INSVEH_ON_CLAIM NUMBER(4),
CR_INSVEH_ENTERED NUMBER(4),
CR_EST_DAMAGE NUMBER(14,2),
CR_CLAIM_COMPLETED VARCHAR2(1 BYTE),
CR_CLAIM_STATUS VARCHAR2(4 BYTE),
CR_CLAIM_VERIFY_FLAG VARCHAR2(2 BYTE),
CR_DUP_PROCESS_NO NUMBER(10),
CR_WASI90_CLAIM_NO VARCHAR2(25 BYTE),
CR_CATS_ASSIGN_FLAG VARCHAR2(1 BYTE),
CR_CATS_INSTRUCTIONS VARCHAR2(1000 BYTE),
CR_SYSTEM_ACTIVITY_NO VARCHAR2(14 BYTE),
CR_CREATED_ON DATE DEFAULT SYSDATE,
CR_CREATED_BY VARCHAR2(15 BYTE) NOT NULL,
CR_MODIFIED_ON DATE,
CR_MODIFIED_BY VARCHAR2(15 BYTE),
CR_NICB_FLAG VARCHAR2(1 BYTE),
CR_POLICE_REPORT_FLAG VARCHAR2(1 BYTE),
CR_CTC_PERSON_PHONE VARCHAR2(20 BYTE),
CR_CTC_PERSON_FAX VARCHAR2(20 BYTE),
CR_RPT_PERSON_NAME VARCHAR2(100 BYTE),
CR_RPT_PERSON_PHONE VARCHAR2(20 BYTE),
CR_RPT_PERSON_FAX VARCHAR2(20 BYTE),
CR_CTC_PERSON_NAME VARCHAR2(100 BYTE)


- New Index Created

CREATE INDEX RENDEV.DLR_SRH_RPT_IDX ON RENDEV.CLAIM_REGISTER
(CR_CLAIM_NO, CR_LINE, CR_POLICY_NO, CR_POLICY_RENEW_NO, CR_LOSS_DATE,
CR_REPORTED_DATE)

All this Column in the Following Index are used in the Where Clause Condition in the above Query
Re: Tuning this Query [message #223431 is a reply to message #223263] Thu, 08 March 2007 20:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Fun it through SQL TRACE and TK*Prof to see the number of rows processed at each step.

Oracle is using the CARTESIAN JOIN because it thinks the join of CLAIM_REGISTER to ANSWER_TABLE (A) will return a single row. I suspect that this could be wrong. Did you gather stats on your new index?

Ross Leishman
Re: Tuning this Query [message #223730 is a reply to message #223245] Sat, 10 March 2007 15:38 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Quote:
CREATE INDEX RENDEV.DLR_SRH_RPT_IDX ON RENDEV.CLAIM_REGISTER
(CR_CLAIM_NO, CR_LINE, CR_POLICY_NO, CR_POLICY_RENEW_NO, CR_LOSS_DATE,
CR_REPORTED_DATE)

All this Column in the Following Index are used in the Where Clause Condition in the above Query


IMHO you don't need that index.
Try following:

1. CREATE INDEX ...
ON RENDEV.CLAIM_REGISTER (CR_LOSS_DATE, CR_REPORTED_DATE)...

2. Change the query:
...
AND cr_loss_date >= TO_DATE ('04/30/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND cr_loss_date <= TO_DATE ('02/08/2007 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
AND cr_reported_date <= TO_DATE ('03/06/2007 23:59:59', 'MM/DD/YYYY HH24:MI:SS')


I assumed that all other indexes are correct ones and in place.

Post EXPLAIN of corrected query.

HTH.
Michael
Re: Tuning this Query [message #224645 is a reply to message #223245] Wed, 14 March 2007 22:07 Go to previous message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Thnx Micheal,

I have applied the changes suggested by you and the performance of the query has increased to a great extent from 130 secs to 20 secs now.

Thnx
Atul

Previous Topic: 2 Dimensional Arrays into PL/SQL
Next Topic: ORA-03001: unimplemented feature
Goto Forum:
  


Current Time: Sun Dec 04 02:42:48 CST 2016

Total time taken to generate the page: 0.09635 seconds