AWR实战分析之----direct path read temp

àì夳堔傛蜴生んèń 2021-12-20 01:27 347阅读 0赞

http://blog.sina.com.cn/s/blog\_61cd89f60102eej1.html

1.direct path read temp

select TOTAL\_BLOCKS,USED\_BLOCKS from v$sort\_segment;

看看是不是在不段增加  
select \* from v$sysstat where NAME LIKE '%workarea executions%';

看看是不是在不段增加  
select operation\_id,operation\_type,actual\_mem\_used,tempseg\_size,tablespace  
from v$session s,v$sql\_workarea\_active w  
where s.sid=w.sid  
and S.SID=?

http://blog.csdn.net/zq9017197/article/details/7940984

为了排序工作在临时区域读写时，等待direct path read temp、direct path write temp事件。这个等待事件收从oracle 10g起被分类的，oracle 9i为止是通过direct path read、direct path write等待观察的。排序段上的 direct path I/O是在需要排序的数据比排序所分配的PGA内存区大时发生的。因此在排序工作时若大量发生direct path read temp、direct path write temp等待，就可以通过追加分配内存区域而避免等待。

**\[sql\]** [view plain][] [copy][view plain]

1.  SQL> select event\#,name,parameter1,parameter2,parameter3 from v$event\_name where name = 'direct path read temp'; 
2.  
3.  EVENT\# NAME PARAMETER1 PARAMETER2 PARAMETER3 
4.  \---------- ------------------------------ --------------- --------------- --------------- 
5.  195 direct path read temp file number first dba block cnt 
6.  
7.  SQL> select event\#,name,parameter1,parameter2,parameter3 from v$event\_name where name = 'direct path write temp'; 
8.  
9.  EVENT\# NAME PARAMETER1 PARAMETER2 PARAMETER3 
10. \---------- ------------------------------ --------------- --------------- --------------- 
11. 197 direct path write temp file number first dba block cnt

1、应用程序层  
检查需要排序的sql语句是否已经最优化。不必要的排序操作会导致CPU浪费、PGA区域浪费、磁盘I/O浪费。从UNION和UNION ALL的性能差异上可以得知，只靠减少不必要的排序操作，也能解决许多问题。  
2、oracle内存层  
在进程上分配的工作区大小内一次性实现的排序称为One pass sort。与此相反的情况称为Multi pass sort。发生Multi pass sort时，排序工作过程中将排序结果读写到排序段（sort segment）区域，因此发生direct path read temp、direct path write temp等待。如果该等待大量发生，就可以适当提高pga\_aggregate\_target值，以此消除问题。

oracle在调优指南上推荐如下设定pga\_aggregate\_target值。  
OLTP：pga\_aggregate\_target=(total\_mem \* 80%) \* 20%  
OLAP：pga\_aggregate\_target=(total\_mem \* 80%) \* 50%  
上述的意思是，假设OS本身使用20%左右的内存，OLTP系统上使用剩余内存的20%左右，OLAP系统因为排序工作较多，所以使用剩余内存的50%左右。

sample 1;

select a.sql\_text,a.sql\_id,b.event from v$sql a, (select sql\_id,event from DBA\_HIST\_ACTIVE\_SESS\_HISTORY  
where  
sample\_time between to\_timestamp('&begin\_time','yyyy-mm-dd hh24:mi:ss') and to\_timestamp('&end\_time','yyyy-mm-dd hh24:mi:ss'))  
b where a.sql\_id=b.sql\_id and b.event like '%direct path read temp%%'

2018-01-02 02:00:00  
2018-01-02 05:00:00

0ssa391ppxxhx  
crh7vrb1kaqbb  
bu3yqq1424q6c

\#\#\#\#20180110  
direct path read temp  
select TOTAL\_BLOCKS,USED\_BLOCKS from v$sort\_segment;  
看看是不是在不段增加  
select \* from v$sysstat where NAME LIKE '%workarea executions%';  
看看是不是在不段增加  
select operation\_id,operation\_type,actual\_mem\_used,tempseg\_size,tablespace  
from v$session s,v$sql\_workarea\_active w  
where s.sid=w.sid  
and S.SID=?  
能够看到会话使用了工作区或者临时表空间信息。  
prod:  
SQL> select operation\_id,operation\_type,actual\_mem\_used,tempseg\_size,tablespace  
2 from v$session s,v$sql\_workarea\_active w  
3 where s.sid=w.sid  
4 /  
OPERATION\_ID OPERATION\_TYPE ACTUAL\_MEM\_USED TEMPSEG\_SIZE TABLESPACE  
\------------ -------------------------------------------------------------------------------- --------------- ------------ ------------------------------  
b5c92dn9a3ac2 3 HASH-JOIN 1623040  
3 HASH-JOIN 1644544

SQL>  
b5c92dn9a3ac2  
INSERT INTO txs\_summ(stacid,brchcd,trandt,datefq,datecd,tranam,lsblam,onblam) SELECT stacid, brchcd, trandt, datefq, datecd,  
tranam, lsblam, tranam+lsblam AS onblam FROM (SELECT a.stacid, a.deptcd as brchcd ,:1 as trandt,:2 as datefq,:3 as datecd,  
coalesce( (select SUM(CASE WHEN T.AMNTCD = 'D' THEN T.TRANEQ \* -1 WHEN T.AMNTCD = 'C' THEN T.TRANEQ END) from gli\_vchr t where t.PRDUCD  
IN ('D-GOVSE','D-LOCGOV') and t.itemcd like '6%' and t.itemcd not in (select t.prodcd from txp\_type\_map t where t.prodp1 =  
'pricetaxspsm') and t.trandt >= :4 and t.trandt <= :5 and t.acctbr in  
('9600','756028','756034','756004','756040','756046','756020','9091','999807','756003','756055','756062','756027','756030','756015','756057','756060','7560  
26','756014','756012','756058','756056','756059','756010','756031','756038','756009','756018','756021','756029','756013','756042','756045','9085'

DEV:

SQL> select operation\_id,operation\_type,actual\_mem\_used,tempseg\_size,tablespace  
2 from v$session s,v$sql\_workarea\_active w  
3 where s.sid=w.sid  
4 ;

OPERATION\_ID  
\------------  
OPERATION\_TYPE  
\--------------------------------------------------------------------------------  
ACTUAL\_MEM\_USED TEMPSEG\_SIZE TABLESPACE  
\--------------- ------------ ------------------------------  
1  
SORT (v2)  
8111104

SQL> /

STATISTIC\# NAME  
\---------- ----------------------------------------------------------------  
CLASS VALUE STAT\_ID  
\---------- ---------- ----------  
618 workarea executions - optimal  
64 7822 3211650785

619 workarea executions - onepass  
64 8 798730793

620 workarea executions - multipass  
64 0 3804491469

setting:

根据sql 对比，感觉该SQL 消耗了500M pga, 因此调小PGA到400M ,看看该SQL 走的是否是direct path read temp，结论是的走了direct path read temp  
SQL\*Plus: Release 11.2.0.4.0 Production on Wed Jan 10 10:11:45 2018

Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  
SQL> show parameter pga

NAME TYPE VALUE  
\------------------------------------ ----------- ------------------------------  
pga\_aggregate\_target big integer 4G  
SQL> alter system set pga\_aggregate\_target=400M;

System altered.

alter system set "\_pga\_max\_size"=400M;  
\[CBO PARAMETER \_PGA\_MAX\_SIZE CBO initialization parameter "\_pga\_max\_size" with a non-default value of "838860 KB" as per V$SQL\_OPTIMIZER\_ENV.  
Review the correctness of this non-default value "838860 KB" for SQL\_ID crh7vrb1kaqbb.\]

\#\#\#setting2: 怀疑 direct path read temp 是由于FILESYSTEMIO\_OPTIONS 参数引起的，规避方法是调大PGA

FILESYSTEMIO\_OPTIONS = SETALL v ASYNCH Oracle11g on RHEL5 direct path read temp waits  
此问题未回答。

johnlanc63  
Newbie  
johnlanc63 2011-6-28 下午11:26  
In the process of migrating from hosting systems in house to a 3rd party data centre I am trying to understand why some queries are taking longer than our  
existing system. The existing server is running ASM which we are unable to use in the 3rd party datacentre, so this is undoubtedly contributing due to the  
way things like async io is working.

The new system is a LeftHand SAN with masses of spindles and everyting is spread over everything (Network RAID 10 over RAID 5).

I thought the slowness might be due to directio not being used for sorts, so I changed FILESYSTEMIO\_OPTIONS=SETALL from AYSNCH.

The results:  
1) On current system elapsed time: 60 seconds to return result to screen.  
2) On new system FILESYSTEMIO\_OPTIONS=ASYNCH: 90 seconds to return result to screen.  
3) On new system FILESYSTEMIO\_OPTIONS=SETALL: 200 seconds to turn result to screen.

I have included tkprof from 10046 traces for all three scenarios. What I cannot see in the difference between scenarios 2 and 3 is why there is almost a  
full 2 minutes difference between them.

Any ideas anyone?

John

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Instance on ASM:

select count(\*)  
from  
(SELECT CCL\_NUM, PR\_TRF\_ID, PR\_TIM\_ID, SCHEME\_ID, CCL\_TIM\_ID  
from GMS.V\_WP\_CYCLETIMING)

call count cpu elapsed disk query current rows  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
Parse 1 0.03 0.03 0 8 0 0  
Execute 1 0.00 0.00 0 0 0 0  
Fetch 2 50.67 60.14 173417 68671 52 1  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
total 4 50.70 60.18 173417 68679 52 1

Misses in library cache during parse: 1  
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=68671 pr=173417 pw=128388 time=60144927 us)  
1212626 1212626 1212626 VIEW V\_WP\_CYCLETIMING\_RAW (cr=68671 pr=173417 pw=128388 time=59363277 us cost=236044 size=12825032 card=6412516)  
  
1212626 1212626 1212626 SORT GROUP BY (cr=68671 pr=173417 pw=128388 time=58957659 us cost=236044 size=891339724 card=6412516)  
6518626 6518626 6518626 HASH JOIN RIGHT OUTER (cr=68671 pr=45029 pw=0 time=20578830 us cost=38180 size=891339724 card=6412516)  
103 103 103 VIEW V\_TRF\_MAP (cr=2 pr=0 pw=0 time=992 us cost=2 size=3605 card=103)  
103 103 103 WINDOW BUFFER (cr=2 pr=0 pw=0 time=785 us cost=2 size=2575 card=103)  
103 103 103 TABLE ACCESS BY INDEX ROWID T\_TRF\_MAP (cr=2 pr=0 pw=0 time=233 us cost=2 size=2575 card=103)  
103 103 103 INDEX FULL SCAN T\_TRF\_MAP\_PK (cr=1 pr=0 pw=0 time=125 us cost=1 size=0 card=103)(object id 112849)  
6518626 6518626 6518626 HASH JOIN RIGHT OUTER (cr=68669 pr=45029 pw=0 time=16625866 us cost=38139 size=666901664 card=6412516)  
42 42 42 TABLE ACCESS FULL T\_SCHEMEMAPPING (cr=6 pr=0 pw=0 time=81 us cost=3 size=756 card=42)  
6518626 6518626 6518626 HASH JOIN RIGHT OUTER (cr=68663 pr=45029 pw=0 time=12030313 us cost=38097 size=551476376 card=6412516)  
8 8 8 TABLE ACCESS FULL T\_CYCLEFREQUENCY (cr=6 pr=0 pw=0 time=31 us cost=3 size=48 card=8)  
6518626 6518626 6518626 HASH JOIN (cr=68657 pr=45029 pw=0 time=8544563 us cost=38055 size=513001280 card=6412516)  
2319169 2319169 2319169 TABLE ACCESS FULL T\_MEMBERSHIP (cr=23623 pr=0 pw=0 time=1186376 us cost=6598 size=122892849 card=2318733)  
  
6519398 6519398 6519398 TABLE ACCESS FULL T\_EVENT (cr=45034 pr=45029 pw=0 time=1509690 us cost=12379 size=173537721 card=6427323)

Elapsed times include waiting on following events:  
Event waited on Times Max. Wait Total Waited  
\---------------------------------------- Waited ---------- ------------  
SQL\*Net break/reset to client 4 0.00 0.00  
SQL\*Net message to client 4 0.00 0.00  
SQL\*Net message from client 4 12.28 29.78  
asynch descriptor resize 16 0.00 0.00  
direct path read 12 0.00 0.03  
direct path write temp 20 0.01 0.03  
direct path read temp 10193 0.01 9.30

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Instance on LeftHand SAN with FILESYSTEMIO\_OPTIONS=ASYNCH

select count(\*)  
from  
(SELECT CCL\_NUM, PR\_TRF\_ID, PR\_TIM\_ID, SCHEME\_ID, CCL\_TIM\_ID  
from GMS.V\_WP\_CYCLETIMING)

call count cpu elapsed disk query current rows  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
Parse 1 0.11 0.27 40 644 0 0  
Execute 1 0.00 0.00 0 0 0 0  
Fetch 2 54.49 98.64 197404 68948 52 1  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
total 4 54.61 98.91 197444 69592 52 1

Misses in library cache during parse: 1  
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=68948 pr=197404 pw=128470 time=98644230 us)  
1212626 1212626 1212626 VIEW V\_WP\_CYCLETIMING\_RAW (cr=68948 pr=197404 pw=128470 time=98844607 us cost=230412 size=13388690 card=6694345)  
  
1212626 1212626 1212626 SORT GROUP BY (cr=68948 pr=197404 pw=128470 time=98609350 us cost=230412 size=930513955 card=6694345)  
6518585 6518585 6518585 HASH JOIN RIGHT OUTER (cr=68948 pr=68934 pw=0 time=31595734 us cost=32525 size=930513955 card=6694345)  
103 103 103 VIEW V\_TRF\_MAP (cr=2 pr=2 pw=0 time=14750 us cost=2 size=3605 card=103)  
103 103 103 WINDOW BUFFER (cr=2 pr=2 pw=0 time=14540 us cost=2 size=2575 card=103)  
103 103 103 TABLE ACCESS BY INDEX ROWID T\_TRF\_MAP (cr=2 pr=2 pw=0 time=13926 us cost=2 size=2575 card=103)  
103 103 103 INDEX FULL SCAN T\_TRF\_MAP\_PK (cr=1 pr=1 pw=0 time=5471 us cost=1 size=0 card=103)(object id 72130)  
6518585 6518585 6518585 HASH JOIN RIGHT OUTER (cr=68946 pr=68932 pw=0 time=27226814 us cost=32488 size=696211880 card=6694345)  
42 42 42 TABLE ACCESS FULL T\_SCHEMEMAPPING (cr=6 pr=6 pw=0 time=17364 us cost=3 size=756 card=42)  
6518585 6518585 6518585 HASH JOIN RIGHT OUTER (cr=68940 pr=68926 pw=0 time=22297207 us cost=32449 size=575713670 card=6694345)  
8 8 8 TABLE ACCESS FULL T\_CYCLEFREQUENCY (cr=6 pr=6 pw=0 time=52914 us cost=3 size=48 card=8)  
6518585 6518585 6518585 HASH JOIN (cr=68934 pr=68920 pw=0 time=18325600 us cost=32411 size=535547600 card=6694345)  
2319128 2319128 2319128 TABLE ACCESS FULL T\_MEMBERSHIP (cr=23368 pr=23358 pw=0 time=1869684 us cost=5751 size=122892849 card=2318733)  
  
6519357 6519357 6519357 TABLE ACCESS FULL T\_EVENT (cr=45566 pr=45562 pw=0 time=1721636 us cost=11090 size=173399751 card=6422213)

Elapsed times include waiting on following events:  
Event waited on Times Max. Wait Total Waited  
\---------------------------------------- Waited ---------- ------------  
SQL\*Net message to client 2 0.00 0.00  
db file sequential read 8 0.04 0.07  
asynch descriptor resize 16 0.00 0.00  
db file scattered read 202 0.23 7.76  
SQL\*Net message from client 2 0.00 0.00  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Instance on LeftHand SAN with FILESYSTEMIO\_OPTIONS=SETALL

select count(\*)  
from  
(SELECT CCL\_NUM, PR\_TRF\_ID, PR\_TIM\_ID, SCHEME\_ID, CCL\_TIM\_ID  
from GMS.V\_WP\_CYCLETIMING)

call count cpu elapsed disk query current rows  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
Parse 1 0.10 0.37 41 644 0 0  
Execute 1 0.00 0.00 0 0 0 0  
Fetch 1 51.36 94.60 140435 68948 52 0  
\------- ------ -------- ---------- ---------- ---------- ---------- ----------  
total 3 51.46 94.98 140476 69592 52 0

Misses in library cache during parse: 1  
Optimizer mode: ALL\_ROWS  
Parsing user id: SYS  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation  
\---------- ---------- ---------- ---------------------------------------------------  
0 0 0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=25 us)  
108509 108509 108509 VIEW V\_WP\_CYCLETIMING\_RAW (cr=68948 pr=140428 pw=128470 time=97808970 us cost=230412 size=13388690 card=6694345)  
  
108509 108509 108509 SORT GROUP BY (cr=68948 pr=140428 pw=128470 time=97769557 us cost=230412 size=930513955 card=6694345)  
6518585 6518585 6518585 HASH JOIN RIGHT OUTER (cr=68948 pr=68934 pw=0 time=29068316 us cost=32525 size=930513955 card=6694345)  
103 103 103 VIEW V\_TRF\_MAP (cr=2 pr=2 pw=0 time=14016 us cost=2 size=3605 card=103)  
103 103 103 WINDOW BUFFER (cr=2 pr=2 pw=0 time=13806 us cost=2 size=2575 card=103)  
103 103 103 TABLE ACCESS BY INDEX ROWID T\_TRF\_MAP (cr=2 pr=2 pw=0 time=13346 us cost=2 size=2575 card=103)  
103 103 103 INDEX FULL SCAN T\_TRF\_MAP\_PK (cr=1 pr=1 pw=0 time=5791 us cost=1 size=0 card=103)(object id 72130)  
6518585 6518585 6518585 HASH JOIN RIGHT OUTER (cr=68946 pr=68932 pw=0 time=24707108 us cost=32488 size=696211880 card=6694345)  
42 42 42 TABLE ACCESS FULL T\_SCHEMEMAPPING (cr=6 pr=6 pw=0 time=49970 us cost=3 size=756 card=42)  
6518585 6518585 6518585 HASH JOIN RIGHT OUTER (cr=68940 pr=68926 pw=0 time=19732952 us cost=32449 size=575713670 card=6694345)  
8 8 8 TABLE ACCESS FULL T\_CYCLEFREQUENCY (cr=6 pr=6 pw=0 time=49917 us cost=3 size=48 card=8)  
6518585 6518585 6518585 HASH JOIN (cr=68934 pr=68920 pw=0 time=15793861 us cost=32411 size=535547600 card=6694345)  
2319128 2319128 2319128 TABLE ACCESS FULL T\_MEMBERSHIP (cr=23368 pr=23358 pw=0 time=1794753 us cost=5751 size=122892849 card=2318733)  
  
6519357 6519357 6519357 TABLE ACCESS FULL T\_EVENT (cr=45566 pr=45562 pw=0 time=1637949 us cost=11090 size=173399751 card=6422213)

Elapsed times include waiting on following events:  
Event waited on Times Max. Wait Total Waited  
\---------------------------------------- Waited ---------- ------------  
SQL\*Net message to client 1 0.00 0.00  
db file sequential read 8 0.04 0.12  
asynch descriptor resize 15 0.00 0.00  
db file scattered read 202 0.10 5.52  
direct path read 14 0.05 0.33  
direct path write temp 2769 0.24 16.12  
direct path read temp 1362 0.29 21.26  
SQL\*Net break/reset to client 1 0.00 0.00  
SQL\*Net message from client 1 7.59 7.59  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

我有同样的问题 显示 0 喜欢(0)  
1102 查看 标签： 回复  
平均用户评级: 无评分 (0 评级)平均用户评级无评分(0 评级)  
您的评级： 评级 差（1，最高值为 5）评级 中下（2，最高值为 5）评级 中等（3，最高值为 5）评级 中上（4，最高值为 5）评级 优（5，最高值为 5）  
1有用  
1. Re: FILESYSTEMIO\_OPTIONS = SETALL v ASYNCH Oracle11g on RHEL5 direct path read temp waits  
User774969-Oracle  
Explorer  
User774969-Oracle 2011-6-28 下午11:26 （回复 johnlanc63）  
Hi,  
The execution plan looks same in all the cases. Only the elapsed time is different every time and not the CPU time.

In "Instance on LeftHand SAN with FILESYSTEMIO\_OPTIONS=SETALL", the elapsed time is increased due to extensive sorting which  
went to disk due to lack of PGA memory or inadequate pga settings.  
  
You may need to increase the pga\_aggregate\_target and \_pga\_max\_size.  
Refer.Note.453540.1.  
  
Regards  
Bala  
喜爱 显示 0 喜欢(0) 回复 操作  
2. Re: FILESYSTEMIO\_OPTIONS = SETALL v ASYNCH Oracle11g on RHEL5 direct path read temp waits  
Tom321  
Grand Master  
Tom321 2015-6-2 上午10:47 （回复 johnlanc63）  
Hi,

you can run the 10046 trace with level 8 to get details on the time each fetch takes. Compare the fetch time, multiblock count, io type, etc for your  
different io strategy.

e.g.:

WAIT \#18446744071523675328: nam='direct path read' ela= 4044 file number=1 first dba=225152 block cnt=128 obj\#=107558 tim=25471670157341

What filesystem are you using? Is it enabled for async and direct io? Is async io enabled on os level?

Oracle Linux 5 - Filesystem & I/O Type Supportability (Doc ID 1601765.1)

How To Check if Asynchronous I/O is Working On Linux (Doc ID 237299.1)

Regards

Thomas

\#\#\#\#\#\#\#\#\#\#\#\#\#\#setting 3 pga 建议值是 5倍work area size，可以考虑加大生产的pGA

With the automatic PGA management mode, you can control the size for the work area sizes, to be able perform big sort operations or hash joins in memory  
and avoid messages like:

kxhfWrite: hash-join is spilling to disk

You have to set the following parameters to proper values:

1. PGA\_AGGREGATE\_TARGET

should be set to five times the desired work area size

2. \_PGA\_MAX\_SIZE

should be set in minimum of twice the desired work area size. The default value is 200Mb.

3. \_SMM\_MAX\_SIZE

normally this parameter is not needed but maybe under certain circumstances  
if set it should be equal to the desired work area size (in kb !)

Example:  
If you like to use a sort area size of 1GB for a special operation (e.g for the creating of an index on a large table) you could set the values as follows:

PGA\_AGGREGATE TARGET = 5G  
"\_PGA\_MAX\_SIZE" = 2G  
"\_SMM\_MAX\_SIZE" = 1048576

Dynamically, the values must be changed for the full instance using the "alter system" command. It is not allowed changes the above parameters for one  
session.

Please note that there is an internal limitation that a single workarea size is limited to 2GB:

\_PGA\_MAX\_SIZE is set up to 2GB (1GB limit for 32-bit) per bug 3946308

The fix for Bug 17951233 doesn't allow a work area to obtain more than 2 GBs. It simply prevents the session from failing with ORA-00600 \[kcblin\_3\].

Also please be careful that the above changes are very dangerous because the values affect all sessions in the database. If you need to have granular  
control of the work area for a single session, then it is better to set at the session level WORKAREA\_SIZE\_POLICY=MANUAL and \*\_AREA\_SIZE as high as needed.

NOTE:  
\#\#\#step 5

模拟linux 删除linux 缓存，在执行，这说明，生产环境的数据库是没有用到操作系统的文件系统缓存， 而测试环境是用到了操作系统文件系统的缓存。已经提交SR ，增大  
pga\_aggregate\_target size 从4G 到6G, 然后观察系统

\[root@kfdb~\]\# echo 3 |sudo tee /proc/sys/vm/drop\_caches  
3

或者  
\[root@kfdb~\]\# echo 1 |sudo tee /proc/sys/vm/drop\_caches  
3

执行时间：  
MERGE INTO SUNTX.RB\_CBV\_COM\_CUPR\_HIS T USING (SELECT \* FROM (SELECT T.\*, ROW\_NUMBER() OVER(PARTITION BY T.CUPRCD, T.BEGNDT ORDER BY T.CUPRCD, T.BEGNDT  
DESC) ROW\_NUM FROM SUNTX.RB\_CBV\_COM\_CUPR T) WHERE ROW\_NUM = 1) L ON (T.CUPRCD = L.CUPRCD) WHEN MATCHED THEN UPDATE SET T.CUPRNA = NVL(L.CUPRNA, T.CUPRNA),  
T.EASYCD = NVL(L.EASYCD, T.EASYCD), T.PYTXNO = NVL(L.PYTXNO, T.PYTXNO), T.ADDRES = NVL(L.ADDRES, T.ADDRES), T.TELENO = NVL(L.TELENO, T.TELENO), T.BANKNA =  
NVL(L.BANKNA, T.BANKNA), T.ACCTNO = NVL(L.ACCTNO, T.ACCTNO), T.EMAILO = NVL(L.EMAILO, T.EMAILO), T.SMRYTX = NVL(L.SMRYTX, T.SMRYTX), T.BEGNDT = NVL  
(L.BEGNDT, T.BEGNDT), T.ENDDDT = NVL(L.ENDDDT, T.ENDDDT), T.STATUS = NVL(L.STATUS, T.STATUS), T.BODYTP = NVL(L.BODYTP, T.BODYTP), T.CONTTG = NVL(L.CONTTG,  
T.CONTTG), T.ADDREE = NVL(L.ADDREE, T.ADDREE), T.RECVAD = NVL(L.RECVAD, T.RECVAD), T.RECVPH = NVL(L.RECVPH, T.RECVPH), T.CUPREN = NVL(L.CUPREN, T.CUPREN),  
T.ORVATSI = NVL(L.ORVATSI, T.ORVATSI), T.CUPRTP = NVL(L.CUPRTP, T.CUPRTP), T.IDNUMB = NVL(L.IDNUMB, T.IDNUMB), T.INVATO = NVL(L.INVATO, T.INVATO),  
T.SYSTEMID = NVL(L.SYSTEMID, T.SYSTEMID) WHEN NOT MATCHED THEN INSERT (CUPRCD, CUPRNA, EASYCD, PYTXNO, ADDRES, TELENO, BANKNA, ACCTNO, EMAILO, SMRYTX,  
BEGNDT, ENDDDT, STATUS, BODYTP, CONTTG, ADDREE, RECVAD, RECVPH, CUPREN, ORVATSI, CUPRTP, IDNUMB, INVATO, SYSTEMID) VALUES (L.CUPRCD, L.CUPRNA, L.EASYCD,  
L.PYTXNO, L.ADDRES, L.TELENO, L.BANKNA, L.ACCTNO, L.EMAILO, L.SMRYTX, L.BEGNDT, L.ENDDDT, L.STATUS, L.BODYTP, L.CONTTG, L.ADDREE, L.RECVAD, L.RECVPH,  
L.CUPREN, L.ORVATSI, L.CUPRTP, L.IDNUMB, L.INVATO, L.SYSTEMID)

从1分钟增大到3分钟

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

\#\#\#\#\#发现1：  
SQL>set pagesize 999  
SQL>/

TBS\_NAME TOTAL\_GB ALLOTED\_GB USED\_GB PCT\_USED  
\------------------------------------------------------------ ---------- ---------- ----------  
\------------------------------------------------------------------------------------  
TEMP 32 4 .01 .03 %  
SUNTX\_TEMP 42 32.8145 0 0 %

dev:

select count(\*) from SUNTX.RB\_CBV\_COM\_CUPR;  
COUNT(\*)  
\----------  
3886

prod:  
SQL> select count(\*) from SUNTX.RB\_CBV\_COM\_CUPR;

COUNT(\*)  
\----------  
2553181

RB\_CBV\_COM\_CUPR

转载于:https://www.cnblogs.com/feiyun8616/p/8206116.html

[view plain]: http://blog.csdn.net/zq9017197/article/details/7940984#