1 Reply Latest reply on Aug 2, 2011 8:03 PM by Brian Bontrager

    Oracle buffer cache size

      I need to calculate buffer cache size calculation for get operation.

      SELECT o.object_name, h.status, count(*) number_of_blockes
      FROM V$BH h, DBA_OBJECTS o WHERE h.objd=o.data_object_id
      AND o.owner NOT IN('SYS','SYSTEM','SYSMAN')
      AND h.status NOT IN('free')
      GROUP BY o.object_name,h.status
      ORDER BY count(*) DESC;

      Used the above query, so i got the number of blocks used to cache data.

      I performed a get operation in one db and number of blocks noticed.
      But the problem is same operation in another db shows different number of blocks.

      Both db are same configuration.

      Anyone notices this issue??
        • 1. Re: Oracle buffer cache size
          Brian Bontrager
          Why do you expect them to be the same?

          Oracle version of each database?
          Number of objects in each database?
          Size of buffer cache in each database?

          The amount of query activity that would actually load blocks into the buffer cache in each database is not likely to be "the same".

          Identical data can take up a different number of blocks in different databases, depending on how it was loaded, transactions on that data, etc, so the number of blocks used in the buffer cache is likely to be different in different databases, even for the same data set.