| query optimization [message #151542] |
Thu, 15 December 2005 08:01  |
Rajesh Joshi
Messages: 12
Registered: July 2002
|
Junior Member |
|
|
Hi ,
I need to optimize following query.
SELECT
TO_CHAR(reqd1.creation_date) CREATION_DATE, mproj.ref_master_project_name PROJECT_NAME, reqd1.visible_parameter15 REV_ITEM,
reqd.visible_parameter10 INJECT_PHASE, reqd.visible_parameter7 SEVERITY, reqd.visible_parameter9 TECH_CLASS,
reqd.visible_parameter8 CAUSE, reqd1.visible_parameter20 DETECTION_PHASE, reqd.visible_parameter41 DEFECT_DESC,
reqd.visible_parameter15 REWORK_EFFORT, reqd1.visible_parameter11 TYPE_OF_DEFECT_, reqd1.visible_parameter7
REV_OR_TEST_EFFORT
FROM
KCRT_REQUEST_DETAILS reqd,
KNTA_REFERENCES refr,
KCRT_REQUEST_DETAILS reqd1,
KCRT_FG_MASTER_PROJ_REF mproj,
kdrv_projects_v proj
WHERE
reqd.request_type_id in (SELECT request_type_id FROM KCRT_REQUEST_TYPES WHERE request_type_name
in ('Defect_ChildReq', 'Defect_ChildReq2.0') )
AND mproj.request_id=reqd1.request_id
AND refr.parameter1=TO_CHAR(reqd.request_id)
AND reqd1.request_type_id in (SELECT request_type_id FROM KCRT_REQUEST_TYPES
WHERE request_type_name
in ('Defect Tracker {Ver BSLQMS 1.1}', 'Defect Tracker {Ver BSLQMS 2.0}' ) )
AND reqd1.request_id=refr.source_id
AND refr.target_type_code=20
AND reqd1.batch_number=1
AND (reqd1.visible_parameter11 LIKE '%Review%' OR reqd1.visible_parameter11 LIKE '%Testing%')
AND proj.project_id=mproj.ref_master_project_id
AND proj.project_type_code='PROJECT'
AND proj.visible_user_data2='SDLC'
AND proj.parent_project_id=-1
AND proj.template_flag='N'
AND proj.project_name=(SELECT b.visible_parameter1 FROM KCRT_REQUESTs_v b
WHERE b.request_type_name='Set Default Project {Ver. BSLQMS 1.0}' AND b.created_by='30953'
AND ROWNUM<2)
ORDER BY mproj.ref_master_project_name
______________________________________________________________
Following is the explain plan
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=103 Card=1 Bytes=509
)
1 0 SORT (ORDER BY) (Cost=103 Card=1 Bytes=509)
2 1 NESTED LOOPS (Cost=101 Card=1 Bytes=509)
3 2 NESTED LOOPS (Cost=101 Card=1 Bytes=482)
4 3 MERGE JOIN (CARTESIAN) (Cost=23 Card=1 Bytes=339)
5 4 NESTED LOOPS (Cost=20 Card=1 Bytes=310)
6 5 NESTED LOOPS (Cost=20 Card=1 Bytes=306)
7 6 NESTED LOOPS (Cost=18 Card=1 Bytes=294)
8 7 NESTED LOOPS (Cost=17 Card=1 Bytes=265)
9 8 HASH JOIN (Cost=15 Card=1 Bytes=211)
10 9 NESTED LOOPS (OUTER) (Cost=5 Card=1 Byte
s=194)
11 10 NESTED LOOPS (OUTER) (Cost=4 Card=1 By
tes=190)
12 11 NESTED LOOPS (OUTER) (Cost=4 Card=1
Bytes=186)
13 12 NESTED LOOPS (OUTER) (Cost=4 Card=
1 Bytes=182)
14 13 NESTED LOOPS (OUTER) (Cost=4 Car
d=1 Bytes=180)
15 14 NESTED LOOPS (OUTER) (Cost=4 C
ard=1 Bytes=153)
16 15 NESTED LOOPS (OUTER) (Cost=4
Card=1 Bytes=126)
17 16 NESTED LOOPS (OUTER) (Cost
=4 Card=1 Bytes=99)
18 17 TABLE ACCESS (BY INDEX R
OWID) OF 'KDRV_PROJECTS' (Cost=4 Card=1 Bytes=72)
19 18 INDEX (RANGE SCAN) OF
'KDRV_PROJECTS_N1' (NON-UNIQUE) (Cost=2 Card=5)
20 18 COUNT (STOPKEY)
21 20 MERGE JOIN (OUTER) (
Cost=7 Card=1 Bytes=241)
22 21 NESTED LOOPS (OUTE
R) (Cost=7 Card=1 Bytes=237)
23 22 NESTED LOOPS (OU
TER) (Cost=5 Card=1 Bytes=227)
24 23 NESTED LOOPS (
OUTER) (Cost=5 Card=1 Bytes=223)
25 24 NESTED LOOPS
(OUTER) (Cost=5 Card=1 Bytes=219)
26 25 NESTED LOO
PS (OUTER) (Cost=5 Card=1 Bytes=215)
27 26 NESTED L
OOPS (OUTER) (Cost=5 Card=1 Bytes=211)
28 27 NESTED
LOOPS (OUTER) (Cost=5 Card=1 Bytes=207)
29 28 NEST
ED LOOPS (OUTER) (Cost=5 Card=1 Bytes=180)
30 29 NE
STED LOOPS (OUTER) (Cost=5 Card=1 Bytes=153)
31 30
NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=126)
32 31
NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=99)
33 32
NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=72)
34 33
NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=69)
35 34
NESTED LOOPS (Cost=5 Card=1 Bytes=67)
36 35
TABLE ACCESS (BY INDEX ROWID) OF 'KCRT_REQUEST_TYP
ES' (Cost=2 Card=1 Bytes=29)
37 36
INDEX (UNIQUE SCAN) OF 'KCRT_REQUEST_TYPES_U2' (
UNIQUE) (Cost=1 Card=290)
38 35
TABLE ACCESS (BY INDEX ROWID) OF 'KCRT_REQUESTS' (
Cost=5 Card=1 Bytes=38)
39 38
AND-EQUAL
40 39
INDEX (RANGE SCAN) OF 'KCRT_REQUESTS_N1' (NON-
UNIQUE) (Cost=1 Card=26)
41 39
INDEX (RANGE SCAN) OF 'KCRT_REQUESTS_N8' (NON-
UNIQUE) (Cost=1 Card=26)
42 34
INDEX (UNIQUE SCAN) OF 'KCRT_CONTACTS_U1' (UNIQUE)
43 33
INDEX (UNIQUE SCAN) OF 'KCRT_REQUEST_SUB_TYPES_U1' (UN
IQUE)
44 32
INDEX (UNIQUE SCAN) OF 'KNTA_LOOKUPS_U1' (UNIQUE)
45 31
INDEX (UNIQUE SCAN) OF 'KNTA_LOOKUPS_U1' (UNIQUE)
46 30
INDEX (UNIQUE SCAN) OF 'KNTA_LOOKUPS_U1' (UNIQUE)
47 29 IN
DEX (UNIQUE SCAN) OF 'KNTA_LOOKUPS_U1' (UNIQUE)
48 28 INDE
X (UNIQUE SCAN) OF 'KNTA_LOOKUPS_U1' (UNIQUE)
49 27 INDEX
(UNIQUE SCAN) OF 'KNTA_SECURITY_GROUPS_U1' (UNIQUE)
50 26 INDEX (U
NIQUE SCAN) OF 'KWFL_WORKFLOWS_U1' (UNIQUE)
51 25 INDEX (UNI
QUE SCAN) OF 'KCRT_STATUSES_U1' (UNIQUE)
52 24 INDEX (UNIQU
E SCAN) OF 'KNTA_USERS_U1' (UNIQUE)
53 23 INDEX (UNIQUE
SCAN) OF 'KCRT_FLS_VISIBILITY_U2' (UNIQUE)
54 22 TABLE ACCESS (BY
INDEX ROWID) OF 'KCRT_REQUEST_DETAILS' (Cost=2 Card=1 Bytes
=10)
55 54 INDEX (RANGE S
CAN) OF 'KCRT_REQUEST_DETAILS_N1' (NON-UNIQUE) (Cost=1 Card=
1)
56 21 BUFFER (SORT) (Cos
t=5 Card=1 Bytes=4)
57 56 INDEX (UNIQUE SC
AN) OF 'KNTA_USERS_U1' (UNIQUE)
58 17 INDEX (UNIQUE SCAN) OF '
KNTA_LOOKUPS_U1' (UNIQUE)
59 16 INDEX (UNIQUE SCAN) OF 'KN
TA_LOOKUPS_U1' (UNIQUE)
60 15 INDEX (UNIQUE SCAN) OF 'KNTA
_LOOKUPS_U1' (UNIQUE)
61 14 INDEX (UNIQUE SCAN) OF 'KNTA_L
OOKUPS_U1' (UNIQUE)
62 13 INDEX (UNIQUE SCAN) OF 'KDRV_STA
TES_U1' (UNIQUE)
63 12 INDEX (UNIQUE SCAN) OF 'KDRV_PROJE
CT_TEMPLATES_U1' (UNIQUE)
64 11 INDEX (UNIQUE SCAN) OF 'KCRT_STATUSE
S_U1' (UNIQUE)
65 10 INDEX (RANGE SCAN) OF 'KDRV_CRITICAL_P
ATH_TASKS_N2' (NON-UNIQUE) (Cost=1 Card=1 Bytes=4)
66 9 TABLE ACCESS (FULL) OF 'KCRT_FG_MASTER_P
ROJ_REF' (Cost=9 Card=9182 Bytes=156094)
67 8 TABLE ACCESS (BY INDEX ROWID) OF 'KCRT_REQ
UEST_DETAILS' (Cost=2 Card=1 Bytes=54)
68 67 INDEX (RANGE SCAN) OF 'KCRT_REQUEST_DETA
ILS_N1' (NON-UNIQUE) (Cost=1 Card=1)
69 7 TABLE ACCESS (BY INDEX ROWID) OF 'KCRT_REQUE
ST_TYPES' (Cost=1 Card=1 Bytes=29)
70 69 INDEX (UNIQUE SCAN) OF 'KCRT_REQUEST_TYPES
_U1' (UNIQUE)
71 6 TABLE ACCESS (BY INDEX ROWID) OF 'KNTA_REFEREN
CES' (Cost=2 Card=1 Bytes=12)
72 71 INDEX (RANGE SCAN) OF 'KNTA_REFERENCES_N3' (
NON-UNIQUE) (Cost=1 Card=2)
73 5 INDEX (UNIQUE SCAN) OF 'KDRV_PROJECTS_U1' (UNIQU
E)
74 4 BUFFER (SORT) (Cost=23 Card=2 Bytes=58)
75 74 INLIST ITERATOR
76 75 TABLE ACCESS (BY INDEX ROWID) OF 'KCRT_REQUEST
_TYPES' (Cost=3 Card=2 Bytes=58)
77 76 INDEX (RANGE SCAN) OF 'KCRT_REQUEST_TYPES_U2
' (UNIQUE) (Cost=1 Card=2)
78 3 TABLE ACCESS (BY INDEX ROWID) OF 'KCRT_REQUEST_DETAI
LS' (Cost=78 Card=1 Bytes=143)
79 78 INDEX (RANGE SCAN) OF 'KCRT_REQUEST_DETAILS_N2' (N
ON-UNIQUE) (Cost=1 Card=175)
80 2 INDEX (UNIQUE SCAN) OF 'KNTA_LOOKUPS_U1' (UNIQUE)
Pleas elet me know for any other info reqd
Thanks
Rajesh
[Updated on: Thu, 15 December 2005 08:22] by Moderator Report message to a moderator |
|
|
|
| Re: query optimization [message #151551 is a reply to message #151542] |
Thu, 15 December 2005 09:04  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Have you computed statistics recently?
How does this query perform?SELECT TO_CHAR(reqd1.creation_date) creation_date
, mproj.ref_master_project_name project_name
, reqd1.visible_parameter15 rev_item
, reqd.visible_parameter10 inject_phase
, reqd.visible_parameter7 severity
, reqd.visible_parameter9 tech_class
, reqd.visible_parameter8 cause
, reqd1.visible_parameter20 detection_phase
, reqd.visible_parameter41 defect_desc
, reqd.visible_parameter15 rework_effort
, reqd1.visible_parameter11 type_of_defect_
, reqd1.visible_parameter7 rev_or_test_effort
FROM kcrt_request_details reqd
, knta_references refr
, kcrt_request_details reqd1
, kcrt_fg_master_proj_ref mproj
, kdrv_projects_v proj
, kcrt_request_types krt_def
, kcrt_request_types krt_trk
WHERE proj.project_type_code = 'PROJECT'
AND proj.visible_user_data2 = 'SDLC'
AND proj.parent_project_id = -1
AND proj.template_flag = 'N'
AND EXISTS (SELECT NULL
FROM kcrt_requests_v krv
WHERE krv.visibile_parameter1 = proj.project_name
AND krv.request_type_name = 'Set Default Project {Ver. BSLQMS 1.0}'
AND krv.created_by = '30953')
AND proj.project_id = mproj.ref_master_project_id
AND mproj.request_id = reqd1.request_id
AND reqd1.batch_number = 1
AND 1 IN (SIGN(INSTR(reqd1.visible_parameter11,'Review'))
, SIGN(INSTR(reqd1.visible_parameter11,'Testing')))
AND reqd1.request_type_id = krt_trk.request_type_id
AND krt_trk.request_type_name IN ('Defect Tracker {Ver BSLQMS 1.1}'
, 'Defect Tracker {Ver BSLQMS 2.0}')
AND reqd1.request_id = refr.source_id
AND refr.target_type_code = 20
AND refr.parameter1 = TO_CHAR(reqd.request_id)
AND reqd.request_type_id = krt_def.request_type_id
AND krt_def.request_type_name IN ('Defect_ChildReq'
, 'Defect_ChildReq2.0')
ORDER BY mproj.ref_master_project_name
/
|
|
|
|
| Re: query optimization [message #151555 is a reply to message #151551] |
Thu, 15 December 2005 09:26 |
Rajesh Joshi
Messages: 12
Registered: July 2002
|
Junior Member |
|
|
Hi ,
Thanks for the reply .
I am getting the following error on executing the query u have sent.
ERROR at line 26:
ORA-00904: "KRV"."VISIBILE_PARAMETER1": invalid identifier
I have analyzed the table kcrt_fg_master_proj_ref which is giving the fts in the explian plan.
Regards
Rajesh
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
