[20160114]physical reads和direct path reads.txt
--上个星期在ITPUB论坛里遇到几个关于磁盘性能地下的问题.
--主要链接如下:
http://www.itpub.net/thread-2050157-1-1.html
http://www.itpub.net/thread-2050439-1-1.html
http://www.itpub.net/thread-2050601-1-1.html
--都是表现为磁盘IO相关的等待时间比较大.论坛讨论大部分认为是存储出现问题.
--而其中链接:http://www.itpub.net/thread-2050439-1-1.html
--因为我们旧系统使用的就是该公司的产品,他们的连接数比我们小,而应用出现这个严重的性能问题.
--log file sync单次8354毫秒.
--如果仔细检查可以发现大量索引没有建立,而且看AWR报表发现:
Segments by Physical Reads
Total Physical Reads: 5,752,157
Captured Segments account for 94.1% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
PHARMACY TSP_PHARMACY DRUG_DISPENSE_REC TABLE 4,233,164 73.59
OUTPBILL TSP_OUTPBILL INVOICE_INFO TABLE 763,679 13.28
OUTPBILL TSP_OUTPBILL OUTP_BILL_ITEMS TABLE 371,704 6.46
OUTPDOCT TSP_OUTPADM OUTP_PRESC TABLE 18,007 0.31
INPBILL TSP_INPBILL INP_BILL_DETAIL TABLE 8,609 0.15
Back to Segment Statistics
Back to Top
Segments by Direct Physical Reads
Total Direct Physical Reads: 5,599,810
Captured Segments account for 95.8% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Direct Reads %Total
PHARMACY TSP_PHARMACY DRUG_DISPENSE_REC TABLE 4,231,626 75.57
OUTPBILL TSP_OUTPBILL INVOICE_INFO TABLE 763,584 13.64
OUTPBILL TSP_OUTPBILL OUTP_BILL_ITEMS TABLE 371,366 6.63
--上下对比问题就很清楚了.
单独拿DRUG_DISPENSE_REC来看:
Direct Physical Reads =4,231,626
Physical Reads = 4,233,164
-- 4233164-4231626=1538 ,真正的物理读仅仅1538.
两者基本相等.我认为Direct Physical Reads算作Physical Reads的一部分.还是测试看看.
1.环境:
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> @ &r/hide _small_table_threshold
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_small_table_threshold%')
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------ ---------------------------------------------------- ------------- ------------- ------------
_small_table_threshold lower threshold level of table size for direct reads TRUE 872 872
$ cat sess.sql
set verify off
column name format a40
SELECT b.NAME, a.statistic#, a.VALUE,a.sid
FROM v$mystat a, v$statname b
WHERE lower(b.NAME) in ('consistent gets direct','physical reads direct','table scans (direct read)',
'cleanouts only - consistent read gets',
'session logical reads',
'physical reads',
'redo size'
) AND a.statistic# = b.statistic# order by name ;
--直接拿sh.sales测试:
SCOTT@book> @ &r/sess
NAME STATISTIC# VALUE SID
---------------------------------------- ---------- ---------- ----------
cleanouts only - consistent read gets 328 0 232
consistent gets direct 92 0 232
physical reads 94 0 232
physical reads direct 97 0 232
redo size 194 772 232
session logical reads 14 46 232
table scans (direct read) 413 0 232
7 rows selected.
SCOTT@book> select /*+full(a) */ count(*) from sh.sales a;
COUNT(*)
----------
918843
SCOTT@book> @ &r/sess
NAME STATISTIC# VALUE SID
---------------------------------------- ---------- ---------- ----------
cleanouts only - consistent read gets 328 0 232
consistent gets direct 92 1619 232
physical reads 94 1619 232
physical reads direct 97 1619 232
redo size 194 772 232
session logical reads 14 1681 232
table scans (direct read) 413 16 232
7 rows selected.
--可以发现physical reads direct,physical reads 接近.说明physical reads direct是直接路径读的一部分.
3.看看awr报表:
SCOTT@book> exec dbms_workload_repository.create_snapshot();
--执行 select /*+full(a) */ count(*) from sh.sales a; 多次..
SCOTT@book> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--检查awr报表:
Segments by Logical Reads DB/Inst: BOOK/book Snaps: 836-837
-> Total Logical Reads: 89,525
-> Captured Segments account for 98.8% of Total
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SH EXAMPLE SALES ES_Q4_2001 TABLE 5,712 6.38
SH EXAMPLE SALES ES_Q1_1999 TABLE 5,152 5.75
SH EXAMPLE SALES ES_Q3_1999 TABLE 5,152 5.75
SH EXAMPLE SALES ES_Q3_2001 TABLE 5,152 5.75
SH EXAMPLE SALES ES_Q1_2000 TABLE 5,056 5.65
------------------------------------------------------
Segments by Physical Reads DB/Inst: BOOK/book Snaps: 836-837
-> Total Physical Reads: 74,514
-> Captured Segments account for 100.0% of Total
Tablespace Subobject Obj. Physical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SH EXAMPLE SALES ES_Q4_2001 TABLE 5,704 7.65
SH EXAMPLE SALES ES_Q3_2001 TABLE 5,244 7.04
SH EXAMPLE SALES ES_Q1_1999 TABLE 5,106 6.85
SH EXAMPLE SALES ES_Q3_1999 TABLE 5,060 6.79
SH EXAMPLE SALES ES_Q1_2000 TABLE 5,014 6.73
------------------------------------------------------
Segments by Physical Read Requests DB/Inst: BOOK/book Snaps: 836-837
-> Total Physical Read Requests: 776
-> Captured Segments account for 97.4% of Total
Tablespace Subobject Obj. Phys Read
Owner Name Object Name Name Type Requests %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SH EXAMPLE SALES ES_Q1_1998 TABLE 46 5.93
SH EXAMPLE SALES ES_Q1_1999 TABLE 46 5.93
SH EXAMPLE SALES ES_Q1_2000 TABLE 46 5.93
SH EXAMPLE SALES ES_Q1_2001 TABLE 46 5.93
SH EXAMPLE SALES ES_Q2_1998 TABLE 46 5.93
------------------------------------------------------
Segments by UnOptimized Reads DB/Inst: BOOK/book Snaps: 836-837
-> Total UnOptimized Read Requests: 776
-> Captured Segments account for 97.4% of Total
Tablespace Subobject Obj. UnOptimized
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SH EXAMPLE SALES ES_Q1_1998 TABLE 46 5.93
SH EXAMPLE SALES ES_Q1_1999 TABLE 46 5.93
SH EXAMPLE SALES ES_Q1_2000 TABLE 46 5.93
SH EXAMPLE SALES ES_Q1_2001 TABLE 46 5.93
SH EXAMPLE SALES ES_Q2_1998 TABLE 46 5.93
------------------------------------------------------
Segments by Optimized Reads DB/Inst: BOOK/book Snaps: 836-837
No data exists for this section of the report.
------------------------------------------------------
Segments by Direct Physical Reads DB/Inst: BOOK/book Snaps: 836-837
-> Total Direct Physical Reads: 74,476
-> Captured Segments account for 100.0% of Total
Tablespace Subobject Obj. Direct
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SH EXAMPLE SALES ES_Q4_2001 TABLE 5,704 7.66
SH EXAMPLE SALES ES_Q3_2001 TABLE 5,244 7.04
SH EXAMPLE SALES ES_Q1_1999 TABLE 5,106 6.86
SH EXAMPLE SALES ES_Q3_1999 TABLE 5,060 6.79
SH EXAMPLE SALES ES_Q1_2000 TABLE 5,014 6.73
------------------------------------------------------
--可以发现Direct Physical Reads算做Physical Reads的一部分,Physical Reads算作Logical Reads的一部分.
--而看
File IO Stats
ordered by Tablespace, File
Tablespace Filename Reads Av Rds/s Av Rd(ms) Av Blks/Rd 1-bk Rds/s Av 1-bk Rd(ms) Writes Writes avg/s Buffer Waits Av Buf Wt(ms)
EXAMPLE /mnt/ramdisk/book/example01.dbf 736 16 0.00 101.19 0 0 0 0 0.00
SYSAUX /mnt/ramdisk/book/sysaux01.dbf 30 1 0.00 1.00 1 0.00 3 0 0 0.00
SYSTEM /mnt/ramdisk/book/system01.dbf 15 0 0.00 1.00 0 0.00 0 0 0 0.00
--我的测试次数不是太多,可以发现file Io STAT 的读 仅仅 736 .Tablespace IO Stats也出现类似的情况,这样非常具有迷惑性,感觉
--读很少,实际上系统存在大量的直接路径读,如果仔细看IOStat by Function/Filetype summary就可以发现:
IOStat by Function summary DB/Inst: BOOK/book Snaps: 836-837
-> 'Data' columns suffixed with M,G,T,P are in multiples of 1024
other columns suffixed with K,M,G,T,P are in multiples of 1000
-> ordered by (Data Read + Write) desc
Reads: Reqs Data Writes: Reqs Data Waits: Avg
Function Name Data per sec per sec Data per sec per sec Count Tm(ms)
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Direct Reads 582M 16.0 12.638M 0M 0.0 0M 736 0.0
LGWR 0M 0.0 0M 2M 0.6 .043M 56 0.0
Others 2M 3.3 .043M 0M 0.7 0M 183 0.0
Buffer Cache Re 0M 0.8 0M 0M 0.0 0M 37 0.0
Direct Writes 0M 0.0 0M 0M 0.1 0M 3 0.0
TOTAL: 584M 20.1 12.681M 2M 1.3 .043M 1015 0.0
------------------------------------------------------
--再回过头看http://www.itpub.net/thread-2050439-1-1.html的awr报表,居然发现这个版本没有这部分内容.他使用的是11.1.0.7.0.
--我一直认为他的应用问题大于存储性能不好.
