GAGA LIFE.

DBAブログ -APとロジックとAKがある-

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言語の特徴として挙げられます。

SQLで記述しないHow(どのようにデータを抽出するかのアルゴリズム決定・制御)について、
リレーショナルデータベースではクエリ評価エンジンと呼ばれる機能が担います。
Oracleでは、この部分を「CBO(Cost Base Optimizer)」と呼ばれる機能が実施しています。

CBOがアルゴリズムを組み立てる際のインプットとなる情報には、「統計情報」やSQLテキストがあります。
CBOが決定したアルゴリズム(どのようにデータを取ってくるか)は「実行計画」と呼ばれます。

CBOが決定するHow部分(データ取得のためのアルゴリズム)は、常に最良のものが選択されるわけではなく、
予測として決定されるため、非効率な実行計画が選択されてパフォーマンス劣化を招きます。

CBOの予測精度を高めるためにはインプット情報の精度を高める、
特に統計情報の精度を高い状態に保つことが肝要です。
※それでも非効率な実行計画が選択されることはありますが。。


SQLの成り立ちや歴史については以下を参照

Pro Oracle SQL, Second Edition
CHAPTER 1: Core SQL
The SQL Language


参考資料

The Relational Model for Database Management: Version 2 

The Relational Model for Database Management: Version 2

The Relational Model for Database Management: Version 2

 

データベース実践講義 ―エンジニアのためのリレーショナル理論

データベース実践講義 ―エンジニアのためのリレーショナル理論 (THEORY/IN/PRACTICE)

データベース実践講義 ―エンジニアのためのリレーショナル理論 (THEORY/IN/PRACTICE)

 

プログラマのためのSQL 第4版 

プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに

プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに

 

SQLパフォーマンス詳解 

SQLパフォーマンス詳解

SQLパフォーマンス詳解

 

アート・オブ・SQL ―パフォーマンスを引き出すSQLプログラミング手法 

アート・オブ・SQL ―パフォーマンスを引き出すSQLプログラミング手法 (Theory in practice)

アート・オブ・SQL ―パフォーマンスを引き出すSQLプログラミング手法 (Theory in practice)

  • 作者: Stephane Faroult,Peter Robson,木下哲也,有限会社福龍興業
  • 出版社/メーカー: オライリー・ジャパン
  • 発売日: 2007/09/15
  • メディア: 大型本
  • 購入: 4人 クリック: 215回
  • この商品を含むブログ (28件) を見る
 

SQL and Relational Theory, 3rd Edition 

SQL and Relational Theory: How to Write Accurate SQL Code

SQL and Relational Theory: How to Write Accurate SQL Code

 

SQL実践入門 

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

 

達人に学ぶSQL徹底指南書

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

 

Troubleshooting Oracle Performance, Second Edition 

Troubleshooting Oracle Performance

Troubleshooting Oracle Performance

 

Pro Oracle SQL, Second Edition

Pro Oracle SQL (Expert's Voice in Oracle)

Pro Oracle SQL (Expert's Voice in Oracle)

  • 作者: Karen Morton,Kerry Osborne,Robyn Sands,Riyaj Shamsudeen,Jared Still
  • 出版社/メーカー: Apress
  • 発売日: 2013/10/29
  • メディア: ペーパーバック
  • この商品を含むブログを見る