Tuning Experts

From: <oracledba.amit_at_gmail.com>
Date: 29 Sep 2006 00:19:42 -0700
Message-ID: <1159514381.979821.280980@k70g2000cwa.googlegroups.com>

Hi there,

I am trying to tune the following query. The database is on a remote server. The AUTOTRACE for the same is also attached.

> select mil.mil_key, mil.mil_name, act.act_name, sta.sta_status, sta.sta_bucket,
  2 sch.sch_data, obj.obj_key, obj.obj_name, obj_type, orc.orc_key, sch.sch_key,
  3 sch.sch_rowver,sch.sch_status, sch.sch_type, sch.sch_update, sch.sch_updateby, sch.sch_action,
  4 sch.sch_actual_start, sch.sch_actual_end, sch.sch_create, sch.sch_createby, sch.sch_proj_end,
  5 sch.sch_proj_start, osi.osi_assigned_to_usr_key, osi.osi_create, osi.osi_createby, osi.osi_retry_for,
  6 osi.osi_rowver, osi.osi_update, osi.osi_updateby, osi.osi_assigned_to_ugp_key, osi.osi_assign_type,   7 asgnusr.usr_key as ASSIGNEE_USER_KEY, asgnusr.usr_login as ASSIGNEE_USER_LOGIN,
  8 asgnusr.usr_first_name ASSIGNEE_FIRST_NAME, asgnusr.usr_last_name ASSIGNEE_LAST_NAME,
  9 asgnugp.ugp_key, asgnugp.ugp_name, usr.usr_login as target_user  10 from sch, sta, pkg, obj, act, mil,
 11 osi left outer join usr asgnusr on
osi.osi_assigned_to_usr_key=asgnusr.usr_key  12 left outer join ugp asgnugp on
osi.osi_assigned_to_ugp_key=asgnugp.ugp_key,  13 orc LEFT OUTER JOIN usr on orc.usr_key = usr.usr_key  14 where sch.sch_key=osi.sch_key

 15  and sta.sta_status=sch.sch_status
 16  and orc.pkg_key=pkg.pkg_key
 17  and osi.orc_key=orc.orc_key
 18  and pkg.obj_key = obj.obj_key
 19  and osi.mil_key=mil.mil_key
 20  and pkg.pkg_type='Provisioning'
 21  and act.ACT_KEY = osi.ACT_KEY
 22  and (sta.sta_bucket='Pending'
 23       or sta.sta_bucket='Rejected')
 24  and (osi.osi_assigned_to_usr_key =1
 25       or osi.osi_assigned_to_ugp_key in (1));

268363 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=30757 Card=6958 Bytes=2407468)

   1 0 HASH JOIN (Cost=30757 Card=6958 Bytes=2407468)    2 1 TABLE ACCESS (FULL) OF 'OBJ' (Cost=4 Card=1020 Bytes=32640)

   3    1     HASH JOIN (Cost=30750 Card=6958 Bytes=2184812)
   4    3       TABLE ACCESS (FULL) OF 'PKG' (Cost=4 Card=979
Bytes=20559)
   5    3       HASH JOIN (Cost=30744 Card=6961 Bytes=2039573)
   6    5         TABLE ACCESS (FULL) OF 'ACT' (Cost=2 Card=90
Bytes=900)
   7    5         HASH JOIN (Cost=30741 Card=6961 Bytes=1969963)
   8    7           TABLE ACCESS (FULL) OF 'MIL' (Cost=25 Card=13970
Bytes=391160)
   9    7           HASH JOIN (OUTER) (Cost=30695 Card=6961
Bytes=1775055)
  10    9             HASH JOIN (OUTER) (Cost=30348 Card=6961
Bytes=1573186)
  11   10               HASH JOIN (OUTER) (Cost=30314 Card=6961
Bytes=1433966)
  12   11                 HASH JOIN (Cost=29983 Card=6961
Bytes=1336512)
  13   12                   HASH JOIN (Cost=27335 Card=6961
Bytes=1239058)
  14   13                     INLIST ITERATOR
  15   14                       TABLE ACCESS (BY INDEX ROWID) OF 'STA'
(Cost=2 Card=4 Bytes=52)
  16   15                         INDEX (RANGE SCAN) OF
'IDX_STA_STA_BUCKET' (NON-UNIQUE)   (Cost=1 Card=1)
  17   13                     NESTED LOOPS (Cost=27332 Card=13290
Bytes=2192850)
  18   17                       TABLE ACCESS (BY INDEX ROWID) OF 'OSI'
(Cost=751 Card=13290 Bytes=1036620)
  19   18                         BITMAP CONVERSION (TO ROWIDS)
  20   19                           BITMAP OR
  21   20                             BITMAP CONVERSION (FROM ROWIDS)
  22   21                               INDEX (RANGE SCAN) OF
'IDX_OSI_ASSIGNED_TO_USR_KEY' (NON-UNIQUE) (Cost=8)
  23   20                             BITMAP CONVERSION (FROM ROWIDS)
  24   23                               INDEX (RANGE SCAN) OF
'IDX_OSI_ASSIGNED_TO_UGP_KEY' (NON-UNIQUE) (Cost=3)
  25   17                       TABLE ACCESS (BY INDEX ROWID) OF 'SCH'
(Cost=2 Card=1 Bytes=87)
  26   25                         INDEX (UNIQUE SCAN) OF 'PK_SCH'
(UNIQUE) (Cost=1 Card=1)
  27   12                   TABLE ACCESS (FULL) OF 'ORC' (Cost=2101
Card=1332200 Bytes=18650800)
  28   11                 TABLE ACCESS (FULL) OF 'USR' (Cost=286
Card=56506 Bytes=791084)
  29   10               TABLE ACCESS (FULL) OF 'UGP' (Cost=7 Card=6023
Bytes=120460)
  30    9             TABLE ACCESS (FULL) OF 'USR' (Cost=286 Card=56506

Bytes=1638674)

Statistics

---

          0  recursive calls
          0  db block gets
   13433819  consistent gets
     420642  physical reads
          0  redo size
   35779777  bytes sent via SQL*Net to client
     197285  bytes received via SQL*Net from client
      17892  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     268363  rows processed

Your suggestions are required and welcome. JFYI, I am experimenting with arraysize right now. Anything more than that would be a good help.

Thanking you in anticipation.

Regards,
Amit Received on Fri Sep 29 2006 - 02:19:42 CDT