Discussion :

[Vince7-7]

Bonjour,
En 9i r2 comment voir ce qui est mit dans la shared pool? J'ai besoin de faire des optim à ce niveau.

[13thFloor]

V$DB_OBJECT_CACHE devrait faire ton bonheur.

Au passage, un petit script qui indique l'état des différents pools :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
col "SGA POOLS"			for A25
col "MEMORY"			for A11	head ""
col "AVG"			for A6	head ""
col "#"				for A4  head ""
col "OTHER POOLS"		for A20 head ""
select 'SHARED POOL' "SGA POOLS",'-->'||SH_SZ||' M' "MEMORY",'' "AVG",
'' "#",'OTHER POOLS' "OTHER POOLS",'-->'||OP_SZ||' M' "MEMORY" from  
( select round(sum(BYTES)/1024/1024) SH_SZ 
	from v$sgastat where POOL ='shared pool'),
( select round(sum(BYTES)/1024/1024) OP_SZ 
	from v$sgastat where POOL !='shared pool' or POOL IS NULL)
UNION ALL
select '_________________________','___________','______',
       '','____________________','_________'
from DUAL
UNION ALL
----------------------------------------------------------------------
-- --SHARED_POOL ... Pick the 6 top mem allocations up :
----------------------------------------------------------------------
select SP.POOL,SP.SZ_,PCT,'|  |',B.POOL,B.SZ_ from
(select ROWNUM ROW_,POOL,PCT,
decode(sign(length(SIZE_)-7),1,round(SIZE_/1024/1024)||' M',
                                      '   '||round(SIZE_/1024)||' K' ) SZ_
 from ( select NAME POOL ,BYTES SIZE_ ,round(BYTES/SUM_*100)||'%' PCT
        from v$sgastat,
       ( select sum(BYTES) SUM_ from v$sgastat where POOL ='shared pool')      
        where POOL = 'shared pool' 
        order by 2 desc )  
where rownum < 7 ) SP,
----------------------------------------------------------------------
-- --OTHER POOLS
--
----------------------------------------------------------------------
(select ROWNUM ROW_,POOL, 
       decode(sign(length(SIZE_)-7),1,round(SIZE_/1024/1024)||' M',
       '   '||round(SIZE_/1024)||' K' )  SZ_
       from 
             (select POOL,sum(bytes) SIZE_ from v$sgastat
              where POOL is not null
              and POOL not like '%shared%'
              group by POOL
              UNION ALL 
              select NAME POOL, bytes from v$sgastat
              where POOL is  null )) B
WHERE SP.ROW_=B.ROW_ (+)
UNION ALL
select '_________________________','___________','______',
       '|  |','____________________','_________'
from DUAL
UNION ALL
----------------------------------------------------------------------
-- -- END OF THE SGA 
--
----------------------------------------------------------------------
select '','','','','','' from dual
UNION ALL
select '=========================','===========','======',
       '====','====================','=========' from dual
UNION ALL
select 'ALL UGAs AND PGAs','','','','','' from dual
UNION ALL
select 'UGA / PGA ...','MEMORY','AVG','','ESTIMATED MAX SIZE','' from dual
UNION ALL
select 	substr(a.name,9,10)||' ('||count(*)||' sessions)' "ALL UGAs AND PGAs",
	      round(sum(b.value) /1024/1024,1)||' M' "MEMORY_MB",
	decode(sign(length(round(sum(b.value)/count(*)))-7), 
			1,round(sum(b.value)/count(*)/1024/1024,1)||' M',
       			round(sum(b.value)/count(*)/1024)||' K' ) "AVG/SESSION",
	'',
        MX||' sessions (HWM)= ',
round(sum((b.value)* MX )/count(*)/1024/1024)||' M' 
from v$statname a, v$sesstat b,
(select max_utilization  MX from v$resource_limit 
        where resource_name = 'sessions')
where a.statistic# = b.statistic#
and   a.name like '%ga memory' 
group by a.name,MX 
UNION ALL
select '_________________________','___________','______',
       '____','____________________','_________'
from DUAL
;

Et un autre pour anticiper les ora-4031 (si le subpool n'est pas positionné à 1 dans une configuration > 4 cpu).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
col sga_heap format a15 
col size format a10 
select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment, 
decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K', 
4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8, 
'8-9k', 9,'9-10k','> 10K') "size", 
count(*),ksmchcls Status, sum(ksmchsiz) Bytes 
from x$ksmsp 
where KSMCHCOM = 'free memory' 
group by ksmchidx, ksmchcls, 
'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K', 
1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6, 
'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');

Cela indique toute les portions contigües de mémoires en shared pool. S'il ne reste que de petites portions, l'ora-4031 guette.

[Vince7-7]

Merci à toi