1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
| set serveroutput on size 10000
set verify off
ACCEPT MTS_mode PROMPT 'MTS ou non ? (Y/N) ' DEFAULT N
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';
select used_pool_size
into used_pool_size
from (
-- For non-MTS add up object, shared sql, cursors and 20% overhead.
select round(1.2*(object_mem+shared_sql+cursor_mem)) used_pool_size
from dual
where '&MTS_mode' = 'N'
UNION
-- 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 round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem)) used_pool_size
from dual
where '&MTS_mode' = 'Y'
);
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, '999G999G999G999') || ' bytes');
dbms_output.put_line ('Shared sql : '|| to_char(shared_sql, '999G999G999G999') || ' bytes');
dbms_output.put_line ('Cursors : '|| to_char(cursor_mem, '999G999G999G999') || ' bytes');
IF '&MTS_MODE' = 'Y' THEN
dbms_output.put_line ('MTS session : '||to_char(mts_mem, '999G999G999G999') || ' bytes');
END IF;
dbms_output.put_line ('Free memory : '|| to_char(free_mem, '999G999G999G999') || ' bytes ' || '(' || to_char(round(free_mem/1024/1024,2)) || 'MB)');
dbms_output.put_line ('Shared pool utilization (total) : '|| to_char(used_pool_size, '999G999G999G999') || ' bytes ' || '(' || to_char(round(used_pool_size/1024/1024,2)) || 'MB)');
dbms_output.put_line ('Shared pool allocation (actual) : '|| to_char(to_number(pool_size), '999G999G999G999') ||' 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;
/ |
[Oracle 8i][Internet] Shared Pool Size
Répondre avec citation
) et vérifie ensuite que ces problèmes ne sont plus rencontrés...
, mais peut etre qu'une estimation est possible ?
Envoyé par orafrance
Cette procédure donne une estimation de l'allocation de la mémoire partagées pour Oracle, ainsi que son utilisation. 
Partager