Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 11G R2 关于 AMM 只是构想，实际用的时候会越界的 ...
发新帖
lingzhiluoye

0

积分

0

好友

8

主题

[Exadata常规管理] 11G R2 关于 AMM 只是构想，实际用的时候会越界的

1#
发表于 2012-12-5 14:09:46 | 查看: 6315| 回复: 12
今天早上oracle 11g R2  双节点RAC 中的RAC-2 节点时 告警日志报：

Wed Dec 05 09:02:09 2012
Thread 2 advanced to log sequence 4639 (LGWR switch)
  Current log# 3 seq# 4639 mem# 0: +DGDATA/db4qigou/onlinelog/group_3.265.756882319
  Current log# 3 seq# 4639 mem# 1: +DGFLASH/db4qigou/onlinelog/group_3.259.756882319
Wed Dec 05 09:02:10 2012
Archived Log entry 10225 added for thread 2 sequence 4638 ID 0x3909c46d dest 1:
Wed Dec 05 09:05:49 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:11:49 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:17:52 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:24:19 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:54:24 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:00:24 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:11:22 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:17:22 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
"alert_DB4QIGOU_2.log" 27930L, 1534179C                                      27837,1       99%
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 08:04:18 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 08:10:19 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 08:16:19 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 08:22:19 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 08:47:48 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 08:53:48 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 08:59:48 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:02:09 2012
Thread 2 advanced to log sequence 4639 (LGWR switch)
  Current log# 3 seq# 4639 mem# 0: +DGDATA/db4qigou/onlinelog/group_3.265.756882319
  Current log# 3 seq# 4639 mem# 1: +DGFLASH/db4qigou/onlinelog/group_3.259.756882319
Wed Dec 05 09:02:10 2012
Archived Log entry 10225 added for thread 2 sequence 4638 ID 0x3909c46d dest 1:
Wed Dec 05 09:05:49 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:11:49 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:42:23 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:48:24 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 09:54:24 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:00:24 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:11:22 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:17:22 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:23:22 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:29:23 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:35:23 2012
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)
Wed Dec 05 10:40:51 2012
Thread 2 advanced to log sequence 4640 (LGWR switch)
  Current log# 4 seq# 4640 mem# 0: +DGDATA/db4qigou/onlinelog/group_4.266.756882319
  Current log# 4 seq# 4640 mem# 1: +DGFLASH/db4qigou/onlinelog/group_4.260.756882319

.......................................................................................................................

上网查阅资料 说： AMM管理只是个构想，实际使用中，可能会越界

Oracle 11g memory_target parameter
Oracle Tips by Burleson Consulting

This short article examines the new PGA memory management in Oracle 11g, using the new memory_target and memory_max_target parameters.  

Prior to Oracle 11g, the DBA set the sga_target and sga_max_size parameters, allowing Oracle to reallocate RAM within the SGA.  The PGA was independent, as governed by the pga_aggregate_target parameter.

Now in Oracle 11g we see the memory_max_target parameter which governs the total maximum RAM for both the PGA and SGA regions and the new memory_target parameter which governs the existing sizes.  This allows RAM to be de-allocated from the SGA and transferred to the PGA.

Note:  When using AMM (by setting memory_target, and/or sga_target, the values for the 搕raditional� pool parameters (db_cache_size, shared_pool_size, &c) are not ignored.  Rather, they will specify the minimum size that Oracle will always maintain for each sub-area in the SGA.

This is an important Oracle11g new feature because it lays the foundation for inter-instance RAM memory sharing.
We are seeing the second age of mainframe computing, and server consolidation where it's not uncommon to find a dozen instances on a single large server.

As of Oracle 10g, Automatic Memory Management (AMM) only allows shifting of RAM within the confines of sga_max_size.  Now in Oracle 11g, we see this new Automatic Shared Memory Management (ASMM).  The memory_target parameter is dynamic (changeable with "alter system" commands), whereby RAM can be de-allocated from an instances SGA/PGA and re-allocated to another instance.

The Oracle 11g documentation notes that the memory_target parameter specifies the system-wide RAM and explains that the memory_max_target=memory_target if you do not explicitly set memory_max_target.

The docs also note that the memory_max_target specifies the maximum amount of RAM that you would allocate for all of Oracle, all SGA and PGA regions combined.

Issues with memory_target in 11g - Steve Karam

I was working on an 11g client yesterday.  Their settings were:
memory_target = 16G
sga_target = 0
db_cache_size = 0
shared_pool_size = 0
etc.

For whatever reason, Oracle would not allocate over 64 MB for the buffer cache.  We tried to set the minimum by doing:
alter system set db_cache_size = 6G

But we received this error:
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
Pretty weird, right?  

So we set memory_target = 0, which set the sga_target = 640MB and kept all the other pools at 0.  Then we set the sga_target to 0, and were finally allowed to use more memory for the buffer cache.

So even though the new AMM parameter was set very high, Oracle was still going by the rules of the underlying sga_target parameter, which was incorrect.

I wanted to pass this around so you all know that memory_target in 11g is buggy.  For that matter, sga_target has always seemed buggy as well with the way it can harm system performance.

这篇文章 出自这里 ： http://www.dba-oracle.com/oracle11g/oracle_11g_memory_target_parameter.htm
..................................................................................................................................................................................

------RAC 节点 2 的情况  -------


SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 7296M
memory_target                        big integer 7296M
shared_memory_address                integer     0

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 7296M
sga_target                           big integer 0
SQL>
SQL>
SQL> Select count(*) from v$session where status='ACTIVE';

  COUNT(*)
----------
        43
2#
发表于 2012-12-6 10:34:42
Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1.0 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms
The following message is reported in the alert log:

WARNING: Granules of pga_aggregate_target %d cannot be more than memory_target (%d) - sga_target (%d) or min_sga(%d)

Changes
The MEMORY_TARGET and SGA_MAX_SIZE or SGA_TARGET parameters are set.
Cause
This warning message is raised because the MEMORY_TARGET/MEMORY_MAX_TARGET and SGA_MAX_SIZE/SGA_TARGET parameters are set the same.  However, in Oracle 11g the MEMORY_TARGET parameter defines the total amount of memory that Oracle can use, which incorporates both SGA and PGA areas.  Therefore if these are set the same as the SGA_MAX_SIZE/SGA_TARGET parameters then this leaves no memory available for the PGA, and hence this warning message is raised.

Solution
Ensure that the MEMORY_TARGET/MEMORY_MAX_TARGET parameter settings are set to at least the sum of SGA_MAX_SIZE/SGA_TARGET plus the PGA_AGGREGATE_TARGET parameter.

回复 只看该作者 道具 举报

3#
发表于 2012-12-6 10:36:44
SQL> show parameter memory_max_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 7296M
SQL>
SQL> show parameter memory_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 7296M
SQL>
SQL> show parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 7296M
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0
SQL>
SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0

回复 只看该作者 道具 举报

4#
发表于 2012-12-6 10:37:46
答案出自于 陶陶  16117057

回复 只看该作者 道具 举报

5#
发表于 2012-12-6 13:32:44
说版本 号 至少精确到 第四位！！
  1. Applies to:

  3. Oracle Server - Enterprise Edition - Version 11.1.0.6 to 11.2.0.1 [Release 11.1 to 11.2]
  4. Information in this document applies to any platform.
  5. ***Checked for relevance on 17-APR-2012***
  6. Symptoms

  8. Alert.log file displays the message :
  9. WARNING: Granules of pga_aggregate_target 102 cannot be more than memory_target (104) - sga_target (0) or min_sga (5)

  11. The following Note has already been reviewed:

  13. Note 970274.1 WARNING: GRANULES OF PGA_AGGREGATE_TARGET CANNOT BE MORE THAN MEMORY_TARGET

  15. and the current database parameters were:
  16. sga_target = 0
  17. memory_target = 8G
  18. memory_max_target = 8G
  19. pga_aggregate_target = 0

  21. Cause

  23. Bug 9242054 , which is closed as a duplicate of unpublished Bug 8813366, has been opened to address this issue.

  25. It was found that, from the message:
  26. WARNING: Granules of pga_aggregate_target 124 cannot be more than memory_target (128) - sga_target (0) or min_sga (5)

  28. granule size is 64MB due to memory_target = 8192M
  29. total number of granules is 128 (8192M / 64MB = 128)

  31. And 5 granules are allocated for min_sga and hence you keep seeing message like 124 for pga_aggregate_target > 128 and so forth.

  33. In order to determine the number of granules at a certain time, you may check the warning message and the output of

  35. set lines 100
  36. col end_time format a16 head "Time Stamp"
  37. col init format 999,999,999 head "Initial (MB)"
  38. col target format 999,999,999 head "Target (MB)"
  39. col final format 999,999,999 head "Final (MB)"
  40. col component format a25 head "Component"
  41. col status head "Status"

  43. select to_char(end_time,'dd-Mon hh24:mi') end_time, component,
  44. (initial_size/1024/1024) init, (target_size/1024/1024) target,
  45. (final_size/1024/1024) final, status
  46. from v$memory_resize_ops order by end_time;

  48. col mns format 999,999,999 head "Min Size"
  49. col mxs format 999,999,999 head "Max Size"
  50. col granule format 999,999,999 head "Granule Size"

  52. select component, (min_size/1024/1024) mns,
  53. (max_size/1024/1024) mxs, (granule_size/1024/1024) granule
  54. from v$memory_dynamic_components;


  57. Solution

  59. To fix this issue:

  61. 1- Apply Patch 8813366 located in:
  62. https://updates.oracle.com/download/8813366.html

  64. or

  66. 2- Upgrade to 11.2.0.1 and apply 11.2.0.2 patchset which has the fix for bug 8813366.

  68. or

  70. 3-WORKAROUND:

  72. Reduce the number of shared pool subpools by setting the init.ora parameter

  74. "_kghdsidx_count"=2  

  76. or reduce granule size with init.ora parameter:

  78. "_ksmg_granule_size"=16777216

  80. NOTE: These require an outage of the database.
复制代码
跑一下下面的脚本呢
  1. set lines 100
  2. col end_time format a16 head "Time Stamp"
  3. col init format 999,999,999 head "Initial (MB)"
  4. col target format 999,999,999 head "Target (MB)"
  5. col final format 999,999,999 head "Final (MB)"
  6. col component format a25 head "Component"
  7. col status head "Status"

  9. select to_char(end_time,'dd-Mon hh24:mi') end_time, component,
  10. (initial_size/1024/1024) init, (target_size/1024/1024) target,
  11. (final_size/1024/1024) final, status
  12. from v$memory_resize_ops order by end_time;

  14. col mns format 999,999,999 head "Min Size"
  15. col mxs format 999,999,999 head "Max Size"
  16. col granule format 999,999,999 head "Granule Size"

  18. select component, (min_size/1024/1024) mns,
  19. (max_size/1024/1024) mxs, (granule_size/1024/1024) granule
  20. from v$memory_dynamic_components;
复制代码

回复 只看该作者 道具 举报

6#
发表于 2012-12-6 14:09:36
Liu Maclean(刘相兵 发表于 2012-12-6 13:32
说版本 号 至少精确到 第四位！！跑一下下面的脚本呢

SQL> select *  from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

回复 只看该作者 道具 举报

7#
发表于 2012-12-12 12:04:43
Liu Maclean(刘相兵 发表于 2012-12-6 13:32
说版本 号 至少精确到 第四位！！跑一下下面的脚本呢

我重新设置  sga_max_size 就可以了吧  <  memory_max_target
给 PGA 留点空间
从而解决 Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)

回复 只看该作者 道具 举报

8#
发表于 2012-12-12 13:54:02
让你跑的脚本 你给结果了吗？

回复 只看该作者 道具 举报

9#
发表于 2012-12-12 21:53:31
久等了，输入框字符有限 ，无法显示结果，详情请见附件

v$memory_dynamic_components.txt

2.18 KB, 下载次数: 673

v$memory_resize_ops .txt

93.65 KB, 下载次数: 678

回复 只看该作者 道具 举报

10#
发表于 2012-12-12 21:57:49
如果仅仅是 想不让该告警信息出现，那么直接 禁用AMM就是了， 设置ASMM 参考目前的PGA和SGA大小

不清楚你的具体问题是什么。

回复 只看该作者 道具 举报

11#
发表于 2012-12-13 22:36:51
你好！大刘，我想问的是，ASMM是11g 的新特性，PGA与SGA 自动管理，共用内存。
SGA将内存耗尽，PGA无法得到可用内存 回报这个错误
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)


SQL> show parameter memory_max_target
NAME                                  TYPE             VALUE
------------------------------------      -----------          ------------------------------
memory_max_target              big integer      7296M
SQL>
SQL> show parameter memory_target
NAME                                TYPE             VALUE
------------------------------------    -----------         ------------------------------
memory_target                   big integer      7296M
SQL>
SQL> show parameter sga_max_size
NAME                                TYPE           VALUE
------------------------------------    -----------       ------------------------------
sga_max_size                      big integer   7296M
SQL> show parameter sga_target
NAME                                 TYPE            VALUE
------------------------------------     -----------         ------------------------------
sga_target                           big integer     0
SQL>
SQL> show parameter pga
NAME                                    TYPE           VALUE
------------------------------------        -----------        ------------------------------
pga_aggregate_target             big integer     0

这里
memory_max_target = sga_max_size
sga_target=pga=0   SGA与PGA自动管理的
SGA将内存耗尽，PGA无法得到可用内存 回报这个错误
WARNING: Granules of pga_aggregate_target 109 cannot be more than memory_target (114) - sga_target (0) or min_sga (7)


所以 分别设置 SGA 与 PGA 的最大值 就解决问题了

回复 只看该作者 道具 举报

12#
发表于 2012-12-14 09:57:28
"SGA将内存耗尽，PGA无法得到可用内存 回报这个错误"

我的理解不是这样， 不存在sga将内存耗尽这种说法

AMM 下 会自动调优PGA和SGA的大小

你的PGA Target 最大到过 6,848 ，granule size  为64MB ，  6848/64=107

memory_target = 7296M    / 64 = 114个granule

出现该警告可能与 你使用AMM后PGA 不断膨胀有关， 即 PGA TARGET想从 107 个granule size 上升到 109个时 大于( memory_target (114) - sga_target (0) or min_sga (7))

最简单的禁用AMM，使用ASMM或者手动内存管理 ； 至于你是否真的可能PGA不断膨胀，具体参考v$memory_resize_ops .txt


18-Nov 06:15     PGA Target                       6,528        6,848        6,848 COMPLETE      

11-18 06:15 时 PGA TARGET确实膨胀到过 6,848  MB ，这说明这个库可能又很多sort/hash操作

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 04:32 , Processed in 0.058054 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569