Re: reducing LIO's

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_at_labor.state.ny.us> 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_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
> ----------------------------------------------------------------------------
> ------------
> "It is your atittude, not your aptitude that determins your altitude."
> --
> http://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."
--
http://www.freelists.org/webpage/oracle-l