oracleパフォーマンス最適化1
SQLチューニングには確かに深いところがたくさんあります.そして、SQLチューニングも難しい仕事です.ネット上の良い文章を共有して、皆さんにお届けします.その中で言及した[
デフォルトでは、ORACLEはCHOOSEオプティマイザを採用しています.不要なフルテーブルスキャン(full table scan)を避けるためには、CHOOSEオプティマイザの使用をできるだけ避け、ルールベースまたはコストベースのオプティマイザを直接採用する必要があります.]update tableAなど、多くのシーンで役立ちます.name set column_name = (select column_value from tableB_name where tableB_name.fid = tableA_name.freferenceid ) where tableA_name.fid in(........)tableA_の場合、このような文nameのデータ量は巨大で、デフォルトのchooseで実行するのは恐ろしくて、とても遅いです.もしこのsqlがupdate/*rule*/tablea_に変更されたらname set column_name = (select column_value from tableB_name where tableB_name.fid = tableA_name.freferenceid ) where tableA_name.fid in(........)速度の向上は非常に顕著で、数秒で実行が完了します.今、全文を差し上げますが、実際によく知られているチューニング方法の多くは、実際の応用では非常に明らかなチューニングには達しませんが、oracleがsqlを実行する内部メカニズムを理解するのに非常に役立ちます.
1. 適切なORACLEオプティマイザを選択
ORACLEのオプティマイザは3種類あります.
a.RULE(ルールベース)b.COST(コストベース)c.CHOOSE(選択性)
デフォルトのオプティマイザを設定し、init.oraファイル中OPTIMIZER_MODEパラメータの各種声明、例えばRULE、COST、CHOOSE、ALL_ROWS,FIRST_ROWS . もちろんSQL文レベルやセッションレベルで上書きします.
コストベースのオプティマイザ(CBO,Cost-Based Optimizer)を使用するには、データベース内のオブジェクト統計(object statistics)の精度を向上させるためにanalyzeコマンドを頻繁に実行する必要があります.
データベースのオプティマイザモードが選択性(CHOOSE)に設定場合、実際のオプティマイザモードはanalyzeコマンドが実行するかどうかに関係する.tableがanalyzeを通過すると、オプティマイザモードは自動的にCBOとなり、逆にデータベースはRULE形式のオプティマイザを採用する.
デフォルトでは、ORACLEはCHOOSEオプティマイザを採用しています.不要なフルテーブルスキャン(full table scan)を避けるためには、CHOOSEオプティマイザの使用をできるだけ避け、ルールベースまたはコストベースのオプティマイザを直接採用する必要があります.
2.Tableへのアクセス方法
ORACLEは、2つのアクセステーブルに記録される方法を採用しています.
a.全表スキャン
全テーブルスキャンとは、テーブル内の各レコードに順次アクセスすることである.ORACLEは、複数のデータブロックを一度に読み込む方式で全テーブルスキャンを最適化する.
b.ROWIDによるテーブルへのアクセス
ROWIDベースのアクセス方式を採用し、アクセステーブルの効率を高めることができます.ROWIDにはテーブルに記録された物理的な位置情報が含まれています.ORACLEはインデックス(INDEX)を用いてデータとデータを格納する物理的位置(ROWID)とのつながりを実現した.通常、インデックスはROWIDに素早くアクセスする方法を提供するので、インデックス列に基づくクエリーは性能の向上を得ることができる.
3.SQL文の共有
同じSQL文を繰り返し解析しないために、ORACLEは最初の解析後、SQL文をメモリに格納する.システムのグローバル領域SGA(system global area)にある共有プール(shared buffer pool)のメモリは、すべてのデータベースユーザによって共有することができる.したがって、SQL文(カーソルと呼ばれる場合もある)を実行すると、以前の実行文と完全に同じであれば、ORACLEは解析済みの文と最適な実行パスをすぐに取得することができます.ORACLEのこの機能はSQLの実行性能を大幅に向上させ、メモリの使用を節約した.
残念なことに、ORACLEは簡単なテーブルに対してキャッシュを提供するだけで、この機能はマルチテーブル接続クエリーには適用されません.
データベース管理者はinitにいる必要があります.oraではこの領域に適切なパラメータを設定、このメモリ領域が大きいほどより多くの文を保持することができ、もちろん共有される可能性も大きい.
ORACLEにSQL文をコミットすると、ORACLEはまずこのメモリで同じ文を検索します.
ここで注意しなければならないのは、ORACLEは両者に対して厳格に一致しており、共有を達成するには、SQL文は完全に同じでなければならない(スペース、改行などを含む).
共有文は、次の3つの条件を満たす必要があります.
A.文字レベルの比較:
現在実行されている文と共有プールの文は完全に同じである必要があります.
例:
例:
ユーザー・オブジェクト名のアクセス方法
SQLが共有できるかどうか、理由
C.2つのSQL文に同じ名前のバインド変数を使用する必要があります(bind variables)
例:
第1のグループの2つのSQL文は同じ(共有可能)であり、第2のグループの2つの文は異なる(実行時に異なるバインド変数に同じ値が割り当てられている場合でも)
ORACLEの解析器は、FROM句中のテーブル名を右から左の順に処理するので、FROM句中に最後に書くテーブル(ベーステーブルdriving table)が最初に処理される.FROM句に複数のテーブルが含まれている場合は、レコード数が最も少ないテーブルをベーステーブルとして選択する必要があります.ORACLEが複数のテーブルを処理する場合、それらはソートおよびマージ方式で接続する.まず、1番目のテーブル(FROM句の最後のテーブル)をスキャンする記録をディスパッチし、2番目のテーブル(FROM句の最後の2番目のテーブル)をスキャンし、最後に2番目のテーブルから検索したすべての記録を1番目のテーブルの適切な記録とマージする.
例:
表TAB 1 16384条記録
表TAB 2 1条記録
ベーステーブルとしてTAB 2を選択(最良の方法)
例:
EMPテーブルはLOCATIONテーブルとCATEGORYテーブルの交差を記述する.
ORACLEでは、WHERE句を下から上へ順に解析するが、この原理により、表間の接続は他のWHERE条件の前に書かなければならず、最大数の記録をフィルタできる条件はWHERE句の末尾に書かなければならない.
例:(非効率、実行時間156.3秒)
すべてのCOLUMNをSELECT句にリストしたい場合は、動的SQL列を使用して「*」を参照するのが便利です.残念なことに、これは非常に非効率な方法です.実際、ORACLEでは解析中に'*'をすべてのカラム名に順次変換し、データ辞書をクエリーすることで完了します.これは、より多くの時間を費やすことを意味します.
7.データベースへのアクセス数を減らす
各SQL文を実行すると、ORACLEは内部で多くの仕事を実行した:SQL文を解析し、インデックスの利用率を推定し、変数をバインドし、データブロックを読むなど.このように、データベースへのアクセス回数を減らすことで、実際にORACLEの作業量を減らすことができる.
例えば、以下の3つの方法で、従業員番号が0342または0291の従業員を検索することができる.
方法1(最も非効率)
SQL*Plus、SQL*Forms、Pro*CでARRAYSIZEパラメータを再設定すると、データベースへのアクセスごとに検索データ量が増加し、推奨値は200になります.
8.DECODE関数を使用して処理時間を短縮
DECODE関数を用いることで、同一の記録や同一のテーブルを繰り返し走査することを回避することができる.
例:
9.統合が簡単で、関連のないデータベース・アクセス
いくつかの簡単なデータベースがあれば
検索文は、関係なくクエリーに統合できます.
例:
検索は、1つにまとめることができる.
10.重複レコードの削除
最も効率的な重複記録の削除方法(ROWIDを使用しているため)
テーブルのレコードを削除する場合、通常、
ロールバックセグメント(rollback segments)は、復元可能な情報を格納するために用いる.COMMITトランザクションがない場合、ORACLEはデータを削除前の状態(正確には削除コマンドを実行する前の状況)に復元し、TRUNCATEを使用すると、ロールバックセグメントにはリカバリ可能な情報は格納されません.コマンドが実行すると、データは復元できない.そのため、少ないリソースが呼び出され、実行時間も短くなります.(注:TRUNCATEは全表削除のみ適用、TRUNCATEはDDL DMLではありません)
12.なるべくCOMMITを多く使う
可能な限り、プログラムの中でできるだけ多くのCOMMITを使用して、このようにプログラムの性能が向上して、需要もCOMMITが解放した資源のため減少します:
COMMITがリリースしたリソース:
a.
ロールバックセグメントにおけるデータの復元のための情報.
b.プログラム文で取得されたロック
c.redo log bufferにおける空間
d.Oracleは、上記3つのリソースの内部費用を管理する
(注:COMMITを使用する場合は、トランザクションの完全性に注意する必要があります.現実的には、効率とトランザクションの完全性は魚と熊の掌を兼ねることができません)
DECODEがNULLの場合、SUM(NULL)の値はNULL-->すべての値がNULLの場合、SUM(NULL)=NULLですが、NULLでない値が1つある限り、SUM()<>NULLなので元のSQLには論理的な問題はないはずです
第8点の個人的な見方について:もしDECODEがNULLを取るならば、SUM(NULL)の値はNULLで、正常に和を求めることはできません.以下のように変えることができます:SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)D 0020_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,0)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,0)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%';
デフォルトでは、ORACLEはCHOOSEオプティマイザを採用しています.不要なフルテーブルスキャン(full table scan)を避けるためには、CHOOSEオプティマイザの使用をできるだけ避け、ルールベースまたはコストベースのオプティマイザを直接採用する必要があります.]update tableAなど、多くのシーンで役立ちます.name set column_name = (select column_value from tableB_name where tableB_name.fid = tableA_name.freferenceid ) where tableA_name.fid in(........)tableA_の場合、このような文nameのデータ量は巨大で、デフォルトのchooseで実行するのは恐ろしくて、とても遅いです.もしこのsqlがupdate/*rule*/tablea_に変更されたらname set column_name = (select column_value from tableB_name where tableB_name.fid = tableA_name.freferenceid ) where tableA_name.fid in(........)速度の向上は非常に顕著で、数秒で実行が完了します.今、全文を差し上げますが、実際によく知られているチューニング方法の多くは、実際の応用では非常に明らかなチューニングには達しませんが、oracleがsqlを実行する内部メカニズムを理解するのに非常に役立ちます.
1. 適切なORACLEオプティマイザを選択
ORACLEのオプティマイザは3種類あります.
a.RULE(ルールベース)b.COST(コストベース)c.CHOOSE(選択性)
デフォルトのオプティマイザを設定し、init.oraファイル中OPTIMIZER_MODEパラメータの各種声明、例えばRULE、COST、CHOOSE、ALL_ROWS,FIRST_ROWS . もちろんSQL文レベルやセッションレベルで上書きします.
コストベースのオプティマイザ(CBO,Cost-Based Optimizer)を使用するには、データベース内のオブジェクト統計(object statistics)の精度を向上させるためにanalyzeコマンドを頻繁に実行する必要があります.
データベースのオプティマイザモードが選択性(CHOOSE)に設定場合、実際のオプティマイザモードはanalyzeコマンドが実行するかどうかに関係する.tableがanalyzeを通過すると、オプティマイザモードは自動的にCBOとなり、逆にデータベースはRULE形式のオプティマイザを採用する.
デフォルトでは、ORACLEはCHOOSEオプティマイザを採用しています.不要なフルテーブルスキャン(full table scan)を避けるためには、CHOOSEオプティマイザの使用をできるだけ避け、ルールベースまたはコストベースのオプティマイザを直接採用する必要があります.
2.Tableへのアクセス方法
ORACLEは、2つのアクセステーブルに記録される方法を採用しています.
a.全表スキャン
全テーブルスキャンとは、テーブル内の各レコードに順次アクセスすることである.ORACLEは、複数のデータブロックを一度に読み込む方式で全テーブルスキャンを最適化する.
b.ROWIDによるテーブルへのアクセス
ROWIDベースのアクセス方式を採用し、アクセステーブルの効率を高めることができます.ROWIDにはテーブルに記録された物理的な位置情報が含まれています.ORACLEはインデックス(INDEX)を用いてデータとデータを格納する物理的位置(ROWID)とのつながりを実現した.通常、インデックスはROWIDに素早くアクセスする方法を提供するので、インデックス列に基づくクエリーは性能の向上を得ることができる.
3.SQL文の共有
同じSQL文を繰り返し解析しないために、ORACLEは最初の解析後、SQL文をメモリに格納する.システムのグローバル領域SGA(system global area)にある共有プール(shared buffer pool)のメモリは、すべてのデータベースユーザによって共有することができる.したがって、SQL文(カーソルと呼ばれる場合もある)を実行すると、以前の実行文と完全に同じであれば、ORACLEは解析済みの文と最適な実行パスをすぐに取得することができます.ORACLEのこの機能はSQLの実行性能を大幅に向上させ、メモリの使用を節約した.
残念なことに、ORACLEは簡単なテーブルに対してキャッシュを提供するだけで、この機能はマルチテーブル接続クエリーには適用されません.
データベース管理者はinitにいる必要があります.oraではこの領域に適切なパラメータを設定、このメモリ領域が大きいほどより多くの文を保持することができ、もちろん共有される可能性も大きい.
ORACLEにSQL文をコミットすると、ORACLEはまずこのメモリで同じ文を検索します.
ここで注意しなければならないのは、ORACLEは両者に対して厳格に一致しており、共有を達成するには、SQL文は完全に同じでなければならない(スペース、改行などを含む).
共有文は、次の3つの条件を満たす必要があります.
A.文字レベルの比較:
現在実行されている文と共有プールの文は完全に同じである必要があります.
例:
SELECT * FROM EMP;
次のどれとも違います SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
B.2つの文が指すオブジェクトは完全に同じである必要があります.例:
ユーザー・オブジェクト名のアクセス方法
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner
は、以下のSQL文がこの2つのユーザ間で共有できるかどうかを考慮する.SQLが共有できるかどうか、理由
select max(sal_cap) from sal_limit;
できません.各ユーザーにprivate synonym-sal_がありますlimit、それらは異なるオブジェクトです
select count(*0 from work_city where sdesc like 'NEW%';
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id
できません.ユーザーjackがprivate synonymを介してplant_にアクセスdetailでjillはテーブルの所有者であり、オブジェクトが異なる.C.2つのSQL文に同じ名前のバインド変数を使用する必要があります(bind variables)
例:
第1のグループの2つのSQL文は同じ(共有可能)であり、第2のグループの2つの文は異なる(実行時に異なるバインド変数に同じ値が割り当てられている場合でも)
a.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;
b.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;
4. 最も効率的なテーブル名の順序を選択します(ルール・ベースのオプティマイザでのみ有効です).ORACLEの解析器は、FROM句中のテーブル名を右から左の順に処理するので、FROM句中に最後に書くテーブル(ベーステーブルdriving table)が最初に処理される.FROM句に複数のテーブルが含まれている場合は、レコード数が最も少ないテーブルをベーステーブルとして選択する必要があります.ORACLEが複数のテーブルを処理する場合、それらはソートおよびマージ方式で接続する.まず、1番目のテーブル(FROM句の最後のテーブル)をスキャンする記録をディスパッチし、2番目のテーブル(FROM句の最後の2番目のテーブル)をスキャンし、最後に2番目のテーブルから検索したすべての記録を1番目のテーブルの適切な記録とマージする.
例:
表TAB 1 16384条記録
表TAB 2 1条記録
ベーステーブルとしてTAB 2を選択(最良の方法)
select count(*) from tab1,tab2 0.96 ベーステーブルとしてTAB 2を選択(悪い方法)select count(*) from tab2,tab1 26.09 3つ以上のテーブル接続クエリーがある場合は、ベーステーブルとしてクロステーブル(intersection table)を選択する必要があり、クロステーブルとは、他のテーブルで参照するテーブルを指す.例:
EMPテーブルはLOCATIONテーブルとCATEGORYテーブルの交差を記述する.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN 次のSQLよりも効率的にSELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000 5.WHERE句中の接続順序.ORACLEでは、WHERE句を下から上へ順に解析するが、この原理により、表間の接続は他のWHERE条件の前に書かなければならず、最大数の記録をフィルタできる条件はWHERE句の末尾に書かなければならない.
例:(非効率、実行時間156.3秒)
SELECT …
FROM EMP E
WHERE SAL >50000
AND JOB = ‘MANAGER'
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO); (効率的、実行時間10.6秒)SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL >50000
AND JOB = ‘MANAGER'; 6.SELECT句では「*」の使用を避けるすべてのCOLUMNをSELECT句にリストしたい場合は、動的SQL列を使用して「*」を参照するのが便利です.残念なことに、これは非常に非効率な方法です.実際、ORACLEでは解析中に'*'をすべてのカラム名に順次変換し、データ辞書をクエリーすることで完了します.これは、より多くの時間を費やすことを意味します.
7.データベースへのアクセス数を減らす
各SQL文を実行すると、ORACLEは内部で多くの仕事を実行した:SQL文を解析し、インデックスの利用率を推定し、変数をバインドし、データブロックを読むなど.このように、データベースへのアクセス回数を減らすことで、実際にORACLEの作業量を減らすことができる.
例えば、以下の3つの方法で、従業員番号が0342または0291の従業員を検索することができる.
方法1(最も非効率)
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 291; 方法2(次低効率)DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
…..
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1;
END; 方法3(効率)SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291; 注意:SQL*Plus、SQL*Forms、Pro*CでARRAYSIZEパラメータを再設定すると、データベースへのアクセスごとに検索データ量が増加し、推奨値は200になります.
8.DECODE関数を使用して処理時間を短縮
DECODE関数を用いることで、同一の記録や同一のテーブルを繰り返し走査することを回避することができる.
例:
SELECT COUNT(*)，SUM(SAL) FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%';
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%'; DECODE関数で同じ結果を効率的に得ることができますSELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%'; 同様に、DECODE関数はGROUP BYとORDER BYの句にも用いることができる.9.統合が簡単で、関連のないデータベース・アクセス
いくつかの簡単なデータベースがあれば
検索文は、関係なくクエリーに統合できます.
例:
SELECT NAME FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME FROM DPT
WHERE DPT_NO = 10 ;
SELECT NAME FROM CAT
WHERE CAT_TYPE = ‘RD'; 上の3つ検索は、1つにまとめることができる.
SELECT E.NAME , D.NAME , C.NAME FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(‘X',X.DUMMY) = NVL(‘X',E.ROWID(+))
AND NVL(‘X',X.DUMMY) = NVL(‘X',D.ROWID(+))
AND NVL(‘X',X.DUMMY) = NVL(‘X',C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD'; (訳者:この方法で効率が向上したが、プログラムの可読性が大幅に低下したため、読者は利害を見極めなければならない)10.重複レコードの削除
最も効率的な重複記録の削除方法(ROWIDを使用しているため)
DELETE FROM EMP E
WHERE E.ROWID >(SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO); 11.DELETEの代わりにTRUNCATEを使用テーブルのレコードを削除する場合、通常、
ロールバックセグメント(rollback segments)は、復元可能な情報を格納するために用いる.COMMITトランザクションがない場合、ORACLEはデータを削除前の状態(正確には削除コマンドを実行する前の状況)に復元し、TRUNCATEを使用すると、ロールバックセグメントにはリカバリ可能な情報は格納されません.コマンドが実行すると、データは復元できない.そのため、少ないリソースが呼び出され、実行時間も短くなります.(注:TRUNCATEは全表削除のみ適用、TRUNCATEはDDL DMLではありません)
12.なるべくCOMMITを多く使う
可能な限り、プログラムの中でできるだけ多くのCOMMITを使用して、このようにプログラムの性能が向上して、需要もCOMMITが解放した資源のため減少します:
COMMITがリリースしたリソース:
a.
ロールバックセグメントにおけるデータの復元のための情報.
b.プログラム文で取得されたロック
c.redo log bufferにおける空間
d.Oracleは、上記3つのリソースの内部費用を管理する
(注:COMMITを使用する場合は、トランザクションの完全性に注意する必要があります.現実的には、効率とトランザクションの完全性は魚と熊の掌を兼ねることができません)
DECODEがNULLの場合、SUM(NULL)の値はNULL-->すべての値がNULLの場合、SUM(NULL)=NULLですが、NULLでない値が1つある限り、SUM()<>NULLなので元のSQLには論理的な問題はないはずです
第8点の個人的な見方について:もしDECODEがNULLを取るならば、SUM(NULL)の値はNULLで、正常に和を求めることはできません.以下のように変えることができます:SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)D 0020_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,0)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,0)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%';