【SQL杂货铺】-内存管理

SET LINESIZE 1000 PAGESIZE 1000

COLUMN component FORMAT A30

SELECT inst_id,

component,

        ROUND(current_size/1024/1024) AS current_size_mb,

        ROUND(min_size/1024/1024) AS min_size_mb,

        ROUND(max_size/1024/1024) AS max_size_mb

FROM gv$memory_dynamic_components

WHERE current_size != 0

ORDER BY component;

SET LINESIZE 1000 PAGESIZE 1000

COLUMN parameter FORMAT A25

SELECT start_time,

       end_time,

       component,

       oper_type,

       oper_mode,

       parameter,

       ROUND(initial_size/1024/1024) AS initial_size_mb,

       ROUND(target_size/1024/1024) AS target_size_mb,

       ROUND(final_size/1024/1024) AS final_size_mb,

       status

FROM v$memory_resize_ops

ORDER BY start_time;

SELECT * FROM v$memory_target_advice ORDER BY memory_size;

说明：

MEMORY_SIZE：oracle建议的内存大小

MEMORY_SIZE_FACTOR：内存基线因子，0.25, 0.5, 0.75, 1, 1.5, 1.75, and 2

ESTD_DB_TIME：当内存基线因子为1时，完成当前数据库工作量所需要的所有数据库时间（即所有用户消耗的数据库时间），这是一个建议值，它会根据memory_target参数的改变而改变

ESTD_DB_TIME_FACTOR：消耗数据库时间的比例因子

VERSION：v$memory_target_advice这个视图快照的版本号

select 'SGA' AS NAME,

       ROUND(sum(value) / 1024 / 1024, 2) || 'M' AS "SIZE(M)"

  from v$sga

union all

select 'PGA' AS NAME, ROUND(value / 1024 / 1024, 2) || 'M' AS "SIZE(M)"

  from v$pgastat

 where name = 'total PGA allocated'

union all

select 'TOTAL' AS NAME,

       ((SELECT ROUND(sum(value) / 1024 / 1024, 2) from v$sga) +

       (select ROUND(value / 1024 / 1024, 2)

           from v$pgastat

          where name = 'total PGA allocated')) || 'M' AS "SIZE(M)"

  FROM DUAL;

select p.username,p.spid,p.program,pm.category,pm.used,pm.allocated,pm.max_allocated from v$process p ,v$process_memory pm where p.pid=pm.pid and p.pid = &pid;

spool auto_sga_diag.log

set line 190 pagesize 1400

SELECT a.SGA_MEM + b.PGA_MEM "TOTAL_MEMORY"

    FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM"

            FROM v$sga_dynamic_components,

                 (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM"

                    FROM v$process) a

           WHERE component IN ('shared pool',

                               'large pool',

                               'java pool',

                               'streams pool',

                               'DEFAULT buffer cache')) a,

         (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM" FROM v$process) b;

select component,

       current_size / 1024 / 1024 "CURRENT_SIZE",

       min_size / 1024 / 1024 "MIN_SIZE",

       user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",

       last_oper_type "TYPE"

  from v$sga_dynamic_components;

select component, granule_size / 1024 / 1024 "GRANULE_SIZE(Mb)"

  from v$sga_dynamic_components;

col component for a25

col status format a10 head "Status"

col initial_size for 999,999,999,999 head "Initial"

col parameter for a25 heading "Parameter"

col final_size for 999,999,999,999 head "Final"

col changed head "Changed At"

col low format 999,999,999,999 head "Lowest"

col high format 999,999,999,999 head "Highest"

col lowMB format 999,999 head "MBytes"

col highMB format 999,999 head "MBytes"

SELECT component,

       parameter,

       initial_size,

       final_size,

       status,

       to_char(end_time, 'mm/dd/yyyy hh24:mi:ss') changed

  FROM v$sga_resize_ops

 ORDER BY component;

SELECT component,

       min(final_size) low,

       (min(final_size / 1024 / 1024)) lowMB,

       max(final_size) high,

       (max(final_size / 1024 / 1024)) highMB

  FROM v$sga_resize_ops

 GROUP BY component

 ORDER BY component;

SELECT name, bytes FROM v$sgainfo;

select * from v$sgastat order by bytes asc;

select name, trunc(bytes / 1024 / 1024, 2) "size(MB)"

  from v$sgastat

 where pool is null

union

select pool, trunc(sum(bytes) / 1024 / 1024, 2) "size(MB)"

  from v$sgastat

 where pool is not null

 group by pool;

 select * from V$SGA_CURRENT_RESIZE_OPS;

 select * from v$sga_target_advice;

show parameter size

show parameter statistics        

spool off;

--清空共享池

alter system flush shared_pool; 

--清空数据库缓冲区

alter system flush buffer_cache;  

--1、Library Cache的命中率:

计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)

SQL>SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;

通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。

--2、计算共享池内存使用率:

SQL>SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'

FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';

其中: &TSP_IN_M是你的总的共享池的SIZE(M)

共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。

--3、db buffer cache命中率:

计算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]

SQL>SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,

1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"

FROM V$BUFFER_POOL_STATISTICS

WHERE NAME='DEFAULT';

通常应在90%以上，否则，需要调整,加大DB_CACHE_SIZE

--4、数据缓冲区命中率：

SQL> select value from v$sysstat where name ='physical reads';

SQL> select value from v$sysstat where name ='physical reads direct';

SQL> select value from v$sysstat where name ='physical reads direct (lob)';

SQL> select value from v$sysstat where name ='consistent gets';

SQL> select value from v$sysstat where name = 'db block gets';

这里命中率的计算应该是

令 x = physical reads direct + physical reads direct (lob)

命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100

通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区

--5、共享池的命中率：

SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;

假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存

--6、计算在内存中排序的比率:

SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)';—查询内存排序数

SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)';—查询磁盘排序数

--caculate sort in memory ratio

SQL>SELECT round(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM dual;

此比率越大越好,太小整要考虑调整,加大PGA

--7、PGA的命中率:

计算公式:BP x 100 / (BP + EBP)

BP: bytes processed

EBP: extra bytes read/written

SQL>SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage';

--8、共享区字典缓存区命中率

计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)

命中率应大于0.85

SQL>select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;

--9、数据高速缓存区命中率

计算公式：1-(physical reads / (db block gets + consistent gets))

命中率应大于0.90最好

SQL>select name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets');

--10、共享区库缓存区命中率

计算公式：SUM(pins - reloads) / SUM(pins)

命中率应大于0.99

SQL>select sum(pins-reloads)/sum(pins) from v$librarycache;