16、物理IO_2
物理读
查看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;
