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." -- //www.freelists.org/webpage/oracle-l