ORACLE TUNE THINKING (二) 等待优化思路

sunyunyi

等待优化思路：

作者简介：
----------------------------------------------------
@ 孙显鹏，海天起点oracle技术专家，十年从业经验
@ 精通oracle内部原理，擅长调优和解决疑难问题
@ 致力于帮助客户解决生产中的问题，提高生产效率。
@ 爱好：书法，周易，中医。微信：sunyunyi_sun
@ 易曰：精义入神，以致用也！
@ 调优乃燮理阴阳何其难也！
-----------------------------------------------------


首先说下等待事件优化思路,以db file sequential read 为例:
查询mos文档弄清楚db file sequential read 是什么意思，P1,P2,P3值含义，等待时间，处理方法。
看看mos上描述db file sequential read事件：

Definition:
Versions:7.0 - 12.1
Documentation: 12.1 11.2 11.1 10.2 10.1

This signifies a wait for an I/O read request to complete. This call differs from "db file scattered read" in that a sequential read reads data
into contiguous memory (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).
A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3).
This wait may also be seen for reads from datafile headers (P2=1 indicates a file header read) .

上面是定义，下面是参数
Individual Waits:
Parameters:        
    P1 = file#
    P2 = block#
    P3 = blocks

        file#   This is the file# of the file that Oracle is trying to read
                from. From Oracle8 onwards it is the ABSOLUTE file number (AFN).

        block#  This is the starting block number in the file from where
                Oracle starts reading the blocks. Typically only one block is
                being read.

                See Note:181306.1 to determine the tablespace, filename
                and object for this file#,block# pair.

        blocks  This parameter specifies the number of blocks that Oracle is
                trying to read from the file# starting at block#. This is
                usually "1" but if P3 > 1 then this is a multiblock read.
                Multiblock "db file sequential read"s may be seen in
                earlier Oracle versions when reading from a SORT (TEMPORARY)
                segments.

Wait Time:        
    The IO is generally issued as a single IO request to the OS - the wait
    blocks until the IO request completes.
    Note than an Oracle read request to the OS may be satisfied from an
    OS file system cache so the wait time may be very small.


Systemwide Waits:
IO is a normal activity so you are really interested in unnecessary or slow IO activity.
If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the "Tablespace IO",
and "File IO" sections of the AWR (or STATSPACK) reports, along with ADDM and ASH output, to get information on which tablespaces / files are
servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant
then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found
by looking at V$FILESTAT.

See the "Top SQL by Disk Reads" sections of AWR reports for clues about any SQL causing high I/O.

It can also be useful to see which sessions are performing reads and trace them to see if the IOs are expected or not. This statement can be used
to see which sessions may be worth tracing:

  SELECT sid, total_waits, time_waited
    FROM v$session_event
   WHERE event='db file sequential read'
     and total_waits>0
   ORDER BY 3,2
  ;
One can also look at:

Statements with high DISK_READS in V$SQL - shown in the "Top SQL by Disk Reads" section of AWR.
Sessions with high "physical reads" in V$SESSTAT

Reducing Waits / Wait times:
Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by good application design and efficient
execution plans. Changes to execution plans can yield orders of magnitude changes in performance. Tweaking at system level usually only achieves percentage gains.
The following points may help: ---可参考处理思路
Check for SQL using unselective index scans
A larger buffer cache can help - test this by actually increasing <<ParameterB_CACHE_SIZE>> (or <<ParameterB_BLOCK_BUFFERS>> if still using that).
Never increase the SGA size if it may induce additional paging or swapping on the system.
A less obvious issue which can affect the IO rates is how well data is clustered physically.
Eg: Assume that you frequently fetch rows from a table where a column is between two values via an index scan.
If there are 100 rows in each index block then the two extremes are:
Each of the table rows is in a different physical block (100 blocks need to be read for each index block)
The table rows are all located in the few adjacent blocks (a handful of blocks need to be read for each index block)
Pre-sorting or re-organising data can help to tackle this in severe situations.
See if partitioning can be used to reduce the amount of data you need to look at.
It can help to place files which incur frequent index scans on disks which have are buffered by a cache of some form. eg: flash cache or hardware disk cache.
For non-ASM based databases put such datafiles on a filesystem with an O/S file system cache. This can allow some of Oracles read requests to be satisfied from
the cache rather than from a real disk IO.

关于file# 和 block#的说明：

This reference note describes how to interpret file# and block# parameters from wait events (eg: obtained from <> or from the WAIT trace lines in trace files).
The text uses:
&AFN to represent the absolute file number (file#)
&BLOCKNO to represent the block number (block#)
IMPORTANT: The details here assume that file# is an absolute file number.

file#
The name of the file can be displayed with the following SQL:
  SELECT tablespace_name, file_name
    FROM dba_data_files
   WHERE file_id = &AFN
  ;
If the file number does not appear in with this select in Oracle8i/9i/10g/11g AND file# is greater than the DB_FILES parameter value then the file is probably a
TEMPFILE. In this case the filename can be found using:
  SELECT tablespace_name, file_name
    FROM dba_temp_files f, v$parameter p
   WHERE p.name='db_files'
     AND f.file_id+p.value = &AFN
  ;
block#
If the file is NOT a TEMPFILE then the following query should show the name and type of the segment:
  SELECT  owner , segment_name , segment_type
    FROM  dba_extents
   WHERE  file_id = &AFN
     AND  &BLOCKNO BETWEEN block_id AND block_id + blocks -1
  ;
In Oracle8 onwards add PARTITION_NAME to the select list above to obtain details of the partition (if any).
If the block# is 1 then this is typically a datafile header and the query above will return no rows.
For INDEX segments it is often desirable to know which table the index is on. Eg:

  SELECT table_owner, table_name
    FROM dba_indexes
   WHERE owner='&OWNER'
     AND index_name='&SEGMENT_NAME'
  ;
Absolute File Number
In Oracle7 all file# values can be treated as the absolute file number for the queries in this note .
In Oracle8 onwards each datafile has a relative file number and an absolute file number. The relative file number is relative to the tablespace which owns the datafile. The following query will show the absolute and relative file numbers for datafiles in the database:

  SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
    FROM dba_data_files
  ;
In Oracle8i onwards a database can also contain TEMPFILES. The following query will show the absolute and relative file numbers for tempfiles in the database:
  SELECT tablespace_name, file_id+value "AFN", relative_fno "RFN"
    FROM dba_temp_files, v$parameter
   WHERE name='db_files';
It is important to use the correct (absolute) file number in the queries in this note.

总结下：
db file sequential read 等待表示session等待一个读io操作，P1代表文件绝对号，P2代表起始块号，P3代表一次读几个块，
通过P1可以定位到哪个数据文件继而知道哪个表空间（特别是undo还是temp表空间），P1,P2结合可以确定哪个对象（表，索引），
P2,P3可以确定是否读段头块。通过等待时间可以确定是否平均等待时间是否过大，是否IO出现问题？索引不合理，是否可以采用
多块读操作提高效率。如果是事后分析通过查询ASH：

set lines 1200 pages 1200
col tablespace_name for a30
select f.tablespace_name,sum(ash.p1_ct) tbs_access_ct
from dba_data_files f,
(
select p1,count(*) p1_ct
from dba_hist_active_sess_history h
where h.sample_time>=to_date('2018-07-04:16:00','yyyy-mm-dd-hh24:mi:ss')
      and h.sample_time<=to_date('2018-07-04:16:30','yyyy-mm-dd-hh24:mi:ss')
      and h.sql_id = '&sql_id'
      and h.event='db file sequential read'
group by p1) ash
where f.file_id=ash.p1
group by  f.tablespace_name
desc 2
/


对于所有等待事件都需要如上学习，首先弄明白等待的含义然后学习处理方法！处理任何问题道理是一样的，把事情弄明白是前提，
你把病情都没搞明白你就开药方那是要出大问题的，弄不好出人命的。你就一个一个等待如此学习，积累多了处理故障那就思路开阔了。
这也就是我一直和大家说的一定要研究原理，认真阅读官方文档的原因了。比如安装rac，你就认真阅读oracle rac安装文档就行了，
别到处百度了，官方文档把所有问题都说的很清楚，特别是附录讲了很多实用的东西。当然了一定要认真读才行的，你不能走马观花。
另外P1是十进制在分析锁需要转换为16进制，不然你看不明白一串数字。


孙显鹏