[20190507]sga_target=0注意修改_kghdsidx_count设置.txt
[20190507]sga_target=0注意修改_kghdsidx_count设置.txt
--//昨天遇到一例视图定义太复杂导致长时间分析sql语句出现library cache lock等待事件的情况.
--//加上大量使用非绑定变量语句,导致硬解析增加,导致问题更加严重.
--//顺便解析当时同事发现仅仅1个cpu特别忙.实际上因为仅仅1个shared pool latch在工作.
1.环境:
> @ ver1
port_string version banner
------------------ -------------- ----------------------------------------------------------------
ibmpc/win_nt-8.1.0 10.2.0.3.0 oracle database 10g enterprise edition release 10.2.0.3.0 - prod
--//注32位版本.
2.解析:
select addr
,latch#
,child#
,level#
,name
,gets
,sleeps
,immediate_gets
,immediate_misses
,spin_gets
from v$latch_children
where name like 'shared pool'
order by addr;
addr latch# child# level# name gets sleeps immediate_gets immediate_misses spin_gets
-------- ---------- ---------- ---------- ----------- ---------- ---------- -------------- ---------------- ----------
05aa3858 213 1 7 shared pool 25235287 182 0 0 7384
05aa38c0 213 2 7 shared pool 151 0 0 0 0
05aa3928 213 3 7 shared pool 151 0 0 0 0
05aa3990 213 4 7 shared pool 151 0 0 0 0
05aa39f8 213 5 7 shared pool 151 0 0 0 0
05aa3a60 213 6 7 shared pool 151 0 0 0 0
05aa3ac8 213 7 7 shared pool 151 0 0 0 0
7 rows selected.
--//仅仅1个shared pool latch在使用.
> show parameter sga_
name type value
------------- ----------- ------
sga_max_size big integer 1200m
sga_target big integer 1200m
> @ hide _kghdsidx_count
name description default_value session_value system_value
--------------- ------------------ ---------------------- ---------------------- ----------------------
_kghdsidx_count max kghdsidx count true 1 1
> @ hide _enable_shared_pool_durations
name description default_value session_value system_value
----------------------------- -------------------------------------- ------------- ------------- ------------
_enable_shared_pool_durations temporary to disable/enable kgh policy true true true
> show parameter cpu_count
name type value
--------- ------- ------
cpu_count integer 16
--//注:在任务管理器看到32个cpu.不过我发现图中下面一排16个cpu好像没有负载.
--//主要问题是参数_kghdsidx_count=1.仅仅1个subpool.看来这个版本按照每个子缓冲池至少为512mb设计的.
--//参数_kghdsidx_count大小由cpu数量以及共享池大小决定的.最大不过7个.
--//我仅仅知道的原则:
--//共享子缓冲池的分配的算法很简单：
--//·每个子缓冲池必须满足一定的内存约束；
--//·每4颗cpu可以分配一个子缓冲池，最多7个。
--//本来oracle这样做为了减少shared pool,但是带来另外的问题,如果每个子池太小,反而出现ora-04031错误.于是oracle限制每个子池
--//的大小,你可以发现一些blog提到减少参数_kghdsidx_count大小,限制每个子池避免出现子池太小的情况.
--//在oracle 9i中，每个subpool至少128mb，在oracle 10g中，每个子缓冲池至少为256mb,在oracle 11g中，每个子缓冲池至少为512mb.
--//oracle 10g会将单个缓冲池分割再细分4个子分区进行管理(这可能是因为通常4颗cpu才分配一个subpool),
--//分别是"instance", "session", "cursor", and "execution".
--//对方安装32位系统,不能设置很大sga,受限共享内存大小600m上下,这样仅仅1个shared pool latch.
--//这样在大量硬解析的情况下,特别在分析sql语句很长时间的情况下仅仅1个shared pool latch自然很忙.
--//也就是同事看到的情况,仅仅1个cpu在忙...而且sql语句中的视图关联的表太多,导致1条sql语句消耗共享池很大,我执行前面的语句,查看
--//v$sqlarea的sharable_mem达到512k.这样大量非绑定变量语句导致许多对象退出又再进入共享池.
--//这就好比一个宾馆的前台仅仅1名接待人员一样,客户入住登记少没有问题,一旦大量客户登记入住,1个人自然忙不过来,
--//而其他人根本插不上手.有时候非常像现实工作的场景,1个忙的要死,别人根本插不上手,只能在那干等^_^.
3.突然想起我以前1个测试:
--//[20190104]sga_target 的设置和ora-04031错误.txt => http://blog.itpub.net/267265/viewspace-2305567/
--//发现一个问题,就是设置sga_target=0,如果大量语句不使用绑定变量可能存在问题.参数_kghdsidx_count=1,仅仅1个shared pool latch.
--//这样情况应该适当增加_kghdsidx_count,通过测试说明问题:
$ export oracle_sid=xxxx
$ cat initxxxx.ora
db_name=xxxx
instance_name=xxxx
sga_target=0
sga_max_size=20g
--//设置sga_target=0.
sys@xxxx> startup nomount
oracle instance started.
total system global area 2.1379e+10 bytes
fixed size 2262656 bytes
variable size 2.1206e+10 bytes
database buffers 134217728 bytes
redo buffers 36073472 bytes
select addr
,latch#
,child#
,level#
,name
,gets
,sleeps
,immediate_gets
,immediate_misses
,spin_gets
from v$latch_children
where name like 'shared pool'
order by addr;
addr latch# child# level# name gets sleeps immediate_gets immediate_misses spin_gets
---------------- ---------- ---------- ---------- ----------- ---- ---------- -------------- ---------------- ----------
000000006010f288 336 1 7 shared pool 1397 0 0 0 5
000000006010f328 336 2 7 shared pool 0 0 0 0 0
000000006010f3c8 336 3 7 shared pool 0 0 0 0 0
000000006010f468 336 4 7 shared pool 0 0 0 0 0
000000006010f508 336 5 7 shared pool 0 0 0 0 0
000000006010f5a8 336 6 7 shared pool 0 0 0 0 0
000000006010f648 336 7 7 shared pool 0 0 0 0 0
7 rows selected.
sys@xxxx> @ hide _kghdsidx_count
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_kghdsidx_count%')
name description default_value session_value system_value
--------------- ------------------ ------------- ------------- ------------
_kghdsidx_count max kghdsidx count true 1 1
sys@xxxx> @ hide _enable_shared_pool_durations
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_enable_shared_pool_durations%')
name description default_value session_value system_value
----------------------------- -------------------------------------- ------------- ------------- ------------
_enable_shared_pool_durations temporary to disable/enable kgh policy true false false
--//注意_enable_shared_pool_durations参数,在sga_target=0的情况下,_enable_shared_pool_durations的缺省值等于false(实际上设
--//置为true也无效,看后面测试)
--//也就是仅仅1个subpool,下面仅仅一个子子池.
sys@xxxx> @ sgastatx.sql 'free memory'
-- all allocations:
subpool bytes mb
------------------------------ ---------- ----------
shared pool (0 - unused): 67108864 64
shared pool (1): 268435456 256
shared pool (total): 335544320 320
-- allocations matching "free memory":
old 15: and lower(ksmssnam) like lower('%&1%')
new 15: and lower(ksmssnam) like lower('%free memory%')
subpool name sum(bytes) mb
------------------------------ -------------------- ---------- ----------
shared pool (0 - unused): free memory 67108864 64
shared pool (1): free memory 30813880 29.39
--//仅仅1个子池.共享内存不足的情况下会从shared pool (0 - unused)分过来.
--//另外注意1个问题,有一些文章提示查询x$kghlu可以查询这些子缓冲池的分配,我的测试不行,当然我现在sga_target=0的情况下不存
--//在子子池的情况.
--//通过一个内部表x$kghlu([k]ernel [g]eneric memory [h]eap manager state of [l]r[u] of unpinned recreatable chunks)可以
--//查询这些子缓冲池的分配： (我的测试不行!!)
sys@xxxx> select * from x$kghlu;
addr indx inst_id kghluidx kghludur kghlushrpool kghlufsh kghluops kghlurcr kghlutrn kghlumxa kghlumes kghlumer kghlurcn kghlurmi kghlurmz kghlurmx kghlunfu kghlunfs
---------------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00007f6d32e5eff0 0 1 1 0 1 0 64 3 43 2147483647 0 0 0 0 0 0 0 0
--//只能通过heapdump转储获取这方面信息.
sys@xxxx> alter session set events 'immediate trace name heapdump level 2';
session altered.
$ grep 'sga heap' /u01/app/oracle/diag/rdbms/xxxx/xxxx/trace/xxxx_ora_43866.trc
heap dump heap name="sga heap" desc=0x60001190
heap dump heap name="sga heap(1,0)" desc=0x6005c318
--//可以看出问题.仅仅1个子池,不细分子子池.也就是在sga_target=0的情况下,可能需要手工设置_kghdsidx_count的大小.
--//不然可能出现shared pool latch的争用,特别在应用没有绑定变量的情况下.
--//我个人建议sga_target=0的情况下手工设置shared_pool_size,db_cache_size.
--//或者sga_target<>0的情况下,也设置shared_pool_size,db_cache_size作为最小值,避免内存在这些组件中变换.
--//修改*._kghdsidx_count=3
$ cat initxxxx.ora
db_name=xxxx
instance_name=xxxx
#sga_target=20g
sga_target=0
sga_max_size=20g
#pre_page_sga=true
*._kghdsidx_count=3
sys@xxxx> startup nomount
ora-04031: unable to allocate 320032 bytes of shared memory ("shared pool","unknown object","kgsk scheduler","kgsk chg class latches")
--//shared_pool_size太小.仅仅320m.
--//测试增加*._kghdsidx_count=3,*.shared_pool_size=1600m,*._enable_shared_pool_durations=true的情况:
$ cat initxxxx.ora
db_name=xxxx
instance_name=xxxx
sga_target=0g
#sga_target=20g
sga_max_size=20g
#pre_page_sga=true
*._kghdsidx_count=3
*.shared_pool_size=1600m
*._enable_shared_pool_durations=true
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sys@xxxx> startup nomount
oracle instance started.
total system global area 2.1379e+10 bytes
fixed size 2262656 bytes
variable size 2.1206e+10 bytes
database buffers 134217728 bytes
redo buffers 36073472 by
sys@xxxx> @ hide _kghdsidx_count
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_kghdsidx_count%')
name description default_value session_value system_value
--------------- ------------------ ------------- ------------- ------------
_kghdsidx_count max kghdsidx count false 3 3
sys@xxxx> @ hide _enable_shared_pool_durations
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_enable_shared_pool_durations%')
name description default_value session_value system_value
---------------------------------------- -------------------------------------- ------------- ------------- ------------
_enable_shared_pool_durations temporary to disable/enable kgh policy false true true
sys@xxxx> select * from x$kghlu;
addr indx inst_id kghluidx kghludur kghlushrpool kghlufsh kghluops kghlurcr kghlutrn kghlumxa kghlumes kghlumer kghlurcn kghlurmi kghlurmz kghlurmx kghlunfu kghlunfs
---------------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00007fc432a52f70 0 1 3 0 1 0 20 0 14 2147483647 0 0 0 0 0 0 0 0
00007fc432a54640 1 1 2 0 1 0 9 0 7 2147483647 0 0 0 0 0 0 0 0
00007fc432a53ff0 2 1 1 0 1 0 14 0 10 2147483647 0 0 0 0 0 0 0 0
--//仅仅3个子池.看不出子子池.
sys@xxxx> alter session set events 'immediate trace name heapdump level 2';
session altered.
$ grep 'sga heap' /u01/app/oracle/diag/rdbms/xxxx/xxxx/trace/xxxx_ora_43949.trc
heap dump heap name="sga heap" desc=0x60001190
heap dump heap name="sga heap(1,0)" desc=0x6005c318
heap dump heap name="sga heap(2,0)" desc=0x60065be0
heap dump heap name="sga heap(3,0)" desc=0x6006f4a8
--//可以发现设置即使_enable_shared_pool_durations=true,在sga_target=0g的情况下也不会出现子子池的情况.
sys@xxxx> @ sgastatx.sql 'free memory'
-- all allocations:
subpool bytes mb
------------------------------ ---------- ----------
shared pool (0 - unused): 1275068416 1216
shared pool (1): 134217728 128
shared pool (2): 134217728 128
shared pool (3): 134217728 128
shared pool (total): 1677721600 1600
-- allocations matching "free memory":
old 15: and lower(ksmssnam) like lower('%&1%')
new 15: and lower(ksmssnam) like lower('%free memory%')
subpool name sum(bytes) mb
------------------------------ ---------------------------------------- ---------- ----------
shared pool (0 - unused): free memory 1275068416 1216
shared pool (1): free memory 48797608 46.54
shared pool (2): free memory 34835672 33.22
shared pool (3): free memory 48306064 46.07
select addr
,latch#
,child#
,level#
,name
,gets
,sleeps
,immediate_gets
,immediate_misses
,spin_gets
from v$latch_children
where name like 'shared pool'
order by addr;
addr latch# child# level# name gets sleeps immediate_gets immediate_misses spin_gets
---------------- ---------- ---------- ---------- ----------- ---- ---------- -------------- ---------------- ----------
000000006010f288 336 1 7 shared pool 732 0 1 0 0
000000006010f328 336 2 7 shared pool 673 0 1 0 0
000000006010f3c8 336 3 7 shared pool 903 0 1 0 1
000000006010f468 336 4 7 shared pool 1 0 0 0 0
000000006010f508 336 5 7 shared pool 1 0 0 0 0
000000006010f5a8 336 6 7 shared pool 1 0 0 0 0
000000006010f648 336 7 7 shared pool 1 0 0 0 0
7 rows selected.
--//总之,如果手工管理内存,设置sga_target=0g的情况下,适当设置_kghdsidx_count,shared_pool_size,db_cache_size值.
4.最后测试sga_target<>0的情况:
$ grep -v "^#" initxxxx.ora
db_name=xxxx
instance_name=xxxx
sga_target=20g
sga_max_size=20g
sys@xxxx> @ hide _kghdsidx_count
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_kghdsidx_count%')
name description default_value session_value system_value
---------------------------------------- --------------------------------------- ------------- ------------- ------------
_kghdsidx_count max kghdsidx count true 6 6
sys@xxxx> @ hide _enable_shared_pool_durations
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_enable_shared_pool_durations%')
name description default_value session_value system_value
---------------------------------------- --------------------------------------- ------------- ------------- ------------
_enable_shared_pool_durations temporary to disable/enable kgh policy true true true
sys@xxxx> @ sgastatx.sql 'free memory'
-- all allocations:
subpool bytes mb
------------------------------ ---------- ----------
shared pool (0 - unused): 469762048 448
shared pool (1): 268435456 256
shared pool (2): 335544320 320
shared pool (3): 335544320 320
shared pool (4): 335544320 320
shared pool (5): 268435456 256
shared pool (6): 335544320 320
shared pool (total): 2348810240 2240
8 rows selected.
-- allocations matching "free memory":
old 15: and lower(ksmssnam) like lower('%&1%')
new 15: and lower(ksmssnam) like lower('%free memory%')
subpool name sum(bytes) mb
------------------------------ -------------------- ---------- ----------
shared pool (0 - unused): free memory 469762048 448
shared pool (1): free memory 207961376 198.33
shared pool (2): free memory 224686568 214.28
shared pool (3): free memory 270474992 257.95
shared pool (4): free memory 234357448 223.5
shared pool (5): free memory 206367112 196.81
shared pool (6): free memory 239940912 228.83
7 rows selected.
sys@xxxx> select * from x$kghlu;
sub ssub flushed lru list recurrent transient reserved reserved reserved reserved free unpin last frunp
addr indx inst_id pool pool kghlushrpool chunks operations chunks chunks kghlumxa kghlumes kghlumer scans misses miss size miss max sz unsuccess unsucc size
---------------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- -----------
00007f78519f2e48 0 1 6 0 1 0 17 0 17 2147483647 0 0 0 0 0 0 0 0
00007f78519f27f8 1 1 5 0 1 0 27 0 17 2147483647 0 0 0 0 0 0 0 0
00007f78519f3e60 2 1 4 0 1 0 23 0 19 2147483647 0 0 0 0 0 0 0 0
00007f78519f3810 3 1 3 0 1 0 40 0 16 2147483647 0 0 0 0 0 0 0 0
00007f78519f4ee0 4 1 2 0 1 0 34 0 22 2147483647 0 0 0 0 0 0 0 0
00007f78519f4890 5 1 1 0 1 0 26 0 16 2147483647 0 0 0 0 0 0 0 0
6 rows selected.
--// 看不出子子池的分配情况.
sys@xxxx> alter session set events 'immediate trace name heapdump level 2';
session altered.
$ egrep "sga heap|total heap size" /u01/app/oracle/diag/rdbms/xxxx/xxxx/trace/xxxx_ora_44083.trc
heap dump heap name="sga heap" desc=0x60001190
heap dump heap name="sga heap(1,0)" desc=0x6005c318
total heap size = 67108776
heap dump heap name="sga heap(1,1)" desc=0x6005db70
total heap size = 67108776
heap dump heap name="sga heap(1,2)" desc=0x6005f3c8
total heap size = 67108776
heap dump heap name="sga heap(1,3)" desc=0x60060c20
total heap size = 67108776
--// 67108776*4/1024/1024 = 255.99966430664062500000 ,接近256m.
heap dump heap name="sga heap(2,0)" desc=0x60065be0
total heap size =134217552
heap dump heap name="sga heap(2,1)" desc=0x60067438
total heap size = 67108776
heap dump heap name="sga heap(2,2)" desc=0x60068c90
total heap size = 67108776
heap dump heap name="sga heap(2,3)" desc=0x6006a4e8
total heap size = 67108776
--//(134217552+67108776+67108776+67108776)/1024/1024 = 319.99958038330078125000,接近320m.
heap dump heap name="sga heap(3,0)" desc=0x6006f4a8
total heap size =134217552
heap dump heap name="sga heap(3,1)" desc=0x60070d00
total heap size = 67108776
heap dump heap name="sga heap(3,2)" desc=0x60072558
total heap size = 67108776
heap dump heap name="sga heap(3,3)" desc=0x60073db0
total heap size = 67108776
heap dump heap name="sga heap(4,0)" desc=0x60078d70
total heap size =134217552
heap dump heap name="sga heap(4,1)" desc=0x6007a5c8
total heap size = 67108776
heap dump heap name="sga heap(4,2)" desc=0x6007be20
total heap size = 67108776
heap dump heap name="sga heap(4,3)" desc=0x6007d678
total heap size = 67108776
heap dump heap name="sga heap(5,0)" desc=0x60082638
total heap size = 67108776
heap dump heap name="sga heap(5,1)" desc=0x60083e90
total heap size = 67108776
heap dump heap name="sga heap(5,2)" desc=0x600856e8
total heap size = 67108776
heap dump heap name="sga heap(5,3)" desc=0x60086f40
total heap size = 67108776
heap dump heap name="sga heap(6,0)" desc=0x6008bf00
total heap size =134217552
heap dump heap name="sga heap(6,1)" desc=0x6008d758
total heap size = 67108776
heap dump heap name="sga heap(6,2)" desc=0x6008efb0
total heap size = 67108776
heap dump heap name="sga heap(6,3)" desc=0x60090808
total heap size = 67108776
--//这样可以看到每个子池有几个子子池,并且每个的大小.
总结:
--//总之注意,如果手工管理内存,设置sga_target=0g的情况下,适当设置_kghdsidx_count,shared_pool_size,db_cache_size值.避免
--//shared pool latch仅仅1个的情况.
--//我个性喜欢手工管理内存设置sga_target=sga_max_size,设置shared_pool_size,db_cache_size基本不会转换.
--//附上sgastatx.sql脚本.
$ cat sgastatx.sql
--------------------------------------------------------------------------------
--
-- file name: sgastatx
-- purpose: show shared pool stats by sub-pool from x$ksmss
--
-- author: tanel poder
-- copyright: (c) http://www.tanelpoder.com
--
-- usage: @sgastatx <statistic name>
-- @sgastatx "free memory"
-- @sgastatx cursor
--
-- other: the other script for querying v$sgastat is called sgastat.sql
--
--
--
--------------------------------------------------------------------------------
col sgastatx_subpool head subpool for a30
prompt
prompt -- all allocations:
select
'shared pool ('||nvl(decode(to_char(ksmdsidx),'0','0 - unused',ksmdsidx), 'total')||'):' sgastatx_subpool
, sum(ksmsslen) bytes
, round(sum(ksmsslen)/1048576,2) mb
from
x$ksmss
where
ksmsslen > 0
--and ksmdsidx > 0
group by rollup
( ksmdsidx )
order by
sgastatx_subpool asc
/
break on sgastatx_subpool skip 1
prompt -- allocations matching "&1":
select
subpool sgastatx_subpool
, name
, sum(bytes)
, round(sum(bytes)/1048576,2) mb
from (
select
'shared pool ('||decode(to_char(ksmdsidx),'0','0 - unused',ksmdsidx)||'):' subpool
, ksmssnam name
, ksmsslen bytes
from
x$ksmss
where
ksmsslen > 0
and lower(ksmssnam) like lower('%&1%')
)
group by
subpool
, name
order by
subpool asc
, sum(bytes) desc
/
break on sgastatx_subpool dup