Home » RDBMS Server » Performance Tuning » Performance issue in query (Oracle 10 G)
Performance issue in query [message #553113] Wed, 02 May 2012 00:21 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
Hi,

I am trying to execute the following query . Its taking more than 10 mins to execute.
pls help me how to reduce the execution time.

                SELECT ic.corn_id "CORN_ID", trace_id.ixml_id "TRACE_ID",
                       trx.trx_status "STATUS_OF_CLAIM",
                       trx.trx_complet_tm "PROCESS_DATE",
                       ic.send_id "SENDER_ID", ic.send_nm "SENDER_NAME",
                       ic.test_ind "TEST_PROD_INDICATOR",
                       ic.ch_status_agn "CLEARING_HOUSE_STATUS",
                       ic.ac_status_agn "AC_STATUS",
                       trx.transaction_type_id "CLAIM_TYPE",
                       subscriber.nmfirst "SUBSCRIBER_FIRST_NAME",
                       subscriber.nmlast "SUBSCRIBER_LAST_NAME",
                       subscriber.nmmid "SUBSCRIBER_MID_NAME",
                       subscriber.ixml_id "SUBSCRIBER_ID",
                       subscriber.group_nbr "SUBSCRIBER_GROUP_NO",
                       subscriber.group_nm "SUBSCRIBER_GROUP_NAME",
                       subscriber.birth_dt "SUBSCRIBER_DOB",
                       subscriber.gender_cd "SUBSCRIBER_GENDER",
                       customer.customer_id "PAYER_ID",
                       customer.customer_nm "PAYER_NAME",
                       pat_name.nmfirst "PATIENT_FIRST_NAME",
                       pat_name.nmlast "PATIENT_LAST_NAME",
                       pat_name.nmmid "PATIENT_MID_NAME",
                       patient.ixml_id "PATIENT_ID",
                       patient.birth_dt "PATIENT_DOB",
                       ic.patcntrl_nbr "PATIENT_CONTROL_NO",
                       ic.total_charge_amt "TOTAL_CLAIM_AMOUNT",
                       ic.created_on, 'ADVCLM', ic.claim_id "CLAIM_ID"
                  FROM ixml_claim ic,
                       trx,
                       ixml_id trace_id,
                       (SELECT ixml_id, ixml_patient.claim_id, name_id,
                               gender_cd, birth_dt
                          FROM ixml_patient,
                               ixml_patient_id_assoc ipa,
                               ixml_id
                         WHERE ixml_patient.patient_agn = ipa.patient_agn
                           AND ipa.id_agn = ixml_id.id_agn
                           AND ixml_id.primary_attribute_value IN
                                                     ('MI', 'ZZ', 'SY', 'EI')) patient,
                       ixml_name pat_name,
                       (SELECT   MAX
                                    (NVL (customer_status_until_dt,
                                          TO_DATE ('01-JAN-2900')
                                         )
                                    ) "CUSTOMER_EFFECTIVE_DT",
                                 a.customer_agn, customer_nm, customer_id
                            FROM (SELECT customer_id, customer_agn,
                                         customer_nm
                                    FROM customer
                                   WHERE 
                                   customer_agn=15395
                                   AND (par_flg, customer_id) IN (
                                             SELECT   MAX (par_flg),
                                                      customer_id
                                                 FROM customer
                                             GROUP BY customer_id
                                             )) a,
                                 customer_status b
                           WHERE a.customer_agn = b.customer_agn
                        GROUP BY a.customer_agn, customer_nm, customer_id) customer,
                       (  SELECT subr.name_id   ,subr.nmfirst,subr.nmlast,subr.nmmid,subr.claim_id ,ixmlid.ixml_id,
                                 subr.group_nbr , subr.group_nm, subr.birth_dt,
                                 subr.gender_cd , subr.subscriber_agn
                          FROM 
                               (SELECT sub.name_id, NAME.nmfirst, NAME.nmlast,
                                       NAME.nmmid, sub.claim_id,
                                       sub.group_nbr, sub.group_nm, sub.birth_dt,
                                       sub.gender_cd , sub.subscriber_agn
                                FROM   ixml_subscriber sub,
                                       ixml_name NAME
                                WHERE  sub.name_id   = NAME.name_id
                                AND    sub.s_seq_nbr = 1 ) subr
                           
                               JOIN
                                
                                (select subscriber_agn, max(ixml_id) ixml_id,claim_id 
                             from (SELECT   sia.subscriber_agn,
                                          ID.ixml_id ixml_id, id.claim_id
                                 FROM    ixml_subscriber_id_assoc sia,
                                         ixml_id ID,
                                         ixml_subscriber sub
                                 WHERE sia.id_agn         = ID.id_agn 
                                 AND   sia.subscriber_agn = sub.subscriber_agn                                
                                 AND   id.claim_id        = sub.claim_id
                                 AND   id.claim_id        = 166245
                                 AND   ID.primary_attribute_type_id(+) = 1
                                 AND   (  primary_attribute_value(+) = 'MI'
                                          OR primary_attribute_value(+) = 'ZZ'
                                          OR primary_attribute_value(+) = 'SY'
                                          OR primary_attribute_value(+) = 'EI'
                                         ))
                             group by subscriber_agn,claim_id)   ixmlid                                
                                ON
                                subr.subscriber_agn = ixmlid.subscriber_agn ) subscriber
                 WHERE ic.trx_agn = trx.trx_agn
                   AND ic.claim_id = patient.claim_id(+)
                   AND trx.customer_agn = customer.customer_agn(+)
                   AND patient.name_id = pat_name.name_id(+)
                   AND ic.claim_id = trace_id.claim_id(+)
                   AND trace_id.primary_attribute_value(+) = 'D9'
                   AND ic.claim_id = subscriber.claim_id(+)                  
                   

                   
 



Execution plan

Plan
SELECT STATEMENT  ALL_ROWSCost: 103,573  Bytes: 500,823,388  Cardinality: 740,863  												
	52 HASH JOIN RIGHT OUTER  Cost: 103,573  Bytes: 500,823,388  Cardinality: 740,863  											
		18 VIEW ADVCLM. Cost: 10  Bytes: 301  Cardinality: 1  										
			17 NESTED LOOPS  									
				15 NESTED LOOPS  Cost: 10  Bytes: 95  Cardinality: 1  								
					13 NESTED LOOPS  Cost: 8  Bytes: 78  Cardinality: 1  							
						10 VIEW ADVCLM. Cost: 7  Bytes: 40  Cardinality: 1  						
							9 HASH GROUP BY  Cost: 7  Bytes: 50  Cardinality: 1  					
								8 NESTED LOOPS  				
									6 NESTED LOOPS  Cost: 6  Bytes: 50  Cardinality: 1  			
										4 NESTED LOOPS  Cost: 4  Bytes: 23  Cardinality: 1  		
											2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ADVCLM.IXML_SUBSCRIBER Cost: 2  Bytes: 11  Cardinality: 1  Partition #: 11  	
												1 INDEX RANGE SCAN INDEX ADVCLM.IDX_XML_SUBSCRIBER_CLAIM Cost: 1  Cardinality: 1  
											3 INDEX RANGE SCAN INDEX ADVCLM.IDX_SUBSCRIBER_ID_AGN Cost: 2  Bytes: 12  Cardinality: 1  	
										5 INDEX UNIQUE SCAN INDEX (UNIQUE) ADVCLM.XPKIXML_ID Cost: 1  Cardinality: 1  		
									7 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ADVCLM.IXML_ID Cost: 2  Bytes: 27  Cardinality: 1  Partition #: 15  			
						12 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ADVCLM.IXML_SUBSCRIBER Cost: 1  Bytes: 38  Cardinality: 1  Partition #: 16  						
							11 INDEX UNIQUE SCAN INDEX (UNIQUE) ADVCLM.XPKIXML_SUBSCRIBER Cost: 0  Cardinality: 1  					
					14 INDEX UNIQUE SCAN INDEX (UNIQUE) ADVCLM.XPKIXML_NAME Cost: 1  Cardinality: 1  							
				16 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ADVCLM.IXML_NAME Cost: 2  Bytes: 17  Cardinality: 1  Partition #: 19  								
		51 HASH JOIN RIGHT OUTER  Cost: 103,559  Bytes: 277,823,625  Cardinality: 740,863  										
			30 VIEW ADVCLM. Cost: 7  Bytes: 92  Cardinality: 1  									
				29 HASH GROUP BY  Cost: 7  Bytes: 35  Cardinality: 1  								
					28 NESTED LOOPS  Cost: 7  Bytes: 35  Cardinality: 1  							
						23 NESTED LOOPS  Cost: 4  Bytes: 33  Cardinality: 1  						
							20 TABLE ACCESS BY INDEX ROWID TABLE ADVCLM.CUSTOMER Cost: 2  Bytes: 24  Cardinality: 1  					
								19 INDEX UNIQUE SCAN INDEX (UNIQUE) ADVCLM.XPKCUSTOMER Cost: 1  Cardinality: 1  				
							22 TABLE ACCESS BY INDEX ROWID TABLE ADVCLM.CUSTOMER_STATUS Cost: 2  Bytes: 9  Cardinality: 1  					
								21 INDEX RANGE SCAN INDEX ADVCLM.IDX_CUST_STAT_CUSTOMER_AGN Cost: 1  Cardinality: 1  				
						27 VIEW PUSHED PREDICATE VIEW SYS.VW_NSO_1 Cost: 3  Bytes: 2  Cardinality: 1  						
							26 FILTER  					
								25 SORT AGGREGATE  Bytes: 8  Cardinality: 1  				
									24 INDEX SKIP SCAN INDEX ADVCLM.IDX_CUSTOMER_PAR_FLG_CUST_ID Cost: 3  Bytes: 8  Cardinality: 1  			
			50 HASH JOIN  Cost: 103,548  Bytes: 209,664,229  Cardinality: 740,863  									
				32 PARTITION RANGE ALL  Cost: 14,436  Bytes: 104,099,533  Cardinality: 2,539,013  Partition #: 34  Partitions accessed #1 - #202								
					31 TABLE ACCESS FULL TABLE ADVCLM.TRX Cost: 14,436  Bytes: 104,099,533  Cardinality: 2,539,013  Partition #: 34  Partitions accessed #1 - #202							
				49 HASH JOIN OUTER  Cost: 75,945  Bytes: 179,288,846  Cardinality: 740,863  								
					46 HASH JOIN RIGHT OUTER  Cost: 52,233  Bytes: 166,694,175  Cardinality: 740,863  							
						41 VIEW ADVCLM. Cost: 25,942  Bytes: 40,580,984  Cardinality: 654,532  						
							40 HASH JOIN  Cost: 25,942  Bytes: 35,999,260  Cardinality: 654,532  					
								34 PARTITION RANGE ALL  Cost: 3,715  Bytes: 17,544,000  Cardinality: 731,000  Partition #: 40  Partitions accessed #1 - #202				
									33 TABLE ACCESS FULL TABLE ADVCLM.IXML_PATIENT Cost: 3,715  Bytes: 17,544,000  Cardinality: 731,000  Partition #: 40  Partitions accessed #1 - #202			
								39 HASH JOIN  Cost: 19,997  Bytes: 20,290,492  Cardinality: 654,532  				
									36 PARTITION RANGE ALL  Cost: 3,425  Bytes: 7,600,923  Cardinality: 690,993  Partition #: 43  Partitions accessed #1 - #202			
										35 TABLE ACCESS FULL TABLE ADVCLM.IXML_PATIENT_ID_ASSOC Cost: 3,425  Bytes: 7,600,923  Cardinality: 690,993  Partition #: 43  Partitions accessed #1 - #202		
									38 PARTITION RANGE ALL  Cost: 15,059  Bytes: 13,090,640  Cardinality: 654,532  Partition #: 45  Partitions accessed #1 - #202			
										37 TABLE ACCESS FULL TABLE ADVCLM.IXML_ID Cost: 15,059  Bytes: 13,090,640  Cardinality: 654,532  Partition #: 45  Partitions accessed #1 - #202		
						45 HASH JOIN RIGHT OUTER  Cost: 19,026  Bytes: 120,760,669  Cardinality: 740,863  						
							42 INDEX FAST FULL SCAN INDEX ADVCLM.IDX_PRIMARY_ATTR_CLAIM_IXML_ID Cost: 7,747  Bytes: 3,109,027  Cardinality: 163,633  					
							44 PARTITION RANGE ALL  Cost: 6,357  Bytes: 106,684,272  Cardinality: 740,863  Partition #: 49  Partitions accessed #1 - #202					
								43 TABLE ACCESS FULL TABLE ADVCLM.IXML_CLAIM Cost: 6,357  Bytes: 106,684,272  Cardinality: 740,863  Partition #: 49  Partitions accessed #1 - #202				
					48 PARTITION RANGE ALL  Cost: 9,282  Bytes: 103,897,489  Cardinality: 6,111,617  Partition #: 51  Partitions accessed #1 - #202							
						47 TABLE ACCESS FULL TABLE ADVCLM.IXML_NAME Cost: 9,282  Bytes: 103,897,489  Cardinality: 6,111,617  Partition #: 51  Partitions accessed #1 - #202



Thanks

[Updated on: Wed, 02 May 2012 01:03] by Moderator

Report message to a moderator

Re: Performance issue in query [message #553115 is a reply to message #553113] Wed, 02 May 2012 00:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof


BTW, you did an acceptable job of formatting the code.
Too bad you did not do the same for the EXPLAIN PLAN.
please post again the EXPLAIN PLAN; only this time use code-tags.

[Updated on: Wed, 02 May 2012 00:26]

Report message to a moderator

Re: Performance issue in query [message #553497 is a reply to message #553113] Sat, 05 May 2012 07:04 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
I can not read the explain plan very well, but I see a few Full Table Scans in (at least) /ad/the last few rows of the plan.
For example the very last row is a FTS with a cardinality of over 6M. Maybe that is just what is needed but I can also imagine that an index would be of help.
Re: Performance issue in query [message #553798 is a reply to message #553113] Tue, 08 May 2012 09:52 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
If you still need a solution, do the following please:
1. make the following settings

set linesize 1000
set pagesize 1000

2. then run

alter session set statistics_level=all;

3. then run your sql,
4. after that run the following select:

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));


5. upload the last formatted output.
Previous Topic: Help required in analyzing AWR report
Next Topic: how to set the sql_profile
Goto Forum:
  


Current Time: Thu Mar 28 15:41:46 CDT 2024