想知道查看oracle sga各个pool的使用情况

seedog123

想知道查看oracle sga各个pool的使用情况的sql，越全越好。谢谢啦

Maclean Liu(刘相兵

http://www.oracledatabase12g.com ... -sga-asmm-diag.html


需要脚本请去 oracledatabase12g.com 搜索， 关键词可以使 TERM+ Script  ，例如  SGA  Script

1. 
   
2. spool auto_sga_diag.log
   
3. 
   
4. set line 190 pagesize 1400
   
5. 
   
6. SELECT a.SGA_MEM + b.PGA_MEM "TOTAL_MEMORY"
   
7.     FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM"
   
8.             FROM v$sga_dynamic_components,
   
9.                  (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM"
   
10.                     FROM v$process) a
    
11.            WHERE component IN ('shared pool',
    
12.                                'large pool',
    
13.                                'java pool',
    
14.                                'streams pool',
    
15.                                'DEFAULT buffer cache')) a,
    
16.          (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM" FROM v$process) b;
    
17. 
    
18. select component,
    
19.        current_size / 1024 / 1024 "CURRENT_SIZE",
    
20.        min_size / 1024 / 1024 "MIN_SIZE",
    
21.        user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",
    
22.        last_oper_type "TYPE"
    
23.   from v$sga_dynamic_components;
    
24. 
    
25. select component, granule_size / 1024 / 1024 "GRANULE_SIZE(Mb)"
    
26.   from v$sga_dynamic_components;
    
27. 
    
28. col component for a25
    
29. col status format a10 head "Status"
    
30. col initial_size for 999,999,999,999 head "Initial"
    
31. col parameter for a25 heading "Parameter"
    
32. col final_size for 999,999,999,999 head "Final"
    
33. col changed head "Changed At"
    
34. col low format 999,999,999,999 head "Lowest"
    
35. col high format 999,999,999,999 head "Highest"
    
36. col lowMB format 999,999 head "MBytes"
    
37. col highMB format 999,999 head "MBytes"
    
38. 
    
39. SELECT component,
    
40.        parameter,
    
41.        initial_size,
    
42.        final_size,
    
43.        status,
    
44.        to_char(end_time, 'mm/dd/yyyy hh24:mi:ss') changed
    
45.   FROM v$sga_resize_ops
    
46. ORDER BY component;
    
47. 
    
48. SELECT component,
    
49.        min(final_size) low,
    
50.        (min(final_size / 1024 / 1024)) lowMB,
    
51.        max(final_size) high,
    
52.        (max(final_size / 1024 / 1024)) highMB
    
53.   FROM v$sga_resize_ops
    
54. GROUP BY component
    
55. ORDER BY component;
    
56. 
    
57. SELECT name, bytes FROM v$sgainfo;
    
58. 
    
59. select * from v$sgastat order by bytes asc;
    
60. 
    
61. select name, trunc(bytes / 1024 / 1024, 2) "size(MB)"
    
62.   from v$sgastat
    
63. where pool is null
    
64. union
    
65. select pool, trunc(sum(bytes) / 1024 / 1024, 2) "size(MB)"
    
66.   from v$sgastat
    
67. where pool is not null
    
68. group by pool;
    
69. 
    
70. select * from V$SGA_CURRENT_RESIZE_OPS;
    
71. 
    
72. select * from v$sga_target_advice;
    
73. 
    
74. show parameter size
    
75. show parameter statistics        
    
76. 
    
77. spool off;

复制代码

seedog123

好的 非常谢谢 liu

seedog123

这个sql有java pool，streams pool，shared pool，large pool的使用情况，但是没有db cache的使用情况，能不能把这个SQL扩得强大一点，加db cache，keep pool的一些使用信息？嘿嘿


select a.pool pool_name,name,trunc(a.pool_size,2)
"sum_pool_size(M)",trunc(b.Free_size,2)
"Free_size(M)",round(b.Free_size/pool_size,2) "free_rent%"
from (select pool,sum(bytes)/1024/1024 pool_size from
sys.v_$sgastat group by pool) a,
(select pool,name,sum(bytes)/1024/1024 Free_size from
sys.v_$sgastat t where t.NAME='free memory' group by pool,name) b
where a.pool=b.pool;