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

Oracle Database 23c新機能 #5 データベース・パフォーマンス関連新機能後半
2024年11月28日実施 Oracle Database Technology Night #84後半の資料です

1. 統合メモリー管理
2. 適応型結果キャッシュ・オブジェクトの除外
3. 自動索引付けの機能拡張
4. 自動データ・クラスタリング
5. 自動ストレージ圧縮
6. Hybrid Columnar Compressionのパフォーマンスおよびディスク効率の向上
7. 新しい問合せ変換(Query Transformation)

Transcript

1. データベース・ パフォーマンス関連新機能後半 Oracle Database Technology Night #84 辻 研⼀郎 ⽇本オラクル株式会社 2024年11⽉28⽇
2. Copyright © 2024, Oracle and/or its affiliates Agenda 2 1. 統合メモリー管理 2. 適応型結果キャッシュ・オブジェクトの除外 3. ⾃動索引付けの機能拡張 4. ⾃動データ・クラスタリング 5. ⾃動ストレージ圧縮 6. Hybrid Columnar Compressionのパフォーマンスおよびディスク効率の向上 7. 新しい問合せ変換（Query Transformation） ※機能名の⽇本語表記はマニュアルに合わせてあります。そのままで伝わりにくい ものについては括弧書きで追加説明しています
3. Copyright © 2024, Oracle and/or its affiliates 3 統合メモリー管理
4. 統合サイズ指定でシンプルなメモリー管理 概要 • 単⼀の初期化パラメータ（memory_size）でインスタンスで使⽤可能な総メモリーの割り当てが可能に • MEMORY_SIZEパラメータの値をもとに、SGA_TARGETやPGA_AGGREGATE_LIMITの値をOracleが計算して設 定、 SGAやPGAのメモリコンポーネント間の柔軟なメモリ割り当てをOracleが実施 • 従来のMEMORY_TARGETと違い、hugepageとともに使⽤可能 • MEMORY_TARGETの場合、DBインスタンスの合計物理メモリが4GBを越える場合⾃動メモリ管理(MEMORY_TARGET)を指 定することはできないという制限があります • Hugepageを指定して割り当てたSGAとPGA間の柔軟なメモリ割り当てをOracleが実施 • 初期化パラメータmemory_sizeでインスタンス全体（CDBレベル）で使⽤可能な合計メモリーサイズを指定（デフォルト︓0） • memory_sizeはALTER SYSTEM⽂で変更が可能（ただし 起動時の値< 変更サイズ < max_memory_sizeの値） メリット • ⼤規模なインスタンスのメモリ管理を⼀つのパラメータで設定可能、SGAやPGAのメモリコンポーネント間の柔軟なメモ リ割り当てをOracleが実施することで管理を簡潔に 統合メモリー管理 Copyright © 2024, Oracle and/or its affiliates 4
5. 統合メモリー管理 パラメータ 関連パラメータ︓ • MEMORY_SIZE = integer [K | M | G] （デフォルト 0） • インスタンス全体で使⽤可能なメモリーのサイズを指定、 PDB毎に指定は不可 • 1536MBからMEMORY_MAX_SIZEまで指定可能 • ALTER SYSTEM⽂で変更が可能（ただし 起動時の値< 変更サイズ < memory_max_sizeの値） • MEMORY_SIZEが0以外の値に設定されている場合、 CDBレベルのSGA_MAX_SIZE、 PGA_AGGREGATE_LIMITおよびSGA_TARGETは無視されます • MEMORY_ MAX_ SIZE = integer [K | M | G]（MEMORY_SIZE初期化パラメータの値） • MEMORY_SIZEパラメータに設定できる最⼤値を設定 • MEMORY_SIZEからOracleデータベースで使⽤可能な物理メモリー・サイズまで指定可能 • 変更には再起動が必要 5 Copyright © 2024, Oracle and/or its affiliates
6. Copyright © 2024, Oracle and/or its affiliates 6 適応型結果キャッシュ・オブジェクトの除外
7. 統計を利⽤して⾮効率なオブジェクトを結果キャッシュから除外 機能概要 • 結果キャッシュの利⽤状況の統計に基づいて、利⽤が有効でないオブジェクトを⾃動でブロックリストに登録し、結果 キャッシュからそのオブジェクトを除外 • 初期化パラメータRESULT_CACH_AUTO_BLOCKLISTで有効化/無効化を制御（デフォルトON) • バックグラウンドで定期的に対象となるテーブルを検出しブロックリストに登録する メリット • 結果キャッシュが有効ではない、または悪影響となるオブジェクトを⾃動的に除外することで、メモリの有効活⽤ • メモリ管理の作業負荷軽減とパフォーマンスの向上 適応型結果キャッシュ・オブジェクトの除外 Copyright © 2024, Oracle and/or its affiliates 7
8. ご参考︓サーバー結果キャッシュ︓繰り返し実⾏される問合せのレスポンスを向上 SQL問合せ結果キャッシュ • SQL問合せの結果セットを共有プールに格納 • ブロックの再読取りおよび結果の再計算という多くのリソースを消費する操作する必要がなくなる • 結果の構成に使⽤されているデータの更新が⾏われた場合、そのキャッシュは⾃動的に無効化される PL/SQLファンクション結果キャッシュ • ファンクションの結果セットを共有プールに格納 適応型結果キャッシュ・オブジェクトの除外 Copyright © 2024, Oracle and/or its affiliates 8
9. 適応型結果キャッシュ・オブジェクトの除外 SQL問合せ結果キャッシュ 使⽤⽅法 作成された結果キャッシュ・オブジェクトの無効化や削除 • LRUアルゴリズムによる削除 • 既存の結果キャッシュの全削除はDBMS_RESULT_CACHE.FLUSHプロシージャで可能 • 個別キャッシュの無効化はDBMS_RESULT_CACHE.INVALIDATE/INVALIDATE_OBJECTで可能 特定のオブジェクトの結果キャッシュの除外（キャッシュを作成しない） • ブロックリストへの登録 • ⼿動(19c〜)、⾃動(23ai) 9 Copyright © 2024, Oracle and/or its affiliates
10. ブロックリストへの登録、登録されたオブジェクトの操作 ⼿動によるブロックリストへの登録 (19c〜） • dbms_result_cacheパッケージを利⽤。19cのときはblack_listだったが23aiでblock_listに変更 • block_list /object_block_list • block_list_remove/object_block_list_remove • block_list_add/object_block_list_add • block_list_clear/object_block_list_clear ブロックリストへの⾃動登録（23ai〜） • 統計情報から⾃動的にバックグラウンドプロセスが登録。 • ブロック・リストに登録するために使⽤される統計の⼀部 • 無効化メッセージのラウンドトリップ時間。 • オブジェクト当たりの最近の無効化の数。 • オブジェクト当たりの保存合計時間。 • 無効化の合計コスト • ブロックリストに登録されたオブジェクトはdbms_result_cacheパッケージで操作可 適応型結果キャッシュ・オブジェクトの除外 Copyright © 2024, Oracle and/or its affiliates 10
11. Copyright © 2024, Oracle and/or its affiliates 11 ⾃動索引付けの機能拡張
12. 範囲述語を使⽤してフィルタ処理された列、およびファンクション索引のサポート • 機能概要 • ⾃動索引付けの索引付与の評価にDMLのオーバーヘッドも考慮されるようになりました。 • ⾃動索引付け⾃体はExadata Onlyの機能で、デフォルトはOFF • ⾃動索引付けにDML考慮の部分は、デフォルトでON • メリット • DML操作の影響を評価することが可能になることで、より精密な⾃動索引付けが可能に ⾃動索引付けの機能拡張 Copyright © 2024, Oracle and/or its affiliates 12
13. DMLオーバーヘッドの計算の有効/無効化と設定確認 索引は問合せのパフォーマンス向上をもたらしますが、INSERT、UPDATE、DELETEなどのDML操作においては、索引 を更新する必要が発⽣し、オーバーヘッドが発⽣します。機能拡張では、新しい索引が有益であるかどうかを判断する際 に、このオーバーヘッドを考慮します。この機能は、CONFIGUREパラメータAUTO_INDEX_INCLUDE_DML_COST によって制御されます このパラメータの現在の設定を以下で確認 ⾃動索引付けの機能拡張 Copyright © 2024, Oracle and/or its affiliates 13 --DMLコスト加味を有効に設定（⾃動索引付け有効化時のデフォルト） SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_INCLUDE_DML_COST','IMPLEMENT') -- DMLコスト加味を無効に設定 SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_INCLUDE_DML_COST','OFF') SQL> SELECT parameter_name,parameter_value FROM DBA_AUTO_INDEX_CONFIG WHERE parameter_name = 'AUTO_INDEX_INCLUDE_DML_COST';
14. Copyright © 2024, Oracle and/or its affiliates 14 ⾃動データ・クラスタリング
15. ⾃動データ・クラスタリング ゾーンマップおよび属性クラスタリングの⾃動適⽤ • 機能概要 • ユーザーの介⼊がほとんどない形で、アプリケーションで使⽤されるクエリのキャプチャデータをもとに、分析検証し、属 性クラスタリングやゾーンマップを適⽤することができる。 • 現在、Exadata Only（Exadata/ExaDB-D/ExaDB-C@C）で使⽤可能 • ゾーンマップにはpartitioningオプションが必要、属性クラスタリングはEE標準機能 • 21cでのAutomatic Zonemapの機能拡張 • メリット • 実ワークロードに応じた形で属性クラスタリングやゾーンマップの効果のレポート作成および適⽤の管理負荷軽減 • ゾーンマップと属性クラスタリングの効果により、特にDWHワークロードのパフォーマンスが向上が期待できる 15 Copyright © 2024, Oracle and/or its affiliates
16. ⾃動データ・クラスタリング ご参考︓ゾーンマップ（12cR1 - ） • ゾーンマップとは • データの格納情報をユーザー・オブジェクトとして保持 • 指定されたカラムデータのブロックレンジごとの最⼩値・最⼤値 • 問合せの述語に基づき、処理に不要なデータ・ブロックに対するRead IOを削減（プルーニング） • アプリケーションに透過的に性能向上を実現 • Exadata Storage Indexに類似 • 索引、パーティション、SmartScan、Storage Indexといった機能と併⽤可能 16 Copyright © 2024, Oracle and/or its affiliates
17. ⾃動データ・クラスタリング ご参考︓属性クラスタリング（12cR1 - ） • 属性クラスタリングとは • データを整えつつ物理的に格納 • 物理的に近接したエリアに格納される • ユーザー定義による構成 • クラスタ・タイプ • Clustering by Linear order • 単⼀表での使⽤を推奨 • Clustering by Interleaved order • 結合表での使⽤を推奨 17 Copyright © 2024, Oracle and/or its affiliates
18. ⾃動データ・クラスタリング ⼿順︓キャプチャ（⾃動）→推奨のための設定→推奨事項⽣成→検証→適⽤ • ワークロードのキャプチャ • ⾃動STSを使⽤、⼿動のSTSを⽤意することも可 • 設定 • DBMS_AUTO_CLUSTERING.CONFIGUREを⽤いてパラメータの設定。パラメータの内容は、スキーマの指定や、何秒以上のクエリを対象とするか、 ログの保持期間など • 属性クラスタリングとゾーンマップの推奨事項を⽣成 • デフォルトでは、⾃動STSに格納されたSQLワークロードを使⽤。⼿動で取得したSTSを指定することも可能。推奨に含めたいテーブル名の指定も可 • DBMS_AUTO_CLUSTERING.RECOMMEND_CLUSTERING_METHODで⽣成し、 dba_auto_clustering_recommendationsで確認 • 推奨事項を検証 • DBMS_AUTO_CLUSTERING.VERIFY_RECOMMENDATIONを実⾏、テーブル名の指定も可 • 推奨されるクラスタリング⽅法とゾーン マップを使⽤した場合と使⽤しない場合のアプリケーション ワークロードのパフォーマンスを⽐較。改善の場合 VERIFIEDとマークされ、改善されなかった場合REJECTEDとマークされます • 推奨事項を適⽤ • DBMS_AUTO_CLUSTERING.APPLY_RECOMMENDATIONを実⾏ • 上記でVERIFIEDとマークされたDDLを表に適⽤。オンライン表再定義により既存データも含めて変更を適⽤する⽅法と、これからのデータに対して属 性クラスタリングやゾーンマップを適⽤する⽅法の2種類があります。 18 Copyright © 2024, Oracle and/or its affiliates
19. Copyright © 2024, Oracle and/or its affiliates 19 ⾃動ストレージ圧縮
20. ⾃動ストレージ圧縮 後からHCC圧縮 • 機能概要 • 最初に⾮圧縮でダイレクトロードを実⾏し、バックグラウンドで徐々にHCC(Hybrid Columnar Compression)形 式の圧縮を実⾏ • 新しくロードされたデータに変更（DML）がなくなるまで待機し、バックグラウンドでAutoTaskにより徐々に圧縮。 • ⾃動圧縮が開始されるまでの待機時間（＝DMLアクティビティがなし）を指定できます。デフォルトは1440分（１⽇） • デフォルトはオフ • HCCが利⽤可能な環境で使⽤可能 • Exadata（Exadata/ExaDB-D/ExaDB-C@C）、ODA、Base DB EE以上、OnP EE(ZFS,Axitom,FS1ストレージ) • メリット • HCC表へのダイレクトロードの実⾏時間の短縮 • HCCのロード時のオーバーヘッドを排除した形で⾼圧縮を享受 20 Copyright © 2024, Oracle and/or its affiliates
21. ⾃動ストレージ圧縮 設定と確認 ⾃動ストレージ圧縮の有効化と閾値設定 モニタリング 21 Copyright © 2024, Oracle and/or its affiliates --⾃動圧縮データの移動のモニター値 select name, value from v$sysstat where name like 'Auto compression data%'; NAME VALUE ------------------------------------------------ -------- Auto compression data movement success 1 --データ移動の試⾏の成功数 Auto compression data movement failure 0 --データ移動の試⾏の失敗数 Auto compression data moved 6 --ここが⾮圧縮時のセグメントサイズと⼀致したら完了。複数表が 対象の場合、それらすべて含まれます - ⾃動ストレージ圧縮の有効化（デフォルト無効） exec dbms_ilm_admin.enable_auto_optimize; --無効化するときは以下 --exec dbms_ilm_admin.disable_auto_optimize; --変更がないことを判断する⾮アクティブ期間を設定 (デフォルト1440分) exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.auto_optimize_inactivity_threshold, 1440); 前提条件︓ PDBで、初期化パラメータHEAT_MAP=ON 表は、HCCを指定して、SEGMENT SPACE MANAGEMENT AUTO およびAUTOALLOCATEを使⽤する表領域に存在する必要があります
22. Copyright © 2024, Oracle and/or its affiliates 22 Hybrid Columnar Compressionのパフォーマンス およびディスク効率の向上
23. Hybrid Columnar Compressionのパフォーマンスおよびディスク効率の向上 HCCの内部アルゴリズムの改良 • 機能概要 • ハイブリッド列圧縮 (HCC) の圧縮アルゴリズムの改良。圧縮および解凍速度の⾼速化、新しく作成された HCC 圧縮テーブルまたは再構築された既存の HCC 圧縮テーブルの圧縮率の向上が含まれます。データと選択した圧 縮レベルによって効果は異なります • メリット • HCC表のデータベース ストレージの使⽤率を削減、および、アプリケーションのワークロード パフォーマンスを向上 23 Copyright © 2024, Oracle and/or its affiliates
24. Hybrid Columnar Compressionのパフォーマンスおよびディスク効率の向上 検証⽅法 24 Copyright © 2024, Oracle and/or its affiliates --⾮圧縮表の作成 CREATE TABLE PTEST_NOCOMP(col1 number, col2 varchar2(100)); insert into PTEST_NOCOMP select rownum,LPAD(TO_CHAR(MOD(rownum,10)),100,'A') from XMLTABLE ('1 to 80000000'); CREATE TABLE PTEST_HCC_QL COLUMN STORE COMPRESS FOR QUERY LOW AS SELECT * FROM PTEST_NOCOMP WHERE 1=0; CREATE TABLE PTEST_HCC_QH COLUMN STORE COMPRESS FOR QUERY HIGH AS SELECT * FROM PTEST_NOCOMP WHERE 1=0; CREATE TABLE PTEST_HCC_AL COLUMN STORE COMPRESS FOR ARCHIVE LOW AS SELECT * FROM PTEST_NOCOMP WHERE 1=0; CREATE TABLE PTEST_HCC_AH COLUMN STORE COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM PTEST_NOCOMP WHERE 1=0; --ロード（圧縮）--各実⾏前にキャッシュをクリア INSERT /*+ APPEND */ INTO PTEST_HCC_QL SELECT * FROM PTEST_NOCOMP; INSERT /*+ APPEND */ INTO PTEST_HCC_QH SELECT * FROM PTEST_NOCOMP; INSERT /*+ APPEND */ INTO PTEST_HCC_AL SELECT * FROM PTEST_NOCOMP; INSERT /*+ APPEND */ INTO PTEST_HCC_AH SELECT * FROM PTEST_NOCOMP; --クエリ（解凍） --各実⾏前にキャッシュをクリア select SUM(col1) from PTEST_HCC_QL; select SUM(col1) from PTEST_HCC_QH; select SUM(col1) from PTEST_HCC_AL; select SUM(col1) from PTEST_HCC_AH;
25. Hybrid Columnar Compressionのパフォーマンスおよびディスク効率の向上 簡易検証結果（※） 19c(BaseDB E5 2 OCPU DATA 256GB) 23ai(BaseDB E5 2 OCPU DATA 256GB) ⾮圧縮 サイズ(MB) 9781 9781 QUERY LOW ダイレクトインサート 00:01:44.34 00:01:08.84 検索(全件SUM) 00:00:02.46 00:00:02.61 サイズ(MB) 310 308 QUERY HIGH ダイレクトインサート 00:01:21.78 00:01:07.34 検索(全件SUM) 00:00:01.93 00:00:01.81 サイズ(MB) 29 20 ARCHIVE LOW ダイレクトインサート 00:01:22.39 00:01:05.11 検索(全件SUM) 00:00:01.92 00:00:01.93 サイズ(MB) 29 26 ARCHIVE HIGH ダイレクトインサート 00:03:57.58 00:01:19.36 検索(全件SUM) 00:00:05.20 00:00:01.90 サイズ(MB) 20 23 25 Copyright © 2024, Oracle and/or its affiliates ※簡易的な検証結果ですので、実システムでの効果とは異なる可能性はあります。実データで効果検証をして導⼊をご検討ください
26. Copyright © 2024, Oracle and/or its affiliates 新しい問合せ変換（Query Transformation） 26 • ビューやサブクエリのサブサンプション • Union All ビューに対するGroup By Pushdown • Group By Placementの拡張
27. ご参考︓問合せ変換（Query Transformation）について オプティマイザの操作の⼀つで、より優れた実⾏計画を⽣成できるように、SQLの問合せ結果は同じですが、別の記述に 書き換える操作 27 Copyright © 2024, Oracle and/or its affiliates SQLチューニング・ガイド: 4.1.2 コストベースの最適化 例 OR Expansion （OR拡張） 索引利⽤できない 索引利⽤できる ここで実施 Optimizer 他にもビューのマージ/ネストした副問合せの解除/述語のプッシュ/Group By/DISTINCTの配置の最適化 津島博⼠のパフォーマンス講座 第29回 表結合とSQL⾃動変換について
28. 概要 サブサンプション（包摂）は新しい問合せ変換⼿法です。 複数のビューまたは副問合せを1つに置換できます。 表のアクセスと結合が減少するため、パフォーマンスが向上します。 ビューやサブクエリのサブサンプション Copyright © 2024, Oracle and/or its affiliates 28 SELECT * FROM (SELECT avg(amount) av FROM sales s, products p WHERE s.prod_id = p.id AND s.region_id = 5 AND s.sale_code = 1) v1, (SELECT sum(comsn) sm FROM sales s, products p WHERE s.prod_id = p.id AND s.region_id = 5 AND s.sale_code = 2) v2; SELECT avg(CASE WHEN sale_code = 1 THEN amount ELSE NULL END) av, sum(CASE WHEN sale_code = 2 THEN comsn ELSE NULL END) sm FROM sales s, products p WHERE s.prod_id = p.id AND s.region_id = 5 AND s.sale_code IN (1,2);
29. 実⾏計画の違い ビューやサブクエリのサブサンプション Copyright © 2024, Oracle and/or its affiliates 29 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 207 (100)| | | 1 | VIEW | VW_SVS_2E85B742 | 1 | 26 | 207 (1)| 00:00:01 | | 2 | SORT AGGREGATE | | 1 | 59 | | | |* 3 | HASH JOIN | | 57 | 3363 | 207 (1)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 57 | 3135 | 202 (1)| 00:00:01 | |* 5 | INDEX RANGE SCAN | SALES_REGION_ID | 200 | | 1 (0)| 00:00:01 | | 6 | INDEX FAST FULL SCAN | PRODUCTS_PK | 1000 | 4000 | 4 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("S"."PROD_ID"="P"."ID") 4 - filter(("S"."SALE_CODE"=1 OR "S"."SALE_CODE"=2)) 5 - access("S"."REGION_ID"=5) SELECT * FROM (SELECT avg(amount) av FROM sales s, products p WHERE s.prod_id = p.id AND s.region_id = 5 AND s.sale_code = 1) v1, (SELECT sum(comsn) sm FROM sales s, products p WHERE s.prod_id = p.id AND s.region_id = 5 AND s.sale_code = 2) v2; SELECT avg(CASE WHEN sale_code = 1 THEN amount ELSE NULL END) av, sum(CASE WHEN sale_code = 2 THEN comsn ELSE NULL END) sm FROM sales s, products p WHERE s.prod_id = p.id AND s.region_id = 5 AND s.sale_code IN (1,2); ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 414 (100)| | | 1 | NESTED LOOPS | | 1 | 26 | 414 (1)| 00:00:01 | | 2 | VIEW | | 1 | 13 | 207 (1)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 37 | | | |* 4 | HASH JOIN | | 29 | 1073 | 207 (1)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 29 | 957 | 202 (1)| 00:00:01 | |* 6 | INDEX RANGE SCAN | SALES_REGION_ID | 200 | | 1 (0)| 00:00:01 | | 7 | INDEX FAST FULL SCAN | PRODUCTS_PK | 1000 | 4000 | 4 (0)| 00:00:01 | | 8 | VIEW | | 1 | 13 | 207 (1)| 00:00:01 | | 9 | SORT AGGREGATE | | 1 | 37 | | | |* 10 | HASH JOIN | | 29 | 1073 | 207 (1)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 29 | 957 | 202 (1)| 00:00:01 | |* 12 | INDEX RANGE SCAN | SALES_REGION_ID | 200 | | 1 (0)| 00:00:01 | | 13 | INDEX FAST FULL SCAN | PRODUCTS_PK | 1000 | 4000 | 4 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("S"."PROD_ID"="P"."ID") 5 - filter("S"."SALE_CODE"=1) 6 - access("S"."REGION_ID"=5) 10 - access("S"."PROD_ID"="P"."ID") 11 - filter("S"."SALE_CODE"=2) 12 - access("S"."REGION_ID"=5) ２つのスキャンと１つの結合のセットを 削減 ｚ ｚ ｚ
30. 概要 Union All ビューに対するGroup By Pushdown Copyright © 2024, Oracle and/or its affiliates 30 GROUP BYをビューにプッシュし、各ビュー・ブランチにGROUP BYを配置。 これにより、 GROUP BYによる重複値の削減効果が⾒込め、パフォーマンスが⼤幅に向上します。 ※Group By Pushdownの機能⾃体は11gから利⽤可能。23aiではUnion Allビューに適⽤可能になりました SELECT s.region_id, s.sale_code, sum(p.prod_keyv) sm FROM products p, (SELECT region_id, sale_code FROM sales_emea s1 WHERE region_id < 500 UNION ALL SELECT region_id, sale_code FROM sales s2 WHERE region_id < 480) s WHERE s.region_id = p.source_rgn GROUP BY s.region_id, s.sale_code; SELECT s.region_id, s.sale_code, sum(p.prod_keyv * s.cs) sm FROM products p, (SELECT region_id, sale_code, count(*) cs FROM sales_emea s1 WHERE region_id < 500 GROUP BY s1.region_id, s1.sale_code UNION ALL SELECT region_id, sale_code, count(*) cs FROM sales s2 WHERE region_id < 480 GROUP BY s2.region_id, s2.sale_code) s WHERE s.region_id = p.source_rgn GROUP BY s.region_id, s.sale_code;
31. 実⾏計画の違い Union All ビューに対するGroup By Pushdown Copyright © 2024, Oracle and/or its affiliates 31 SELECT s.region_id, s.sale_code, sum(p.prod_keyv) sm FROM products p, (SELECT region_id, sale_code FROM sales_emea s1 WHERE region_id < 500 UNION ALL SELECT region_id, sale_code FROM sales s2 WHERE region_id < 480) s WHERE s.region_id = p.source_rgn GROUP BY s.region_id, s.sale_code; SELECT s.region_id, s.sale_code, sum(p.prod_keyv * s.cs) sm FROM products p, (SELECT region_id, sale_code, count(*) cs FROM sales_emea s1 WHERE region_id < 500 GROUP BY s1.region_id, s1.sale_code UNION ALL SELECT region_id, sale_code, count(*) cs FROM sales s2 WHERE region_id < 480 GROUP BY s2.region_id, s2.sale_code) s WHERE s.region_id = p.source_rgn GROUP BY s.region_id, s.sale_code; ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2475 | 81675 | 46530 (2)| 00:00:02 | | 1 | HASH GROUP BY | | 2475 | 81675 | 46530 (2)| 00:00:02 | |* 2 | HASH JOIN | | 37M| 1189M| 45709 (1)| 00:00:02 | | 3 | TABLE ACCESS FULL | PRODUCTS | 10000 | 253K| 220 (0)| 00:00:01 | | 4 | VIEW | | 1961K| 13M| 45417 (1)| 00:00:02 | | 5 | UNION-ALL | | 1961K| 13M| 45417 (1)| 00:00:02 | |* 6 | TABLE ACCESS FULL| SALES_EMEA | 1000K| 6835K| 22707 (1)| 00:00:01 | |* 7 | TABLE ACCESS FULL| SALES | 961K| 6575K| 22711 (1)| 00:00:01 | ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2475 | 111K| 45675 (1)| 00:00:02 | | 1 | HASH GROUP BY | | 2475 | 111K| 45675 (1)| 00:00:02 | |* 2 | HASH JOIN | | 93558 | 4202K| 45673 (1)| 00:00:02 | | 3 | VIEW | | 4856 | 97120 | 45453 (1)| 00:00:02 | | 4 | UNION-ALL | | 4856 | 33992 | 45453 (1)| 00:00:02 | | 5 | HASH GROUP BY | | 2475 | 17325 | 22725 (1)| 00:00:01 | |* 6 | TABLE ACCESS FULL| SALES_EMEA | 1000K| 6835K| 22707 (1)| 00:00:01 | | 7 | HASH GROUP BY | | 2381 | 16667 | 22728 (1)| 00:00:01 | |* 8 | TABLE ACCESS FULL| SALES | 961K| 6575K| 22711 (1)| 00:00:01 | | 9 | TABLE ACCESS FULL | PRODUCTS | 10000 | 253K| 220 (0)| 00:00:01 | ------------------------------------------------------------------------------------- 注︓ デフォルトでのプランではなく説明⽤にグループ化機能の最適化を無効化したプランです GROUP BYを結合前に実施することで、結合の⾏数を削減 ｚ ｚ
32. 概要 Group By Placementの拡張 Copyright © 2024, Oracle and/or its affiliates 32 算術式と条件⽂を分解して、グループ化を容易に 実⾏計画の早い段階でグループ化を実⾏すると、結合に必要な⾏数が削減されます。 例︓ SUM(t1.a*t2.b)を個々の項に分解し、t2(またはt1)に対して初期グループ集約を⾏い、その後t1(またはt2)にjoin し、最終グループ集約を⾏う。 ※Group By Placementの機能⾃体は11gから利⽤可能。23aiでは算術式に適⽤可能になりました SELECT t1.region_id,SUM(t1.amount*t2.icount) FROM sales t1, sales_emea t2 WHERE t1.sale_code = t2.sale_code GROUP BY t1.region_id; SELECT t1.region_id, SUM(t1.amount*v_gbc_3.sb) FROM sales t1, (SELECT t2.sale_code, SUM(t2.icount) as sb FROM sales_emea t2 GROUP BY t2.sale_code) v_gbc_3 WHERE t1.sale_code = v_gbc_3.sale_code GROUP BY t1.region_id;
33. 実⾏計画の違い Group By Placementの拡張 Copyright © 2024, Oracle and/or its affiliates 33 SELECT t1.region_id,SUM(t1.amount*t2.icount) FROM sales t1, sales_emea t2 WHERE t1.sale_code = t2.sale_code GROUP BY t1.region_id; SELECT t1.region_id, SUM(t1.amount*v_gbc_3.sb) FROM sales t1, (SELECT t2.sale_code, SUM(t2.icount) as sb FROM sales_emea t2 GROUP BY t2.sale_code) v_gbc_3 WHERE t1.sale_code = v_gbc_3.sale_code GROUP BY t1.region_id; GROUP BYを結合前に実施することで、結合の⾏数を削減 ｚ ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 500 | 17500 | | 53114 (91)| 00:00:03 | | 1 | HASH GROUP BY | | 500 | 17500 | | 53114 (91)| 00:00:03 | |* 2 | HASH JOIN | | 1428M| 46G| 1760K| 8160 (41)| 00:00:01 | | 3 | TABLE ACCESS FULL| SALES_EMEA | 100K| 585K| | 2299 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL| SALES | 100K| 2832K| | 2299 (1)| 00:00:01 | ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 500 | 22500 | 4604 (1)| 00:00:01 | | 1 | HASH GROUP BY | | 500 | 22500 | 4604 (1)| 00:00:01 | |* 2 | HASH JOIN | | 100K| 4394K| 4601 (1)| 00:00:01 | | 3 | VIEW | V_GBC_3 | 7 | 112 | 2302 (1)| 00:00:01 | | 4 | HASH GROUP BY | | 7 | 42 | 2302 (1)| 00:00:01 | | 5 | TABLE ACCESS FULL| SALES_EMEA | 100K| 585K| 2299 (1)| 00:00:01 | | 6 | TABLE ACCESS FULL | SALES | 100K| 2832K| 2299 (1)| 00:00:01 | ------------------------------------------------------------------------------------
34. Copyright © 2024, Oracle and/or its affiliates まとめ 34 1. 統合メモリー管理 2. 適応型結果キャッシュ・オブジェクトの除外 3. ⾃動索引付けの機能拡張 4. ⾃動データ・クラスタリング 5. ⾃動ストレージ圧縮 6. Hybrid Columnar Compressionのパフォーマンスおよびディスク効率の向上 7. 新しい問合せ変換（Query Transformation） ※機能名の⽇本語表記はマニュアルに合わせてあります。そのままで伝わりにくい ものについては括弧書きで追加説明しています
35. ありがとうございました Copyright © 2024, Oracle and/or its affiliates 35