tech_memo/ORACLE/Memory

今日の20件

counter: 1267
today: 2
yesterday: 1
online: 1

tech_memo/ORACLE

PGAたっぷりなのに、一時表領域が使用（Diskソート）が発生する
- _pga_max_sizeの値の決定方法
ORA-04030
メモリの種類
PGA・UGA 使用量
- DB全体の使用状況確認
- 指定ユーザの使用量確認
バッファキャッシュの中身確認
自動メモリ管理
自動共有メモリ管理 (ASMM)
手動共有メモリー管理
Streamsプールの見積もり

PGAたっぷりなのに、一時表領域が使用（Diskソート）が発生する †

そもそも1プロセスがソートに利用できるメモリサイズ(SQL work area size)は？
- 10gR1まで
```
MIN( pga_aggregate_target * 0.05, _pga_max_size * 0.5)
```
- 10gR2から
```
MIN( pga_aggregate_target * 0.20, _pga_max_size * 0.5)
```
- 参考 : http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2010/05/pga_aggregate-7.html
- _pga_max_sizeは隠しパラメータ。隠しパラメータの確認方法は⇒ tech_memo/ORACLE/hidden_parameter
Insight Technologyのこのへん関連のブログ
- https://www.insight-tec.com/mailmagazine/ora3/vol188.html

_pga_max_sizeの値の決定方法 †

http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2010/09/pga_aggregate-3.html

ORA-04030 †

参考 †

http://www.cosol.jp/tech/detail/ot0039_ora04030.shtml

概要 †

OracleがOSからメモリを割当しようとしたときに、失敗すると発生するエラー
下記サンプル。バイト数と、カッコ内のメッセージは、エラー発生時の処理内容によって異なる
```
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pmuccst: adt/record)
```

発生要因 †

Oracle Databaseが動作するマシンで割当可能メモリが不足した
オペレーティング・システムの制限で割り当てに失敗した
Oracle の Bug
1 つのOracleサーバプロセスにマッピングできるPGA上限に抵触している（後述）

1 つのOracleサーバプロセスにマッピングできるPGAの上限 †

参考
- http://m.blog.itpub.net/38267/viewspace-716196/

割当て可能なPGAのサイズ

(割り当てる領域のサイズ) X (割り当て可能な領域の数)

割り当てる領域のサイズ
- データベースの以下の隠しパラメータで制御されており、デフォルトは 64KB
```
realfree_heap_pagesize_hint  ※12.1 から _realfree_heap_pagesize
```
- 現在の隠しパラメータの設定は、以下の SQL 文で確認することができる。
```
SQL> select a.ksppinm Parameter,b.ksppstvl Value
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '%_realfree_heap_pagesize_hint%';
```
- 初期化パラメータ(SPFILE)で変更可能
```
_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144
```

割り当て可能な領域の数
- Linux では以下の OS カーネル・パラメータで制御されており、デフォルトは 65,536
```
sysctl -a | grep max_map_count
```
- 変更方法
```
sysctl -w vm.max_map_count=XXX
```

メモリの種類 †

参考 : https://www.insight-tec.com/mailmagazine/ora3/vol037.html
SGA（SYSTEM GLOBAL AREA）
- 1つのOracleインスタンスのデータと制御情報が入る共有メモリ。データベースバッファ、共有プール、REDOログバッファなどが含まれる。
PGA（PROGRAM GLOBAL AREA）
- セッションを確立すると、サーバプロセスごとに確保される領域。データや制御情報が含まれる非共有メモリ。
UGA（USER GLOBAL AREA）
- セッション単位に１つのUGAが存在する。MTS接続の場合SGA内、専用サーバ接続の場合PGA内に確保される領域。
CGA (CALL GLOBAL AREA)
- MTS接続、専用サーバ接続に関わらず、PGA内に保持される。セッションの変数、配列などを含むスタック領域が確保される。

PGA・UGA 使用量 †

DB全体の使用状況確認 †

V$SESSTATにセッション(SID)ごとのPGAに関する情報が格納されている

ただし、v$SESSTATには、
統計名がないので、v$STATNAME
で統計名を指定して、V$SESSTATと結合する

           set linesize 200
           set pagesize 30
           col time for a25

           select 
               to_char(systimestamp, 'YYYYMMDD-HH24:MI:SS') as time, 
               pga.TOTAL_USED_PGA_MB, 
               uga.TOTAL_USED_UGA_MB, 
               pga_max.MAX_USED_PGA_MB, 
               uga_max.MAX_USED_UGA_MB
           from (
               select sum(value)/1024/1024 as TOTAL_USED_PGA_MB from v$sesstat aa, v$statname bb
                   where aa.statistic# = bb.statistic# and bb.name = 'session pga memory'
           ) pga,
           (
               select sum(value)/1024/1024 as TOTAL_USED_UGA_MB from v$sesstat aa, v$statname bb
                   where aa.statistic# = bb.statistic# and bb.name = 'session uga memory'
           ) uga,
           (
               select sum(value)/1024/1024 as MAX_USED_PGA_MB from v\$sesstat aa, v\$statname bb
                   where aa.statistic# = bb.statistic# and bb.name = 'session pga memory max'
           ) pga_max,
           (
               select sum(value)/1024/1024 as MAX_USED_UGA_MB from v$sesstat aa, v$statname bb
                   where aa.statistic# = bb.statistic# and bb.name = 'session uga memory max'
           ) uga_max
           ;

指定ユーザの使用量確認 †

一発目は重い。

set linesize 200
set pagesize 30
col username for a10

select  
   a.sid,
   a.username,
   a.status,
   b.value/1024/1024 "USED_PGA_MB",
   d.value/1024/1024 "USED_UGA_MB"
from 
   v$session a,
   (select aa.sid, aa.value from v$sesstat aa, v$statname bb
   where aa.statistic# = bb.statistic# and bb.name = 'session pga memory') b,
   (select ee.sid, ee.value from v$sesstat ee, v$statname ff
   where ee.statistic# = ff.statistic# and ff.name = 'session uga memory') d
where a.sid = b.sid and
   b.sid = d.sid and
   a.username = UPPER('<ユーザ名>')
order by a.username, a.sid;

バッファキャッシュの中身確認 †

参考 : https://ameblo.jp/archive-redo-blog/entry-10036131078.html

下記は、オブジェクト単位の、バッファキャッシュに乗っているブロック数

select b.obj, o.object_name, count(*) blocks, b.lru_flag, b.tch
 from x$bh b
 left outer join dba_objects o
   on b.obj = o.object_id
 group by b.obj, o.object_name, b.lru_flag, b.tch

x $bhに対するSELECT権限がない場合は、v$ bhでも類似する情報が取得可能

select o.owner, o.object_name, count(*) number_of_blocks, count(*)*8196/1024/1024 as SIZE_MB
   from dba_objects o, v$bh bh
   where o.data_object_id = bh.objd and o.owner = 'HOGE'
       and bh.status != 'free' and bh.ts# > 0
   group by o.owner, o.object_name
   order by 3;

LRU_FLAG
- 該当ブロックがLRU（Last Recent Used）リストの中でどのように見られているかを表すフラグ
  - 8 : 残留候補
  - 2 : 追い出し候補
  - 0 : どちらでもない
TCH
- 該当ブロックがキャッシュ上で利用された回数
上記SQLのcount(*)に、初期化パラメータのdb_block_sizeを掛けることで、バッファキャッシュに乗っているサイズを計算することもできる

自動メモリ管理 †

Oracle全体のメモリ（SGA+PGA)自動管理
以下の初期化パラメータを設定する
- MEMORY_TARGET > 0
- MEMORY_MAX_TARGET > 0
この時、SGA_TARGET != 0を設定していると、この値がSGAの最小値となる

自動共有メモリ管理 (ASMM) †

SGAとPGAの値を設定し、SGAおよび、PGA内の割り当ては自動管理する
以下の初期化パラメータを設定する
- 自動メモリ管理OFF
  - MEMORY_TARGET = 0
- 自動共有メモリ管理ON
  - SGA_TARGET > 0
  - SGA_MAX_SIZE > 0
  - PGA_AGGREGATE_TARGET != 0 ( => WORK_AREA_SIZE_POLICY=AUTOに設定される)

手動共有メモリー管理 †

SGA内の様々なPoolサイズを手動設定する
以下の初期化パラメータを設定する
- 自動メモリ管理OFF
  - MEMORY_TARGET = 0
- 自動共有メモリ管理OFF
  - SGA_TARGET = 0
  - PGA_AGGREGATE_TARGET = 0 ( => WORK_AREA_SIZE_POLICY=MANUALに設定される)
- 手動メモリ設定(SGA関連)
  - DB_CACHE_SIZE > 0
  - SHARED_POOL_SIZE > 0
  - LARGE_POOL_SIZE > 0
  - JAVA_POOL_SIZE > 0
  - STREAMS_POOL_SIZE >= 0 (0の場合必要に応じてバッファキャッシュからStreamsプールに転送)
- 手動メモリ設定(PGA関連(*_AREA_SIZE))
  - BITMAP_MERGE_AREA_SIZE > 0
  - CREATE_BITMAP_AREA_SIZE > 0
  - HASH_AREA_SIZE > 0
  - SORT_AREA_SIZE > 0

Streamsプールの見積もり †

V$STREAMS_POOL_ADVICE動的パフォーマンス・ビューで確認

列	説明
STREAMS_POOL_SIZE_FOR_ESTIMATE	見積り用のOracle Streamsプールのサイズ（MB単位）が表示されます。このサイズの範囲は、Oracle Streamsプールの現在のサイズより小さい値から、Oracle Streamsプールの現在のサイズより大きい値で、増分ごとに別々の行が表示されます。Oracle Streamsプールの現在のサイズを示すエントリが必ず存在し、常に20の増分があります。増分の範囲とサイズは、Oracle Streamsプールの現在のサイズによって異なります
STREAMS_POOL_SIZE_FACTOR	Oracle Streamsプールの現在のサイズに関連した見積りのサイズ係数が表示されます。たとえば、サイズ係数.2は、見積りがOracle Streamsプールの現在のサイズの20%に相当することを示し、サイズ係数1.6は見積りがOracle Streamsプールの現在のサイズの160%に相当することを示します。サイズ係数が1.0の行には、Oracle Streamsプールの現在のサイズが表示されます。
ESTD_SPILL_COUNT	問合せによって返されるSTREAMS_POOL_SIZE_FOR_ESTIMATEおよびSTREAMS_POOL_SIZE_FACTORごとに、メモリーからオーバーフローしてキュー表に入れられるメッセージの見積り数が表示されます。
ESTD_SPILL_TIME	問合せによって返されるSTREAMS_POOL_SIZE_FOR_ESTIMATEおよびSTREAMS_POOL_SIZE_FACTORごとに、メッセージがオーバーフローしてキュー表に入れられるまでの見積り経過時間（秒単位）が表示されます。
ESTD_UNSPILL_COUNT	問合せによって返されるSTREAMS_POOL_SIZE_FOR_ESTIMATEおよびSTREAMS_POOL_SIZE_FACTORごとに、オーバーフローしたメッセージがキュー表からメモリーに戻される見積り数が表示されます。
ESTD_UNSPILL_TIME	問合せによって返されるSTREAMS_POOL_SIZE_FOR_ESTIMATEおよびSTREAMS_POOL_SIZE_FACTORごとに、オーバーフローしたメッセージがキュー表からメモリーに戻されるまでの見積り経過時間（秒単位）が表示されます。

http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05775-02/strms_otmon.htm#i1020290

今日の20件

counter: 1267
today: 2
yesterday: 1
online: 1

tech_memo / ORACLE / Memory

最新の20件

今日の20件

PGAたっぷりなのに、一時表領域が使用（Diskソート）が発生する †

_pga_max_sizeの値の決定方法 †

ORA-04030 †

参考 †

概要 †

発生要因 †

1 つのOracleサーバプロセスにマッピングできるPGAの上限 †

メモリの種類 †

PGA・UGA 使用量 †

DB全体の使用状況確認 †

指定ユーザの使用量確認 †

バッファキャッシュの中身確認 †

自動メモリ管理 †

自動共有メモリ管理 (ASMM) †

手動共有メモリー管理 †

Streamsプールの見積もり †

最新の20件

今日の20件