[20220415]為什麼沒有子子池.txt

lfree發表於2022-04-15

[20220415]為什麼沒有子子池.txt

--//前一陣子分析sga heap轉儲,我發現生產系統_kghdsidx_count=7,分成7個子池,但是我發現沒有再細分4個子子池(也叫孫子池)。
--//看看是什麼原因導致這樣的情況。

1.環境:
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@127.0.0.1:9014/zzzz> @ hide _kghdsidx_count
NAME            DESCRIPTION        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
--------------- ------------------ ------------- ------------- ------------ ----- ---------
_kghdsidx_count max kghdsidx count TRUE          7             7            FALSE FALSE

SYS@127.0.0.1:9014/zzzz> @ hide _enable_shared_pool_durations
NAME                          DESCRIPTION                            DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
----------------------------- -------------------------------------- ------------- ------------- ------------ ----- ---------
_enable_shared_pool_durations temporary to disable/enable kgh policy TRUE          FALSE         FALSE        FALSE FALSE

--//我看了以前的工作筆記,發現如果設定sga_target=0 ,會出現_kghdsidx_count=1的情況,同時_enable_shared_pool_durations=false.
--//連結 : http://blog.itpub.net/267265/viewspace-2305567/ =>[20190104]sga_target 的設定和ORA-04031錯誤.txt
--//我仔細看了我的測試,發現只要設定sga_target 非0 ,不會出現這樣的情況,實際上上面連結的測試分析有錯,注意往下看。

SYS@127.0.0.1:9014/zzzz> create pfile='/tmp/@.ora' from spfile;
File created.

--//檢查引數檔案才發現,不知道安裝者為什麼設定許多隱含引數。

*._add_col_optim_enabled=FALSE
*._awr_disabled_flush_tables=''
*._bloom_filter_enabled=FALSE
*._cleanup_rollback_entries=2000
*._clusterwide_global_transactions=FALSE
*._datafile_write_errors_crash_instance=FALSE
*._gc_defer_time=3
*._gc_policy_time=0
*._gc_read_mostly_locking=FALSE
*._gc_undo_affinity=FALSE
*._ksmg_granule_size=67108864
*._lm_lms_priority_dynamic=FALSE
*._memory_imm_mode_without_autosga=FALSE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*._PX_use_large_pool=TRUE
*._resource_manager_always_off=TRUE
*._resource_manager_always_on=FALSE
*._serial_direct_read='NEVER'
*._smu_debug_mode=134217728
*._system_trig_enabled=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
--//我看了最大可能就是_memory_imm_mode_without_autosga 引數。

2.簡單測試看看:

$ cat initxxxx.ora
db_name=xxxx
instance_name=xxxx
sga_target=50G
sga_max_size=50G
_add_col_optim_enabled=FALSE
_awr_disabled_flush_tables=''
_bloom_filter_enabled=FALSE
_cleanup_rollback_entries=2000
_clusterwide_global_transactions=FALSE
_datafile_write_errors_crash_instance=FALSE
_gc_defer_time=3
_gc_policy_time=0
_gc_read_mostly_locking=FALSE
_gc_undo_affinity=FALSE
_ksmg_granule_size=67108864
_lm_lms_priority_dynamic=FALSE
_PX_use_large_pool=TRUE
_resource_manager_always_off=TRUE
_resource_manager_always_on=FALSE
_serial_direct_read='NEVER'
_smu_debug_mode=134217728
_system_trig_enabled=TRUE
_undo_autotune=FALSE
_use_adaptive_log_file_sync='FALSE'

$ export ORACLE_SID=xxxx

SYS@xxxx> startup nomount
ORACLE instance started.

Total System Global Area 5.3447E+10 bytes
Fixed Size                  2281912 bytes
Variable Size            4563406408 bytes
Database Buffers         4.8855E+10 bytes
Redo Buffers               26464256 bytes
SYS@xxxx> @ hide _enable_shared_pool_durations
NAME                          DESCRIPTION                            DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES_MODI ISSYS_MODIFIABLE
----------------------------- -------------------------------------- ------------- ------------- ------------ ---------- ------------------
_enable_shared_pool_durations temporary to disable/enable kgh policy TRUE          TRUE          TRUE         FALSE      FALSE

--//這樣基本可以排除了這些隱含引數的影響。到底是那個引數導致影響出現這樣的情況呢?
--//create pfile ='/tmp/xxxx.ora' from spfile;

$ grep sga_target  /tmp/xxxx.ora
zzzz1.__sga_target=20266876928
zzzz2.__sga_target=20266876928
*.sga_target=0

--//*.sga_target=0的影響嗎?給引數檔案initxxxx.ora追加1行:
*.sga_target=0

SYS@xxxx> startup nomount
ORACLE instance started.
Total System Global Area 5.3447E+10 bytes
Fixed Size                  2281912 bytes
Variable Size            5.3284E+10 bytes
Database Buffers          134217728 bytes
Redo Buffers               26464256 bytes

SYS@xxxx> @ hide _enable_shared_pool_durations
NAME                          DESCRIPTION                            DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES_MODI ISSYS_MODIFIABLE
----------------------------- -------------------------------------- ------------- ------------- ------------ ---------- ------------------
_enable_shared_pool_durations temporary to disable/enable kgh policy TRUE          FALSE         FALSE        FALSE      FALSE

--//噢,還是怪自己沒有仔細檢查:
SYS@ywdb1> show parameter sga_target
NAME       TYPE        VALUE
---------- ----------- -----
sga_target big integer 0

--//實際上這個是一個先入為主的判斷錯誤,我認為sga_target=0,這樣_kghdsidx_count=1,實際上還受隱含引數shared_pool_size的影響。
--//我以前的分析測試有錯,我重複演示我以前遇到的問題。

$ cat initxxxx.ora
db_name=xxxx
instance_name=xxxx
sga_target=0
sga_max_size=50G
##shared_pool_size=2G

--//重啟例項略。

SYS@xxxx> @ hide _kghdsidx_count
NAME                                     DESCRIPTION                            DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE           ISSES_MODI ISSYS_MODIFIABLE
---------------------------------------- -------------------------------------- ---------------------- ---------------------- ---------------------- ---------- ------------------
_kghdsidx_count                          max kghdsidx count                     TRUE                   1                      1                      FALSE      FALSE

SYS@xxxx> @ hide _enable_shared_pool_durations
NAME                                     DESCRIPTION                            DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE           ISSES_MODI ISSYS_MODIFIABLE
---------------------------------------- -------------------------------------- ---------------------- ---------------------- ---------------------- ---------- ------------------
_enable_shared_pool_durations            temporary to disable/enable kgh policy TRUE                   FALSE                  FALSE                  FALSE      FALSE

--//_kghdsidx_count =1 ,_enable_shared_pool_durations=false.
--//導致先入為主的認為sga_target=0,_kghdsidx_count=1這樣的錯誤思維,這個測試我忽略這樣情況下shared_pool_size的大小,

SYS@xxxx> @ hide shared_pool_size
NAME                 DESCRIPTION                         DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES_MODI ISSYS_MODIFIABLE
-------------------- ----------------------------------- ------------- ------------- ------------ ---------- ------------------
__shared_pool_size   Actual size in bytes of shared pool TRUE          402653184     402653184    FALSE      IMMEDIATE
_io_shared_pool_size Size of I/O buffer pool from SGA    TRUE          4194304       4194304      FALSE      FALSE
shared_pool_size     size in bytes of shared pool        TRUE          402653184     402653184    FALSE      IMMEDIATE

--//402653184/1024/1024 = 384M ,這樣建立的例項shared_pool_size = 384M. 沒有大於512M。這樣_kghdsidx_count=1是正常的。

--//取消shared_pool_size=2G的註解
$ cat initxxxx.ora
db_name=xxxx
instance_name=xxxx
sga_target=0
sga_max_size=50G
shared_pool_size=2G

--//重啟例項略。

SYS@xxxx> @ hide _kghdsidx_count
NAME                                     DESCRIPTION                             DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE           ISSES_MODI ISSYS_MODIFIABLE
---------------------------------------- --------------------------------------- ---------------------- ---------------------- ---------------------- ---------- ------------------
_kghdsidx_count                          max kghdsidx count                      TRUE                   4                      4                      FALSE      FALSE

SYS@xxxx> @ hide _enable_shared_pool_durations
NAME                                     DESCRIPTION                             DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE           ISSES_MODI ISSYS_MODIFIABLE
---------------------------------------- --------------------------------------- ---------------------- ---------------------- ---------------------- ---------- ------------------
_enable_shared_pool_durations            temporary to disable/enable kgh policy  TRUE                   FALSE                  FALSE                  FALSE      FALSE

--//_kghdsidx_count =4 ,_enable_shared_pool_durations=false.
--//可以看出實際上設定sga_target=0 ,最大的問題是_enable_shared_pool_durations=false.導致沒有子子池出現。
--//這樣連結http://www.itpub.net/thread-2104417-1-1.html的問題也可以解析sga_target=0的情況下,_enable_shared_pool_durations=false。
--//這樣就沒有子子池。
--//也許以上鍊接的問題,就是分配不均勻導致的ora-04031錯誤。





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

相關文章