[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

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐