Re: reducing LIO's

Jaffar wrote

On Thu, 31 Mar 2005 17:33:18 +0300, The Human Fly <sjaffarhussain_at_gmail.com> 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
well it looks like you might get some benefit from a compound index on (ORDM_ORD_NB,ORDM_STS) - especially of ORDM_ORD_NB is unindexed.

However your query appears to be executing (on average) in 0.14s (251/1771), rather than focus on tuning the query, I'd be interested in why you are executing it so frequently, just as in life if you can avoid doing the work at all and still meet your requirements so much the better.   

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l