PGA/UGA的问题

fusnow · 发表于 2006-10-10 22:52

不知道你说sort_area_size的根据是什么，不过我认为sort area是在UGA，也就是在dedicated servers下是在PGA里，在shared servers下是在SGA里。

根据是concept：

The private SQL area of a cursor is itself divided into two areas whose lifetimes are
different:
The persistent area, which contains, for example, bind information. It is freed
only when the cursor is closed.
The run-time area, which is freed when the execution is terminated.
Oracle creates the runtime area as the first step of an execute request. For INSERT,
UPDATE, and DELETE statements, Oracle frees the runtime area after the statement
has been run. For queries, Oracle frees the runtime area only after all rows are
fetched or the query is canceled.
The location of a private SQL area depends on the type of connection established for
a session. If a session is connected through a dedicated server, private SQL areas are
located in the server process’s PGA. However, if a session is connected through a
shared server, part of the private SQL area is kept in the SGA.

------这一段说private SQL area包括runtime area，private SQL area是在dedicated servers下是在PGA里，在shared servers下是在SGA里。

SQL Work Areas
For complex queries (for example, decision-support queries), a big portion of the
runtime area is dedicated to work areas allocated by memory-intensive operators
such as the following:
Sort-based operators (order by, group-by, rollup, window function)
Hash-join
Bitmap merge
Bitmap create

sort, hash, bitmap merge/create都是runtime area的一部分

lilyzhang918 · 发表于 2006-10-11 13:42

最初由 fusnow 发布
[B]不知道你说sort_area_size的根据是什么，不过我认为sort area是在UGA，也就是在dedicated servers下是在PGA里，在shared servers下是在SGA里。

[/B]

在这一点上，我完全同意飞雪兄的看法。

另有一个问题请教飞雪兄，不知在MTS下PGA是由什么进程allocate和管理呢？是shared server process吗？

SONGHEFEI · 发表于 2006-10-11 13:49

当oracle 进行sort area 分配的时候,小于sort_area_retained_size的部分是在UGA(l arge pool )中分配, 而大于sort_area_retained_size 小于sort_area_size 的部分是在PGA中分配的!当然默认情况下sort_area_tetained_size=sort_area_size 所以都在UGA中分配了!

fusnow · 发表于 2006-10-11 19:59

最初由 SONGHEFEI 发布
[B]当oracle 进行sort area 分配的时候,小于sort_area_retained_size的部分是在UGA(l arge pool )中分配, 而大于sort_area_retained_size 小于sort_area_size 的部分是在PGA中分配的!当然默认情况下sort_area_tetained_size=sort_area_size 所以都在UGA中分配了! [/B]

原来是这样。
一直都是用9i，看来8i很多东西我还是不知道。