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

On 29 Sep 2006 00:19:42 -0700, "oracledba.amit@gmail.com"
<oracledba.amit@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.

What is your problem?
What is your goal?

Jaap.

On 29.09.2006 10:38, Jaap W. van Dijk wrote:
> On 29 Sep 2006 00:19:42 -0700, "oracledba.amit@gmail.com"
> <oracledba.amit@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.
>
> What is your problem?
> What is your goal?

More questions: what is the Oracle version? If 10, what did Oracle suggest?

robert

Hi Amit,

Nice that you have posted query and execution plan.
Some points I would like to say are
1) If possible can we remove Left JOINs??
2) You are using IN to compare single value. Try to use = there.
3) replace Or conditions by using 'Union ALL' .

I hope these helps.

With Warm regards
Jatinder Singh

oracledba.amit@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,
OBJ_TYPE,
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,
ASGNUGP.UGP_NAME,
USR.USR_LOGIN AS TARGET_USER
FROM
SCH,
STA,
PKG,
OBJ,
ACT,
MIL,
OSI,
USR ASGNUSR,
UGP ASGNUGP,
ORC
WHERE
SCH.SCH_KEY=OSI.SCH_KEY
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 couple outer joins, verify that an outer join is
necessary.
* OSI.OSI_ASSIGNED_TO_UGP_KEY IN (1) could be written as
OSI.OSI_ASSIGNED_TO_UGP_KEY=1
* STA.STA_BUCKET='Pending' OR STA.STA_BUCKET='Rejected' could be
rewritten as STA.STA_BUCKET IN ('Pending','Rejected') - this may or
may not help performance
* Review the WHERE clause, are you removing enough rows from each table
before performing a join?

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
---------------
21,646,277 TOTAL BYTES

(Side note: cost of scanning 18.5MB seems a little low)

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.

Hi Robert,

Thankyou for your response.

The Oracle Version is 9.2.0.6

Regards,
Amit

Robert Klemme wrote:
> On 29.09.2006 10:38, Jaap W. van Dijk wrote:
> > On 29 Sep 2006 00:19:42 -0700, "oracledba.amit@gmail.com"
> > <oracledba.amit@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.
> >
> > What is your problem?
> > What is your goal?
>
> More questions: what is the Oracle version? If 10, what did Oracle suggest?
>
> robert