| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning Experts
oracledba.amit_at_gmail.com wrote:
> 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
I did not perform a full analysis of the autotrace, but here is what I
see after a brief analysis:
First, reformating the SQL statement:
SELECT
MIL.MIL_KEY, MIL.MIL_NAME, ACT.ACT_NAME, STA.STA_STATUS, STA.STA_BUCKET, SCH.SCH_DATA, OBJ.OBJ_KEY, OBJ.OBJ_NAME,
ORC.ORC_KEY, SCH.SCH_KEY, SCH.SCH_ROWVER, SCH.SCH_STATUS, SCH.SCH_TYPE, SCH.SCH_UPDATE, SCH.SCH_UPDATEBY, SCH.SCH_ACTION, SCH.SCH_ACTUAL_START, SCH.SCH_ACTUAL_END, SCH.SCH_CREATE, SCH.SCH_CREATEBY, SCH.SCH_PROJ_END, SCH.SCH_PROJ_START, OSI.OSI_ASSIGNED_TO_USR_KEY, OSI.OSI_CREATE, OSI.OSI_CREATEBY, OSI.OSI_RETRY_FOR, OSI.OSI_ROWVER, OSI.OSI_UPDATE, OSI.OSI_UPDATEBY, OSI.OSI_ASSIGNED_TO_UGP_KEY, OSI.OSI_ASSIGN_TYPE, ASGNUSR.USR_KEY AS ASSIGNEE_USER_KEY, ASGNUSR.USR_LOGIN AS ASSIGNEE_USER_LOGIN, ASGNUSR.USR_FIRST_NAME ASSIGNEE_FIRST_NAME, ASGNUSR.USR_LAST_NAME ASSIGNEE_LAST_NAME,ASGNUGP.UGP_KEY,
SCH, STA, PKG, OBJ, ACT, MIL, OSI,
AND STA.STA_STATUS=SCH.SCH_STATUS
AND ORC.PKG_KEY=PKG.PKG_KEY
AND OSI.ORC_KEY=ORC.ORC_KEY
AND PKG.OBJ_KEY = OBJ.OBJ_KEY
AND OSI.MIL_KEY=MIL.MIL_KEY
AND PKG.PKG_TYPE='Provisioning'
AND ACT.ACT_KEY = OSI.ACT_KEY
AND (STA.STA_BUCKET='Pending'
OR STA.STA_BUCKET='Rejected')
AND (OSI.OSI_ASSIGNED_TO_USR_KEY =1
OR OSI.OSI_ASSIGNED_TO_UGP_KEY IN (1))
AND OSI.OSI_ASSIGNED_TO_USR_KEY=ASGNUSR.USR_KEY(+)
AND OSI.OSI_ASSIGNED_TO_UGP_KEY=ASGNUGP.UGP_KEY(+)
AND ORC.USR_KEY = USR.USR_KEY(+);
You are performing a full table scan of the following (bytes):
32,640 OBJ
20,559 PKG
900 ACT
391,160 MIL 18,650,800 ORC (Cost=2101) 791,084 USR 120,460 UGP 1,638,674 USR
You are apparently accessing few indexes (bytes):
52 ACCESS BY INDEX ROWID STA
1,036,620 ACCESS BY INDEX ROWID OSI
87 ACCESS BY INDEX ROWID SCH
13,433,819 blocks were read from the buffer cache
420,642 blocks were read from disk
Does the database have a 2KB block size?
35,779,777 bytes were sent to the client in 17,892 round trips in order to send 2,407,468 bytes to the client. You stated that the database is on a remote server. If the remote link has an average latency of 50ms, it would take 14.4 minutes just to send 1 byte of data across the link 17,892 times. Take a close look at how you can decrease the number of round trips.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Fri Sep 29 2006 - 07:07:42 CDT
 |
 |