Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: reducing LIO's

RE: reducing LIO's

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Thu, 31 Mar 2005 09:42:30 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0704AC03DB@exchsen0a1ma>


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_at_perceptron.com] Sent: Thursday, March 31, 2005 9:30 AM
To: sjaffarhussain_at_gmail.com; 'Mercadante, Thomas F' Cc: oracle-l_at_freelists.org
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_at_perceptron.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of The Human Fly Sent: Thursday, March 31, 2005 9:24 AM
To: Mercadante, Thomas F
Cc: oracle-l_at_freelists.org
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_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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 31 2005 - 09:46:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US