16、物理IO_2

一只c小凶许發表於2024-11-21

物理讀

檢視IO狀態（作業系統層面）：

[root@db11g ~]# iostat 1 100 -x
Linux 2.6.32-431.el6.x86_64 (db11g) 	02/06/2017 	_x86_64_	(2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.29    0.00    2.27    0.09    0.00   97.35

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.25     3.50    0.24    2.29    14.49    39.00    21.11     0.01    2.54   2.30   0.58
scd0              0.00     0.00    0.00    0.00     0.00     0.00     8.00     0.00    1.27   1.25   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.68    0.00    3.38    0.00    0.00   95.95

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
-- r/s + w/s = iops
-- rsec/s + wsec/s = IO吞吐量
-- %util：繁忙度
-- 當磁碟裡發生順序的從頭到尾的讀資料的時候，rrqm/s的值會很高

吞吐量的計算：

rsec/s = 14.49
14.49 * 0.5 = 7.24
7.24/1024 = 0.007MByte/s

幾個IO相關的統計資訊（oracle資料庫內部層面）

檢視當前會話的sid：

SQL> set linesize 150
SQL> set pagesize 0
SQL> select sid from v$mystat where rownum = 1;
	19                                             -- 當前會話的sid

查詢物理讀的引數：

select b.name,a.value from v$sesstat a,v$statname b
 where a.STATISTIC#=b.STATISTIC# and lower(name) like '%physical read%' and sid=19;

物理讀常關注的幾個引數有：
1、physical read total IO requests：total IO是所有的IO，還包含一些其他的管理工具所產生的物理IO（這些管理工具的物理IO都是不經過buffer的）；physical read total IO requests是總的IO，以次數來計算（總的IO次數）

2、physical read IO requests：不包含其他的管理工具所產生的物理IO的所有IO
3、physical read total multi block requests：多塊讀的次數
4、physical read total bytes：讀取資料的總位元組數
5、physical reads：從磁碟讀取資料的總塊數
6、physical reads cache：從資料庫的block讀取到記憶體的buffer的物理讀（包含在total IO裡面）
7、physical reads direct：從資料庫的block讀取到PGA的物理讀

8、physical reads direct temporary tablespace：臨時檔案交換空間（發生磁碟排序）

9、physical reads direct (lob)：讀取大物件（圖片、照片）

1和2都是總的物理讀（physical read total IO requests包含physical read IO requests）

physical reads cache + physical reads direct = physical read IO requests

physical reads cache（讀取的是塊數） + physical reads direct（讀取的是次數）所以有可能physical read IO requests（讀取的是次數）的值小於它倆的和（因為多塊讀的時候，次數就少了）

physical read total bytes/physical read total IO requests = 每次讀取資料的大小（等於8k時，說明是單塊讀）

physical read total IO requests：既包含單塊讀的次數，也包含多塊讀的次數

physical reads >= physical read total IO requests（總塊數大於等於總次數，因為有多塊讀的時候）

physical reads direct temporary tablespace 和 physical reads direct (lob)屬於非常規的

上述公式是沒問題的，但是大體上沒那麼嚴格，導致資料不是那麼的準確，它可能還有一些別的情況（比如一些後臺程序產生的一些讀寫的情況）

或者這樣查詢（只看自己想知道的引數）：

select b.name,a.value from v$sesstat a,v$statname b
where a.STATISTIC#=b.STATISTIC# and lower(name) like '%physical read%' and sid=820
and name in ('physical read total IO requests',
'physical read total multi block requests',
'physical read total bytes',
'physical reads',
'physical reads cache',
'physical reads direct',
'physical reads direct temporary tablespace',
'physical reads direct (lob)',
'physical read IO requests');

現在刪除表t1：

SQL> drop table t1;
Table dropped.

再次查詢物理讀引數：

select b.name,a.value from v$sesstat a,v$statname b
where a.STATISTIC#=b.STATISTIC# and lower(name) like '%physical read%' and sid=926
and name in ('physical read total IO requests',
'physical read total multi block requests',
'physical read total bytes',
'physical reads',
'physical reads cache',
'physical reads direct',
'physical reads direct temporary tablespace',
'physical reads direct (lob)',
'physical read IO requests');

檢視多塊讀的引數：

SQL> show parameter db_file
db_file_multiblock_read_count	           integer	 19   -- 每次多塊讀的數量
db_file_name_convert		            string
db_files			                 integer	 200

對t1表進行一次全表掃描：

SQL> select count(*) from t1;   -- 查詢總行要全表掃描
     86309

select b.name,a.value from v$sesstat a,v$statname b
where a.STATISTIC#=b.STATISTIC# and lower(name) like '%physical read%' and sid=926
and name in ('physical read total IO requests',
'physical read total multi block requests',
'physical read total bytes',
'physical reads',
'physical reads cache',
'physical reads direct',
'physical reads direct temporary tablespace',
'physical reads direct (lob)',
'physical read IO requests');

跟蹤會話（模擬物理讀過程）

查詢會話sid和serial#：

select sid,serial# from v$session a where a.USERNAME='U1';

開啟跟蹤，執行SQL，關閉跟蹤：

SQL> exec dbms_monitor.session_trace_enable(926,543);  -- 開啟跟蹤
PL/SQL procedure successfully completed.

SQL> select count(*) from t1;  -- 執行select
     86309

SQL> exec dbms_monitor.session_trace_disable(926,543);  -- 結束跟蹤
PL/SQL procedure successfully completed.

檢視跟蹤檔案：

[oracle@db11g ~]$ cd $LOG
[oracle@db11g trace]$ ll -t | more


[oracle@db11g trace]$ vi cis_ora_3338.trc


模擬臨時資料檔案交換空間的物理讀過程

同樣的跟蹤會話：

記憶體讀
select記憶體讀

SQL> set autotrace trace
SQL> select * from t1;
86309 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time	     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	         | 71096 |    14M|   344   (1)| 00:00:05 |
|   1 |  TABLE ACCESS FULL| T1	 | 71096 |    14M|   344   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
	 22  recursive calls
	  0  db block gets
       6980  consistent gets     --- select記憶體讀
       1233  physical reads
	  0  redo size
    9923052  bytes sent via SQL*Net to client
      63807  bytes received via SQL*Net from client
       5755  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      86309  rows processed

DML記憶體讀

SQL> set autotrace trace
SQL> delete from t1;
86309 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 775918519

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | DELETE STATEMENT   |	      |	1 |   343   (0)| 00:00:05 |
|   1 |  DELETE 	                | T1   |	  |	           |	               |
|   2 |   TABLE ACCESS FULL| T1   |	1 |   343   (0)| 00:00:05 |
-------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
	 38  recursive calls
      96745  db block gets    --- DML記憶體讀
       1331  consistent gets
	971  physical reads
  32640372  redo size
	842  bytes sent via SQL*Net to client
	771  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
      86309  rows processed

oracle備份、恢復的物理讀

物理寫

物理寫常關注的幾個引數：

physical writes
physical writes direct
physical write IO requests
physical writes direct temporary tablespace
physical write total multi block requests
physical write bytes
physical writes direct (lob)
physical write total IO requests

select * from v$system_event;

相關文章