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_at_gmail.com> wrote:
> 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
>
--
Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia
----------------------------------------------------------------------------------------
"It is your atittude, not your aptitude that determins your altitude."
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 31 2005 - 10:00:20 CST
