oracle版本10.1.0

一条简单的sql，在pl/sql里执行，表里共有20多w条记录：

select * from table (1s)

select * from table order by date_report desc (18s)

加上order by date_report desc后竟要耗费18秒，这样的速度我想没有客户能接受。

开始优化：

首先在表里建立date_report的索引，发现速度没有多大的提升

查了一通资料，通过sqlplus查询得知oracle默认了参数(排序)sort_area_size是64k，是不是太小了，加大到10240000，重启数据库

再试，没反应还是18秒，哪里出现问题了？

show parameter sort_area_size查看，对的，分明是10M的大小；

继续查资料发现oracle里默认将参数workarea_size_policy=auto，此时sort_area_size是不起作用的，于是继续设置参数：

alter system set workarea_size_policy=MANUAL scope=spfile;

再次重启DB，刷新sql，好了速度飞快2s内打开sql。

sort_area_size是当查询需要排序的时候，数据库会话将使用这部分内存进行排序，当内存大小不足的时候，使用临时表空间进行磁盘排序。由于磁盘排序效率和内存排序效率相差好几个数量级，所以这个参数的设置很重要。

当

出现大量排序时的磁盘I/O操作时，可以考虑增加sort_area_size的值。sort_area_size是Oracle用于一次排序所需的最大

内存数，在排序结束但是结果列返回之前，Oracle会释放sort_area_size大小的内存，但是会保留

sort_area_retained_size大小的内存，知道最后一行结果列返回以后，才释放所有的内存。

会导致排序的语句有 Select DISTINCT , MINUS , INTERSECT ,

UNION 和 min()、max()、count() 操作；而不会导致排序的语句有 Update

, 带BETWEEN子句的Select 等等。

监控内存和硬盘的排序比率，最好使它小于 .10，增sort_area_size

Select name, value FROM v$sysstat

Where name IN ('sorts

(memory)', 'sorts

(disk)');

1 sorts (memory) 23851793

2 sorts (disk) 59

Order by提速，与索引关系不大。

因为order by 是对查询结果的排序，添加索引只能优化产生检索结果这个步骤，后面Order by的速度并不能够提高。

如果速度难以忍受，应该考虑对排序区扩容。

扩大sort_area_size的大小。

ORACLE內存管理 之一 ORACLE PGA SGA

轉自:http://sunmoonking.spaces.live.com/blog/cns!E3BD9CBED01777CA!509.entry

MEM主要由兩部分組成

SGA, System Global Area---可以被所以PROCESS訪問。

PGA, Process Global Area—單個PROCESS(thread)私有。

UGA會包括在其中之一里

UGA, User Global Area—SESSION私有，shared server的時候在SGA，dedicated

server的時候在PGA

HASH_AREA_SIZE server process用來在內存裡存hash table的大小.

大致來看,如果workarea_size_policy=manual那麼pga_aggregate_target將會不被使用,而是使用響應的

sort_area_size,hash_area_size等參數.;如果workarea_size_policy=auto的話那麼就會使用

pga_aggregate_target而不使用其他的sort_area_size,hash_area_size等參數.

if ( 數據庫版本 >= 10gR1 )

then

if

(workarea_size_policy=auto)

then

sort area size 無效

else

sort area size 有效

end if

else -- 數據庫版本 < 10gr1

if ( shared server 連接方式

)

then

sort area size 有效

else -- dedicated

server連接方式

if (workarea_size_policy=auto)

then

sort area size 無效

else

sort area size有效

end if

end if

end if

用sort_area_size 512K,1M,1G來看手工與自動管理時的異同，以下是自動管理

SQL> create table wwm as select * from all_objects;

SQL> alter session set sort_area_size=524288;

session xxx memory表示現在這個時刻用了多少，session xxx memory

max 表示最高峰時用了多少

SQL> set autotrace traceonly statistics;

SQL> select * from wwm order by 1,2,3,4

---------------------------------------------------------

0 recursive calls

0 db block gets

420 consistent gets

0 physical reads

0 redo size

2178018 bytes sent via SQL*Net to client

22768 bytes received via SQL*Net from

client

2038 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

30547 rows processed

SQL> alter session set

sort_area_size=1048576; 同上

SQL> alter session set

sort_area_size=1048576000; 同上

自動管理時sort_area_size不起作用.

改成手工管理後

SQL> alter session set workarea_size_policy=manual;

SQL> alter session set sort_area_size =

65536; --64K

SQL> set autotrace traceonly statistics;

SQL> select * from wwm order by 1,2,3,4

----------------------------------------------------------

0 recursive calls