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

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 一边是BUFFER CACHE不够用，一边是shared pool存在大量 ...
发新帖
avantar

90

积分

0

好友

0

主题

一边是BUFFER CACHE不够用，一边是shared pool存在大量的free memory

1#
发表于 2012-3-13 22:47:35 | 查看: 8732| 回复: 16
大概描述下我们一个生产库的情况，
SGA_TARGET=22G
之前由于大量的SQL没有使用绑定变量，导致shared pool的CCursor、PCursor，sql area等区域占用超过16G内存，整个shared pool约18-19G，
BUFFER CACHE只有3G-4G的样子，
后面我们要求开发把SQL改写了，使用了绑定变量，果然CCursor、PCursor，sql area占用的内存都释放了，sql area更是只有几十M，
现在shared pool中free memory约16G，但并没有释放给BUFFER CACHE。

请问下是否需要重启下数据库才可以实现我们的目的，谢谢。
17#
发表于 2012-3-19 10:57:57
Bug 7189722 - Frequent grow/shrink SGA resize operations [ID 7189722.8]

--------------------------------------------------------------------------------

  修改时间 03-DEC-2011     类型 PATCH     状态 PUBLISHED   


Bug 7189722  Frequent grow/shrink SGA resize operations
This note gives a brief overview of bug 7189722.
The content was last updated on: 02-DEC-2011
Click here for details of each of the sections below.

Affects:
Product (Component) Oracle Server (Rdbms)  
Range of versions believed to be affected Versions BELOW 11.2  
Versions confirmed as being affected 10.2.0.4

Platforms affected Generic (all / most platforms affected)  

Fixed:
This issue is fixed in 11.2.0.1 (Base Release)
10.2.0.5 (Server Patch Set)


Symptoms: Related To:
Performance Affected (General)
Automatic Memory Management
V$SGA_RESIZE_OPS
_MEMORY_BROKER_STAT_INTERVAL


Description
Frequent resizes of the shared pool / buffer cache may be
seen when using Auto Memory Management,
V$SGA_RESIZE_OPS shows alternating shared pool and buffer cache resizes
within minutes of each other.

Workaround
  alter system set "_memory_broker_stat_interval"=999; --- 999sec between resizes

Note:
The 11g form of this fix is missing some changes as described in bug 9267837.
For 11g use the fix for bug 9267837 instead.

For more information see Note:742599.1

HOOKS PSE:A204 PARAMETER:_MEMORY_BROKER_STAT_INTERVAL VIEW:V$SGA_RESIZE_OPS LIKELYAFFECTS XAFFECTS_10.2.0.1 XAFFECTS_V10020001 AFFECTS=10.2.0.1 XAFFECTS_10.2.0.2 XAFFECTS_V10020002 AFFECTS=10.2.0.2 XAFFECTS_10.2.0.3 XAFFECTS_V10020003 AFFECTS=10.2.0.3 XAFFECTS_10.2.0.4 XAFFECTS_V10020004 AFFECTS=10.2.0.4 XAFFECTS_11.1.0.6 XAFFECTS_V11010006 AFFECTS=11.1.0.6 XAFFECTS_11.1.0.7 XAFFECTS_V11010007 AFFECTS=11.1.0.7 XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_AMM TAG_PERF AMM PERF FIXED_10.2.0.5 FIXED_11.2.0.1
Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.



References
Bug:7189722 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article


怀疑跟这个BUG有关

回复 只看该作者 道具 举报

16#
发表于 2012-3-14 22:29:10
SQL> select * from v$version;

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

SQL> set line 190 pagesize 1400
SQL> select component,
  2         current_size / 1024 / 1024 "CURRENT_SIZE",
  3         min_size / 1024 / 1024 "MIN_SIZE",
  4         user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",
  5         last_oper_type "TYPE"
  6    from v$sga_dynamic_components;

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE TYPE
---------------------------------------------------------------- ------------ ---------- ------------------- -------------
shared pool                                                               480        316                 320 SHRINK
large pool                                                                  4          0                   0 GROW
java pool                                                                   4          4                   0 STATIC
streams pool                                                                0          0                   0 STATIC
DEFAULT buffer cache                                                      500        164                 500 GROW
KEEP buffer cache                                                           0          0                   0 STATIC
RECYCLE buffer cache                                                        0          0                   0 STATIC
DEFAULT 2K buffer cache                                                     0          0                   0 STATIC
DEFAULT 4K buffer cache                                                     0          0                   0 STATIC
DEFAULT 8K buffer cache                                                     0          0                   0 STATIC
DEFAULT 16K buffer cache                                                    0          0                   0 STATIC
DEFAULT 32K buffer cache                                                    0          0                   0 STATIC
Shared IO Pool                                                              0          0                   0 STATIC
ASM Buffer Cache                                                            0          0                   0 STATIC

14 rows selected.


可以看到 shared pool   发生了SHRINK ，  DEFAULT buffer cache 发生了GROW

但是在11.2.0.3 中 KGH:NO ACCESS消失了：


SQL> select * from v$sgastat where name like '%KGH%';

no rows selected



SQL> select name, trunc(bytes / 1024 / 1024, 2) "size(MB)"
  2    from v$sgastat
  3  where pool is null
  4  union
  5  select pool, trunc(sum(bytes) / 1024 / 1024, 2) "size(MB)"
  6    from v$sgastat
  7  where pool is not null
  8  group by pool;

NAME                         size(MB)
-------------------------- ----------
buffer_cache                      500
fixed_sga                        2.13
java pool                           4
large pool                          4
log_buffer                       5.32
shared pool                       480

6 rows selected.


这就避免了 v$sgastat对 POOL 的统计和 v$sga_dynamic_components 得到的数值 不一致的问题。

回复 只看该作者 道具 举报

15#
发表于 2012-3-14 21:23:23
the following event can be used to get a listing of current granule allocations:

alter session set events ‘immediate trace name
DUMP_ALL_COMP_GRANULE_ADDRS level 1′;

And the granules shown in listid 4 of the default buffer cache are
represented by “KGH: NO ACCESS” as you can see below.

From trace file produced by dump_all_comp_granule_addrs event in udump:

GRANULE SIZE is 16777216

COMPONENT NAME : DEFAULT buffer cache, curnum=72, usrsz=0, tgt=72, st=0          ==> DEFAULT buffer cache,
Number of granules in listid 0 is 0
Number of granules in listid 1 is 0
Number of granules in listid 2 is 52
Number of granules in listid 3 is 0
Number of granules in listid 4 is 20

SQL> select bytes from v$sgastat where name ='KGH: NO ACCESS';

BYTES
———-
329746560

20 granules * 16MB = 335544320 ~= 329746560


V$SGASTAT的信息来源于 x$ksmstrs

GV$SGASTAT:

select inst_id, '', ksmssnam, ksmsslen
  from x$ksmfs
where ksmsslen > 1
union all
select inst_id, 'shared pool', ksmssnam, sum(ksmsslen)
  from x$ksmss
where ksmsslen > 1
group by inst_id, 'shared pool', ksmssnam
union all
select inst_id, 'large pool', ksmssnam, sum(ksmsslen)
  from x$ksmls
where ksmsslen > 1
group by inst_id, 'large pool', ksmssnam
union all
select inst_id, 'java pool', ksmssnam, sum(ksmsslen)
  from x$ksmjs
where ksmsslen > 1
group by inst_id, 'java pool', ksmssnam
union all
select inst_id, 'numa pool', ksmnsnam, sum(ksmnslen)
  from x$ksmns
where ksmnslen > 1
group by inst_id, 'numa pool', ksmnsnam
union all
select inst_id, 'streams pool', ksmssnam, sum(ksmsslen)
  from x$ksmstrs
where ksmsslen > 1
group by inst_id, 'streams pool', ksmssnam

回复 只看该作者 道具 举报

14#
发表于 2012-3-14 21:13:11
即 Default Buffer Cache= Real Default Buffer Cache + KGH: NO ACCESS(in Shared Pool)
而  v$sgastat 中计算的buffer cache= Default Buffer Cache + Keep Cache+ other Buffer cache


即 虽然在 V$SGASTAT中显示的shared pool 大小达到了 7886.28MB ， 但其实这里面有3500MB 其实是Default Buffer Cache在用。




ML的意思是 KGH: NO ACCESS  部分不会从形式上转化成buffer cache了？会一直以 KGH: NO ACCESS在那几个视图中显示？（虽然实际上已经被当做BUFFER CACHE在使用了）

回复 只看该作者 道具 举报

13#
发表于 2012-3-14 21:08:41
所以，我怀疑是不是SGA的自动管理机制存在一些瑕疵，
想通过下面2种方式应该可以解决：
1，创建临时pfile,将pfile中
orcl.__db_cache_size=
orcl.__java_pool_size=
orcl.__large_pool_size=
orcl.__shared_pool_size=
这些表示关闭前的内存分配情况的参数都去掉，再创建spfile重启，让sga启动后自动重新分配BUFFER_CACHE及shared pool。

2，设置buffer_cache的最小值或者干脆手动管理

回复 只看该作者 道具 举报

12#
发表于 2012-3-14 21:00:57
这个库是这样的：
本来库上放了一个系统的A模块和B模块，A模块是自己开发的（JAVA），B模块是购买的产品（C），
一个月前我发现该库SHARED POOL（7-8G），BUFFER CACHE只有3-4G，
进一步检查发现B模块存在许多未使用绑定变量的SQL，但由于是购买的产品，不好整改，
于是1个月前的样子我把B模块对应的数据库的SCHEMA迁移走了，只保留A模块的SCHEMA及A模块的数据。
立马shared pool的free memory及KGH: NO ACCESS就上来了（之前shared pool中的free memory及KGH: NO ACCESS很小）


所以那时候就注意到了KGH: NO ACCESS部分，但应该过去了快一个月了吧，还是维持在3G多，跟之前相比，变化不大。

回复 只看该作者 道具 举报

11#
发表于 2012-3-14 20:44:42
FROM LAOZHANG:

Oracle 已经将该以上问题归纳为bug :




Bug 4577426  V$SGASTAT may show misleading BYTES values larger than really allocated
This note gives a brief overview bug 4577426.
The content was last updated on: 24-SEP-2010
Click here for details of each of the sections below.
Affects:

    Product (Component)        Oracle Server (Rdbms)
    Range of versions believed to be affected         Versions BELOW 12.1
    Versions confirmed as being affected         

        11.2.0.1
        11.1.0.7
        10.2.0.5
        10.2.0.4

    Platforms affected        Generic (all / most platforms affected)

Fixed:

    This issue is fixed in        

        12.1 (Future Release)
        11.2.0.2 (Server Patch Set)

Symptoms:
        
Related To:

    Wrong Results

        

    Performance Monitoring
    V$SGASTAT

Description

    Some values in V$SGASTAT are not accounted correctly.
    This causes them to show as allocations which are
    continually increasing in size when in fact there is no
    increase.
    When this occurs the sum of the v$sgastat "bytes" column
    shows a result larger than actual pool size.
     
    Rediscovery Notes:
     Totals of v$sgastat pools do not agree with
     v$sga_dynamic_components sizes
     
    Workaround
     Ignore values known to be incorrectly accounted.
     or
     Use the information from v$sga_dynamic_components
     or
     Check actual allocations using an SGA heapdump.

回复 只看该作者 道具 举报

10#
发表于 2012-3-14 20:29:53
”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;

    NAME size(MB)
1 buffer_cache 7760
2 fixed_sga 2.08
3 java pool 64
4 large pool 16
5 log_buffer 5.03
6 shared pool 7886.28
7 streams pool 16


sql>show parameter sga_
sga_max_size   12G
sga_target         12G

7760+7886.28已经是15G了，超出了设置的SGA，请问这个是怎么回事。“



v$sgastat 中计算的 shared pool 7886.28MB  ==> 这与我们从v$sga_dynamic_components中得到的shared pool   CURRENT_SIZE 4416MB 是矛盾的。


Why?

来看一下shared pool的组成： KGH: NO ACCESS 大约3G ，  free memory     大约3G

shared pool  sql area                     98632496                                                                                                                                            
shared pool  kglsim heap                 121903488                                                                                                                                            
shared pool  Cursor Stats                126643176                                                                                                                                            
shared pool  kglsim object batch         180560856                                                                                                                                            
shared pool  KGH: NO ACCESS             3670587456                                                                                                                                            
shared pool  free memory                3827729808  


这里存在一个问题  KGH: NO ACCESS 是什么？

statistics 'KGH: NO ACCESS' from v$sgastat:

"KGH: NO ACCESS" refers to granules that are in transit with ASMM i.e memory being reassigned from the Shared Pool to the Database Buffer Cache (Default cache only) and vice-versa. This memory component in the Shared Pool marked as 'KGH: NO ACCESS' is used by the Buffer Cache (Default cache only).

即"KGH: NO ACCESS"这部分虽然是算在Shared Pool中的，但本质上这部分内存是从Shared Pool中重分配到 Default Buffer Cache使用的。


即 Default Buffer Cache= Real Default Buffer Cache + KGH: NO ACCESS(in Shared Pool)
而  v$sgastat 中计算的buffer cache= Default Buffer Cache + Keep Cache+ other Buffer cache


即 虽然在 V$SGASTAT中显示的shared pool 大小达到了 7886.28MB ， 但其实这里面有3500MB 其实是Default Buffer Cache在用。

为什么Oracle要这样做？

为了实现 shared pool的 AUTO SGA ，详见拙作 深入了解 ASMM。

回复 只看该作者 道具 举报

9#
发表于 2012-3-14 20:00:46
"有没有某种事件可以跟踪SGA自动调整的过程？"

如7楼介绍的_memory_management_tracing 这个隐藏参数可以控制对 自动SGA内存管理的追踪， 详见拙作《深入了解ASMM》：http://www.oracledatabase12g.com ... ory-management.html


简单的 SGA RESIZE 记录可以通过 v$sga_resize_ops视图获得：

FOR 10.2:V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include in-progress operations. All sizes are expressed in bytes.

回复 只看该作者 道具 举报

8#
发表于 2012-3-14 16:15:14
ODM Finding:
  1. sga_max_size                         big integer 12G  

  3. sga size  12288MB

  5. shared_pool_size                     big integer 0   
  6. db_cache_size                        big integer 0   
  7. db_keep_cache_size                   big integer 3G


  10. 当前的SGA 分配情况：

  12. SQL> select component,
  13.   2         current_size / 1024 / 1024 "CURRENT_SIZE",
  14.   3         min_size / 1024 / 1024 "MIN_SIZE",
  15.   4         user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",
  16.   5         last_oper_type "TYPE"
  17.   6    from v$sga_dynamic_components;

  19. COMPONENT                                                        CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE TYPE                                                                             
  20. ---------------------------------------------------------------- ------------ ---------- ------------------- -------------                                                                    
  21. shared pool                                                              4416       4368                   0 SHRINK                                                                           
  22. large pool                                                                 16         16                   0 STATIC                                                                           
  23. java pool                                                                  64         16                   0 SHRINK                                                                           
  24. streams pool                                                               16         16                   0 STATIC                                                                           
  25. DEFAULT buffer cache                                                     4688       4048                   0 GROW                                                                             
  26. KEEP buffer cache                                                        3072       2048                3072 GROW                                                                             
  27. RECYCLE buffer cache                                                        0          0                   0 STATIC                                                                           
  28. DEFAULT 2K buffer cache                                                     0          0                   0 STATIC                                                                           
  29. DEFAULT 4K buffer cache                                                     0          0                   0 STATIC                                                                           
  30. DEFAULT 8K buffer cache                                                     0          0                   0 STATIC                                                                           
  31. DEFAULT 16K buffer cache                                                    0          0                   0 STATIC                                                                           
  32. DEFAULT 32K buffer cache                                                    0          0                   0 STATIC                                                                           
  33. ASM Buffer Cache                                                            0          0                   0 STATIC


  36. shared pool size ==> 4416MB
  37. DEFAULT buffer cache => 4688MB
  38. KEEP buffer cache==> 3072MB


  41. shared pool  free memory                3827729808    ==> shared pool 空闲内存3G
复制代码
从last_oper_type 看DEFAULT buffer Cache 最近在Grow 而shared pool在SHRINK ， 即在向LZ希望的方向发展， 建议再观察一段时间， 或者尝试手动设置 db_cache_size 和 shared_pool_size 的 最小大小(ASMM下 这是minimum size)。

回复 只看该作者 道具 举报

7#
发表于 2012-3-14 14:28:36
I need some clarification on the roles of MMON and MMAN background
processes in Oracle 10.2.0.x. As I understand it, MMAN (Memory Manager)
is the process that actually manages the dynamic resizing in ASMM, where
as MMON (Memory Monitor) process gathers some statistics that I am not
very clear on. When I set the "_memory_management_tracing"
initialization parameter to a value (7, 16, 31, 63), it creates a trace
file for MMON which contains memory statistics information but no memory
related information is written in the alert log file. Can someone please
elaborate on the roles of MMAN versus MMON and how can one see what the
MMAN process doing when it resizes memory.

回复 只看该作者 道具 举报

6#
发表于 2012-3-14 10:48:31
有没有某种事件可以跟踪SGA自动调整的过程？

回复 只看该作者 道具 举报

5#
发表于 2012-3-14 10:27:19
既然你有free memory ,就可以在线指定db_cache_size的最小值

回复 只看该作者 道具 举报

4#
发表于 2012-3-14 10:01:18
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;

    NAME size(MB)
1 buffer_cache 7760
2 fixed_sga 2.08
3 java pool 64
4 large pool 16
5 log_buffer 5.03
6 shared pool 7886.28
7 streams pool 16


sql>show parameter sga_
sga_max_size   12G
sga_target         12G

7760+7886.28已经是15G了，超出了设置的SGA，请问这个是怎么回事。

回复 只看该作者 道具 举报

3#
发表于 2012-3-14 08:22:35
附件已上传！！！！！！！

[ 本帖最后由 avantar 于 2012-3-14 10:02 编辑 ]

auto_sga_diag.txt

286.62 KB, 下载次数: 1464

回复 只看该作者 道具 举报

2#
发表于 2012-3-13 22:51:31
action plan:
  1. spool auto_sga_diag.log

  3. set line 190 pagesize 1400

  5. SELECT a.SGA_MEM + b.PGA_MEM "TOTAL_MEMORY"
  6.     FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM"
  7.             FROM v$sga_dynamic_components,
  8.                  (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM"
  9.                     FROM v$process) a
  10.            WHERE component IN ('shared pool',
  11.                                'large pool',
  12.                                'java pool',
  13.                                'streams pool',
  14.                                'DEFAULT buffer cache')) a,
  15.          (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM" FROM v$process) b;

  17. select component,
  18.        current_size / 1024 / 1024 "CURRENT_SIZE",
  19.        min_size / 1024 / 1024 "MIN_SIZE",
  20.        user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",
  21.        last_oper_type "TYPE"
  22.   from v$sga_dynamic_components;

  24. select component, granule_size / 1024 / 1024 "GRANULE_SIZE(Mb)"
  25.   from v$sga_dynamic_components;

  27. col component for a25
  28. col status format a10 head "Status"
  29. col initial_size for 999,999,999,999 head "Initial"
  30. col parameter for a25 heading "Parameter"
  31. col final_size for 999,999,999,999 head "Final"
  32. col changed head "Changed At"
  33. col low format 999,999,999,999 head "Lowest"
  34. col high format 999,999,999,999 head "Highest"
  35. col lowMB format 999,999 head "MBytes"
  36. col highMB format 999,999 head "MBytes"

  38. SELECT component,
  39.        parameter,
  40.        initial_size,
  41.        final_size,
  42.        status,
  43.        to_char(end_time, 'mm/dd/yyyy hh24:mi:ss') changed
  44.   FROM v$sga_resize_ops
  45. ORDER BY component;

  47. SELECT component,
  48.        min(final_size) low,
  49.        (min(final_size / 1024 / 1024)) lowMB,
  50.        max(final_size) high,
  51.        (max(final_size / 1024 / 1024)) highMB
  52.   FROM v$sga_resize_ops
  53. GROUP BY component
  54. ORDER BY component;

  56. SELECT name, bytes FROM v$sgainfo;

  58. select * from v$sgastat order by bytes asc;

  60. select name, trunc(bytes / 1024 / 1024, 2) "size(MB)"
  61.   from v$sgastat
  62. where pool is null
  63. union
  64. select pool, trunc(sum(bytes) / 1024 / 1024, 2) "size(MB)"
  65.   from v$sgastat
  66. where pool is not null
  67. group by pool;

  69. select * from V$SGA_CURRENT_RESIZE_OPS;

  71. select * from v$sga_target_advice;

  73. show parameter size
  74. show parameter statistics        

  76. spool off;
复制代码
运行以上脚本 并贴出结果

回复 只看该作者 道具 举报

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

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

GMT+8, 2024-11-15 10:32 , Processed in 0.062431 second(s), 25 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

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