ITPub博客

首页 > 数据库 > Oracle > direct path read temp

direct path read temp

原创 Oracle 作者:dbs101 时间:2011-04-20 14:49:20 0 删除 编辑

什么是direct path read temp

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

发生direct path read temp原因

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

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

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


点击(此处)折叠或打开

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



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

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

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

点击(此处)折叠或打开

  1. select
  2.     max(t2.name)
  3. from
  4.     t1, t2
  5. where
  6.     t2.id > t1.n1
  7. ;
  8. select 1 from dual;
  9. select
  10.     max(t2.name)
  11. from
  12.     t1, t2
  13. where
  14.     t2.id > t1.n1
  15. call     count       cpu    elapsed       disk      query    current        rows
  16. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  17. Parse        1      0.00       0.91          0          0          0           0
  18. Execute      2      0.00       0.13          0          0          0           0
  19. Fetch        2    254.65    1925.63    2148019       4108          2           1
  20. ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  21. total        5    254.65    1926.67    2148019       4108          2           1
  22. Misses in library cache during parse: 0
  23. Optimizer mode: ALL_ROWS
  24. Parsing user id: SYS
  25. Number of plan statistics captured: 1
  26. Rows (1st) Rows (avg) Rows (max)  Row Source Operation
  27. ---------- ---------- ----------  ---------------------------------------------------
  28.          1          1          1  SORT AGGREGATE (cr=4108 pr=2148019 pw=1015 time=1925632436 us)
  29.    8660933    8660933    8660933   MERGE JOIN  (cr=4108 pr=2148019 pw=1015 time=1988006822 us cost=5098 size=17186223600 card=8550360)
  30.       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)
  31.    8660933    8660933    8660933    SORT JOIN (cr=4097 pr=2148019 pw=1015 time=3960776281 us cost=2829 size=8210475 card=4095)
  32.       4095       4095       4095     TABLE ACCESS FULL T2 (cr=4097 pr=4095 pw=0 time=339331 us cost=1112 size=8210475 card=4095)

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



********************************************************************************

hashjoin.sh脚本用来执行四次hashjoin.sql语句。


点击(此处)折叠或打开

  1. #!/bin/bash
  2. n=1
  3. while (( $n <= 4 ))
  4. do
  5.         sqlplus "dbs101/dbs101@dbs101" @hashjoin.sql $n &
  6.         n=$(( n+1 ))
  7. done


设置pga_aggregate_target为100m。


点击(此处)折叠或打开

  1. SQL> show parameter pga
  2.  
  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. pga_aggregate_target                 big integer 100M


 
SQL>


开始运行:

点击(此处)折叠或打开

  1. SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
  2.        operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
  3.        trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
  4.        NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
  5. FROM V$SQL_WORKAREA_ACTIVE
  6. ORDER BY 1,2
  7. /
  8.        SID OPERATION                           ESIZE        MEM    MAX MEM       PASS      TSIZE
  9. ---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
  10.         26 SORT (v2)                             448        169       5716          0       8192
  11.        145 SORT (v2)                             448        169       4708          0       8192
  12.        151 SORT (v2)                             448        169       3889          0       8192
  13.        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。


点击(此处)折叠或打开

  1.        SID OPERATION                           ESIZE        MEM    MAX MEM       PASS      TSIZE
  2. ---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
  3.        151 SORT (v2)                            6648       8173       8173          0
  4.        152 SORT (v2)                            6648       8173       8173          0
  5.        153 SORT (v2)                            6648       8173       8173          0
  6.        154 SORT (v2)                            6648       8173       8173          0


可以看到TSIZE为0,sort操作在内存完成。


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

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-692961/,如需转载,请注明出处,否则将追究法律责任。

上一篇: PGA内存管理
下一篇: Direct IO
请登录后发表评论 登录
全部评论

注册时间:2010-12-18

  • 博文量
    92
  • 访问量
    437865