A reader, June 07, 2004 - 9:23 am UTC
Thanks Tom,
Please only to confirm, what I know.
In dedicate server mode.
1. When will you increase the value of this parameter in 9i.
and how will you set (please specify views, because I'm not sure if I'm using the best view).
2. which view do you use to see if this parameter had been change (other than v$paramters), the evidence that changeing this paramter, Oracle increases sort area size.
June 07, 2004 - 10:14 am UTC
use statspack -- it has an advisor that
a) tells you what you might want to set it to
b) how it has been doing at its current setting (how many optimal executions, one pass and so on)
A reader, June 07, 2004 - 10:22 am UTC
Thanks
Is sort_area_size really ignored?
yas, August 17, 2005 - 8:58 am UTC
Hi Tom,
I was trying to tune an insert statement and hit a related case. I have these parameters instance level:
NAME VALUE
------------------------------ ----------
hash_area_size 131072
sort_area_size 65536
sort_area_retained_size 0
workarea_size_policy AUTO
pga_aggregate_target 2147483648
I run the insert statement which joins 2 tables and performs lag analytic function and uses direct insert.
The results are:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 0 0 0
Execute 2 529.42 1747.96 381883 261756 738517 2837843
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 529.46 1748.00 381883 261756 738517 2837843
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 161 0.02 0.03
db file scattered read 16936 0.66 139.66
direct path write 5653 1.72 210.11
db file sequential read 4918 0.07 11.54
latch free 17 0.01 0.02
direct path read 68041 2.84 868.24
control file parallel write 121 0.01 0.43
log buffer space 1772 0.63 105.61
log file sync 24 0.56 4.69
log file switch completion 21 0.25 1.23
buffer busy waits 3 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 5.17 5.17
After seeing the direct path operation waits, i set the following:
alter session set sort_area_size=0;
alter session set hash_area_size=0;
Then i run the same insert again (after truncating the inserted table above). Results:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.03 0 0 0 0
Execute 2 467.94 798.42 377805 261678 738529 2838036
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 467.99 798.46 377805 261678 738529 2838036
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 91
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 161 0.00 0.00
db file scattered read 17739 0.55 121.31
db file sequential read 7325 0.06 12.49
direct path write 5263 0.36 100.92
direct path read 67334 0.27 63.71
latch free 10 0.00 0.00
control file parallel write 121 0.01 0.41
log file switch completion 22 0.27 1.34
log buffer space 1851 0.33 116.81
log file sync 24 0.38 4.92
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 9.94 9.94
About half of the previous run time.
It seems automatic workarea size policy does not ignore sort_area_size or hash_area_size. Or am i missing something?
August 17, 2005 - 1:48 pm UTC
workarea size = auto ignores sort/hash area size using dedicated server in 9i and using dedicated OR shared server in 10g.
what is more likely the case here -- the next time you ran, there were less users, you got more ram.