Oracle 11g インターバル・パーティションに関する検証 その5

<Oracle 11g インターバル・パーティションに関する検証 その5>
ペンネーム: ミラニスタ

どのパーティションにも格納(マップ)されない新規レコードに対して、
Oracleが自動的に適切なパーティションを追加してくれるインターバル・パー
ティションを検証しています。

▼ 前回のおさらい

インターバル・パーティション表に対して、100件のレコードを逆順にInsert
しても、正常にパーティションが追加されていることを確認しました。

また、この表の全件検索を行ったところ
1. 全件検索なので、すべてのパーティションがアクセス対象となる。
(Operation: PARTITION RANGE ALL)
PARTITION_POSITION順にすべてのパーティションが対象となる。
2. アクセス対象となっている各パーティションのセットについて、全件検索
(Operation: TABLE ACCESS FULL)が繰り返し実行される。
3. それぞれの結果がマージされ、全体の結果として返される。
となる結果が返ってくることを確認しました。

さらに、その際の実行計画を見ると

  実行計画
  ----------------------------------------------------------------
  | Id  | Operation           | Name    | Rows  || Pstart| Pstop |
  ----------------------------------------------------------------
  |   0 | SELECT STATEMENT    |         |   100 ||       |       |
  |   1 |  PARTITION RANGE ALL|         |   100 ||     1 |1048575|
  |   2 |   TABLE ACCESS FULL | INTER01 |   100 ||     1 |1048575|
  ----------------------------------------------------------------

パーティション数が10個しかないにも関わらず、PARTITION RANGE ALLオペレー
ションのPstop(PARTITION_STOP)の値が「1048575」というとんでもない値に
なっていることがわかりました。

今回はこの値の謎に迫ってみます。

▼ データ・ディクショナリ・ビューに対応する内部表を予め確認しておく

トレース・ファイルの内容やトレース・ファイルをTKPROFで整形した結果を
確認する前に、データ・ディクショナリ・ビューで確認できるパーティション
情報がどの内部表に格納されているかを押えておきましょう。
データ・ディクショナリ・ビューと元表の関係がわかれば、膨大なトレース・
ファイルの情報から関係するSQL文を抽出することが容易になります。

内部表とデータ・ディクショナリの対応は、dbms_metadata.get_ddlプロシー
ジャを使って、データ・ディクショナリ・ビューを作成するDDLを取得すれば
確認することができます。(KROWN #63106参照)

  SQL> set pages 0
  SQL> set long 2000000000
  SQL> select dbms_metadata.get_ddl('VIEW','','SYS') from dual;

パーティション表に関するデータ・ディクショナリ・ビューの説明は

===========
Oracle Database VLDBおよびパーティショニング・ガイド
11gリリース1(11.1)
E05787-01

3 パーティションの管理
  パーティション表および索引の情報の表示
===========

にまとめられています。

今回の検証では、パーティション化情報を持つ以下の2つのビューについて
DDLを取得します。

1. USER_PART_TABLES
現行のユーザーが所有するパーティション表の、オブジェクト・レベルの
パーティション化情報

2. USER_TAB_PARTITIONS
現行のユーザーが所有するパーティション表の、パーティション・レベルの
パーティション化情報

○ レベルとパーティション化情報との関係

   パーティション表-パーティション
        |                 |
        |                 +-> USER_TAB_PARTITIONS
        +-> USER_PART_TABLES

1. USER_PART_TABLES

  CREATE OR REPLACE FORCE VIEW "SYS"."USER_PART_TABLES" (
    "TABLE_NAME"
  , "PARTITIONING_TYPE"
  , "SUBPARTITIONING_TYPE"
  , "PARTITION_COUNT"
  , "DEF_SUBPARTITION_COUNT"
  , "PARTITIONING_KEY_COUNT"
  , "SUBPARTITIONING_KEY_COUNT"
  ................ 中略 ...........................
  , "INTERVAL") AS
  select
    o.name
  , decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST',
                        5, 'REFERENCE', 'UNKNOWN')
  , decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH',
                                3, 'SYSTEM', 4, 'LIST', 'UNKNOWN')
  , po.partcnt
  , mod(trunc(po.spare2/65536), 65536)
  , po.partkeycols
  , mod(trunc(po.spare2/256), 256)
  ................ 中略 ...........................
  , po.interval_str
  from
    sys.obj$     o
  , sys.partobj$ po
  , sys.ts$      ts
  , sys.tab$     t
  where o.obj#        = po.obj#
  and   po.defts#     = ts.ts# (+)
  and   t.obj#        = o.obj#
  and   o.owner#      = userenv('SCHEMAID')
  and   o.subname     IS NULL
  and   o.namespace   = 1
  and   o.remoteowner IS NULL
  and   o.linkname    IS NULL
  and   bitand(t.property, 64 + 128) = 0
  ................ 後略 ...........................

例えば、USER_PART_TABLES.PARTITION_COUNT列はsys.partobj$のpartcnt列で
あることがわかります。この列は「この表のパーティション数」を示します。
USER_PART_TABLESビューの他のカラムの意味については、リファレンス・マ
ニュアルを参照してください。

2. USER_TAB_PARTITIONS

  CREATE OR REPLACE FORCE VIEW "SYS"."USER_TAB_PARTITIONS" (
    "TABLE_NAME"
  , "COMPOSITE"
  , "PARTITION_NAME"
  , "SUBPARTITION_COUNT"
  , "HIGH_VALUE"
  , "HIGH_VALUE_LENGTH"
  , "PARTITION_POSITION"
  ................ 中略 ...........................
  , "USER_STATS") AS
  select
    o.name
  , 'NO'
  , o.subname
  , 0
  , tp.hiboundval
  , tp.hiboundlen
  , tp.part#
  ................ 中略 ...........................
  , decode(bitand(tp.flags, 8), 0, 'NO', 'YES')
  from
    obj$      o
  , tabpartv$ tp
  , ts$       ts
  , sys.seg$  s
  , sys.tab$  t
  where o.obj#        = tp.obj#
  and   ts.ts#        = tp.ts#
  and   tp.file#      = s.file#
  and   tp.block#     = s.block#
  and   tp.ts#        = s.ts#
  and   tp.bo#        = t.obj#
  and   bitand(t.trigflag, 1073741824) != 1073741824
  and   o.owner#      = userenv('SCHEMAID')
  and   o.namespace   = 1
  and   o.remoteowner IS NULL
  and   o.linkname    IS NULL
  ................ 後略 ...........................

USER_TAB_PARTITIONS.TABLE_NAME、PARTITION_NAMEの各カラムは、別の内部
表であるsys.obj$表のname、subname列が実体です。
オブジェクト番号(obj#)を使ってtabpartv$と結合しています。

▼ SQLトレースの結果を見てみる

前回の検証で、パーティション追加操作によってどんな内部SQLが発行され
ているかを確認するために、SQLトレース(Levle 4:バインド変数の情報付き)
を取得しました。
それでは、TKPROFで整形した結果から関係がありそうなSQLを探していきま
しょう。

(1) SQL ID : なし

  CREATE TABLE inter01(c1 number)
  PARTITION BY RANGE(c1)
  INTERVAL(10)
  (PARTITION VALUES LESS THAN(11))
  
  call     count       cpu    elapsed      query    current        rows
  ------- ------  -------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.00          0          0           0
  Execute      1      0.00       0.01          1         13           0
  Fetch        0      0.00       0.00          0          0           0
  ------- ------  -------- ---------- ---------- ----------  ----------
  total        2      0.00       0.01          1         13           0

これは、インターバル・パーティション表作成のDDLですね。
この後に出現する「part」という文字列を含むSQL文を抽出します。

(2) SQL ID : d1updcq3y3087

  insert into partobj$ (obj#, parttype, partcnt, partkeycols, flags, defts#, 
    defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize, 
    defminexts, defmaxexts, defextpct, deflists, defgroups, deflogging, spare1, 
    spare2, parameters,defmaxsize, spare3, interval_str, interval_bival) 
  values
   (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, 
    :18, :19, :20 * 4294967296 + :21 * 65536 + :22 * 256 + :23, :24, :25, :26, 
    :27, :28)
  
  call     count       cpu    elapsed      query    current        rows
  ------- ------  -------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.00          0          0           0
  Execute      1      0.00       0.00          1          3           1
  Fetch        0      0.00       0.00          0          0           0
  ------- ------  -------- ---------- ---------- ----------  ----------
  total        2      0.00       0.00          1          3           1

「partobj$」という内部表に対するInsert文が確認できました。このInsert
文は1回だけ実行されています。つまり、このSQLはパーティション表作成時に
実行されるようです
さらに、トレース・ファイルを直接確認すると、以下のような形でバインド
変数値が表示されているのがわかります。(Bind# とvalue=xxxxx に注目)

  ................ 前略 .....................................
  PARSING IN CURSOR #3 len=463 dep=1 uid=0 oct=2 lid=0 tim=20300300294
  hv=2279702791 ad='2e6dc0a8' sqlid='d1updcq3y3087'
  insert into partobj$ (obj#, parttype, partcnt, partkeycols, ...
  ................ 中略 .....................................
   Bind#0
    oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
    oacflg=00 fl2=0001 frm=00 csi=00 siz=552 off=0
    kxsbbbfp=07b8dbc4  bln=22  avl=04  flg=05
    value=71216
   Bind#1
    oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
    oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
    kxsbbbfp=07b8dbdc  bln=22  avl=02  flg=01
    value=1
  ................ 後略 .....................................

このままでは見にくいので、各カラムと値の関係をまとめてみます。

○ バインド変数値まとめ(一部のみ)

  obj#         = 71216
  parttype     = 1
  partcnt      = 1048575
  partkeycols  = 1
  interval_str = "10"

おーっと、いきなり謎の「1048575」が出てきました!
partcnt列はUSER_PART_TABLES.PARTITION_COUNT列なので「この表のパーティ
ション数」でしたね。
この段階では、パーティション数=0 のはずです。一体どうなっているんで
しょうか???
とりあえず、次のSQLを探しましょう。

(3) SQL ID : 9hp6m1g7j275b

  insert into tabpart$ (obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, ts#,
     file#, block#, pctfree$, pctused$, initrans, maxtrans, flags, analyzetime, 
    samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, bhiboundval) 
  values
   (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, 
    :18, :19, :20, :21, :22, EMPTY_BLOB()) returning bhiboundval into :23
  
  call     count       cpu    elapsed      query    current        rows
  ------- ------  -------- ---------- ---------- ----------  ----------
  Parse       10      0.00       0.00          0          0           0
  Execute     10      0.03       0.02         20         72          10
  Fetch        0      0.00       0.00          0          0           0
  ------- ------  -------- ---------- ---------- ----------  ----------
  total       20      0.03       0.02         20         72          10

Parse、Executeがそれぞれ10回ずつであることから、「tabpart$」に対する
このInsert文は10個のパーティションを追加する際に発行されたものと考えら
れます。

○ バインド変数値まとめ(一部のみ)*No.は実行回次

  No. obj#  bo#   part#      hiboundlen hiboundval
  --- ----- ----- ---------- ---------- ----------
    1 71217 71216         10          2 "11"
    2 71218 71216 2147483656          3 "101"
    3 71219 71216 2147483655          2 "91"
    4 71220 71216 2147483654          2 "81"
    5 71221 71216 2147483653          2 "71"
    6 71222 71216 2147483652          2 "61"
    7 71223 71216 2147483651          2 "51"
    8 71224 71216 2147483650          2 "41"
    9 71225 71216 2147483649          2 "31"
   10 71226 71216 2147483648          2 "21"

bo#列は、恐らく各パーティションが属するパーティション表のオブジェク
トIDのことでしょう。(bo#:Base Object Id?でしょうか?)

part#列は、USER_TAB_PARTITIONS.PARTITION_POSITION列に該当し、意味す
る所は「表内のパーティションの位置」すなわち「このパーティションは表内
で何番目か?」という値です。
一番最初が「10」、2番目が「2147483656」、3番目が「2147483655」。。。
訳が分かりません???
謎を解き明かすどころか、益々分からなくなってきました。

hiboundlen列はHIGH_VALUE値の長さで、hiboundval列はHIGH_VALUE値そのも
のです。
part#列とhiboundval列の大小をよく見比べてみると、大小関係は一致して
います。

分からないことは置いておいて、次のSQLを探しましょう。

(4) SQL ID : 0rbcqb9pa39d1

  update partobj$ set partcnt = :1 
  where
   obj# = :2
  
  call     count       cpu    elapsed      query    current        rows
  ------- ------  -------- ---------- ---------- ----------  ----------
  Parse        9      0.00       0.00          0          0           0
  Execute      9      0.00       0.00          9         18           9
  Fetch        0      0.00       0.00          0          0           0
  ------- ------  -------- ---------- ---------- ----------  ----------
  total       18      0.00       0.00          9         18           9

partobj$表に対するupdate文が9回(パーティション追加の回数)発行されて
います。

○ バインド変数値まとめ *No.は実行回次

  No. partcnt obj# 
  --- ------- -----
    1 1048575 71216
    2 1048575 71216
    3 1048575 71216
    4 1048575 71216
    5 1048575 71216
    6 1048575 71216
    7 1048575 71216
    8 1048575 71216
    9 1048575 71216

何と!9回ともpartcnt列は謎の「1048575」に更新されています。というこ
とは値自身は最初から全く変わっていません。
これは一体どういうことでしょうか?

この辺で、今までに判明した疑問点をまとめておきます。

1. partobj$.partcntは、パーティション表作成時およびパーティション
追加時に追加・更新されるが、その値は常に「1048575」である。
2. tabpart$.part#は、「表内のパーティションの位置」のはずだが、
「2147483656」のように意味不明な値が格納されている。
3. ただし、tabpart$.part#の値の大小はhiboundvalの大小に対応しているよ
うだ。

SQLトレースの中には、この他にも沢山の内部SQLが確認できますが、この検
証では、上記 (2)~(4) の3つのSQLに着目していきます。

今回は、Pstop(PARTITION_STOP)=1048575 の謎に迫っているつもりでした
が、分からないことが逆に増えてしまいました。

長くなってしまったので今回はこれまでです。謎解きは次回までのお楽しみ
とします。

連休中に地区の運動会に出ました!体が痛い!!
恵比寿より