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  |
miroconnect@yahoo.com
Messages: 202
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  |
cookiemonster
Messages: 13958
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 |
cookiemonster
Messages: 13958
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 20:32:18 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
