Oracleでデータを消してしまったあなたへ。"フラッシュバッククエリ"

■データを誤って消してしまった・・・

Oracleでそんな経験あると思います。背筋が凍ります。

実はDML（INSERT、UPDATE、DELETE）のSQLを誤って実行し、さらにCOMMITしてしまった場合でも、

UNDOデータを参照して復活させることができます。

これをフラッシュバッククエリ(Flashback Query)といいます。

・3分前のデータを参照

select * from hoge
as of timestamp (systimestamp - interval '3' minute );

・1時間前のデータを参照

select * from hoge
as of timestamp (systimestamp - interval '1' hour );

・1日前のデータを参照

select * from hoge
as of timestamp (systimestamp - interval '1' day );

・指定した日付、時間のデータを参照

select * from hoge
as of timestamp　to_timestamp('2013-09-07 09:30:00', 'yyyy-mm-dd hh:mi:ss');

・SCNからデータを参照

select * from hoge

as of scn (18416710);

またWhere句で指定したデータを参照することも可能です。

リカバリする際は、INSERTすればいいのですが、
複数のテーブルをリカバリする際には、CTAS(Create Table As Select)でリカバリテーブルを作ることをオススメします。

create table hoge_recovery as
select * from hoge
as of timestamp (systimestamp - interval '3' minute );

INSERTだともちろんUNDO表領域を使いますので、
一つ目のテーブルをリカバリしている間に二つ目のテーブルのUNDOデータが無くなってしまう可能性があります。
CTASならUNDO表領域を使わないので、まずはリカバリデータの確保をして、
そのあとゆっくりINSERTしましょう。

Oracle統計情報のPENDING＆PUBLISH機能

■Oracleの統計情報の取得は恐い・・・

筆者はSQLのパフォーマンスチューニングをする際、まずは対象のオブジェクトの統計方法の状態から確認します。
というのも、過去に自動取得機能のせいで毎日SQLのパフォーマンスが劣化し大変な思いをしたことがあり、それ以来自動取得機能をOFFにしているのです。

その為、手動で統計情報を収集しなおすことがよくあるのですが、
収集後すぐに統計情報が反映してしまうので、さらに劣化するということも多々ありました。

一度テストしたいな～と常々思っていたら、ありました。そんな機能が。

■PENDING＆PUBLISH

Oracle Database 11gから、統計情報に"保留"="PENDING"と、"公開"="PUBLISH"という制御が新たに追加されました。

デフォルトではすぐに公開する設定（TRUE）ですが、
FALSEにしておくと、収集された統計情報は一旦保留されます。
以下ではUSERユーザーのTAB表の統計情報を保留状態で収集し、一度テストをしてから公開する流れを書いていきます。

①現在の状況を確認

select dbms_stats.get_prefs('PUBLISH','user','tab') from dual; DBMS_STATS.GET_PREFS('PUBLISH','user','tab') ------------------------------------------- TRUE

②TAB表のPUBLISHをFALSEに変更

exec dbms_stats.set_table_prefs('user', 'tab', 'PUBLISH', 'FALSE'); PL/SQLプロシージャが正常に完了しました。

テーブルに反映されたかどうか再度①のSQLで確認

select dbms_stats.get_prefs('PUBLISH','user','tab') from dual; DBMS_STATS.GET_PREFS('PUBLISH','user','tab') ------------------------------------------- FALSE

③統計情報収集

exec dbms_stats.gather_table_stats(ownname=>'user',tabname=>'tab'                                            ,estimate_percent=>100,cascade=>TRUE); PL/SQLプロシージャが正常に完了しました。

OracleバッファキャッシュのKEEP POOLは危険！？

■バッファキャッシュについて

まずはOracleのバッファキャッシュについて説明します。

Oracleのバッファキャッシュに頻繁に使用するデータを格納することによって、物理I/Oを減らすことができます。このバッファキャッシュをいかに効率的に使えるかが、Oracleデータベース全体のパフォーマンスチューニングのキモとなるわけです。

バッファキャッシュには効率的にデータを格納するための機能、仕組みがいくつかあるのですが、その中に「マルチバッファ・プール」というものがあります。

これは、バッファキャッシュを３つのプールに分け、それぞれ適したデータを適したプールに割り当てることで、無駄なくバッファキャッシュを使うことができるというものです。

　●３つのプール

  1.DEFAULT 

　　⇒通常のバッファキャッシュ

　 　　サイズの初期化パラメータ ： DB_CACHE_SIZE

  2.KEEP

　　⇒利用頻度の高く、できるだけバッファキャッシュ上に残したいデータを格納する為のプール

　　　 プールサイズは対象オブジェクトサイズより大きい必要がある

　 　　サイズの初期化パラメータ ： DB_KEEP_CACHE_SIZE

  3.RECYCLE

　　⇒利用頻度が低く、フルスキャンがよく行われるデータを格納する為のプール

　 　　サイズの初期化パラメータ ： DB_RECYCLE_CACHE_SIZE

この３つのプールのどれに割り当てるかは、オブジェクト毎に設定する必要があります。

（確認例）

　　SELECT TABLE_NAME , BUFFFER_POOL

　　FROM DBA_TABLES

　　WHERE TABLE_NAME = [テーブル名] 

（変更例）

　　ALTER TABLE [テーブル名] STORAGE(BUFFER_POOL [DEFAULT/KEEP/RECYCLE])

ざっくりですが、説明は以上です。

この内、KEEP POOL（KEEPキャッシュ）に関して、どれくらい有効か検証していたのですが、

その際にとんでもない事件が発生しました。

Oracle ASMの概念を簡単にまとめてみた

■Oracle ASMまとめ

今日はOracle Database 10g以降から搭載されている、自動ストレージ管理機能 Oracle ASM (Automatic Storage Management)を簡単にまとめてみようと思います。

ASMの役割を三行で言ってしまうと
　１．複数のストレージを一つのボリュームとして扱い、ディスクへのI/Oを分散する
　２．オンラインでのディスク追加を可能にする
　３．冗長構成を実現する
です。


１．複数のストレージを一つのボリュームとして扱い、ディスクへのI/Oを分散する

複数の物理ストレージを一つの仮想ストレージプール（ASMディスクグループ）として扱います。
そのASMディスクグループを細分化したASMディスクに、均等にファイルを格納します。
これによって、ASMディスクの使用量とアクセス頻度を均一にし、I/Oの偏りやパフォーマンス劣化を防ぎます。

これを実現している機構を簡単に説明します。
ASMを利用する際、Oracle Databaseのインスタンスとは別にASMのインスタンスが起動します。
それがエクステントマップ（どのASMディスクにデータがあるかを示すマップ）をSGA領域上に定期的に構築します。
そのエクステントマップを基にDatabaseインスタンスがI/O処理をするわけです。

※ポイントは、I/O処理の際に、毎回ASMインスタンスに問い合わせをしているというわけではないという点です。あくまでSGA上のデータを読み取るだけなので、パフォーマンスへの影響は少なくて済みます。

Oracle Client 8.0.5でEXP80をインストールする方法

■古いDBサーバを扱っている方々へ・・・

もはやこのご時世にこの情報は必要なのでしょうか・・・

ただ私のように古いDBサーバの管理を押し付けられている人もいるはずなので、

何かのお役に立てばこれ幸いです。

Oracle Database 8.0.*のデータを、別サーバからEXPORTする際、

Oracle Client 8.0.*が必要です。

余談ですが、Oracle8iのEXPORTコマンドは

　　8.0.*　　　　⇒　EXP80

　　8.1.*以降　 ⇒　EXP

で、両者に互換性はありません。

そのため、必然的にOracle Client 8.0.*が必要になります。

筆者は先日Oracle Client 8.0.5をWindows2000Severにインストールし、

（本当は8.0.5のサポートはWindowsNTまでなのですが・・・）

コマンドプロンプトにてEXP80コマンドをバシッと叩きましたが、

「コマンドが見つかりません」

■カスタムインストールをしましょう

原因はインストール時のOptionの選択です。

筆者は「Select Installation Option」画面の際に、

　"Oracle8 Client"

を選択していました。

どうやらこれではbinフォルダ下にEXP80.exeはインストールされないようです。

Oracleで"－"（全角マイナス）が文字化けしない！？

■文字化けするはずなのに・・・

先日、仕事中にある奇妙なことに気が付きました。

新しく構築したOracle DatabaseのキャラクタセットをUTF-8にしてみて、

いろいろ検証していたところ、

「文字化けするだろう」と思っていた全角記号が文字化けしないのです！

例えば"－"（全角マイナス）です。

① MS932 を Unicodeに変換

      817C　→　FF0D　

② SJIS を Unicodeに変換

      817C  →  2212

このマッピングの違いによって、Javaプログラムなどで文字化けする問題が発生します。

UTF-8のDBなので、②の変換によって登録された"－"（全角マイナス）は、

Windowsでは文字化けしてしまうだろう(※)と思ったのですが、

SQL*PLUSで確認すると正しく表示されてしまうのです。

※2212にあたる文字がMS932には無い為

そこで次のような検証をしてみました。

OracleにおけるSQLとは何ぞや？

■SQLとは何ぞや？

基本をおさらいしましょう。
OracleにおけるSQLとは何でしょうか？

簡単でいうと
Oracleのデータベースの中にあるデータを操作したり、データベースの構成を変更したりする命令文です。
別にOracle固有の言語というわけではなく、MySQLやPostgreSQLなどでも使われます。
（筆者はOracleしか扱ったことがないので、これ以降Oracleのことを書いています。）

■SQLの種類

DML(Data Manipulation Language)

  SELECT　・・・　データを検索する

  INSERT　・・・　データを追加する

  UPDATE　・・・　データを更新する

  DELETE　・・・　データを削除する

DDL(Data Definition Language)

  CREATE　・・・　オブジェクトを作成する

  DROP　・・・　オブジェクトを削除する

  ALTER　・・・　オブジェクトの提示を変更する

  GRANT　・・・　ユーザ権限を付与する

  REVOKE　・・・　ユーザ権限を削除する

■SQLの処理ステップ

(1)PARSE　＝　SQLの解析

　　・構文に間違いがないかチェックする

　　・指定されたオブジェクトが存在するかチェックする

　　・実行計画を決定する

(2)BIND　＝　変数のバインド

　　・バインド変数を指定している場合、値を代入するためのメモリ領域を確保する

(3)EXECUTE　＝　SQLの実行

(4)FETCH　＝　行のフェッチ

　　・SELECT文の場合のみ、ユーザに問い合わせ結果を返す

SQLの基本的な説明は以上です。

これ以外にもいろいろありますが割愛します。

■暴れん坊SQL対策

紹介してきたSQLですが、実は暴れん坊なんです。

Cardinality Feedback（カーディナリティ フィードバック)とは

■同じSQLなのに実行計画が変わる！？

先日、非常に重たいSQLのチューニングを行うべく様々なテストを行っていた中で、
少し不思議な出来事がありました。

全く同じSQLを２連続で実行すると、実行計画が変わるのです。
全く同じなのだから同じ実行計画で実行されてほしいのですが、
やはりOracleのオプティマイザはそう簡単に思ったとおりに動いてはくれません・・・

よくよく調べてみると、Oracle11gR2からの新機能「Cardinality Feedback」が関係していることがわかりました。

■Cardinality Feedback（カーディナリティ　フィードバック)とは

実行計画を生成するオプティマイザのインプット情報に、実データのカーディナリティ（データの種類の多さ）情報を加えることによって、より最適な実行計画を生成する機能です。

ハードパース時、統計情報と実データのカーディナリティに乖離がある場合、
そのカーディナリティを記録し、その後に実行される全く同じSQLの実行計画は、実データを加味して最適になるように変更されます。

SQL*PLUSで"&"（アンパサンド）を使いたい

■SQL*PLUSで"&"を使いたい・・・

先日SQL*PLUSで実行するSQL文の中に"＆"（アンパサンド）を入れたいことがありました。

具体的にはカラムにURL文字列をそのまま格納したかったんです。

INSERT INTO table VALUES ('xxx','yyy','http://abcde.com/main.jsp?abc=ABC&def=DEF');

といった感じです。

しかし、Oracle先生はこんな質問をしてきます。

 defに値を入力してください:

いやいやそういうことではないんですよ・・・

■"&"を無視してもらう

"&"（アンパサンド）を無視してもらう方法は簡単です。

 SET DEFINE OFF

これだけです。

SQL*Plusにおいて"&"はデフォルトの置換変数として定義されています。

その定義をOFFにするおまじないです。

そもそも置換変数とは、SQL内の文字列を指定された内容に置き換えることができる変数です。
シェルスクリプトなどで引数として渡すことによって内容をします。

例えば、select.sqlというファイルに
　select * from emp where name = '&1';
と書かれており、
　
　SQL> @select.sql

と実行すると、


　1に値を入力してください:


となります。
そこで以下のように引数を与えてあげると

　SQL> @select.sql 山田
　旧   1: select * from emp where name = '&1'
　新   1: select * from emp where name = '山田'
　
　NAME
　----------
　山田


と、めでたく&1の文字列が置換されました。