Re: Please Help DB Buffer Cache Size

From: <brijeshmathew_at_gmail.com>
Date: 16 Aug 2005 23:53:45 -0700
Message-ID: <1124261625.639969.173280@g49g2000cwa.googlegroups.com>

Mladen Gogala wrote:
> On Tue, 16 Aug 2005 09:48:49 -0700, brijeshmathew wrote:
>
> > My current buffer size is 140 MB. I am having performance issues. That
> > is the reason I am doing this.
>
> So, are you having performance issues with the buffer cache or an
> application? What is slow? Buffer cache or an application? How
> do you the discern buffer cache being slow from buffer cache being fast?
> Personally, I am inclined to shoot the buffer cache with S&W .44" on
> the first sign of movement, regardless of the speed. Either that or to
> check into Betty Ford for detox.
> I usually tune the applications, because they have users who can tell
> me about the response time, response time can be measured, I can see what
> is the application waiting on, where is it spending time, how much CPU
> time does it consume and the other, entirely unimportant, details.
>
> --
> http://www.mgogala.com

The whole reason why I asked whether I should look into increasing my buffer cache size is, I was doubtful about my approach, and I needed some help from you experts.
Some of my queries are becoming slow, as the data in database is increasing. I shall explain one of the functions that shows a real slow down is below.

---

CREATE or replace FUNCTION getnumberofshowsforPeriod(lmno IN NUMBER, fromDate In Date, toDate In Date)

   RETURN NUMBER is
   cursor c1 is select count( distinct s_no) lCount from s

        where mno = lmno and valuedate between fromDate and toDate group by valuedate , tid;

   lNoOfShows Number(5);

   BEGIN

	lNoOfShows := 0 ;
        for lcur in c1 loop
           lNoOfShows  := lNoOfShows + lCur.lCount;
        end loop;

      	RETURN(lNoOfShows);

    END;

---

This function gets the Number of shows for a particular movie, for a period.
Now, say I am trying to fetch the no of shows for each movie shown since 01-01-2004, there are 500 distinct movies (mno), and in a loop, I try to call this function, for these 500 movies. This whole process takes over a minute.
For this specified period, in my transaction table (s) , there are 500000 rows.

The structure for table (S) is

sale_date
show_date
tid
mno
s_no
paymenttype
sales
tickts

Could there be a better approach to this function. ? Can this query be fine tuned. ?
This is the plan explained for the SQL in my function

      COST CARDINALITY QUERY_PLAN

• -----------

  ---

  80 1 SELECT STATEMENT

        80 1 2.1 SORT GROUP BY

                           3.1 FILTER

        53           1       4.1 TABLE ACCESS BY INDEX ROWID SHOWS

                               5.1 BITMAP CONVERSION TO ROWIDS

                                 6.1 BITMAP AND

                                   7.1 BITMAP CONVERSION FROM ROWIDS

         6           1               8.1 INDEX RANGE SCAN SHOWMNOIDX
NON-UNIQU
                       E



                                   7.2 BITMAP CONVERSION FROM ROWIDS


      COST CARDINALITY QUERY_PLAN

• -----------

  ---

  8.1 SORT ORDER BY

        42           1                 9.1 INDEX RANGE SCAN
SHOWSVALUEDATEIDX
                       NON-UNIQUE

11 rows selected.

If i Do
select getnumberofshowsforperiod( &lmno
,to_date('01012005','ddmmyyyy') ,to_date('01082005','ddmmyyyy') ) from dual

Statistics

---

        366  recursive calls
          7  db block gets
       5987  consistent gets
       4135  physical reads
          0  redo size
        469  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
          1  rows processed

Thanks for all your help

Brijesh Mathew Received on Wed Aug 17 2005 - 01:53:45 CDT