20160114physical reads direct path reads

[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.
--我一直认为他的应用问题大于存储性能不好.

时间: 2024-10-14 16:10:14

20160114physical reads direct path reads的相关文章

Oracle 11g新特性direct path read引发的系统停运故障诊断处理

黎俊杰 | 2016-07-28 14:37 声明:部分表名为了脱敏而用XX代替 1.故障现象 (1)一个业务系统输入用户名与密码后无法进入首页,表现为一直在运行等待,运行缓慢 (2)整个系统无法正常使用,接近停运状态 2.故障解决方法 调整数据库参数alter system setevent='10949 trace name context forever, level 1'来关闭"direct path read"(直接路径读)特性,使SQL语句可以从缓存中查询数据,达到降低I/

Parameter DIRECT: Conventional Path Export Versus Direct Path Export [ID 155477.1]引用

Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data ? [ID 552424.1]   Modified 06-APR-2009     Type HOWTO     Status PUBLISHED   In this Document   Goal   Solution      1. Introduction.       2. Expo

[20150511]关于11G Direct Path Read.txt

[20150511]关于11G Direct Path Read.txt --前一阵子被别人问到升级到11G要注意那些细节,我给对方讲了审计,用户口令管理(大小写),sql tuning advisor,auto space advisor, --以及Direct Path Read等问题,对方问了很多Direct Path Read相关的问题. --实际上就是讲简单就是一些表大于一定的情况下,读取数据从磁盘,绕过buffer cache的情况. --如果有用户使用Direct Path Rea

解决direct path read 与 direct path write问题

1.这个问题主要与sort有关. 2.确定对象的sql: SELECT segment_name, partition_name, p1, p2, wait1.sql_hash_value FROM dba_extents, wait1 WHERE wait1.p2 BETWEEN block_id AND (block_id + blocks - 1) AND file_id = wait1.p1 AND wait1.event = 'direct path read'ORDER BY seg

ORACLE等待事件:direct path write

    2015年4月27日,晚上6点左右,电渠3g2库ORACLE RAC系统节点1出现大量的direct path write等待事件,导致大量的会话堆积,节点1几乎无法使用,应用受到影响,相关处理流程如下:     环境:     操作系统:hp-unix     数据库版本:10.2.0.5     故障描述:开始是27号上午9左右,应用开发有人执行update语句,发现执行好长时间没有完成,然后在数据库中查询并试图杀死会话,但是执行杀会话后再操作系统中依然有相关会话存在, 然后,试图使

ORACLE 索引并行引起的direct path read temp和latch free等待导致进程数超过最大数

    2016年10月27日下午,测试同事说测试数据库连接不上了,让我们DBA查看问题并解决一下.    操作系统:Red Hat Enterprise Linux Server release 6.6 (Santiago)     数据库版本: [oracle@se31 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 28 08:59:04 2016 Copyright (c) 1982,

statspack 使用实例

--1.´´½¨ perfstat ±í¿Õ¼ä create  tablespace  perfstat datafile  '/home/orapaid/oradata/prdyp/perfstat.dbf'  size 500m; --2.ÒÑsysµÇ½ִÐÐÏÂÁнű¾@/home/orapaid/product/92/rdbms/admin/catdbsyn.sql@/home/orapaid/product/92/rdbms/admin/dbmspool.sql --3.Ô

ORACLE 物理读 逻辑读 一致性读 当前模式读总结浅析

     在ORACLE数据库中有物理读(Physical Reads).逻辑读(Logical Reads).一致性读(Consistant Get).当前模式读(DB Block Gets)等诸多概念,如果不理解或混淆这些概念的话,对你深入理解一些知识无疑是一个障碍,但是这些概念确实挺让让人犯晕的.下面我们总结.学习一下这方面的知识点.捋一捋他们的关系和特点,希望对你有所帮助.   物理读(Physical Reads)   从磁盘读取数据块到内存的操作叫物理读,当SGA里的高速缓存(Cac

Buffer cache 的调整与优化(一)

--============================== -- Buffer cache 的调整与优化(一) --==============================       Buffer Cache是SGA的重要组成部分,主要用于缓存数据块,其大小也直接影响系统的性能.当Buffer Cache过小的时候,将会造成更多的 free buffer waits事件. 下面将具体描述Buffer Cache的作用,调整与优化.   一.SGA的所有组件     从动态视图v$sg