Home » RDBMS Server » Performance Tuning » Help Help me plz urgent
Help Help me plz urgent [message #206652] Fri, 01 December 2006 00:27 Go to next message
Messages: 8
Registered: July 2006
Junior Member
Please help me to tune this query
  SELECT DISTINCT a.trx_reference_id, a.transaction_id, a.cur_trx_history_id, a.reference_id,
                        a.transaction_type, a.transaction_subtype, a.status, a.legal_id,
                        a.limit_profile_id, a.legal_name AS lname, a.customer_name AS cname,
                        a.customer_id, a.transaction_date, a.trx_origin_country, b.user_state,
                        b.user_trx_type, c.llp_bca_ref_appr_date, c.cms_orig_country,
                        c.cms_bca_create_date, d.country_name, sp.lsp_id, mp.lmp_le_id,
                        sp.lsp_short_name AS customer_name, mp.lmp_long_name AS legal_name,
                        UPPER (mp.lmp_long_name) AS upper_legal_name, 
						a.deal_no, ' ' AS task_flag
                   FROM TRANSACTION a,
                        cms_trx_totrack b,
                        sci_lsp_lmt_profile c,
                        country d,
                        sci_le_sub_profile sp,
                        sci_le_main_profile mp,
                        sci_lsp_appr_lmts l
                  WHERE sp.cms_le_main_profile_id = mp.cms_le_main_profile_id(+)
                    AND a.customer_id = sp.cms_le_sub_profile_id(+)                    
                    AND l.cms_limit_status != 'DELETED'
                    AND a.transaction_type = b.transaction_type
                    AND a.status = b.curr_state
                    AND (a.from_state = b.from_state OR b.from_state IS NULL)
                    AND (a.transaction_subtype = b.transaction_subtype  OR b.transaction_subtype IS NULL )
					AND c.cms_lsp_lmt_profile_id = l.cms_limit_profile_id
                    AND c.cms_lsp_lmt_profile_id = a.limit_profile_id 
                    AND c.cms_orig_country = d.country_iso_code
					AND c.cms_orig_country IN ('SG')
                    AND c.cms_bca_complete_ind != 'Y'					
					AND (c.cms_orig_organisation IN ('SG')  OR l.cms_bkg_organisation IN ('SCBL')   )
                    AND a.transaction_type IN ('LIMIT')
                    AND a.status = 'ACTIVE'                    
                    AND mp.lmp_sgmnt_code_value IN ('A','B')

Explain plan attached
  • Attachment: explain.jpg
    (Size: 82.13KB, Downloaded 118 times)
Re: Help Help me plz urgent [message #206800 is a reply to message #206652] Fri, 01 December 2006 09:56 Go to previous message
Messages: 611
Registered: July 2006
Senior Member
Do you have updated stats for all involved tables/indexes?
Can you post TKPROF?

Previous Topic: Would my SQL work to record which indexes are being used
Next Topic: Timing queries
Goto Forum:

Current Time: Wed Oct 26 08:43:20 CDT 2016

Total time taken to generate the page: 0.10981 seconds