[20190507]sga_target=0注意修改_kghdsidx_count設定.txt

lfree發表於2019-05-07

[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)

--

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

[20190507]sga_target 的設定和ORA-04031錯誤2.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)

--

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


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2643583/,如需轉載,請註明出處,否則將追究法律責任。

相關文章