Oracle 11g インターバル・パーティションに関する検証 その５
＜Oracle 11g インターバル・パーティションに関する検証 その５＞
ペンネーム： ミラニスタ
どのパーティションにも格納（マップ）されない新規レコードに対して、
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 の謎に迫っているつもりでした
が、分からないことが逆に増えてしまいました。
長くなってしまったので今回はこれまでです。謎解きは次回までのお楽しみ
とします。
連休中に地区の運動会に出ました！体が痛い！！
恵比寿より