[20170224]nocache工具的小测试2.txt
http://blog.itpub.net/267265/viewspace-2134054/
--前面我测试读取oracle某个数据块时,文件系统缓存会64K,而且并不是该块开始,而是整齐画一的64K.
--今天看看如果读取文件呢?
1.环境:
$ ls -l alert_book.log
-rw-r----- 1 oracle oinstall 4279050 2017-02-24 08:38:22 alert_book.log
$ cachedel alert_book.log
2.测试使用dd:
$ dd if=alert_book.log skip=4096 bs=1 count=1
1+0 records in
1+0 records out
1 byte (1 B) copied, 0.000154046 seconds, 6.5 kB/s
$ cachestats -v alert_book.log
alert_book.log pages in cache: 1/1045 (0.1%) [filesize=4178.8K, pagesize=4K]
cache map:
0: | |x| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
32: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
--//正好读取1块.也是4K分,第2块的位置.测试读3块看看并使用strce跟踪看看:
$ cachedel -n 2 alert_book.log
$ cachestats -v alert_book.log
alert_book.log pages in cache: 0/1045 (0.0%) [filesize=4178.8K, pagesize=4K]
cache map:
0: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
32: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
64: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
$ strace -o /tmp/dd.txt dd if=alert_book.log skip=8192 bs=1 count=1
g1+0 records in
1+0 records out
1 byte (1 B) copied, 0.000604916 seconds, 1.7 kB/s
$ cachestats -v alert_book.log
alert_book.log pages in cache: 1/1045 (0.1%) [filesize=4178.8K, pagesize=4K]
cache map:
0: | | |x| | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
32: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
ioctl(0, MGSL_IOCSTXIDLE or MTIOCGET or SNDCTL_MIDI_MPUCMD, 0x7fff6ce0b100) = -1 ENOTTY (Inappropriate ioctl for device)
lseek(0, 8192, SEEK_CUR) = 8192
read(0, "g", 1) = 1
write(1, "g", 1) = 1
close(0) = 0
close(1) = 0
--//可以发现精确读取第3块的位置(pagesize=4K).写出如下
$ cachedel -n 2 alert_book.log
$ dd if=alert_book.log of=/dev/null skip=2 bs=4096 count=1
--//也是一样.
$ cachestats -v alert_book.log
alert_book.log pages in cache: 1/1045 (0.1%) [filesize=4178.8K, pagesize=4K]
cache map:
0: | | |x| | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
32: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
--//使用bvi工具看看:(执行前先执行cachedel -n 2 alert_book.log)
$ env COLUMNS=266 bvi -b 8192 -s 1 alert_book.log
--//发现结果也与前面的测试一样.难道读取64K是oracle相关吗?还是回到数据库测试看看.
3.数据库环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> show parameter filesystem
NAME TYPE VALUE
-------------------- ------ ------
filesystemio_options string none
--//注:这次测试没执行这条语句.而是直接执行select rowid,dept.* from dept where rowid='AAAVRCAAEAAAACHAAA';.
SCOTT@book> select rowid,dept.* from dept ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
AAAVRCAAEAAAACHAAC 30 SALES CHICAGO
AAAVRCAAEAAAACHAAD 40 OPERATIONS BOSTON
SCOTT@book> @ &r/rowid AAAVRCAAEAAAACHAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
87106 4 135 0 0x1000087 4,135 alter system dump datafile 4 block 135 ;
$ cachedel /mnt/ramdisk/book/users01.dbf
SCOTT@book> select rowid,dept.* from dept where rowid='AAAVRCAAEAAAACHAAA';
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
$ cachestats -v /mnt/ramdisk/book/users01.dbf | head -16
/mnt/ramdisk/book/users01.dbf pages in cache: 18/32770 (0.1%) [filesize=131080.0K, pagesize=4K]
cache map:
0: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
32: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
64: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
96: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
128: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
160: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
192: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
224: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
256: | | | | | | | | | | | | | | |x|x| | | | | | | | | | | | | | | | |
288: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
320: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
352: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
384: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
--//这次就正确了.而且缓存就是2块(pagesize=4K).为什么前面的测试缓存64K呢?而且前面的测试我自己重复测试多次.
--//噢,仔细想想明白了,当时我的测试先执行:
select rowid,dept.* from dept ;
--//然后再执行如下:
alter system flush buffer_cache;
$ cachedel /mnt/ramdisk/book/users01.dbf
select rowid,dept.* from dept where rowid='AAAVRCAAEAAAACHAAA';
$ cachestats -v /mnt/ramdisk/book/users01.dbf | head -16
--//这样就出现前面第一次测试的情况.而这次一上来就执行:
alter system flush buffer_cache;
$ cachedel /mnt/ramdisk/book/users01.dbf
select rowid,dept.* from dept where rowid='AAAVRCAAEAAAACHAAA';
$ cachestats -v /mnt/ramdisk/book/users01.dbf | head -16
--//两者的差别是仅仅开始是否执行了select rowid,dept.* from dept ; 语句.
--//从另外一个方面也说明与加载的数据块有关.
--//在执行select rowid,dept.* from dept ;后相关的数据库已经读入数据缓存.
SCOTT@book> select * from dba_extents where owner=user and segment_name='DEPT';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ -------------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT DEPT TABLE USERS 0 4 128 65536 8 4
--//128,128+8-1=135之间.
SCOTT@book> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner=user and segment_name='DEPT';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 130
--//段头在130块,而相关数据在135块.
--//执行select rowid,dept.* from dept ; 执行计划如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2mrphdnp9mw7q, child number 1
-------------------------------------
select rowid,dept.* from dept
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
--//要逻辑读7,130,131,132,133,134,135块.(6块).
SCOTT@book> alter system flush buffer_cache;
System altered.
--//这样的对应的数据块在缓存被清除,标识为free
SYS@book> @ &r/bh 4 134
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084B66B38 4 134 0 free 0 0 0 0 0 0 000000007043A000 DEPT
0000000084B66B38 4 134 0 free 0 0 0 0 0 0 0000000072024000 DEPT
0000000084B66B38 4 134 1 data block free 0 0 0 0 0 0 0000000072638000 DEPT
SCOTT@book> select rowid,dept.* from dept where rowid='AAAVRCAAEAAAACHAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cut97mhj7hyy3, child number 1
-------------------------------------
select rowid,dept.* from dept where rowid='AAAVRCAAEAAAACHAAB'
Plan hash value: 3453257278
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 1 | 8 |
| 1 | TABLE ACCESS BY USER ROWID| DEPT | 1 | 1 | 20 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | 8 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
--//buffers=1,而reads=8? 有一点奇怪吧.oracle为什么这样操作呢?不是很清楚....
SYS@book> @ &r/bh 4 134
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ------------
0000000084B66B38 4 134 14 unused xcur 0 0 0 0 0 0 0000000071B7E000 DEPT
0000000084B66B38 4 134 0 free 0 0 0 0 0 0 0000000072024000 DEPT
0000000084B66B38 4 134 1 data block free 0 0 0 0 0 0 0000000072638000 DEPT
--//这里也说明dba=4,134,state=xcur,说明执行时读取了块dba=4,134的块地址.再次执行:
SCOTT@book> select rowid,dept.* from dept where rowid='AAAVRCAAEAAAACHAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
--//执行计划:
Plan hash value: 3453257278
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 1 |
| 1 | TABLE ACCESS BY USER ROWID| DEPT | 1 | 1 | 20 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
-----------------------------------------------------------------------------------------------------------------------------
--//buffers=1.上下对比也说明问题,这也是我前面看到文件系统为什么缓存64K的原因.至于为什么这样,我就不清除了.^_^ .