ITPub博客

首页 > 数据库 > Oracle > oracle优化--表优化(char VS varchar2)

oracle优化--表优化(char VS varchar2)

原创 Oracle 作者:wysyl1990 时间:2018-08-10 09:30:26 0 删除 编辑

实验结论:


1、char和varchar2占用的磁盘空间相同;

2、char与varchar2皆会产生行迁移;

3、char与varchar2产生的io次数相同,但涉及的size大小不同(char比varchar2大一倍)



实验详情:


一、创建实验表,并各新增10000条数据


create table scott.t_char

    (

    id number,

    name char(2000),

    name1 char(2000),

    name2 char(2000),

    name3 char(2000),

    name4 char(2000) 

    ); 



create table scott.t_varchar

    (

    id number,

    name varchar2(2000),

    name1 varchar2(2000),

    name2 varchar2(2000),

    name3 varchar2(2000),

    name4 varchar2(2000) 

    ); 



SQL> begin 

    for i in 1 .. 10000 loop

    insert into scott.t_char(id) values (i);

    end loop;

    end;

    /  2    3    4    5    6  


PL/SQL procedure successfully completed.


SQL> commit;


Commit complete.


SQL> begin 

    for i in 1 .. 10000 loop

    insert into scott.t_varchar(id) values (i);

    end loop;

    end;

    /  2    3    4    5    6  


PL/SQL procedure successfully completed.


SQL> commit;


Commit complete.


二、使用dump查看两张表的块信息(此时皆没出现行迁移)


SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.t_char where id=1;


B     F

---------- ----------

       215     4


SQL> alter system dump datafile 4 block 215;


System altered.


block_row_dump:

tab 0, row 0, @0x1f92

tl: 6 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 2]  c1 02

tab 0, row 1, @0x1f8c

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1

col  0: [ 2]  c1 03

tab 0, row 2, @0x1f86

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1

col  0: [ 2]  c1 04

tab 0, row 3, @0x1f80

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1

col  0: [ 2]  c1 05

tab 0, row 4, @0x1f7a

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1

col  0: [ 2]  c1 06

tab 0, row 5, @0x1f74

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1


SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.t_varchar where id=1;


 B     F

---------- ----------

       223     4


SQL> alter system dump datafile 4 block 223;


System altered.


block_row_dump:

tab 0, row 0, @0x1f92

tl: 6 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 2]  c1 02

tab 0, row 1, @0x1f8c

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1

col  0: [ 2]  c1 03

tab 0, row 2, @0x1f86

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1

col  0: [ 2]  c1 04

tab 0, row 3, @0x1f80

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1

col  0: [ 2]  c1 05

tab 0, row 4, @0x1f7a

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1

col  0: [ 2]  c1 06

tab 0, row 5, @0x1f74

tl: 6 fb: --H-FL-- lb: 0x2  cc: 1


三、查看执行计划(使用char的表所占字节比varchar2表大接近一倍)


SQL> select * from scott.t_char where id=1;



Execution Plan

----------------------------------------------------------

Plan hash value: 3241939766


----------------------------------------------------------------------------

| Id  | Operation   | Name   | Rows  | Bytes | Cost (%CPU)| Time    |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |    | 1 | 10023 | 7   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T_CHAR | 1 | 10023 | 7   (0)| 00:00:01 |

----------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   1 - filter("ID"=1)


Note

-----

   - dynamic sampling used for this statement (level=2)



Statistics

----------------------------------------------------------

  9  recursive calls

  0  db block gets

58  consistent gets

  0  physical reads

  0  redo size

859  bytes sent via SQL*Net to client

524  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 * from scott.t_varchar where id=1;



Execution Plan

----------------------------------------------------------

Plan hash value: 4100862799


-------------------------------------------------------------------------------

| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       |     1 |  5023 |     7 (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T_VARCHAR |     1 |  5023 |     7 (0)| 00:00:01 |

-------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   1 - filter("ID"=1)


Note

-----

   - dynamic sampling used for this statement (level=2)



Statistics

----------------------------------------------------------

10  recursive calls

  0  db block gets

58  consistent gets

  0  physical reads

  0  redo size

859  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  1  rows processed


四、修改两张表ID为1的数据,使用dump观察可以发现两张表皆出现行迁移。


SQL> update scott.t_char set name1=dbms_random.string('u', 2000) where id=1;


1 row updated.


SQL> update scott.t_varchar set name1=dbms_random.string('u', 2000) where id=1;


1 row updated.


SQL> commit;


Commit complete.


SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.t_char where id=1;


 B     F

---------- ----------

       215     4


SQL> alter system dump datafile 4 block 215;


System altered.


block_row_dump:

tab 0, row 0, @0xdec

tl: 9 fb: --H----- lb: 0x1  cc: 0

nrid:  0x010000e1.64


tl: 2016 fb: ----FL-- lb: 0x2  cc: 3

hrid: 0x010000d7.0

col  0: [ 2]  c1 02

col  1: *NULL*

col  2: [2000]

 59 4b 4f 52 55 4f 50 4f 59 4e 46 45 42 4a 50 42 52 42 57 4e 49 42 57 59 53

 55 4a 4d 48 46 45 50 57 58 43 44 58 54 47 47 52 42 55 45 45 48 56 45 4b 4e


SQL> select dbms_rowid.rowid_block_number(rowid) b,dbms_rowid.rowid_relative_fno(rowid) f from scott.t_varchar where id=1;


 B     F

---------- ----------

       223     4


SQL> alter system dump datafile 4 block 223;


System altered.


block_row_dump:

tab 0, row 0, @0xdec

tl: 9 fb: --H----- lb: 0x1  cc: 0

nrid:  0x010000e9.64


tl: 2016 fb: ----FL-- lb: 0x2  cc: 3

hrid: 0x010000df.0

col  0: [ 2]  c1 02

col  1: *NULL*

col  2: [2000]

 4b 4d 50 47 44 57 5a 45 46 45 5a 46 56 48 52 54 44 44 50 4e 49 44 56 51 45

 43 48 44 53 54 54 48 4c 55 43 49 51 4c 4c 54 4a 55 42 59 5a 48 44 54 4e 44

 41 59 4f 42 56 50 4f 58 4a 56 50 54 4c 52 5a 42 4e 4d 53 44 4e 4d 58 52 4c


五、使用会话跟踪,可以观察到两张表的IO次数相同,但在字节方面,char表仍比varchar2表高;


SQL ID: ffsfudx75qyyc Plan Hash: 3241939766


select * 

from

 scott.t_char where id=1



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0         23          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.00       0.00          0         23          0           1


Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         1          1          1  TABLE ACCESS FULL T_CHAR (cr=23 pr=0 pw=0 time=265 us cost=7 size=10023 card=1)


=====================================================================


SQL ID: gdvwfucd49ass Plan Hash: 4100862799


select * 

from

 scott.t_varchar where id=1



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0         23          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.00       0.00          0         23          0           1


Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         1          1          1  TABLE ACCESS FULL T_VARCHAR (cr=23 pr=0 pw=0 time=193 us cost=7 size=5023 card=1)


六、通过视图user_segments查看磁盘空间占用,观察到两张表的空间占用一致


SQL> select s.segment_name,s.BYTES,s.blocks from user_segments s where s.segment_name in ('T_CHAR','T_VARCHAR');


SEGMENT_NAME

--------------------------------------------------------------------------------

     BYTES     BLOCKS

---------- ----------

T_CHAR

    196608    24


T_VARCHAR

    196608    24


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

请登录后发表评论 登录
全部评论

注册时间:2017-08-01

  • 博文量
    13
  • 访问量
    6179