Re: Please Help DB Buffer Cache Size

From: Brijesh <brijeshmathew_at_gmail.com>
Date: 18 Aug 2005 06:31:21 -0700
Message-ID: <1124371881.881078.249510@f14g2000cwb.googlegroups.com>

Frank
If you this is how you suggest to change my function, instead of using a cursor construct.. ?
select /*+ INDEX ( a SHOWMNOIDX)*/ sum( count( distinct show_no) ) into lNoOfShows from income.shows a where mno = lmno and valuedate between fromdate and toDate

group by valuedate , theatre_id;
Then the statistics will be ,, for the same query that i gave above

Statistics

        410  recursive calls
          2  db block gets
      18960  consistent gets
          0  physical reads
          0  redo size
       5163  bytes sent via SQL*Net to client
       1813  bytes received via SQL*Net from client
         12  SQL*Net roundtrips to/from client
        177  sorts (memory)
          0  sorts (disk)
        164  rows processed

I have tried this before I changed the function to work with a cursor construct.. I see that there are more consistent gets when it is changed to a Single Statement, and more Sorts. I do not see a way to implement this Query using Count() and Max()., however I certainly undestand your whole point.

Regards
Brijesh Received on Thu Aug 18 2005 - 08:31:21 CDT