Re: reducing LIO's

  • From: The Human Fly <sjaffarhussain@xxxxxxxxx>
  • To: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
  • Date: Thu, 31 Mar 2005 17:24:02 +0300

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

Other related posts: