Oracle Database 23c新機能 #5 データベース・パフォーマンス関連新機能前半

データベース・パフォーマンス関連新機能前半 Oracle Database Technology Night #84 津島浩樹⽇本オラクル株式会社
2024年11⽉28⽇

Copyright © 2024, Oracle and/or its affiliates Agenda 2 1.
リソース管理の強化 2. SQL履歴とSQL計画管理の強化 3. Oracle Database In-Memory(DBIM) 強化 4. Materialized View(MView) 強化 ※機能名の⽇本語表記はマニュアルに合わせてあります。そのままで伝わりにくいものについては括弧書きで追加説明しています

Copyright © 2024, Oracle and/or its affiliates 3 リソース管理の強化 •
パラレル処理リソース管理の機能拡張 • インスタンス間リソース管理

パラレル実⾏で実⾏終了前にリソース(PXプロセス)の解放が可能に機能概要 • 次のようなパラレル処理のPXプロセスが、SQL⽂が終了する前にプロアクティブに解放され、他のSQL⽂で使⽤できるようになった • 2つのPXプロセス・セットを使⽤するパラレル・クエリ (例えば、スキャンと Group By)
でスキャンが終了したとき (1つのPXプロセス・セットが解放される) • PXプロセスが不⾜するとシリアル実⾏またはパラレル・ステートメント・キューイング (⾃動DOPが有効) が⾏われるメリット • 可能な限り早期にPXプロセスを解放し、他のSQL⽂で使⽤できるようにすることで、使⽤可能なリソースの利⽤が最適化され、システムやアプリケーションの全体的なパフォーマンスが向上するパラレル処理リソース管理の機能拡張 Copyright © 2024, Oracle and/or its affiliates 5 スキャン表1 PX PX GroupBy PX PX QC

利⽤イメージ（パラレル・クエリの実⾏中に別セッションでV$PX_PROCESSを確認） • 23ai (2つのPXプロセスが解放された) • 21c (PXプロセスは解放されない) パラレル処理リソース管理の機能拡張機 Copyright ©
2024, Oracle and/or its affiliates 6 SQL> set pause on SQL> select /*+ parallel(2) */ * from (select a.col2,count(*) from t1 A,t1 B where a.col1=b.col2 group by a.col2) where rownum < 20; ... <実⾏中> SQL> select * from V$PX_PROCESS; SERV STATUS PID SPID SID SERIAL# IS_GV CON_ID ---- --------- ----- ---------- ----- -------- ----- ------ P000 IN USE 76 16453 42 28334 FALSE 0 P001 IN USE 77 16455 178 34206 FALSE 0 P002 AVAILABLE 78 16458 FALSE 0 P003 AVAILABLE 91 17064 FALSE 0 ... SQL> select * from V$PQ_SESSTAT; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------- ---------- ------------- ------ DML Parallelized 1 3 3 Servers 4 0 3 Server Sets 2 0 3 SQL> set pause on SQL> select /*+ parallel(2) */ * from (select a.col2,count(*) from t1 A,t1 B where a.col1=b.col2 group by a.col2) where rownum < 20; ... <実⾏中> SQL> select * from V$PX_PROCESS; SERV STATUS PID SPID SID SERIAL# IS_GV CON_ID ---- --------- ----- ---------- ----- -------- ----- ------ P000 IN USE 87 92782 502 34485 FALSE 0 P001 IN USE 88 92784 28 59852 FALSE 0 P002 IN USE 92 92786 33 35227 FALSE 0 P003 IN USE 93 92788 188 5622 FALSE 0 ... SQL> select * from V$PQ_SESSTAT; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------- ---------- ------------- ------ DML Parallelized 1 3 3 Server Threads 4 0 3 Slave Sets 2 0 3

利⽤イメージ（パラレルDMLのコミット前） • 23ai (2つのPXプロセスが解放された) • 21c (PXプロセスは解放されない) パラレル処理リソース管理の機能拡張機 Copyright ©
2024, Oracle and/or its affiliates 7 SQL> insert /*+ ENABLE_PARALLEL_DML parallel(2) */ into wt1 select col2,count(*) from t1 group by col2; 500000 rows created. SQL> select * from V$PX_PROCESS; SERV STATUS PID SPID SID SERIAL# IS_GV CON_ID ---- --------- ----- ---------- ----- -------- ----- ------ P000 IN USE 76 16453 36 27452 FALSE 0 P001 IN USE 77 16455 187 47485 FALSE 0 P002 AVAILABLE 78 16458 FALSE 0 P003 AVAILABLE 91 17064 FALSE 0 ... SQL> select * from V$PQ_SESSTAT; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------- ---------- ------------- ------ DML Parallelized 1 3 3 Servers 4 0 3 Server Sets 2 0 3 SQL> insert /*+ ENABLE_PARALLEL_DML parallel(2) */ into wt1 select col2,count(*) from t1 group by col2; 500000 rows created. SQL> select * from V$PX_PROCESS; SERV STATUS PID SPID SID SERIAL# IS_GV CON_ID ---- --------- ----- ---------- ----- -------- ----- ------ P000 IN USE 87 92782 502 48882 FALSE 0 P001 IN USE 88 92784 28 11600 FALSE 0 P002 IN USE 92 92786 33 40696 FALSE 0 P003 IN USE 93 92788 188 15867 FALSE 0 ... SQL> select * from V$PQ_SESSTAT; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------- ---------- ------------- ------ DML Parallelized 1 1 3 Server Threads 4 0 3 Slave Sets 2 0 3

利⽤イメージ（パラレルDMLのコミット前） • パラレルDMLだと解放されない (スキャンだけパラレルの時は解放される) • パラレル・ロールバックのためにPXプロセスは解放されないようだパラレル処理リソース管理の機能拡張機 Copyright © 2024,
Oracle and/or its affiliates 8 SQL> update /*+ ENABLE_PARALLEL_DML parallel(2) */ t1 set id = id + 1 where id < 1000000; 999999 rows updated. SQL> select * from V$PX_PROCESS; SERV STATUS PID SPID SID SERIAL# IS_GV CON_ID ---- --------- ----- ---------- ----- -------- ----- ------ P000 IN USE 77 16537 184 53356 FALSE 0 P001 IN USE 78 16539 349 20738 FALSE 0 P002 AVAILABLE 79 16542 FALSE 0 ... SQL> select * from V$PQ_SESSTAT; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------- ---------- ------------- ------ DML Parallelized 1 2 3 Servers 2 0 3 Server Sets 1 0 3 SQL> update /*+ parallel(2) */ t1 set id = id + 1 where id < 1000000; 999999 rows updated. SQL> select * from V$PX_PROCESS; SERV STATUS PID SPID SID SERIAL# IS_GV CON_ID ---- --------- ----- ---------- ----- -------- ----- ------ P000 AVAILABLE 77 16537 FALSE 0 P001 AVAILABLE 78 16539 FALSE 0 P002 AVAILABLE 79 16542 FALSE 0 ... SQL> select * from V$PQ_SESSTAT; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------- ---------- ------------- ------ DML Parallelized 0 2 3 Servers 2 0 3 Server Sets 1 0 3

サーバ・レベルのインスタンス間CPUリソース管理が可能に機能概要 • サーバー・レベルのインスタンス間リソース管理では、各CDBに必要な最⼩CPUリソースの指定と、最⼩値を超える CPUリソースの共有が可能になった • Linux cgroups (control groups)
と統合し、CPU shares とCPU limits を実装 • CDBレベルの初期化パラメータCPU_COUNTとCPU_MIN_COUNTを使⽤してcgroupsが⾃動的に構成される • サーバーをオーバーサブスクライブさせながら、特定のDBインスタンスのCPUリソースを保証することが可能 • あるDBインスタンスが保証されたCPUを使⽤しない場合、他のDBインスタンスがそのCPUを使⽤することが可能 • 初期化パラメータRESOURCE_MANAGER_CPU_SCOPE を SERVER_WIDE にすると有効 (ADBのデフォルトは SERVER_WIDE、その他はINSTANCE_ONLY) • SERVER_WIDEにすると初期化パラメータPROCESSOR_GROUP_NAMEは無視されるメリット • データベースを単⼀サーバーに統合することがこれまで以上に簡単になった • これまでは、オペレーティングシステム・レベルで CPU などのリソースの公正な利⽤を確保するのは複雑でした • cgroupsでプロセス・グループの作成を⼿動で⾏い、その名前をPROCESSOR_GROUP_NAMEに設定するインスタンス間リソース管理 Copyright © 2024, Oracle and/or its affiliates 10 HIGH LOW MED * Autonomous Database, Exadata, ExaCS, ExaCCにて利⽤可能

利⽤イメージ V$RSRC_PLAN[_HISTORY]ビューに列 (CPU_COUNT、CPU_MIN_COUNT、CPU_SCOPE) が追加され、確認が可能になっているインスタンス間リソース管理 Copyright © 2024, Oracle
and/or its affiliates 11 SQL> connect / as sysdba SQL> ALTER SYSTEM set resource_manager_plan = DEFAULT_CDB_PLAN ; SQL> ALTER SYSTEM SET CPU_COUNT = 4 ; SQL> ALTER SYSTEM SET CPU_MIN_COUNT = 2 ; SQL> ALTER SYSTEM SET RESOURCE_MANAGER_CPU_SCOPE = SERVER_WIDE ; ... <データベース再起動> ... SQL> select CPU_MANAGED,CPU_SCOPE,CPU_COUNT,CPU_MIN_COUNT from V$RSRC_PLAN where name = 'DEFAULT_CDB_PLAN' ; CPU CPU_SCOPE CPU_COUNT CPU_MIN_COUNT --- ------------- ---------- ------------- ON SERVER_WIDE 4 2 $ cd /sys/fs/cgroup/cpu/ORA_pt1_pt11_8ae34484 $ cat cpu.cfs_period_us cpu.cfs_quota_us cpu.shares 100000 -1 4 $ cat /sys/fs/cgroup/cpuset/ORA_pt1_pt11_8ae34484/cpuset.cpus 0-3 ORA_<DB名>_<ORACLE_SID>_xxxxxxxx cpuset.cpus は CDB で使⽤できる CPU が設定され、それを以下で制御する • CPU_COUNT は cpu.cfs_period_us と cpu.cfs_quota_us で設定 • CPU_MIN_COUNT は cpu.shares で設定

cgroups (control groups) について cpu.cfs_period_us • cgroupによる CPUリソースへのアクセスを再割り当てする⼀定間隔 (マイクロ秒)
を指定 (デフォルトは100000) cpu.cfs_quota_us • cgroup内の全タスクがcpu.cfs_period_usに対して実⾏できる合計時間 (マイクロ秒) で、使⽤できる割合を指定 (デフォルトは-1で、cgroupがCPU時間制限を順守しないことを⽰す) • 300000/100000 は3CPU使⽤できること cpu.shares • cgroup内のタスクで使⽤できる CPU時間の相対的配分を指定 (デフォルトは1024) cpuset.cpus • cgroup内のタスクがアクセスを許可されるCPUを指定 (0-3 など) インスタンス間リソース管理 Copyright © 2024, Oracle and/or its affiliates 12 SQL> ALTER SYSTEM SET CPU_COUNT = 3 ; ... CPU CPU_SCOPE CPU_COUNT CPU_MIN_COUNT --- ------------- ---------- ------------- ON SERVER_WIDE 3 2 $ cd /sys/fs/cgroup/cpu/ORA_pt1_pt11_8ae34484 $ cat cpu.cfs_period_us cpu.cfs_quota_us cpu.shares 100000 300000 4 SQL> ALTER SYSTEM SET CPU_MIN_COUNT = 1 ; ... CPU CPU_SCOPE CPU_COUNT CPU_MIN_COUNT --- ------------- ---------- ------------- ON SERVER_WIDE 3 1 $ cd /sys/fs/cgroup/cpu/ORA_pt1_pt11_8ae34484 $ cat cpu.cfs_period_us cpu.cfs_quota_us cpu.shares 100000 300000 2

Copyright © 2024, Oracle and/or its affiliates 13 SQL履歴とSQL計画管理の強化 •
SQL履歴の監視とレポート • ⾃動SQL計画管理の機能強化 • 検証済みSQL計画ベースラインの追加

クエリ履歴の追跡とレポーティングの機能拡張機能概要 • セッション内でユーザが実⾏したSQLの追跡機能が強化され、より完全な履歴を追跡し、レポートすることが可能になった (すべてのSQL⽂の情報をメモリ容量に応じて「ベスト・エフォート」で監視する） • カーソル・キャッシュ (v$sql*) ビューやSQL監視より追跡が強化され、各ユーザ・セッションで発⾏したすべてのSQL
(クエリ、DML、DDL) の履歴が記録される (バックグラウンドSQLおよび再帰的SQLを除く) • セッションごとにSQL履歴に記録されるSQL⽂の数は50まで • カーソル・キャッシュ (v$SQL*) ビューではできない、SQL⽂の実⾏ごとの実⾏統計やユーザ・セッションが追跡される • リアルタイムSQL監視ではヒントによって強制されないと追跡されない5秒未満の⾮パラレル・クエリも含む • 失敗したSQLもERROR_NUMBERとともに記録される • セッションが切断されると、そのセッションのSQL履歴はなくなる (dba_xxx のようなビューはない) • 初期化パラメータSQL_HISTORY_ENABLEDが追加され、有効または無効にすることができまる (デフォルトFALSE) • alter system, alter session で変更可能 (PDB単位での変更も可能) メリット • アプリケーション開発者と開発運⽤者は、データベース上で実⾏されるクエリに関する詳細な洞察を得ることができ、これによりアプリケーションをより適切に管理し、最適化することができる SQL履歴の監視とレポート Copyright © 2024, Oracle and/or its affiliates 15

情報の格納動作 • 取得された情報は SGA (共有プール) に格納される • 共有プールの 2% まで使⽤される
• SQL履歴データは CDB レベルの SGAに格納され、セッションごとに関連付けられる • SQL_TEXT は 100 バイトまで取得 • セッション毎に最⼤ 50 エントリまでしか格納されない • 各セッションで最後に実⾏したクエリ、DML、DDL、alter session/alter system⽂、grant/revoke⽂、PLSQLブロックを追跡 • セッションを終了するとそのセッション情報は削除される • SQL履歴のエントリはディスクに保存されない • alter system flush query_history で⾃セッションの情報を削除可能設定により 1-2% 程度の負荷増が⾒込まれるため、デフォルトで無効になっている SQL履歴の監視とレポート Copyright © 2024, Oracle and/or its affiliates 16

SQL履歴データへのアクセス追跡された情報は以下の⽅法で表⽰できる (DBAはすべてのユーザーセッションの履歴を、⾮DBAユーザーは⾃分が所有するすべてのセッションの履歴のみを閲覧できる) • [g]v$sql_historyビュー • 部分的なSQLテキスト(最初の100⽂字)、統計情報、SQLタイプ、セッション情報、エラー情報、プラン・ハッシュを含む SQL履歴データ SQL履歴の監視とレポート
Copyright © 2024, Oracle and/or its affiliates 17

利⽤イメージ（V$SQL_HISTORYビュー） SQL履歴の監視とレポート Copyright © 2024, Oracle and/or its affiliates 18
SQL> alter system set SQL_HISTORY_ENABLED = TRUE ; SQL> select col3,avg(id) from t2 group by col3; ... SQL> select sql_id, elapsed_time, cpu_time, physical_read_bytes, user_io_wait_time, sql_text 2 from V$SQL_HISTORY order by 2; SQL_ID ELAPSED_TIME CPU_TIME PHYSICAL_READ_BYTES USER_IO_WAIT_TIME SQL_TEXT ------------- ------------ --------- ------------------- ----------------- -------------------------------------------------- 1261gvnxmdgan 0 0 0 0 alter system set SQL_HISTORY_ENABLED = TRUE 6u5zqzz2nm55c 3 3 0 0 SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS $SESSION','USERNAME'), USER) FROM SYS.DUAL fjy3hvnt2x1h2 10 10 0 0 select sql_id,elapsed_time,cpu_time,physical_read_ bytes,user_io_wait_time,sql_text from v$sql_histor fjy3hvnt2x1h2 11 11 0 0 select sql_id,elapsed_time,cpu_time,physical_read_ bytes,user_io_wait_time,sql_text from v$sql_histor 7jmwzc81n3t9w 21 0 0 0 select col3,avg(id) from t2 group by col3 ...

リアルタイムSQL計画管理機能概要 • SQL計画管理が強化され、SQLのパフォーマンス低下をバックグラウンド・プロセスではなくリアルタイムで検出し、防⽌する (BaseDB Enterprise Editionで利⽤可能になった) • パフォーマンスの低下を防⽌
• 実⾏計画の変更はハードパース時に検出される (⾃動STSを活⽤) • ⾃動STSは23aiからADB以外でもデフォルトが有効になっている • 最初の実⾏後にSQL性能が以前の実⾏計画の性能と⽐較される • 以前の実⾏計画の性能が良い場合、以前の実⾏計画がSQL⽂の今後の実⾏で強制される • SQL実⾏計画を即座に発⾒、テスト、修復することでリスクを低減する • アプリケーションの可⽤性を向上 • 修復は⾃動で⾏われるため、⼿作業による時間がかからないメリット • SQLのレスポンス・タイム低下の原因となるSQL実⾏計画の問題をプロアクティブに処理する⾃動SQL計画管理の機能強化 Copyright © 2024, Oracle and/or its affiliates 20 RESULTS ⾃動SQL計画管理、⾃動STSの詳細は以下を参照してください Tech Night #64 「Automatic SQL Plan Management は使えるか」

⾃動STS (SQLチューニング・セット) について 19cから⾃動索引のために⾃動STSが追加された 19.08から以下の問題によりデフォルトが無効になった • 多くのSQL⽂をキャプチャし、多くのSYSAUX表領域を占有するという不満により • ⾃動STSを使⽤する機能が無効でも (機能が使⽤できない⾮Exadata/⾮ADBでも)
実⾏された • ADBでは⾃動SQL計画管理がデフォルト有効のため⾃動STSもデフォルトが有効様々な⾃動機能で使⽤されている • ⾃動SQL計画管理(19c)、⾃動パーティション化(19cのADB)、⾃動マテリアライズド・ビュー(21c)、⾃動ゾーン・マップ(21c) 問題点を改善するために、いくつかの機能拡張 (BUG 32189400など) を⾏い、23aiからデフォルトが有効になった • 主な改善 (BUG 32189400) • 構⽂解析スキーマがOracleが保持するスキーマ (SYS、SYSTEMなど) の場合はSQL⽂を取得しない • 保持期間は⾃動索引と関連付けられていたが独⾃に管理できるようにした • 異なるリテラル値を持つSQL⽂を1000件から100件に制限し、異なるパージ・ポリシー (パージ頻度を短く) を使⽤ • 同じ“force matching signature”(FMS)で、異なる"exact matching signature"を持つSQL⽂ • Insert into table values⽂の取り込みをスキップする⾃動SQL計画管理の機能強化 Copyright © 2024, Oracle and/or its affiliates 21

SQL⽂のハードパース時に異なるプランが⽣成されたが、新しいプランは最初の実⾏に使われるリアルタイムSQL計画管理 Copyright © 2024, Oracle and/or its affiliates 22
SQL⽂の実⾏新しいプランの使⽤ Automatic SQL Tuning Set

Automatic SQL Tuning Set 初回実⾏後、パフォーマンス統計を以前の計画と⽐較するリアルタイムSQL計画管理 Copyright © 2024, Oracle
and/or its affiliates 23 新しいプランの実⾏統計を、最⼩コスト・プランの実⾏統計と⽐較する

新しい計画が以前の計画よりも悪い場合、以前の計画を使⽤してSQL計画ベースラインを作成するリアルタイムSQL計画管理 Copyright © 2024, Oracle and/or its affiliates 24
承認された Automatic SQL Tuning Set より良いプラン SQL計画ベースライン

今後は以前のプランが使⽤されるリアルタイムSQL計画管理 Copyright © 2024, Oracle and/or its affiliates 25
最終的には、すべてのプランがASTSに取り込まれる承認された Automatic SQL Tuning Set SQL⽂はハードパースされる承認されたSQL計画ベースラインが使⽤される SQL計画ベースライン

リアルタイムSQL計画管理利⽤イメージ（⾼頻度⾃動SPM展開アドバイザ・タスク、⾃動SQL計画管理の設定）リアルタイムSQL計画管理 (フォアグラウンドで実⾏) を可能にする • exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK', 'AUTO’) •
以下のパラメータも設定される • ALTERNATE_PLAN_SOURCE : FOREGROUND • ALTERNATE_PLAN_BASELINE : AUTO ⾃動SQL計画管理 (バックグラウンド) を可能にする • exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK', 'ON’) • ALTERNATE_PLAN_SOURCE : SQL_TUNING_SET • ALTERNATE_PLAN_BASELINE : AUTO 無効化 (ADB以外ではデフォルトでオフ) • exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK', 'OFF') • ALTERNATE_PLAN_SOURCE : SQL_TUNING_SET • ALTERNATE_PLAN_BASELINE : EXISTING 26 Copyright © 2024, Oracle and/or its affiliates

リアルタイムSQL計画管理利⽤イメージ（テスト表の作成） 27 Copyright © 2024, Oracle and/or its affiliates
-- テスト表の作成 create table tab1 (col1 number ,col2 number,col3 char(100),col4 char(100)) nocompress; declare begin for i in 1..8000000 loop insert into tab1 values(i,i,i || 'aaaaaaa',LPAD('a',trunc(DBMS_RANDOM.VALUE*100),'abcdefghijklmno')); if i mod 5000 = 0 then commit; end if; end loop; end; / -- col2=11の条件でオプティマイザを間違わせるように update tab1 set col2=11 where col2 between 10 and 7000000; create index ix1_tab1 on tab1 (col2); create table tab2 (col1 number ,col2 number,col3 char(100),col4 char(100)) nocompress; ...<同じように2000,000件作成> create unique index ix1_tab2 on tab2 (col1); -- 統計情報の収集(ヒストグラムも収集) SQL> exec dbms_stats.gather_table_stats(NULL,'TAB1',method_opt=>'for all columns size 254',no_invalidate=>false); SQL> exec dbms_stats.gather_table_stats(NULL,'TAB2',method_opt=>'for all columns size 254',no_invalidate=>false);

リアルタイムSQL計画管理利⽤イメージ（リアルタイムSQL計画管理を有効前の動作） 28 Copyright © 2024, Oracle and/or its affiliates
-- ⾼頻度オプティマイザ統計収集のOFFを確認（後でヒストグラムを削除し意図的に実⾏計画を変化させるので、ヒストグラムが作成されないようにする） SQL> Select dbid, task_name, enabled, status, to_char(last_schedule_time, 'DD-MON-YY hh24:mi') LATEST From dba_autotask_schedule_control Where dbid = sys_context('userenv', 'con_dbid') ; DBID TASK_NAME ENABL STATUS LATEST ---------- ---------------------------------------- ----- ---------- ------------------------ 1771651223 Auto STS Capture Task TRUE SUCCEEDED 05-JUN-24 04:51 1771651223 Auto Statistics Management Task FALSE 1771651223 Auto SPM Task FALSE ... -- ⾃動SPM状態の確認 SQL> Select Parameter_Name, Parameter_Value From Dba_Sql_Management_Config Where Parameter_Name like '%SPM%’; PARAMETER_NAME PARAMETER_VALUE ---------------------------------------- -------------------- AUTO_SPM_EVOLVE_TASK OFF -- 検証⽤SQLの実⾏ SQL> set timing on SQL> select /* spm_test1x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 where t1.col1=t2.col1 and t1.col2 = 11; SSUM(T1.COL1) SUM(T2.COL2) COUNT(*) ------------ ------------ ---------- 2.0000E+12 2.0000E+12 1999991 Elapsed: 00:00:01.44

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID 5zh18nzhf1046, child number 0 ------------------------------------- Plan hash value: 3709025667 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 92939 (100)| | | 1 | SORT AGGREGATE | | 1 | 22 | | | | |* 2 | HASH JOIN | | 2000K| 41M| 45M| 92939 (1)| 00:00:04 | | 3 | TABLE ACCESS FULL| TAB2 | 2000K| 22M| | 16565 (1)| 00:00:01 | |* 4 | TABLE ACCESS FULL| TAB1 | 7005K| 66M| | 66784 (1)| 00:00:03 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."COL1"="T2"."COL1") 4 - filter("T1"."COL2"=11) ...<数分待つ>... -- ⾃動STSの確認 SQL> select sql_id,sql_text,EXECUTIONS,PLAN_HASH_VALUE from dba_sqlset_statements where sql_text like 'select /* spm_test1x */%' and sqlset_name = 'SYS_AUTO_STS'; SQL_ID SQL_TEXT EXECUTIONS PLAN_HASH_VALUE ------------- -------------------------------------------------------------------------------- ---------- --------------- 5zh18nzhf1046 select /* spm_test1x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 1 3709025667

-- ヒストグラム削除 SQL> exec dbms_stats.delete_column_stats(user,'tab1','col2',col_stat_type=>'histogram',no_invalidate=>false); SQL> select /* spm_test1x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 where t1.col1=t2.col1 and t1.col2 = 11; ... Elapsed: 00:00:03.13 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID 5zh18nzhf1046, child number 1 ------------------------------------- Plan hash value: 1789327689 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 14 (100)| | | 1 | SORT AGGREGATE | | 1 | 22 | | | | 2 | NESTED LOOPS | | 8 | 176 | 14 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 8 | 176 | 14 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 8 | 80 | 4 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IX1_TAB1 | 8 | | 3 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | IX1_TAB2 | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 12 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T1"."COL2"=11) 6 - access("T1"."COL1"="T2"."COL1")

リアルタイムSQL計画管理利⽤イメージ（リアルタイムSQL計画管理を有効後の動作） 31 Copyright © 2024, Oracle and/or its affiliates
-- リアルタイムSQL計画管理を有効にする（ALTERNATE_PLAN_SOURCE、ALTERNATE_PLAN_BASELINEも設定される） SQL> exec dbms_spm.configure('AUTO_SPM_EVOLVE_TASK', 'AUTO’) ; SQL> select Parameter_Name, Parameter_Value From Dba_Sql_Management_Config Where Parameter_Name like '%SPM%'; PARAMETER_NAME PARAMETER_VALUE ---------------------------------------- -------------------- AUTO_SPM_EVOLVE_TASK AUTO SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK' AND PARAMETER_NAME like 'ALTERNATE_PLAN%’; PARAMETER_NAME VALUE ---------------------------------------- -------------------- ALTERNATE_PLAN_SOURCE FOREGROUND ALTERNATE_PLAN_BASELINE AUTO -- ハード・パースさせる SQL> alter system flush shared_pool; -- 最初は遅い実⾏計画で実⾏される SQL> select /* spm_test1x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 where t1.col1=t2.col1 and t1.col2 = 11; ... Elapsed: 00:00:05.16

リアルタイムSQL計画管理利⽤イメージ利⽤イメージ（リアルタイムSQL計画管理を有効後の動作） 32 Copyright © 2024, Oracle and/or its affiliates
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID 5zh18nzhf1046, child number 0 ------------------------------------- Plan hash value: 1789327689 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 14 (100)| | | 1 | SORT AGGREGATE | | 1 | 22 | | | | 2 | NESTED LOOPS | | 8 | 176 | 14 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 8 | 176 | 14 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 8 | 80 | 4 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IX1_TAB1 | 8 | | 3 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | IX1_TAB2 | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 12 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T1"."COL2"=11) 6 - access("T1"."COL1"="T2"."COL1") Note ----- - This is SQL Plan Management Test Plan

リアルタイムSQL計画管理利⽤イメージ利⽤イメージ（リアルタイムSQL計画管理を有効後の動作） 33 Copyright © 2024, Oracle and/or its affiliates
SQL> select /* spm_test1x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 where t1.col1=t2.col1 and t1.col2 = 11; ... Elapsed: 00:00:01.45 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID 5zh18nzhf1046, child number 2 ------------------------------------- Plan hash value: 3709025667 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 85625 (100)| | | 1 | SORT AGGREGATE | | 1 | 22 | | | | |* 2 | HASH JOIN | | 8 | 176 | 45M| 85625 (1)| 00:00:04 | | 3 | TABLE ACCESS FULL| TAB2 | 2000K| 22M| | 16565 (1)| 00:00:01 | |* 4 | TABLE ACCESS FULL| TAB1 | 8 | 80 | | 66784 (1)| 00:00:03 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."COL1"="T2"."COL1") 4 - filter("T1"."COL2"=11) Note ----- - SQL plan baseline SQL_PLAN_fju6v3nz8hq9g6643e676 used for this statement

リアルタイムSQL計画管理 SQL計画ベースラインの確認 34 Copyright © 2024, Oracle and/or its affiliates
SQL> SELECT sql_handle, plan_name, enabled, accepted, origin ,cpu_time, buffer_gets FROM dba_sql_plan_baselines WHERE sql_text like 'select /* spm_test1x */%'; SQL_HANDLE PLAN_NAME ENA ACC ORIGIN CPU_TIME BUFFER_GETS -------------------- ------------------------------ --- --- ------------------ -------- ----------- SQL_e8e8db1d3e88592f SQL_PLAN_fju6v3nz8hq9g20eac2a6 YES NO FOREGROUND-CAPTURE 4984251 2984875 SQL_e8e8db1d3e88592f SQL_PLAN_fju6v3nz8hq9g6643e676 YES YES FOREGROUND-CAPTURE 1432980 302955 -- SQL計画ベースラインの実⾏計画を表⽰する SQL> SET LINESIZE 150 PAGESIZE 2000 SQL> SELECT t.* FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_e8e8db1d3e88592f')) t;

リアルタイムSQL計画管理 SQL計画ベースラインの実⾏計画の表⽰ 35 Copyright © 2024, Oracle and/or its affiliates
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_e8e8db1d3e88592f SQL text: select /* spm_test1x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 where t1.col1=t2.col1 and t1.col2 = 11 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_fju6v3nz8hq9g20eac2a6 Plan id: 552256166 Enabled: YES Fixed: NO Accepted: NO Origin: FOREGROUND-CAPTURE Plan rows: From re-compilation -------------------------------------------------------------------------------- Plan hash value: 1789327689 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 14 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 22 | | | | 2 | NESTED LOOPS | | 8 | 176 | 14 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 8 | 176 | 14 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 8 | 80 | 4 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IX1_TAB1 | 8 | | 3 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | IX1_TAB2 | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 12 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T1"."COL2"=11) 6 - access("T1"."COL1"="T2"."COL1") ...<続き>... -------------------------------------------------------------------------------- Plan name: SQL_PLAN_fju6v3nz8hq9g6643e676 Plan id: 3709025667 Enabled: YES Fixed: NO Accepted: YES Origin: FOREGROUND-CAPTURE Plan rows: From Auto SQL Tuning Set -------------------------------------------------------------------------------- Plan hash value: 3709025667 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 92939 (100)| | | 1 | SORT AGGREGATE | | 1 | 22 | | | | |* 2 | HASH JOIN | | 2000K| 41M| 45M| 92939 (1)| 00:00:04 | | 3 | TABLE ACCESS FULL| TAB2 | 2000K| 22M| | 16565 (1)| 00:00:01 | |* 4 | TABLE ACCESS FULL| TAB1 | 7005K| 66M| | 66784 (1)| 00:00:03 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."COL1"="T2"."COL1") 4 - filter("T1"."COL2"=11)

指定されたSQLに最適な承認済みSQL計画ベースラインを作成機能概要 • パフォーマンスが低下したSQLに対して、最適な実⾏計画をSQL計画ベースラインに作成することが１ステップで可能になった (新しいDBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINEファンクションを実⾏するのみ) 1. カーソルキャッシュ、AWR、⾃動SQLチューニング・セットからSQL計画管理にプランをロードする 2. SPM展開アドバイザを内部的に使⽤し、最も性能の良い計画を特定する
3. 最適な検証済みSQL計画ベースラインを作成する • 使⽤⽅法 (sql_idを指定するのみ) • exec :report := dbms_spm.add_verified_sql_plan_baseline('<sql_id>'); select :report report from dual; メリット • この機能により、⾃動SQL計画管理を使⽤していない場合でもパフォーマンス管理が向上する (⾃動SQLチューニング・セットを使⽤している場合にSQL計画ベースラインの⼿動作成が簡単になった) 検証済みSQL計画ベースラインの追加 Copyright © 2024, Oracle and/or its affiliates 37

検証済みSQL計画ベースラインの追加 SQL計画ベースラインの⼿動作成（Oracle Database 23aiより前）実⾏計画のロード • DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEファンクション • DBMS_SPM.LOAD_PLANS_FROM_AWRファンクション •
DBMS_SPM.LOAD_PLANS_FROM_SQLSETファンクション SPM Evolve（SPM展開アドバイザ） • DBMS_SPM.CREATE_EVOLVE_TASKファンクション • DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEファンクション 38 Copyright © 2024, Oracle and/or its affiliates

検証済みSQL計画ベースラインの追加利⽤イメージ（リアルタイムSQL計画管理と同じ表とSQLで検証） 39 Copyright © 2024, Oracle and/or its affiliates
SQL> select /* spm_test2x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 where t1.col1=t2.col1 and t1.col2 = 11; ... Elapsed: 00:00:01.28 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID 0s7cgu5d8v05h, child number 0 ------------------------------------- Plan hash value: 3709025667 ------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| ------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | | 1 | SORT AGGREGATE | | 1 | 22 | | |* 2 | HASH JOIN | | 2000K| 41M| 45M| | 3 | TABLE ACCESS FULL| TAB2 | 2000K| 22M| | |* 4 | TABLE ACCESS FULL| TAB1 | 6964K| 66M| | ------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."COL1"="T2"."COL1") 4 - filter("T1"."COL2"=11) -- CURSOR CACHEから削除(ASTSのみに格納させる) SQL> alter system flush shared_pool; -- ヒストグラム削除 SQL> exec dbms_stats.delete_column_stats(user,'tab1','col2’, ... ); SQL> select /* spm_test2x */ ... ; ... Elapsed: 00:00:05.21 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID 0s7cgu5d8v05h, child number 0 ------------------------------------- Plan hash value: 1789327689 ------------------------------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | | 2 | NESTED LOOPS | | 8 | | 3 | NESTED LOOPS | | 8 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 8 | |* 5 | INDEX RANGE SCAN | IX1_TAB1 | 8 | |* 6 | INDEX UNIQUE SCAN | IX1_TAB2 | 1 | | 7 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | ------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T1"."COL2"=11) 6 - access("T1"."COL1"="T2"."COL1")

検証済みSQL計画ベースラインの追加利⽤イメージ（検証済みSQL計画ベースラインの作成） 40 Copyright © 2024, Oracle and/or its affiliates
SQL> SET linesize 1000 pagesize 0 SQL> SET LONGCHUNKSIZE 10000000 LONG 10000000 SQL> var report clob SQL> exec :report := dbms_spm.add_verified_sql_plan_baseline('0s7cgu5d8v05h'); SQL> select :report report from dual; ----------------------------------------------------------------------------------------- SQL Plan Baselines verified for SQL ID: 0s7cgu5d8v05h ------------------------------------------------------------------------------------------ Plan Hash Value Plan Name Reproduced Accepted Source --------------- ------------------------------ ---------- -------- -------------- 3709025667 SQL_PLAN_40aahs4vxccxw6643e676 YES YES SQL TUNING SET 1789327689 SQL_PLAN_40aahs4vxccxw20eac2a6 YES NO CURSOR CACHE ----------------------------------------------------------------------------------------- SQL Handle : SQL_402950c137d633bc SQL Text : select /* spm_test2x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 where t1.col1=t2.col1 and t1.col2 = 11 -----------------------------------------------------------------------------------------

検証済みSQL計画ベースラインの追加利⽤イメージ（SQL計画ベースラインが使⽤されるかの確認） 41 Copyright © 2024, Oracle and/or its affiliates
SQL> select /* spm_test2x */ sum(t1.col1),sum(t2.col2),count(*) from tab1 t1,tab2 t2 where t1.col1=t2.col1 and t1.col2 = 11; ... Elapsed: 00:00:01.27 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR); SQL_ID 0s7cgu5d8v05h, child number 2 ------------------------------------- Plan hash value: 3709025667 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | 85625 (100)| | | 1 | SORT AGGREGATE | | 1 | 22 | | | | |* 2 | HASH JOIN | | 8 | 176 | 45M| 85625 (1)| 00:00:04 | | 3 | TABLE ACCESS FULL| TAB2 | 2000K| 22M| | 16565 (1)| 00:00:01 | |* 4 | TABLE ACCESS FULL| TAB1 | 8 | 80 | | 66784 (1)| 00:00:03 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."COL1"="T2"."COL1") 4 - filter("T1"."COL2"=11) Note ----- - SQL plan baseline SQL_PLAN_40aahs4vxccxw6643e676 used for this statement

Copyright © 2024, Oracle and/or its affiliates 42 Oracle Database
In-Memory 強化 • インメモリ最適化⽇付

Oracle Database In-Memory (DBIM) 強化既に実施済みの機能 Oracle DatabaseTechnology Night #74
「Oracle Database 23c Database In-Memory 新機能」 • インメモリ・ディープ・ベクトル化の機能拡張 • ⾃動インメモリの機能拡張 • ⾃動インメモリ・サイジング • インメモリ・パフォーマンス機能の⾃動有効化 • 新しいインメモリ・アドバイザ • Database In-Memory その他の機能拡張 • 選択しやすいインメモリ列 • インメモリRACレベル・グローバル・ディクショナリ • ハイブリッド Exadata スキャン • Autonomous Database Dedicated のインメモリ • Fast Ingest (Memoptimize for Write) の機能拡張 43 Copyright © 2024, Oracle and/or its affiliates

インメモリ最適化⽇付による DATE クエリの⾼速化機能概要 • インメモリ式フレームワークを活⽤して、DATEコンポーネント (YEAR、 MONTH) を抽出し、簡単にIM列ストアにポピュレートすることができ、 DATEベースのクエリのパフォーマンスが向上する
• これまでのIM仮想列や⾃動IM式で作成の必要がない • 初期化パラメータINMEMORY_OPTIMIZED_DATEをENABLEにすると有効になり、ポピュレーション時に全てのDATE列をインメモリ式にポピュレートする • その後、SQLのEXTRACT関数を使⽤してアクセスできるメリット • 簡単にインメモリ式フレームワークを活⽤して、このようなクエリを最⼤ 6倍⾼速に実⾏できるようになった (IM式を使⽤しない場合に対して) • DATE列に対して抽出された各コンポーネント (例えばMONTH) は、 1⾏あたり1Bのメモリ内オーバーヘッドを追加するだけである In-Memory Optimized Dates Copyright © 2024, Oracle and/or its affiliates 44 SALES order_date extract (month from order_date) extract (year from order_date) Create table SALES ( … , order_date date); Alter table SALES inmemory; Select count(*) from SALES; select extract(month from order_date), sum(order_amount) from SALES where extract(year from order_date) = 2022 group by extract(month from order_date);

利⽤イメージ（これまでとの⽐較）全てのDATE列がIM式にポピュレートされる • ESS (Expression Statistics Store) を使⽤した⾃動IM式やIM仮想列で作成する必要がないこれまでのIM式の作成
(⾃動IM式) In-Memory Optimized Dates Copyright © 2024, Oracle and/or its affiliates 45 SQL> alter session set INMEMORY_OPTIMIZED_DATE=ENABLE; SQL> create table imedate (c1 number,c2 number,c3 date,c4 date); ... SQL> alter table imedate inmemory; -- IMのポピュレート (全てのDATE列がポピュレートされる) SQL> select count(*) from imedate; SQL> SELECT column_name FROM v$im_imecol_cu i, dba_objects o WHERE i.objd=o.data_object_id AND o.object_name='IMEDATE'; COLUMN_NAME ------------------------------ SYS_IME_DATE_MONTH_C3 SYS_IME_DATE_YEAR_C3 SYS_IME_DATE_MONTH_C4 SYS_IME_DATE_YEAR_C4 SQL> create table imedate (c1 number,c2 number,c3 date,c4 date); ... SQL> alter table imedate inmemory; SQL> select count(*) from imedate; SQL> select extract(month from c3), sum(c1) from imedate where c2 < 3 group by extract(month from c3) ; -- ESSを使⽤したIM式のキャプチャ (使⽤された式が⾃動キャプチャされる) SQL> exec dbms_stats.flush_database_monitoring_info(); SQL> exec dbms_inmemory_admin.ime_capture_expressions('CURRENT'); SQL> select COLUMN_NAME, sql_expression from dba_im_expressions where table_name = 'IMEDATE'; COLUMN_NAME SQL_EXPRESSION ------------------------------ ----------------------------- SYS_IME000100000061B8BB EXTRACT(MONTH FROM "C3") -- IM式のポピュレート SQL> exec dbms_inmemory_admin.ime_populate_expressions(); SQL> SELECT column_name FROM v$im_imecol_cu i, dba_objects o WHERE i.objd=o.data_object_id AND o.object_name='IMEDATE'; COLUMN_NAME ------------------------------ SYS_IME000100000061B8BB

利⽤イメージ（クエリ実⾏時間はこれまでのIM式と変わらない） In-Memory Optimized Dates Copyright © 2024, Oracle and/or its
affiliates 46 SQL> alter session set INMEMORY_OPTIMIZED_DATE=ENABLE; ... SQL> select extract(month from c3), sum(c1) from imedate where extract(year from c3) = 2023 group by extract(month from c3); 12 rows selected. Elapsed: 00:00:00.02 ----------------------------------------------- | Id | Operation | Name | ----------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | |* 2 | TABLE ACCESS INMEMORY FULL| IMEDATE | ----------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - inmemory("IMEDATE"."SYS_IME_DATE_YEAR_C3"=2023) filter("IMEDATE"."SYS_IME_DATE_YEAR_C3"=2023) SQL> alter session set INMEMORY_OPTIMIZED_DATE=DISABLE; ... -- extract(year from c3)のIM式を追加して実⾏ SQL> select extract(month from c3), sum(c1) from imedate where extract(year from c3) = 2023 group by extract(month from c3); 12 rows selected. Elapsed: 00:00:00.02 ----------------------------------------------- | Id | Operation | Name | ----------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | |* 2 | TABLE ACCESS INMEMORY FULL| IMEDATE | ----------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - inmemory("IMEDATE"."SYS_IME000100000061B8BC"=2023) filter("IMEDATE"."SYS_IME000100000061B8BC"=2023)

Materialized View(MView) 強化既に実施済みの機能 Oracle Database 23c新機能セミナー「データベース・パフォーマンス関連新機能」 Oracle DatabaseTechnology
Night #72 「Oracle Database 23c データベース・パフォーマンス関連新機能」 • マテリアライズド・ビューでのANSI結合のサポート • ANSI結合⽂による完全なクエリ・リライトをサポート • 論理パーティション・チェンジ・トラッキング • より効率的なMviewのリフレッシュと失効追跡のためのLPCTを追加（LPCTリライト） • マテリアライズド・ビューの同時リフレッシュ • 同時リフレッシュの提供（同じオンコミットMViewをシリアライズの必要ない同時リフレッシュが可能に） 48 Copyright © 2024, Oracle and/or its affiliates

⾃動パーティション化を含むようになり、より正確な費⽤対効果分析と幅広いユーザビリティで強化された機能概要 • ⾃動マテリアライズド・ビューの内部アルゴリズムが強化され、データベースの幅広い機能を利⽤できるようになりました • マテリアライズド・ビューの⾃動パーティション化をサポートした • 基礎となるファクト表がパーティション化されている場合のみ • 使⽤されるのはリスト・パーティション化のみ
• ⾃動マテリアライズド・ビューの選択を以下のバランスを考慮した内部コストモデルに強化 • アクセス・メリット • メンテナンス（リフレッシュ）コスト • 実⾏頻度メリット • マテリアライズド・ビューを維持するための運⽤上のオーバーヘッドとクエリ・パフォーマンスとの間のトレードオフのバランスを⾃動的に調整する⾃動マテリアライズド・ビューの機能拡張 Copyright © 2024, Oracle and/or its affiliates 49 * Autonomous Database, Exadata, ExaCS, ExaCCにて利⽤可能

⾃動マテリアライズド・ビューとは⾃動マテリアライズド・ビューの機能拡張 Copyright © 2024, Oracle and/or its affiliates データベース・パフォーマンス・リポジトリ
• [19c] Automatic SQL Tuning Set (ASTS) • [21c] Object Activity Tracking System (OATS) • Automatic Workload Repository (AWR) 分析⾼頻度⾃動タスク • 候補の特定、検証、監視のためのエキスパート・システム最適化された物理データベース • ⾃動マテリアライズド・ビュー [21c] 実装 Feedback 50

⾃動マテリアライズド・ビューとは⾃動マテリアライズド・ビューの機能拡張 Copyright © 2024, Oracle and/or its affiliates データベース・パフォーマンス・リポジトリ
• [19c] Automatic SQL Tuning Set (ASTS) • [21c] Object Activity Tracking System (OATS) • Automatic Workload Repository (AWR) 分析⾮表⽰の候補⾃動索引と同じように ASTS SQL⽂のテスト実⾏によって検証される最適化された物理データベース • ⾃動マテリアライズド・ビュー [21c] 実装 Feedback 51

⾃動マテリアライズド・ビューとはこれまで⼿動で実施していたワークロードの監視から、MVおよびMVログの作成、メンテナンスを⾃動で実施できるように • ⾃動作成 • データベースのワークロードに基づいてMV候補を⽣成する • オブジェクト・アクティビティ・トラッキング・システム (OATS) によって提供されるワークロード情報
• ⾮表⽰の空MV候補を作成 • オプティマイザを使⽤してASTSクエリをテスト・パースし、最適な候補を特定する • ⾮表⽰のMV候補を構築し、作業負荷クエリのサンプルに対してパフォーマンス改善をテストする • 費⽤対効果を予測 • システムリソースのコスト: ソースデータと⼀致させるためにMVをリフレッシュする必要がある • メリット: SQLのパフォーマンス向上とシステムリソースの削減 • ⾃動メンテナンス • ⾼頻度タスク・フレームワークでリフレッシュをスケジューリング • 予測アルゴリズムを使⽤することで、パフォーマンスの利点を最⼤化し、アプリケーションのワークロードに対するリフレッシュの影響を最⼩化する⾃動マテリアライズド・ビューの機能拡張 Copyright © 2024, Oracle and/or its affiliates 52

インターフェースと使い⽅ (DBMS_AUTO_MVのサブプログラム) ⾃動マテリアライズド・ビューの使い⽅ Copyright © 2024, Oracle and/or its affiliates
NO IMPLEMENT RECOMMEND REFRESH DBA_AUTO_MV_ANALYSIS_RECOMMEN DATIONS (推奨事項の確認とDDL⽂を適応) CONFIGURE REPORT_LAST_ACTIVITY /REPORT_ACTIVITY REPORT ONLY REPORT ONLY IMPLEMENT AUTO_MV_MODE DROP_AUTO_MV (不要なMVがあれば削除) AUTO_MV_MAINT_TASK ENABLE DISABLE DBMS_AUTO_MVのサブプログラム⽤途 CONFIGURE ⾃動MViewの動作を制御する RECOMMEND 指定されたSTSに基づいた推奨作成 • ⼿動で⾃動MView分析および検証を実⾏する DROP_AUTO_MVS ⾃動マテリアライズド・ビューの削除 REFRESH ⾃動MView (古くなったMView) のリフレッシュの強制実⾏ REPORT_ACTIVITY 特定期間内の⾃動MViewに関するレポート REPORT_LAST_ACTIVITY 直近の⾃動MViewに関するレポート 54

制御パラメータの設定 (CONFIGUREプロシージャ) ⾃動マテリアライズド・ビューの動作を制御する • 構⽂お使いの環境のすべてのコンフィギュレーション設定を確認⾃動パーティション化のインターフェースと使い⽅ Copyright © 2024,
Oracle and/or its affiliates dbms_auto_mv.configure(parameter_name, parameter_value, allow); SQL> SELECT parameter_name,parameter_value,last_updated,updated_by FROM dba_auto_mv_config; PARAMETER_NAME PARAMETER_VALUE LAST_UPDATED UPDATED_BY ----------------------------------- --------------- --------------- --------------- AUTO_MV_MAINT_REPORT_RETENTION 31 AUTO_MV_MAINT_TASK DISABLE AUTO_MV_MODE OFF AUTO_MV_RETENTION 33 ... 55

制御パラメータの設定 (CONFIGUREプロシージャのパラメータ) ⾃動マテリアライズド・ビューの使い⽅ Copyright © 2024, Oracle and/or its affiliates
56 パラメータ名概要 AUTO_MV_MODE ⾃動MVの機能を使⽤するかどうかを設定 IMPLEMENT : 有効 OFF: 無効 (デフォルト) REPORT ONLY : 推奨事項が⽣成され内部リポジトリに格納される AUTO_MV_MAINT_TASK MVの⾃動メンテナンスをアクティブ化および⾮アクティブ化 (Default : DISABLE) ENABLE : 有効 DISABLE : 無効(デフォルト) CLEANUP_AND_DISABLE : 全ての⾃動MVを削除しタスクを無効化 AUTO_MV_SPACE_BUDGET ⾃動MVが利⽤する領域のサイズ (Default :ユーザー表の合計サイズの10%) デフォルト表領域を利⽤ (AUTO_MV_DEFAULT_TABLESPACEを未使⽤) 時のみ AUTO_MV_DEFAULT_TABLESPACE ⾃動MVが利⽤する表領域 (Default : NULL) AUTO_MV_TEMP_TABLESPACE ⾃動MVが利⽤する⼀時表領域 (Default : NULL) AUTO_MV_RETENTION ⾃動MVがこの期間問合せで使⽤されないと削除 (Default : 33⽇) AUTO_MV_ANALYZE_REPORT_RETENTION 分析および推奨履歴を保存する最⼤⽇数 (Default : 31⽇) AUTO_MV_ANALYZE_WORKLOAD_WINDOW 推奨事項を作成する最新スナップショットからの最⼤時間 (Default : 24時間) AUTO_MV_ANALYZE_WORKLOAD_MIN_TIME 推奨事項の対象となる問合せの最⼩時間を秒単位で指定 (Default : 120秒) AUTO_MV_SCHEMA スキーマ単位で有効/無効の制御 (allowパラメータ) AUTO_MV_APP_MODULE アプリケーション・モジュール単位で有効/無効の制御 (allowパラメータ) AUTO_MV_VERIFY_REPORT_RETENTION 検証履歴を保存する最⼤⽇数 (Default : 31⽇) AUTO_MV_MAINT_REPORT_RETENTION ⾃動MVのメンテナンスの履歴を保存する最⼤⽇数 (Default : 31⽇)

推奨事項の作成 (RECOMMENDファンクション) 指定STSに基づいた⾃動MV分析や検証をコマンドラインから⼿動で実⾏する • 構⽂ • SYS_AUTO_STSを使⽤して、過去24時間の推奨事項を⽣成およびレポートする⾃動マテリアライズド・ビューの使い⽅ Copyright ©
2024, Oracle and/or its affiliates dbms_auto_mv.recommend(sts_owner, sts_name, workload_start_time, workload_end_time, automv_mode); PARAMETER _NAME ⽤途 sts_owner SQL チューニングセットの所有者の名前 (デフォルトはSYS) sts_name SQL チューニングセットの名前 (デフォルトはSYS_AUTO_STS) workload_start_time ワークロードウィンドウの開始時刻 (デフォルトはAUTO_MV_ANALYZE_WORKLOAD_WINDOWで定義された時間数をSYSDATEから引いた値) workload_end_time ワークロードウィンドウの終了時刻 automv_mode ⾃動マテリアライズド・ビューのモードを設定する REPORT_ONLY: 推奨事項のみを出⼒ (デフォルト) IMPLEMENT: 推奨される⾃動マテリアライズド・ビューが検証され、実装される SQL> var exec_name varchar2(200); SQL> begin :exec_name := dbms_auto_mv.recommend(); end; / SQL> SELECT RECOMMENDATIONS FROM DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS WHERE execution_name = :exec_name; 57

アクティビティ・レポート⾃動MVの各タスクのアクティビティおよび使⽤状況に関するレポートを⽣成する • DBMS_AUTO_MV.REPORT_ACTIVITYファンクション（特定期間のアクティビティ・レポート） • DBMS_AUTO_MV.REPORT_LAST_ACTIVITYファンクション（最後のアクティビティ・レポート）⾃動マテリアライズド・ビューの使い⽅ Copyright © 2024,
Oracle and/or its affiliates 58 SQL> set linesize 300 trims on pagesize 1000 long 100000 SQL> column report format a120 SQL> select dbms_auto_mv.report_activity(sysdate-30, -- 開始⽇時 null, -- 終了⽇時 'text', -- レポート種別 (TEXT,HTML,XML) 'all', -- 出⼒するセクション (ALL,SUMMARY,MV_DETAILS,QUERY_DETAILS VERIFICATION_DETAILS) 'typical') -- レベル (BASIC,TYPICAL,ALL) report FROM dual;

⾃動MVの削除 (DROP_AUTO_MVSプロシージャ) 指定STSに基づいた⾃動MV分析や検証をコマンドラインから⼿動で実⾏する • 構⽂⾃動マテリアライズド・ビューの使い⽅ Copyright © 2024, Oracle
and/or its affiliates dbms_auto_mv.drop_auto_mvs(owner, mv_name, allow_recreate); PARAMETER _NAME ⽤途 owner ⾃動マテリアライズド・ビューの所有者の名前 mv_name ⾃動マテリアライズド・ビューの名前 allow_recreate 削除された⾃動マテリアライズド・ビューの⾃動作成を有効または無効にする FALSE: 削除された⾃動マテリアライズド・ビューの⾃動作成を無効 (デフォルト) TRUE: 削除された⾃動マテリアライズド・ビューの⾃動作成を有効 59

その他（関連ビュー）設定を確認するビュー • DBA_AUTO_MV_CONFIG 分析およびチューニング(Analyze)に関するビュー • DBA_AUTO_MV_ANALYSIS_ACTIONS • アクション、コマンド、アドバイザ固有のフラグ、コマンド・パラメータなど
• DBA_AUTO_MV_ANALYSIS_EXECUTIONS • 分析およびチューニングの実⾏に関する情報 (同時実⾏数、ユーザーが要求したDOP、実⾏終了時の実際のDOP、ステータス、関連アドバイザ、情報、エラーメッセージなど) を表⽰ • DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS • ⾃動MVに関連付けられた推奨 • DBA_AUTO_MV_ANALYSIS_REPORT • タスク名と実⾏名、ジャーナル・エントリの連番、ジャーナルのメッセージなど • DBA_AUTO_MV_ANALYSIS_TASK • タスク識別⼦とタスクの説明、作成⽇と最終変更⽇、実⾏データ、親タスク、ステータス、その他の情報など Verifyに関するビュー • DBA_AUTO_MV_VERIFICATION_REPORT • 検証に関連付けられているタスク名、実⾏名およびメッセージ • DBA_AUTO_MV_VERIFICATION_STATUS • 検証の所有者、開始/終了タイムスタンプ、使⽤されるSQL チューニング・セットおよび各検証で使⽤されるSQLパフォーマンス・アナライザ・タスク Refreshに関するビュー • DBA_AUTO_MV_MAINT_REPORT • ⾃動MVのメンテナンス・アクションに関連付けられている⽇付、時間およびメッセージ • DBA_AUTO_MV_REFRESH_HISTORY • ⾃動MVの各リフレッシュの所有者名、ビュー名、⽇付、開始時間と終了時間、経過時間、ステータスおよびエラー番号⾃動マテリアライズド・ビューの使い⽅ Copyright © 2024, Oracle and/or its affiliates 60

OATSとはスナップショット・ベース（アクティビティの定期的なスナップショット） • 設定可能な保持期間 – DBMS_ACTIVITY.CONFIGURE • スナップショットの間隔 (ACTIVITY_INTERVAL_MINUTES, デフォルト15分)
• スナップショットの保存期間 (ACTIVITY_RETENTION_DAYS, デフォルト400⽇) • スナップショットの使⽤可能な領域サイズ (ACTIVITY_SPACE_PERCENT, デフォルトSYSAUXの5%) AWRのようなものではない • さまざまなメトリクスを記録する（テーブル・スキャン、クエリ・リライト、DDLおよびDML操作のカウントなど） • メトリクスの詳細はビュー (DBA_ACTIVITY_TABLE, DBA_ACTIVITY_MVIEW) を参照 API • DBMS_ACTIVITY（CONFIGURE, CREATE_SNAPSHOT, DELETE_SNAPSHOT）ディクショナリ・ビュー • DBA_ACTIVITY_CONFIG（制御する構成パラメータの現在の値） • DBA_ACTIVITY_SNAPSHOT_META（アクティビティ・スナップショットに関する情報） • DBA_ACTIVITY_TABLE（各スナップショット内で取得された表の使⽤状況データ） • DBA_ACTIVITY_MVIEW（各スナップショット内で取得されたマテリアライズド・ビューの使⽤状況データ） Object Activity Tracking System (OATS) Copyright © 2024, Oracle and/or its affiliates 62

DBMS_ACTIVITYパッケージ • DBMS_ACTIVITY. CONFIGURE (OATSの構成パラメータの設定) • DBMS_ACTIVITY. CREATE_SNAPSHOT (アクティビティ・スナップショットを⼿動で作成) •
MS_ACTIVITY. DELETE_SNAPSHOT • ⼊⼒スナップショットID/⼊⼒タイムスタンプ値に基づいて、古いスナップショットを⼿動で削除する Object Activity Tracking System (OATS) Copyright © 2024, Oracle and/or its affiliates 63 SQL> exec DBMS_ACTIVITY.CONFIGURE('ACTIVITY_RETENTION_DAYS', '10'); SQL> BEGIN IF dbms_activity.delete_snapshots(200) THEN dbms_output.put_line('Snapshots Deleted'); ELSE dbms_output.put_line('Snapshots Not Deleted'); END IF; END; / SQL> SELECT dbms_activity.create_snapshot SNAP_ID; SNAP_ID ---------- xxx

ディクショナリ・ビュー • DBA_ACTIVITY_CONFIG (構成パラメータの確認) • DBA_ACTIVITY_SNAPSHOT_META (アクティビティ・スナップショットの確認) Object Activity Tracking
System (OATS) Copyright © 2024, Oracle and/or its affiliates 64 SQL> select CON_DBNAME,PARAMETER_NAME,PARAMETER_VALUE from DBA_ACTIVITY_CONFIG; CON_DBNAME PARAMETER_NAME PARAMETER_ ---------- ------------------------------ ---------- PDB$SEED ACTIVITY_INTERVAL_MINUTES 15 PDB$SEED ACTIVITY_RETENTION_DAYS 400 PDB$SEED ACTIVITY_SPACE_PERCENT 5 PDB$SEED ACTIVITY_SYS_TABLES NO SQL> select * from DBA_ACTIVITY_SNAPSHOT_META order by SNAP_ID; SNAP_ID CON_DBNAME INSTANCE BEGIN_TIME END_TIME FLUSH_ELAPSED_TIME FLU ---------- ----------- -------- ---------------------------- ---------------------------- ---------------------------- --- 452 DB0619_PDB1 1 07-JUL-23 01.34.27.000 AM 07-JUL-23 01.34.33.000 AM +00 00:00:01.000 NO 472 DB0619_PDB1 1 18-JUL-23 05.49.06.000 AM 18-JUL-23 06.15.57.000 AM +00 00:00:01.000 NO ...

ディクショナリ・ビュー • DBA_ACTIVITY_TABLE (表の使⽤状況データ) • DBA_ACTIVITY_MVIEW (マテリアライズド・ビューの使⽤状況データ) Object Activity Tracking
System (OATS) Copyright © 2024, Oracle and/or its affiliates 65 SQL> select SNAP_ID,TABLE_NAME,SCANS,LOADS,LOAD_ROWS,INSERT_ROWS,DELETE_ROWS,UPDATE_ROWS,TRUNCATES,TRUNCATED_ROWS,PARTITION_TRUNCATES 2 from DBA_ACTIVITY_TABLE where CON_DBNAME='DB0921_PDB1' and TABLE_OWNER='TEST23' order by SNAP_ID; SNAP_ID TABLE_NAME SCANS LOADS LOAD_ROWS INSERT_ROWS DELETE_ROWS UPDATE_ROWS ---------- ------------------------------ ---------- ---------- ---------- ----------- ----------- ----------- 603 BIN$CIP4HOxdTGXgY1MAAAq/Hg==$0 0 0 0 0 0 0 738 TAB01 2 1 20000000 0 0 0 739 TAB01 2 0 0 0 0 0 740 TAB01 36 0 0 0 0 0 742 TAB01 13 0 0 0 0 0 743 TAB01 33 0 0 0 0 0 ... SQL> select SNAP_ID,MVIEW_NAME,COMPILES,REWRITES_TEXTMATCH,REWRITES_GENERAL,REWRITES_PARTIAL,REWRITES_DELTA,REWRITES_PCT 2 from DBA_ACTIVITY_MVIEW order by SNAP_ID; SNAP_ID MVIEW_NAME COMPILES REWRITES_TEXTMATCH REWRITES_GENERAL REWRITES_PARTIAL REWRITES_DELTA REWRITES_PCT ---------- ------------------------------ ---------- ------------------ ---------------- ---------------- -------------- ------------ 369 SUM_SALES_PROD_WEEK_MV 1 0 1 1 0 0 370 SUM_SALES_PROD_WEEK_MV 0 0 1 1 0 0 371 SUM_SALES_PSCAT_WEEK_MV02 1 0 2 2 0 0

Copyright © 2024, Oracle and/or its affiliates まとめ 66 1.
リソース管理の強化 2. SQL履歴とSQL計画管理の強化 3. Oracle Database In-Memory(DBIM) 強化 4. Materialized View(MView) 強化 ※機能名の⽇本語表記はマニュアルに合わせてあります。そのままで伝わりにくいものについては括弧書きで追加説明しています

Oracle Database 23c新機能 #5 データベース・パフォーマンス関連新機能前半

Oracle Database 23c新機能 #5 データベース・パフォーマンス関連新機能前半

Video

More Decks by oracle4engineer

Other Decks in Technology

Featured

Transcript