[SSRS][Oracle]Memory Usage Report

        接著分享Memory Usage Report

1.左上的

SELECT substr(name, 0, 512), ((substr(value, 0, 512))/1024/1024) value

FROM v$system_parameter2

WHERE name = 'memory_max_target' OR name = 'memory_target' ORDER BY name;

SELECT substr(name, 0, 512), ((substr(value, 0, 512))/1024/1024) value

FROM v$system_parameter2

WHERE name = 'sga_max_size' OR name = 'sga_target';

SELECT substr(name, 0, 512), ((substr(value, 0, 512))/1024/1024) value

FROM v$system_parameter2

WHERE name = 'pga_aggregate_target'

union all

select name, value/1024/1024 from v$pgastat where name = 'maximum PGA allocated';

2.右上的Hit Rates

select 'Buffer Nowait' Name, ROUND((1-(a.value/b.value))*100,2) VALUE

from v$sysstat b,(select sum(count) value from v$waitstat) a

where b.name ='session logical reads'

UNION ALL

select  'Redo NoWait', ROUND ((1-(a.value / b.value))*100,2)

from V$SYSSTAT a, V$SYSSTAT b

where a.name = 'redo log space requests'

and b.name = 'redo entries'

UNION ALL

SELECT 'Buffer Hit' NAME,

ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE)

* 100

/ (congets.VALUE + dbgets.VALUE),

2

) VALUE

FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads

WHERE congets.NAME = 'consistent gets'

AND dbgets.NAME = 'db block gets'

AND physreads.NAME = 'physical reads'

UNION ALL

SELECT 'Memory Sort',

ROUND ( ms.VALUE

/ DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))

* 100,

2

)

FROM v$sysstat ds, v$sysstat ms

WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'

UNION ALL

select 'Library Cache', round(100*(1-(sum(reloads)/sum(pins))),2)

from v$librarycache

UNION ALL

select 'Soft Parse', ROUND ((1-(a.value / b.value))*100 ,2)

from V$SYSSTAT a, V$SYSSTAT b

where a.name = 'parse count (hard)'

and b.name = 'parse count (total)'

UNION ALL

select 'Execute to Parse', ROUND ((1-(a.value / b.value))*100 ,2)

from V$SYSSTAT a, V$SYSSTAT b

where b.name = 'execute count'

and a.name = 'parse count (total)'

UNION ALL

SELECT 'Latch Hit Ratio', ROUND ((1 - (Sum(misses) / Sum(gets))) * 100 ,2)

FROM   v$latch

UNION ALL

select 'Parse CPU to Parse Elapsd', ROUND (((a.value / b.value)*100) ,2)

from V$SYSSTAT a, V$SYSSTAT b

where a.name = 'parse time cpu'

and b.name = 'parse time elapsed'

UNION ALL

select 'Non-Parse CPU', ROUND ((1-(a.value / b.value))*100 ,2)

from V$SYSSTAT a, V$SYSSTAT b

where a.name = 'parse time cpu'

and b.name = 'CPU used by this session';

3.左下的SGA

 select

  replace(name,'Size','') "NAME",

  round((bytes/1024/1024),2) "MB"

from v$sgainfo

where name in

  ('Buffer Cache Size',

  'Fixed SGA Size',

  'Large Pool Size',

  'Streams Pool Size'

  ,'Java Pool Size'

  ,'Shared Pool Size'

  ,'Redo Buffers');

4.右下的Shared Pool

with cte as (

SELECT pool,name, bytes/(1024*1024) MB

FROM   v$sgastat

WHERE  pool = 'shared pool' AND

       name IN ('free memory', 'row cache','SQLA')

union all

SELECT pool,'miscellaneous', sum(bytes)/(1024*1024) MB

FROM   v$sgastat

WHERE  pool = 'shared pool' AND

       name not IN ('free memory', 'row cache','SQLA')

group by pool

)

select

    (case name

        when 'free memory' then 'Free memory'

        when 'row cache' then 'Dictionary cache'

        when 'SQLA' then 'SQL area'

        when 'miscellaneous' then 'Miscellaneous'

    end) name,

    MB

from cte;

5.左上SGA旁邊還有個Sga Target Advice可以點進去

select SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME_FACTOR, ESTD_PHYSICAL_READS from V$SGA_TARGET_ADVICE;

Sample檔在此