在出报表时，其它应用不能执行
Statspack查看报告
Event Waits Wait Time (cs) % Total Wt Time
-------------------------------------------- ------------ ------------ -------
direct path read 54,393 29,687 47.85
direct path write 34,272 28,559 46.03
log file parallel write 1,723 1,314 2.12
log file sync 1,326 1,215 1.96
control file parallel write 373 416 .67
运行了不到20分钟， direct path read/write就等待了 60000/100/60=10分钟。成为瓶颈。
查direct path read/write:
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 files sequential read').
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
Causes
This happens in the following situations:
• The sorts are too large to fit in memory and go to disk. If a sort does not fit into memory, then some of the sort data is written out directly to disk. This data is later read back in, using direct reads.
• Parallel slaves are used for scanning data.
• The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.
Actions
The file_id shows if the reads are for an object in TEMP tablespace (sorts to disk) or full table scans by parallel slaves. This is the biggest wait for large data warehouse sites. However, if the workload is not a DSS workload, then examine why this is happening.
Sorts to Disk
Examine the SQL statement currently being run by the session experiencing waits to see what is causing the sorts. Query V$TEMPSEG_USAGE to find the SQL statement that is generating the sort. Also query the statistics from V$SESSTAT for the session to determine the size of the sort. See if it is possible to reduce the sorting by tuning the SQL statement. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the SORT_AREA_SIZE for the system (if the sorts are not too big) or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET.
查看目前sga配置：
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 734,003,200
Fixed Size 75,804
Redo Buffers 77,824
Variable Size 196,767,744
----------------
sum 930,924,572
服务器内存有3G。可以适当调大sort_area_size.
把sort_area_size 调整为512k.
ALTER SYSTEM ... DEFERRED
Alter system set sort_area_size=524288;
调整后，重启，再运行此报告。用sysevent查看：
Avg
Total Total Time Ela Time % of
Event Name Waits Timeouts (in Hours) (Secs) Concern
------------------------------ ---------------- --------------- ----------- ----------- -------
CPU time 0.10 68.21
db file sequential read 16,742 0 0.02 0.01 15.88
direct read, direct write 已经消失。
初步判断问题解决。
--------