[20171206]設定db_2k_cache_size的疑問.txt
[20171206]設定db_2k_cache_size的疑問.txt
--//上午測試最小的資料檔案,我想看看2k資料塊能建議多小的資料檔案,我的測試最小10K,但是遇到1個無法理解的問題.
1.環境:
SYS@book> @ &r/ver1
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@book> alter system set db_2k_cache_size=1M scope=spfile;
System altered.
2.測試:
--//重啟資料庫:
SYS@book> startup
ORACLE instance started.
Total System Global Area 734949376 bytes
Fixed Size 2256752 bytes
Variable Size 197132432 bytes
Database Buffers 528482304 bytes
Redo Buffers 7077888 bytes
Database mounted.
Database opened.
SYS@book> show parameter db_2k
NAME TYPE VALUE
---------------- ----------- ------
db_2k_cache_size big integer 96M
SYS@book> show spparameter db_2k
SID NAME TYPE VALUE
-------- ---------------- ----------- -----
* db_2k_cache_size big integer 1M
SYS@book> show parameter sga_
NAME TYPE VALUE
------------ ----------- -----
sga_max_size big integer 704M
sga_target big integer 0
--//為什麼這麼大,按照我的理解至少一個Granule Size.
SYS@book> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2256752 No
Redo Buffers 7077888 No
Buffer Cache Size 528482304 Yes
Shared Pool Size 180355072 Yes
Large Pool Size 12582912 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 734949376 No
Startup overhead in Shared Pool 133977368 No
Free SGA Memory Available 0
12 rows selected.
--//Granule Size=4194304 ,而實際db_2k_cache_size佔用96M.
SYS@book> @ &r/memalloc
MIN(BASEADDR) MAX(BASEADDR) GRANULES MB GRANFLAGS COMPONENT GRANSTATE
---------------- ---------------- ---------- ---------- ---------- -------------------------------- ----------------
0000000060C00000 0000000066800000 24 96 4 DEFAULT 2K buffer cache ALLOC
0000000066C00000 0000000080000000 102 408 4 DEFAULT buffer cache ALLOC
0000000080400000 0000000080400000 1 4 4 java pool ALLOC
0000000080800000 0000000081000000 3 12 4 large pool ALLOC
0000000081400000 000000008BC00000 43 172 4 shared pool ALLOC
press enter .....
BASEADDR GRANSIZE GRANFLAGS COMPONENT GRANSTATE
---------------- ---------- ---------- -------------------------------- ----------------
0000000060C00000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000061000000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000061400000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000061800000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000061C00000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000062000000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000062400000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000062800000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000062C00000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000063000000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000063400000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000063800000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000063C00000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000064000000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000064400000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000064800000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000064C00000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000065000000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000065400000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000065800000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000065C00000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000066000000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000066400000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000066800000 4194304 4 DEFAULT 2K buffer cache ALLOC
0000000066C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000067000000 4194304 4 DEFAULT buffer cache ALLOC
0000000067400000 4194304 4 DEFAULT buffer cache ALLOC
0000000067800000 4194304 4 DEFAULT buffer cache ALLOC
0000000067C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000068000000 4194304 4 DEFAULT buffer cache ALLOC
0000000068400000 4194304 4 DEFAULT buffer cache ALLOC
0000000068800000 4194304 4 DEFAULT buffer cache ALLOC
0000000068C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000069000000 4194304 4 DEFAULT buffer cache ALLOC
0000000069400000 4194304 4 DEFAULT buffer cache ALLOC
0000000069800000 4194304 4 DEFAULT buffer cache ALLOC
0000000069C00000 4194304 4 DEFAULT buffer cache ALLOC
000000006A000000 4194304 4 DEFAULT buffer cache ALLOC
000000006A400000 4194304 4 DEFAULT buffer cache ALLOC
000000006A800000 4194304 4 DEFAULT buffer cache ALLOC
000000006AC00000 4194304 4 DEFAULT buffer cache ALLOC
000000006B000000 4194304 4 DEFAULT buffer cache ALLOC
000000006B400000 4194304 4 DEFAULT buffer cache ALLOC
000000006B800000 4194304 4 DEFAULT buffer cache ALLOC
000000006BC00000 4194304 4 DEFAULT buffer cache ALLOC
000000006C000000 4194304 4 DEFAULT buffer cache ALLOC
000000006C400000 4194304 4 DEFAULT buffer cache ALLOC
000000006C800000 4194304 4 DEFAULT buffer cache ALLOC
000000006CC00000 4194304 4 DEFAULT buffer cache ALLOC
000000006D000000 4194304 4 DEFAULT buffer cache ALLOC
000000006D400000 4194304 4 DEFAULT buffer cache ALLOC
000000006D800000 4194304 4 DEFAULT buffer cache ALLOC
000000006DC00000 4194304 4 DEFAULT buffer cache ALLOC
000000006E000000 4194304 4 DEFAULT buffer cache ALLOC
000000006E400000 4194304 4 DEFAULT buffer cache ALLOC
000000006E800000 4194304 4 DEFAULT buffer cache ALLOC
000000006EC00000 4194304 4 DEFAULT buffer cache ALLOC
000000006F000000 4194304 4 DEFAULT buffer cache ALLOC
000000006F400000 4194304 4 DEFAULT buffer cache ALLOC
000000006F800000 4194304 4 DEFAULT buffer cache ALLOC
000000006FC00000 4194304 4 DEFAULT buffer cache ALLOC
0000000070000000 4194304 4 DEFAULT buffer cache ALLOC
0000000070400000 4194304 4 DEFAULT buffer cache ALLOC
0000000070800000 4194304 4 DEFAULT buffer cache ALLOC
0000000070C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000071000000 4194304 4 DEFAULT buffer cache ALLOC
0000000071400000 4194304 4 DEFAULT buffer cache ALLOC
0000000071800000 4194304 4 DEFAULT buffer cache ALLOC
0000000071C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000072000000 4194304 4 DEFAULT buffer cache ALLOC
0000000072400000 4194304 4 DEFAULT buffer cache ALLOC
0000000072800000 4194304 4 DEFAULT buffer cache ALLOC
0000000072C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000073000000 4194304 4 DEFAULT buffer cache ALLOC
0000000073400000 4194304 4 DEFAULT buffer cache ALLOC
0000000073800000 4194304 4 DEFAULT buffer cache ALLOC
0000000073C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000074000000 4194304 4 DEFAULT buffer cache ALLOC
0000000074400000 4194304 4 DEFAULT buffer cache ALLOC
0000000074800000 4194304 4 DEFAULT buffer cache ALLOC
0000000074C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000075000000 4194304 4 DEFAULT buffer cache ALLOC
0000000075400000 4194304 4 DEFAULT buffer cache ALLOC
0000000075800000 4194304 4 DEFAULT buffer cache ALLOC
0000000075C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000076000000 4194304 4 DEFAULT buffer cache ALLOC
0000000076400000 4194304 4 DEFAULT buffer cache ALLOC
0000000076800000 4194304 4 DEFAULT buffer cache ALLOC
0000000076C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000077000000 4194304 4 DEFAULT buffer cache ALLOC
0000000077400000 4194304 4 DEFAULT buffer cache ALLOC
0000000077800000 4194304 4 DEFAULT buffer cache ALLOC
0000000077C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000078000000 4194304 4 DEFAULT buffer cache ALLOC
0000000078400000 4194304 4 DEFAULT buffer cache ALLOC
0000000078800000 4194304 4 DEFAULT buffer cache ALLOC
0000000078C00000 4194304 4 DEFAULT buffer cache ALLOC
0000000079000000 4194304 4 DEFAULT buffer cache ALLOC
0000000079400000 4194304 4 DEFAULT buffer cache ALLOC
0000000079800000 4194304 4 DEFAULT buffer cache ALLOC
0000000079C00000 4194304 4 DEFAULT buffer cache ALLOC
000000007A000000 4194304 4 DEFAULT buffer cache ALLOC
000000007A400000 4194304 4 DEFAULT buffer cache ALLOC
000000007A800000 4194304 4 DEFAULT buffer cache ALLOC
000000007AC00000 4194304 4 DEFAULT buffer cache ALLOC
000000007B000000 4194304 4 DEFAULT buffer cache ALLOC
000000007B400000 4194304 4 DEFAULT buffer cache ALLOC
000000007B800000 4194304 4 DEFAULT buffer cache ALLOC
000000007BC00000 4194304 4 DEFAULT buffer cache ALLOC
000000007C000000 4194304 4 DEFAULT buffer cache ALLOC
000000007C400000 4194304 4 DEFAULT buffer cache ALLOC
000000007C800000 4194304 4 DEFAULT buffer cache ALLOC
000000007CC00000 4194304 4 DEFAULT buffer cache ALLOC
000000007D000000 4194304 4 DEFAULT buffer cache ALLOC
000000007D400000 4194304 4 DEFAULT buffer cache ALLOC
000000007D800000 4194304 4 DEFAULT buffer cache ALLOC
000000007DC00000 4194304 4 DEFAULT buffer cache ALLOC
000000007E000000 4194304 4 DEFAULT buffer cache ALLOC
000000007E400000 4194304 4 DEFAULT buffer cache ALLOC
000000007E800000 4194304 4 DEFAULT buffer cache ALLOC
000000007EC00000 4194304 4 DEFAULT buffer cache ALLOC
000000007F000000 4194304 4 DEFAULT buffer cache ALLOC
000000007F400000 4194304 4 DEFAULT buffer cache ALLOC
000000007F800000 4194304 4 DEFAULT buffer cache ALLOC
000000007FC00000 4194304 4 DEFAULT buffer cache ALLOC
0000000080000000 4194304 4 DEFAULT buffer cache ALLOC
0000000080400000 4194304 4 java pool ALLOC
0000000080800000 4194304 4 large pool ALLOC
0000000080C00000 4194304 4 large pool ALLOC
0000000081000000 4194304 4 large pool ALLOC
0000000081400000 4194304 4 shared pool ALLOC
0000000081800000 4194304 4 shared pool ALLOC
0000000081C00000 4194304 4 shared pool ALLOC
0000000082000000 4194304 4 shared pool ALLOC
0000000082400000 4194304 4 shared pool ALLOC
0000000082800000 4194304 4 shared pool ALLOC
0000000082C00000 4194304 4 shared pool ALLOC
0000000083000000 4194304 4 shared pool ALLOC
0000000083400000 4194304 4 shared pool ALLOC
0000000083800000 4194304 4 shared pool ALLOC
0000000083C00000 4194304 4 shared pool ALLOC
0000000084000000 4194304 4 shared pool ALLOC
0000000084400000 4194304 4 shared pool ALLOC
0000000084800000 4194304 4 shared pool ALLOC
0000000084C00000 4194304 4 shared pool ALLOC
0000000085000000 4194304 4 shared pool ALLOC
0000000085400000 4194304 4 shared pool ALLOC
0000000085800000 4194304 4 shared pool ALLOC
0000000085C00000 4194304 4 shared pool ALLOC
0000000086000000 4194304 4 shared pool ALLOC
0000000086400000 4194304 4 shared pool ALLOC
0000000086800000 4194304 4 shared pool ALLOC
0000000086C00000 4194304 4 shared pool ALLOC
0000000087000000 4194304 4 shared pool ALLOC
0000000087400000 4194304 4 shared pool ALLOC
0000000087800000 4194304 4 shared pool ALLOC
0000000087C00000 4194304 4 shared pool ALLOC
0000000088000000 4194304 4 shared pool ALLOC
0000000088400000 4194304 4 shared pool ALLOC
0000000088800000 4194304 4 shared pool ALLOC
0000000088C00000 4194304 4 shared pool ALLOC
0000000089000000 4194304 4 shared pool ALLOC
0000000089400000 4194304 4 shared pool ALLOC
0000000089800000 4194304 4 shared pool ALLOC
0000000089C00000 4194304 4 shared pool ALLOC
000000008A000000 4194304 4 shared pool ALLOC
000000008A400000 4194304 4 shared pool ALLOC
000000008A800000 4194304 4 shared pool ALLOC
000000008AC00000 4194304 4 shared pool ALLOC
000000008B000000 4194304 4 shared pool ALLOC
000000008B400000 4194304 4 shared pool ALLOC
000000008B800000 4194304 4 shared pool ALLOC
000000008BC00000 4194304 4 shared pool ALLOC
173 rows selected.
--//我的測試庫記憶體引數都是手動設定的,沒有使用動態管理.你看到的分配都是連續的區域.
SYS@book> alter system reset db_2k_cache_size sid='*';
System altered.
SYS@book> drop tablespace t03 including contents and datafiles;
Tablespace dropped.
--//刪除2K的資料檔案.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
SYS@book> show parameter sga_
NAME TYPE VALUE
------------ ----------- ------
sga_max_size big integer 608M
sga_target big integer 0
SYS@book> @ &r/memalloc
MIN(BASEADDR) MAX(BASEADDR) GRANULES MB GRANFLAGS COMPONENT GRANSTATE
---------------- ---------------- ---------- ---------- ---------- -------------------------------- ----------------
0000000060C00000 000000007A000000 102 408 4 DEFAULT buffer cache ALLOC
000000007A400000 000000007A400000 1 4 4 java pool ALLOC
000000007A800000 000000007B000000 3 12 4 large pool ALLOC
000000007B400000 0000000085C00000 43 172 4 shared pool ALLOC
//後面省略....
//才明白一個oracle記憶體分配的一個問題,我以前一直以為不同塊大小的記憶體分配是從DEFAULT buffer cache分配的,而實際上不是,
//而是單獨定義,單獨使用.
//如果你對比前面show sga的輸出就明白了(或者看show parameter sga_的顯示).包括分配記憶體的基地址.
//前面sga_max_size=704M,後面sga_max_size=608M.
//704-608=96M
//至於為什麼要分配這麼大,我就不清楚了,那位知道???
3.附上memalloc的指令碼:
col component format a32
select min(BASEADDR), max(BASEADDR), count(1) Granules, sum(a.gransize)/1048576 MB, a.GRANFLAGS, component, a.GRANSTATE
from x$ksmge a, x$kmgsct b
where a.grantype = b.grantype (+)
group by a.GRANFLAGS, component, a.GRANSTATE
order by 1,2;
pause press enter .....
select a.BASEADDR, a.gransize, a.GRANFLAGS, b.component, a.GRANSTATE
from x$ksmge a, x$kmgsct b
where a.grantype = b.grantype (+)
order by 1,2;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2148352/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191209]降序索引疑問.txt索引
- [20190401]那個更快的疑問.txt
- [20190411]linux stat 命令疑問.txtLinux
- [20221125]設定hugepages遇到的問題.txt
- [20201221]spfile設定問題.txt
- [20190510]rman備份的疑問8.txt
- [20190510]rman備份的疑問7.txt
- [20190509]rman備份的疑問5.txt
- [20190423]那個更快的疑問3.txt
- [20220308]查詢x$ksmmem遇到的疑問.txt
- [20191209]降序索引疑問2.txt索引
- [20200303]降序索引疑問5.txt索引
- [20191210]降序索引疑問3.txt索引
- [20191218]降序索引疑問4.txt索引
- [20220304]測試library cache mutex遇到的疑問.txtMutex
- [20221014]資料檔案2的小疑問.txt
- [20191213]不完全恢復疑問.txt
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20201207]關於ORACLE IMU的一些疑問.txtOracle
- [20210926]並行執行計劃疑問.txt並行
- [20190312]關於增量檢查點的疑問(補充).txt
- [20220406]使用那個shared pool latch的疑問1.txt
- [20221111]bash eval設定變數問題.txt變數
- [20240804]關於kitty設定與linux LANG環境設定問題.txtLinux
- [20220412]shared pool latch與使用sga heap的疑問2.txt
- [20220413]shared pool latch與使用sga heap的疑問3.txt
- [20241118]NLS_LANG設定問題2.txt
- RESTful設計中的常見疑問REST
- [20230308]versions偽列versions_starttime疑問2.txt
- 我的疑問
- [20180713]關於hash join 測試中一個疑問.txt
- [20200819]12c Global Temporary table 統計資訊的收集的疑問.txt
- [20190321]測試相同語句遇到導致cursor pin S的疑問.txt
- locust 使用的疑問
- [20190103]設定pre_page_sga=true啟動緩慢的問題.txt
- 有些疑問
- [20221214]limits.conf裡面關於memlock設定問題.txtMIT
- [20230224]bbed設定偏移技巧.txt
- [20191204]sqlplus特殊定義導致的問題.txtSQL