请教高手：实现一个从3300万记录中汇总排序查询，需把sort_area_size设置为多大合适？能否超过shared_pool_size？这有什么规定？（急！！
我要实现一个从3300万人口记录中汇总排序生成按省地县乡村分组的查询，程序如下，已运行2天无结果？查看会话，发现时间大量耗在排序上。不知如何调优？请出手！
create or replace view h_1_1s as
select dzmc as mc,substr(id,1,2) as code,
count(*) as a1,sum(decode(h02,'1',1.0)) as a2,sum(decode(h02,'2',1.0)) as a3,
sum(h031+h032)as a4,sum(h031) as a5,sum(h032) as a6,
sum((h031+h032)*decode(h02,'1',1.0)) as a8,sum(h031*decode(h02,'1',1.0)) as a9,sum(h032*decode(h02,'1',1.0)) as a10,
sum((h031+h032)*decode(h02,'2',1.0)) as a12,sum(h031*decode(h02,'2',1.0)) as a13,sum(h032*decode(h02,'2',1.0)) as a14
from x_dzm ,hu
where dzdm=substr(id,1,2)
group by substr(id,1,2),dzmc;
select * from h_1_1s;
create or replace view h_1_1d as
select dzmc as mc,substr(id,1,4) as code,
count(*) as a1,sum(decode(h02,'1',1.0)) as a2,sum(decode(h02,'2',1.0)) as a3,
sum(h031+h032)as a4,sum(h031) as a5,sum(h032) as a6,
sum((h031+h032)*decode(h02,'1',1.0)) as a8,sum(h031*decode(h02,'1',1.0)) as a9,sum(h032*decode(h02,'1',1.0)) as a10,
sum((h031+h032)*decode(h02,'2',1.0)) as a12,sum(h031*decode(h02,'2',1.0)) as a13,sum(h032*decode(h02,'2',1.0)) as a14
from x_dzm ,hu
where dzdm=substr(id,1,4)
group by substr(id,1,4),dzmc;
select * from h_1_1d;
create or replace view h_1_1x as
select dzmc as mc,substr(id,1,6) as code,
count(*) as a1,sum(decode(h02,'1',1.0)) as a2,sum(decode(h02,'2',1.0)) as a3,
sum(h031+h032)as a4,sum(h031) as a5,sum(h032) as a6,
sum((h031+h032)*decode(h02,'1',1.0)) as a8,sum(h031*decode(h02,'1',1.0)) as a9,sum(h032*decode(h02,'1',1.0)) as a10,
sum((h031+h032)*decode(h02,'2',1.0)) as a12,sum(h031*decode(h02,'2',1.0)) as a13,sum(h032*decode(h02,'2',1.0)) as a14
from x_dzm ,hu
where dzdm=substr(id,1,6)
group by substr(id,1,6),dzmc;
select * from h_1_1x;
create or replace view h_1_1n as
select dzmc as mc,substr(id,1,9) as code,
count(*) as a1,sum(decode(h02,'1',1.0)) as a2,sum(decode(h02,'2',1.0)) as a3,
sum(h031+h032)as a4,sum(h031) as a5,sum(h032) as a6,
sum((h031+h032)*decode(h02,'1',1.0)) as a8,sum(h031*decode(h02,'1',1.0)) as a9,sum(h032*decode(h02,'1',1.0)) as a10,
sum((h031+h032)*decode(h02,'2',1.0)) as a12,sum(h031*decode(h02,'2',1.0)) as a13,sum(h032*decode(h02,'2',1.0)) as a14
from x_dzm ,hu
where dzdm=substr(id,1,9)
group by substr(id,1,9),dzmc;
select * from h_1_1n;
create or replace view h_1_1c as
select dzmc as mc,substr(id,1,12) as code,
count(*) as a1,sum(decode(h02,'1',1.0)) as a2,sum(decode(h02,'2',1.0)) as a3,
sum(h031+h032)as a4,sum(h031) as a5,sum(h032) as a6,
sum((h031+h032)*decode(h02,'1',1.0)) as a8,sum(h031*decode(h02,'1',1.0)) as a9,sum(h032*decode(h02,'1',1.0)) as a10,
sum((h031+h032)*decode(h02,'2',1.0)) as a12,sum(h031*decode(h02,'2',1.0)) as a13,sum(h032*decode(h02,'2',1.0)) as a14
from x_dzm ,hu
where dzdm=substr(id,1,12)
group by substr(id,1,12),dzmc;
select * from h_1_1c;
create or replace view h_1_1 as
(select * from h_1_1s
union all
select * from h_1_1d
union all
select * from h_1_1x
union all
select * from h_1_1n
union all
select * from h_1_1c
);
select * from h_1_1;
create or replace view r_1_1s as
select dzmc as mc,substr(id,1,2) as code,
count(*) as a4,sum(decode(r03,'1',1.0)) as a5,sum(decode(r03,'2',1.0)) as a6,
sum(decode(h02,'1',1.0)) as a8,sum(decode(h02,'1',1.0)*decode(r03,'1',1.0)) as a9,sum(decode(h02,'1',1.0)*decode(r03,'2',1.0)) as a10,
sum(decode(h02,'2',1.0)) as a12,sum(decode(h02,'2',1.0)*decode(r03,'1',1.0)) as a13,sum(decode(h02,'2',1.0)*decode(r03,'2',1.0)) as a14
from x_dzm ,tb_hu_ren
where dzdm=substr(id,1,2)
group by substr(id,1,2),dzmc;
select * from r_1_1s;
create or replace view r_1_1d as
select dzmc as mc,substr(id,1,4) as code,
count(*) as a4,sum(decode(r03,'1',1.0)) as a5,sum(decode(r03,'2',1.0)) as a6,
sum(decode(h02,'1',1.0)) as a8,sum(decode(h02,'1',1.0)*decode(r03,'1',1.0)) as a9,sum(decode(h02,'1',1.0)*decode(r03,'2',1.0)) as a10,
sum(decode(h02,'2',1.0)) as a12,sum(decode(h02,'2',1.0)*decode(r03,'1',1.0)) as a13,sum(decode(h02,'2',1.0)*decode(r03,'2',1.0)) as a14
from x_dzm ,tb_hu_ren
where dzdm=substr(id,1,4)
group by substr(id,1,4),dzmc;
select * from r_1_1d;
create or replace view r_1_1x as
select dzmc as mc,substr(id,1,6) as code,
count(*) as a4,sum(decode(r03,'1',1.0)) as a5,sum(decode(r03,'2',1.0)) as a6,
sum(decode(h02,'1',1.0)) as a8,sum(decode(h02,'1',1.0)*decode(r03,'1',1.0)) as a9,sum(decode(h02,'1',1.0)*decode(r03,'2',1.0)) as a10,
sum(decode(h02,'2',1.0)) as a12,sum(decode(h02,'2',1.0)*decode(r03,'1',1.0)) as a13,sum(decode(h02,'2',1.0)*decode(r03,'2',1.0)) as a14
from x_dzm ,tb_hu_ren
where dzdm=substr(id,1,6)
group by substr(id,1,6),dzmc;
select * from r_1_1x;
create or replace view r_1_1n as
select dzmc as mc,substr(id,1,9) as code,
count(*) as a4,sum(decode(r03,'1',1.0)) as a5,sum(decode(r03,'2',1.0)) as a6,
sum(decode(h02,'1',1.0)) as a8,sum(decode(h02,'1',1.0)*decode(r03,'1',1.0)) as a9,sum(decode(h02,'1',1.0)*decode(r03,'2',1.0)) as a10,
sum(decode(h02,'2',1.0)) as a12,sum(decode(h02,'2',1.0)*decode(r03,'1',1.0)) as a13,sum(decode(h02,'2',1.0)*decode(r03,'2',1.0)) as a14
from x_dzm ,tb_hu_ren
where dzdm=substr(id,1,9)
group by substr(id,1,9),dzmc;
select * from r_1_1n;
create or replace view r_1_1c as
select dzmc as mc,substr(id,1,12) as code,
count(*) as a4,sum(decode(r03,'1',1.0)) as a5,sum(decode(r03,'2',1.0)) as a6,
sum(decode(h02,'1',1.0)) as a8,sum(decode(h02,'1',1.0)*decode(r03,'1',1.0)) as a9,sum(decode(h02,'1',1.0)*decode(r03,'2',1.0)) as a10,
sum(decode(h02,'2',1.0)) as a12,sum(decode(h02,'2',1.0)*decode(r03,'1',1.0)) as a13,sum(decode(h02,'2',1.0)*decode(r03,'2',1.0)) as a14
from x_dzm ,tb_hu_ren
where dzdm=substr(id,1,12)
group by substr(id,1,12),dzmc;
select * from r_1_1c;
create or replace view r_1_1 as
(select * from r_1_1s
union all
select * from r_1_1d
union all
select * from r_1_1x
union all
select * from r_1_1n
union all
select * from r_1_1c
);
select * from r_1_1;
create or replace view a_1_1 as
select a.mc mc,a.code code,a.a1 a1,a.a2 a2,a.a3 a3,b.a4 as a4,b.a5 a5,b.a6 a6,round(b.a5/b.a6*100,2) a7,
b.a8 a8,b.a9 a9,b.a10 a10,round(b.a9/b.a10*100,2) a11,
b.a12 a12,b.a13 a13,b.a14 a14,round(b.a13/b.a14*100,2) a15,round(b.a8/a.a2*100,2) a16
from h_1_1 a,r_1_1 b
where a.code=b.code
order by 2;
select * from a_1_1;
set echo off
set feedback off
set trimout on
set trimspool on
set linesize 400
set pagesize 0
set heading off
spool c:\rknew\a_1_1.txt
select * from a_1_1;
spool off
问题点数：0、回复次数：9Top
1 楼penitent（只取一瓢）回复于 2003-06-02 11:39:31 得分 0
sort_area_size不是关键，关键是你的数据库的设计与程序的设计
sort_area_size太大会要命的。
Top
2 楼BlueskyWide（谈趣者）回复于 2003-06-02 11:46:13 得分 0
数据结构上应采用树型结构。
Top
3 楼enhydraboy（乱舞的浮尘）回复于 2003-06-02 11:53:29 得分 0
你turnning的主要目标有：
sort_area_size,sort_area_retain_size,临时表空间。
Increasing the size of the sort area causes each server process that sorts to allocate more memory. It may affect operating system memory allocation and induce paging and swapping.
If you increase sort area size, consider decreasing the retained size of the sort area, or the size to which Oracle reduces the sort area if its data is not expected to be referenced soon. A smaller retained sort area reduces memory usage but causes additional I/O to write and read data to and from temporary segments on disk.
Because sorts are done in memory if they are smaller than
SORT_AREA_SIZE, you should consider this value when setting extent sizes.
• Select INITIAL and NEXT values as integer multiples of SORT_AREA_SIZE,
allowing an extra block for the segment header.
• Set PCTINCREASE to 0.
To define the sort space needed by the users,
join the V$SESSION and V$SORT_USAGE views to obtain information on the
currently active disk sorts in the instance:
SQL> SELECT s.username, u."USER", u.tablespace, u.contents,
2 u.extents, u.blocks
3 FROM v$session s,v$sort_usage u
4 WHERE s.saddr=u.session_addr
The temporary tablespace should be striped over many disks. If the
temporary tablespace is only striped over two disks with a maximum of 50 I/Os per
second each, then you can only perform 100 I/Os per second. This restriction could
become a problem, making sort operations take a very long time. You can speed up
sort operations fivefold if you stripe the temporary tablespace over ten disks. This
enables 500 I/Os per second.
5 AND u.contents=’TEMPORARY’;Top
4 楼googogo（googogo）回复于 2003-06-02 12:09:23 得分 0
有这么大的表，数据库和主机配置应该不会太小。根据个人做移动项目经验，sort_area_size 和 sort_area_retain_size都设为200M应该可以了。
在不改应用的情况下，调整这两个参数能提高10倍左右性能（一般情况下这两个参数都是几十兆）
alter session set ...Top
5 楼llm06（blacksheep）回复于 2003-06-02 12:36:05 得分 0
你可以考虑以下的问题
• Avoid sort operations
• Size SORT_MULTIBLOCK_READ_COUNT to reduce
the number of I/Os
• Configure TEMPORARY tablespaces
具体多少从来没作过这么大的排序，多试，找到最佳的设置Top
6 楼enhydraboy（乱舞的浮尘）回复于 2003-06-02 15:02:54 得分 0
转贴：
关于排序、sort_area_size、临时表空间
简单陈述一下：
针对每个session,排序首先会使用sort_area_size ，如果不足则会使用临时表空间。但这里面又到底是怎么一个过程呢？下面阐述一下，也许对大家有用处（如果有什么不清楚或者不恰当的地方欢迎大家探讨）
假设sort_area_size = 100k,正好能盛下100条记录进行排序
当排序记录小于等于100条，ok,所有排序在内存中进行，很快
但若超过100条，则会使用临时表空间（利用磁盘进行）
我们选取一个临界值来说明，假设需要排序的记录有10010条
这个时候我们进行的排序会分为101组进行
每读100条进行一次小组排序，然后写入磁盘，第101组只有10条，排序后也写入磁盘
这是进行第二次排序，这次排序将在前100小组里面各抽取一条进行排序。《按照我个人的猜测，应该是排好后每写入一条入磁盘则将该记录所在小组重新抽取一条出来进行排序（这时是有序记录组里面所以很快）》。当这个过程完成后，这时所需要的磁盘空间大约为 实际记录存储空间的2倍（这也是多数书上提到的排序空间大约是记录空间的2倍的原因）
由于还剩下10条记录，于是这10条记录需要跟前面排序的10000条记录进行排序合并，这个代价也是相当大的！
所以，我们通常推荐，假如你需要排序的记录最大为100万条，则sort_area_size最小要能装下1000条，否则如上面的例子，那多余的10条，仅仅10条将会带来巨大的代价！
如果，设置的极度不合理的情况下，排序记录达到了 sort_area_size所能容纳的三次方以上，比如上面例子中排序需要100万记录
那么同样的，重复这个过程，当每一万条记录如上排序后，再如上从这100小组（每组10000条记录）各抽一条进行排序……
在这个过程中，磁盘的消耗和时间的代价大家都应该有个感性认识了
所以，我们建议： sprt_area_size 所能容纳记录数至少大于排序记录数的 平方根
Top
7 楼enhydraboy（乱舞的浮尘）回复于 2003-06-02 15:19:05 得分 0
我基本同意该贴中的大部分说法，仅对其中猜测的部分有不同意见，“这是进行第二次排序，这次排序将在前100小组里面各抽取一条进行排序。《按照我个人的猜测，应该是排好后每写入一条入磁盘则将该记录所在小组重新抽取一条出来进行排序（这时是有序记录组里面所以很快）》。”
按照该作者的猜想，那么我认为没有必要只用100个小组，而是可以用101个小组（尽管只有10条记录）进行排序合并，因为按照这个算法，是为了合并分组的排序成为一个最终的结果，因此，最后一个小组，只有10条，也可以参加整个合并的运算。而在tunning sort的时候，临时表空间的存储参数应该和sort_area_size有一定的关系。
欢迎大家讨论。Top
8 楼zhaozy777（蓝）回复于 2003-06-02 15:46:17 得分 0
我觉得sort_area_size不是很关键，一般一个2G物理内存的小型机，sort_area_size，的值也不会设很大，在2M左右。关键是你在语句中使用的“group by substr(id,1,2),dzmc;”由于数据量庞大很耗资源的。建议你先在字段‘dzmc’和‘substr(id,1,2)’上建立index，这样速度会提高很多。oracle调优，一般先调sql语句、后调参数、最后调硬件。
Top
9 楼ouygg（痞子酷）回复于 2003-06-02 16:35:36 得分 0
我要实现一个从3300万人口记录中汇总排序生成按省地县乡村分组的查询，程序如下，已运行2天无结果？查看会话，发现时间大量耗在排序上。不知如何调优？请出手！
数据磁盘最好是一个地县一个区
本人建议:
第一步:地县记入缓冲区
第二步:按一个一个地县来排序.
第三步:合并每每一个地县
查询时按市地县查:选择那个省,在选那个地市,在查询.就一切ok啦.
一步查处所有地,一个应用程序最多能访问4GB空间,3300万记录加起来大于4GB,没有任何结果.
Top
