这篇文章是为了补充《Oracle性能优化与诊断案例精选》一书中的案例而写的，但是想想，也许还可以扩展一下，对于刚接触 Oracle 数据库的朋友们，试着回答一下以下几个问题，看看自己能否找到正确的答案：
当我们 insert 一条记录，不提交，这个数据在内存还是磁盘？
当我们 insert 一条记录，提交，这个数据在内存还是磁盘？
当我们 insert 一条记录，不提交，检查点，这个数据在内存还是磁盘？
当我们 insert 一条记录，提交，检查点，这个数据在内存还是磁盘？
先看看我的测试：
我们首先执行一个数据行插入，提交，执行检查点，这条数据被写入磁盘；
再插入一条数据，提交，此时（在检查点发生之前）这条数据不会写入磁盘；
当然 DUMP 之类的操作也不会引起这条数据写入磁盘；
那么如何验证：
通过DUMP BLOCK，从数据文件转储，只能看到一行记录；
通过DUMP Buffer Cache Block，能够看到两条记录。
这里的知识点是，如何DUMP Buffer Cache中的数据块，书中未提及这个知识点，但是隐含的用到了。
1.以下步骤执行了数据写入和DUMP，我们看到DUMP Datafile时只有一条记录存在于数据文件之上
[eygle@enmoteam1 ~]$ sqlplus eygle/eygle SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 7 14:24:00 2017 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> CREATE TABLE eygle (n varchar2(20)); Table created. SQL> INSERT INTO eygle VALUES('ENMOTECH'); 1 row created. SQL> commit; Commit complete. SQL> ALTER system checkpoint; System altered. SQL> INSERT INTO eygle VALUES('YHEM'); 1 row created. SQL> commit; Commit complete. SQL> select dbms_rowid.rowid_relative_fno(rowid)fno, 2 dbms_rowid.rowid_block_number (rowid) block# from eygle; FNO BLOCK# ---------- ---------- 20 2362757 20 2362757 SQL> alter system dump datafile 20 block 2362757; System altered. SQL> select value from v$diag_info where name='Default Trace File'; VALUE ---------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9165.trc SQL> ! cat /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9165.trc *** ACTION NAME:() 2017-04-07 14:25:11.875 Start dump data blocks tsn: 4 file#:20 minblk 2362757 maxblk 2362757 Block dump from cache: Dump of buffer cache at level 4 for tsn=4 rdba=86248837 BH (0x643e75e8) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x641a0000 set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0 dbwrid: 0 obj: 107958 objn: 107958 tsn: 4 afn: 20 hint: f hash: [0x647f09d8,0x8fc75b48] lru: [0x62fefbd0,0x63fee080] obj-flags: object_ckpt_list ckptq: [0x8ff1f8f0,0x647de048] fileq: [0x8ff1fb70,0x8ff1fb70] objq: [0x87841758,0x87841758] objaq: [0x64feb008,0x87841738] st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 1 flags: buffer_dirty block_written_once redo_since_read LRBA: [0x346.21360.0] LSCN: [0x0.2af3ac2e] HSCN: [0x0.2af3ac2e] HSUB: [1] BH (0x647f0928) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x64698000 set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0 dbwrid: 0 obj: 107957 objn: 107957 tsn: 4 afn: 20 hint: f hash: [0x8fc75b48,0x643e7698] lru: [0x637ecb20,0x653f7750] lru-flags: on_auxiliary_list ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: FREE md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33 flags: Block dump from disk: buffer tsn: 4 rdba: 0x05240d85 (20/2362757) scn: 0x0000.2af3ac29 seq: 0x01 flg: 0x06 tail: 0xac290601 frmt: 0x02 chkval: 0xe737 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F35FD4B9200 to 0x00007F35FD4BB200 7F35FD4BB1F0 0801012C 4F4D4E45 48434554 AC290601 [,...ENMOTECH..).] Block header dump: 0x05240d85 Object id on Block? Y seg/obj: 0x1a5b6 csc: 0x00.2af3ac27 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x5240d80 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0005.011.000e5d0f 0x054000c9.56ba.12 --U- 1 fsc 0x0000.2af3ac29 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x05240d85 data_block_dump,data header at 0x7f35fd4b9264 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x7f35fd4b9264 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f8c avsp=0x1f78 tosp=0x1f78 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f8c block_row_dump: tab 0, row 0, @0x1f8c tl: 12 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 8] 45 4e 4d 4f 54 45 43 48 end_of_block_dump End dump data blocks tsn: 4 file#: 20 minblk 2362757 maxblk 2362757
2.进一步的DUMP Buffer Cache Block，这里有两个命令：
ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p1 level level'; -- 这里的 level 是 表空间号 +1； ALTER SESSION SET EVENTS 'immediate trace name buffer level level'; -- 这里的 level 是 相对文件号 * 4194304 + BlockNumber
经过转储可以看到Cache中的两条记录：
SQL> connect eygle/eygle Connected. SQL> select dbms_rowid.rowid_relative_fno(rowid)fno, 2 dbms_rowid.rowid_block_number (rowid) block# from eygle; FNO BLOCK# ---------- ---------- 20 2362757 20 2362757 SQL> select 20*4194304 +2362757 from dual; 20*4194304+2362757 ------------------ 86248837 SQL> select default_tablespace from dba_users where username='EYGLE'; DEFAULT_TABLESPACE ------------------------------ USERS SQL> SELECT ts# FROM sys.ts$ WHERE name = 'USERS'; TS# ---------- 4 SQL> ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p1 level 5'; Session altered. SQL> ALTER SESSION SET EVENTS 'immediate trace name buffer level 86248837'; Session altered. SQL> select value from v$diag_info where name='Default Trace File'; VALUE ----------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9214.trc SQL> ! cat /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9214.trc Unix process pid: 9214, image: oracle@enmoteam1 (TNS V1-V3) *** 2017-04-07 14:26:33.824 *** SESSION ID:(377.2369) 2017-04-07 14:26:33.824 *** CLIENT ID:() 2017-04-07 14:26:33.824 *** SERVICE NAME:(SYS$USERS) 2017-04-07 14:26:33.824 *** MODULE NAME:(SQL*Plus) 2017-04-07 14:26:33.824 *** ACTION NAME:() 2017-04-07 14:26:33.824 Dump of buffer cache at level 10 for tsn=4 rdba=86248837 0641A1FE0 00000000 00000000 0401022C 4D454859 [........,...YHEM] 0641A1FF0 0801012C 4F4D4E45 48434554 AC2E0602 [,...ENMOTECH....] Block header dump: 0x05240d85 Object id on Block? Y seg/obj: 0x1a5b6 csc: 0x00.2af3ac27 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x5240d80 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0005.011.000e5d0f 0x054000c9.56ba.12 --U- 1 fsc 0x0000.2af3ac29 0x02 0x0007.00c.000e6208 0x05400148.5ca1.2d --U- 1 fsc 0x0000.2af3ac2e bdba: 0x05240d85 data_block_dump,data header at 0x641a0064 =============== tsiz: 0x1f98 hsiz: 0x16 pbl: 0x641a0064 76543210 flag=-------- ntab=1 nrow=2 frre=-1 fsbo=0x16 fseo=0x1f84 avsp=0x1f6d tosp=0x1f6d 0xe:pti[0] nrow=2 offs=0 0x12:pri[0] offs=0x1f8c 0x14:pri[1] offs=0x1f84 block_row_dump: tab 0, row 0, @0x1f8c tl: 12 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 8] 45 4e 4d 4f 54 45 43 48 tab 0, row 1, @0x1f84 tl: 8 fb: --H-FL-- lb: 0x2 cc: 1 col 0: [ 4] 59 48 45 4d end_of_block_dump BH (0x647f0928) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x64698000 set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0 dbwrid: 0 obj: 107957 objn: 107957 tsn: 4 afn: 20 hint: f hash: [0x8fc75b48,0x643e7698] lru: [0x637ecb20,0x653f7750] lru-flags: on_auxiliary_list ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: FREE md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33 flags: Buffer contents not dumped
这样的动手测试验证，非常有助于理解 Oracle 的运行原理，以及在这后台 Redo 的作用。三天不练手生，大家应当动手不懈。而且如果能够看到以上 TRACE 文件中的所有输出，那就是具有相当水准的专家风范了。
本文来自云栖社区合作伙伴“DBGEEK”
