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;
/

-- ****************************************************
-- shared_pool size and suggestion if needed
-- ****************************************************
-- 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;
/

-- ****************************************************
-- 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;
/

-- ****************************************************
-- 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_Num5      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 effeciency areas to be examined are SQL AREA,
TABLE/PROCEDURE,');
DBMS_OUTPUT.PUT_LINE(
' BODY, and TRIGGER.');
DBMS_OUTPUT.PUT_LINE('   ');

select round(sum(gethits)/sum(gets) * 100,0) into v_Num1 from
v$librarycache
where namespace = 'SQL AREA';
select round(sum(gethits)/sum(gets) * 100,0) into v_Num2 from
v$librarycache
where namespace = 'TABLE/PROCEDURE';
select round(sum(gethits)/sum(gets) * 100,0) into v_Num3 from
v$librarycache
where namespace = 'BODY';
select round(decode(sum(gethits),0,0)/decode(sum(gets),0,1) *
100,0) into v_Num4
from v$librarycache where namespace = 'TRIGGER';

select 0 into v_num4 from dual where v_num4 is null;
select decode(v_num4,0,3,4) into v_num5 from dual;

DBMS_OUTPUT.PUT_LINE(
'Running at '||(v_num1+v_num2+v_num3+v_num4)/v_num5||
'% overall Library Cache effeciency.');

DBMS_OUTPUT.PUT_LINE('Running at '||v_num1||'% sql area
effeciency.');
if v_num1 < 95 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 effeciency.');
if v_num2 < 95 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 effeciency.');
if v_num3 < 95 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 effeciency.');
if v_num4 < 95 and v_num4 > 0 then
DBMS_OUTPUT.PUT_LINE(
'** Consider increasing the shared_pool and/or open cursors.');
end if;

select sum(reloads) - (sum(pins)*.01) into v_Num1 from
v$librarycache
where namespace = 'SQL AREA';
select sum(reloads) - (sum(pins)*.01) into v_Num2 from
v$librarycache
where namespace = 'TABLE/PROCEDURE';
select sum(reloads) - (sum(pins)*.01) into v_Num3 from
v$librarycache
where namespace = 'BODY';
select sum(reloads) - (sum(pins)*.01) into v_Num4 from
v$librarycache
where namespace = 'TRIGGER';

if v_num1 > 0 then
DBMS_OUTPUT.PUT_LINE(
'** SQL AREA reloads > 1% of pins -- increase shared_pool.');
end if;

if v_num2 > 0 then
DBMS_OUTPUT.PUT_LINE(
'** TABLE/PROCEDURE reloads > 1% of pins -- increase
shared_pool.');
end if;

if v_num3 > 0 then
DBMS_OUTPUT.PUT_LINE(
'** BODY reloads > 1% of pins -- increase shared_pool.');
end if;

if v_num4 > 0 then
DBMS_OUTPUT.PUT_LINE(
'** TRIGGER reloads > 1% of pins -- increase shared_pool.');
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
effeciency.');

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;
/

-- ****************************************************
--  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;
/

-- ****************************************************
-- 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;
/

-- ****************************************************
-- 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;
/

-- ****************************************************
-- 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;
/

-- ****************************************************
-- db block buffer effeciency 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
effeciency.');

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;
/

-- ****************************************************
-- PGA tuning as opposed to SGA but affects performance.
-- ****************************************************

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 count(*)
into v_num1
from v$sesstat a, v$sesstat b,
v$sysstat c, v$sysstat d
where c.name = 'session cursor cache hits'
and d.name ='parse count'
and a.sid = b.sid
and a.statistic# = d.statistic#
and (b.statistic# =c.statistic#
and b.value > 0);

if v_num1 > 0 then
DBMS_OUTPUT.PUT_LINE(
'** Examine session cursor cache hits and parse counts');
DBMS_OUTPUT.PUT_LINE(
'closely and consider setting SESSION_CACHED_CURSORS.');
end if;

exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('** Currently no data to select from.');

end;
/