Home » RDBMS Server » Performance Tuning » query optimization
query optimization [message #151542] Thu, 15 December 2005 08:01 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: query optimization [message #151563 is a reply to message #151555] Thu, 15 December 2005 09:52 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
That was a typo. Please change visibile on line 26 to visible and retest.
Previous Topic: my application is too slow.
Next Topic: when do I need to create an indexes.
Goto Forum:
  


Current Time: Mon Nov 28 05:54:14 CST 2022