數(shù)據(jù)庫中如何查詢pga/sga及組件值

這篇文章主要介紹數(shù)據(jù)庫中如何查詢pga/sga及組件值，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

查詢buffer_cache設(shè)置的大小對于物理讀次數(shù)和時(shí)間的預(yù)估影響：
select size_for_estimate         "Cache Size (MB)",
       size_factor,
       buffers_for_estimate      "Buffers",
       estd_physical_read_factor est_read_factor,
       estd_physical_reads       estd_phy_red,
       estd_physical_read_time   est_phy_red_t
  from v$db_cache_advice
 where name = 'DEFAULT'
   and block_size =
       (select value from v$parameter where name = 'db_block_size');

查詢當(dāng)sga組件大小
SQL> select component,current_size,max_size,min_size from v$sga_dynamic_components;

COMPONENT                                                        CURRENT_SIZE   MAX_SIZE   MIN_SIZE
---------------------------------------------------------------- ------------ ---------- ----------
shared pool                                                        4294967296          0 4294967296
large pool                                                          268435456          0  268435456
java pool                                                           134217728          0  134217728
streams pool                                                        436207616          0  436207616
DEFAULT buffer cache                                               1.6744E+10          0 1.6744E+10
KEEP buffer cache                                                           0          0          0
RECYCLE buffer cache                                                        0          0          0
DEFAULT 2K buffer cache                                                     0          0          0
DEFAULT 4K buffer cache                                                     0          0          0
DEFAULT 8K buffer cache                                                     0          0          0
DEFAULT 16K buffer cache                                                    0          0          0

COMPONENT                                                        CURRENT_SIZE   MAX_SIZE   MIN_SIZE
---------------------------------------------------------------- ------------ ---------- ----------
DEFAULT 32K buffer cache                                                    0          0          0
ASM Buffer Cache                                                            0          0          0

13 rows selected.

SQL>


SQL>  select pool,sum(bytes/1024/1024) size_m from v$sgastat group by pool;

POOL             SIZE_M
------------ ----------
             15983.9976
shared pool  4126.17428
streams pool  416.03157
large pool          256
java pool           128



oracle 提供了多個(gè)初始化參數(shù)，來設(shè)置SGA中各個(gè)部分的內(nèi)存大小
SQL> show parameter shared_pool_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 244M
控制共享池的大小，實(shí)例運(yùn)行期間可以動(dòng)態(tài)調(diào)整


SQL> show parameter java_pool_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 52M
 控制JAVA池的大小，實(shí)例運(yùn)行期間可以動(dòng)態(tài)調(diào)整


SQL> show parameter large_pool_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 60M
 控制大池的大小，實(shí)例運(yùn)行期間可以動(dòng)態(tài)調(diào)整


SQL> show  parameter streams_pool_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0
 控制流池的大小，實(shí)例運(yùn)行期間可以動(dòng)態(tài)調(diào)整


SQL> show parameter db_cache_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 160M
控制數(shù)據(jù)緩沖區(qū)的大小，實(shí)例運(yùn)行期間可以動(dòng)態(tài)調(diào)整


SQL> show parameter log_buffer;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_buffer                           integer     5603328
 控制日志緩沖區(qū)的大小，該參數(shù)在運(yùn)行期間是不能修改的。


SQL> show parameter sga_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0
與SGA的自動(dòng)管理相關(guān)，如果值是零，需要DBA手動(dòng)管理SGA


SQL> show parameter sga_max_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 524M
sga可以使用的最大內(nèi)存，sga_target的值不能超過sga_max_size，在為SGA中個(gè)部分組成 分配內(nèi)存時(shí)，SGA的總內(nèi)存不能超過sga_max_size設(shè)置的值，否則會(huì)報(bào)錯(cuò)。sga_max_size 在實(shí)例運(yùn)行期間不能夠動(dòng)態(tài)調(diào)整，必須先修改初始化參數(shù)文件，然后重啟實(shí)例。
sga_max_size參數(shù)僅在實(shí)例運(yùn)行期間有效，如果實(shí)例啟動(dòng)時(shí)發(fā)現(xiàn)SGA各部分組成的參數(shù)值 總和大于sga_max_size，實(shí)例會(huì)忽略當(dāng)前設(shè)置的sga_max_size值，并修改sga_max_size為 當(dāng)前SGA的最大值。
 
SQL> show parameter statistics_level;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
 該值與SGA內(nèi)存的自動(dòng)管理相關(guān)，值為typical或者all的時(shí)候oracle會(huì)根據(jù)會(huì)更加收集到的相關(guān)統(tǒng)計(jì)信息作為參照，確定如何分配SGA中的各部分內(nèi)存。
 
 oracle SGA中的各部分內(nèi)存都是以粒度進(jìn)行分配與回收。粒度的大小不能由DBA設(shè)定，取決與SGA的大小，系統(tǒng)平臺(tái)的類型。 在大多數(shù)平臺(tái)下SGA<1G 粒度是4MB，SGA>1G 粒度是16MB。 在win32平臺(tái)下，SGA>1G，粒度是8MB。設(shè)置oracle SGA中各部分的值時(shí)，需要是oracle 粒度的整倍數(shù)，否則oracle會(huì)自動(dòng)進(jìn)行調(diào)整
SQL> select component,granule_size from v$sga_dynamic_components;
 
COMPONENT                                                        GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool                                                           4194304
large pool                                                            4194304
java pool                                                             4194304
streams pool                                                          4194304
DEFAULT buffer cache                                                  4194304
KEEP buffer cache                                                     4194304
RECYCLE buffer cache                                                  4194304
DEFAULT 2K buffer cache                                               4194304
DEFAULT 4K buffer cache                                               4194304
DEFAULT 8K buffer cache                                               4194304
DEFAULT 16K buffer cache                                              4194304
DEFAULT 32K buffer cache                                              4194304
Shared IO Pool                                                        4194304
ASM Buffer Cache                                                      4194304
14 rows selected
oracle SGA中各部分組成粒度值

SQL> select name,bytes from v$sgainfo;
NAME                                  BYTES
-------------------------------- ----------
Fixed SGA Size                      1375820    存儲(chǔ)數(shù)據(jù)庫與實(shí)例的狀態(tài)信息
Redo Buffers                        5795840
Buffer Cache Size                 167772160
Shared Pool Size                  255852544
data dictionary cache              62914560
Java Pool Size                     54525952
Streams Pool Size                         0
Shared IO Pool Size                       0
Granule Size                        4194304
Maximum SGA Size                  548237312
Startup overhead in Shared Pool    58720256
Free SGA Memory Available                 0
SGA中各部分內(nèi)存分配情況查看
 
SQL> select * from v$sga;
 
NAME                      VALUE
-------------------- ----------
Fixed Size              1375820
Variable Size         373293492 包括共享池，大池，java池，流池幾個(gè)組成部分。
Database Buffers      167772160  數(shù)據(jù)緩沖區(qū)（Buffer Cache Size）
Redo Buffers            5795840
SGA中各部分內(nèi)存分配情況查看。

SGA中各個(gè)部分所使用的內(nèi)存只能供自己使用，相互之間無法共享。如果Java Pool分配了1G的內(nèi)存，但是當(dāng)前數(shù)據(jù)庫并沒有運(yùn)用流特性，分配給流池的空間也不能被SGA中的其他
組件使用。所以使用alter system set 設(shè)置SGA中各部分組成的內(nèi)存的時(shí)候，需要謹(jǐn)慎設(shè)置以免導(dǎo)致系統(tǒng)內(nèi)存的浪費(fèi)，性能損耗。

SQL> show parameter shared_pool_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 244M
 
SQL> alter system set shared_pool_size=230m;
System altered

SQL> show parameter shared_pool_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 232M  
 
oracle自動(dòng)根據(jù)粒度調(diào)整為粒度(此處是4MB)進(jìn)行調(diào)整。


SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 4G

SQL> select * from v$pgastat;

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                   4294967296 bytes
aggregate PGA auto target                                        2837357568 bytes
global memory bound                                               429496320 bytes
total PGA inuse                                                  1607690240 bytes
total PGA allocated                                              2188926976 bytes
maximum PGA allocated                                            1.0088E+10 bytes
total freeable PGA memory                                         176619520 bytes
process count                                                           336
max processes count                                                    1115
PGA memory freed back to OS                                      7.4384E+13 bytes
total PGA used for auto workareas                                 464832512 bytes

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
maximum PGA used for auto workareas                              5814672384 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                               1062912 bytes
over allocation count                                                 82922
bytes processed                                                  3.2595E+14 bytes
extra bytes read/written                                         4.6775E+12 bytes
cache hit percentage                                                  98.58 percent
recompute count (total)                                            15634412

19 rows selected.

SQL>

以上是“數(shù)據(jù)庫中如何查詢pga/sga及組件值”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！