「SQLの実行計画」から処理時間の差を理解する：しばちょう先生の試して納得！ DBAへの道（改）（1）（1/3 ページ）

データベース技術や知識は、座学だけでなく「実際に手を動かして、実際に試して」理解を深めよう──。日本オラクルのデータベーススペシャリストが「新人をDBAに育てる際に使用する課題」をベースに、DBAがすぐ実践できる即効テクニックを紹介。第1回目は「“SQLの実行計画”から処理時間の差を理解する方法」を解説します。

[柴田長，日本オラクル株式会社]

※本連載は、日本オラクルの技術解説ブログ「Oracle Technology Network：しばちょう先生の試して納得！DBAへの道」より、提供者の許可の下、最新情報の追記とともに一部改訂して転載するものです。

　皆さんこんにちは。日本オラクルの“しばちょう”こと、柴田長（しばたつかさ）です。

　私は日本オラクルに入社してから丸5年間ほど、グリッド技術の検証センターにおいて、オラクルの持つ最新技術をパートナー各社と共同で検証し、数多くのホワイトペーパーを執筆してきました。その後5年以上の間、高パフォーマンスやミッションクリティカルな大規模案件の現場において、お客さまのシステムに最適なソリューションデザインの提案や各種トラブルの問題解決に従事しています。

　実機での経験をしているからこそ、瞬時にその機能の適用シナリオも含めて自信を持って自分の言葉（お客さまに合わせた言葉）でお客さまに提案できますし、早期にトラブル原因の当たりを付けることで解決のアイデアをひらめくことが可能になっていると思っています。さらには、これらの活動で得られた製品技術のメリット、デメリットやTipsなどを各種イベントやセミナーでも発表しています。

　本連載は、まさに「体験していただく」ことが主軸となります。単純なテキストベースの機能紹介だけではなく、手を動かして理解を深められる内容にしていきたいと考えております。なお記事冒頭の「注」にも明記しましたが、本連載は、筆者が所属する日本オラクルの技術解説ブログ「Oracle Technology Network」で掲載した記事の中から、特に人気のあった回をベースに最新情報を盛り込んだ改訂版としてお届けするものです。皆さま、ご愛読のほどよろしくお願いします。

「SQLの実行計画」から処理時間に差が出ることを理解する

　第1回目は、中でも人気のあった「SQLの実行計画」を取り上げます。

　まず、基本的な実行計画の取得方法を学び、実行計画を使って性能差を納得する流れを体験していただきたいと思います。「SQLが速い／遅い」という事実だけで終わりにするのではなく、「なぜ速いのか／遅いのか」までを分析できることがDBA（Database Administrator：データベース管理者）に求められることです。ぜひともチャレンジしてみてください。

演習1：実演習前の環境準備

　以下のSQLを実行し、演習前の環境準備を実施してください。

$ sqlplus / as sysdba
SQL> 
/* ファイルシステム上に、ビックファイル表領域TBS_BIGを作成 */
create bigfile tablespace TBS_BIG datafile '/oracle_datafile1/tbs_big.dbf' size 500M;
　
/* TBS_BIG表領域をデフォルト表領域とする、TRYユーザーを作成 */
create user TRY identified by TRY12345 ;
grant CREATE SESSION, CREATE TABLE, CREATE SEQUENCE to TRY;
alter user TRY default tablespace TBS_BIG;
alter user TRY quota 400M on TBS_BIG;
　
connect TRY/TRY12345
/* TRYスキーマ内に、TBL1表を作成 */
create table TBL1 (col1 number, col2 varchar2(100))
segment creation immediate
tablespace TBS_BIG;
　
/* TBL1表をベースに、TRYスキーマ内にTBL2表を作成（レコードはコピーしない） */
create table TBL2
segment creation immediate
tablespace TBS_BIG 
as select * from TBL1 where 1=2;
　

　特に難しい部分はないかと思いますが、最後の2つのSQL文で表を作成する際に指定している「segment creation immediate」句は、「ちょっと見慣れない」と感じるかもしれません。こちらは、Oracle Database 11g Release 2から登場した「セグメント作成の遅延」の挙動を設定する初期化パラメータ「DEFERRED_SEGMENT_CREATION」を上書きして、表作成のタイミングでセグメント割り当てを行わせるための記述です。

　この機能のメリットは大きく2点あります。「インストール時に数百、数千の表が作成されるが、1度もデータがINSERTされない表が大部分を占めるようなアプリケーションにおいて、ディスク領域が大幅に削減されること」や「セグメントを作る時間を短縮できるので、そのようなアプリケーションのインストール時間が短縮されること」です。

SQL> /* TBL2表へ100万表を一括インサート */
insert into TBL2 
  select i+j,rpad(to_char(i+j),100,'A')
    from  (
           with DATA2(j) as (
                             select 0 j from DUAL
                               union all
                             select j+1000 from DATA2 where j < 999000
                            )
           select j from DATA2
          ),
          (
           with DATA1(i) as (
                             select 1 i from DUAL
                               union all
                             select i+1 from DATA1 where i < 1000
                            )
           select i from DATA1
          );
commit;
　
/* TBL2表の第一カラム（COL1）に対して主キーを作成 */
alter table TBL2 add constraint PK_TBL2_COL1 primary key(COL1);
　
　
/* TBL2表をベースに、TRYスキーマ内にTBL3表を作成（全レコードをコピー） */
create table TBL3 as select * from TBL2;

　こちらは、2つの再帰的With句を直積させた副問い合わせの結果をINSERTする例です。順序オブジェクトを使用せずに、ユニーク（一意）な100万行を高速に生成することが可能です。こちらもOracle Database 11g Release 2から実行可能となっていますので、ぜひ試してみてくださいね。詳細については別の機会に説明する予定です。なお、このような複雑な構文のSQLは、いつでもすぐ使えるように個人PC内に保存して持ち歩いていたりします。

　最後に、以降の演習で使用する「TBL2」表と「TBL3」表のセグメントサイズが同じであることを、USER_SEGMENTSビューで確認しておきましょう。BLOCKS列を比較することで何個のブロックが各表セグメントに割り当てられているのかを簡単に確認できますよね。

SQL> /* 各表セグメントのサイズを確認 */
select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS from USER_SEGMENTS;
　
SEGMENT_NAME       SEGMENT_TYPE       TABLESPACE_NAME          BLOCKS
------------------ ------------------ ------------------------ ----------
TBL1               TABLE              TBS_BIG                           8
TBL2               TABLE              TBS_BIG                       16384
TBL3               TABLE              TBS_BIG                       16384
PK_TBL2_COL1       INDEX              TBS_BIG                        1920

　ということで、環境の準備が整いました。……といきたいところですが、もうちょっと待ってください。SQLの性能測定を行う上で重要なポイントを少し解説させてください。