- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
2#
发表于 2012-4-26 10:36:59
http://www.oracledatabase12g.com ... -sga-asmm-diag.html
需要脚本请去 oracledatabase12g.com 搜索, 关键词可以使 TERM+ Script ,例如 SGA Script-
- spool auto_sga_diag.log
- set line 190 pagesize 1400
- SELECT a.SGA_MEM + b.PGA_MEM "TOTAL_MEMORY"
- FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM"
- FROM v$sga_dynamic_components,
- (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM"
- FROM v$process) a
- WHERE component IN ('shared pool',
- 'large pool',
- 'java pool',
- 'streams pool',
- 'DEFAULT buffer cache')) a,
- (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM" FROM v$process) b;
- select component,
- current_size / 1024 / 1024 "CURRENT_SIZE",
- min_size / 1024 / 1024 "MIN_SIZE",
- user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",
- last_oper_type "TYPE"
- from v$sga_dynamic_components;
- select component, granule_size / 1024 / 1024 "GRANULE_SIZE(Mb)"
- from v$sga_dynamic_components;
- col component for a25
- col status format a10 head "Status"
- col initial_size for 999,999,999,999 head "Initial"
- col parameter for a25 heading "Parameter"
- col final_size for 999,999,999,999 head "Final"
- col changed head "Changed At"
- col low format 999,999,999,999 head "Lowest"
- col high format 999,999,999,999 head "Highest"
- col lowMB format 999,999 head "MBytes"
- col highMB format 999,999 head "MBytes"
- SELECT component,
- parameter,
- initial_size,
- final_size,
- status,
- to_char(end_time, 'mm/dd/yyyy hh24:mi:ss') changed
- FROM v$sga_resize_ops
- ORDER BY component;
- SELECT component,
- min(final_size) low,
- (min(final_size / 1024 / 1024)) lowMB,
- max(final_size) high,
- (max(final_size / 1024 / 1024)) highMB
- FROM v$sga_resize_ops
- GROUP BY component
- ORDER BY component;
- SELECT name, bytes FROM v$sgainfo;
- select * from v$sgastat order by bytes asc;
- select name, trunc(bytes / 1024 / 1024, 2) "size(MB)"
- from v$sgastat
- where pool is null
- union
- select pool, trunc(sum(bytes) / 1024 / 1024, 2) "size(MB)"
- from v$sgastat
- where pool is not null
- group by pool;
- select * from V$SGA_CURRENT_RESIZE_OPS;
- select * from v$sga_target_advice;
- show parameter size
- show parameter statistics
- spool off;
复制代码 |
|