Home » RDBMS Server » Performance Tuning » Query taking different time in tqo schemas (Oracle 10g)
Query taking different time in tqo schemas [message #343047] Tue, 26 August 2008 01:34 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have two schemas on the same server where I am running the same query but it is taking different time to execute the same query in two schemas.
SELECT   /*+ no_query_transformation */
         basedata.*
    FROM (SELECT (SELECT SUBSTR (vrygbs, 3, 1)
                    FROM vwsrygbs v1
                   WHERE v1.vrygbs =
                                 (SELECT MIN (v2.vrygbs)
                                    FROM vwsrygbs v2
                                   WHERE v1.vwkor_id_pk = v2.vwkor_id_pk)
                     AND ROWNUM = 1
                     AND owo.wkor_id_pk = v1.vwkor_id_pk) ryg,
                 owo.wkor_id_pk workorderid, owo.wkor_order_id_fk orderid,
                 vupdateddate currentstatustime,
                 vwostate.vstatunkey stateuniqueid,
                 owo.wkor_required_quantity orderquantity,
                    oli.oli_sku_number
                 || '-'
                 || oli.oli_skurevision_number skunumber,
                 oo.ord_deliverydatetime deliverydatetime,
                 oo.ord_deliverydatetime duedate, owsi.wsi_name workstepname,
                 asti.stai_state_name currentstatus,
                 asti.stai_state_name status,
                 ast.sta_state_id_pk currentstatusid,
                 owwm.wowsm_bom_id_fk bomid,
                 owo.wkor_parent_wo_id parentworkorderid,
                 owo.wkor_master_wo_id masterworkorderid,
                 owo.wkor_status_code workorderstatusid,
                 ows.wrs_id_pk workstepid, ar.role_id_pk roleid
            FROM ord_orders oo,
                 vworkorder_states vwostate,
                 ord_wkor_ws_mapping owwm,
                 ord_work_steps ows,
                 ord_lineitems oli,
                 ord_work_steps_in owsi,
                 adm_states ast,
                 adm_states_in asti,
                 ord_work_order owo,
                 adm_roles ar
           WHERE wkor_order_id_fk = oo.ord_id_pk
             AND vwostate.vwoid = owo.wkor_id_pk
             AND vwostate.vupdateddate =
                    (SELECT MAX (vwostate1.vupdateddate)
                       FROM vworkorder_states vwostate1
                      WHERE vwostate1.vwoid = owo.wkor_id_pk
                        AND vwostate1.voprid = ows.wrs_operation_type_id_fk
                        AND vwostate1.vwsid = ows.wrs_id_pk)
             AND vwostate.voprid = ows.wrs_operation_type_id_fk
             AND owwm.wowsm_wo_id = owo.wkor_id_pk
             AND owwm.wowsm_ws_id = ows.wrs_id_pk
             AND oli.oli_order_id_fk = owo.wkor_order_id_fk
             AND wkor_ord_lineitem_id_fk = oli_id_pk
             AND owsi.wsi_id_fk = ows.wrs_id_pk
             AND ast.sta_state_id_pk = asti.stai_state_id_fk
             AND vwostate.vstatunkey = ast.sta_state_key_un
             AND oo.ord_completed_date IS NULL
             AND NVL (oo.ord_type, 0) != 'D'
             AND NVL (oli.oli_type, 0) != 'P'
             AND NVL (owo.wkor_type, 0) NOT IN ('P', 'X')
             AND owsi.wsi_language_id_fk = 1
             AND owsi.wsi_language_id_fk = asti.stai_language_id_fk
             AND owwm.wowsm_ws_id = vwostate.vwsid
             AND ows.wrs_operation_type_id_fk = ar.role_opr_type_id_fk
             AND ar.role_id_pk = 19
             AND owo.wkor_status_code != 129
             AND ast.sta_state_key_un NOT IN (
                    SELECT v_sta_state_key_un
                      FROM vwstatesstartcomplete vsc
                     WHERE vsc.v_start_complete = 0
                       AND vsc.v_sta_operation_type_id_fk =
                                                  ows.wrs_operation_type_id_fk)) basedata,
         adm_states as1,
         adm_states as2
   WHERE as1.sta_state_id_pk = basedata.currentstatusid
     AND as2.sta_state_id_pk = basedata.workorderstatusid
     AND (   (    (ryg = 'B' OR ryg = 'S')
              AND (as1.sta_operation_type_id_fk = as2.sta_operation_type_id_fk
                  )
             )
          OR (ryg = 'R' OR ryg = 'Y' OR ryg = 'G')
         )
     AND basedata.ryg != 'S'
     AND basedata.ryg != 'B'
ORDER BY duedate, basedata.orderid ASC, basedata.workorderid ASC


The explain plan is also different in the two schemas. Though they are big but I have pasted them here.

The explan Plan in Schema 1 where it is running in some milliseconds :

Plan
SELECT STATEMENT  ALL_ROWSCost: 262  Bytes: 304  Cardinality: 1  																									
	105 SORT ORDER BY  Cost: 262  Bytes: 304  Cardinality: 1  																								
		104 NESTED LOOPS  Cost: 261  Bytes: 304  Cardinality: 1  																							
			101 NESTED LOOPS  Cost: 260  Bytes: 297  Cardinality: 1  																						
				98 VIEW OCEBACKUP. Cost: 259  Bytes: 290  Cardinality: 1  																					
					97 NESTED LOOPS  Cost: 155  Bytes: 189  Cardinality: 1  																				
						94 NESTED LOOPS  Cost: 154  Bytes: 165  Cardinality: 1  																			
							91 NESTED LOOPS  Cost: 153  Bytes: 142  Cardinality: 1  																		
								83 NESTED LOOPS  Cost: 152  Bytes: 134  Cardinality: 1  																	
									80 NESTED LOOPS  Cost: 151  Bytes: 119  Cardinality: 1  																
										77 NESTED LOOPS  Cost: 150  Bytes: 95  Cardinality: 1  															
											20 HASH JOIN  Cost: 149  Bytes: 67  Cardinality: 1  														
												18 HASH JOIN  Cost: 142  Bytes: 165  Cardinality: 3  													
													4 NESTED LOOPS  Cost: 4  Bytes: 195  Cardinality: 15  												
														2 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_ROLES Cost: 1  Bytes: 6  Cardinality: 1  											
															1 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086785 Cost: 0  Cardinality: 1  										
														3 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WORK_STEPS Cost: 3  Bytes: 105  Cardinality: 15  											
													17 VIEW VIEW OCEBACKUP.VWORKORDER_STATES Cost: 138  Bytes: 4,830  Cardinality: 115  												
														16 HASH JOIN  Cost: 138  Bytes: 6,785  Cardinality: 115  											
															14 VIEW SYS. Cost: 134  Bytes: 5,980  Cardinality: 115  										
																13 NESTED LOOPS OUTER  Cost: 134  Bytes: 5,980  Cardinality: 115  									
																	9 VIEW OCEBACKUP. Cost: 19  Bytes: 5,520  Cardinality: 115  								
																		8 SORT UNIQUE  Cost: 19  Bytes: 5,240  Cardinality: 115  							
																			7 UNION-ALL  						
																				5 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WO_STATES_HISTORY Cost: 9  Bytes: 1,184  Cardinality: 37  					
																				6 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 8  Bytes: 4,056  Cardinality: 78  					
																	12 VIEW SYS. Cost: 1  Bytes: 4  Cardinality: 1  								
																		11 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1  Bytes: 8  Cardinality: 1  							
																			10 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 0  Cardinality: 1  						
															15 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WORK_STEPS Cost: 3  Bytes: 2,296  Cardinality: 328  										
												19 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WKOR_WS_MAPPING Cost: 6  Bytes: 2,340  Cardinality: 195  													
											76 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 1  Bytes: 28  Cardinality: 1  														
												75 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_ORDER_PK Cost: 0  Cardinality: 1  													
													35 SORT AGGREGATE  Bytes: 48  Cardinality: 1  												
														34 VIEW VIEW OCEBACKUP.VWORKORDER_STATES Cost: 14  Bytes: 48  Cardinality: 1  											
															33 NESTED LOOPS  Cost: 14  Bytes: 55  Cardinality: 1  										
																22 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_STEPS Cost: 1  Bytes: 7  Cardinality: 1  									
																	21 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0087144 Cost: 0  Cardinality: 1  								
																32 VIEW SYS. Cost: 13  Bytes: 48  Cardinality: 1  									
																	31 NESTED LOOPS OUTER  Cost: 13  Bytes: 96  Cardinality: 2  								
																		28 VIEW OCEBACKUP. Cost: 13  Bytes: 96  Cardinality: 2  							
																			27 SORT UNIQUE  Cost: 13  Bytes: 84  Cardinality: 2  						
																				26 UNION-ALL  					
																					23 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WO_STATES_HISTORY Cost: 9  Bytes: 32  Cardinality: 1  				
																					25 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 2  Bytes: 52  Cardinality: 1  				
																						24 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_ORDER_PK Cost: 1  Cardinality: 1  			
																		30 VIEW SYS. Cost: 0  Cardinality: 1  							
																			29 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 0  Bytes: 4  Cardinality: 1  						
													74 COUNT STOPKEY  												
														73 FILTER  											
															53 VIEW VIEW OCEBACKUP.VWSRYGBS Cost: 22  Bytes: 81  Cardinality: 9  										
																52 SORT ORDER BY  Cost: 22  Bytes: 918  Cardinality: 9  									
																	51 HASH JOIN  Cost: 21  Bytes: 918  Cardinality: 9  								
																		49 HASH JOIN  Cost: 15  Bytes: 855  Cardinality: 9  							
																			39 MERGE JOIN CARTESIAN  Cost: 9  Bytes: 54  Cardinality: 3  						
																				36 TABLE ACCESS FULL TABLE OCEBACKUP.ADM_SITE_CONFIGURATION Cost: 3  Bytes: 6  Cardinality: 1  					
																				38 BUFFER SORT  Cost: 6  Bytes: 36  Cardinality: 3  					
																					37 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WKOR_WS_MAPPING Cost: 6  Bytes: 36  Cardinality: 3  				
																			48 VIEW SYS. Cost: 5  Bytes: 847  Cardinality: 11  						
																				47 NESTED LOOPS OUTER  Cost: 5  Bytes: 308  Cardinality: 11  					
																					41 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 2  Bytes: 15  Cardinality: 1  				
																						40 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_ORDER_PK Cost: 1  Cardinality: 1  			
																					46 VIEW SYS. Cost: 3  Bytes: 143  Cardinality: 11  				
																						45 VIEW VIEW OCEBACKUP.index$_join$_028 Cost: 3  Bytes: 88  Cardinality: 11  			
																							44 HASH JOIN  		
																								42 INDEX FAST FULL SCAN INDEX (UNIQUE) OCEBACKUP.CONS_STA_STATE_KEY_UN Cost: 1  Bytes: 88  Cardinality: 11  	
																								43 INDEX FAST FULL SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 1  Bytes: 88  Cardinality: 11  	
																		50 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_ORDERS Cost: 6  Bytes: 91  Cardinality: 13  							
															72 SORT AGGREGATE  Bytes: 18  Cardinality: 1  										
																71 VIEW VIEW OCEBACKUP.VWSRYGBS Cost: 22  Bytes: 162  Cardinality: 9  									
																	70 SORT ORDER BY  Cost: 22  Bytes: 999  Cardinality: 9  								
																		69 HASH JOIN  Cost: 21  Bytes: 999  Cardinality: 9  							
																			67 HASH JOIN  Cost: 15  Bytes: 936  Cardinality: 9  						
																				57 MERGE JOIN CARTESIAN  Cost: 9  Bytes: 54  Cardinality: 3  					
																					54 TABLE ACCESS FULL TABLE OCEBACKUP.ADM_SITE_CONFIGURATION Cost: 3  Bytes: 6  Cardinality: 1  				
																					56 BUFFER SORT  Cost: 6  Bytes: 36  Cardinality: 3  				
																						55 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_WKOR_WS_MAPPING Cost: 6  Bytes: 36  Cardinality: 3  			
																				66 VIEW SYS. Cost: 5  Bytes: 946  Cardinality: 11  					
																					65 NESTED LOOPS OUTER  Cost: 5  Bytes: 308  Cardinality: 11  				
																						59 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_ORDER Cost: 2  Bytes: 15  Cardinality: 1  			
																							58 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_ORDER_PK Cost: 1  Cardinality: 1  		
																						64 VIEW SYS. Cost: 3  Bytes: 143  Cardinality: 11  			
																							63 VIEW VIEW OCEBACKUP.index$_join$_035 Cost: 3  Bytes: 88  Cardinality: 11  		
																								62 HASH JOIN  	
																									60 INDEX FAST FULL SCAN INDEX (UNIQUE) OCEBACKUP.CONS_STA_STATE_KEY_UN Cost: 1  Bytes: 88  Cardinality: 11  
																									61 INDEX FAST FULL SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 1  Bytes: 88  Cardinality: 11  
																			68 TABLE ACCESS FULL TABLE OCEBACKUP.ORD_ORDERS Cost: 6  Bytes: 91  Cardinality: 13  						
										79 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_LINEITEMS Cost: 1  Bytes: 24  Cardinality: 1  															
											78 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0087087 Cost: 0  Cardinality: 1  														
									82 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_ORDERS Cost: 1  Bytes: 15  Cardinality: 1  																
										81 INDEX RANGE SCAN INDEX OCEBACKUP.ORDER_ID Cost: 0  Cardinality: 1  															
								90 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1  Bytes: 8  Cardinality: 1  																	
									89 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.CONS_STA_STATE_KEY_UN Cost: 0  Cardinality: 1  																
										88 VIEW VIEW OCEBACKUP.VWSTATESSTARTCOMPLETE Cost: 2  Bytes: 29  Cardinality: 1  															
											87 NESTED LOOPS  Cost: 2  Bytes: 24  Cardinality: 1  														
												85 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1  Bytes: 20  Cardinality: 1  													
													84 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.CONS_STA_STATE_KEY_UN Cost: 0  Cardinality: 1  												
												86 INDEX RANGE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086829 Cost: 1  Bytes: 4  Cardinality: 1  													
							93 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES_IN Cost: 1  Bytes: 23  Cardinality: 1  																		
								92 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086829 Cost: 0  Cardinality: 1  																	
						96 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ORD_WORK_STEPS_IN Cost: 1  Bytes: 24  Cardinality: 1  																			
							95 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.ORD_WORK_STEPS_IN_PK Cost: 0  Cardinality: 1  																		
				100 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1  Bytes: 7  Cardinality: 1  																					
					99 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 0  Cardinality: 1  																				
			103 TABLE ACCESS BY INDEX ROWID TABLE OCEBACKUP.ADM_STATES Cost: 1  Bytes: 7  Cardinality: 1  																						
				102 INDEX UNIQUE SCAN INDEX (UNIQUE) OCEBACKUP.SYS_C0086826 Cost: 0  Cardinality: 1  																					


In schema 2 where its taking more than 8 seconds:

Plan
SELECT STATEMENT  ALL_ROWSCost: 3,604  Bytes: 313  Cardinality: 1  																			
	108 SORT ORDER BY  Cost: 3,604  Bytes: 313  Cardinality: 1  																		
		107 NESTED LOOPS  Cost: 3,603  Bytes: 313  Cardinality: 1  																	
			104 NESTED LOOPS  Cost: 3,602  Bytes: 306  Cardinality: 1  																
				101 VIEW OCE211. Cost: 3,601  Bytes: 299  Cardinality: 1  															
					100 FILTER  														
						36 NESTED LOOPS  Cost: 3,538  Bytes: 189  Cardinality: 1  													
							33 NESTED LOOPS  Cost: 3,537  Bytes: 165  Cardinality: 1  												
								30 NESTED LOOPS  Cost: 3,536  Bytes: 141  Cardinality: 1  											
									27 HASH JOIN  Cost: 3,535  Bytes: 125  Cardinality: 1  										
										25 HASH JOIN  Cost: 3,526  Bytes: 2,646  Cardinality: 27  									
											23 HASH JOIN  Cost: 3,519  Bytes: 6,192  Cardinality: 72  								
												9 HASH JOIN  Cost: 9  Bytes: 53,944  Cardinality: 1,226  							
													7 NESTED LOOPS  Cost: 5  Bytes: 3,034  Cardinality: 82  						
														4 NESTED LOOPS  Cost: 4  Bytes: 2,378  Cardinality: 82  					
															2 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_ROLES Cost: 1  Bytes: 6  Cardinality: 1  				
																1 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105095 Cost: 0  Cardinality: 1  			
															3 TABLE ACCESS FULL TABLE OCE211.ADM_STATES_IN Cost: 3  Bytes: 1,886  Cardinality: 82  				
														6 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1  Bytes: 8  Cardinality: 1  					
															5 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0  Cardinality: 1  				
													8 TABLE ACCESS FULL TABLE OCE211.ORD_WORK_STEPS Cost: 3  Bytes: 2,527  Cardinality: 361  						
												22 VIEW VIEW OCE211.VWORKORDER_STATES Cost: 3,510  Bytes: 146,454  Cardinality: 3,487  							
													21 HASH JOIN  Cost: 3,510  Bytes: 205,733  Cardinality: 3,487  						
														10 TABLE ACCESS FULL TABLE OCE211.ORD_WORK_STEPS Cost: 3  Bytes: 2,527  Cardinality: 361  					
														20 VIEW SYS. Cost: 3,507  Bytes: 181,324  Cardinality: 3,487  					
															19 NESTED LOOPS OUTER  Cost: 3,507  Bytes: 181,324  Cardinality: 3,487  				
																15 VIEW OCE211. Cost: 16  Bytes: 167,376  Cardinality: 3,487  			
																	14 SORT UNIQUE  Cost: 16  Bytes: 131,141  Cardinality: 3,487  		
																		13 UNION-ALL  	
																			11 TABLE ACCESS FULL TABLE OCE211.ORD_WO_STATES_HISTORY Cost: 5  Bytes: 62,040  Cardinality: 1,880  
																			12 TABLE ACCESS FULL TABLE OCE211.ORD_WORK_ORDER Cost: 8  Bytes: 69,101  Cardinality: 1,607  
																18 VIEW SYS. Cost: 1  Bytes: 4  Cardinality: 1  			
																	17 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1  Bytes: 8  Cardinality: 1  		
																		16 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0  Cardinality: 1  	
											24 TABLE ACCESS FULL TABLE OCE211.ORD_WKOR_WS_MAPPING Cost: 6  Bytes: 46,860  Cardinality: 3,905  								
										26 TABLE ACCESS FULL TABLE OCE211.ORD_WORK_ORDER Cost: 8  Bytes: 42,498  Cardinality: 1,574  									
									29 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_ORDERS Cost: 1  Bytes: 16  Cardinality: 1  										
										28 INDEX RANGE SCAN INDEX OCE211.ORDER_ID Cost: 0  Cardinality: 1  									
								32 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_STEPS_IN Cost: 1  Bytes: 24  Cardinality: 1  											
									31 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.ORD_WORK_STEPS_IN_PK Cost: 0  Cardinality: 1  										
							35 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_LINEITEMS Cost: 1  Bytes: 24  Cardinality: 1  												
								34 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105402 Cost: 0  Cardinality: 1  											
						57 SORT AGGREGATE  Bytes: 48  Cardinality: 1  													
							56 VIEW VIEW OCE211.VWORKORDER_STATES Cost: 7  Bytes: 48  Cardinality: 1  												
								55 NESTED LOOPS  Cost: 7  Bytes: 55  Cardinality: 1  											
									38 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_STEPS Cost: 1  Bytes: 7  Cardinality: 1  										
										37 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105464 Cost: 0  Cardinality: 1  									
									54 VIEW SYS. Cost: 6  Bytes: 48  Cardinality: 1  										
										53 NESTED LOOPS OUTER  Cost: 6  Bytes: 96  Cardinality: 2  									
											50 VIEW OCE211. Cost: 6  Bytes: 96  Cardinality: 2  								
												49 SORT UNIQUE  Cost: 6  Bytes: 76  Cardinality: 2  							
													48 UNION-ALL  						
														45 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WO_STATES_HISTORY Cost: 2  Bytes: 33  Cardinality: 1  					
															44 BITMAP CONVERSION TO ROWIDS  				
																43 BITMAP AND  			
																	40 BITMAP CONVERSION FROM ROWIDS  		
																		39 INDEX RANGE SCAN INDEX OCE211.IDX_ORD_WO_ST_HIST_WO_ID_FK Cost: 1  Cardinality: 6  	
																	42 BITMAP CONVERSION FROM ROWIDS  		
																		41 INDEX RANGE SCAN INDEX OCE211.IDX_ORD_WO_ST_HIST_WRS_ID_FK Cost: 1  Cardinality: 6  	
														47 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_ORDER Cost: 2  Bytes: 43  Cardinality: 1  					
															46 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.ORD_WORK_ORDER_PK Cost: 1  Cardinality: 1  				
											52 VIEW SYS. Cost: 0  Cardinality: 1  								
												51 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0  Bytes: 4  Cardinality: 1  							
						62 VIEW VIEW OCE211.VWSTATESSTARTCOMPLETE Cost: 2  Bytes: 29  Cardinality: 1  													
							61 NESTED LOOPS  Cost: 2  Bytes: 24  Cardinality: 1  												
								59 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1  Bytes: 20  Cardinality: 1  											
									58 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.CONS_STA_STATE_KEY_UN Cost: 0  Cardinality: 1  										
								60 INDEX RANGE SCAN INDEX (UNIQUE) OCE211.SYS_C00105140 Cost: 1  Bytes: 4  Cardinality: 1  											
						99 COUNT STOPKEY  													
							98 FILTER  												
								79 VIEW VIEW OCE211.VWSRYGBS Cost: 13  Bytes: 18  Cardinality: 2  											
									78 SORT ORDER BY  Cost: 13  Bytes: 206  Cardinality: 2  										
										77 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_ORDERS Cost: 1  Bytes: 8  Cardinality: 1  									
											76 NESTED LOOPS  Cost: 12  Bytes: 206  Cardinality: 2  								
												74 HASH JOIN  Cost: 11  Bytes: 190  Cardinality: 2  							
													67 MERGE JOIN CARTESIAN  Cost: 5  Bytes: 36  Cardinality: 2  						
														63 TABLE ACCESS FULL TABLE OCE211.ADM_SITE_CONFIGURATION Cost: 3  Bytes: 6  Cardinality: 1  					
														66 BUFFER SORT  Cost: 2  Bytes: 24  Cardinality: 2  					
															65 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WKOR_WS_MAPPING Cost: 2  Bytes: 24  Cardinality: 2  				
																64 INDEX RANGE SCAN INDEX OCE211.IDX_ORD_WKOR_WS_MAPPING_WO_ID Cost: 1  Cardinality: 3  			
													73 VIEW SYS. Cost: 5  Bytes: 231  Cardinality: 3  						
														72 NESTED LOOPS OUTER  Cost: 5  Bytes: 84  Cardinality: 3  					
															69 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_ORDER Cost: 2  Bytes: 15  Cardinality: 1  				
																68 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.ORD_WORK_ORDER_PK Cost: 1  Cardinality: 1  			
															71 VIEW SYS. Cost: 3  Bytes: 39  Cardinality: 3  				
																70 TABLE ACCESS FULL TABLE OCE211.ADM_STATES Cost: 3  Bytes: 24  Cardinality: 3  			
												75 INDEX RANGE SCAN INDEX OCE211.ORDER_ID Cost: 0  Cardinality: 1  							
								97 SORT AGGREGATE  Bytes: 18  Cardinality: 1  											
									96 VIEW VIEW OCE211.VWSRYGBS Cost: 13  Bytes: 36  Cardinality: 2  										
										95 SORT ORDER BY  Cost: 13  Bytes: 224  Cardinality: 2  									
											94 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_ORDERS Cost: 1  Bytes: 8  Cardinality: 1  								
												93 NESTED LOOPS  Cost: 12  Bytes: 224  Cardinality: 2  							
													91 HASH JOIN  Cost: 11  Bytes: 208  Cardinality: 2  						
														84 MERGE JOIN CARTESIAN  Cost: 5  Bytes: 36  Cardinality: 2  					
															80 TABLE ACCESS FULL TABLE OCE211.ADM_SITE_CONFIGURATION Cost: 3  Bytes: 6  Cardinality: 1  				
															83 BUFFER SORT  Cost: 2  Bytes: 24  Cardinality: 2  				
																82 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WKOR_WS_MAPPING Cost: 2  Bytes: 24  Cardinality: 2  			
																	81 INDEX RANGE SCAN INDEX OCE211.IDX_ORD_WKOR_WS_MAPPING_WO_ID Cost: 1  Cardinality: 3  		
														90 VIEW SYS. Cost: 5  Bytes: 258  Cardinality: 3  					
															89 NESTED LOOPS OUTER  Cost: 5  Bytes: 84  Cardinality: 3  				
																86 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ORD_WORK_ORDER Cost: 2  Bytes: 15  Cardinality: 1  			
																	85 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.ORD_WORK_ORDER_PK Cost: 1  Cardinality: 1  		
																88 VIEW SYS. Cost: 3  Bytes: 39  Cardinality: 3  			
																	87 TABLE ACCESS FULL TABLE OCE211.ADM_STATES Cost: 3  Bytes: 24  Cardinality: 3  		
													92 INDEX RANGE SCAN INDEX OCE211.ORDER_ID Cost: 0  Cardinality: 1  						
				103 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1  Bytes: 7  Cardinality: 1  															
					102 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0  Cardinality: 1  														
			106 TABLE ACCESS BY INDEX ROWID TABLE OCE211.ADM_STATES Cost: 1  Bytes: 7  Cardinality: 1  																
				105 INDEX UNIQUE SCAN INDEX (UNIQUE) OCE211.SYS_C00105137 Cost: 0  Cardinality: 1  															


I see that the Cardinality is different (more in schema 2 where its taking longer)

Please advice what could be the obvious reason behind this.

Thanks for looking into this.

Mahi
Re: Query taking different time in tqo schemas [message #343061 is a reply to message #343047] Tue, 26 August 2008 02:36 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
The statistics were missing for the second schema. I ran the statement and getting good results now.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (ownname => 'oce2', degree=>2 , cascade=>true);

Thanks,
Mahi
Previous Topic: Methods to do tuning
Next Topic: NOT IN Vs. NOT EXISTS
Goto Forum:
  


Current Time: Thu Dec 08 22:33:22 CST 2016

Total time taken to generate the page: 0.10208 seconds