direct path read temp

direct path read temp
当一个session从磁盘中直接读取buffer到PGA中（而不是从buffer cache中）。当进程从pga中
读取block，这个块还没有从磁盘中读取，进程就发出一个等待呼叫并更新统计信息。这个等待
的次数不一定和读请求数相同。

原因：
1 排序太大，内存放不下，有些排序数据被直接写到磁盘中。然后这些排序数据再被直接读到
内存中。
2 并行扫描数据
3 服务进程处理buffer的速度比I/O返回buffer的快。这说明IO已经过载了。

操作：
如果file_id是temp表空间的对象或者是并行扫描的table。这些操作很可能是数据仓库的大数据
量的操作。

1 排序到磁盘
从V$TEMPSEG_USAGE找到排序操作的sql。然后增大pga内存。

select * from V$statname sn, v$sesstat st
where st.STATISTIC# = sn.STATISTIC#
and sn.name like 'sorts%'

2 全表并行扫描
如果是全表扫描，请检查全表扫描是不是正确的访问路径，如果是对的，请确保IO能足够支持
扫描操作。

3 Hash Join
如果执行计划是Hash Join, 那么可能是HASH_AREA_SIZE太小了。

hashjoin.sql脚本用来执行一个语句，其中有sort操作。

select
    max(t2.name)
from
    t1, t2
where
    t2.id > t1.n1
;

select 1 from dual;

select
    max(t2.name)
from
    t1, t2
where
    t2.id > t1.n1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.91          0          0          0           0
Execute      2      0.00       0.13          0          0          0           0
Fetch        2    254.65    1925.63    2148019       4108          2           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5    254.65    1926.67    2148019       4108          2           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=4108 pr=2148019 pw=1015 time=1925632436 us)
   8660933    8660933    8660933   MERGE JOIN  (cr=4108 pr=2148019 pw=1015 time=1988006822 us cost=5098 size=17186223600 card=8550360)
      4176       4176       4176    INDEX FULL SCAN T1_IX (cr=11 pr=0 pw=0 time=35352 us cost=2237 size=5000000 card=1000000)(object id 79803)
   8660933    8660933    8660933    SORT JOIN (cr=4097 pr=2148019 pw=1015 time=3960776281 us cost=2829 size=8210475 card=4095)
      4095       4095       4095     TABLE ACCESS FULL T2 (cr=4097 pr=4095 pw=0 time=339331 us cost=1112 size=8210475 card=4095)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  cursor: pin S wait on X                         1        0.91          0.91
  SQL*Net message to client                       2        0.00          0.00
  KSV master wait                                 3        0.01          0.02
  ASM file metadata operation                     2        0.03          0.04
  direct path read                               46        0.29          3.24
  Disk file operations I/O                        1        0.00          0.00
  CSS initialization                              2        0.27          0.27
  CSS operation: action                           2        0.03          0.04
  CSS operation: query                            6        0.00          0.00
  direct path write temp                         53        0.09          1.23
  direct path read temp                      319204        0.34       1694.64
  SQL*Net message from client                     2        0.65          1.01
********************************************************************************

hashjoin.sh脚本用来执行四次hashjoin.sql语句。
#!/bin/bash
n=1
while (( $n <= 4 ))
do
        sqlplus "dbs101/dbs101@dbs101" @hashjoin.sql $n &
        n=$(( n+1 ))
done

设置pga_aggregate_target为100m。

SQL> show parameter pga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 100M
 
SQL>

开始运行：
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2
/

       SID OPERATION                           ESIZE        MEM    MAX MEM       PASS      TSIZE
---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
        26 SORT (v2)                             448        169       5716          0       8192
       145 SORT (v2)                             448        169       4708          0       8192
       151 SORT (v2)                             448        169       3889          0       8192
       152 SORT (v2)                             448        169       3889          0       8192

可以看到TSIZE为8192，sort操作在内存不能完成，开始在temporary tablespace中排序。

执行时间为Elapsed: 01:06:52.70

查看v$session_wait视图，发现等待事件为direct path read temp。文件id为201。

设置pga_aggregate_target为140m。

       SID OPERATION                           ESIZE        MEM    MAX MEM       PASS      TSIZE
---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
       151 SORT (v2)                            6648       8173       8173          0
       152 SORT (v2)                            6648       8173       8173          0
       153 SORT (v2)                            6648       8173       8173          0
       154 SORT (v2)                            6648       8173       8173          0
可以看到TSIZE为0，sort操作在内存完成。

执行时间为Elapsed: 00:00:54.86

注释：pga的内存设置对排序，hash join，并行操作有很大的影响。尽可能地让这些操作在
内存中完成，如果实在很大时，1 pass到temp tablespace也行。如果是multiple pass时，
那么性能将会受到很大影响。