去年の冬の事、「内部エラーが発生したので調査して欲しい」との連絡がありました。
開発中の新本番環境で、構成は以下の通りです。
![新・門外不出のOracle現場ワザ 〜エキスパートが明かす運用・管理の極意【電子書籍】[ 五十嵐建平, 大塚信男, 小田圭二, 村方仁, 谷敦雄, 宮崎博之, 神田達成 ]](https://thumbnail.image.rakuten.co.jp/@0_mall/rakutenkobo-ebooks/cabinet/0198/2000000160198.jpg?_ex=128x128 "新・門外不出のOracle現場ワザ 〜エキスパートが明かす運用・管理の極意【電子書籍】[ 五十嵐建平, 大塚信男, 小田圭二, 村方仁, 谷敦雄, 宮崎博之, 神田達成 ]")
トレースファイルを見ると、エラー発生時に実行されていたのは以下のようなselect文でした。
TABLE_Aは、テーブルではなくビューです。
SELECT * FROM ( SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5,T1.col6
FROM TABLE_A T1 WHERE ( T1.col5 = :bind1 ) ORDER BY 6 DESC, 3 DESC )
WHERE ROWNUM <= 102
早速、MOS(MyOracleSupport)で調べてみます。
Bug 17008068 ORA-600 [qctcte1], [0] from PL/SQL
WITH句を使ったPL/SQLで発生するとの事なので、該当しなさそうです。
回避策は「WITH句の使用をやめろ」となっています。何か、身も蓋もない回避策ですね・・・
Bug 25293659 - ORA-600[qctcte1] Or Hang With Query That Use Recursive Subquery Factoring (With Clause) (ドキュメントID 25293659.8)
こちらもWITH句を使ったケースなので該当しなさそうですが、一応、回避策である"_simple_view_merging"=off でテストしてみます。
SQL> alter session set "_simple_view_merging"=off;
セッションが変更されました。
SQL> variable bind1 VARCHAR2(32) ;
SQL> execute :bind1 := '01%';
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM ( SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5,T1.col6 FROM TABLE_A T1 WHERE ( T1.col5 = :bind1 ) ORDER BY 6 DESC, 3 DESC ) WHERE ROWNUM <= 102;
レコードが選択されませんでした。
一応、エラーは回避できました。
ORA-00600: [qctcte1], [0], Internal Error Code After 12c Upgrade (ドキュメントID 2168777.1)
12.1.0.2以降で発生する不具合で、オプティマイザがクエリ変換を実行する時に発生するとの事。かなりそれっぽいです。
回避策は以下の2つのいずれかとあるので、それぞれ試してみます。
SQL> alter session set "_optimizer_filter_pushdown"=false;
セッションが変更されました。
SQL> SELECT * FROM ( SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5,T1.col6 FROM TABLE_A T1 WHERE ( T1.col5 = :bind1 ) ORDER BY 6 DESC, 3 DESC ) WHERE ROWNUM <= 102;
行1でエラーが発生しました。:
ORA-00600: 内部エラー・コード, 引数: [qctcte1], [0], , , , , , ,
, , ,
回避策その1では回避できなかったので、接続しなおして、その2を試してみます。
SQL> alter session set "_optimizer_cost_based_transformation"=off;
セッションが変更されました。
SQL> variable bind1 VARCHAR2(32) ;
SQL> execute :bind1 := '01%';
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT * FROM ( SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5,T1.col6 FROM TABLE_A T1 WHERE ( T1.col5 = :bind1 ) ORDER BY 6 DESC, 3 DESC ) WHERE ROWNUM <= 102;
レコードが選択されませんでした。
こちらでは回避できました。
類似はしているけれど回避策の点で完全に合致はしていないのでサポートに問い合わせてみました。
結果、サポート側で把握している類似例も上記3点であり、詳細原因は不明なままですが、"_simple_view_merging"=offか、 "_optimizer_cost_based_transformation"=offのいずれかで回避してくれとの事でした。
"_simple_view_merging"=offは余り設定したくないな・・・と思って調べたところ、DB全体で設定すると以下の不具合が発生するそうです。
自動メンテナンスタスクジョブが動かなくなってしまうのですね。
原因は、以下の仕様です。
※BUG番号がついていますが、バグではないとしてクローズされています。
BUG 22512858 - ORA-02014: CANNOT SELECT FOR UPDATE FROM VIEW WITH DISTINCT, GROUP BY, ETC
"_simple_view_merging"=offを設定すると、更新可能な複合ビューの操作ができなくなる、というものです。
結局、ヒント句としてエラーの発生するSQLに埋め込む事で、影響を最小化して回避する事になりました。
以下のいずれかです。
/*+ OPT_PARAM('_simple_view_merging' 'false') */
/*+ OPT_PARAM('_optimizer_cost_based_transformation' 'false') */
クエリ変換と複合ビューに関する詳細は以前の記事で記載しています。
