| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: reducing LIO's
Jafar,
A couple of things -
Why are you doing a full table scan of the ORDM table? Is there an index on the ORDM.ORDM_ORD_NB column? There must not be because you are scanning that table first and then going to the ORDT table via the ORDT_ORDNB index. Of these two tables, which one has the most number of rows?
Consider adding the ORDT_STS column to the 'ORDT_ORDNB' index.
And what Niall said. Explore why you are executing this so often. Again, you might be better off with a Materialized view of this data so that the join is eliminated.
Tom
-----Original Message-----
From: The Human Fly [mailto:sjaffarhussain_at_gmail.com]
Sent: Thursday, March 31, 2005 9:33 AM
To: Mercadante, Thomas F
Cc: oracle-l_at_freelists.org
Subject: Re: reducing LIO's
Here is the query and its execution plan,
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- ---------
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6304 Card=2072 Bytes=70448)
1 0 SORT (ORDER BY) (Cost=6304 Card=2072 Bytes=70448) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ORDT' (Cost=3 Card=1 Bytes=22)
3 2 NESTED LOOPS (Cost=6245 Card=2072 Bytes=70448) 4 3 TABLE ACCESS (FULL) OF 'ORDM' (Cost=125 Card=2040 Bytes=24480) 5 3 INDEX (RANGE SCAN) OF 'ORDT_ORDNB' (NON-UNIQUE)(Cost=2 Card=2)
Statistics
0 recursive calls
0 db block gets
6959 consistent gets ===>
0 physical reads
0 redo size
2283 bytes sent via SQL*Net to client
297 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
54 rows processed
It I use USE_HASH hint to force the query to use HASH joing, then there is a big diference in consistent gets.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12808 Card=2072 Bytes=70448)
1 0 SORT (ORDER BY) (Cost=12808 Card=2072 Bytes=70448)
2 1 HASH JOIN (Cost=12749 Card=2072 Bytes=70448) 3 2 TABLE ACCESS (FULL) OF 'ORDM' (Cost=125 Card=2040 Bytes=24480) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'ORDT' (Cost=12620 Card=243584 Bytes=5358848) 5 4 INDEX (RANGE SCAN) OF 'ORDT_STS' (NON-UNIQUE)(Cost=224 Card=2)
Statistics
0 recursive calls
0 db block gets
10411 consistent gets =======>
0 physical reads
0 redo size
2283 bytes sent via SQL*Net to client
297 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
54 rows processed
On Thu, 31 Mar 2005 17:24:02 +0300, The Human Fly
<sjaffarhussain_at_gmail.com> wrote:
> 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
> >
-- 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-lReceived on Thu Mar 31 2005 - 09:59:40 CST
 |
 |