[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)
--
-- 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 重設定sga_targetOracle
- 改sga_target的注意事項
- [20171218]修改AWR snapshot 設定.txt
- [20190104]sga_target 的設定和ORA-04031錯誤.txt
- [20181024]修改awr收集資訊設定.txt
- Oracle10g修改SGA_TARGETOracle
- memory_target、sga_target、pga_target的設定
- Oracle Rac 修改SGA_TARGET值無變化Oracle
- win10 如何設定txt背景綠色 win10如何在txt文件修改背景顏色Win10
- [20191230]注意設定SYNC_HWCLOCK=yes.txt
- 編碼設定應注意
- oracle process設定的注意事情Oracle
- 預設角色的設定和修改
- 修改sga_target引起的ora-27100 bug嗎
- JavaScript 設定CSS與注意事項JavaScriptCSS
- SQL Server Profiler 設定注意事項SQLServer
- 設定samba是應注意的地方!Samba
- 攻擊者正利用DrayTek路由器0day漏洞修改DNS設定路由器DNS
- 使用stty修改終端設定
- 修改oracle賬戶profile設定Oracle
- oracle awr 修改Snapshots設定Oracle
- Windows XP 預設設定修改攻略(轉)Windows
- [Hive]hive分割槽設定注意事項Hive
- oracle設定process需要注意的事情Oracle
- Oracle:記憶體設定注意事項Oracle記憶體
- 修改sqlplus預設環境設定SQL
- Mac怎麼建立txt檔案?如何設定新建txt的快捷鍵?Mac
- 修改python語法注意Python
- Jboss修改預設介面(設定虛擬主機)
- antd ProTable 設定padding為0padding
- axios 修改Content-Type設定iOS
- 設定SAP後臺顯示和修改
- 用安全模板修改XP本地策略設定
- WAS修改了主機名的設定方法
- CentOS7預設的快捷鍵如何修改設定CentOS
- CentOS6.8修改系統預設語言設定CentOS
- 怎麼修改設定路由器預設密碼路由器密碼
- 10g中同時設定了SGA_TARGET和SHARED_POOL_SIZE的問題