(5.6)
調べた限りない模様
(12cR1)
https://www.drk7.jp/MT/archives/001578.html
-- 1.準備
drop table tab1 purge;
create table tab1(
col1 int
,col2 varchar2(10)
)
;
create index ind1 on tab1(col1,col2);
declare
begin
for i in 1..100 loop
for k in 1..100 loop
insert into tab1 values(i,'DATA'||to_char(k) );
end loop;
end loop;
commit;
end;
/
select * from tab1 order by col1,col2;
-- 2.インデックスのオブジェクトID確認
select object_id from dba_objects where object_name = 'IND1';
-- 3.TreeDump取得
alter session set tracefile_identifier = 'treedump';
--alter session set events 'immediate trace name treedump level [object_id]';
alter session set events 'immediate trace name treedump level 31541';
-- 4.TreeDump確認
cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
view orcl_ora_18772_treedump.trc
(TreeDump出力抜粋)
----- begin tree dump
branch: 0x10176ab 16873131 (0: nrow: 50, level: 1)
leaf: 0x10176af 16873135 (-1: row:189.189 avs:4042)
leaf: 0x10176ac 16873132 (0: row:189.189 avs:4042)
-- 5.ブランチノードとリーフノードのFILE_IDとBLOCK_ID確認
--select
-- dbms_utility.data_block_address_file([ブランチノードアドレス]) "file_id(branch)"
-- ,dbms_utility.data_block_address_block([ブランチノードアドレス]) "block_id(branch)"
-- ,dbms_utility.data_block_address_file([リーフノードアドレス]) "file_id(leaf)"
-- ,dbms_utility.data_block_address_block([リーフノードアドレス]) "block_id(leaf)"
--from dual;
select
dbms_utility.data_block_address_file(16873131) "file_id(branch)"
,dbms_utility.data_block_address_block(16873131) "block_id(branch)"
,dbms_utility.data_block_address_file(16873135) "file_id(leaf)"
,dbms_utility.data_block_address_block(16873135) "block_id(leaf)"
from dual;
file_id(branch) block_id(branch) file_id(leaf) block_id(leaf)
--------------- ---------------- ------------- --------------
4 95915 4 95919
-- 6.ブランチノードのデータダンプ取得
alter session set tracefile_identifier = 'branchdatadump';
--alter system dump datafile [file_id] block [block_id];
alter system dump datafile 4 block 95915;
view orcl_ora_18772_branchdatadump.trc
(データダンプ取得抜粋)
Branch block dump
=================
header address 139686638344268=0x7f0b5475584c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 49
kdxcofbo 126=0x7e
kdxcofeo 7370=0x1cca
kdxcoavs 7244
kdxbrlmc 16873135=0x10176af
kdxbrsno 47
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8042] dba: 16873132=0x10176ac
col 0; len 2; (2): c1 03
col 1; len 5; (5): 44 41 54 41 39
col 2; TERM
row#1[8028] dba: 16873133=0x10176ad
col 0; len 2; (2): c1 05
col 1; len 5; (5): 44 41 54 41 38
col 2; TERM
-- 7.リーフノードのデータをダンプ取得
alter session set tracefile_identifier = 'leafdatadump';
--alter system dump datafile [file_id] block [block_id];
alter system dump datafile 4 block 95919;
view orcl_ora_18772_leafdatadump.trc
(データダンプ取得抜粋)
Leaf block dump
===============
header address 139686638344292=0x7f0b54755864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x87: opcode=7: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 189
kdxcofbo 414=0x19e
kdxcofeo 4456=0x1168
kdxcoavs 4042
kdxlespl 0
kdxlende 0
kdxlenxt 16873132=0x10176ac
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4456] flag: ----S--, lock: 2, len=18
col 0; len 2; (2): c1 02
col 1; len 5; (5): 44 41 54 41 31
col 2; len 6; (6): 01 01 76 a5 00 00
row#1[4474] flag: ----S--, lock: 2, len=19
col 0; len 2; (2): c1 02
col 1; len 6; (6): 44 41 54 41 31 30
col 2; len 6; (6): 01 01 76 a5 00 09
(11)
【1】pageinsepct
http://pgsqldeepdive.blogspot.com/2012/12/postgresqlhot.html
create extension pageinspect;
drop table tab1;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);
insert into tab1 select a from generate_series(1,1000) a;
analyze tab1;
-- B-treeインデックスのメタページに関する情報
select * from bt_metap('tab1pk');
-- B-treeインデックスの個別のページ要約情報
select * from bt_page_stats('tab1pk',1);
select * from bt_page_stats('tab1pk',2);
select * from bt_page_stats('tab1pk',3);
select * from bt_page_stats('tab1pk',4);
-- B-treeインデックスページ上の全項目についての詳細情報
select * from bt_page_items('tab1pk',1);
select * from bt_page_items('tab1pk',2);
select * from bt_page_items('tab1pk',3);
select * from bt_page_items('tab1pk',4);
【2】pg_filedump
http://pgsqldeepdive.blogspot.com/2012/12/pgfiledump.html
https://qiita.com/mkyz08/items/5a1c9d4bd5b8ba82f345
-- 1.Postgresソースコンパイル
yum install gcc zlib-devel readline-devel
cd /usr/local/src
wget https://ftp.postgresql.org/pub/source/v11.7/postgresql-11.7.tar.gz
tar xvfz postgresql-11.7.tar.gz
chown -R postgres:postgres postgresql-11.7
su postgres
cd postgresql-11.7
./configure
make
sudo make install
-- 2.pg_filedumpインストール
su - postgres
tar xvzf pg_filedump-REL_11_0-02b1aaa.tar.gz
cd pg_filedump-REL_11_0-02b1aaa
export PATH=$PATH:/usr/local/pgsql/bin
make
sudo bash -c 'PATH=$PATH:/usr/local/pgsql/bin make install'
--3. 動作確認
drop table tab1;
create table tab1(col1 int);
alter table tab1 add constraint tab1pk primary key(col1);
insert into tab1 select a from generate_series(1,1000) a;
analyze tab1;
sudo systemctl restart postgresql-11
※なぜか一度サービス再起動しないとpg_filedump実行時にエラーとなる
/usr/pgsql-11/bin/oid2name
/usr/pgsql-11/bin/oid2name -d test -i
/usr/local/pgsql/bin/pg_filedump -a /var/lib/pgsql/11/data/base/16384/16503
/usr/local/pgsql/bin/pg_filedump -R 1 1 /var/lib/pgsql/11/data/base/16384/16503
for i in `seq 1 4` ;do
/usr/local/pgsql/bin/pg_filedump -R ${i} ${i} /var/lib/pgsql/11/data/base/16384/16503 | grep -E "ROOT|LEAF|Blocks:"
done
(2014)
http://memorandom-nishi.hatenablog.jp/entry/2017/02/26/174358
https://sqlity.net/en/2204/dbcc-ind/
drop table tab1;
create table tab1(col1 int not null);
alter table tab1 add constraint tab1pk primary key CLUSTERED (col1);
drop table tab2;
create table tab2(col1 int not null);
alter table tab2 add constraint tab2pk primary key NONCLUSTERED (col1);
declare @i int;
set @i= 1;
while (@i <= 10000)
begin
insert into tab1 values (@i);
insert into tab2 values (@i);
set @i = @i + 1;
end
go
select *
from sys.dm_db_database_page_allocations( db_id('test'),
object_id('dbo.tab1'),
null,
null,
'detailed'
) as [dpa]
inner join sys.indexes as [i]
on [dpa].[object_id] = [i].[object_id]
and [dpa].[index_id] = [i].[index_id]
where [dpa].[page_level] is not null
order by [dpa].[index_id],
[dpa].[page_level] desc,
[dpa].[allocated_page_page_id]
;
select *
from sys.dm_db_database_page_allocations( db_id('test'),
object_id('dbo.tab2'),
null,
null,
'detailed'
) as [dpa]
inner join sys.indexes as [i]
on [dpa].[object_id] = [i].[object_id]
and [dpa].[index_id] = [i].[index_id]
where [dpa].[page_level] is not null
order by [dpa].[index_id],
[dpa].[page_level] desc,
[dpa].[allocated_page_page_id]
;
dbcc ind('test','dbo.tab1',1 )
dbcc ind('test','dbo.tab2',2 )
