Oracle 23c freeのオプティマイザ関連パラメータを調べる [オプティマイザ]
１．はじめに
4/5未明のOracle ACE向けアナウンスで、オラクル社のGerald Venzl氏からOracle Database 23c Freeがリリースされたとの説明があった(参考[1])。これは開発者向けの無償版で、従来Xeと呼ばれていたものに相当するそうだ。正式な23cは現在ベータテスト中である中、開発者向けを無償で先にリリースするのは、23cのDeveloper firstの姿勢の本気度を感じさせられる。RACやDataGuardのような機能は使えないが、JSON等の新機能を試したりすることができるのは開発者に取って朗報だろう。正式なサポートはないが、Oracle Forumにスレッドがあるので活用するとよいだろう（参考[2]）。
23cのリリースでまず一番私が確認したかったのは、オプティマイザに影響を与えるパラメータである。デフォルト値が変更されたものや、新機能として追加されたものを把握することは、いずれ必要となる19cからの移行作業で必ず必要となるためである。本稿では、23cのオプティマイザ関連のパラメータについて、その調査方法と結果ついて共有したい。
なお、Oracle Database 23c Freeの導入方法については、すでに日本語で紹介したブログもあるので、ここで特筆する必要はないだろう。ちなみに、私はVirtual Box VMのイメージを使ったが、特段難しいことはなく利用することができた。
２．オプティマイザのパラメータ確認方法
オプティマイザに影響を与えるパラメータの確認方法はいくつかあるが、私が個人的に最も信頼しているのは10053トレースである。10053トレースの取得方法は以下の通り。今回の場合SQLはなんでも良いので、select 1 from dualとした。
-- to enable the 10053 trace alter session set events '10053 trace name context forever'; execute any SQL -- stop tracing alter session set events '10053 trace name context off';
結果は以下のようにtraceディレクトリ配下にSID_ora_PID.trcという名前で出力される。
[oracle@localhost trace]$ ls -ltr /opt/oracle/diag/rdbms/free/FREE/trace/ | tail -rw-r-----. 1 oracle oinstall 7077 Apr 5 20:23 FREE_dbrm_1924.trm -rw-r-----. 1 oracle oinstall 37378 Apr 5 20:23 FREE_dbrm_1924.trc -rw-r-----. 1 oracle oinstall 68439 Apr 5 20:23 FREE_ora_12524.trm -rw-r-----. 1 oracle oinstall 126447 Apr 5 20:23 FREE_ora_12524.trc ★ -rw-r-----. 1 oracle oinstall 1731 Apr 5 20:27 FREE_gcr2_12649.trm
トレースファイルの中を確認すると、以下のようにPARAMETERS USED BY THE OPTIMIZERというセクションがあり、そこにオプティマイザに影響を与える（隠し）初期化パラメータが出力される。PARAMETERS WITH ALTERED VALUESはデフォルト値から変更されたパラメータ、PARAMETERS WITH DEFAULT VALUESはデフォルト値から変更のないパラメータを表す。
Trace file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_12524.trc Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 ... *************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** ************************************* PARAMETERS WITH ALTERED VALUES ****************************** Compilation Environment Dump _swat_ver_mv_knob = 0 Bug Fix Control Environment ************************************* PARAMETERS WITH DEFAULT VALUES ****************************** Compilation Environment Dump optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = false parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 23.1.0
多くの初期化パラメータは_（アンダースコア）が接頭辞となる隠しパラメータであり、マニュアルには記載されていない。このため、以下のSQLでそれぞれのパラメータの説明を取得する（x$ksppi.ksppdescに初期化パラメータの説明がある）。簡単な説明しかないものの、概ねどのような機能を持つのかくらいのことはわかるだろう。
set markup csv on
spool /tmp/initparam19c.csv
SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value", a.KSPPDESC "DESCRIPTION",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
order by 1
/
３．取得結果
上記の確認方法を使って、Oracle Database 23c Freeと、Oracle 19.11.0(EE)とで比較を行った。比較結果の詳細を確認したい場合は、参考[3]を参照されたい。ここでは主な変更点について記載しておきたい。
まず全体のパラメータ数は、23cが658個に対して、19cは613個と45増えている。具体的には以下の通り、23cの新規パラメータは46個あり、19cにあって23cにないパラメータが1つ存在した。19cからの移行においては、新しいオプティマイザの機能追加が想定しない挙動（実行計画）となる可能性があるため、必要に応じて見直しする対象となるだろう（具体的には値がtrueとなっているものは、機能を無効とするためにfalseに変更するといった対処など）。
◇23cのオプティマイザ関連の新規パラメータ
| # | 23.2.0 | value | description |
| 10 | _ansi_join_mv_rewrite_enabled | true | enable/disable ansi join mv rewrite |
| 17 | _autoptn_costing | false | DBMS_AUTO_PARTITION is compiling query for cost estimates |
| 40 | _cell_index_scan_enabled | true | enable CELL processing of index FFS |
| 41 | _cell_iot_scan_enabled | true | enable CELL processing of IOT FFS |
| 44 | _cell_metadata_compression | AUTO | Cell metadata compression strategy |
| 62 | _cross_con_remove_pushed_preds | false | remove pushed predicates from query fetching across containers |
| 82 | _enable_columnar_cache | 1 | enable 12g bigfile tablespace |
| 109 | _groupby_orderby_combine | 5000 | groupby/orderby don't combine threshold |
| 123 | _inmemory_hpk4sql_flags | 0 | In-Memory HPK4SQL flags |
| 129 | _json_qryovergen_rewrite | true | enable/disable JSON query over generation function rewrite |
| 160 | _obsolete_result_cache_mode | MANUAL | USERS SHOULD NOT SET THIS! Used for old qksced parameterof result_cache_mode |
| 232 | _optimizer_exists_to_any_rewrite | true | consider exists-to-any rewrite of subqueries |
| 286 | _optimizer_nested_loop_join | on | favor/unfavor nested loop join |
| 303 | _optimizer_push_gby_into_union_all | true | consider pushing down group-by into union-all branches |
| 323 | _optimizer_subsume_vw_sq | on | consider subsumption of views or subqueries |
| 341 | _optimizer_use_stats_models | false | use optimizer statistics extrapolation models |
| 353 | _optimizer_wc_filter_pushdown | true | enable/disable with clause filter predicate pushdown |
| 387 | _px_adaptive_dist_nij_enabled | on | enable adaptive distribution methods for left non-inner joins |
| 399 | _px_extended_join_skew_handling | true | enables extended skew handling for parallel joins |
| 424 | _px_parallelize_non_native_datatype | true | enable parallelization for non-native datatypes |
| 427 | _px_partition_load_skew_handling | on | enable partition load skew handling |
| 428 | _px_partition_load_skew_threshold | 3 | partition loads bigger than threshold times average are skewed |
| 446 | _px_window_skew_handling | true | enable window function skew handling |
| 447 | _qa_lrg_type | 0 | Oracle internal parameter to specify QA lrg type |
| 491 | _rowsets_use_work_heap_buffer | true | allow/disallow use of work heap buffer for values for rowsets |
| 497 | _slave_mapping_skew_handling | true | enables skew handling for slave mapping plans |
| 516 | _sqlexec_hash_based_distagg_ser_civ_enabled | true | enable hash based distinct aggregation in serial/CIV queries |
| 518 | _sqlexec_hash_based_set_operation_enabled | true | enable/disable hash based set operation |
| 519 | _sqlexec_hash_rollup_enabled | true | enable hash rollup |
| 523 | _sqlexec_use_delayed_unpacking | true | enable/disable the usage of delayed unpacking |
| 526 | _sqlexec_window_function_settings | 63 | execution settings for window functions |
| 531 | _swat_ver_mv_knob | 0 | Knob to control MV/REWRITE behavior |
| 542 | _use_dirty_reads | 0 | enable the use of column statistics for DDP functions |
| 553 | _zonemap_refresh_within_load | true | Control the refresh of basic zonemaps during/after data load |
| 574 | escrow_dirty_cursor | 0 | whether to encrypt newly created tablespaces |
| 575 | escrow_internal_cursor | 0 | whether to encrypt newly created tablespaces |
| 578 | group_by_position_enabled | false | enable/disable group by position |
| 579 | gwr_trigger_enabled | 0 | enable/disable group by position |
| 589 | json_expression_check | off | enable/disable JSON query statement check |
| 591 | memoptimize_writes | HINT | write data to IGA without memoptimize_write hint |
| 597 | optimizer_cross_shard_resiliency | false | enables resilient execution of cross shard queries |
| 614 | optimizer_use_sql_quarantine | true | enable use of sql quarantine |
| 644 | shard_enable_raft_follower_read | true | enable read from follower replications units in a shard |
| 645 | shard_queries_restricted_by_key | false | add shard key predicates to the query |
| 656 | translate_table_name_hash | 0 | number of active transactions per rollback segment |
| 658 | valid_shard_session_key | 0 | User process dump directory |
◇23cで廃止されたオプティマイザ関連の新規パラメータ
| 19.11.0 | value | description |
| _optimizer_quarantine_sql | true | enable use of sql quarantine |
次に、同じパラメータであるが値が異なるものを調べたところ、27個あった。この27のうち、19cで私が意図的に変更したものや、バージョンや環境に依存するもの（CPU数やメモリサイズ等）を除くと、以下の4つのパラメータが残った。これらデフォルト値が変わっているものもオプティマイザの挙動に影響を与えるため、移行時は留意する必要がある。
◇19cと23cでデフォルト値が変更されたオプティマイザ系パラメータ
| # | 23.2.0 | value | description |
| 36 | _cdb_special_old_xplan | false | display old-style plan for CDB special fixed table |
| 275 | _optimizer_key_vector_payload_dim_aggs | true | enables or disables dimension payloading of aggregates in VT |
| 515 | _sqlexec_hash_based_distagg_enabled | true | enable hash based distinct aggregation for gby queries |
| 626 | parallel_execution_enabled | false | policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE) |
４．おわりに
本稿では23cのOracle Database Freeと、19.11.0（EE）のオプティマイザ関連パラメータを、10053トレースを取得し比較した。Developer EditionとEEではそもそも異なる設定もあるだろうし、そもそも23cのEE版はベータテスト中という状況であるので、この結果を直接どうこう議論できるものではないだろう。実際の移行においては、23cが正式にリリースされた後、同様の方法でオプティマイザの挙動に影響するパラメータを洗い出す必要があるだろう。それでも、23cと19cでどの程度の変更があるのかの感覚を掴むことはできたと思う。
以上
参考
[1]:Introducing Oracle Database 23c Free - Developer Release, Oracle Database Insider, Gerald Venzl
[2]:Oracle Database Free - Developer Release, Oracle Forum
[3]:オプティマイザ関連初期化パラメータの差分確認結果（23cと19c）(OptimizerParameter23vs19.xlsx)
2023-04-09 13:04
コメント(0)
