ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > [20121025]1条记录会有多少row pieces.txt

[20121025]1条记录会有多少row pieces.txt

原创 Linux操作系统 作者：lfree 时间：2012-10-25 15:46:30 0 删除 编辑 
[20121025]1条记录会有多少row pieces.txt

jonathan lewis出了一道题目Quiz,参考这个链接:

http://jonathanlewis.wordpress.com/2012/03/30/quiz-night-17/

create table t1 (
        col000  number(1),
        col001  number(1),
        col002  number(1),
...
        col997  number(1),
        col998  number(1),
        col999  number(1),
        constraint t1_pk primary key (col000)
)
;

How many row pieces might that row consist of ?

自己做一些测试:
1.环境如下:

SQL> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

2.建表方法:
spool a.sql
select 'create table t1 (' from dual 
union all
select 'col'||lpad(rownum-1,3,'0')||' number(1),' from dual connect by level<=1000
union all 
select 'constraint t1_pk primary key (col000));' from dual ;

spool b.sql
select 'INSERT into t1 VALUES (' from dual
union all
select '1,' from dual connect by level<=999
union all
select ' 1);' from dual ;

SQL> select rowid from t1;

ROWID
------------------
AAAcRpAAEAAAAIPAAD

SQL> @ lookup_rowid AAAcRpAAEAAAAIPAAD

    OBJECT       FILE      BLOCK        ROW
---------- ---------- ---------- ----------
    115817          4        527          3

SQL> alter system checkpoint;
System altered.

BBED> set dba 4,527
        DBA             0x0100020f (16777743 4,527)

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
 Block: 527                                   Dba:0x0100020f
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
.....
 sb2 kdbr[4]                                @166
 ub1 freespace[4984]                        @174
 ub1 rowdata[3030]                          @5158
 ub4 tailchk                                @8188

--我仅仅插入一条记录,可以发现sb2 kdbr[4] @166,有4个目录.

BBED> p *kdbr[0]
rowdata[2262]
-------------
ub1 rowdata[2262]                           @7420     0x04

BBED> x /rn
rowdata[2262]                               @7420
-------------
flag@7420: 0x04 (KDRHFL)
lock@7421: 0x01
cols@7422:  255

col    0[2] @7423: 1
col    1[2] @7426: 1
........
col  253[2] @8182: 1
col  254[2] @8185: 1

--一个行目录有255个字段.

2.重新测试(如果插入几个字段,在修改其他呢?):
SQL> drop table t1 purge;
Table dropped.
SQL> @a.sql
Table created.

SQL> insert into t1(col000) values(1);
SQL> commit;

SQL> select rowid from t1;
ROWID
------------------
AAAcS5AAEAAAAIPAAA

SQL> @ lookup_rowid AAAcS5AAEAAAAIPAAA

    OBJECT       FILE      BLOCK        ROW
---------- ---------- ---------- ----------
    115897          4        527          0

现在执行如下:
begin
for i in 255..999 loop
 execute immediate 'update t1 set col'||i||'=0';
 end loop;
commit;
end;
/
SQL> alter system checkpoint;
System altered.

BBED> set dba 4,527
        DBA             0x0100020f (16777743 4,527)

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
 Block: 527                                   Dba:0x0100020f
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
....
 sb2 kdbr[1]                                @118
 ub1 freespace[8050]                        @120
 ub1 rowdata[18]                            @8170
 ub4 tailchk                                @8188

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8170     0x28

BBED> x /rn
rowdata[0]                                  @8170
----------
flag@8170: 0x28 (KDRHFF, KDRHFH)
lock@8171: 0x02
cols@8172:    1
nrid@8173:0x0100020b.0

col    0[2] @8179: 1

--nrid表示其他信息在0x0100020b.0,在dba=0x0100020b,row=0的位置.

BBED>  set dba 0x0100020b
        DBA             0x0100020b (16777739 4,523)

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
 Block: 523                                   Dba:0x0100020b
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
...

 sb2 kdbr[1]                                @142
 ub1 freespace[7785]                        @144
 ub1 rowdata[259]                           @7929
 ub4 tailchk                                @8188

--仅仅一个行目录.

BBED> x /rn
rowdata[0]                                  @7929
----------
flag@7929: 0x00 (NONE)
lock@7930: 0x01
cols@7931:    1
nrid@7932:0x0100020c.0

col    0[0] @7938: *NULL*

--注意仅仅一个字段,内容为空.也就是表的col001字段.

SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.

SQL> column segment_name format a20
SQL> select segment_name,segment_subtype,bytes,blocks,extents,initial_extent,next_extent from dba_segments where wner=user and segment_name='T1';

SEGMENT_NAME         SEGMENT_SU      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT
-------------------- ---------- ---------- ---------- ---------- -------------- -----------
T1                   ASSM          6291456        768         21          65536     1048576

--可以发现占用768块,extents=21.
SQL> select segment_name,segment_type,extent_id,file_id,block_id,bytes,blocks,relative_fno from dba_extents where wner=user and segment_name='T1';

SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
-------------------- ------------------ ---------- ---------- ---------- ---------- ---------- ------------
T1                   TABLE                       0          4        520      65536          8            4
T1                   TABLE                       1          4        552      65536          8            4
T1                   TABLE                       2          4        568      65536          8            4
T1                   TABLE                       3          4        584      65536          8            4
T1                   TABLE                       4          4        608      65536          8            4
T1                   TABLE                       5          4        624      65536          8            4
T1                   TABLE                       6          4        632      65536          8            4
T1                   TABLE                       7          4       1792      65536          8            4
T1                   TABLE                       8          4       1800      65536          8            4
T1                   TABLE                       9          4       1808      65536          8            4
T1                   TABLE                      10          4       1816      65536          8            4
T1                   TABLE                      11          4       1824      65536          8            4
T1                   TABLE                      12          4       1832      65536          8            4
T1                   TABLE                      13          4       1840      65536          8            4
T1                   TABLE                      14          4       1848      65536          8            4
T1                   TABLE                      15          4       1856      65536          8            4
T1                   TABLE                      16          4        640    1048576        128            4
T1                   TABLE                      17          4        768    1048576        128            4
T1                   TABLE                      18          4        896    1048576        128            4
T1                   TABLE                      19          4       1024    1048576        128            4
T1                   TABLE                      20          4       1152    1048576        128            4

21 rows selected.

--仅仅1条记录占用这么多块.
SQL> set autotrace traceonly
--注意我使用hint,通过索引访问
SQL> select /*+ index(t1) */ col999 from t1 where col000=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2347959165
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     5 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COL000"=1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        747  consistent gets
          0  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--访问col999,consistent gets高达747.

SQL> select /*+ index(t1) */ col001 from t1 where col000=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2347959165

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL000"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+ index(t1) */ col002 from t1 where col000=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2347959165

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL000"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--col002需要4个consistent gets,col001需要3个consistent gets.说明存在行链接情况.

alter system flush buffer_cache; --刷新buffer cache.
alter session set events '10046 trace name context forever, level 12';
select /*+ index(t1) */ col255 from t1 where col000=1;
alter session set events '10046 trace name context off';

$ grep 'db file sequential read' /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_7578.trc| grep obj#=115897| tail -10
WAIT #2: nam='db file sequential read' ela= 25 file#=4 block#=859 blocks=1 obj#=115897 tim=1351136716474142
WAIT #2: nam='db file sequential read' ela= 20 file#=4 block#=863 blocks=1 obj#=115897 tim=1351136716474222
WAIT #2: nam='db file sequential read' ela= 22 file#=4 block#=867 blocks=1 obj#=115897 tim=1351136716474344
WAIT #2: nam='db file sequential read' ela= 19 file#=4 block#=871 blocks=1 obj#=115897 tim=1351136716474422
WAIT #2: nam='db file sequential read' ela= 19 file#=4 block#=875 blocks=1 obj#=115897 tim=1351136716474503
WAIT #2: nam='db file sequential read' ela= 19 file#=4 block#=879 blocks=1 obj#=115897 tim=1351136716474567
WAIT #2: nam='db file sequential read' ela= 16 file#=4 block#=883 blocks=1 obj#=115897 tim=1351136716474625
WAIT #2: nam='db file sequential read' ela= 16 file#=4 block#=887 blocks=1 obj#=115897 tim=1351136716474681
WAIT #2: nam='db file sequential read' ela= 15 file#=4 block#=891 blocks=1 obj#=115897 tim=1351136716474736
WAIT #2: nam='db file sequential read' ela= 15 file#=4 block#=895 blocks=1 obj#=115897 tim=1351136716474795

$ grep 'db file sequential read' /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_7578.trc| grep obj#=115897| wc
    256    3328   27716

--读取了256次表T1.另外可以看到assm管理模式插入到后面时数据块的分配情况,相隔4块.(859->863->867...).

BBED> set dba 4,895
        DBA             0x0100037f (16778111 4,895)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @7675     0x00

BBED> x /rnnnn
rowdata[0]                                  @7675
----------
flag@7675: 0x00 (NONE)
lock@7676: 0x01
cols@7677:    1
nrid@7678:0x01000340.0

col    0[1] @7684: 0
--正好对应col255的值.

3.一点疑问:
前面我们执行select /*+ index(t1) */ col999 from t1 where col000=1;仅仅747逻辑读,按照前面的介绍,都是1块保存1个字段,按照道理
逻辑读应该在1000以上.在做如下测试看看.

alter system flush buffer_cache; --刷新buffer cache.
alter session set events '10046 trace name context forever, level 12';
select /*+ index(t1) */ col255 from t1 where col000=1;
alter session set events '10046 trace name context off';

$ grep 'db file sequential read' /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_17897.trc| grep obj#=115897|wc
    746    9698   80910
--确实读取746次.这样讲有些块应该保存几个字段.

--取出块
$ grep 'db file sequential read' test_ora_17897.trc| grep obj#=115897|cut -d"=" -f5 | cut -d' ' -f1 > /tmp/aa1.txt

--使用vi编辑aa1.txt,^M相当于回车,使用ctrl-v+ctrl+M输入.
:%s/^/set dba 4,/
:%s+$+^Mp *kdbr[0]^Mx /rnnnnn+
开头加入
set count 8192
set width 210
结尾加入
quit

参数文件里面如下:
$ cat bbedreadonly.par
blocksize=8192
listfile=/home/oracle11g/bbed/filelist.txt
mode=browse
PASSWORD=blockedit
spool=yes

$ /u01/app/oracle11g/product/11.2.0/db_1/bin/bbed parfile=bbedreadonly.par cmdfile=/tmp/aa1.txt

--检查log.bbd文件.
BBED> set dba 4,1171
    DBA             0x01000493 (16778387 4,1171)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @7675     0x04

BBED> x /rnnnnn
rowdata[0]                                  @7675
----------
flag@7675: 0x04 (KDRHFL)
lock@7676: 0x01
cols@7677:  255

col    0[1] @7678: 0
col    1[1] @7680: 0
col    2[1] @7682: 0
...

col  244[1] @8166: 0
col  245[1] @8168: 0
col  246[1] @8170: 0
col  247[1] @8172: 0
col  248[1] @8174: 0
col  249[1] @8176: 0
col  250[1] @8178: 0
col  251[1] @8180: 0
col  252[1] @8182: 0
col  253[1] @8184: 0
col  254[1] @8186: 0

--可以发现读取的最后1块,dba=4,1171,保存最后255个字段. 999-255+1=745,这么讲col745~colcol999在一个块中(dba=4,1171)

SQL> set autotrace traceonly
SQL> select /*+ index(t1) */ col745 from t1 where col000=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2347959165

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     5 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL000"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        747  consistent gets
          0  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--逻辑读与执行select /*+ index(t1) */ col999 from t1 where col000=1;一样.

总结:
1.建立表字段太多不好,如果太多超过255,可能的话可以根据业务需要拆分多个表.
2.如果一个表的字段很多,操作模式先insert一些字段,然后update后面字段多次,并且是分许多次的情况下,可能出出现这种情况.
我管的系统没有这么多的字段.按照链接的介绍,这种情况出现在像Siebel应用很常见.


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-747213/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20121023]改变show parameter的显示宽度.txt
下一篇： [20121026]11g下访问v$sql_shared_memory.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7969425

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593