rem PL/SQL SGA ANALYSIS -- Provided to Fuller Theological Seminary
rem by Danette Miller of Sunguard/SCT
set echo off
/* SGA Size */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'The SGA is the core memory of a computer dedicated to Oracle. Generally, ');
DBMS_OUTPUT.PUT_LINE(
'the larger the SGA the better, but the SGA must stay within core, i.e. it');
DBMS_OUTPUT.PUT_LINE(
'must not get paged by the operating system. Typically the SGA can occupy ');
DBMS_OUTPUT.PUT_LINE(
'up to 1/2 of the on system memory. The size of the SGA is determined ');
DBMS_OUTPUT.PUT_LINE(
'mainly by the init.ora parameters of shared_pool and db_block_buffers.');
DBMS_OUTPUT.PUT_LINE(' ');
select round(sum(bytes)/1024/1024,0) into v_Num1 from v$sgastat;
DBMS_OUTPUT.PUT_LINE('Currently your SGA size is about '||v_Num1||
' Megabytes.');
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* sga size estimate */
-- Using the following formula, make the optimal shared pool
-- size calculation based on current users.
-- optimal size in meg = 25 + (3 * number of gui users)+
-- (6 * number of charmod users)
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
select round(value/1024/1024,0)into v_num1 from v$parameter
where name ='shared_pool_size';
select machine into v_str1 from v$session where sid=1;
select count(*) into v_num2 from v$session
where username is not null and machine = v_str1;
select count(*) into v_num3 from v$session
where username is not null and machine <> v_str1;
v_num4 := 25 + (3 * v_num3)+ (6 * v_num2-1);
DBMS_OUTPUT.PUT_LINE('The shared_pool is currently about '||v_num1||' meg.');
IF v_num1 < v_num4 then
DBMS_OUTPUT.PUT_LINE(
'** Based on current users, the shared_pool should be at least '||
v_num4||' meg.' );
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* db block buffer efficiency ratio */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'DB Block Buffer efficiency is a percentage indicating a data row did not');
DBMS_OUTPUT.PUT_LINE(
'need to be reloaded because it was already in the buffer.');
DBMS_OUTPUT.PUT_LINE(' ');
select round((1-(pr.value/(bg.value+cg.value)))*100,0)
into v_Num1
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name = 'physical reads'
and bg.name = 'db block gets'
and cg.name = 'consistent gets';
DBMS_OUTPUT.PUT_LINE('Running at '||v_num1||'% DB Block efficiency.');
if v_num1 < 95 then
DBMS_OUTPUT.PUT_LINE(
'** This is <= 95% so db_block_buffers should be increased.');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* Dictionary Cache Efficiency ratio */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'Dictionary Cache Efficiency is a percentage reflecting that object');
DBMS_OUTPUT.PUT_LINE(
'information did not need to be reloaded because it was already in the ');
DBMS_OUTPUT.PUT_LINE(
'dictionay cache.');
DBMS_OUTPUT.PUT_LINE(' ');
select round(sum(gets)/(sum(gets)+sum(getmisses)) * 100,0)
into v_Num1
from v$rowcache;
DBMS_OUTPUT.PUT_LINE('Running at '||v_num1||'% Dictionary Cache efficiency.');
if v_num1 < 90 then
DBMS_OUTPUT.PUT_LINE('** Consider increasing the shared_pool.');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* Library Cache Efficiency */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'Library Cache Efficiency is a percentage that reflect a SQL statement did');
DBMS_OUTPUT.PUT_LINE(
'not need to be reloaded -parsed- because it was already in the library');
DBMS_OUTPUT.PUT_LINE(
'cache. The efficiency areas to be examined are SQL AREA, TABLE/PROCEDURE,');
DBMS_OUTPUT.PUT_LINE(
' BODY, and TRIGGER.');
DBMS_OUTPUT.PUT_LINE(' ');
select round(sum(pinhits)/sum(pins) * 100,0) into v_Num1 from v$librarycache
where namespace = 'SQL AREA';
select round(sum(pinhits)/sum(pins) * 100,0) into v_Num2 from v$librarycache
where namespace = 'TABLE/PROCEDURE';
select round(sum(pinhits)/sum(pins) * 100,0) into v_Num3 from v$librarycache
where namespace = 'BODY';
select round(sum(pinhits)/decode(sum(pins),0,1) * 100,0) into v_Num4
from v$librarycache where namespace = 'TRIGGER';
DBMS_OUTPUT.PUT_LINE(
'Running at '||(v_num1+v_num2+v_num3+v_num4)/4||
'% overall Library Cache efficiency.');
DBMS_OUTPUT.PUT_LINE('Running at '||v_num1||'% sql area efficiency.');
if v_num1 < 99 then
DBMS_OUTPUT.PUT_LINE(
'** Consider increasing the shared_pool and/or open cursors.');
end if;
DBMS_OUTPUT.PUT_LINE(
'Running at '||v_num2||'% table/procedure efficiency.');
if v_num2 < 99 then
DBMS_OUTPUT.PUT_LINE(
'** Consider increasing the shared_pool and/or open cursors.');
end if;
DBMS_OUTPUT.PUT_LINE(
'Running at '||v_num3||'% body efficiency.');
if v_num3 < 99 then
DBMS_OUTPUT.PUT_LINE(
'** Consider increasing the shared_pool and/or open cursors.');
end if;
DBMS_OUTPUT.PUT_LINE(
'Running at '||v_num4||'% trigger efficiency.');
if v_num4 < 99 then
DBMS_OUTPUT.PUT_LINE(
'** Consider increasing the shared_pool and/or open cursors.');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* Recursive Calls */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'The total number of recursive calls (Oracle issued SQL statements). May ');
DBMS_OUTPUT.PUT_LINE(
'show dynamic extension of tables or rollback segments. May be caused by:');
DBMS_OUTPUT.PUT_LINE(
'misses in the data dictionary cache, data base triggers, stored procedures,');
DBMS_OUTPUT.PUT_LINE(
'functions, packages, anonymous PL/SQL blocks,DDL statements, enforcement');
DBMS_OUTPUT.PUT_LINE(
'of referential integrity constraints.');
DBMS_OUTPUT.PUT_LINE(' ');
select value into v_Num1 from v$sysstat where name = 'recursive calls';
DBMS_OUTPUT.PUT_LINE(
'Current number of recursive calls is '||v_num1||'.');
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* Redo Log Buffer Contention */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'The number of times a user process waited for redo log buffer space. Should');
DBMS_OUTPUT.PUT_LINE(
'be near 0.');
DBMS_OUTPUT.PUT_LINE(' ');
select value into v_num1
from v$sysstat
where name = 'redo log space wait time';
DBMS_OUTPUT.PUT_LINE('Currently have '||v_num1||' redo log buffer space waits.');
select value into v_num2 from v$parameter where name = 'log_buffer';
if v_num1 > 5 then
DBMS_OUTPUT.PUT_LINE(
'** Consider raising the LOG_BUFFER from '||v_num2||' to '||v_num2*1.05||'.');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* Rollback Segment Contention */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'The percentage that a request for data resulted in a wait for a rollback ');
DBMS_OUTPUT.PUT_LINE(
'segment.');
DBMS_OUTPUT.PUT_LINE(' ');
select round(sum(waits)/sum(gets),0)
into v_Num1
from v$rollstat;
DBMS_OUTPUT.PUT_LINE(
'There is '||v_num1||'% rollback segment contention.');
IF v_num1 > 1 then
DBMS_OUTPUT.PUT_LINE(
'** Consider creating more rollback segments.');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* Shared Pool Reload Count */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'The number of times a SQL statement or procedure has been reparsed or');
DBMS_OUTPUT.PUT_LINE(
'reloaded because of a lack of memory. ');
DBMS_OUTPUT.PUT_LINE(' ');
select sum(reloads)into v_Num1 from v$librarycache
where namespace in (
'SQL AREA','TABLE/PROCEDURE', 'BODY','TRIGGER');
DBMS_OUTPUT.PUT_LINE(
'Currently there is a '||v_num1||' shared pool reload count.');
if v_num1 > 49 then
DBMS_OUTPUT.PUT_LINE('** Consider increasing shared_pool or open_cursors.');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE(
'Currently no data to select from.');
end;
/
/* Shared Pool Reload Ratio */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'The shared pool reload ration is the percentage of SQL statement executions');
DBMS_OUTPUT.PUT_LINE(
'that result in a SQL statement reload.');
DBMS_OUTPUT.PUT_LINE(' ');
select round(sum(reloads) / sum(pins) * 100,0) into v_Num1 from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');
DBMS_OUTPUT.PUT_LINE(
'Running with '||v_num1||'% shared pool reload.');
if v_num1 > 1 then
DBMS_OUTPUT.PUT_LINE(
'** Consider increasing shared_pool or open_cursors.');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* Sort Area Efficiency */
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'Sort Area Efficiency is the percentage of sorts performed in memory as');
DBMS_OUTPUT.PUT_LINE(
'opposed to sorts performed in temporary segments on disk.');
DBMS_OUTPUT.PUT_LINE(' ');
select round((sum(decode(name, 'sorts (memory)', value, 0))
/ (sum(decode(name, 'sorts (memory)', value, 0))
+ sum(decode(name, 'sorts (disk)', value, 0))))
* 100,0) into v_Num1 from v$sysstat;
DBMS_OUTPUT.PUT_LINE(
'Running with '||v_num1||'% sort area efficiency.');
if v_num1 < 90 then
DBMS_OUTPUT.PUT_LINE(
'** Consider increasing sort_area_size and setting sort_area_retained_size.');
end if;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently no data to select from.');
end;
/
/* Redo Log Allocation Latch Contention */
/* starting points to consider --
LOG_SMALL_ENTRY_MAX_SIZE = 5k
LOG_ENTRY_PREBUILD_THRESHOLD = 5k
*/
SET SERVEROUTPUT ON;
DECLARE
/* Declare variables to be used in this block. */
no_users NUMBER :=0;
v_Num1 NUMBER;
v_Num2 NUMBER;
v_Num3 NUMBER;
v_Num4 NUMBER;
v_str1 varchar2(25);
v_OutputStr VARCHAR2(500);
BEGIN
-- Enable the buffer first.
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(
'Redo Log Allocation Latch Contention is the percentage of time that a');
DBMS_OUTPUT.PUT_LINE(
'process attempted to acquire a redo log latch held by another process. ');
DBMS_OUTPUT.PUT_LINE(' ');
select value into v_num1 from v$parameter where name = 'cpu_count';
select value into v_num2 from v$parameter
where name = 'log_simultaneous_copies';
select round(greatest((sum(decode(ln.name, 'redo copy', misses,0))
/ greatest(sum(decode(ln.name, 'redo copy', gets,0)),1)),
(sum(decode(ln.name, 'redo allocation', misses,0))
/ greatest(sum(decode(ln.name, 'redo allocation', gets,0)),1)),
(sum(decode(ln.name, 'redo copy', immediate_misses,0))
/ greatest(sum(decode(ln.name, 'redo copy', immediate_gets,0))
+ sum(decode(ln.name, 'redo copy', immediate_misses,0)),1)),
(sum(decode(ln.name, 'redo allocation', immediate_misses,0))
/ greatest(sum(decode(ln.name, 'redo allocation', immediate_gets,0))
+ sum(decode(ln.name, 'redo allocation', immediate_misses,0)),1)))* 100,0)
into v_num3 from v$latch l, v$latchname ln where l.latch# = ln.latch#;
DBMS_OUTPUT.PUT_LINE(
'Running with '||v_num3||'% Redo Log Allocation Latch Contention.');
if v_num3 > 1 then
if v_num1 > 1 then
if v_num2 < v_num1 * 2 then
DBMS_OUTPUT.PUT_LINE(
'** Consider setting the LOG_SIMULTANEOUS_COPIES to '||v_num1*2||'.');
end if;
DBMS_OUTPUT.PUT_LINE(
'** Consider setting the LOG_ENTRY_PREBUILD_THRESHOLD.');
end if;
if v_num2 > 0 then
DBMS_OUTPUT.PUT_LINE(
'** Consider setting the LOG_SMALL_ENTRY_MAX_SIZE.');
end if;
end if;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('This completes the SGA tuning assessment.');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Currently have no redo log buffer space waits.');
end;
/