help tuning query

Scunningham99 · Dec 16, 2009

Hi We have this query which was produced using discoverer.

The cost is only 18 but the query is producing massive nested loops which i think is the problem.

Please can someone help me re-write the query:-

Code:

SELECT /*+ PARALLEL(o102266,16,2) PARALLEL(o102270,16,2)*/ o102270.CLASSIFICATION_NAME as E120175,o102266.EFFECTIVE_DATE
 as E129029,o102266.ELEMENT_NAME as E129695,o102266.LOCATION_CODE as E143440,o102266.ORGANIZATION_NAME 
as E148945,o102266.PAYROLL_NAME as E150361,o102266.SEGMENT1 as E158438,o102266.SEGMENT2 as E158554,o102266.SEGMENT3 
as E158651,o102266.SEGMENT4 as E158673,o102266.SEGMENT5 as E158687,o102266.SEGMENT6 as E158701,as111061_120175_OLD 
as as111061_120175_OLD,as111074_150361_OLD as as111074_150361_OLD,SUM(o102266.CREDIT_AMOUNT) as E125014_SUM,SUM(o102266.DEBIT_AMOUNT) 
as E126872_SUM
 FROM PAY_COSTING_DETAILS_V o102266,
      PAYBG_ELEMENT_CLASSIFICATION_V o102270,
     ( SELECT /*+ PARALLEL(o102271,16,2) */ o102271.CLASSIFICATION_NAME AS as111061_120175_OLD_2, MAX(o102271.ORDER_BY) 
AS as111061_120175_OLD FROM PAYLV_ELEMENT_CLASSIFICATION_V o102271  WHERE (o102271.BG_SECURITY_FLAG_CODE = 'Y') 
AND (o102271.CLASSIFICATION_NAME IS NOT NULL ) GROUP BY o102271.CLASSIFICATION_NAME),
     ( SELECT /*+ PARALLEL(o102319,16,2) */ o102319.PAYROLL_NAME AS as111074_150361_OLD_2, MAX(o102319.ORDER_BY) 
AS as111074_150361_OLD FROM PAYLV_PAYROLL_H_V o102319  WHERE (o102319.BG_SECURITY_FLAG_CODE = 'Y') GROUP BY o102319.PAYROLL_NAME)
 WHERE ( (o102270.CLASSIFICATION_ID = o102266.CLASSIFICATION_ID)
   and (o102270.CLASSIFICATION_NAME = as111061_120175_OLD_2(+))
   and (o102266.PAYROLL_NAME = as111074_150361_OLD_2(+)))
   AND (o102266.BUSINESS_GROUP_ID = NVL(PAY_US_ADHOC_UTILS.GET_SECPROFILE_BG_ID,o102266.
BUSINESS_GROUP_ID))
   AND (o102270.BG_SECURITY_FLAG_CODE = 'Y')
   AND (o102266.EFFECTIVE_DATE = '04-DEC-2009')
   AND (o102266.ELEMENT_NAME NOT LIKE 'Wage Accrual ER Taxes' AND o102266.ELEMENT_NAME NOT LIKE 'Wage Accrual Earnings')
   AND (o102266.PAYROLL_NAME = 'US HOA Bi-Weekly')
 GROUP BY as111061_120175_OLD,as111074_150361_OLD,o102270.
CLASSIFICATION_NAME,o102266.EFFECTIVE_DATE,o102266.
ELEMENT_NAME,
o102266.LOCATION_CODE,o102266.ORGANIZATION_NAME,o102266.
PAYROLL_NAME,o102266.SEGMENT1,o102266.SEGMENT2,o102266.
SEGMENT3,
o102266.SEGMENT4,o102266.SEGMENT5,o102266.SEGMENT6
 ORDER BY o102266.LOCATION_CODE ASC ;



Plan
SELECT STATEMENT  ALL_ROWSCost: 18  Bytes: 665  
Cardinality: 
1  
    109 PX COORDINATOR FORCED 
SERIAL
        108 PX SEND QC (ORDER) SYS.:TQ20010 Cost: 18  
Bytes: 665  
Cardinality: 
1  
            107 SORT GROUP BY  Cost: 18  Bytes: 665  
Cardinality: 
1  
                106 PX RECEIVE  Cost: 18  Bytes: 665  
Cardinality: 
1 
                    105 PX SEND RANGE SYS.:TQ20009 Cost: 
18  Bytes: 665  
Cardinality: 
1 
                        104 SORT GROUP BY  Cost: 18  Bytes: 
665  
Cardinality: 
1
                            103 NESTED LOOPS  Cost: 18  
Bytes: 665  
Cardinality: 
1 
                                101 NESTED LOOPS  Cost: 18  
Bytes: 657  
Cardinality: 
1  
                                    98 NESTED LOOPS  Cost: 
18  Bytes: 627  
Cardinality: 
1 
                                        95 HASH JOIN OUTER  
Cost: 18  
Bytes: 602  Cardinality: 
1  
                                            74 PX 
RECEIVE   
                                                73 PX SEND 
HASH 
SYS.:TQ20007 
                                                    72 
NESTED 
LOOPS 
                                                        70 
NESTED LOOPS  
Cost: 16  Bytes: 518  Cardinality: 
1  
 
68 NESTED 
LOOPS  Cost: 16  Bytes: 492  Cardinality: 
1 
   
65 NESTED 
LOOPS  Cost: 16  Bytes: 472  Cardinality: 
1  
    
62 
BUFFER 
SORT  
      
61 
PX 
RECEIVE 
        
60 PX SEND BROADCAST 
SYS.:TQ20005

   
59 BUFFER SORT  Cost: 18  Bytes: 665  Cardinality: 
1  
     
58 NESTED LOOPS  Cost: 15  Bytes: 448  Cardinality: 
1   
     
55 NESTED LOOPS  Cost: 15  Bytes: 415  Cardinality: 
1    
     
52 NESTED LOOPS  Cost: 15  Bytes: 392  Cardinality: 
1     
     
49 NESTED LOOPS  Cost: 15  Bytes: 368  Cardinality: 
1   
     
46 NESTED LOOPS  Cost: 15  Bytes: 339  Cardinality: 
1   
   
43 NESTED LOOPS  Cost: 15  Bytes: 332  Cardinality: 
1  
   
40 NESTED LOOPS  Cost: 15  Bytes: 233  Cardinality: 
1   
 
37 NESTED LOOPS  Cost: 15  Bytes: 215  Cardinality: 
1   
 
34 HASH JOIN  Cost: 15  Bytes: 208  Cardinality: 
1   
 
29 PX RECEIVE  Cost: 5  Bytes: 164  Cardinality: 
1   
   
28 PX SEND HASH SYS.:TQ20003 Cost: 5  Bytes: 164  Cardinality: 
1    
   
27 HASH JOIN OUTER  Cost: 5  Bytes: 164  Cardinality: 
1   
   
9 PX RECEIVE  Cost: 2  Bytes: 80  Cardinality: 
1   
  
8 PX SEND HASH SYS.:TQ20002 Cost: 2  Bytes: 80  Cardinality: 
1   
    
7 NESTED LOOPS  Cost: 2  Bytes: 80  Cardinality: 
1  
   
5 NESTED LOOPS  Cost: 2  Bytes: 77  Cardinality: 
1   
  
2 PX BLOCK 
ITERATOR                                          
   
1 TABLE ACCESS FULL TABLE HR.PAY_PAYROLL_ACTIONS Cost: 2  
Bytes: 21  Cardinality: 
1      
   
4 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ALL_PAYROLLS_F 
Cost: 2  Bytes: 56  Cardinality: 
1     
   
3 INDEX RANGE SCAN INDEX (UNIQUE) HR.PAY_PAYROLLS_F_PK 
Cost: 1  Cardinality: 
1   
   
6 INDEX UNIQUE SCAN INDEX (UNIQUE) 
HR.PAY_CONSOLIDATION_SETS_PK Cost: 0  Bytes: 3  
Cardinality: 1  
    
26 BUFFER 
SORT     
   
25 PX RECEIVE  Cost: 2  Bytes: 168  Cardinality: 
2                                                      
     
24 PX SEND HASH SYS.:TQ20000 Cost: 2  Bytes: 168  
Cardinality: 
2                                                  
  
23 VIEW APPS. Cost: 2  Bytes: 168  Cardinality: 
2                                              
  
22 HASH GROUP BY  Cost: 2  Bytes: 168  Cardinality: 
2                                          
  
21 PX COORDINATOR FORCED 
SERIAL                                      
    
20 PX SEND QC (RANDOM) SYS.:TQ10001 Cost: 2  Bytes: 168  
Cardinality: 2                                  
     
19 BUFFER SORT  Cost: 18  Bytes: 665  Cardinality: 
1                              
   

18 VIEW VIEW APPS.PAYLV_PAYROLL_H_V Cost: 2  Bytes: 168  
Cardinality: 2                          
  
17 UNION-ALL                      
    
11 PX BLOCK ITERATOR  Cost: 2  Bytes: 40  Cardinality: 
1                  
  
10 TABLE ACCESS FULL TABLE HR.PAY_ALL_PAYROLLS_F Cost: 2  
Bytes: 40  Cardinality: 1              
   
16 BUFFER SORT                  
     
15 PX RECEIVE  Cost: 2  Cardinality: 1              
   
14 PX SEND ROUND-ROBIN SYS.:TQ10000 Cost: 2  Cardinality: 

1          
 
13 FILTER      
               
12 FAST DUAL  Cost: 2  Cardinality: 1  
 
33 PX RECEIVE  Cost: 10  Bytes: 6,204  Cardinality: 
141   
  
32 PX SEND HASH SYS.:TQ20004 Cost: 10  Bytes: 6,204  
Cardinality: 
141  
  
31 PX BLOCK ITERATOR  Cost: 10  Bytes: 6,204  Cardinality: 
141   
   
30 TABLE ACCESS FULL TABLE HR.PER_ALL_ASSIGNMENTS_F Cost: 
10  Bytes: 6,204  Cardinality: 
141 
     
36 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_LOCATIONS_ALL 
Cost: 1  Bytes: 7  Cardinality: 
1   

 
35 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_LOCATIONS_PK 
Cost: 0  Cardinality: 
1   
 
39 TABLE ACCESS BY INDEX ROWID TABLE 
HR.PAY_ASSIGNMENT_ACTIONS Cost: 4  Bytes: 18  Cardinality: 
1  

38 INDEX RANGE SCAN INDEX HR.PAY_ASSIGNMENT_ACTIONS_N51 
Cost: 2  Cardinality: 
1    
 
42 TABLE ACCESS BY INDEX ROWID TABLE HR.PER_ALL_PEOPLE_F 
Cost: 2  Bytes: 99  Cardinality: 
1     
 
41 INDEX RANGE SCAN INDEX (UNIQUE) HR.PER_PEOPLE_F_PK Cost: 
1  Cardinality: 
2 

45 TABLE ACCESS BY INDEX ROWID TABLE 
HR.HR_ALL_ORGANIZATION_UNITS Cost: 1  Bytes: 7  
Cardinality: 
1   
  
44 INDEX UNIQUE SCAN INDEX (UNIQUE) 
HR.HR_ORGANIZATION_UNITS_PK Cost: 0  Cardinality: 
1     

48 TABLE ACCESS BY INDEX ROWID TABLE 
HR.HR_ALL_ORGANIZATION_UNITS_TL Cost: 1  Bytes: 29  
Cardinality: 
1

47 INDEX UNIQUE SCAN INDEX (UNIQUE) 
HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Cardinality: 
1  

51 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_LOCATIONS_ALL_TL 
Cost: 1  Bytes: 24  Cardinality: 
1  

50 INDEX UNIQUE SCAN INDEX (UNIQUE) 
HR.HR_LOCATIONS_ALL_TL_PK Cost: 0  Cardinality: 
1 

54 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_COSTS Cost: 3  
Bytes: 322  Cardinality: 
14 

53 INDEX RANGE SCAN INDEX HR.PAY_COSTS_FK1 Cost: 2  
Cardinality: 
42 

57 TABLE ACCESS BY INDEX ROWID TABLE 
HR.PAY_COST_ALLOCATION_KEYFLEX Cost: 1  Bytes: 33  
Cardinality: 1   

56 INDEX UNIQUE SCAN INDEX (UNIQUE) 
HR.PAY_COST_ALLOCATION_KEYFLE_PK Cost: 0  Cardinality: 
1 

64 PX BLOCK ITERATOR  Cost: 13  Bytes: 14,280  Cardinality: 
595  

63 TABLE ACCESS FULL TABLE HR.PAY_ELEMENT_TYPES_F Cost: 13  Bytes: 14,280  Cardinality: 595 

67 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_CLASSIFICATIONS Cost: 1  Bytes: 20  Cardinality: 1   

66 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_CLASSIFICATION_PK Cost: 0  Cardinality: 1

69 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_CLASS_TL_PK Cost: 0  Cardinality: 1  

TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_CLASSIFICATIONS_TL Cost: 1  Bytes: 26  Cardinality: 1  

                                            94 PX RECEIVE  Cost: 2  Bytes: 168  Cardinality: 2   

                                                93 PX SEND HASH SYS.:TQ20008 Cost: 2  Bytes: 168  Cardinality: 2 

                                                    92 VIEW APPS. Cost: 2  Bytes: 168  Cardinality: 2  

                                                        91 HASH GROUP BY  Cost: 2  Bytes: 168  Cardinality: 2  

                                                            90 PX RECEIVE  Cost: 2  Bytes: 168  Cardinality: 2 

89 PX SEND HASH SYS.:TQ20006 Cost: 2  Bytes: 168  Cardinality: 2  

88 HASH GROUP BY  Cost: 2  Bytes: 168  Cardinality: 2

87 VIEW VIEW APPS.PAYLV_ELEMENT_CLASSIFICATION_V Cost: 2  
Bytes: 168  Cardinality: 2 

86 
UNION-ALL  
 
80 NESTED 
LOOPS
 
78 NESTED LOOPS  Cost: 2  Bytes: 46  Cardinality: 
1 
  
76 PX BLOCK ITERATOR

75 TABLE ACCESS FULL TABLE HR.PAY_ELEMENT_CLASSIFICATIONS 
Cost: 2  Bytes: 20  Cardinality: 
1   

77 INDEX UNIQUE SCAN INDEX (UNIQUE) 
HR.PAY_ELEMENT_CLASS_TL_PK Cost: 0  Cardinality: 1   

    
79 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_CLASSIFICATIONS_TL Cost: 1  Bytes: 26  Cardinality: 1   

     
85 BUFFER SORT    

    
84 PX RECEIVE  Cost: 2  Cardinality: 1   

                                                          &amp;n

Sy UK

Thargy · Dec 16, 2009

If you are serious about this, then you have some work to do.

This query is typical of "generated code" and is as atrocious as nearly all such queries are. Rest assured that you are not alone, I have seen such monsters produced by data access layers, abstraction layers, Business Objects etc.

To improve the performance of this query you will have to provide us with the following:-

Create table statements for each and every table referenced in the query.
Insert statements for representative date for the above tables (with sensitive data such as addresses and bank details suitably obfuscated) and an English language narrative of what the query is supposed to do.

Note that this narrative must not contain the words select, join or any other SQL keyword.

We also need you to post what the desired output result set is for the data you provide above.

Also, it must be possible to override discoverer, and make it use the "tuned" query instead of generating its own. This might presumably be achieved by means of a view, but since I know nothing about discoverer, I'll leave that bit up to you.

So, are you serious or just wanting some magic woofle dust sprinkled on your database?

Note that a hacked 'solution' is possible, whereby we just make helpful comments about the structure of the query without knowing its purpose. As a first step towards a solution, I've used a formatter on the query and posted below. Over to you now.

Code:

 SELECT                   /*+ PARALLEL(o102266,16,2) PARALLEL(o102270,16,2)*/
        o102270.CLASSIFICATION_NAME AS E120175,
         o102266.EFFECTIVE_DATE         AS E129029,
         o102266.ELEMENT_NAME           AS E129695,
         o102266.LOCATION_CODE          AS E143440,
         o102266.ORGANIZATION_NAME  AS E148945,
         o102266.PAYROLL_NAME            AS E150361,
         o102266.SEGMENT1 AS E158438,
         o102266.SEGMENT2 AS E158554,
         o102266.SEGMENT3 AS E158651,
         o102266.SEGMENT4 AS E158673,
         o102266.SEGMENT5 AS E158687,
         o102266.SEGMENT6 AS E158701,
         as111061_120175_OLD AS as111061_120175_OLD,
         as111074_150361_OLD AS as111074_150361_OLD,
         SUM (o102266.CREDIT_AMOUNT) AS E125014_SUM,
         SUM (o102266.DEBIT_AMOUNT) AS E126872_SUM
    FROM PAY_COSTING_DETAILS_V o102266,
         PAYBG_ELEMENT_CLASSIFICATION_V o102270,
         (  SELECT                               /*+ PARALLEL(o102271,16,2) */
                  o102271.CLASSIFICATION_NAME
                      AS as111061_120175_OLD_2,
                   MAX (o102271.ORDER_BY) AS as111061_120175_OLD
              FROM PAYLV_ELEMENT_CLASSIFICATION_V o102271
             WHERE (o102271.BG_SECURITY_FLAG_CODE = 'Y')
                   AND (o102271.CLASSIFICATION_NAME IS NOT NULL)
          GROUP BY o102271.CLASSIFICATION_NAME),
         (  SELECT                               /*+ PARALLEL(o102319,16,2) */
                  o102319.PAYROLL_NAME
                      AS as111074_150361_OLD_2,
                   MAX (o102319.ORDER_BY) AS as111074_150361_OLD
              FROM PAYLV_PAYROLL_H_V o102319
             WHERE (o102319.BG_SECURITY_FLAG_CODE = 'Y')
          GROUP BY o102319.PAYROLL_NAME)
   WHERE (    (o102270.CLASSIFICATION_ID = o102266.CLASSIFICATION_ID)
          AND (o102270.CLASSIFICATION_NAME = as111061_120175_OLD_2(+))
          AND (o102266.PAYROLL_NAME = as111074_150361_OLD_2(+)))
         AND (o102266.BUSINESS_GROUP_ID =
                 NVL (PAY_US_ADHOC_UTILS.GET_SECPROFILE_BG_ID,
                      o102266.BUSINESS_GROUP_ID))
         AND (o102270.BG_SECURITY_FLAG_CODE = 'Y')
         AND (o102266.EFFECTIVE_DATE = '04-DEC-2009')
         AND (o102266.ELEMENT_NAME NOT LIKE 'Wage Accrual ER Taxes'
              AND o102266.ELEMENT_NAME NOT LIKE 'Wage Accrual Earnings')
         AND (o102266.PAYROLL_NAME = 'US HOA Bi-Weekly')
GROUP BY as111061_120175_OLD,
         as111074_150361_OLD,
         o102270.CLASSIFICATION_NAME,
         o102266.EFFECTIVE_DATE,
         o102266.ELEMENT_NAME,
         o102266.LOCATION_CODE,
         o102266.ORGANIZATION_NAME,
         o102266.PAYROLL_NAME,
         o102266.SEGMENT1,
         o102266.SEGMENT2,
         o102266.SEGMENT3,
         o102266.SEGMENT4,
         o102266.SEGMENT5,
         o102266.SEGMENT6
ORDER BY o102266.LOCATION_CODE ASC;

Regards

T

Scunningham99 · Dec 17, 2009

thanks for your help

got it working instead of runnign for 20mins +

in now runs in under 5 seconds

by replacing

Code:

AND (o102270.BG_SECURITY_FLAG_CODE = 'Y')

with:

Code:

AND (o102270.BG_SECURITY_FLAG_CODE = (select bg_security_flag_code from PAYBG_ELEMENT_CLASSIFICATION_V 
where bg_security_flag_code = 'Y' and classification_id = o102270.CLASSIFICATION_ID))

Plan

Code:

SELECT STATEMENT  ALL_ROWSCost: 10  Bytes: 665  Cardinality: 1  																														
	103 SORT GROUP BY  Cost: 10  Bytes: 665  Cardinality: 1  																													
		102 FILTER  																												
			97 PX COORDINATOR FORCED SERIAL  																											
				96 PX SEND QC (RANDOM) SYS.:TQ20006 Cost: 9  Bytes: 665  Cardinality: 1  																										
					95 HASH JOIN OUTER  Cost: 9  Bytes: 665  Cardinality: 1  																									
						77 PX RECEIVE  Cost: 6  Bytes: 581  Cardinality: 1  																								
							76 PX SEND HASH SYS.:TQ20005 Cost: 6  Bytes: 581  Cardinality: 1  																							
								75 HASH JOIN OUTER BUFFERED  Cost: 6  Bytes: 581  Cardinality: 1  																						
									54 PX RECEIVE  																					
										53 PX SEND HASH SYS.:TQ20003 																				
											52 NESTED LOOPS  																			
												50 NESTED LOOPS  Cost: 4  Bytes: 497  Cardinality: 1  																		
													48 NESTED LOOPS  Cost: 4  Bytes: 467  Cardinality: 1  																	
														46 NESTED LOOPS  Cost: 4  Bytes: 459  Cardinality: 1  																
															43 NESTED LOOPS  Cost: 4  Bytes: 433  Cardinality: 1  															
																40 NESTED LOOPS  Cost: 3  Bytes: 413  Cardinality: 1  														
																	37 NESTED LOOPS  Cost: 3  Bytes: 389  Cardinality: 1  													
																		34 NESTED LOOPS  Cost: 3  Bytes: 364  Cardinality: 1  												
																			31 NESTED LOOPS  Cost: 3  Bytes: 331  Cardinality: 1  											
																				28 NESTED LOOPS  Cost: 3  Bytes: 308  Cardinality: 1  										
																					25 NESTED LOOPS  Cost: 3  Bytes: 284  Cardinality: 1  									
																						22 NESTED LOOPS  Cost: 3  Bytes: 255  Cardinality: 1  								
																							19 NESTED LOOPS  Cost: 3  Bytes: 248  Cardinality: 1  							
																								16 NESTED LOOPS  Cost: 3  Bytes: 149  Cardinality: 1  						
																									13 NESTED LOOPS  Cost: 3  Bytes: 142  Cardinality: 1  					
																										10 NESTED LOOPS  Cost: 3  Bytes: 98  Cardinality: 1  				
																											7 NESTED LOOPS  Cost: 2  Bytes: 80  Cardinality: 1  			
																												5 NESTED LOOPS  Cost: 2  Bytes: 77  Cardinality: 1  		
																													2 PX BLOCK ITERATOR  	
																														1 TABLE ACCESS FULL TABLE HR.PAY_PAYROLL_ACTIONS Cost: 2  Bytes: 21  Cardinality: 1  
																													4 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ALL_PAYROLLS_F Cost: 2  Bytes: 56  Cardinality: 1  	
																														3 INDEX RANGE SCAN INDEX (UNIQUE) HR.PAY_PAYROLLS_F_PK Cost: 1  Cardinality: 1  
																												6 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_CONSOLIDATION_SETS_PK Cost: 0  Bytes: 3  Cardinality: 1  		
																											9 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ASSIGNMENT_ACTIONS Cost: 21  Bytes: 1,332  Cardinality: 74  			
																												8 INDEX RANGE SCAN INDEX HR.PAY_ASSIGNMENT_ACTIONS_N50 Cost: 3  Cardinality: 297  		
																										12 TABLE ACCESS BY INDEX ROWID TABLE HR.PER_ALL_ASSIGNMENTS_F Cost: 5  Bytes: 44  Cardinality: 1  				
																											11 INDEX RANGE SCAN INDEX (UNIQUE) HR.PER_ASSIGNMENTS_F_PK Cost: 1  Cardinality: 4  			
																									15 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_LOCATIONS_ALL Cost: 1  Bytes: 7  Cardinality: 1  					
																										14 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_LOCATIONS_PK Cost: 0  Cardinality: 1  				
																								18 TABLE ACCESS BY INDEX ROWID TABLE HR.PER_ALL_PEOPLE_F Cost: 2  Bytes: 99  Cardinality: 1  						
																									17 INDEX RANGE SCAN INDEX (UNIQUE) HR.PER_PEOPLE_F_PK Cost: 1  Cardinality: 2  					
																							21 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 1  Bytes: 7  Cardinality: 1  							
																								20 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ORGANIZATION_UNITS_PK Cost: 0  Cardinality: 1  						
																						24 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ALL_ORGANIZATION_UNITS_TL Cost: 1  Bytes: 29  Cardinality: 1  								
																							23 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Cardinality: 1  							
																					27 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_LOCATIONS_ALL_TL Cost: 1  Bytes: 24  Cardinality: 1  									
																						26 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_LOCATIONS_ALL_TL_PK Cost: 0  Cardinality: 1  								
																				30 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_COSTS Cost: 3  Bytes: 322  Cardinality: 14  										
																					29 INDEX RANGE SCAN INDEX HR.PAY_COSTS_FK1 Cost: 2  Cardinality: 42  									
																			33 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_COST_ALLOCATION_KEYFLEX Cost: 1  Bytes: 33  Cardinality: 1  											
																				32 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_COST_ALLOCATION_KEYFLE_PK Cost: 0  Cardinality: 1  										
																		36 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_INPUT_VALUES_F Cost: 2  Bytes: 25  Cardinality: 1  												
																			35 INDEX RANGE SCAN INDEX (UNIQUE) HR.PAY_INPUT_VALUES_F_PK Cost: 1  Cardinality: 1  											
																	39 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_TYPES_F Cost: 2  Bytes: 24  Cardinality: 1  													
																		38 INDEX RANGE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_TYPES_F_PK Cost: 1  Cardinality: 1  												
																42 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_CLASSIFICATIONS Cost: 1  Bytes: 20  Cardinality: 1  														
																	41 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_CLASSIFICATION_PK Cost: 0  Cardinality: 1  													
															45 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_CLASSIFICATIONS_TL Cost: 1  Bytes: 26  Cardinality: 1  															
																44 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_CLASS_TL_PK Cost: 0  Cardinality: 1  														
														47 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_INPUT_VALUES_F_TL_PK Cost: 0  Bytes: 8  Cardinality: 1  																
													49 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_TYPES_F_TL_PK Cost: 0  Cardinality: 1  																	
												51 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_TYPES_F_TL Cost: 1  Bytes: 30  Cardinality: 1  																		
									74 PX RECEIVE  Cost: 2  Bytes: 168  Cardinality: 2  																					
										73 PX SEND HASH SYS.:TQ20004 Cost: 2  Bytes: 168  Cardinality: 2  																				
											72 VIEW APPS. Cost: 2  Bytes: 168  Cardinality: 2  																			
												71 HASH GROUP BY  Cost: 2  Bytes: 168  Cardinality: 2  																		
													70 PX RECEIVE  Cost: 2  Bytes: 168  Cardinality: 2  																	
														69 PX SEND HASH SYS.:TQ20002 Cost: 2  Bytes: 168  Cardinality: 2  																
															68 HASH GROUP BY  Cost: 2  Bytes: 168  Cardinality: 2  															
																67 VIEW VIEW APPS.PAYLV_ELEMENT_CLASSIFICATION_V Cost: 2  Bytes: 168  Cardinality: 2  														
																	66 UNION-ALL  													
																		60 NESTED LOOPS  												
																			58 NESTED LOOPS  Cost: 2  Bytes: 46  Cardinality: 1  											
																				56 PX BLOCK ITERATOR  										
																					55 TABLE ACCESS FULL TABLE HR.PAY_ELEMENT_CLASSIFICATIONS Cost: 2  Bytes: 20  Cardinality: 1  									
																				57 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_CLASS_TL_PK Cost: 0  Cardinality: 1  										
																			59 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_CLASSIFICATIONS_TL Cost: 1  Bytes: 26  Cardinality: 1  											
																		65 BUFFER SORT  												
																			64 PX RECEIVE  Cost: 2  Cardinality: 1  											
																				63 PX SEND ROUND-ROBIN SYS.:TQ20000 Cost: 2  Cardinality: 1  										
																					62 FILTER  									
																						61 FAST DUAL  Cost: 2  Cardinality: 1  								
						94 BUFFER SORT  																								
							93 PX RECEIVE  Cost: 2  Bytes: 168  Cardinality: 2  																							
								92 PX SEND HASH SYS.:TQ20001 Cost: 2  Bytes: 168  Cardinality: 2  																						
									91 VIEW APPS. Cost: 2  Bytes: 168  Cardinality: 2  																					
										90 HASH GROUP BY  Cost: 2  Bytes: 168  Cardinality: 2  																				
											89 PX COORDINATOR FORCED SERIAL  																			
												88 PX SEND QC (RANDOM) SYS.:TQ10001 Cost: 2  Bytes: 168  Cardinality: 2  																		
													87 BUFFER SORT  Cost: 10  Bytes: 665  Cardinality: 1  																	
														86 VIEW VIEW APPS.PAYLV_PAYROLL_H_V Cost: 2  Bytes: 168  Cardinality: 2  																
															85 UNION-ALL  															
																79 PX BLOCK ITERATOR  Cost: 2  Bytes: 40  Cardinality: 1  														
																	78 TABLE ACCESS FULL TABLE HR.PAY_ALL_PAYROLLS_F Cost: 2  Bytes: 40  Cardinality: 1  													
																84 BUFFER SORT  														
																	83 PX RECEIVE  Cost: 2  Cardinality: 1  													
																		82 PX SEND ROUND-ROBIN SYS.:TQ10000 Cost: 2  Cardinality: 1  												
																			81 FILTER  											
																				80 FAST DUAL  Cost: 2  Cardinality: 1  										
			101 NESTED LOOPS  Cost: 1  Bytes: 27  Cardinality: 1  																											
				99 TABLE ACCESS BY INDEX ROWID TABLE HR.PAY_ELEMENT_CLASSIFICATIONS Cost: 1  Bytes: 20  Cardinality: 1  																										
					98 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_CLASSIFICATION_PK Cost: 0  Cardinality: 1  																									
				100 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.PAY_ELEMENT_CLASS_TL_PK Cost: 0  Bytes: 7  Cardinality: 1

Sy UK

Thargy · Dec 17, 2009

Nice one. It's always nice to get a big win like that.

Regards

T

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

help tuning query

Scunningham99

Programmer

Thargy

Technical User

Scunningham99

Programmer

Thargy

Technical User

Similar threads

Part and Inventory Search

Sponsor