ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle8i中SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的理解

Oracle8i中SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的理解

原创 Linux操作系统 作者:oracle_ace 时间:2008-01-07 21:52:19 0 删除 编辑

在Oracle中,一个process执行一个逻辑复杂的SQL时,可能这个SQL包含了众多的SORT工作,Oracle也只最多最多分配一个SORT_AREA_SIZE大小的空间来进行SORT.但是Oracle可以为我们分配多个SORT_AREA_RETAINED_SIZE来临时保存SORT结束后的结果.

SORT_AREA_SIZE
-------------------------
SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.

Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never occur. Only one memory area of SORT_AREA_SIZE exists for each user process at any time.SORT_AREA_SIZE is also used for inserts and updates to bitmap indexes.

Setting this value appropriately results in a bitmap segment being updated only once for each DML operation, even if more than one row in that segment changes.

Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.

SORT_AREA_RETAINED_SIZE
-----------------------------------------

SORT_AREA_RETAINED_SIZE specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes.
The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory.
This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.

Oracle may allocate multiple sort spaces of this size for each query.
Usually, only one or two sorts occur at one time, even for complex queries.
In some cases, however, additional concurrent sorts are required, and each sort keeps its own memory area.

the SORT_AREA_RETAINED_SIZE is the amount of memory retained when a sort completes and results are being returned.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12361284/viewspace-114504/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    284
  • 访问量
    787258