Jafar, A couple of things - Why are you doing a full table scan of the ORDM table? Is there an index on the ORDM.ORDM_ORD_NB column? There must not be because you are scanning that table first and then going to the ORDT table via the ORDT_ORDNB index. Of these two tables, which one has the most number of rows? Consider adding the ORDT_STS column to the 'ORDT_ORDNB' index. And what Niall said. Explore why you are executing this so often. Again, you might be better off with a Materialized view of this data so that the join is eliminated. Tom -----Original Message----- From: The Human Fly [mailto:sjaffarhussain@xxxxxxxxx] Sent: Thursday, March 31, 2005 9:33 AM To: Mercadante, Thomas F Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: reducing LIO's 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 54 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6304 Card=2072 Bytes=70448) 1 0 SORT (ORDER BY) (Cost=6304 Card=2072 Bytes=70448) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORDT' (Cost=3 Card=1 Bytes=22) 3 2 NESTED LOOPS (Cost=6245 Card=2072 Bytes=70448) 4 3 TABLE ACCESS (FULL) OF 'ORDM' (Cost=125 Card=2040 Bytes=24480) 5 3 INDEX (RANGE SCAN) OF 'ORDT_ORDNB' (NON-UNIQUE) (Cost=2 Card=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6959 consistent gets ===> 0 physical reads 0 redo size 2283 bytes sent via SQL*Net to client 297 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 54 rows processed It I use USE_HASH hint to force the query to use HASH joing, then there is a big diference in consistent gets. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12808 Card=2072 Bytes=70448) 1 0 SORT (ORDER BY) (Cost=12808 Card=2072 Bytes=70448) 2 1 HASH JOIN (Cost=12749 Card=2072 Bytes=70448) 3 2 TABLE ACCESS (FULL) OF 'ORDM' (Cost=125 Card=2040 Bytes=24480) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'ORDT' (Cost=12620 Card=243584 Bytes=5358848) 5 4 INDEX (RANGE SCAN) OF 'ORDT_STS' (NON-UNIQUE) (Cost=224 Card=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 10411 consistent gets =======> 0 physical reads 0 redo size 2283 bytes sent via SQL*Net to client 297 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 54 rows processed On Thu, 31 Mar 2005 17:24:02 +0300, The Human Fly <sjaffarhussain@xxxxxxxxx> wrote: > well, > > The query is very simple, it looks, and I dont think re-writing the > code will do something for me. However, IOT and MV probably used for > DSS I believe. Our system is purly OLTP with 24x7. Its a trading > application. By the way, it doesn't have any groups functions. > > > On Thu, 31 Mar 2005 09:19:50 -0500, Mercadante, Thomas F > <thomas.mercadante@xxxxxxxxxxxxxxxxx> wrote: > > Jaffar, > > > > There are a lot of things you can do to make this faster. > > > > Have you looked at IOT tables? - Basically an index that contains all of the > > data that you need from your query. > > Have you looked at Materialized views (summary tables) and have Query > > Rewrite take over to reduce IO's? > > > > If you provide more information (like the query and the table structures) we > > could help you better. > > > > Tom > > > > -----Original Message----- > > From: The Human Fly [mailto:sjaffarhussain@xxxxxxxxx] > > Sent: Thursday, March 31, 2005 9:13 AM > > To: oracle-l@xxxxxxxxxxxxx > > Subject: reducing LIO's > > > > Hello list, > > > > We have one query in our trading application thats executed 1770 times > > in just 20 mintues of time and around 490 users were connected, > > bascially, this query runs for every tranaction. The problem with this > > query is, it has a lot of buffers gets and using too much cpu, and > > when we approch oracle support they ask us to reduce buffers gets, > > which I understand. > > I have done some bench marking, like, I have created one combination > > index and I have forced index hint to use this query. When I force > > hint to use newly created index, it reduces cost 50%, but, when I look > > at buffers gets, it was more than the previous one. > > Is creating index is the way to reduce LIO? If so, when my buffer gets > > or more when using index? > > > > my query is having order by and joing of two tables with two columns. > > server has 9 cpus > > buffer cache size is 1600MB > > OS : AIX > > Oracle 9i > > > > -- > > 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 > > > > -- > Best Regards, > Jaffar, OCP DBA > Banque Saudi Fransi > Saudi Arabia > ---------------------------------------------------------------------------- ------------ > "It is your atittude, not your aptitude that determins your altitude." > -- 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