Re: reducing LIO's

  • From: Steve Rospo <srospo@xxxxxxxxxxx>
  • To: The Human Fly <sjaffarhussain@xxxxxxxxx>
  • Date: Fri, 1 Apr 2005 10:24:53 -0800 (PST)

[ Try this again S-]


Do you have a version of the query that uses table aliases so we can see
which columns come from which tables?  What indexes are on the tables?

There's a lot of discussion of IOTs which I'm a big fan of in the
appropriate setting.  IOTs would (probably) only be advisable if the
ORDT_STS and/or ORDM_STS are part of the primary key.  A lot of time these
sorts of "flag" columns aren't part of the PK.  List partitioning might be
a better choice if that's the case.  If they are part of the PK, swap
them to the front of the PK should you try the IOT route.

> On Thu, 31 Mar 2005, The Human Fly wrote:
>
> > Here is the query and its execution plan,
> >
> >  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
> > --------------- ------------ -------------- ------ -------- --------- 
> > ----------
> > 18,305,779 1,771 10,336.4 9.4 176.37 251.04 2163204450
> > Module: JDBC Thin Client
> > BEGIN PROC_APP_OW_ORD(); END;
> >
> > 18,168,431 1,768 10,276.3 9.3 170.39 240.53 3569511138
> > Module: JDBC Thin Client
> > SELECT ORDT_APPR_DT, ORDT_ORD_NB, ORDT_TRN_NB, ORDT_MKT_ID , ORDT_STS,
> > ORDM_STS, ORDT_EXCH_ID, ORDM_SUB_ID, ORDM_ORDT_NB
> > FROM ORDT,ORDM
> > WHERE ORDM_ORD_NB=ORDT_ORD_NB
> >  AND ORDT_STS='K' AND ORDM_STS IN ('C','G','L')
> >  ORDER BY ORDT_APPR_DT,ORDT_ORD_NB

-- 
Stephen Rospo        Principal Software Architect
Vallent Corporation (formerly Watchmark-Comnitel)
Stephen.Rospo@xxxxxxxxxxx           (425)564-8145

This email may contain confidential information. If you received this in
error, please notify the sender immediately by return email and delete this
message and any attachments. Thank you.

--
//www.freelists.org/webpage/oracle-l

Other related posts: