ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 排序sort area 内存不足会用到临时表空间

排序sort area 内存不足会用到临时表空间

原创 Linux操作系统 作者:paulyibinyi 时间:2008-02-28 09:10:13 0 删除 编辑

排序:

使用到排序的操作有: create index,order by ,group by ,收集统计信息时,都可能会用临时表空间

  排序操作首先会在sort area 内存中进行排序,一旦sort area 内存不足,则会使用到临时表空间 sort in disk

oracle 9i  目前排序在pga 中用 自动管理

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 25165824

SQL> show parameter work

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

一般把这个参数设大点

以下例子说明sort area 内存不足 用到临时表空间 sort in disk

ALTER SESSION SET  workarea_size_policy =MANUAL     --改为手动

SQL> alter session set sort_area_size=10000000;

SQL> set autotrace traceonly
SQL> select owner,object_name from dba_objects order by object_id;

8655 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     VIEW OF 'DBA_OBJECTS'
   3    2       UNION-ALL
   4    3         FILTER
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   6    5             NESTED LOOPS
   7    6               TABLE ACCESS (FULL) OF 'USER$'
   8    6               INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   9    4           TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
  10    9             INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
  11    3         NESTED LOOPS
  12   11           TABLE ACCESS (FULL) OF 'USER$'
  13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5443  consistent gets
          0  physical reads
          0  redo size
     248492  bytes sent via SQL*Net to client
       6839  bytes received via SQL*Net from client
        578  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       8655  rows processed

SQL> alter session set sort_area_size=100;

Session altered.

SQL> set autotrace traceonly
SQL> select owner,object_name from dba_objects order by object_id;

8655 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     VIEW OF 'DBA_OBJECTS'
   3    2       UNION-ALL
   4    3         FILTER
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   6    5             NESTED LOOPS
   7    6               TABLE ACCESS (FULL) OF 'USER$'
   8    6               INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   9    4           TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
  10    9             INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
  11    3         NESTED LOOPS
  12   11           TABLE ACCESS (FULL) OF 'USER$'
  13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

 


Statistics
----------------------------------------------------------
          7  recursive calls
         34  db block gets
       5445  consistent gets
        185  physical reads           --物理读
          0  redo size
     248492  bytes sent via SQL*Net to client
       6839  bytes received via SQL*Net from client
        578  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
       8655  rows processed

 

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

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    903
  • 访问量
    6525722