|
|
|
![]()
| [일반] [오라클튜닝] SGA 내의 점유된 메모리 영역의 크기계산 |
|
| 호석 님께서 쓴 글입니다 - 211.♡.74.31 |
읽음:4084 |
|
|
현재 각 parameter에 의해 점유된 SGA 내의 점유된 메모리 영역의 크기는
V$SGASTAT에서 조회하여 볼 수 있다.
(select * from v$sgastat;)
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
select sum(sharable_mem) into object_mem from v$db_object_cache;
select sum(sharable_mem) into shared_sql from v$sqlarea;
select sum(250*users_opening) into cursor_mem from v$sqlarea;
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';
select sum(bytes) into free_mem from v$sgastat where name = 'free memory';
--used_pool_size := round(1.3*(object_mem + shared_sql + cursor_mem)); --NON MTS
used_pool_size := round(1.3*(object_mem + shared_sql + cursor_mem+mts_mem)); --MTS
select value into pool_size from v$parameter where name='shared_pool_size';
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))|| '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;
/
|
|
|
|
|
|
