しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、本年もどうぞよろしくお願い致します。”しばちょう”こと柴田長（しばた つかさ）です。昨年後半は多忙の為に休載の月が多く、「このまま終わってしまうのではないか？」とご心配（？）をお掛けし申し訳ございませんでした。少し仕事が楽になったらやろうと思っていては、いつまで経ってもやれるわけがない。と言う事で、今月は強行スケジュールで連載をお届けしようと思います。そして、今年2016年中に連載50回目を目指して頑張りたいと思います！！
さて、2016年2月23日（火）19時～21時に、Japan Oracle User Group(JPOUG)主催のイベントJPOUG Tech Talk Night #6 が開催されます。私とは別の柴田さん（柴田歩さん）のOracle Databaseの問合せ最適化に関する お話と いくつかのライトニングトークを予定しておりますので、ご興味のある方はWebからのお申込みの上、ぜひご参加ください。私は参加予定です！
さて、今回は機能のご紹介と言うよりもデータベース管理者としてのスキル向上に役立つネタを体験して頂きたいと考えています。SQLのパフォーマンス・チューニングを実施する際に実行計画が良いとか悪いとか議論をすると思いますが、一つのSQLに対して同時に複数の実行計画が存在することがあります。その原因について皆さんはいくつ程度思い当たりますかね？そして、その切り分けはどのように実施して行きますかね？と言う際に非常に便利なのが、V$SQL_SHARED_CURSORビューを参照してみることです。いざという時に、意外と頭の中から消えてしまっていたりするので、今回連載として扱う事で、少しでも記憶に留めておいて頂ければと考えておりますので、是非ともお付き合い下さいね。
以下の演習をOracle Database 11g Release 2以降のデータベースで試してみてください。
なお、Oracle Database 12c Release 1 (12.1.0.2) の単一インスタンス・データベースのインストレーション・ガイド 及び、Oracle VM VirtualBoxを用いたOracle Database 12c Release 1 環境の構築ガイドが、Oracle Technology Networkのこちらのページに公開されておりますので、参考にしてみてください。
【参考情報】
[Oracle Advanced Compression] http://www.oracle.com/technetwork/jp/database/options/compression/overview/index.html
[White Paper] Oracle Database 12cのOracle Advanced Compression 新機能概要
- 【今回ご紹介するネタ一覧（逆引き）】
- ✓ SET AUTO TRACEによるSQLの実行計画の確認方法（演習2）
- ✓ APPROX_COUNT_DISTINCTファンクションの使用例（演習2）
- ✓ V$SQLビューでSQL_ID等のカーソル情報の確認方法（演習3）
- ✓ V$SQL_SHARED_CURSORビューでカーソル共有できない理由の確認方法（演習4）
- ✓ V$SES_OPTIMIZER_ENVビューでセッション単位のオプティマイザ関連パラメータの確認方法（演習5）
1. 次のSQLで今回の演習で使用するTAB43表をTRYスキーマ内に作成して下さい。
$ sqlplus / as sysdba
SQL>
-- TBS43表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成
create tablespace TBS43 datafile '+FRA(DATAFILE)' size 512M ;
create user TRY identified by TRY12345 default tablespace TBS43 ;
grant connect, resource, dba to TRY ;
-- TAB43表の作成
connect TRY/TRY12345
create table TAB43 (COL1 number NOT NULL, COL2 number, COL3 char(100)) ;
-- TAB43表へレコード100万件を挿入
declare
intNum_COL1 number := 0 ;
intInterval number := 10000 ;
begin
for i in 1..100 loop
insert into TAB43
select LEVEL + intNum_COL1, mod(LEVEL,10) , rpad('A',100, LEVEL)
from DUAL connect by LEVEL <= intInterval ;
commit ;
intNum_COL1 := intNum_COL1 + intInterval ;
end loop ;
end ;
/
-- TAB43表に主キー（COL1列）を定義
alter table TAB43 add primary key (COL1) ;
-- TAB43表のオプティマイザ統計情報を取得
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TAB43');
-- TAB43表の統計情報の確認
set linesize 150 pages 5000
col TABLE_NAME for a16
select TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,
to_char(LAST_ANALYZED, 'YYYY/MM/DD HH24:MI:SS')
from USER_TABLES ;
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
---------------- ---------- ---------- ----------- -------------------
TAB43 1000000 16217 109 2016/01/20 07:29:23
さてさて、毎度お馴染みの指の準備体操から始めましょう。いざ検証を始めようと考えた際に、このようなスキーマを猛スピードなタイピングで作成することが出来るとカッコイイですよね。私の中でPL/SQLのループ文の書き方を忘れてしまっていたりすると、最近データベースを触れる機会が少なくなっているなぁという危機を感じるバロメータになっています。
今回作成して頂いたTAB43表は、COL1列が1~100万の一意の値、COL2列が0~9、COL3列はオマケです。PL/SQLじゃなくて良かったのではないかと、この説明文を書いた後に気付くという錆付き具合。。。うぉーもっとデータベース触りたい！！
2. TAB43表を検索する同一のクエリを二つのセッションで別々の実行計画で実行して下さい。ただし、演習の都合上、バインド変数は使用しないでください。
【Session-A】
$ sqlplus TRY/TRY12345
SQL>
-- 後の演習目的として、Session-AのSIDを確認
col SID for a12
col USER_NAME for a32
select sys_context('USERENV','SID') as SID,
sys_context('USERENV', 'CURRENT_USER') as USER_NAME
from DUAL ;
SID USER_NAME
------------ --------------------------------
265 TRY
-- 検証目的の為に、共有プールのフラッシュ
alter system flush shared_pool ;
-- 検証目的の為に、バッファ・キャッシュのフラッシュ
alter system flush buffer_cache ;
set autotrace on
set timing on
set linesize 150 pages 5000
-- SQL Plan-A
select COL2, APPROX_COUNT_DISTINCT(COL3)
from TAB43
where COL1 between 1 and 500000
group by COL2
order by COL2 ;
COL2 APPROX_COUNT_DISTINCT(COL3)
---------- ---------------------------
0 964
1 979
2 997
3 984
4 997
5 986
6 986
7 1021
8 998
9 993
10 rows selected.
Elapsed: 00:00:01.53
Execution Plan
----------------------------------------------------------
Plan hash value: 3061955075
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1090 | 4477 (2)| 00:00:01 |
| 1 | SORT GROUP BY APPROX| | 10 | 1090 | 4477 (2)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TAB43 | 500K| 51M| 4432 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"<=500000 AND "COL1">=1)
【Session-B】
$ sqlplus TRY/TRY12345
SQL>
-- 後の演習目的として、Session-BのSIDを確認
col SID for a12
col USER_NAME for a32
select sys_context('USERENV','SID') as SID,
sys_context('USERENV', 'CURRENT_USER') as USER_NAME
from DUAL ;
SID USER_NAME
------------ --------------------------------
25 TRY
-- 検証目的の為に、バッファ・キャッシュのフラッシュ
alter system flush buffer_cache ;
-- ここで何かを実行します。今は内緒。答えは演習5の解説部で公開！！
alter session set OPTIMIZER_INDEX_COST_ADJ=1 ;
set autotrace on
set timing on
set linesize 150 pages 5000
-- SQL Plan-B
select COL2, APPROX_COUNT_DISTINCT(COL3)
from TAB43
where COL1 between 1 and 500000
group by COL2
order by COL2 ;
COL2 APPROX_COUNT_DISTINCT(COL3)
---------- ---------------------------
0 964
1 979
2 997
3 984
4 997
5 986
6 986
7 1021
8 998
9 993
10 rows selected.
Elapsed: 00:00:02.54
Execution Plan
----------------------------------------------------------
Plan hash value: 1998381927
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1090 | 135 (34)| 00:00:01 |
| 1 | SORT GROUP BY APPROX | | 10 | 1090 | 135 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB43 | 500K| 51M| 90 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C005776 | 500K| | 11 (10)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1">=1 AND "COL1"<=500000)
冒頭で私が述べた「一つのSQLに対して同時に複数の実行計画が存在する」という状況を再現して頂きたく、ちょいと意味不明な演習問題に取り組んで頂きました。上記の私の回答例では、Session-AとSession-Bの二つのセッションにおいて全く同一のSQL文を使用していますが、Session-AではTAB43表に対して全表検索「TABLE ACCESS FULL」でアクセスする実行計画であり、Session-BではTAB43表に対して索引経由でアクセスする「TABLE ACCESS BY INDEX ROWID BATCHED」という異なる実行計画になっています。本当に同じSQL文だったの？と言う確認は、次の演習3に回すとして、このように同一SQL文で異なる実行計画が存在する現象が発生する理由としては幾つか存在しますよね。
ヒント句はダメですよ。SQL文自体が変わってしまいますからね。どうですか？思い出せましたか？そうですよね。そう、あれです。（さっさと答えを言えと・・・）「第36回 SQLのパース処理とバインド変数の理解」にて皆さんに体験して頂いた「Adaptive Cursor Sharing機能」がその一つです。Adaptive Cursor Sharing機能は所謂、バインド・ピーク機能によって発生する従来の問題（バインド・ピーク機能とは、ハード・パース時にセットされていたバインド変数の中身の値に最適な実行計画を作成する機能です。次に同じSQL文を実行した際はソフト・パースとなり、新たな実行計画は作成されません。その代わり、バインド変数の中の値にとってみれば最適な実行計画では無く性能が劣化する事象をここでは問題と定義しています。）を解決する素晴らしい機能ですよね。
しかし、演習問題の但し書きを思い出して下さい。「バインド変数を使うな！」です。バインド変数を使わないと言う事は、このAdaptive Cursor Sharing機能は動作できませんから、他の方法で私の回答例は実現しているわけですね。何だろうか？と興味を持って頂けた方、ありがとうございます。皆さんの本番データベースにて同じような現象が発生した場合、その理由の候補を幾つか推測して調査を行われていると思いますので、是非ともデータベース管理者の皆さんに原因を探って頂きたく、上記の回答例から答えのコマンドを消させて頂いています。すいませーん。
ちなみに、Adaptive Cursor Sharing機能以外にも、カーディナリティ・フィードバックや動的サンプリング、SQL Plan Management、SQLアウトライン、SQLプロファイル等も今回の現象が再現する理由に挙げられますが、これらは今回の答えでは無いことを先にお伝えしておきます。ますます謎めいてきましたかね？ もう既にあたりが付いている方もいらっしゃるかと思いますが、しばらくお付き合いをお願い致します。
おまけとして、私が上記の回答例の中でTAB43表に対して実行したSQL文を見直してみてください。あまり見たことのないAPPROX_COUNT_DISTINCTファンクションを使用しています。これはOracle Database 12c Release 1からの新機能であり、SQL言語リファレンスには次のような説明書きがあります。『このファンクションは、exprの異なる値を含む正確な数の行を戻すCOUNT (DISTINCT expr)ファンクションの代替機能を提供します。APPROX_COUNT_DISTINCTは、正確な結果とわずかに誤差がありますが、COUNTよりはるかに高速に大量のデータを処理します。』なにー！！非常に面白い説明じゃないですか！！と誰もが思いますよね。「正確な結果とわずかに誤差がある」なんて、データベースとしてあって良いのか？と突っ込みたくなる方もいらっしゃるかと思いますが、データ分布の傾向を捉えるような要件の場合は、少しぐらい正確な値じゃなくても良いわけで、かつ、それが高速に結果を返せるのであれば非常に嬉しいと思うのですよ。あれ？以前の連載で似たような事を読んだことがあるなー？と感じられた方、素敵です。そうなのですよ、「第8回 オプティマイザ統計情報の管理 ～統計収集の高速化を体験してみる～」の演習6で私は次のような解説を書いているのです。『Oracle Database 11gの「DBMS_STATS.AUTO_SAMPLE_SIZE」では新しいハッシュ・ベースのサンプリング・アルゴリズムが採用されることで、「サンプリング100%とほぼ同等の統計情報の精度」と「サンプリング10%の統計情報収集とほぼ同程度の実行時間」を同時に満たす機能へと進化を遂げています。』
裏は取れていませんが、恐らく、このAPPROX_COUNT_DISTINCTファンクションは、Oracle Database 11g Release 2のオプティマイザ統計情報の収集から既に使われていて、Oracle Database 12c Release 1にて、一般にも提供されるようになったのではと個人的に思っています。このような知識の繋がりは非常に興奮します。
さて、ではAPPROX_COUNT_DISTINCTファンクションが登場しなかったら、COUNT（DISCINCT xxx）を実行する必要があったわけで、そちらに置き換えたSQL文をSession-Cにて実行した結果が次の通りです。
【Session-C】
$ sqlplus TRY/TRY12345
SQL>
-- Session-Aで作成したカーソルを残しておきたい為、共有プールのフラッシュは実行しない
-- alter system flush shared_pool ;
-- 検証目的の為に、バッファ・キャッシュのフラッシュ
alter system flush buffer_cache ;
set autotrace on
set timing on
set linesize 150 pages 5000
-- APPROX_COUNT_DISTINCTを使わない場合
select COL2, COUNT(DISTINCT COL3)
from TAB43
where COL1 between 1 and 500000
group by COL2
order by COL2 ;
COL2 COUNT(DISTINCTCOL3)
---------- -------------------
0 991
1 989
2 989
3 989
4 989
5 989
6 989
7 989
8 989
9 989
10 rows selected.
Elapsed: 00:00:01.85
Execution Plan
----------------------------------------------------------
Plan hash value: 2165565975
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1050 | | 8957 (2)| 00:00:01 |
| 1 | SORT GROUP BY | | 10 | 1050 | | 8957 (2)| 00:00:01 |
| 2 | VIEW | VM_NWVW_1 | 9892 | 1014K| | 8957 (2)| 00:00:01 |
| 3 | HASH GROUP BY | | 9892 | 1052K| 57M| 8957 (2)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TAB43 | 500K| 51M| | 4432 (1)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("COL1"<=500000 AND "COL1">=1)
全表検索となる実行計画が選択されましたので、比較対象としてはSession-Aの実行時間（1.53秒）が適切であり、APPROX_COUNT_DISTINCTファンクションによる効果が実証されていますね。今回は、データ量が小さな検証環境での比較なので、それほど大きな差にはなっていませんが、もしご興味があれば、大きなデータ量でも比較実行してみることをお薦めします。また、実行結果も比較して頂くと、「正確な結果とわずかな誤差」という説明部分にご納得いただけると思います。
さてさて、この解説を書くだけで1時間を消費してしまいました。原稿提出の締め切りまであと2時間・・・頑張りましょう。
3. V$SQLビューに問い合わせて、演習2で実行したTAB43に対するクエリのSQL_ID及び、CHILD_NUMBERを確認して下さい。
$ sqlplus / as sysdba
SQL>
set linesize 150 pages 5000
col SQL_TEXT for a40
select SQL_ID, CHILD_NUMBER, HASH_VALUE, PLAN_HASH_VALUE, SQL_TEXT
from V$SQL
where PARSING_SCHEMA_NAME='TRY'
and SQL_TEXT like 'select COL2%' ;
SQL_ID CHILD_NUMBER HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ------------ ---------- --------------- ----------------------------------------
1804n3c3n4bnk 0 121777810 3061955075 select COL2, APPROX_COUNT_DISTINCT(COL3)
from TAB43 where COL1 between 1 and
500000 group by COL2 order by COL2
1804n3c3n4bnk 1 121777810 1998381927 select COL2, APPROX_COUNT_DISTINCT(COL3)
from TAB43 where COL1 between 1 and
500000 group by COL2 order by COL2
58kmzh22hbtc7 0 2231756167 2165565975 select COL2, COUNT(DISTINCT COL3) from
TAB43 where COL1 between 1 and 500000
group by COL2 order by COL2
演習2ではSession-AとSession-Bにて同一のSQL文を実行しました。Session-Cは異なるSQL文でしたね。それを明確に確認できるのが、V$SQLビューとなります。1行目と2行目がどちらもSQL_ID=’1804n3c3n4bnk’で共通しており、SQL_TEXT列にも全く同じSQL文が記載されています。しかし、3行目だけはSQL_IDもSQL_TEXTも完全に異なっていることが確認出来ますね。ちなみに、データベース再起動後に同じSQL文を実行しても、また同じSQL_IDとなる点は覚えておくと便利でしょう。
V$SQLビューには非常に多くの列が存在しますので、是非マニュアルでご確認を頂きたいのですが、ここでは上記回答例にある残りの3列について少し触れておきます。
まず、HASH_VALUE列はSQL_IDを数値化したものだと思ってもらえればOKです。これが同じか異なるかで、SQL文が同じか異なるかを判断することが可能です。次に、PLAN_HASH_VALUE列ですが、その名の通り、実行計画の数値化したものであり、この値が異なれば、実行計画が異なることを意味します。つまり、1行目と2行目はHASH_VALUE列の値が同じなのでSQL文としては同じだが、PLAN_HASH_VALUE列の値が異なるので、実行計画は異なると一発で判断出来るわけです。そして最後のCHILD_NUMBER列ですが、繰り返しになりますが1行目と2行目は同じSQL文ですが異なる実行計画を持っているわけで、その実行計画に0から番号を付けたものです。「複数の実行計画を持つこと」を別の表現方法として「子カーソルが複数存在する」とも言ったりします。
と言うことで次の演習では、この「子カーソルが複数存在することになった（カーソルを共有できなかった）理由」を探ってみましょう。
4. V$SQL_SHARED_CURSORビューに問い合わせて、SQL_ID=’1804n3c3n4bnk’のクエリが一つのカーソルを共有できない理由を確認して下さい。
$ sqlplus / as sysdba SQL> set linesize 100 pages 5000 set long 1000000 select * from V$SQL_SHARED_CURSOR where SQL_ID='1804n3c3n4bnk' ; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I ------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - REASON CON_ID -------------------------------------------------------------------------------- ---------- 1804n3c3n4bnk 000000009EF89058 000000009FDA29E8 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N <ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)< 0 /reason><size>2x356</size><optimizer_index_cost_adj> 100 1 </optimizer_index_cost_adj></ChildNode> 1804n3c3n4bnk 000000009EF89058 000000009ED9A978 1 N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
非常に多くの「N」が並んでるビューだなぁ、列名が全部表示されていなくて何の列の値を表示しているのか分からん！！というのが最初の印象ではないでしょうか。でも本当に便利なビューですから、絶対に覚えておいて下さいね。そして、上記の回答例の中で赤文字にしている部分が非常に大切な部分ですので解説していきますね。
まず、多くの「N」に交じって「Y」が一か所だけ存在するのは見つけられたでしょうか？この列の列名は「O」と表示されていますが、以下の列定義を参考にして頂くと、その並び順から「OPTIMIZER_MISMATCH」列であることが理解できます。リファレンス・マニュアルで「OPTIMIZER_MISMATCH」列の説明は『(Y|N)オプティマイザの環境が既存の子カーソルに一致しない』と記載がされています。正にその通りなのですが、少し前提をお話すると、SQL文を実行する際にはSQL文だけではなく、実行計画を司るオプティマイザに対して様々な情報をインプットしてハード・パースした実行計画と共に保管されています。そのインプット情報が同じであれば実行計画（カーソル）を使い回しても問題無いでしょうが、何かしらのインプット情報が変わった場合は、その情報を元に最適な実行計画をハード・パースし直した方が安全だと思いませんか？
インプットする情報の具体例を挙げていないので、イマイチ説明が分かりづらいと思いますが、次の「REASON」列をみて頂くと良いと思います。如何でしょう？色々と理由が書いてあることは何となくご理解頂けると思いますが、その通りで、この列に子カーソルが共有出来なかった理由が書いてあるのですね。少し見づらいので成形してみたのが次です。
<reason>Optimizer mismatch(12)</reason><size>2×356</size>
<optimizer_index_cost_adj> 100 1 </optimizer_index_cost_adj>
1行目の初めの文字「Optimizer Mismatch」は正に「Y」が入っていた列名に一致していますね。分かり易い。
2行目は「optimizer_index_cost_adj」なる文字列で二つの数字が囲まれていますが、これは次のようなフォーマットで表現されています。
<オプティマイザ関連パラメータ名>
<この子カーソル内のパラメータ値> <この子カーソルを使えなかったセッション内のパラメータ値>
</オプティマイザ関連パラメータ名>
と言う事は、オプティマイザ関連パラメータ「optimizer_index_cost_adj」の値が、CHILD_NUMBER=0の子カーソルでは「100」だったが、CHILD_NUMBER=1の子カーソルを別で作成することになったセッションでは「1」が設定されていたから、CHILD_NUMBER=0の子カーソルを共有できなかった（使えなかった）。と説明することが出来ます！！凄い！！
答えに近づいてきましたね。演習2のセッションSession-Bでは、このオプティマイザ関連パラメータ「optimizer_index_cost_adj」の値に変更が加えられていたのでは？と。では、実際に各セッションに設定されているオプティマイザ関連パラメータの値を見たくなりませんか？と言う事で最後の演習にチャレンジしてみましょう。
と、その前にオマケ。このV$SQL_SHARED_CURSORビューでは「STATS_ROW_MISMATCH」列は『既存の統計が既存の子カーソルに一致しない』、つまりオプティマイザ統計情報を取得し直したのでインプット情報が変化したと理解出来ますし、「LOAD_OPTIMIZER_STATS」列は、前段で出てきたAdaptive Cursor Sharing機能が動作して再度ハード・パースされた際に「Y」となったりしますし、「USE_FEEDBACK_STATS」列はカーディナリティ・フィードバック機能が動作した際に識別出来ます。
SQL> set linesize 60 desc V$SQL_SHARED_CURSOR Name Null? Type ----------------------------- -------- -------------------- SQL_ID VARCHAR2(13) ADDRESS RAW(8) CHILD_ADDRESS RAW(8) CHILD_NUMBER NUMBER UNBOUND_CURSOR VARCHAR2(1) SQL_TYPE_MISMATCH VARCHAR2(1) OPTIMIZER_MISMATCH VARCHAR2(1) OUTLINE_MISMATCH VARCHAR2(1) STATS_ROW_MISMATCH VARCHAR2(1) LITERAL_MISMATCH VARCHAR2(1) FORCE_HARD_PARSE VARCHAR2(1) EXPLAIN_PLAN_CURSOR VARCHAR2(1) BUFFERED_DML_MISMATCH VARCHAR2(1) PDML_ENV_MISMATCH VARCHAR2(1) INST_DRTLD_MISMATCH VARCHAR2(1) SLAVE_QC_MISMATCH VARCHAR2(1) TYPECHECK_MISMATCH VARCHAR2(1) AUTH_CHECK_MISMATCH VARCHAR2(1) BIND_MISMATCH VARCHAR2(1) DESCRIBE_MISMATCH VARCHAR2(1) LANGUAGE_MISMATCH VARCHAR2(1) TRANSLATION_MISMATCH VARCHAR2(1) BIND_EQUIV_FAILURE VARCHAR2(1) INSUFF_PRIVS VARCHAR2(1) INSUFF_PRIVS_REM VARCHAR2(1) REMOTE_TRANS_MISMATCH VARCHAR2(1) LOGMINER_SESSION_MISMATCH VARCHAR2(1) INCOMP_LTRL_MISMATCH VARCHAR2(1) OVERLAP_TIME_MISMATCH VARCHAR2(1) EDITION_MISMATCH VARCHAR2(1) MV_QUERY_GEN_MISMATCH VARCHAR2(1) USER_BIND_PEEK_MISMATCH VARCHAR2(1) TYPCHK_DEP_MISMATCH VARCHAR2(1) NO_TRIGGER_MISMATCH VARCHAR2(1) FLASHBACK_CURSOR VARCHAR2(1) ANYDATA_TRANSFORMATION VARCHAR2(1) PDDL_ENV_MISMATCH VARCHAR2(1) TOP_LEVEL_RPI_CURSOR VARCHAR2(1) DIFFERENT_LONG_LENGTH VARCHAR2(1) LOGICAL_STANDBY_APPLY VARCHAR2(1) DIFF_CALL_DURN VARCHAR2(1) BIND_UACS_DIFF VARCHAR2(1) PLSQL_CMP_SWITCHS_DIFF VARCHAR2(1) CURSOR_PARTS_MISMATCH VARCHAR2(1) STB_OBJECT_MISMATCH VARCHAR2(1) CROSSEDITION_TRIGGER_MISMATCH VARCHAR2(1) PQ_SLAVE_MISMATCH VARCHAR2(1) TOP_LEVEL_DDL_MISMATCH VARCHAR2(1) MULTI_PX_MISMATCH VARCHAR2(1) BIND_PEEKED_PQ_MISMATCH VARCHAR2(1) MV_REWRITE_MISMATCH VARCHAR2(1) ROLL_INVALID_MISMATCH VARCHAR2(1) OPTIMIZER_MODE_MISMATCH VARCHAR2(1) PX_MISMATCH VARCHAR2(1) MV_STALEOBJ_MISMATCH VARCHAR2(1) FLASHBACK_TABLE_MISMATCH VARCHAR2(1) LITREP_COMP_MISMATCH VARCHAR2(1) PLSQL_DEBUG VARCHAR2(1) LOAD_OPTIMIZER_STATS VARCHAR2(1) ACL_MISMATCH VARCHAR2(1) FLASHBACK_ARCHIVE_MISMATCH VARCHAR2(1) LOCK_USER_SCHEMA_FAILED VARCHAR2(1) REMOTE_MAPPING_MISMATCH VARCHAR2(1) LOAD_RUNTIME_HEAP_FAILED VARCHAR2(1) HASH_MATCH_FAILED VARCHAR2(1) PURGED_CURSOR VARCHAR2(1) BIND_LENGTH_UPGRADEABLE VARCHAR2(1) USE_FEEDBACK_STATS VARCHAR2(1) REASON CLOB CON_ID NUMBER
5. V$SES_OPTIMIZER_ENVビューで、セッション単位でオプティマイザ関連パラメータの変更有無を確認して下さい。
$ sqlplus / as sysdba
SQL>
set linesize 250 pages 50000
col USERNAME for a8
select T1.SID, T2.USERNAME, T1.ID, T1.NAME, T1.ISDEFAULT, T1.VALUE
from V$SES_OPTIMIZER_ENV T1,
V$SESSION T2
where T1.SID = T2.SID
and USERNAME='TRY'
and T2.SID in (265, 25)
and T1.ISDEFAULT='NO'
order by 1,3 ;
SID USERNAME ID NAME ISD VALUE
---------- -------- ---------- ---------------------------------------- --- -------------------------
25 TRY 2 parallel_execution_enabled YES true
25 TRY 9 optimizer_features_enable YES 12.1.0.2
25 TRY 11 cpu_count YES 2
25 TRY 12 active_instance_count YES 1
25 TRY 13 parallel_threads_per_cpu YES 2
25 TRY 14 hash_area_size YES 131072
25 TRY 15 bitmap_merge_area_size YES 1048576
25 TRY 16 sort_area_size YES 65536
25 TRY 17 sort_area_retained_size YES 0
25 TRY 24 pga_aggregate_target YES 399360 KB
25 TRY 35 parallel_query_mode YES enabled
25 TRY 36 parallel_dml_mode YES disabled
25 TRY 37 parallel_ddl_mode YES enabled
25 TRY 38 optimizer_mode YES all_rows
25 TRY 48 cursor_sharing YES exact
25 TRY 50 star_transformation_enabled YES false
25 TRY 66 optimizer_index_cost_adj NO 1
25 TRY 67 optimizer_index_caching YES 0
...(省略)...
265 TRY 2 parallel_execution_enabled YES true
265 TRY 9 optimizer_features_enable YES 12.1.0.2
265 TRY 11 cpu_count YES 2
265 TRY 12 active_instance_count YES 1
265 TRY 13 parallel_threads_per_cpu YES 2
265 TRY 14 hash_area_size YES 131072
265 TRY 15 bitmap_merge_area_size YES 1048576
265 TRY 16 sort_area_size YES 65536
265 TRY 17 sort_area_retained_size YES 0
265 TRY 24 pga_aggregate_target YES 399360 KB
265 TRY 35 parallel_query_mode YES enabled
265 TRY 36 parallel_dml_mode YES disabled
265 TRY 37 parallel_ddl_mode YES enabled
265 TRY 38 optimizer_mode YES all_rows
265 TRY 48 cursor_sharing YES exact
265 TRY 50 star_transformation_enabled YES false
265 TRY 66 optimizer_index_cost_adj YES 100
265 TRY 67 optimizer_index_caching YES 0
...(省略)...
118 rows selected.
V$SES_OPTIMIZER_ENVビューにて、セッション単位でオプティマイザ関連パラメータの設定値を確認することが可能です。特に簡単に見分ける方法としては「ISDEFAULT」列を使うことをお薦めします。オプティマイザ関連パラメータがデフォルトの設定値の場合は「YES」、デフォルトから変更が加えられている場合は「NO」となりますので、一般的には「NO」の列が存在しないかを確認すれば良いと言う事になります。
そして、結果としては演習4の解説で述べさせて頂いた通り、SID=265（演習2のSession-A）のoptimizer_index_cost_adjパラメータがデフォルト値の「1」であったのに対して、SID=25（演習2のSession-B）のoptimizer_index_cost_adjパラメータがデフォルト値の「100」ではなく「1」に変化していることが確認出来ますから、さあ、私が演習2で実行したコマンドは・・・そう！！
SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=1 ;
だったわけですねー。はい、知っていたよという方にとっては、つまらない内容で申し訳ございませんでしたが、少しは復習にはなりましたよね（笑）
さて、SQLのパフォーマンス・チューニングを実施する際に一つのSQLに対して同時に複数の実行計画が存在した場合、その理由を探る方法を体験して頂きましたが、如何でしたでしょうか？
alter session文でオプティマイザ関連のパラメータを変更して実行計画を制御する方法は非常に便利です。しかしながら、アプリケーション・サーバーのコネクション・プールを使用してデータベース・インスタンスへ接続してSQL文を実行するような環境では、alter session文の乱用はご注意くださいね。トランザクションAにおいて、コネクション・プール内から任意のコネクションをGetしてSQL文Aを実行する為に最適な実行計画となるようにalter session文でオプティマイザ関連のパラメータを変更してからSQL文Aを実行したとします。そのトランザクションAが完了したのでコネクションを解放します。次に別のトランザクションBがその同じコネクションを次に使い回せるので毎度、データベース・インスタンスへ接続を試行する必要が無いと言うのがコネクション・プールの魅力なのですが、トランザクションBがSQL文Bを実行しようとした際に、トランザクションAで変更されたオプティマイザ関連のパラメータは生き続けてしまっています。トランザクションBのSQL文Bにとってみれば、オプティマイザ関連パラメータはデフォルト設定であった方が最適だった場合、どうなるでしょう？明らかにNGですよね。この説明文を書いているだけでも混乱してくるので、実際にこれが起因で性能問題が発生した場合には、システム運用の現場は混乱に陥ることになってしまう可能性大です。
私が言いたいことは、コネクション・プールを使用している環境においてalter session文を使用した場合には、きちんとデフォルト値へリセットしてからコネクションを解放するようにアプリケーションを書くように気を付けて下さいね。そして、DBAの方はそのような事が起こり得る事を頭の片隅に記憶しておきましょうね。と言う事になります。
毎度、説明文がややこしくて申し訳ない（頑張ってはいるのですが）のですが、今回も最後まで体験して頂きましてありがとうございました。是非、感想や質問をお待ちしておりますね。次回以降もどうぞよろしくお願い致します。
(ご質問の方法はこちらにあります)
https://blogs.oracle.com/otnjp/shibacho-index