Re: Please Help DB Buffer Cache Size

Frank ,
Which code are you talking about here ?? Neither this is a joke, nor it is developed in a offshore software shop. I was seriously seeking for help here. If you are referring to select--- from dual, that was just an example. My requirement is like this
select mno,
getnumberofshowsforperiod(mno,to_date('01012005','ddmmyyyy') ,to_date('01082005','ddmmyyyy') )
from movies where
firstshown between to_date('01012005','ddmmyyyy') and to_date('01082005','ddmmyyyy')

I got my problem fixed. The SQL Query that I was using in my Query was using an index on the date column, i changed it to use an index on the movie no column, and that worked perfect for me. My Initial result was this
Statistics

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

However, After i changed gave a hint on the index on mno column, this is the result.

Statistics

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

Major difference in query execution time.

I changed my SELECT Query in function like this

   cursor c1 is select /*+ INDEX ( s shmnoIDX ) */ count( distinct s_no) lCount from s

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

Thanks for all the help.

Regards

Brijesh Mathew Received on Wed Aug 17 2005 - 11:30:11 CDT