[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的原因.至於為什麼這樣,我就不清除了.^_^ .

[20170224]nocache工具的小測試2.txt

相關文章