Oracle小話その１３ ＳＧＡについて

客先のシステムでのお話。

とある検索画面で、とあるSQLを実行すると、
下記のエラーが発生するという現象があったらしいです。
（らしい、というのはメーリングリストに流れてきた情報なので）


ORA-04031: 共有メモリーの？バイトを割当てできません


共有プールって、基本的には
足りなくなったら空き領域確保
するようです。
（他にもトリガーはあるみたいですけど）

で、先程のORACLEエラーですが
「足りなくなったら空き領域確保」した結果でも
「連続した領域」が割り当てできなかった場合
に起こるらしい。
要は領域のフラグメンテーションが原因。
（もしくは本当に共有メモリが足りない）

おそらくバカでかいソートなどが発生して
足りなくなったんでしょう。



というか、前にも書いたような気もするのですが
客先のSGA構成ってラージプール設定してないんですよね。
なのでUGA（ユーザ共有領域）が共有プールで使われるため
ORA-04031が発生してしまうんじゃないかなぁ…

…などと考えてたら、
「Oracleから回答が来た。
　LARGE_POOL_SIZEを設定すると良いようだ」
みたいなメーリングリストが流れてきたｗ
気付くの遅いよｗ





というわけで、
今回はSGA（システムグローバルエリア）についておさらいでもしてみますか。

まず、SGAの構成から。
①データベースバッファキャッシュ
②REDOバッファ
③共有プール
（以下はオプション）
　④ラージプール
　⑤JAVAプール


①はデータファイルから取り出したデータブロックをキャッシュします。
　サイズは以下の初期化パラメータから算出できます。
　○Oracle9i以上で、DB_CACHE_SIZEが設定されている場合
　　→DB_CACHE_SIZE
　○上記以外
　　→DB_BLOCK_BUFFERS×DB_BLOCK_SIZE

②はデータベースに対する変更情報（REDO）をキャッシュします。
　サイズは初期化パラメータLOG_BUFFERで設定します。

③はライブラリキャッシュ(共有SQL領域＋共有PL/SQL領域)や、
　データディクショナリ結果をキャッシュします。
　サイズは初期化パラメータSHARED_POOL_SIZEで設定します。

④は共有サーバ接続時のUGA（セッションメモリ）・RMANで使用する
　メモリなどをキャッシュします。
　サイズは初期化パラメータLARGE_POOL_SIZEで設定します。

⑤はJava仮想マシンで動作するJavaコードやデータをキャッシュします。
　サイズは初期化パラメータJAVA_POOL_SIZEで設定します。


メモリ管理については
細かい話をすると書ききれないので
調査用のコマンド・ビューくらいにしておきますか。

■SGAサイズの確認
SHOW SGA コマンド
V$SGAビュー
V$SGASTATビュー

■ライブラリキャッシュの確認
V$LIBRARYCACHEビュー

■共有SQL領域の統計など
V$SQLAREAビュー

■共有プール予約領域サイズの確認
V$SHARED_POOL_RESERVED

■UGAサイズの確認
V$MYSTAT

■データベースバッファキャッシュの診断と統計
V$SYSSTAT
V$SYSTEM_EVENT

■バッファキャッシュアドバイザの確認
V$DB_CACHE_ADVICE

■複数バッファプールに関する初期化パラメータ
V$PARAMETERビュー
　・db_cache_size　　　　　defaultバッファプールのサイズ。
　・db_keep_cache_size　　keepバッファプールのサイズ。
　・db_recycle_cache_size　recycleバッファプールのサイズ。

■複数バッファプールの確認
V$BUFFER_POOL
V$BUFFER_POOL_STATISTICS

■REDOログバッファの診断と統計
V$SYSSTAT
V$SYSTEM_EVENT

■ラッチの確認
V$LATCH

Oracle小話その１２ ロックについて

現在、Oracle8iとOracle9iの２系統でシステム管理してます。
こないだ Oracle8iで発生したデッドロックの調査していて気付いたのですが
8iで発生したデッドロックのトレースファイルって、
原因となったSQL文しか出力されないんですね～
（9i以降は待機中のSQL文も出力される）

ちょっとびっくり。



そういえば、デッドロックの仕組みとかはわかるけど、
オラクルマスターの試験で勉強した時は
なんだかいろんな種類のロックあったなぁ…
うろ覚えだ。



というわけで、これを機会に
ロックに関して復習することにしました。
（以下の記事はOracle9iデータベース概要、リリース2(9.2)を参考にしています）



●ロックのメカニズム
　Oracleには以下の２つのロックモードにより管理してます。
★排他ロック：同じリソースを同時に取得できないようにするためのロック。
　　　　　　　要は「今更新してますよ！」と示しているようなものか。
★共有ロック：同じリソースを同時に取得できるロック。
　　　　　　　要は、他トランザクションから排他ロックをかけられないようにするものか。


●ロックの種類
　ロックには以下の３種類があります。
・DMLロック
・DDLロック
・内部ロック


今回は「DMLロック」「DDLロック」について説明することにします。


DMLロックは
特定の行に対する「行ロック」と
表全体に対する「表ロック」の２つのレベルでロックを取得します。


まずは行ロックの特徴を見てみましょう。

●行ロック(TX)
　・DML文により自動的に取得される。
　・明示的に行ロックを取得するにはSELECT・・・FOR　UPDATEコマンドを使用する。
　・行ロックは排他ロックの一種類のみ。

では次に表ロック。

●表ロック(TM)
　・DML文・DDL文により自動的に取得される。
　　何故なら、行更新中に他のトランザクションから表定義変更や表削除されたら困るからね。
　・明示的に表ロックを取得するにはLOCK TABLEコマンドを使用する。

さらに、表ロックには以下の表ロックモードがあります。
　・行共有表ロック（ROW SHARE：RS）
　・行排他表ロック（ROW EXCLUSIVE：RX）
　・共有表ロック（SHARE：S）
　・共有行排他表ロック（SHARE ROW EXCLUSIVE：SRX）
　・排他表ロック（EXCLUSIVE：X）

同時に取得可能な表ロックモードの関係は以下の通りです。

	RS	RX	S	SRX	X
RS	○	○	○	○	×
RX	○	○	×	×	×
S	○	×	○	×	×
SRX	○	×	×	×	×
X	×	×	×	×	×

次に、各表ロックモードの説明をしていきます。


★行共有表ロック（RS）
＜特徴＞
　表ロックでもっとも緩いロックモード。
　「これから更新しますよー」、ということを示す感じ。
＜取得されるコマンド＞
　・SELECT ... FROM table ... FOR UPDATE OF ... ;
　・LOCK TABLE table IN ROW SHARE MODE;
＜他トランザクションから許可される操作＞
　・上記に示したロック
　・同じ表の行に対する問合わせ・挿入・更新・削除（※注１）（※注２）
＜他トランザクションから禁止される操作＞
　・LOCK TABLE table IN EXCLUSIVE MODE;　（LMODE=6)


★行排他表ロック（RX）
＜特徴＞
　「既に１行以上の更新を行ないましたよー」、ということを示す感じ。
＜取得されるコマンド＞
　・INSERT INTO table ... ;
　・UPDATE table ... ;
　・DELETE FROM table ... ;
　・LOCK TABLE table IN ROW EXCLUSIVE MODE;
＜他トランザクションから許可される操作＞
　・上記に示したロック
　・同じ表の行に対する問合わせ・挿入・更新・削除（※注１）（※注２）
＜他トランザクションから禁止される操作＞
　・LOCK TABLE table IN SHARE MODE;　　　　　　(LMODE=4)
　・LOCK TABLE table IN SHARE EXCLUSIVE MODE;　(LMODE=5)
　・LOCK TABLE table IN EXCLUSIVE MODE;　　　　(LMODE=6)


★共有表ロック（S）
＜特徴＞
　「ロック共有しましょうねー。でも更新しちゃ駄目ですよー」、ということを示す感じ。
＜取得されるコマンド＞
　・LOCK TABLE table IN SHARE MODE;
＜他トランザクションから許可される操作＞
　・上記に示したロック
　・同じ表の行に対する問合わせ・FOR UPDATE 文による特定行のロック（※注２）
（ここでFOR UPDATEロックできても、更新まで出来ない）
＜他トランザクションから禁止される操作＞
　・LOCK TABLE table IN ROW EXCLUSIVE MODE;　　　　(LMODE=3)
　・LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;　(LMODE=4)
　・LOCK TABLE table IN EXCLUSIVE MODE;　　　　　　(LMODE=6)



★共有行排他表ロック（SRX）
＜特徴＞
　「行共有表ロック以外認めん！更新も許さん！」、ということを示す感じ。
＜取得されるコマンド＞
　・LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
＜他トランザクションから許可される操作＞
　・上記に示したロック（行共有表ロックのみ）
　・同じ表の行に対する問合わせ・FOR UPDATE 文による特定行のロック（※注２）
　　（ここでFOR UPDATEロックできても、更新まで出来ない）
＜他トランザクションから禁止される操作＞
　・LOCK TABLE table IN ROW EXCLUSIVE MODE;　　　　　(LMODE=3)
　・LOCK TABLE table IN SHARE MODE;　　　　　　　　　(LMODE=4)
　・LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;　　(LMODE=5)
　・LOCK TABLE table IN EXCLUSIVE MODE;　　　　　　　(LMODE=6)


★排他表ロック（X）
＜特徴＞
　表ロックで最も厳しいロックモード。
　「お前は俺に触れることすらできないだろう…」（見てるだけ）、ということを示す感じ。
＜取得されるコマンド＞
　・LOCK TABLE table IN EXCLUSIVE MODE;
＜他トランザクションから許可される操作＞
　・同じ表に対する問合わせのみ
＜他トランザクションから禁止される操作＞
　・その表に対する全てのDMLを禁止。
　・その表に対する全てのロックを禁止。




それでは次にDDLロックの説明をば。
基本的には「排他」「共有」「ライブラリキャッシュ」の３種類があります。


●DDLロックの特徴
　・DDLロックは短期間だけ保持され、NOWAITモードで要求されるため
　　待機は発生しない。

★排他DDLロック（CREATE・ALTER・DROP）
　同一オブジェクト定義の同時更新による定義破壊を防止する。
　またDDL操作時は、オブジェクトに対するDMLロックも取得する。

★共有DDLロック（GRANT・AUDIT・CREATE PROCEDURE・CREATE SYNONYM・CREATE VIEW）
　同一オブジェクト定義を同時に更新しても破壊することのないDDL文は
　同時に共有DDLロックを取得できる。

★ブレーク可能解析ロック（ライブラリキャッシュの文）
　SQL文実行時の解析フェーズで取得される。
　オブジェクト変更時にライブラリキャッシュの文を無効にしたりする場合に使用される。



それでは最後に
ロックを診断するビューを説明します。

●ロック競合の診断
　★V$LOCK
　　インスタンス内のすべてのロック情報を表示
　　　TYPE=TXの場合⇒ID1列：ロールバックセグメント番号
　　　TYPE=TMの場合⇒ID1列：ロールされているオブジェクト番号

　★V$LOCK_OBJECT
　　DMLロックが適応されている表を確認できる
　　ロックを保持しているユーザーを調べるのに使用する。

　★V$SESSION
　　行ロック発生時、セッションがどの行を待機しているのかを調べるのに使用する。

　★V$SYSSTAT
　　ロック関連のシステム統計を表示




うむ、自分で言うのもなんだが
今回はなかなか良記事のような気がするぞ。
（間違ってなければね（汗））


（※注１）
　UPDATE・DELETEは、
　他トランザクションによって、競合する行ロックが保持されていない場合のみ。
　そうでない場合は待機が発生する。
（※注２）
　SELECT ... FROM table ... FOR UPDATE OF ... ;は、
　他トランザクションによって、競合する行ロックが保持されていない場合のみ。
　そうでない場合は待機が発生する。
（※注３）
　補足。Oracle8i以前の場合は、
　参照性合成制約時も表ロックが取得されていたようです（9i以降は無し）

Oracle小話その１１ 女のバトル

これOracle関係ない話かもしれませんけどｗ

ちょっと長くなってしまいましたがご勘弁を。




数年前に携わっていたシステムのお話。
（形は変わっていますが、現在も同じシステムです）

当時担当していたシステムは、
医薬業界の営業さん（ＭＲ）を
支援するシステムです。

おおまかに説明すると
自分が売り上げた実績を確認したり、どこに訪問したりとか
そんなことを管理しているわけです。

で、実績があるということは目標もあるわけで。
目標は売上目標であるので、ＭＲさん自身で入力します

とまあこんな流れ。
ここまでの話が前提。








とある日。
ある支店の女性の方から（ここで仮にＡさんとしておきます）
こんな問い合わせがありました。

「以前に目標入れたのにいつの間にか消えてます。調査して下さい。」

こんな問い合わせがあった場合
当然バッチ処理を疑います。

夜間バッチで定期的にデータを消しているバッチ処理があり、
それが悪さしているのではないか…と。



調査した結果
毎月定期的に削除するバッチ処理はあるものの、
そのバッチ処理が実行された形跡はありませんでした。

他にもバッチ処理があるのではないか…
ということでさらに調査しましたが、
それらしきものは一切見つかりませんでした。




その時は原因不明ということで
後に再現した場合、再調査ということになりました。




その後も数ヶ月に１回ほど同じ問い合わせを受けました。
そんなに頻発するものでもありませんし、
調査優先度としてはそれほど高くないということもあり
保留していたのですが…


しかし不思議なのは、
いつもＡさんからしか問い合わせがこない
ということ。
他のユーザーからは同じような現象は報告されていませんでした。
なんでだろうねー、と
みなさん顔を合わせて不思議そうにしていました。




しかしその後も問い合わせが続くため、一つ仕掛けを作ることにしました。
それは、更新対象テーブルに追加・更新・削除トリガを作成するということ。
トリガから履歴テーブルを作成し、
いつ・誰が・どんなことを行なっているかを監視することにしたのでした。
（この頃はC/Sで、個々のクライアントが直接DBサーバを見ていたため
　どのマシンの誰が使用しているかというのがキャッチできた）




その仕掛けを行なった後、しばらくしてから再度問い合わせがありました。


そこで履歴テーブルを確認してみると…
問い合わせてきたＡさんとは別な女性なのですが
同じ営業所で別な女性Ｙさん（つまりＡさんの同僚？）が
記録されていました。


このことをＡさんに伝えたところ、
「とりあえずＹさんに確認してみます」
とのことでしたので、その後の報告を待つことにしました。


しかしその後…





全く音沙汰なし








再びその現象が現れたという報告も一切なくなりました。




真相を聞いていないので、この先は推測になるのですが…


～～～～　以下　推測開始　～～～～

まず、システムにログインするには
マスタに登録されている自分のアカウントIDでログインします。
アカウントIDはメールアカウントと同じなので
一度でもメールをやり取りしたことのある相手なら
他人のIDも判明してしまいます。

そして、そのログインパスワードのデフォルトが
アカウントIDの上？桁となっているのです。
みなさん面倒くさいのか、
ほとんどの人がデフォルトのまま登録しているようです。

この話を頭に入れておいて…




Ａさんと同じ営業所のＹさんは、Ａさんのことが大嫌いでした。
Ａさんに嫌がらせを受けたのか、はたまた彼氏を寝取られてしまったのか…

真実はわかりませんが、とにかくＹさんはＡさんが気に食わない。
そこで嫌がらせしてやろうと
ＹさんはＡさんになりすましてそのシステムにログインしてみました。
案の定パスワードはデフォルトのままになっており
難なくなりすますことに成功ました。
そしてＡさんの入力した情報を改ざんしたのでした…

Ａさんは次の日、
データが消えているのに気付き酷く狼狽していました。
してやったりのＹさん。
その後も何度か繰り返して、データの改ざんを行っていました。



しかし、システム側の監視により
ＹさんがＡさんのデータを改ざんしていることが発覚し
ＡさんはＹさんを問い詰めました。
泣き崩れるＹさん。
自分の行った悪事を告白し始めたのでした…


～～～～　以上　推測終了　～～～～



という感じでしょう。多分。
ちなみにその後Ｙさんがどうなったのかは誰も知りません…

Oracle小話その１０ 落ちないセッション

現在、出向先のシステム統合に向けて
シコシコと開発しているのですが
その時のお話。


とある日、何気なーくV$SESSIONを見てみたら

OSユーザー名＝toritori（仮名）
の、身に覚えの無いセッション発見。





あるぇー、なんか接続してるのより多いぞー
と思ってログイン時間見てみたら

１ヶ月以上前なんですけど


んなアーホーなー！(゜Д゜;)





毎日ローカルマシンは落として帰ってるから
今日の日付以外のがあるわけない。

モジュールはSQL*Plusでしたが、
たしかこの日は更新とかしてなかったはず。（SELECT参照だけ）
変な落ち方（ワトソン出るやつね）とかしてなかったと思うし…

こんなん残しておいてもしょうがないから
とりあえずKILLっちゃえ！

確認してみると、ステータスがKILLEDに変わったので
そのうちSMONさんがお掃除してくれるだろうと思いながら
その日は帰ったのですが…




次の日も残ったまま



そのまた次の日も残ったまま





なんでやねーん！ヽ(`Д´)ノ



そしてさらにしばらく立ったあと、
管理者の方からお達しがありました。

「ここ１ヶ月くらい、
　定期バックアップがうまく行っていないようなので
　○○日にORACLE再起動しますね～チンチン」

１ヶ月って…

俺のせいですか？(;´Д｀)
そうなんですか？




でまあ結局再起動で消えてくれたわけですが
KILL SESSIONで消えてくれないってかなり怖いんですけど。




ちなみに、上記現象はSHARED接続時に起こるという噂がありまして…

ワタクシ以外の人も
何度かセッションが残ってしまうということがあったみたいですが
そのセッションたちもSHARED接続でした。
（その時はKILL効いたみたいですけど）


結局本当の原因はわからないんですが
軽いSHARED接続恐怖症になってしまいましたｗ

みなさんも、変なセッションが残っていたら
専用接続か共有接続かを確認してみると良いかも？

Oracle小話その９ CHARとVARCHAR2の違い

なんですかねぇ、この基本的な標題。
というのも、GOLDホルダーであるこのワタクシ…


なんと

間違って覚えておりました！
Σ(￣□￣)ガーン





VARCHAR2に全部空白（半角スペース）のデータを
追加できないなんて、
間違った認識を植え付けたのは誰だー！
（Ｋ泉さんかＴ須さんがあやしい）




よくよく考えたらそんなわけないもん。
何でこんな風に覚えてたんだろう…






そんなわけなので、
おさらいしてみましょう。




基本的な概念としては

　CHAR　　　→　固定長
　VARCHAR2　→　可変長

ですね。
ここまではよいですね。
CHARは不足バイト分を半角スペースで埋めます。
VARCHAR2はもちろんそんなことせずに、入力したデータがそのまま格納されます。




以下を例にとって見てみましょう。

SQL> create table toritori(col1 char(5), col2 varchar2(5));

表が作成されました。

SQL> desc toritori
 名前                NULL?    型
 ------------------- -------- -----------------
 COL1                         CHAR(5)
 COL2                         VARCHAR2(5)

SQL> insert into toritori values('TR1', 'TR2');

1行が作成されました。

SQL> select '('||col1||')' col1 , '('||col2||')' col2 from toritori;

COL1    COL2
------- -------
(TR1  ) (TR2)

COL1はCHAR型なので、勝手にスペースが埋まっていますね。



それでは、今度はVARCHAR2にもスペースを付加してみます
（これすらできないと思い込んでいた…アホか）

SQL> update toritori set col2='TR2  ';

1行が更新されました。

SQL> select '('||col1||')' col1 , '('||col2||')' col2 from toritori;

COL1    COL2
------- -------
(TR1  ) (TR2  )

結果はこうなります。
スペースを付加したら、そのデータが入力されました（当たり前）。





格納されるデータについてはこんなところですね。
それでは次に、これらのデータを検索する時の注意点です。


まず、以下の２つのセマンティクスがあることを覚えましょう。

　①非空白埋め比較セマンティクス
　　比較する文字列のどちらかが（もしくはどちらも）VARCHAR2の場合

　②空白埋め比較セマンティクス
　　比較する文字列のどちらもCHAR（またはリテラル）の場合

①の場合、半角スペースは見た目そのままの扱いで検索されます。
②の場合、半角スペースは無視されたように検索されます。




以上を踏まえて、
例を見てみましょう。（先程のデータを使うことにします）

SQL> select '('||col1||')' col1 , '('||col2||')' col2 from toritori;

COL1    COL2
------- -------
(TR1  ) (TR2  )

まずは、①の場合。（VARCHAR2の比較）

SQL> select '('||col1||')' col1 , '('||col2||')' col2 from toritori
  2  where col2='TR2';

レコードが選択されませんでした。

SQL> select '('||col1||')' col1 , '('||col2||')' col2 from toritori
  2  where col2='TR2  ';

COL1    COL2
------- -------
(TR1  ) (TR2  )

上記の結果を見てわかる通り、半角スペースが認識されて検索しています。




次に、②の場合。（CHARの比較）

SQL> select '('||col1||')' col1 , '('||col2||')' col2 from toritori
  2  where col1='TR1';

COL1    COL2
------- -------
(TR1  ) (TR2  )

↑これがＨＩＴしてしまう！



SQL> select '('||col1||')' col1 , '('||col2||')' col2 from toritori
  2  where col1='TR1  ';

COL1    COL2
------- -------
(TR1  ) (TR2  )

はい。どちらもHITしています。
半角スペースが無視されていることがわかりますね。
（こうしてみると、CHARに入っている半角スペースって
　空気みたいなものだな～）





比較セマンティクスについては
こちらのサイトで詳しく説明されています↓
http://biz.rivus.jp/string_comparison_semantics.html

　＊ちなみに、上記サイトで
　「'1' = '1     ' は成立するか？」
　という検証を行なっています。
　実際行なってみると、以下の結果となりました。
　　①SELECT '式は真です' x FROM DUAL WHERE '1' = '1     ' ;　○
　　②SELECT '式は真です' x FROM DUAL WHERE 1 = '1     ' ;　○
　　③SELECT '式は真です' x FROM DUAL WHERE 1 = '     1' ;　○
　　④SELECT '式は真です' x FROM DUAL WHERE '1' = '     1' ;　×

　④の比較は無かったので、自分で検証してみました。
　（結果は×）






そして次に、データ量・パフォーマンスについて。

データ量については
ORACLEMASTERの黒本を参照すると
「VARCHAR2→容量を節約できる」
と書いてあるのですが、
ネットで彷徨ったところ、いろんな意見が飛び交っておりまして
結局どの説が本当なのかわからずじまいですｗ
こちらなど参照↓
http://www.geocities.jp/kimura804/rdb/InterBase/ip_ib_strings_j.htm


まあ、スペース分節約できてるんだから
黒本が正しいのでしょう。
うん、そう考えよう（適当）


パフォーマンスですが、
これもネットで調べたところ
「CHARの方がパフォーマンスがよい」
という意見が多かったです。

なのでこれ採用（適当）。
たしかに、固定長の方が早そうだし。




そして最後に、
結局どちらを使うべきか？（というか使い分ける場合があるか）


そうですね、VARCHAR2を使っておきましよう。
こっちの方が推奨されているみたいですし。
本当に固定長のデータしか入らないのであれば
CHARでもいいと思いますけどね。
でもそんなにナーバスになる必要は
ないのではないかと。





とりあえず今回はこんな所です。
CHARとVARCHAR2の違いはわかりましたか？

みなさんも間違った知識をひけらかさないように
気をつけましょう！
（俺やっちゅーの）