memory.sql
Download Script
set pages 100
set feedback off
spool memory
alter session set nls_date_format = 'dd-Mon-YYyy hh24:mi:ss';
select sysdate from dual;
prompt
prompt Running Buffer Pool Statistics...
prompt =================================
prompt
column count(*) heading "Total"
prompt Types of Buffers in SGA...
prompt
select kind, count(*)
from v$cache
group by kind
order by 2 desc
/
prompt
prompt Buffers by Object Name...
prompt
select name, count(*)
from v$cache
group by name
having count(*) > 200
order by 2 desc
/
prompt
prompt
prompt Running Data Dictionary Cache Statistics...
prompt ===========================================
prompt
prompt DD cache executions vs Misses -- the hit % should
prompt be greater than 90%.
prompt If it is not then shared_pool_size should be increased.
prompt
select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Data Dictionary Misses",
100 - (sum(getmisses) * 100 / sum(gets)) "Hit %"
from v$rowcache
/
prompt
prompt
prompt Running Library Cache Statistics...
prompt ===================================
prompt
prompt Library cache executions vs Misses -- the reload ratio should
prompt be less than 1%.
prompt If it is not then shared_pool_size should be increased.
prompt
select sum(pins) "Executions",
sum(reloads) "Cache Misses while Executing",
sum(reloads) * 100 / sum(pins) "% Reloaded"
from v$librarycache
/
prompt
prompt SQL Statements with high Parse Calls...
prompt
column sql_text format a50 wrap heading "SQL Text"
column parse_calls format 999999999 heading "Parse Calls"
column executions format 999999999 heading "Executions"
select sql_text, parse_calls, executions
from v$sqlarea
where parse_calls > 100 and executions < 2 * parse_calls
/
prompt
prompt Hit Ratio for the SQL Area -- this value should be in the high 90's...
prompt
column ghr format 999.9 heading "SQL Area Hit Ratio"
select gethitratio * 100 ghr
from v$librarycache
where namespace = 'SQL AREA'
/
prompt
prompt
prompt Running Shared Pool Statistics...
prompt =================================
prompt
prompt Amount of free memory...
prompt
select * from v$sgastat where name = 'free memory';
prompt
prompt The following query shows large PL/SQL objects currently loaded in
prompt the shared pool, but not marked KEPT -- this may cause problems
prompt with efficiency of SQL execution and/or shared pool fragmentation.
prompt
column name for a40 heading "Object Name"
select name, sharable_mem "Sharable Memory"
from v$db_object_cache
where sharable_mem > 10000
and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
and kept = 'NO'
/
prompt
prompt Total SQL Statments in the Shared Pool...
prompt
select count(*) from v$sqlarea
where length(sql_text) > 64
/
prompt
prompt The following query shows duplicate SQL. This duplicate SQL needs
prompt to be eliminated by proper coding practices (eg. bind variables)
prompt
column sql for a64 heading "SQL Text"
column count(*) for 999 heading "Copies"
select substr(sql_text, 1, 64) sql,
count(*)
from v$sqlarea
where length(sql_text) > 64
group by substr(sql_text, 1, 64)
having count(*) > 9
/
prompt
prompt The following query shows large Anonymous PL/SQL Blocks. Such blocks
prompt should be wrapped in packages and then called via the package.
prompt
column sql_text for a80 heading "SQL Text"
select sql_text
from v$sqlarea
where command_type = 47
and length(sql_text) > 1000
/
prompt
prompt System Parse Rate -- Values greater than 10/second are a problem...
prompt
set serveroutput on
declare
v_prate number;
v_pcount1 number;
v_pcount2 number;
v_interval number := 60;
begin
select value into v_pcount1 from v$sysstat where name = 'parse count';
dbms_lock.sleep(v_interval);
select value into v_pcount2 from v$sysstat where name = 'parse count';
v_prate := (v_pcount2 - v_pcount1) / v_interval;
dbms_output.put_line('Parse Rate = '||v_prate);
end;
/
prompt
prompt Total Shared Pool Required...
prompt
column total format 99999999
set heading off
select 'Total - Objects', sum(sharable_mem) total1
from v$db_object_cache
union
select 'Total - SQL', sum(sharable_mem) total2
from v$sqlarea
/
set heading on
prompt
prompt Sum of per user, per cursor memory -- total
prompt
select sum(250 * users_opening) from v$sqlarea;
prompt
prompt
prompt Running Latch Statistics...
prompt ===========================
prompt
prompt Library cache latch activity -- more than 3 or 4 processes waiting
prompt could be a problem.
prompt
select count(*) number_of_waiters
from v$session_wait w, v$latch l
where w.wait_time = 0
and w.event = 'latch free'
and w.p2 = l.latch#
and l.name like 'library%'
/
prompt
prompt General wait activity that could be causing slowdowns...
prompt
column text for a30 wrap
column EVENT for a10 trunc
select SID, SEQ#, EVENT, P1TEXT||' -- '||P1||' '||P2TEXT||' -- '||P2||' '||P3TEXT||' -- '||P3 text, WAIT_TIME
from v$session_wait
where lower(event) != 'client message'
and upper(event) not like '%NET%'
and wait_time = 0
and sid > 5
/
spool off;
