Tuning / SGA - db_block_buffers
db_bb.sql - Requete qui determine la modification de DB_BLOCK_BUFFERS
Doc # ********************************************************************* * Tuning: Requete qui determine la modification de DB_BLOCK_BUFFERS * Si le Hit Ratio est < 70% alors modifiions le parametre * Hit Ratio = (1 - (physical reads/(db block gets+consistent gets))*100 * Requete 1) Select simple, l'utilisateur fait le calcul * Requete 2) Select sophistique, on obtient le resultat * Requete 3) ********************************************************************* # column value format 999,999,999 column name format a33 column hitratio format 999.99 select name, value from v$sysstat where name in ('db block gets', 'consistent gets', 'physical reads') order by name / Rem Requete 2) select (1 - (sum(physical_reads)/(sum(db_block_gets)+sum(consistent_gets)))) hit_ratio from ( select value as db_block_gets, 0 as consistent_gets, 0 as physical_reads from v$sysstat where name = 'db block gets' union select 0 as db_block_gets, value as consistent_gets, 0 as physical_reads from v$sysstat where name = 'consistent gets' union select 0 as db_block_gets, 0 as consistent_gets, value as physical_reads from v$sysstat where name = 'physical reads' ) /