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

Home -> Community -> Usenet -> c.d.o.server -> Re: Please Help DB Buffer Cache Size

Re: Please Help DB Buffer Cache Size

From: <brijeshmathew_at_gmail.com>
Date: 17 Aug 2005 01:48:49 -0700
Message-ID: <1124268529.244477.188140@g49g2000cwa.googlegroups.com>

Mladen Gogala wrote:
> On Tue, 16 Aug 2005 23:53:45 -0700, brijeshmathew wrote:
>
>
> >
> > ---------------------
> > 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.
>
> For this, there are things called analytical functions. It will execute
> an order of magnitude faster then this concoction.
>
>
> >
> > 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
> >
>
> So, where is the time spent? What events are you waiting on? Where is
> the execution profile (10046, lev 12)? That statistics tells me that you
> spent 5987 consistent gets and 5987 physical reads to send just 469 bytes
> to the client. That looks a bit wasteful. Also, you had a single execution
> of the statement and 366 recursive calls, which is a result of PL/SQL
> function. Also, you had a sort which swapped things to the disk. I bet
> that increasing sort_area_size or pga_aggregate_target would be more
> helpful then increasing the size of buffer cache.
>
>
> --
> http://www.mgogala.com

Hi Mladen

I did increase my sort_area_size, to 10 Mb, and I have gained a lot of performance difference in lot of reports, however, this particuar case, and two more functions that I use, I am not able to get any major gain from increasing the buffer cache and the sort area size. I havent changed the pga_aggregate_target.
SQL> show parameter pga_aggregate_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
pga_aggregate_target                 big integer 0
Is there something else I have to look into here ?

The time in my case is spent in the execution of the function that i showed you.
If you see, that particular statistics that I have shown is for 1 (mno). When that function is called for 500 movies(mno)(when a report is for a period of one year or more), it takes quite long time to return the values.
Mladen, I havent worked on Analytical functions in Oracle.

Brijesh Mathew Received on Wed Aug 17 2005 - 03:48:49 CDT

Original text of this message

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