temp表空间不足和direct path read/write temp事件

五一假期第一天上午不断收到核心数据库报警提示temp表空间不足。

关键字：temp表空间不足 direct path read/write temp事件 笛卡尔积

查询temp表空间使用情况，按占用temp从高到低排序

select v.USERNAME, v.SQL_ID, v.BLOCKS, s.SQL_TEXT, j.*

from v$tempseg_usage v,

v$sqlarea s,

(select * from v$session i where i.STATUS = 'ACTIVE') j

where v.SQL_ID = s.SQL_ID

and v.SESSION_ADDR = j.saddr

order by v.blocks desc;

发现大量类似SQL

select count(1)

from (select *

from ccic.prpjrecdetail

union all

select * from ccic.prpjrecdetailhis)

where receiptno = 'SDDK201214012155000168';

执行计划如下：

---------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | VIEW | | 2 | 26 | 2 (0)| 00:00:01 |

| 3 | UNION-ALL | | | | | |

|* 4 | INDEX RANGE SCAN| IND_JRECDETAIL_RECEIPTNO | 1 | 23 | 1 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN| IND_JRECDETAILHIS_RECEIPTNO | 1 | 23 | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("PRPJRECDETAIL"."RECEIPTNO"='SDDK201214012155000168')

5 - access("PRPJRECDETAILHIS"."RECEIPTNO"='SDDK201214012155000168')

这些类似的SQL占用了大量的temp空间，超过150000块（1.2G）的就有5个，temp总大小16G。这些sql的问题是没有绑定变量，似乎不应该是造成频繁报警的原因。为了确定造成报警的原因，还是应该找到那个不断申请temp空间但又得不到满足的SQL。

查询等待事件

select * from v$session v where v.STATUS='ACTIVE' and v.WAIT_CLASS<>'Idle';

发现direct path read/write temp事件，该事件对应的SQL

select a.riskcode,

/*省略部分*/

b.comcode as comcodey

/*省略部分*/

from (select *

from ccic.prpjpayrec

union all

select * from ccic.prpjrefrec) a,

ccic.PrpCmain b

where a.receiptno = :1;

执行计划如下

-------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

-------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 93M| 16G| 1263K (2)| 04:12:37 | | |

| 1 | MERGE JOIN CARTESIAN | | 93M| 16G| 1263K (2)| 04:12:37 | | |

| 2 | VIEW | | 2 | 336 | 2 (0)| 00:00:01 | | |

| 3 | UNION-ALL | | | | | | | |

| 4 | TABLE ACCESS BY INDEX ROWID| PRPJPAYREC | 1 | 167 | 1 (0)| 00:00:01 | | |

|* 5 | INDEX UNIQUE SCAN | PK_JPAYREC | 1 | | 1 (0)| 00:00:01 | | |

| 6 | TABLE ACCESS BY INDEX ROWID| PRPJREFREC | 1 | 160 | 1 (0)| 00:00:01 | | |

|* 7 | INDEX UNIQUE SCAN | PK_JREFREC | 1 | | 1 (0)| 00:00:01 | | |

| 8 | BUFFER SORT | | 46M| 977M| 1263K (2)| 04:12:37 | | |

| 9 | PARTITION RANGE ALL | | 46M| 977M| 631K (2)| 02:06:19 | 1 | 38 |

| 10 | PARTITION LIST ALL | | 46M| 977M| 631K (2)| 02:06:19 | 1 | 22 |

| 11 | TABLE ACCESS FULL | PRPCMAIN | 46M| 977M| 631K (2)| 02:06:19 | 1 | 836 |

-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("PRPJPAYREC"."RECEIPTNO"=:1)

7 - access("PRPJREFREC"."RECEIPTNO"=:1)

这个sql的问题是a，b表没有关联，造成笛卡尔积（执行计划中的MERGE JOIN CARTESIAN），b表是8KW数据量10G多的大表，做笛卡尔积的资源需求无法满足。这个sql是导致频繁报错的罪魁。

两个sql修改如下：

增加表关联

select a.riskcode,

/*省略部分*/

b.comcode as comcodey

/*省略部分*/

from (select *

from ccic.prpjpayrec

union all

select * from ccic.prpjrefrec) a,

ccic.PrpCmain b

where a.policyno = b.policyno

and a.receiptno = :1;

执行计划

------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 426 | 4 (0)| 00:00:01 | | |

| 1 | NESTED LOOPS | | 2 | 426 | 4 (0)| 00:00:01 | | |

| 2 | VIEW | | 2 | 336 | 2 (0)| 00:00:01 | | |

| 3 | UNION-ALL | | | | | | | |

| 4 | TABLE ACCESS BY INDEX ROWID | PRPJPAYREC | 1 | 167 | 1 (0)| 00:00:01 | | |

|* 5 | INDEX UNIQUE SCAN | PK_JPAYREC | 1 | | 1 (0)| 00:00:01 | | |

| 6 | TABLE ACCESS BY INDEX ROWID | PRPJREFREC | 1 | 160 | 1 (0)| 00:00:01 | | |

|* 7 | INDEX UNIQUE SCAN | PK_JREFREC | 1 | | 1 (0)| 00:00:01 | | |

| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| PRPCMAIN | 1 | 45 | 1 (0)| 00:00:01 | ROWID | ROWID |

|* 9 | INDEX UNIQUE SCAN | PK_CMAIN | 1 | | 1 (0)| 00:00:01 | | |

------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("PRPJPAYREC"."RECEIPTNO"=:1)

7 - access("PRPJREFREC"."RECEIPTNO"=:1)

9 - access("A"."POLICYNO"="B"."POLICYNO")

使用绑定变量

select count(1)

from (select *

from ccic.prpjrecdetail

where receiptno = '"+iReceiptno+"'

union all

select *

from ccic.prpjrecdetailhis

where receiptno = '"+iReceiptno+"');

附1

使用temp表空间的操作

1.临时表的操作

2.Hash Join

3.Sort-Merge joins

4.CREATE INDEX

5.ANALYZE

6.Select DISTINCT

7.ORDER BY

8.GROUP BY

9.UNION

10.INTERSECT

11.MINUS

12.etc.

附2

官方文档对direct path read/write temp事件的解释

direct path read and direct path read temp

When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.

If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks already existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).

Check the following V$SESSION_WAIT parameter columns:

•P1 - File_id for the read call

•P2 - Start block_id for the read call

•P3 - Number of blocks in the read call

direct path write and direct path write temp

When a process is writing buffers directly from PGA (as opposed to the DBWR writing them from the buffer cache), the process waits on this event for the write call to complete. Operations that could perform direct path writes include when a sort goes to disk, during parallel DML operations, direct-path INSERTs, parallel create table as select, and some LOB operations.

Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session waits if it has processed all buffers in the PGA and is unable to continue work until an I/O request completes.

Check the following V$SESSION_WAIT parameter columns:

•P1 - File_id for the write call

•P2 - Start block_id for the write call

•P3 - Number of blocks in the write call

后记

昨日我失去了自己的孩子，谨以此文悼之，愿你的灵魂步入天堂。望工作能分散我的注意力，一切还是要向前看吧。

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