Hi!
Not entirely the truth. In huge reporting/OLAP
environments you just can't have that much memory that all sorts are running
entirely in memory. What you should look out for are multipass sorts, the ones
which require reading and writing the same to disk multiple times. So keep the
merge phases 0, but if not got enough memory, keep the merge phases as low as
possible (1).
You can somewhat monitor the merge passes with
following:
SQL> select name, value from v$sysstat where name like
'%workarea%'; -- or use v$sesstat
NAME
VALUE
---------------------------------------------------------------- ---------- workarea memory allocated 0 workarea executions - optimal 1755 workarea executions - onepass 0 workarea executions - multipass 0 Also, if you use parallel queries for your reporting, it
might be so that some parallel slaves do have two sort areas in use, one for
sortin the queried data, other for merging the results with other
slaves.
Read more from http://www.ixora.com.au/newsletter/2000_11.htm#sort_area
Also you could set event 10032 and play with
sort_area_size (reduce it), to see when you start seeing "intermediate runs" in
addition to "initial runs". But more - if you got enough memory, then keep the
size at 100M and don't worry about it - Oracle only allocates that much of
memory for sort that it needs, no memory is wasted.
Tanel.
|
- SORT_AREA_SIZE question Bart Kersteter
- RE: SORT_AREA_SIZE question David Wagoner
- RE: SORT_AREA_SIZE question Nigel Bishop
- RE: SORT_AREA_SIZE question DENNIS WILLIAMS
- RE: SORT_AREA_SIZE question Farnsworth, Dave
- Re: SORT_AREA_SIZE question Darrell Landrum
- RE: SORT_AREA_SIZE question Regis Biassala
- RE: SORT_AREA_SIZE question Rajesh . Rao
- Re: SORT_AREA_SIZE question Tanel Poder
- RE: SORT_AREA_SIZE question Tanel Poder
- RE: SORT_AREA_SIZE question Bart Kersteter
- RE: SORT_AREA_SIZE question DENNIS WILLIAMS
- RE: SORT_AREA_SIZE question John Kanagaraj
- RE: SORT_AREA_SIZE question Rajesh . Rao
- Re: SORT_AREA_SIZE question Tanel Poder
- Re: SORT_AREA_SIZE question Binley Lim
- Re: SORT_AREA_SIZE question Rajesh . Rao