Thursday, December 17, 2009

Script: To Show Shared Pool Measurements


Subject:
Script: To Show Shared Pool Measurements

Note:30487.1
Type:
SCRIPT

Last Revision Date:
06-JAN-2003
Status:
PUBLISHED



Abstract
This script will show certain Shared Pool measurements.
 
Product Name, Product Version
Oracle Server, 7.3.4 to 9.2.0
Platform
Platform Independent
Date Created
14-Aug-1995
 
Instructions
Execution Environment:
     SQL, SQL*Plus
 
Access Privileges:
     Requires DBA access privileges to be executed.
 
Usage:
     sqlplus sys/
 
Instructions:
     Copy the script to a file and execute it from SQL*Plus.
 
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail 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.
 
Description
This script will show certain Shared Pool measurements.
 
References
 
 
Script
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);  
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 sum(bytes) into free_mem from v$sgastat 
        where name = 'free memory'; 
 
-- For non-MTS add up object, shared sql, cursors and 30% overhead. 
used_pool_size := round(1.3*(object_mem+shared_sql+cursor_mem)); 
  
-- For MTS add mts contribution also. 
-- used_pool_size := round(1.3*(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 ('Obj 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)) || 'M)'); 
dbms_output.put_line ('Shared pool utilization (total):  '|| 
to_char(used_pool_size) || ' bytes ' || '(' || 
to_char(round(used_pool_size/1024/1024,2)) || 'M)'); 
dbms_output.put_line ('Shared pool allocation (actual):  '|| pool_size ||' 
bytes ' || '(' || to_char(round(pool_size/1024/1024,2)) || 'M)'); 
dbms_output.put_line ('Percentage Utilized:  '||to_char 
(round(used_pool_size/pool_size*100)) || '%'); 
end;  
/
 
          
==============
Sample Output:
==============
 
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Nov 12 09:19:26 2002
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
 
SQL> @savedscript
Obj mem:  10772546 bytes
Shared sql:  8629513 bytes
Cursors:  4250 bytes
MTS session: 2079640 bytes
Free memory: 40931292 bytes (39.04M)
Shared pool utilization (total):  25228202 bytes (24.06M)
Shared pool allocation (actual):  37748736
bytes (36M)
Percentage Utilized:  67%
 
PL/SQL procedure successfully completed.


No comments:

Post a Comment