TEMP use and spilling to disk

I've run across this recently. I think in this case, a batch process need more memory that was allotted by the auto work area size policy.

This is the issue we saw, lots of direct read and writes to temp. Slow

SQL>alter session set workarea_size_policy=manual;

SQL>alter session set workarea_size_policy=manual;

SQL> alter session set hash_area_size=1073741824;

after changing the work area size policy to manual and giving a large amount a ram to sort we then saw all the direct reads and writes to temp disappear. Slow

I think there may be times when measures like this are needed. Anyone else run across a case like this?

Comments:

Setting manual workarea sizes is definitely a valid option if you need significant memory for these.

But step 1 should be to look at the accuracy of the optimizer's row estimates compared to the actual rowsource metrics for such operations

Problematic temp space usage happens most often when automatic workareas are poorly sized due to these estimates which are significantly inaccurate (too few rows estimated compared to actuals).

Real-Time SQL Monitoring can help diagnose.

Posted by Dominic Brooks on March 27, 2014 at 03:47 AM PDT #

Thanks Dominic, it actually it did occur to me to look at the cardinality estimates. In this case there was a complex inline view. I think the optimizer had a bit of a time with making a good estimate.

Posted by guest on March 27, 2014 at 04:12 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Senior Solutions Specialist

Search

Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today