RE: reducing LIO's
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: 'Igor Neyman' <ineyman@xxxxxxxxxxxxxx>, sjaffarhussain@xxxxxxxxx, "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- Date: Thu, 31 Mar 2005 09:42:30 -0500
I agree. I reduced a very slow OLTP summary query from 3 minutes to 5
seconds using an IOT. This query was used every time a user hit their home
page.
-----Original Message-----
From: Igor Neyman [mailto:ineyman@xxxxxxxxxxxxxx]
Sent: Thursday, March 31, 2005 9:30 AM
To: sjaffarhussain@xxxxxxxxx; 'Mercadante, Thomas F'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: reducing LIO's
IOTs are not just for DSS.
They work perfectly in my OLTP system.
As always, it depends.........
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of The Human Fly
Sent: Thursday, March 31, 2005 9:24 AM
To: Mercadante, Thomas F
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
