Display DB Cache Advice and Cloud Computing

Introduction

Over the last 10 to 15 years, with the onset of cheap memory, fast SSD disks and large disk caches, I rarely find myself checking the cache hit ratio. Its not uncommon to find servers with 256G as a minimum and very fast disks. As such, in most deployments, I find the cache hit ratio views have become mostly redundant, along with the associated scripts.

However, I recently had a scenario which provided a new and useful way in which this script helped immensely. That scenario was cloud computing. Many vendors offer Database as a Service on pre-configured machines. These machines are generally provisioned as factors of a base machine. So, you start with machine X. Then if you outgrow that machine you move to machine 2X. Machine 2X, will have 2 times the CPU, 2 times the Memory, 2 times the Network etc. Then if you outgrow 2X, you move to a 4X machine which has 4 times the resources of the base machine and so on.

The scenario I found myself in was a cloud deployment where several database systems were running 24x7 at no more than 12% CPU on quite large machines. 4 times the base machine to be precise. This begged the question, could there be a cost saving made by going down to a 2X or even an X machine?

In theory, this would see the CPU usage increase from 12% to 24% to 48% as the machine size got smaller. But what about the memory? This is where the buffer cache advice view came in very useful as it estimated how the buffer cache would be affected as the memory of the buffer cache decreased. Based on the view we were able to determine that we could reduce the machine from 4X to 2X. A considerable saving given the costs for such machines work on the same factor, for example, Machine X cost Y dollars and machine 2X costs 2 x Y dollars.

--
-- Display DB Cache Advice in Readable Format
--
-- Note that a + number in the PCT columns is the pct increase.
-- A - number in the PCT columns is the pct decrease.
--
 
col SIZE_FOR_ESTIMATE head SIZE_FOR|EST_MB
col SIZE_FACTOR head SIZE|FACTOR format 99.99
col ESTD_PHYSICAL_READ_FACTOR head EST_PHY|READ_FACT format 99.99
col ESTD_PCT_OF_DB_TIME_FOR_READS head ESTD_PCT|DB_TIME
 
set linesize 132
select SIZE_FOR_ESTIMATE as "Cache Size in MB"
      ,SIZE_FACTOR * 100 as "Pct of Current Buffer Cache"
      ,floor((ESTD_PHYSICAL_READ_FACTOR * 100)-100) as "Pct Increase/Decrease in Physical Reads"
      ,ESTD_PCT_OF_DB_TIME_FOR_READS as "Pct Increase/Decrease in Time of Physical Reads"
from V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
   AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
   AND advice_status = 'ON'
order by 1
/

Related Scripts

Published 19th January 2023

advicecachesgasqltuningv$db_cache_advice
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License