Display database SGA statistics

DECLARE
libcac NUMBER(10,2);
rowcac NUMBER(10,2);
bufcac NUMBER(10,2);
redlog NUMBER(10,2);
spsize NUMBER;
blkbuf NUMBER;
logbuf NUMBER;
BEGIN
SELECT VALUE INTO redlog FROM v$sysstat
WHERE name = ‘redo log space requests’;
SELECT 100*(SUM(pins)-SUM(reloads))/SUM(pins) INTO libcac FROM v$librarycache;
SELECT 100*(SUM(gets)-SUM(getmisses))/SUM(gets) INTO rowcac FROM v$rowcache;
SELECT 100*(cur.VALUE + con.VALUE – phys.VALUE)/(cur.VALUE + con.VALUE) INTO bufcac
FROM v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
WHERE cur.statistic# = ncu.statistic#
AND ncu.name = ‘db block gets’
AND con.statistic# = nco.statistic#
AND nco.name = ‘consistent gets’
AND phys.statistic# = nph.statistic#
AND nph.name = ‘physical reads’;
SELECT VALUE INTO spsize FROM v$parameter WHERE name = ‘shared_pool_size’;
SELECT VALUE INTO blkbuf FROM v$parameter WHERE name = ‘db_block_buffers’;
SELECT VALUE INTO logbuf FROM v$parameter WHERE name = ‘log_buffer’;
DBMS_OUTPUT.put_line(‘> SGA CACHE STATISTICS’);
DBMS_OUTPUT.put_line(‘> ********************’);
DBMS_OUTPUT.put_line(‘> SQL Cache Hit rate = ‘||libcac);
DBMS_OUTPUT.put_line(‘> Dict Cache Hit rate = ‘||rowcac);
DBMS_OUTPUT.put_line(‘> Buffer Cache Hit rate = ‘||bufcac);
DBMS_OUTPUT.put_line(‘> Redo Log space requests = ‘||redlog);
DBMS_OUTPUT.put_line(‘> ‘);
DBMS_OUTPUT.put_line(‘> INIT.ORA SETTING’);
DBMS_OUTPUT.put_line(‘> ****************’);
DBMS_OUTPUT.put_line(‘> Shared Pool Size = ‘||spsize||’ Bytes’);
DBMS_OUTPUT.put_line(‘> DB Block Buffer = ‘||blkbuf||’ Blocks’);
DBMS_OUTPUT.put_line(‘> Log Buffer = ‘||logbuf||’ Bytes’);
DBMS_OUTPUT.put_line(‘> ‘);
IF
libcac < 99 THEN DBMS_OUTPUT.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
IF
rowcac < 85 THEN DBMS_OUTPUT.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
IF
bufcac 100 THEN DBMS_OUTPUT.put_line(‘*** HINT: Log Buffer value is rather low!’);
END IF;
END;

UNDO INFORMATION:dbms_undo_adv

Table 1
Function calls within DBMS_UNDO_ADV

Function

Description

Outputs

undo_info

Provides basic information about your undo

Tablespace name

Maximum size possible

Current retention value

If undo is auto extensible

If undo is guaranteed undo retention

longest_query

Allows you to see what the longest running query was so
you might be able to tune in relation to time

Length of the longest query

required_retention

Query this function to assist in determining what you
should set undo_retention to in order to help prevent snap-shot-too-old
errors. This value is based on the longest running query.

init.ora parameter undo_retention

best_possible_retention

Provides you with a value for undo_retention that best
fits your current undo tablespace size and usage.

init.ora parameter undo_retention

required_undo_size

Provides a value for the size of the undo tablespace to
create in order to support the current value of the init.ora parameter
undo_retention.

Undo tablespace size

undo_health

Gives descriptive output if any problems are encountered
with your current undo tablespace size or setting of the init.ora parameter
undo_retention and provides recommendations to fix.

Problem / Resolution descriptions

undo_advisor

Uses the advisor framework to give descriptive output if
any problems are encountered with your current undo configuration and
possible resolutions.

Problem / Resolution descriptions

undo_autotune

Tells you if undo auto tuning for undo retention is
enabled

TRUE / FALSE

rbu_migration

Provides the size required for undo tablespace size if you
wish to switch to automatic undo management

Size of Undo Tablespace

set serveroutput on
DECLARE
tsn VARCHAR2(40);
tss NUMBER(10);
aex BOOLEAN;
unr NUMBER(5);
rgt BOOLEAN;
retval BOOLEAN;
BEGIN
retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
dbms_output.put_line(‘Undo Tablespace Is: ‘ || tsn);
dbms_output.put_line(‘Undo Tablespace Size Is: ‘ || TO_CHAR(tss));
IF aex THEN
dbms_output.put_line(‘Undo Autoextend Is Set To: TRUE’);
ELSE
dbms_output.put_line(‘Undo Autoextend Is Set To: FALSE’);
END IF;
dbms_output.put_line(‘Undo Retention Is: ‘ || TO_CHAR(unr));
IF rgt THEN
dbms_output.put_line(‘Undo Guarantee Is Set To: TRUE’);
ELSE
dbms_output.put_line(‘Undo Guarantee Is Set To: FALSE’);
END IF;
END;
/

Undo Tablespace Is: UNDOTBS
Undo Tablespace Size Is: 28076
Undo Autoextend Is Set To: FALSE
Undo Retention Is: 43200
Undo Guarantee Is Set To: FALSE

PL/SQL procedure successfully completed.

SQL> SELECT dbms_undo_adv.longest_query(SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-1,SYSDATE)
———————————————-
45689
SQL> SELECT dbms_undo_adv.required_retention(SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-1,SYSDATE)
—————————————————
45689
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 43200
undo_tablespace string UNDOTBS

SQL> SELECT dbms_undo_adv.required_undo_size(43200, SYSDATE-1, SYSDATE)FROM dual;
DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(43200,SYSDATE-1,SYSDATE)
———————————————————
6426

SQL> set serveroutput on
DECLARE
v VARCHAR2(300);
BEGIN
v := dbms_undo_adv.undo_advisor(SYSDATE-1, SYSDATE, 1);
dbms_output.put_line(v);
END;
/
Finding 1:The undo tablespace is OK.

PL/SQL procedure successfully completed.

SQL> set serveroutput on

DECLARE
prob VARCHAR2(100);
reco VARCHAR2(100);
rtnl VARCHAR2(100);
retn PLS_INTEGER;
utbs PLS_INTEGER;
retv PLS_INTEGER;
BEGIN
retv := dbms_undo_adv.undo_health(prob, reco, rtnl, retn, utbs);
dbms_output.put_line(‘Problem: ‘ || prob);
dbms_output.put_line(‘Recmmnd: ‘ || reco);
dbms_output.put_line(‘Rationl: ‘ || rtnl);
dbms_output.put_line(‘Retentn: ‘ || TO_CHAR(retn));
dbms_output.put_line(‘UTBSize: ‘ || TO_CHAR(utbs));
END;
/

Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

undo size based on retention:
SQL> SELECT dbms_undo_adv.required_undo_size(43200)FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(43200)
—————————————
8403