統計情報_問い合わせ処理の実行
実行計画と実行統計、経過時間を表示する。
SQL>SET AUTOTRANCE TRACEONLY
SQL>SET TIMING ON
SQL>SELECT * FROM emp1;
(結果省略)
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE;
AUTOTRACEの統計名称 説明
・recursive calls 再帰的にコールの実行回数。oracle内部処理のためのSQLが生成され、これのコールが必要となる場合がある。このようなコールを再帰的コールと呼ぶ。
・db block gets 更新目的のブロック要求回数
・consistent gets 読み取り目的のブロック要求回数
・physical reads ディスクからのブロック読み取りの合計数
・redo size 生成されたREDOの合計（バイト単位）
・bytes sent via SQL *Net to client Oracleからクライアントアプリケーションへ送信された合計バイト数
・bytesreceived via SQL *Net from client Oracleがクライアントアプリケーションから受信した合計バイト数
・SQL *Net round-trips to/from client Oracleとクライアントとの間で送受信されたメッセージの合計数
・sorts(memory) 完全にメモリ内で実行され、ディスク書き込みを必要としなかったソート操作の数
・sorts(disk) 1回以上のディスク書き込みを必要としたソート操作の数
・rews processed 操作中に処理された行数
Oracle でのSQL処理の流れ
クライアントアプリケーションから発行されたSQLは、サーバープロセスが処理します。
サーバープロセスが行うSQL処理は大きく分けるといかの3つです。
1.SQL解析
共有プール確認
↓（共有プールに解析済みSQL情報が存在せず）
SQL検証
↓
実行計画作成
↓
解析済みSQL情報を共有プールへ格納
↓
2.SQL実行
↓
3.データベースバッァファキャッシュのブロックから行を取得
↓
終了
共有プールの確認
サーバープロセスは受け取ったSQLと同じSQLの解析結果が共有プールに存在しないか確認。存在した場合は、その解析済みSQL情報を使用することで「SQL検証」と「実行計画の作成」の２つの処理をスキップし、SQLを実行します。（ソフトパース）
実行計画作成
SQL検証をパスし、実行可能と判断された有効なSQLに対して、SQLの実行に先立ち「実行計画」を作成します。SQLには具体的な実行手順を指定できない為、Oracleが実効計画を作成して、具体的な実効手順を決定する必要があります。
代表的なアクセスパス
- テーブルスキャン（TABLE ACCESS FULL）：テーブルないの全ての行を読み取る
- ROWIDスキャン（TABLE ACCESS BY ・・・ROWID）:ROWIDを用いて、テーブルから対応する行を読み取る
- 索引一意スキャン（INDEX UNIQUE SCAN）:一意索引から、等価条件に合致した1つの行に対応する1つのROWISを読み取る
- 索引レンジスキャン（INDEX RANGE SCAN）：索引から、範囲条件に合致した1つ以上の行に対応する1つ以上のROWIDを読み取る
Oracleの結合方法
- ネステッドループ結合（NESTED LOOPS）:結合対象のテーブル内のそれぞれの行を総当たりで調べて結合する
- ハッシュ結合（HASH JOIN）:結合対象の列のハッシュ値を利用して結合する
- ソート/マージ結合（MARGE JOIN）:行をソートしてから結合する
CBOとオプテイィマイザ統計
実行計画を作成するのは、サーバープロセスに組み込まれた（CBO（Cost-Based Optimizer:コスト・ベース・オプティマイザ））と呼ばれる処理モジュールです。SQLがアクセスするオブジェクトのオプティマイザ統計をもとに実行計画を作成する。
Oracle10g以降はディスクI/OとCPUを見積もった評価である。統計情報はテーブルの行数や、行の平均サイズ、索引の有無、Bツリー索引の高さ、列値のばらつき具合など、実際に格納されているデータの状態を集約したさまざまな統計情報のこと。
統計情報はデータディレクショナリに保存される。
なお、解析済みSQL情報はSGA内の共有プールに格納されるので、インスタンス停止すると解放されます。また、共有プールはLRUで管理されているので、長い時間使用されていない解析済みSQL情報は、共有プールから消去される場合があります。
Oracle_SQLチューニング
SQLチューニングを勉強することになりました。
以下のようなルールがあるらしい。他にもあるか調らべなきゃな。
where句の条件指定時は索引列に関数を指定しない
指針：where句の条件を指定する場合、索引列に対して右辺の値に対して適用可能であるかを検討してください
理由：索引列に対して関数を指定すると、索引が使用されなくなる。
参考資料：OracleマニュアルSQLチューニングの概要
外部制約・外部キー制約
表のキーの値について、関連する表のキーの値と一致する場合にデータとして許可する。
表と表の論理的な関連付けを行い外部キーに対して値（親キー）が存在する事を保証する。
外部キーには、親表の値もしくはNULLをもつことが可能。
外部キーの親キーは主キーもしくは一意キーでなければならない。
親レコードと子レコードの関係で、レコード削除テストをしてみた。
/* 部門マスタ */
create table bumon(
code char(2) primary key,
bname varchar2(10)
);/* 社員マスタ */
create table syain(
id char(5) primary key,
uname varchar2(10),
bcode char(2)
);insert into bumon values('01', '1部');
insert into bumon values('02', '2部');
insert into bumon values('03', '3部');insert into syain values('10001', '社員1', '01');
insert into syain values('10002', '社員2', '01');
insert into syain values('10003', '社員3', '02');
insert into syain values('10004', '社員4', '02');
insert into syain values('10005', '社員5', '03');
insert into syain values('10006', '社員6', '03');
commit;
オブションによる違いを確認
- on句なし
--FK作成
alter table syain add constraint fk foreign key(bcode) references bumon(code);
--code03を削除してみる。
delete from bumon where code = '03';
--エラーが発生した。参照性制約違反とのこと。
子レコードが存在する場合、親レコードは削除できないのですね。
ORA-02292: integrity constraint (SQL_KATKKKGUONSSHTYWBKZNOREUP.FK) violated - child record found
--一旦、制約削除
alter table syain drop constraint fk;
- on delete set null
--制約作成
alter table syain add constraint fk foreign key (bcode)
references bumon (code) on delete set null;
delete from bumon where code='03';
1 row(s) deleted.--親レコードが削除できた^^
親テーブルと子テーブル確認。親テーブル削除すると子テーブルのレコードはnullになっている。
select * from bumon;
select * from syain;
CODE BNAME
01 1部
02 2部
2 rows selected.
ID UNAME BCODE
10001 社員1 01
10002 社員2 01
10003 社員3 -
10004 社員4 -
10005 社員5 -
10006 社員6 -
6 rows selected.
--一旦、制約削除
alter table syain drop constraint fk;
on delete cascade
--制約作成
alter table syain add constraint fk foreign key (bcode)
references bumon (code) on delete cascade;--レコード削除
delete from bumon where code='03';
--親レコード削除すると、親レコードも子レコードも削除されていることを確認。
select * from bumon;
select * from syain;CODE BNAME
01 1部
02 2部2 rows selected.
ID UNAME BCODE
10001 社員1 01
10002 社員2 01
10003 社員3 02
10004 社員4 024 rows selected.
統計情報ロック/ロック解除
統計情報を手動で取得しようと思ったら、ORA-20005エラーになってしまった。
どうやら、統計情報にロックがかかっているらしい。
解除方法は以下コマンドである。
■統計情報ロック
■統計情報ロック解除
■統計情報ロック確認
select TABLE_NAME ,STATTYPE_LOCKED from USER_TAB_STATISTICS;
■統計情報取得日時確認
select a.TABLE_NAME, a.LAST_ANALYZED
from user_tables;
