Home » RDBMS Server » Performance Tuning » Query Tuning (O9i)
Query Tuning [message #358193] Mon, 10 November 2008 00:59 Go to next message
manoj12
Messages: 195
Registered: March 2008
Location: India
Senior Member
Dear Sir

Dear Sir

This query is taking 3 seconds to execute it
select  distinct
           atcf.base_entity_id
         , be.entity_name_full As Cutomer_Name
         , mbf.bank_facility_name AS Account_No
         , atcf.proposed_limit AS Amount_Sanction
         , atcf.os_balance AS Amount_Outstand
         , rfdt.filter_date
         , bd.mst_borrower_state_id
    from     aud_trn_c3_fac atcf
           , rvw_cas_getchkfacdt rfdt
           , base_entity be
           , rvw_cas_stmt_borr_dtls bd
           , mst_bank_facility mbf
Where     atcf.trans_datetime = rfdt.facility_dt and
          atcf.base_entity_id = rfdt.base_entity_id and
          atcf.base_entity_id = be.id and
          atcf.id = rfdt.trn_c3_fac_id and
          rfdt.base_entity_id = bd.base_entity_id and
          rfdt.sub_process_id = bd.sub_process_id and
          rfdt.filter_date = bd.filter_date and
          rfdt.base_entity_id = be.id and
          atcf.mst_bank_facility_id = mbf.id and
          mbf.mst_facilitytype_id = 1 and
          (atcf.base_entity_id, atcf.id) not in
          (
            select distinct tss.base_entity_id, 
            tss.trn_prop_facility_id 
            from 
            rvw_cas_stmt_sanc_sec_dtls tss)
            

Please check the explain Plan

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=292 Card=1 Bytes=153
          )

   1    0   SORT (UNIQUE) (Cost=228 Card=1 Bytes=153)
   2    1     FILTER
   3    2       MERGE JOIN (Cost=226 Card=1 Bytes=153)
   4    3         SORT (JOIN) (Cost=64 Card=1 Bytes=105)
   5    4           MERGE JOIN (Cost=63 Card=1 Bytes=105)
   6    5             SORT (JOIN) (Cost=14 Card=48 Bytes=3696)
   7    6               HASH JOIN (Cost=12 Card=48 Bytes=3696)
   8    7                 HASH JOIN (Cost=7 Card=48 Bytes=2352)
   9    8                   TABLE ACCESS (BY INDEX ROWID) OF 'MST_BANK
          _FACILITY' (Cost=2 Card=29 Bytes=754)

  10    9                     INDEX (RANGE SCAN) OF 'IDX_MST_BANK_FACI
          LITY' (NON-UNIQUE) (Cost=1 Card=29)

  11    8                   TABLE ACCESS (FULL) OF 'AUD_TRN_C3_FAC' (C
          ost=4 Card=48 Bytes=1104)

  12    7                 TABLE ACCESS (FULL) OF 'BASE_ENTITY' (Cost=4
           Card=1567 Bytes=43876)

  13    5             SORT (JOIN) (Cost=49 Card=1 Bytes=28)
  14   13               VIEW OF 'RVW_CAS_GETCHKFACDT' (Cost=48 Card=1
          Bytes=28)

  15   14                 SORT (GROUP BY) (Cost=48 Card=1 Bytes=63)
  16   15                   MERGE JOIN (Cost=46 Card=1 Bytes=63)
  17   16                     SORT (JOIN) (Cost=10 Card=1 Bytes=26)
  18   17                       HASH JOIN (Cost=8 Card=1 Bytes=26)
  19   18                         TABLE ACCESS (FULL) OF 'TRN_C3_FAC'
          (Cost=3 Card=328 Bytes=2952)

  20   18                         TABLE ACCESS (FULL) OF 'AUD_TRN_C3_F
          AC' (Cost=4 Card=951 Bytes=16167)

  21   16                     FILTER
  22   21                       SORT (JOIN)
  23   22                         VIEW (Cost=44 Card=97 Bytes=3589)
  24   23                           UNION-ALL
  25   24                             SORT (UNIQUE) (Cost=30 Card=89 B
          ytes=2492)

  26   25                               FILTER
  27   26                                 TABLE ACCESS (FULL) OF 'AUD_
          TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)

  28   27                                   SORT (AGGREGATE)
  29   28                                     TABLE ACCESS (FULL) OF '
          MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

  30   26                                 TABLE ACCESS (FULL) OF 'MSTW
          F_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

  31   24                             SORT (UNIQUE) (Cost=6 Card=8 Byt
          es=176)

  32   31                               FILTER
  33   32                                 TABLE ACCESS (FULL) OF 'AUD_
          TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)

  34   33                                   SORT (AGGREGATE)
  35   34                                     TABLE ACCESS (FULL) OF '
          MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

  36   32                                 TABLE ACCESS (FULL) OF 'MSTW
          F_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

  37    3         SORT (JOIN) (Cost=162 Card=3 Bytes=144)
  38   37           VIEW OF 'RVW_CAS_STMT_BORR_DTLS' (Cost=162 Card=3
          Bytes=144)

  39   38             UNION-ALL
  40   39               SORT (UNIQUE) (Cost=56 Card=1 Bytes=51)
  41   40                 MERGE JOIN (Cost=54 Card=1 Bytes=51)
  42   41                   SORT (JOIN) (Cost=49 Card=1 Bytes=24)
  43   42                     VIEW (Cost=47 Card=1 Bytes=24)
  44   43                       SORT (GROUP BY) (Cost=47 Card=1 Bytes=
          45)

  45   44                         MERGE JOIN (Cost=44 Card=1 Bytes=45)
  46   45                           SORT (JOIN) (Cost=8 Card=4 Bytes=7
          6)

  47   46                             HASH JOIN (Cost=6 Card=4 Bytes=7
          6)

  48   47                               TABLE ACCESS (FULL) OF 'TRN_C3
          _CORPORATE' (Cost=2 Card=110 Bytes=660)

  49   47                               TABLE ACCESS (FULL) OF 'AUD_TR
          N_C3_CORPORATE' (Cost=3 Card=358 Bytes=4654)

  50   45                           FILTER
  51   50                             SORT (JOIN)
  52   51                               VIEW (Cost=44 Card=97 Bytes=25
          22)

  53   52                                 UNION-ALL
  54   53                                   SORT (UNIQUE) (Cost=30 Car
          d=89 Bytes=2492)

  55   54                                     FILTER
  56   55                                       TABLE ACCESS (FULL) OF
           'AUD_TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)

  57   56                                         SORT (AGGREGATE)
  58   57                                           TABLE ACCESS (FULL
          ) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

  59   55                                       TABLE ACCESS (FULL) OF
           'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

  60   53                                   SORT (UNIQUE) (Cost=6 Card
          =8 Bytes=176)

  61   60                                     FILTER
  62   61                                       TABLE ACCESS (FULL) OF
           'AUD_TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)

  63   62                                         SORT (AGGREGATE)
  64   63                                           TABLE ACCESS (FULL
          ) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

  65   61                                       TABLE ACCESS (FULL) OF
           'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

  66   41                   SORT (JOIN) (Cost=6 Card=358 Bytes=9666)
  67   66                     TABLE ACCESS (FULL) OF 'AUD_TRN_C3_CORPO
          RATE' (Cost=3 Card=358 Bytes=9666)

  68   39               SORT (UNIQUE) (Cost=53 Card=1 Bytes=51)
  69   68                 MERGE JOIN (Cost=51 Card=1 Bytes=51)
  70   69                   SORT (JOIN) (Cost=48 Card=3 Bytes=72)
  71   70                     VIEW (Cost=46 Card=3 Bytes=72)
  72   71                       SORT (GROUP BY) (Cost=46 Card=3 Bytes=
          135)

  73   72                         MERGE JOIN (Cost=43 Card=49 Bytes=22
          05)

  74   73                           SORT (JOIN) (Cost=7 Card=30 Bytes=
          570)

  75   74                             HASH JOIN (Cost=5 Card=30 Bytes=
          570)

  76   75                               TABLE ACCESS (FULL) OF 'TRN_C3
          _BANK' (Cost=2 Card=4 Bytes=24)

  77   75                               TABLE ACCESS (FULL) OF 'AUD_TR
          N_C3_BANK' (Cost=2 Card=68 Bytes=884)

  78   73                           FILTER
  79   78                             SORT (JOIN)
  80   79                               VIEW (Cost=44 Card=97 Bytes=25
          22)

  81   80                                 UNION-ALL
  82   81                                   SORT (UNIQUE) (Cost=30 Car
          d=89 Bytes=2492)

  83   82                                     FILTER
  84   83                                       TABLE ACCESS (FULL) OF
           'AUD_TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)

  85   84                                         SORT (AGGREGATE)
  86   85                                           TABLE ACCESS (FULL
          ) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

  87   83                                       TABLE ACCESS (FULL) OF
           'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

  88   81                                   SORT (UNIQUE) (Cost=6 Card
          =8 Bytes=176)

  89   88                                     FILTER
  90   89                                       TABLE ACCESS (FULL) OF
           'AUD_TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)

  91   90                                         SORT (AGGREGATE)
  92   91                                           TABLE ACCESS (FULL
          ) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

  93   89                                       TABLE ACCESS (FULL) OF
           'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

  94   69                   SORT (JOIN) (Cost=4 Card=68 Bytes=1836)
  95   94                     TABLE ACCESS (FULL) OF 'AUD_TRN_C3_BANK'
           (Cost=2 Card=68 Bytes=1836)

  96   39               SORT (UNIQUE) (Cost=53 Card=1 Bytes=45)
  97   96                 MERGE JOIN (Cost=51 Card=1 Bytes=45)
  98   97                   SORT (JOIN) (Cost=48 Card=3 Bytes=72)
  99   98                     VIEW (Cost=46 Card=3 Bytes=72)
 100   99                       SORT (GROUP BY) (Cost=46 Card=3 Bytes=
          135)

 101  100                         MERGE JOIN (Cost=43 Card=29 Bytes=13
          05)

 102  101                           SORT (JOIN) (Cost=7 Card=18 Bytes=
          342)

 103  102                             HASH JOIN (Cost=5 Card=18 Bytes=
          342)

 104  103                               TABLE ACCESS (FULL) OF 'TRN_C3
          _SOVEREIGN' (Cost=2 Card=3 Bytes=18)

 105  103                               TABLE ACCESS (FULL) OF 'AUD_TR
          N_C3_SOVEREIGN' (Cost=2 Card=53 Bytes=689)

 106  101                           FILTER
 107  106                             SORT (JOIN)
 108  107                               VIEW (Cost=44 Card=97 Bytes=25
          22)

 109  108                                 UNION-ALL
 110  109                                   SORT (UNIQUE) (Cost=30 Car
          d=89 Bytes=2492)

 111  110                                     FILTER
 112  111                                       TABLE ACCESS (FULL) OF
           'AUD_TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)

 113  112                                         SORT (AGGREGATE)
 114  113                                           TABLE ACCESS (FULL
          ) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

 115  111                                       TABLE ACCESS (FULL) OF
           'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

 116  109                                   SORT (UNIQUE) (Cost=6 Card
          =8 Bytes=176)

 117  116                                     FILTER
 118  117                                       TABLE ACCESS (FULL) OF
           'AUD_TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)

 119  118                                         SORT (AGGREGATE)
 120  119                                           TABLE ACCESS (FULL
          ) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

 121  117                                       TABLE ACCESS (FULL) OF
           'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

 122   97                   SORT (JOIN) (Cost=4 Card=53 Bytes=1113)
 123  122                     TABLE ACCESS (FULL) OF 'AUD_TRN_C3_SOVER
          EIGN' (Cost=2 Card=53 Bytes=1113)

 124    2       MERGE JOIN (Cost=64 Card=1 Bytes=69)
 125  124         SORT (JOIN) (Cost=60 Card=1 Bytes=50)
 126  125           VIEW (Cost=59 Card=1 Bytes=50)
 127  126             SORT (GROUP BY) (Cost=59 Card=1 Bytes=47)
 128  127               MERGE JOIN (Cost=56 Card=1 Bytes=47)
 129  128                 SORT (JOIN) (Cost=7 Card=1 Bytes=28)
 130  129                   HASH JOIN (Cost=5 Card=1 Bytes=28)
 131  130                     TABLE ACCESS (FULL) OF 'TRN_SANC_SECURIT
          Y' (Cost=2 Card=270 Bytes=2430)

 132  130                     TABLE ACCESS (FULL) OF 'AUD_TRN_SANC_SEC
          URITY' (Cost=2 Card=428 Bytes=8132)

 133  128                 FILTER
 134  133                   SORT (JOIN)
 135  134                     VIEW OF 'RVW_CAS_GETCHKFACDT' (Cost=48 C
          ard=1 Bytes=19)

 136  135                       SORT (GROUP BY) (Cost=48 Card=1 Bytes=
          63)

 137  136                         MERGE JOIN (Cost=46 Card=1 Bytes=63)
 138  137                           SORT (JOIN) (Cost=10 Card=1 Bytes=
          26)

 139  138                             HASH JOIN (Cost=8 Card=1 Bytes=2
          6)

 140  139                               TABLE ACCESS (FULL) OF 'TRN_C3
          _FAC' (Cost=3 Card=328 Bytes=2952)

 141  139                               TABLE ACCESS (FULL) OF 'AUD_TR
          N_C3_FAC' (Cost=4 Card=951 Bytes=16167)

 142  137                           FILTER
 143  142                             SORT (JOIN)
 144  143                               VIEW (Cost=44 Card=97 Bytes=35
          89)

 145  144                                 UNION-ALL
 146  145                                   SORT (UNIQUE) (Cost=30 Car
          d=89 Bytes=2492)

 147  146                                     FILTER
 148  147                                       TABLE ACCESS (FULL) OF
           'AUD_TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)

 149  148                                         SORT (AGGREGATE)
 150  149                                           TABLE ACCESS (FULL
          ) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

 151  147                                       TABLE ACCESS (FULL) OF
           'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

 152  145                                   SORT (UNIQUE) (Cost=6 Card
          =8 Bytes=176)

 153  152                                     FILTER
 154  153                                       TABLE ACCESS (FULL) OF
           'AUD_TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)

 155  154                                         SORT (AGGREGATE)
 156  155                                           TABLE ACCESS (FULL
          ) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)

 157  153                                       TABLE ACCESS (FULL) OF
           'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)

 158  124         SORT (JOIN) (Cost=4 Card=1 Bytes=19)
 159  158           TABLE ACCESS (FULL) OF 'AUD_TRN_SANC_SECURITY' (Co
          st=2 Card=1 Bytes=19)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      35867  consistent gets
      22459  physical reads
          0  redo size
       3309  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
        964  sorts (memory)
          0  sorts (disk)
         68  rows processed


Please help to tune the below query?

Regards
Re: Query Tuning [message #358201 is a reply to message #358193] Mon, 10 November 2008 01:36 Go to previous message
rleishman
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmmm. 29 lines of SQL, 159 lines of Explain Plan.

My spidey-senses detect some VIEWS nearby.

Furthermore, I see a number of UNION and SORT(GROUP BY) steps in the plan but none in the SQL. This tells me that your views are probably complex non-mergeable views.

Complex views generally cannot be efficiently joined. You can TRY to tune this SQL, but you will almost certainly FAIL.

What you need to do is to write it from first-principals using the base-tables. And I don't just mean copying the SQL of the views into your statement - that will give the same result. You have to analyse this one from scratch and get rid of redundant functionality.

Build it up table by table, tuning as you go. When you strike a problem, you will then know what caused it.

Ross Leishman
Previous Topic: Performance Issue: Steps that need to take care of
Next Topic: SESSION CPU
Goto Forum:
  


Current Time: Thu Feb 23 15:08:12 CST 2017

Total time taken to generate the page: 0.10512 seconds