Re: Please Help DB Buffer Cache Size

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