Trace/tkprof results from MAXUATB were:

·         Original = 25,510 blocks read

·         New query = 2,539 blocks read = 10 times less than original

·         New query plus new index = 1,621 blocks read = 16 times less than original

 

1) Rewrite query:

 

select * from maximo.workorder

where istask = 0

and historyflag = 0

and woclass = 'WORKORDER'

and status != 'WOUT'

and ( (workorder.ownergroup, workorder.siteid) in (

  select pgt.persongroup , pgt.useforsite

  from maximo.persongroupteam pgt inner join maximo.maxuser mu on (pgt.resppartygroup = mu.personid)

  where mu.userid = 'SE602203'

  and pgt.useforsite is not null)

or workorder.ownergroup in (

  select pgt.persongroup from maximo.persongroupteam pgt inner join maximo.maxuser mu on (pgt.resppartygroup = mu.personid)

  where mu.userid = 'SE602203'

  and pgt.useforsite is null)

or workorder.owner in (

    select mu.personid from maximo.maxuser mu where mu.userid = 'SE602203')

)

/

 

To:

 

select * from maximo.workorder

where istask = 0

and historyflag = 0

and woclass = 'WORKORDER'

and status != 'WOUT'

and (workorder.ownergroup, workorder.siteid) in (

  select pgt.persongroup , pgt.useforsite

  from maximo.persongroupteam pgt inner join maximo.maxuser mu on (pgt.resppartygroup = mu.personid)

  where mu.userid = 'SE602203'

  and pgt.useforsite is not null)

union

select * from maximo.workorder

where istask = 0

and historyflag = 0

and woclass = 'WORKORDER'

and status != 'WOUT'

and workorder.ownergroup in (

  select pgt.persongroup from maximo.persongroupteam pgt inner join maximo.maxuser mu on (pgt.resppartygroup = mu.personid)

  where mu.userid = 'SE602203'

  and pgt.useforsite is null)

union

select * from maximo.workorder

where istask = 0

and historyflag = 0

and woclass = 'WORKORDER'

and status != 'WOUT'

and workorder.owner in (

    select mu.personid from maximo.maxuser mu where mu.userid = 'SE602203')

/

 

2) And create a new index, with:

 

create index af31 on maximo.workorder ( woclass, istask, historyflag, ownergroup, siteid ) compute statistics ;

 

or, alternatively, with very slightly more reads (1646 instead of 1621):

 

create index af5 on maximo.workorder ( owner, woclass, istask, historyflag ) compute statistics ;

 

3) Buffer Cache should be made larger also – maybe by setting sga_target to several gb? There is 16gb of physical memory on the server, only 5gb of which is currently used by oracle in total.

 

Andrew Fraser 5619.