Oracle SQL言語～なぜ非効率な実行計画が選択され、SQLが性能劣化するのか～

概要

統計情報の必要性について自分なりに整理してみました。
なぜ非効率な実行計画が選択され、SQL性能が悪くなるのかもあわせて考えてみました。
DBAをしているとアプリケーションのご担当者や上層部への報告でこの点に関する説明を求められる機会が多いためまとめておきます。

SQL言語の特徴

コンピュータのプログラミング言語には、手続き型言語と非手続き型に大別されます。
SQLはこの内の非手続き型言語にカテゴリされます。
※
非手続き型言語には、さらに関数型言語/論理型言語/問合わせ言語があり、SQLはこの内の問合わせ言語にカテゴライズされます。

カテゴリ	主な言語	説明
手続き型言語	C言語,Java,Python,Perl,Ruby	処理の方法を記述
非手続き型言語	Lisp,SQL	処理の内容を記述

～SQL実践入門(引用)～
権限移譲の功罪
この態度の違いは良いとか悪いとかいうものではなく、言語の設計思想の違いです。
C言語、JavaからRubyに至るまで、手続き型を基礎する言語においては、ユーザがデータアクセスのための手段(How)を責任を持って記述することが前提です。
他方、手続き型であるRDBは、その仕事をユーザからシステム側に移管しました。
その結果、ユーザのすることは対象(What)の記述だけに限定されたのです。

～SQLパフォーマンス詳解(引用)～

開発者はインデックスを知る必要がある

SQLのクエリは、データを要求する英語の文として読むことができます。
通常SQLを書く時には、データベースの内部動作やストレージシステム(ディスクやファイルなど)に関する知識は必要とされません。
データベースに対して、どのファイルを開き、どのように要求する行を見つけるかを指示する必要はないわけです。
多くの開発者は、何年もSQLを触っているにもかかわらず、データベースの内部で行われている処理についてはほとんど知らないでしょう。

何が欲しいのかと、どのようにそれを取得するかという関連性を分離することは、SQLにおいては非常に有効に機能しています。
しかし、それでも完璧ではありません。
パフォーマンスの問題にたどり着く時、このような抽象化は逆効果になります。
前述の通り、SQL文を書く人は、どのようにデータベースが文を実行するかを知らないからです。
つまり、SQL文を書いた人はクエリの実行が遅くても責任を持てないのです。
しかし、経験的には逆のことが言えるでしょう。つまり、パフォーマンス上の問題を避けるためには、開発者はデータベースについて多少なりとも知っておく必要があるということです。

つまり、SQLは条件(What)のみを記述し、どのように(How)データを戻すかは記述しない、という特性があるということです。
多くの手続き型言語では、for文・while文・if文などの繰り返し処理や条件分岐をプログラマ自身が記述しますが、SQL言語ではそのようなアルゴリズムを記述する必要がない点が特徴として挙げられます。
SQLで記述しないHow(どのようにデータを抽出するかのアルゴリズム決定・制御)について、リレーショナルデータベースではクエリ評価エンジンと呼ばれる機能が担います。

Oracleでの動作

Oracleでは、この部分を「CBO(Cost Base Optimizer)」と呼ばれる機能が実施しています。
CBOがアルゴリズムを組み立てる際のインプットとなる情報には、「統計情報」やSQLテキストがあります。
CBOが決定したアルゴリズム(どのようにデータを取ってくるか)は「実行計画」と呼ばれます。
CBOが決定するHow部分(データ取得のためのアルゴリズム)は、常に最良のものが選択されるわけではなく、予測として決定されるため、非効率な実行計画が選択されてパフォーマンス劣化を招きます。
CBOの予測精度を高めるためにはインプット情報の精度を高める、特に統計情報の精度を高い状態に保つことが肝要です。
※それでも非効率な実行計画が選択されることはありますが。。