Maybe because you started using the Index and the table in your query where, before, you were just using the FTS of the table. The real question is *not* the LIO count, but the response time. Did it reduce the end-users response time? Remember, all of our diagnostics tools don't mean spit if the response time is not better. -----Original Message----- From: The Human Fly [mailto:sjaffarhussain@xxxxxxxxx] Sent: Thursday, March 31, 2005 9:56 AM To: niall.litchfield@xxxxxxxxx Cc: Mercadante, Thomas F; oracle-l@xxxxxxxxxxxxx Subject: Re: reducing LIO's Well, I have done it. I have created combination index on those two colums and when my query was not using the index, I have even forced it to use, it reduces the cost but buffer_get were very high. My question was, LIO can be reduced by creating indexs, if so, why my buffer_gets were higher with INDEX hint? Well, we can't avoid this query as it is mandatory query for every trading transaction. On Thu, 31 Mar 2005 15:46:00 +0100, Niall Litchfield <niall.litchfield@xxxxxxxxx> wrote: > Jaffar wrote > > On Thu, 31 Mar 2005 17:33:18 +0300, The Human Fly > <sjaffarhussain@xxxxxxxxx> 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 > -- > //www.freelists.org/webpage/oracle-l > -- Best Regards, Jaffar, OCP DBA Banque Saudi Fransi Saudi Arabia ---------------------------------------------------------------------------- ------------ "It is your atittude, not your aptitude that determins your altitude." -- //www.freelists.org/webpage/oracle-l