Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: reducing LIO's

Re: reducing LIO's

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 31 Mar 2005 15:46:00 +0100
Message-ID: <7765c897050331064628655477@mail.gmail.com>


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
Received on Thu Mar 31 2005 - 09:49:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US