前日，Production DB N突然反应变慢(RHEL4UP4,9208,64bit,MTS mode)

用top –d 1 察看，发现CPU usage大约在40%~70%间浮动，IOWAIT大约在4%~9%左右。Sys使用量1%~3%，和平常一样。

再用iostat –x 1 看了下各个mount point的IO，发现/u04的IOPS很重，使用率一直在100%。（通过这个可以看出这个朋友的第一个习惯就是通过OS入手，看一下CPU、内存、SWAP、IO，确认数据库的性能出现在那个地方，需要大家对操作系统的性能相关的命令熟悉）。

/u04摆放的是TEMP,UNDO,历史Partition,和几个INDEX的Datafiles（旧系统，目前只用local disks，最近由于资料量成长到较大，10月准备上SAN Storage）。

由于观察到/u04上的IOPS主要是write,考虑是否是产生了大量UNDO,但是由REDO的产生量看，和往常并没差太多。

历史Partition的资料可能性更小，查询Datafile的Physical IO也证明不是它们在造成Write IO。
（确认物理IO有问题以后，马上查找v$filestat、v$tempstat、v$sess_io）

再想是否是那几个Index Datafile,这些file 是因为原本放Index的mount point空间不足以支持到SAN Storage上线而临时作的调整，决定哪些file的时侯我已经充分考虑到他们的IO量在整个系统中的权重。

还是先看下Session wait:

select * from v$session_wait where event not in

(select event from stats$idle_event)

连着刷了十几遍，发现一些端倪，有几个Session的wait events一直是direct path write。
（应该查找v$session_event、v$system_event、v$active_session_history）

这是个在我们的系统中很不常见的Wait events。（这个可是最常见的等待事件之一）

官方的释义：

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.

于是抓取该Session的SQL:（意识不错，找问题SQL）

DELETE FROM table_d

WHERE device IN (SELECT device  （有IN的地方就需要注意）

FROM table_d

WHERE class = 94

AND info = to_char(:b1))

看一下PLAN:

DELETE STATEMENT, GOAL = CHOOSE Cost=111 Cardinality=125 Bytes=4875

DELETE Object owner=TP Object name=table_d

HASH JOIN SEMI Cost=111 Cardinality=125 Bytes=4875 （走的是hash join semi，看上去很正常）

TABLE ACCESS FULL Object owner=TP Object name=table_d Cost=43 Cardinality=61807 Bytes=865298

TABLE ACCESS FULL Object owner=TP Object name=table_d Cost=43 Cardinality=1 Bytes=25


再从v$sort_usage里查看下：

select * from v$sort_usage;（习惯很好，对于一些hash、sort，要关注他们使用temp的情况，特别是伴随着相关的等待事件的时候）

这几个Session果然在用TEMP做HASH

SQL> show parameter hash;

NAME TYPE VALUE

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

hash_area_size integer 2097152

hash_join_enabled boolean TRUE

hash_area_size设定为2M，这在平时确实足够了。（最好是使用pga_aggregate_target）

到这里原因就弄清楚了，HASH_JOIN在PGA里没足够空间，使用了TEMP，引起/u04的IOPS突增，影响到UNDO，拖慢整个系统。

解决的办法：Hash_area_size只能在Session级别修改。

打算检查了下PLAN是否正确：中秋刚Analyze过，但发现table实际大小和Statistics差得比较多，Gather stats后再RUN，发现已经走（这个oracle 10g以前的数据库需要注意的一个地方，系统默认不收集统计信息）

DELETE STATEMENT, GOAL = CHOOSE Cost=93 Cardinality=1 Bytes=39

DELETE Object owner=TP Object name=table_d

HASH JOIN Cost=93 Cardinality=1 Bytes=39

SORT UNIQUE

   TABLE ACCESS FULL Object owner=TP Object name=table_d Cost=43 Cardinality=1 Bytes=25

TABLE ACCESS FULL Object owner=TP Object name=table_d Cost=43 Cardinality=61807 Bytes=865298

检查v$sort_usage 已经没有再用temp进行hash。检查了一下其他Production DB，发现一样的SQL 一样的PLAN，并没发生HASH_AREA不足的情况，table_d的资料量和Production DB N的table_d差得比较大，和Developer人员讨论了下，认为是最近的资料转档可能发生点问题，有该删除的没删除掉，那接下来的事情就交给他们了。

SORT UNIQUE
SORT UNIQUE occurs if a user specifies a DISTINCT clause  or if an
operation requires unique values for the next step

老师我有一点疑问，在使用IN操作符时执行计划可能走Semi-Join也可能走sort unique(oracle 9i) or hash unique(oracle 10g)还可能走IN-List Iterator，他们的区别是什么？

看看论坛的帖子，我还会继续的发一些in、exists的帖子。

我们有时候需要关心数据库中到底有多少sql语句使用了temp进行sort或者hash，他们的pass数是多少。
强烈建议大家非常熟悉v$sql_workarea，这个视图以sql为单位，记录这个sql语句执行过程中出现的使用temp和pga的具体情况。
另外一个视图v$sql_workarea_active，这个视图查询的是当前正在session里面执行的sql使用temp和pga的情况。

xuexixuexi