个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2022-03-29 21:05:37
貌似没啥用，先记录下来
-
set echo off
-
spool pool_est
-
/*
-
*********************************************************
-
* *
-
* title : shared pool estimation *
-
* category : information, utility *
-
* subject area : shared pool *
-
* description : 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. *
-
* note: modified to work with later versions 4/11/06 *
-
* *
-
********************************************************/
-
rem if running mts uncomment the mts calculation and output
-
rem commands.
-
-
set serveroutput on;
-
-
declare
-
object_mem number;
-
shared_sql number;
-
cursor_ovh 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
-
where type <> 'cursor';
-
-
-- 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_ovh from v$sqlarea;
-
-
select sum(sharable_mem) into cursor_mem from v$db_object_cache
-
where type='cursor';
-
-
-- 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.
-
-- for pre-9i issue
-
-- select bytes into free_mem from v$sgastat
-
-- where name = 'free memory';
-
-
-- with 9i and newer releases issue
-
select bytes into free_mem from v$sgastat
-
where name = 'free memory'
-
and pool = 'shared pool';
-
-
-
-- for non-mts add up object, shared sql, cursors and 20% overhead.
-
-- not including cursor_mem because this is included in shared_sql
-
used_pool_size := round(1.2*(object_memshared_sql));
-
-
-- for mts mts contribution needs to be included (comment out previous line)
-
-- used_pool_size := round(1.2*(object_mem shared_sql mts_mem));
-
-
-- pre-9i or if using manual sga management, issue
-
-- select value into pool_size from v$parameter where name='shared_pool_size';
-
-
-- with 9i and 10g and and automatic sga management, issue
-
select c.ksppstvl into pool_size from x$ksppi a, x$ksppcv b, x$ksppsv c
-
where a.indx = b.indx and a.indx = c.indx
-
and a.ksppinm = '__shared_pool_size';
-
-
-- display results
-
dbms_output.put_line ('obj mem: '||to_char (object_mem) || ' bytes ' || '('
-
|| to_char(round(object_mem/1024/1024,2)) || 'mb)');
-
dbms_output.put_line ('shared sql: '||to_char (shared_sql) || ' bytes ' || '('
-
|| to_char(round(shared_sql/1024/1024,2)) || 'mb)');
-
dbms_output.put_line ('cursors: '||to_char (cursor_memcursor_ovh) || ' bytes '
-
|| '('|| to_char(round((cursor_memcursor_ovh)/1024/1024,2)) || 'mb)');
-
-- 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(((pool_size-free_mem) / pool_size)*100)) || '%');
-
end;
-
/
-
- spool off
-
select shared_pool_size_for_estimate "size of shared pool in mb",
-
shared_pool_size_factor "size factor",
-
estd_lc_time_saved "time saved in sec"
- from v$shared_pool_advice;
给主人留下些什么吧！~~