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