Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 一边是BUFFER CACHE不够用，一边是shared pool存在大量 ...

90 积分	0 好友	0 主题

发消息

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

1^#

发表于 2012-3-13 22:47:35 | 查看: 8730| 回复: 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。

请问下是否需要重启下数据库才可以实现我们的目的，谢谢。

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-3-13 22:51:31

action plan:

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;

复制代码

运行以上脚本并贴出结果

回复只看该作者道具举报

3^#

发表于 2012-3-14 08:22:35

附件已上传！！！！！！！

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

auto_sga_diag.txt

286.62 KB, 下载次数: 1464

回复只看该作者道具举报

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，请问这个是怎么回事。

回复只看该作者道具举报

5^#

发表于 2012-3-14 10:27:19

既然你有free memory ,就可以在线指定db_cache_size的最小值

回复只看该作者道具举报

6^#

发表于 2012-3-14 10:48:31

有没有某种事件可以跟踪SGA自动调整的过程？

回复只看该作者道具举报

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.

回复只看该作者道具举报

Maclean Liu(刘相兵

8^#

发表于 2012-3-14 16:15:14

ODM Finding:

sga_max_size big integer 12G
sga size 12288MB
shared_pool_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 3G
当前的SGA 分配情况：
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 4416 4368 0 SHRINK
large pool 16 16 0 STATIC
java pool 64 16 0 SHRINK
streams pool 16 16 0 STATIC
DEFAULT buffer cache 4688 4048 0 GROW
KEEP buffer cache 3072 2048 3072 GROW
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
ASM Buffer Cache 0 0 0 STATIC
shared pool size ==> 4416MB
DEFAULT buffer cache => 4688MB
KEEP buffer cache==> 3072MB
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)。

回复只看该作者道具举报

Maclean Liu(刘相兵

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.

回复只看该作者道具举报

Maclean Liu(刘相兵

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。

回复只看该作者道具举报

Maclean Liu(刘相兵

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.

回复只看该作者道具举报

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多，跟之前相比，变化不大。

回复只看该作者道具举报

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的最小值或者干脆手动管理

回复只看该作者道具举报

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在使用了）

回复只看该作者道具举报

Maclean Liu(刘相兵

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

回复只看该作者道具举报

Maclean Liu(刘相兵

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 得到的数值不一致的问题。

回复只看该作者道具举报

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有关

回复只看该作者道具举报

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

GMT+8, 2024-11-15 07:24 , Processed in 0.065358 second(s), 25 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部

TEL/電話+86 13764045638

Email service@parnassusdata.com

QQ 47079569