Mac De Oracle: 実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 10 / No.45 / MAT

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2024年12月
日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

最近のトラックバック

[memo]メモを取りつつ楽しめるようなスタイル (wmo6hash::blog)
ダーリンはGoogleで軽食なり。 (今日のゴハン)
�� プログラムは読む時間の方が長い - 遥か彼方の彼方から (�� プログラムは読む時間の方が長い - 遥か彼方の彼方から)
今流行のケブンリッジ関数・・・しってました！？ (あにずむ)
poken (drmarのメモ)
Photo exhibition "Natural Faces of AIDS Orphans"　写真展「エイズ孤児の素顔」@JICA地球ひろば　広尾 (メガ放談)
創る心とおもてなし―第1回 Webエンジニア料理対決に参加する (Kwappa開発室)
創る心とおもてなし～第一回webエンジニア料理対決に参加した (Mac De Oracle)
徹底比較!! PostgreSQL vs MySQLパート2 with 勝手に vs Firebird (14) (キムラデービーブログ)
Oracle de XMLDB #18 (discus)

Oracle

OTN US

Apple

その他

ブログ de ノベルティ
ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告

RSS

RSSを表示する

当サイトについて

プライバシーポリシー

■プライバシーポリシー

■個人情報の利用目的

　当ブログでは、メールでのお問い合わせ、ブログへのコメントの際、名前（ハンドルネーム）等の個人情報をご登録いただく場合がございます。これらの個人情報は質問に対する回答などでご連絡する場合や、スパムコメント抑止のために利用させていただくものであり、個人情報をご提供いただく際の目的以外では利用いたしません。

■個人情報の第三者への開示

　当サイトでは、個人情報は適切に管理し、以下に該当する場合を除いて第三者に開示することはありません。

本人のご了解がある場合
法令等への協力のため、開示が必要となる場合

個人情報の開示、訂正、追加、削除、利用停止については、ご本人からの個人データの開示、訂正、追加、削除、利用停止のご希望の場合には、ご本人であることを確認させていただいた上で速やかに対応させていただきます。

■アクセス解析ツールについて

　当サイトでは、Googleによるアクセス解析ツール「Googleアナリティクス」を利用しています。このGoogleアナリティクスはトラフィックデータの収集のためにCookieを使用しています。このトラフィックデータは匿名で収集されており、個人を特定するものではありません。この機能はCookieを無効にすることで収集を拒否することが出来ますので、お使いのブラウザの設定をご確認ください。

目的：アクセス数を把握するため
送信先：Google LCC
Googleは受け取った情報をどのように利用するか：Googleの規約に従います
送信される情報：以下４点(R), ユーザー数、セッションの統計情報、おおよその位置情報、ブラウザとデバイスの情報

■広告の配信について

当サイトはGoogleとAmazonの広告を配信しています。

Googleについて

Google及びGoogleのパートナー（第三者配信事業者）の提供する広告を設置しております。その広告配信にはCookieを使用し、当サイトやその他のサイトへの過去のアクセス情報に基づいて広告を配信します。

Google が広告 Cookie を使用することにより、当サイトや他のサイトにアクセスした際の情報に基づいて、Google やそのパートナーが適切な広告を表示しています。

お客様はGoogleアカウントの広告設定ページ（https://adssettings.google.com/u/0/authenticated）で、パーソナライズ広告を無効にできます。また aboutads.info のページにアクセスして頂き、パーソナライズ広告掲載に使用される第三者配信事業者のCookieを無効にできます。

その他、Googleの広告におけるCookieの取り扱い詳細については、Googleのポリシーと規約ページ（https://policies.google.com/technologies/ads）をご覧ください。

Amazonについて

AmazonはアソシエイトリンクのURLに含まれるパラメータを通じて利用者が直前に閲覧したアソシエイトサイトに関する情報などを収集し、これをアソシエイトパートナーに対する紹介料の算定、報告及び支払いに使用したり、アソシエイトパートナーによる複数のウェブサイトや商品戦略毎のパフォーマンスの分析に供したりしています。

アソシエイトパートナーの利用目的：閲覧の傾向や履歴の分析のため広告効果の分析のため
外部送信プログラム名：Amazonアソシエイトプログラム
送信先の会社名：アマゾンジャパン合同会社
送信する情報の内容：閲覧した内容についての情報送信先の利用目的：自社の広告宣伝を目的とした広告情報の掲載のため、ウェブサイトやアプリの監視のため

Amazonによる個人に関する情報の取得及び取扱いに関する方針につきましてはAmazon.co.jpプライバシー規約をご覧ください。

■免責事項

　当サイトからリンクやバナーなどによって他のサイトに移動された場合、移動先サイトで提供される情報、サービス等について一切の責任を負いません。当サイトのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、誤情報が入り込んだり、情報が古くなっていることもございます。当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

■プライバシーポリシーの変更について

　当サイトは、個人情報に関して適用される日本の法令を遵守するとともに、本ポリシーの内容を適宜見直しその改善に努めます。
修正された最新のプライバシーポリシーは常に本ページにて開示されます。

2022年12月10日 (土) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 10 / No.45 / MAT_VIEW REWRITE ACCESS FULL Tweet

Previously on Mac De Oracle...
Day 9は, COLLECTION ITERATOR PICKLER FETCHでした. パイプラインテーブルファンクションだけに限りませんが, 利用者定義プロシージャやファンクション内から実行されるSQL文の有無, そして, 実行計画の把握という一手間多くなるタイプです. 特にパイプラインテーブルファンクションについては, 実行計画に特殊な操作として現れるのが特徴というお話しでした.

では, Day 10 の窓を開けましょう.

今日も, 紹介済みだと勘違いして忘れていた, MAT_VIEW REWRITE ACCESS FULL です！

いつもと同じように 21c で確認します.

SCOTT@orclpdb1> select banner from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

データ作りが面倒なので, 再びサンプルスキーマのSHの力を借りましょうw　（アドベントカレンダー, 全部俺は, ある意味体力, 気力の勝負w　でもあるので, 楽できるところは楽に行きましょう :）



SH@orclpdb1> @day10
  1  CREATE MATERIALIZED VIEW LOG ON sales
  2  WITH
  3    SEQUENCE
  4    , ROWID
  5  (
  6    prod_id
  7    , quantity_sold
  8    , amount_sold
  9  )
 10* INCLUDING NEW VALUES

マテリアライズド・ビュー・ログが作成されました. 

  1  CREATE MATERIALIZED VIEW LOG ON products
  2  WITH
  3    ROWID
  4  (
  5    prod_id
  6    , prod_name
  7    , prod_category
  8    , prod_subcategory)
  9* INCLUDING NEW VALUES

マテリアライズド・ビュー・ログが作成されました. 

  1  CREATE MATERIALIZED VIEW mv_sales_4_day10
  2  REFRESH FAST ON DEMAND
  3  ENABLE QUERY REWRITE
  4  AS
  5  SELECT
  6    prod_name
  7    , SUM(quantity_sold) AS sum_quantity
  8    , SUM(amount_sold) AS sum_amount
  9  FROM
 10    sales
 11    INNER JOIN products
 12    ON
 13      sales.prod_id = products.prod_id
 14  GROUP BY
 15*   prod_name

マテリアライズド・ビューが作成されました.

マテビューの準備ができたので, 本日の主役. Query Rewriteが働いで, sales, products表ではなく, materialize viewが full scan されるように書き換えられ, MAT_VIEW REWRITE ACCESS FULLが現れています！
処理時間もいい感じですよね. 　経過: 00:00:00.02　となっています.

  1  SELECT
  2    prod_name
  3    , SUM(quantity_sold)
  4    , SUM(amount_sold)
  5  FROM
  6    sales
  7    INNER JOIN products
  8    ON
  9      sales.prod_id = products.prod_id
 10  GROUP BY
 11*   prod_name

PROD_NAME                                          SUM(QUANTITY_SOLD) SUM(AMOUNT_SOLD)
-------------------------------------------------- ------------------ ----------------
5MP Telephoto Digital Camera                                     6002        6312268.4
17" LCD w/built-in HDTV Tuner                                    6010       7189171.77
Envoy 256MB - 40GB                                               5766       5635963.08
Y Box                                                            6929        2082330.3
Mini DV Camcorder with 3.5" Swivel LCD                           6160        8314815.4
Envoy Ambassador                                                 9591       15011642.5

...略...

Smash up Boxing                                                  7844        260436.75
Martial Arts Champions                                           6711        148558.92
Comic Book Heroes                                                4572         101214.6
Fly Fishing                                                      4091         34547.82
Finding Fido                                                     6168         78881.08
Adventures with Numbers                                         12742        175563.92
Extension Cable                                                  7576         60713.47
Xtend Memory                                                    15191        366858.31

71行が選択されました. 

経過: 00:00:00.02

71行が選択されました. 

実行計画
----------------------------------------------------------
Plan hash value: 3048942819

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |    71 |  2627 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_SALES_4_DAY10 |    71 |  2627 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       4356  bytes sent via SQL*Net to client
         96  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         71  rows processed

ついでなの, Query Rewirteを無効にしてみるとどうなるでしょうか？　当然, オリジナルのsales, products表が結合されます！　
処理時間も大幅に増加して, 　経過: 00:00:00.28　ですね！　かなりの差ですよねこれ. このあたりもうまく使いたい, Oracleの便利な機能の一つではありますね.

  1  SELECT
  2    /*+
  3      NO_REWRITE
  4    */
  5    prod_name
  6    , SUM(quantity_sold)
  7    , SUM(amount_sold)
  8  FROM
  9    sales
 10    INNER JOIN products
 11    ON
 12      sales.prod_id = products.prod_id
 13  GROUP BY
 14*   prod_name

PROD_NAME                                          SUM(QUANTITY_SOLD) SUM(AMOUNT_SOLD)
-------------------------------------------------- ------------------ ----------------
5MP Telephoto Digital Camera                                     6002        6312268.4
17" LCD w/built-in HDTV Tuner                                    6010       7189171.77
Envoy 256MB - 40GB                                               5766       5635963.08
Y Box                                                            6929        2082330.3
Mini DV Camcorder with 3.5" Swivel LCD                           6160        8314815.4

...略...

Martial Arts Champions                                           6711        148558.92
Comic Book Heroes                                                4572         101214.6
Fly Fishing                                                      4091         34547.82
Finding Fido                                                     6168         78881.08
Adventures with Numbers                                         12742        175563.92
Extension Cable                                                  7576         60713.47
Xtend Memory                                                    15191        366858.31

71行が選択されました. 

経過: 00:00:00.28

71行が選択されました. 

実行計画
----------------------------------------------------------
Plan hash value: 504757596

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    71 |  4260 |   574  (11)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY          |          |    71 |  4260 |   574  (11)| 00:00:01 |       |       |
|*  2 |   HASH JOIN             |          |    72 |  4320 |   573  (11)| 00:00:01 |       |       |
|   3 |    VIEW                 | VW_GBC_5 |    72 |  2160 |   570  (11)| 00:00:01 |       |       |
|   4 |     HASH GROUP BY       |          |    72 |   864 |   570  (11)| 00:00:01 |       |       |
|   5 |      PARTITION RANGE ALL|          |   918K|    10M|   523   (3)| 00:00:01 |     1 |    28 |
|   6 |       TABLE ACCESS FULL | SALES    |   918K|    10M|   523   (3)| 00:00:01 |     1 |    28 |
|   7 |    TABLE ACCESS FULL    | PRODUCTS |    72 |  2160 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="PRODUCTS"."PROD_ID")

Note
-----
   - this is an adaptive plan

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1641  consistent gets
          0  physical reads
          0  redo size
       4356  bytes sent via SQL*Net to client
         96  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         71  rows processed

  1* DROP MATERIALIZED VIEW LOG ON sales

マテリアライズド・ビュー・ログが削除されました. 

  1* DROP MATERIALIZED VIEW LOG ON products

マテリアライズド・ビュー・ログが削除されました. 

  1* DROP MATERIALIZED VIEW mv_sales_4_day10

マテリアライズド・ビューが削除されました.

さてさて, やっと, Day 10です. まだ半分も終わってないのかと, 遠ーーーーーーーーーーくをみるなど.

そんなこと, してても, 明日も私が担当なので, 何か考えますw

参考）
Oracle Database 21c / Data Warehousing Guide - 6 Advanced Materialized Views

投稿日時 2022年12月10日 (土) 00時00分 Oracle, チューニング, SQL, Oracle Database 12c release 1, パフォーマンス, Advent Calendar, Oracle Database 12c Release 2, Oracle Database 18c, Oracle Database 19c, Oracle Database 21c | 固定リンク | 0

Mac De Oracle

MacOS X で動作する Oracle　が正式にリリースされたのだ！ 1996年にリリースされた、Personal Oracle7 for MacOS以来、封印されていたMacOS版が、（2005年）９年ぶりに正式リリースされたのでいろいろあそんでみたくなった！

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

2022年12月10日 (土) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 10 / No.45 / MAT_VIEW REWRITE ACCESS FULL Tweet

コメント

コメントを書く

Mac De Oracle

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

2022年12月10日 (土) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 10 / No.45 / MAT_VIEW REWRITE ACCESS FULL Tweet

コメント

コメントを書く

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 10 / No.45 / MAT_VIEW REWRITE ACCESS FULL Tweet