Re: reducing LIO's
- From: Giovanni Cuccu <gcuccu@xxxxxxxxxxx>
- To: sjaffarhussain@xxxxxxxxx
- Date: Thu, 31 Mar 2005 16:32:20 +0200
Hi Jaffar,
if you goal is to reduce LIO's you have to understand where these are
generated. I assume that your query joins multiple tables, did you try
to break the query into smaller pieces in order to understand where the
majority of LIO's come from?
Perhaps once you understand the cause you can also find a solution.
Hope it helps,
Giovanni
> 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
>>
>
>
>
--
----------------------------------------
Giovanni Cuccu
Sw Engineer@xxxxxxxxxxx
Dianoema S.p.A.
Via de' Carracci 93 40131 Bologna
Tel: 051-7098211 051-4193911
e-mail:gcuccu@xxxxxxxxxxx
----------------------------------------
No man does it all by himself,
I said young man,
put your pride on the shelf
----------------------------------------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
