oracle優化--表優化（行鏈接產生實驗）

一、通過一次插入一筆大于基本塊大小（8K）的數據，制造一筆有行鏈接但沒有行遷移的數據

SQL> insert into t_pctfree values (10002,dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000));

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

二、使用dump確定此行數據有行鏈接但沒有行遷移

SQL> select dbms_rowid.rowid_block_number(rowid) block_id from t_pctfree where id=10002;

  BLOCK_ID

----------

     51036

SQL> alter system dump datafile 5 block 51036;

System altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25082.trc

[oracle@localhost ~]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25082.trc

block_row_dump:

tab 0, row 0, @0xfe4

tl: 4020 fb: --H-F--- lb: 0x1  cc: 3

nrid:  0x0140c758.1

col  0: [ 4]  c3 02 01 03

col  1: [2000]

 55 51 46 4b 57 4c 4a 48 4b 47 4e 43 43 59 42 46 56 44 54 48 54 50 42 51 47

 58 59 54 54 41 48 45 4e 5a 56 4c 47 44 4e 55 43 53 43 51 4d 57 4e 54 59 5a

 48 48 4c 48 41 41 4e 44 45 51 4c 51 4c 58 47 4a 4f 4a 42 4f 41 59 57 57 58

 46 4d 50 4a 45 57 46 42 45 48 48 46 41 44 5a 43 53 55 59 53 4a 42 52 5a 4f

 4b 4f 52 56 56 46 4e 58 57 4a 46 4b 4a 46 56 54 59 51 59 4a 49 51 42 56 4e

 53 48 59 4e 58 55 57 44 41 41 53 4c 4f 43 45 52 4b 4c 4d 46 58 4d 53 50 51

三、確認列1是否是剛才插入的數據

SQL> var n number;

SQL> exec dbms_stats.convert_raw_value('c3020103',:n);

PL/SQL procedure successfully completed.

SQL> print :n

N

----------

     10002

因此可以確認此塊（51036）存儲有1、2兩列信息，剩余列在另一個塊上，而不像是行遷移時，塊信息里只有nrid

四、查看被鏈接的塊信息

SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('0140c758', 'xxxxxxxxxx')) file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(to_number('0140c758','xxxxxxxxxx')) block# from dual;

     FILE#     BLOCK#

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

5 51032

SQL> alter system dump datafile 5 block 51032;

System altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25101.trc

tl: 6012 fb: -----L-- lb: 0x2  cc: 3

col  0: [2000]

 4e 49 59 52 43 52 47 41 44 44 4e 54 43 5a 4d 4d 53 47 55 55 4d 56 55 44 55

 4f 46 53 50 47 4d 50 52 43 45 51 57 52 41 44 4c 55 49 55 59 4c 57 58 5a 52

 44 50 41 45 52 48 57 45 4b 53 58 54 50 56 47 52 4b 5a 52 4d 50 44 42 43 50

 4b 48 51 41 54 47 49 48 50 4c 45 57 4f 48 4f 4a 55 4c 50 59 52 4d 53 55 50

 59 4d 41 41 55 53 5a 48 56 52 41 44 4e 53 49 4a 58 4c 4a 4e 47 54 55 44 43

 4f 56 4f 55 4a 41 46 44 42 55 41 42 48 58 47 56 57 52 55 47 52 44 4a 42 48