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
--------------- ------------ -------------- ------ -------- --------- ----------
18,305,779 1,771 10,336.4 9.4 176.37 251.04 2163204450
Module: JDBC Thin Client
BEGIN PROC_APP_OW_ORD(); END;

18,168,431 1,768 10,276.3 9.3 170.39 240.53 3569511138
Module: JDBC Thin Client
SELECT ORDT_APPR_DT, ORDT_ORD_NB, ORDT_TRN_NB, ORDT_MKT_ID , ORDT_STS,
ORDM_STS, ORDT_EXCH_ID, ORDM_SUB_ID, ORDM_ORDT_NB
FROM ORDT,ORDM 
WHERE ORDM_ORD_NB=ORDT_ORD_NB
 AND ORDT_STS='K' AND ORDM_STS IN ('C','G','L')
 ORDER BY ORDT_APPR_DT,ORDT_ORD_NB


54 rows selected.


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@xxxxxxxxx> 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@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."
> > --
> > 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."
> 


-- 
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

Other related posts: