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;
/ |
Erreur ORA-04031 sans issu
Répondre avec citation
Envoyé par ilalaina
Partager