Home » RDBMS Server » Performance Tuning » Fine Tuning This query
Fine Tuning This query [message #197286] Tue, 10 October 2006 21:02 Go to next message
bagulia_pilla
Messages: 25
Registered: July 2006
Junior Member
Please help to fine tune this query.
Thanks in advance

SELECT   '1', 'U' AS update_status_ind, 'OLD', hist.transaction_date, alcm.limit_charge_map_id,
         alcm.charge_id, alcm.charge_detail_id, sc.sci_las_sec_id,
         hist.reference_id AS cms_collateral_id, sci_security_dtl_id, sc.charge_nature,
         sc.charge_amount, sc.charge_currency_code, sc.le_charge, sc.le_charge_date,
         sc.legal_charge_date, sc.le_juridict, sc.le_juridict_date, sc.le_govnlaw,
         sc.le_govnlaw_date,
         (CASE
            WHEN sc.prior_charge_currency IS NULL
              THEN NULL
            ELSE sc.prior_charge_amount
          END
         ) prior_charge_amount,
         (CASE
            WHEN sc.prior_charge_amount IS NULL
              THEN NULL
            ELSE sc.prior_charge_currency
          END
         ) prior_charge_currency,
         sc.security_rank, sc.prior_chargee, sc.prior_charge_type, sc.charge_type,
         sc.confirm_charge_date, alsm.cms_lsp_appr_lmts_id, alsm.sci_las_sys_gen_id,
         alsm.sci_las_llp_id, alsm.sci_las_lsp_id, alsm.sci_las_le_id, alsm.sci_las_lmt_id,
         alsm.sci_las_sec_id, alsm.las_pledgor_id
    FROM trans_history hist,
         cms_stage_security col,
         cms_stage_limit_charge_map slcm,
         cms_stage_limit_security_map slsm,
         cms_stage_charge_detail sc,
         cms_limit_charge_map alcm,
         cms_limit_security_map alsm,
         cms_charge_detail ac,
         (SELECT   MAX (HISTORY.transaction_date) transaction_date, HISTORY.transaction_id
            FROM trans_history HISTORY,
				 cms_stage_security col_stage,
                 (SELECT   MAX (h.transaction_date) last_trx_date, h.transaction_id
                      FROM trans_history h, cms_security c
                     WHERE h.transaction_date >= TO_DATE (:ctrl1dt, 'dd/mm/yyyy HH24:MI:SS')
                       AND h.transaction_date < TO_DATE (:ctrl2dt, 'dd/mm/yyyy HH24:MI:SS')
                       AND c.cms_collateral_id = TO_NUMBER (h.reference_id)
                       AND c.is_security_perfected = 'N'  
                       AND c.status NOT IN ('DELETED', 'PENDING_DELETE')
					   AND h.opsdesc NOT IN ('PART_DELETE', 'FULL_DELETE')                      
                       AND h.from_state = 'PENDING_UPDATE'
                       AND h.status = 'ACTIVE'
                       AND h.transaction_type = 'COL'
                  GROUP BY h.transaction_id) 
				  LAST_TRX_N
           WHERE HISTORY.transaction_id = LAST_TRX_N.transaction_id             
             AND HISTORY.from_state <> 'ACTIVE'             
             AND HISTORY.opsdesc NOT IN ('PART_DELETE', 'FULL_DELETE')
             AND HISTORY.status = 'ACTIVE'             
             AND (   (TRUNC (HISTORY.transaction_date) < TRUNC (LAST_TRX_N.last_trx_date)
                     )
                  OR (    TRUNC (HISTORY.transaction_date) = TRUNC (LAST_TRX_N.last_trx_date)
                      AND NOT EXISTS (
                             SELECT 1
                               FROM trans_history HISTORY1
                              WHERE HISTORY1.transaction_id =  LAST_TRX_N.transaction_id
                                AND HISTORY1.transaction_type = 'COL'
                                AND TRUNC (HISTORY1.transaction_date) < TRUNC (LAST_TRX_N.last_trx_date))
                     )
                 )
             AND HISTORY.staging_reference_id = col_stage.cms_collateral_id
             AND col_stage.is_security_perfected = 'Y'
        GROUP BY HISTORY.transaction_id )
		PREV_LAST_TRX
 WHERE hist.transaction_id = PREV_LAST_TRX.transaction_id
     AND hist.transaction_date = PREV_LAST_TRX.transaction_date   
     AND col.cms_collateral_id = TO_NUMBER (hist.staging_reference_id)     
     AND col.cms_collateral_id = slcm.cms_collateral_id
     AND slsm.charge_id = slcm.charge_id
     AND sc.charge_detail_id = slcm.charge_detail_id
     AND alsm.cms_lsp_appr_lmts_id = slsm.cms_lsp_appr_lmts_id
     AND alcm.cms_lsp_appr_lmts_id = slcm.cms_lsp_appr_lmts_id
     AND alsm.cms_collateral_id = TO_NUMBER (hist.reference_id)
     AND alsm.charge_id = alsm.charge_id
     AND ac.charge_detail_id = alcm.charge_detail_id
     AND ac.cms_ref_id = sc.cms_ref_id
     AND col.status NOT IN ('DELETED', 'PENDING_DELETE')
     AND (ac.status <> 'DELETED' OR ac.status IS NULL)
     AND alcm.status <> 'DELETED'
     AND slcm.status <> 'DELETED'
     AND alsm.update_status_ind <> 'D'
     AND slsm.update_status_ind <> 'D'



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

SELECT STATEMENT Optimizer Mode=CHOOSE																									1  	73  	 	      	             	 
  NESTED LOOPS																																					1  	305  	73  	 	      	             	 
    NESTED LOOPS																																				1  	238  	72  	 	      	             	 
      NESTED LOOPS																																			1  	211  	71  	 	      	             	 
        MERGE JOIN CARTESIAN																														1  	168  	69  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID								  CMS_R14_DEV.TRANS_HISTORY					1  	35  	3  	 	      	             	 
            NESTED LOOPS																																1  	68  	53  	 	      	             	 
              HASH JOIN																																	1  	33  	50  	 	      	             	 
                VIEW																																		1  	21  	20  	 	      	             	 
                  SORT GROUP BY																													1  	98  	20  	 	      	             	 
                    FILTER		  	 	 	 	      	             	 
                      NESTED LOOPS																											1  	98  	18  	 	      	             	 
                        TABLE ACCESS BY INDEX ROWID		CMS_R14_DEV.TRANS_HISTORY					1  	75  	17  	 	      	             	 
                          INDEX RANGE SCAN						CMS_R14_DEV.IDX_TEST1							1K	7  	 	      	             	 
                        TABLE ACCESS BY INDEX ROWID		CMS_R14_DEV.CMS_SECURITY					1  	23  	1  	 	      	             	 
                          INDEX UNIQUE SCAN						CMS_R14_DEV.SYS_C001217											1		32 K	 	 	 	      	             	 
                VIEW																																		1  	12  	29  	 	      	             	 
                  SORT GROUP BY																													1  	90  	29  	 	      	             	 
                    FILTER		  	 	 	 	      	             	 
                      NESTED LOOPS																											1  	90  	28  	 	      	             	 
                        NESTED LOOPS																										1  	78  	27  	 	      	             	 
                          VIEW																													1  	23  	20  	 	      	             	 
                            SORT GROUP BY																								1  	98  	20  	 	      	             	 
                              FILTER		  	 	 	 	      	             	 
                                NESTED LOOPS																						1  	98  	18  	 	      	             	 
                                  TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.TRANS_HISTORY	1  	75  	17  	 	      	             	 
                                    INDEX RANGE SCAN	CMS_R14_DEV.IDX_TEST1							1 K	 	7  	 	      	             	 
                                  TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_SECURITY	1  	23  	1  	 	      	             	 
                                    INDEX UNIQUE SCAN	CMS_R14_DEV.SYS_C0012171					32 K	 	 	 	      	             	 
                          TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.TRANS_HISTORY					1  	55  	7  	 	      	             	 
                            INDEX RANGE SCAN	CMS_R14_DEV.IDX_TR_HIST_1									25  	 	2  	 	      	             	 
                        TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_STAGE_SECURITY			1  	12  	1  	 	      	             	 
                          INDEX UNIQUE SCAN	CMS_R14_DEV.SYS_C0012388										100  	 	 	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID		CMS_R14_DEV.TRANS_HISTORY						1		25  	2.50964819384571  	 	      	             	 
                        BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
                          BITMAP AND		  	 	 	 	      	             	 
                            BITMAP INDEX SINGLE VALUE	CMS_R14_DEV.IDX_TR_HIST_2	  	 	 	 	      	             	 
                            BITMAP CONVERSION FROM ROWIDS		  	 	 	 	      	             	 
                              INDEX RANGE SCAN	CMS_R14_DEV.IDX_TR_HIST_1								25  	 	3  	 	      	             	 
              INDEX RANGE SCAN	CMS_R14_DEV.IDX_TEST1																		2  	 	2  	 	      	             	 
          BUFFER SORT																																		1  	100  	66  	 	      	             	 
            TABLE ACCESS FULL	CMS_R14_DEV.CMS_CHARGE_DETAIL															1  	100  	16  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_LIMIT_CHARGE_MAP										1  	43  	2  	 	      	             	 
          INDEX RANGE SCAN	CMS_R14_DEV.IDX_LMT_CHGE_MAP_01															5  	 			1  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_SECURITY															1  	27  	1  	 	      	             	 
        INDEX UNIQUE SCAN	CMS_R14_DEV.SYS_C0012171																			5  	 	 	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	CMS_R14_DEV.CMS_LIMIT_SECURITY_MAP											1  	67  	1  	 	      	             	 
      INDEX UNIQUE SCAN	CMS_R14_DEV.SYS_C0012031																				20  	 	 	 	      	             	 

Re: Fine Tuning This query [message #198489 is a reply to message #197286] Tue, 17 October 2006 06:38 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
No problem!
Tuning with out any information about indexes etc Smile

Your query can be just fine, we have no way of knowing.
A full table scan can be good. And they can be bad.
Nested loops can be good. And they can be bad.
Unique index can be good. And they can be bad.
Buffer sort.....etc.

I can see that You have
BITMAP CONVERSION TO ROWIDS

These *can* be bad (and they can be good).
But I would check them.

Also, try to get a grip about Your data. Where *should* the datbase start querying, and where is it not a good idea.


Br
Kim Anthonisen
Previous Topic: Regarding Schema Stats for CBO
Next Topic: datapump parallel
Goto Forum:
  


Current Time: Fri Dec 09 00:19:17 CST 2016

Total time taken to generate the page: 0.16546 seconds