ESTIMATE SHARED POOL UTILIZATION

Estimate Shared Pool Utilization

Disclaimer:

This script is provided for educational purposes only. It is NOT supported by
Oracle World Wide Technical Support. The script has been tested and appears
to work as intended. However, you should always test any script before
relying on it.

PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text
editors, email packages and operating systems handle text formatting (spaces,
tabs and carriage returns), this script may not be in an executable state when
you first receive it. Check over the script to ensure that errors of this
type are corrected.

Abstract:

This script estimates shared pool utilization based on current database
usage. This should be run during peak operation, after all stored objects
(i.e. packages, views) have been loaded.

Requirements:

SELECT privileges on V$ tables

Version Testing:

This script was tested on Oracle 7.3.4 using SVRMGRL or SQL*Plus as internal.
For Oracle 8.0.X please see Note 105004.1.
For Oracle 8.1 and above please see Note 105813.1.

Script:

———– cut ———————- cut ————– cut ————–

SET ECHO off
REM NAME: TFSSPUTL.SQL
REM USAGE:”@path/tfssputl”
REM ————————————————————————
REM REQUIREMENTS:
REM SELECT on V$ tables
REM ————————————————————————
REM PURPOSE:
REM Estimates shared pool utilization based on current database
REM usage. This should be run during peak operation, after all
REM stored objects i.e. packages, views have been loaded.
REM ————————————————————————
REM EXAMPLE:
REM Obj mem: 2536573 bytes
REM Shared sql: 4101742 bytes
REM Cursors: 2125 bytes
REM Free memory: 968976 bytes (.92MB)
REM Shared pool utilization (total): 7968528 bytes (7.6MB)
REM Shared pool allocation (actual): 9000000 bytes (8.58MB)
REM Percentage Utilized: 89%
REM
REM ————————————————————————
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ————————————————————————
REM Main text of script follows:

set echo off
spool tfssputl.lst

Rem If running MTS uncomment the mts calculation and output
Rem commands.

set serveroutput on;

declare
object_mem number;
shared_sql number;
cursor_mem number;
mts_mem number;
used_pool_size number;
free_mem number;
pool_size varchar2(512); — same as V$PARAMETER.VALUE
begin

— Stored objects (packages, views)
select sum(sharable_mem) into object_mem from v$db_object_cache;

— Shared SQL — need to have additional memory if dynamic SQL used
select sum(sharable_mem) into shared_sql from v$sqlarea;

— User Cursor Usage — run this during peak usage.
— assumes 250 bytes per open cursor, for each concurrent user.
select sum(250*users_opening) into cursor_mem from v$sqlarea;

— For a test system — get usage for one user, multiply by # users
— select (250 * value) bytes_per_user
— from v$sesstat s, v$statname n
— where s.statistic# = n.statistic#
— and n.name = ‘opened cursors current’
— and s.sid = 25; — where 25 is the sid of the process

— MTS memory needed to hold session information for shared server users
— This query computes a total for all currently logged on users (run
— during peak period). Alternatively calculate for a single user and
— multiply by # users.
select sum(value) into mts_mem from v$sesstat s, v$statname n
where s.statistic#=n.statistic#
and n.name=’session uga memory max’;

— Free (unused) memory in the SGA: gives an indication of how much memory
— is being wasted out of the total allocated.
select bytes into free_mem from v$sgastat
where name = ‘free memory’;

— For non-MTS add up object, shared sql, cursors and 20% overhead.
used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem));

— For MTS mts contribution needs to be included (comment out previous line)
— used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));

select value into pool_size from v$parameter where name=’shared_pool_size’;

— Display results
dbms_output.put_line (‘Object mem: ‘||to_char (object_mem) || ‘ bytes’);
dbms_output.put_line (‘Shared SQL: ‘||to_char (shared_sql) || ‘ bytes’);
dbms_output.put_line (‘Cursors: ‘||to_char (cursor_mem) || ‘ bytes’);
— dbms_output.put_line (‘MTS session: ‘||to_char (mts_mem) || ‘ bytes’);
dbms_output.put_line (‘Free memory: ‘||to_char (free_mem) || ‘ bytes ‘ ||
‘(‘
|| to_char(round(free_mem/1024/1024,2)) || ‘MB)’);
dbms_output.put_line (‘Shared pool utilization (total): ‘||
to_char(used_pool_size) || ‘ bytes ‘ || ‘(‘ ||
to_char(round(used_pool_size/1024/1024,2)) || ‘MB)’);
dbms_output.put_line (‘Shared pool allocation (actual): ‘|| pool_size ||’
bytes ‘ || ‘(‘ || to_char(round(pool_size/1024/1024,2)) || ‘MB)’);
dbms_output.put_line (‘Percentage Utilized: ‘||to_char
(round(used_pool_size/pool_size*100)) || ‘%’);
end;
/

spool off

———– cut ———————- cut ————– cut ————–

=========
Examples:
=========

Object mem: 2536573 bytes
Shared SQL: 4101742 bytes
Cursors: 2125 bytes
Free memory: 968976 bytes (.92MB)
Shared pool utilization (total): 7968528 bytes (7.6MB)
Shared pool allocation (actual): 9000000 bytes (8.58MB)
Percentage Utilized: 89%

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em BUFFERS, PLSQL TUNING, SGA. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s