oracle SGA區基礎知識與檢視命令

Instance：資料庫啟動後在記憶體中的映像，以及管理該記憶體區的後臺程式；

組成：（SGA+background process）;

檢視資料庫的例項名：

  Sql >show parameter instance name;

檢視資料庫名：

  Show parameter db_name;

資料庫的檔案組成：引數檔案（spfile,pfile..），控制檔案(controlfile)，日誌檔案(redofile..)，口令檔案(v$pwfile_users)，資料檔案，跟蹤檔案(.trace)，警告檔案(alert)，歸檔檔案(achivlog);

例項與資料庫的關係：

  一個例項對應一個資料庫：單點資料庫（通常情況）

  多個例項對應一個數控庫：OPS和RAC(多個SGA)

       前期的並行伺服器：Oracle Paraller Server

       應用叢集 ：Real Application Cluster

 

SGA（system global area）

SGA是一組包含著一個oracle例項的資料和控制資訊的共享記憶體結構

   是為oracle系統分配的共享記憶體；

   是例項的主要組成部分，最影響資料庫效能；

   自動動態管理各種緩衝（ASMM， keep,recycle,redo_log_buffer不參與動態分配）

SGA的快取型別：

1.      db_buffer_cache(資料快取)

(1) default_pool （普通程式訪問此緩衝區會將該快取區移動到LRU列表的MRU端，但是執行FTS（full table scan）和小表查詢時會放到LRU端）

(2) keep pool （資料先進先出，用來放熱點表，VIP池）

(3) recycle pool （用來放冷點大表，不需要多大空間，吸引冷資料。相當於垃圾池）

(4) db_nk_cache_size（不同記憶體塊大小以適應不同塊大小表空間）

2.      shared_pool（SGA中最關鍵記憶體片段，太小降低效能，太大消耗大量cpu資源）

（1）    library_cache（sql語句快取）

（2）    data dictionary cache（資料字典快取）

（3）    other（600多項部分）

3.      large_pool （常用做備用池）

4.      java_pool（java開發支援）

5.      redo_log_buffer（記載對資料庫的任何修改，寫入磁碟的redolog是例項恢復的依據）

 

SGA部分重要查詢命令：

設定自動跟蹤統計：

Set autot on stat

Set autot off

Show sga

Select * from v$sga

Show parameter sga;(檢視sga max size，sga target size大小，執行時是否將SGAs鎖定到實體記憶體（lock sga），啟動時是否都使用實體記憶體（pre page sga）；

Show parametetr pre_page_sga;

Show parameter lock_sga;

Alter system set pre_page_sga=true scope=spfile

alter system set sga_max_size=512M scope=spfile (重啟生效)

show parameter sga_max_size;

show parameter sga_target;

alter system set sga_target_size=400m scope=both (memory –a- s pfile)

Granule_size: sga記憶體的顆粒度大小，手工分配記憶體大小，會自動調整為顆粒度的整數倍。

若SGA小於1G，granule_size=4m

若SGA大於1G granule-size=8m (for windows) granule-size=16m(fro other)

SGA_TARGET   (Automatic shared memory management )

檢視各快取下限值：

Show parameter db_cache_size;

Show parameter shared_pool_size;

Show parameter java_pool_size;

Show parameter large_pool_size;

檢視實際大小：

Select  name,current_size from v$buffer_pool;

Select pool,sum(bytes) from v$sgastat group by pool;(檢視java，shared，large pool size)

若需改小某緩衝區的分配下限值，需先關閉ASMM, 修改完後需將所有快取下限修改回0 使ASMM能自動管理

查詢當前各池的分配大小：

 Select component,current_size from v$sga_dynamic_components ;

指定物件全表掃描時資料仍放在MRU端

  Create table aaa() cache;

 Altert table aaa cache;

修改keep，recycle池大小並指定物件使用

Alter system set db_keep_cache_size=10m scope=both

Alter system set db_recycle_cache_size=10m scope=both

Create table aaa()  storage (buffer_pool keep/recycle)

Alter table bbb storage(buffer_pool keep/recycle)

建立非標準記憶體塊大小（記憶體塊不為8k），以建非標準大小塊表空間

  Show parameter db%cache_size;

Alter system set db_16k_cache_size=10m;

Create tablespace I datafile ‘/u01/i.dat’ size 50m blocksize 16k;

檢視library cache大小

Select sum(sharable_mem) from v$db_object_cache

清空快取區：

 Alter system flush shared_pool

設定計時：

Set timing on;

檢視儲存在library cache中儲存的已分析sql語句

Select * from  v$sqltext where sql_text like ‘%dba_object%’;

Select  sql_text from v$sqlarea where sql_text like ‘%dba_object%’;

資料字典：

Select * from dict; 檢視資料字典

檢視資料庫中有哪些使用者：

Select username from dba_users;

檢視資料庫中有哪些資料檔案；

Select file_name from dba_data_files;

Select name from v$datafile;

檢視資料字典快取的大小

Select sum(sharable) from v$sqlarea;

動態表：

檢視系統中所有的動態表：

Select * from v$fixed_table;

Fixed SGA:shared pool 內的dictionary cache中的一部分與其他部分附加記憶體資訊會被例項的後臺程式所訪問，在例項啟動後會被固定在SGA中，是SGA中固定元件；