Home » RDBMS Server » Performance Tuning » please help me tune this query
please help me tune this query [message #502030] Sun, 03 April 2011 08:42 Go to next message
miroconnect@yahoo.com
Messages: 199
Registered: April 2006
Senior Member
I have this complex query
/* Formatted on 2011/04/03 09:41 (Formatter Plus v4.8.8) */
/* dynamic native SQL query */

SELECT COUNT (*)
  FROM (SELECT   *
            FROM (SELECT audit_number, formatted_audit_number,
                         ag.sys_audit_id, audit_begin_date, audit_end_date,
                         auditee_name, ein, duns, city, state,
                         org.award_org_abbrev, audit_review_code,
                         audit_issue_date, oig_due_date,
                         audit_batch_received_date,
                         REPLACE
                            ((SELECT wmsys.wm_concat (gp.prog_acronym)
                                FROM inf_program gp, ea_audit_program ap
                               WHERE ap.sys_prog_id = gp.sys_prog_id
                                 AND ap.sys_audit_id = ag.sys_audit_id
                                 AND ap.is_live = 'Y'),
                             ',',
                             ' '
                            ) AS prog_acronyms,
                         REPLACE
                            ((SELECT wmsys.wm_concat (ig.grant_nbr)
                                FROM inf_grant ig,
                                     ea_audit_program ap,
                                     ea_audit_prog_grant apg
                               WHERE apg.sys_audit_prog_id =
                                                          ap.sys_audit_prog_id
                                 AND apg.sys_grant_id = ig.sys_grant_id
                                 AND ap.sys_audit_id = ag.sys_audit_id
                                 AND ap.is_live = 'Y'),
                             ',',
                             ' '
                            ) AS grant_no,
                         (SELECT COUNT (fin.finding_number)
                            FROM ea_finding fin
                           WHERE fin.sys_audit_id =
                                            ag.sys_audit_id)
                                                            AS no_of_findings
                    FROM ea_audit_general ag, inf_organization org
                   WHERE ag.sys_org_id = org.sys_org_id)
           WHERE award_org_abbrev = 'ACF'
             AND TRUNC (audit_begin_date) > :1
             AND TRUNC (audit_end_date) < :2
             AND (TRUNC (audit_batch_received_date) BETWEEN :3 AND :4)
             AND sys_audit_id IN (
                    SELECT sys_audit_id
                      FROM (SELECT *
                              FROM (SELECT f.finding_number, f.finding_amt,
                                           prog.admin_by, prog.sys_prog_id,
                                           prog.grant_nbr, ag.sys_audit_id,
                                           (SELECT    first_name
                                                   || ' '
                                                   || last_name
                                              FROM inf_person
                                             WHERE sys_person_id =
                                                                ag.fms_user_id)
                                                                       AS fms,
                                           (SELECT last_name
                                              FROM inf_person
                                             WHERE sys_person_id =
                                                       ag.as_user_id)
                                                                     AS assec,
                                           prog.grants_officer, prog.pd,
                                           prog.fos, amendment_status,
                                           audit_status, ag.currentstep
                                      FROM ea_finding f,
                                           (SELECT currentstep,
                                                   eag.sys_audit_id,
                                                   eag.fms_user_id,
                                                   eag.as_user_id
                                              FROM (SELECT (   wfentry.NAME
                                                            || ','
                                                            || currentstep.step_id
                                                           ) AS currentstep,
                                                           (CASE
                                                               WHEN wfentry.NAME IN
                                                                      ('audit-program-fo-workflow',
                                                                       'audit-program-amendment-fo-workflow'
                                                                      )
                                                               AND wfentity.object_type_id =
                                                                             3
                                                                  THEN (SELECT eag.sys_audit_id
                                                                          FROM ea_audit_general eag,
                                                                               ea_audit_program eap,
                                                                               ea_audit_prog_analyst eapa
                                                                         WHERE eag.sys_audit_id =
                                                                                  eap.sys_audit_id
                                                                           AND eap.sys_audit_prog_id =
                                                                                  eapa.sys_audit_prog_id
                                                                           AND eapa.sys_audit_prog_analyst_id =
                                                                                  wfentity.object_id)
                                                               WHEN wfentry.NAME IN
                                                                      ('audit-program-notification-workflow'
                                                                      )
                                                               AND wfentity.object_type_id =
                                                                             4
                                                                  THEN (SELECT eag.sys_audit_id
                                                                          FROM ea_audit_general eag,
                                                                               ea_audit_program eap,
                                                                               ea_audit_prog_notification eapn
                                                                         WHERE eag.sys_audit_id =
                                                                                  eap.sys_audit_id
                                                                           AND eap.sys_audit_prog_id =
                                                                                  eapn.sys_audit_prog_id
                                                                           AND eapn.sys_notification_id =
                                                                                  wfentity.object_id)
                                                               WHEN wfentry.NAME IN
                                                                      ('audit-program-workflow',
                                                                       'audit-program-amendment-workflow',
                                                                       'audit-program-dfi-review-findings-workflow'
                                                                      )
                                                               AND wfentity.object_type_id =
                                                                             2
                                                                  THEN (SELECT eag.sys_audit_id
                                                                          FROM ea_audit_general eag,
                                                                               ea_audit_program eap
                                                                         WHERE eag.sys_audit_id =
                                                                                  eap.sys_audit_id
                                                                           AND eap.sys_audit_prog_id =
                                                                                  wfentity.object_id)
                                                               WHEN wfentry.NAME IN
                                                                      ('stg-audit-workflow',
                                                                       'ihs-audit-workflow'
                                                                      )
                                                               AND wfentity.object_type_id =
                                                                             1
                                                                  THEN (SELECT eag.sys_audit_id
                                                                          FROM ea_audit_general eag
                                                                         WHERE eag.sys_audit_id =
                                                                                  wfentity.object_id)
                                                            END
                                                           ) AS sys_audit_id
                                                      FROM os_wfentry wfentry,
                                                           os_currentstep currentstep,
                                                           wf_entity wfentity
                                                     WHERE wfentry.ID =
                                                              currentstep.entry_id
                                                       AND wfentity.entry_id =
                                                                    wfentry.ID) wf,
                                                   ea_audit_general eag
                                             WHERE eag.sys_audit_id = wf.sys_audit_id(+)) ag,
                                           (SELECT ap.sys_audit_id,
                                                   ap.sys_prog_id,
                                                   ig.grant_nbr, ip.admin_by,
                                                   (SELECT    first_name
                                                           || ' '
                                                           || last_name
                                                      FROM inf_person
                                                     WHERE sys_person_id =
                                                                 ap.go_user_id)
                                                            AS grants_officer,
                                                   (SELECT    first_name
                                                           || ' '
                                                           || last_name
                                                      FROM inf_person
                                                     WHERE sys_person_id =
                                                                 ap.pd_user_id)
                                                                        AS pd,
                                                   (SELECT    first_name
                                                           || ' '
                                                           || last_name
                                                      FROM inf_person
                                                     WHERE sys_person_id =
                                                               apa.sys_user_id)
                                                                       AS fos,
                                                   (SELECT status
                                                      FROM ea_audit_prog_amendment eapg
                                                     WHERE eapg.sys_audit_prog_id =
                                                              ap.sys_audit_prog_id)
                                                          AS amendment_status,
                                                   ag.audit_review_code
                                                              AS audit_status
                                              FROM ea_audit_program ap,
                                                   ea_audit_prog_grant eapg,
                                                   inf_grant ig,
                                                   inf_program ip,
                                                   ea_audit_general ag,
                                                   ea_audit_prog_analyst apa
                                             WHERE ag.sys_audit_id =
                                                               ap.sys_audit_id
                                               AND ap.sys_audit_prog_id =
                                                        eapg.sys_audit_prog_id
                                               AND eapg.sys_grant_id =
                                                               ig.sys_grant_id
                                               AND ip.sys_prog_id =
                                                                ap.sys_prog_id
                                               AND ap.sys_audit_prog_id = apa.sys_audit_prog_id(+)) prog
                                     WHERE ag.sys_audit_id = f.sys_audit_id
                                       AND ag.sys_audit_id = prog.sys_audit_id(+))
                             WHERE 1 = 1
                               AND (   LOWER (assec) LIKE :5
                                    OR LOWER (fms) LIKE :6
                                    OR LOWER (grants_officer) LIKE :7
                                    OR LOWER (pd) LIKE :8
                                    OR LOWER (fos) LIKE :9
                                   )))
        ORDER BY audit_batch_received_date DESC, sys_audit_id ASC)


this is the explain plan from toad,
Plan
SELECT STATEMENT  ALL_ROWSCost: 262  														
	59 SORT AGGREGATE  Bytes: 37  Cardinality: 1  													
		58 FILTER  												
			5 FILTER  											
				4 NESTED LOOPS  Cost: 116  Bytes: 37  Cardinality: 1  										
					1 TABLE ACCESS FULL TABLE AUDT.EA_AUDIT_GENERAL Cost: 115  Bytes: 29  Cardinality: 1  									
					3 TABLE ACCESS BY INDEX ROWID TABLE AUDT.INF_ORGANIZATION Cost: 1  Bytes: 8  Cardinality: 1  									
						2 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.INF_ORGANIZATION_PK Cost: 0  Cardinality: 1  								
			57 FILTER  											
				52 NESTED LOOPS OUTER  Cost: 146  Bytes: 490  Cardinality: 2  										
					26 MERGE JOIN CARTESIAN  Cost: 10  Bytes: 232  Cardinality: 1  									
						23 NESTED LOOPS OUTER  Cost: 9  Bytes: 226  Cardinality: 1  								
							7 TABLE ACCESS BY INDEX ROWID TABLE AUDT.EA_AUDIT_GENERAL Cost: 2  Bytes: 15  Cardinality: 1  							
								6 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_GENERAL_PK Cost: 1  Cardinality: 1  						
							22 VIEW  Cost: 7  Bytes: 211  Cardinality: 1  							
								21 NESTED LOOPS  Cost: 7  Bytes: 64  Cardinality: 1  						
									19 NESTED LOOPS  Cost: 7  Bytes: 58  Cardinality: 1  					
										16 NESTED LOOPS  Cost: 5  Bytes: 47  Cardinality: 1  				
											14 NESTED LOOPS OUTER  Cost: 5  Bytes: 43  Cardinality: 1  			
												11 NESTED LOOPS  Cost: 3  Bytes: 32  Cardinality: 1  		
													8 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_GENERAL_PK Cost: 1  Bytes: 6  Cardinality: 1  	
													10 TABLE ACCESS BY INDEX ROWID TABLE AUDT.EA_AUDIT_PROGRAM Cost: 2  Bytes: 26  Cardinality: 1  	
														9 INDEX RANGE SCAN INDEX AUDT.EA_AUDIT_PROGRAM_IDX1 Cost: 1  Cardinality: 1  
												13 TABLE ACCESS BY INDEX ROWID TABLE AUDT.EA_AUDIT_PROG_ANALYST Cost: 2  Bytes: 11  Cardinality: 1  		
													12 INDEX RANGE SCAN INDEX AUDT.EA_AUDIT_PROG_ANALYST_IX01 Cost: 1  Cardinality: 1  	
											15 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.INF_PROGRAM_PK Cost: 0  Bytes: 4  Cardinality: 1  			
										18 TABLE ACCESS BY INDEX ROWID TABLE AUDT.EA_AUDIT_PROG_GRANT Cost: 2  Bytes: 11  Cardinality: 1  				
											17 INDEX RANGE SCAN INDEX AUDT.EA_AUDIT_PROG_GRANT_R01 Cost: 1  Cardinality: 1  			
									20 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.INF_GRANT_PK Cost: 0  Bytes: 6  Cardinality: 1  					
						25 BUFFER SORT  Cost: 3  Bytes: 24  Cardinality: 4  								
							24 INDEX RANGE SCAN INDEX AUDT.EA_FINDING_IDX01 Cost: 1  Bytes: 24  Cardinality: 4  							
					51 VIEW  Cost: 136  Bytes: 26  Cardinality: 2  									
						50 FILTER  								
							31 HASH JOIN  Cost: 133  Bytes: 1,330,546  Cardinality: 27,154  							
								27 INDEX FAST FULL SCAN INDEX AUDT.OS_CURRENTSTEP Cost: 18  Bytes: 169,842  Cardinality: 28,307  						
								30 HASH JOIN  Cost: 113  Bytes: 1,167,622  Cardinality: 27,154  						
									28 INDEX FAST FULL SCAN INDEX (UNIQUE) AUDT.WF_ENTITY_U02 Cost: 25  Bytes: 407,310  Cardinality: 27,154  					
									29 TABLE ACCESS FULL TABLE AUDT.OS_WFENTRY Cost: 86  Bytes: 1,647,072  Cardinality: 58,824  					
							38 NESTED LOOPS  Cost: 3  Bytes: 30  Cardinality: 1  							
								36 NESTED LOOPS  Cost: 3  Bytes: 24  Cardinality: 1  						
									33 TABLE ACCESS BY INDEX ROWID TABLE AUDT.EA_AUDIT_PROG_ANALYST Cost: 2  Bytes: 12  Cardinality: 1  					
										32 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_PROG_ANALYST_PK Cost: 1  Cardinality: 1  				
									35 TABLE ACCESS BY INDEX ROWID TABLE AUDT.EA_AUDIT_PROGRAM Cost: 1  Bytes: 187,260  Cardinality: 15,605  					
										34 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_PROGRAM_PK Cost: 0  Cardinality: 1  				
								37 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_GENERAL_PK Cost: 0  Bytes: 76,164  Cardinality: 12,694  						
							44 NESTED LOOPS  Cost: 3  Bytes: 29  Cardinality: 1  							
								42 NESTED LOOPS  Cost: 3  Bytes: 23  Cardinality: 1  						
									39 INDEX RANGE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_PROG_NOTIFICATION_U01 Cost: 2  Bytes: 11  Cardinality: 1  					
									41 TABLE ACCESS BY INDEX ROWID TABLE AUDT.EA_AUDIT_PROGRAM Cost: 1  Bytes: 12  Cardinality: 1  					
										40 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_PROGRAM_PK Cost: 0  Cardinality: 1  				
								43 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_GENERAL_PK Cost: 0  Bytes: 6  Cardinality: 1  						
							48 NESTED LOOPS  Cost: 2  Bytes: 18  Cardinality: 1  							
								46 TABLE ACCESS BY INDEX ROWID TABLE AUDT.EA_AUDIT_PROGRAM Cost: 2  Bytes: 12  Cardinality: 1  						
									45 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_PROGRAM_PK Cost: 1  Cardinality: 1  					
								47 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_GENERAL_PK Cost: 0  Bytes: 76,164  Cardinality: 12,694  						
							49 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.EA_AUDIT_GENERAL_PK Cost: 1  Bytes: 6  Cardinality: 1  							
				54 TABLE ACCESS BY INDEX ROWID TABLE AUDT.INF_PERSON Cost: 2  Bytes: 13  Cardinality: 1  										
					53 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.INF_PERSON_PK Cost: 1  Cardinality: 1  									
				56 TABLE ACCESS BY INDEX ROWID TABLE AUDT.INF_PERSON Cost: 2  Bytes: 21  Cardinality: 1  										
					55 INDEX UNIQUE SCAN INDEX (UNIQUE) AUDT.INF_PERSON_PK Cost: 1  Cardinality: 1  									



I donot know what the plan is suggesting, please help me tune this query

[Updated on: Sun, 03 April 2011 08:54]

Report message to a moderator

Re: please help me tune this query [message #502075 is a reply to message #502030] Mon, 04 April 2011 02:12 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use the method shown below to get the explain plan and repost. Yours is really hard to read:
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 
Re: please help me tune this query [message #502088 is a reply to message #502075] Mon, 04 April 2011 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why have you got an outer-join on wf? You get the sys_audit_id from that inline view so you can only use records where a match exists in it.
You're selecting columns you don't need from the prog inline view.
Re: please help me tune this query [message #502110 is a reply to message #502088] Mon, 04 April 2011 09:10 Go to previous message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
On closer inspection you don't need the inline view wf at all. So delete it and see what happens to the performance.
Previous Topic: Query getting slowed running in Oracle 10g compared to Oracle 9i
Next Topic: Just clarification my mind about redo log file size
Goto Forum:
  


Current Time: Sun Aug 31 03:18:10 CDT 2014

Total time taken to generate the page: 0.08349 seconds