| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: reducing LIO's
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_at_gmail.com]
Sent: Thursday, March 31, 2005 9:13 AM
To: oracle-l_at_freelists.org
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
 |
 |