多重子查询/大结果集查询问题:
问题接此帖http://www.itpub.net/273652.html
上次问题通过调整SGA/SQL语句得到优化[最终情况:oracle关闭重起后,程序第一次执行存储过程(需要大量读取物理文件):1500秒,等第2次跑时150秒(数据进入sga区了),完全符合一切oracle教科书的内容!]
现在的问题是上次问题的综合,
起因: 在我们的应用系统(OLTP,其他的事务都比较小且并发用户也比较多)中有一个查询重复的需求,比如企业/市/省/全国各级管理部门,每一级都需要查本级别管辖范围内是否存在重复的人员记录,程序人员采用多重子查询来实现,其中select嵌套5层,子查询的结果集返回数也很巨大,不会小于20W行;
也曾尝试使用临时表,但临时表(insert *** select )单独跑select 还比较快,12秒吧,但是insert的过程比较慢,得20多分钟
btw:临时表这个对象放在哪了呢?对它的操作(尤其是大结果集时)容易产生什么瓶颈呢?临时表后边也是接着一大堆的查询条件,有可能还有优化的,为什么单独执行SELECT比较快,而INSERT就慢呢?而且事务实在巨大,还有临时表嵌套的情况啊!
(后来没法子,又回到了多重子查询的老路上来,明知有问题啊!)
测试环境(以下都是测试机上的运行结果)/真实环境(年底上线,且还需要从SQL SERVER中迁移):
服务器: P4 2.8 内存2G/ IBM H85 POWERPC 4颗/内存 12G
软件:win2k+oracle8.1.7.0/ aix 4.3.3+ oracle8.1.7.4
表现:
oracle关闭重起后,程序第一次执行存储过程(需要大量读取物理文件):1500秒,等第2次跑时150秒(数据进入sga区了)
虽然第2次执行存储过程比较快的,可是每换一个部门,意即:查询条件改变,就需要重新的1500秒!这个很显然不能满足客户的要求,而且管理部门众多,测试的是一个部门的一个查询,实际的情况是N多的府县道台,他们各有各的查询,到时候岂不是歇菜了啊!
附件内容:
1、存储过程(Ora_PersonProc.sql/Ora_PersddonProc.sql)和最终的SELECT语句(XXXXXX.sql);
2、数据库物理文件情况(data.jpg):
TEMP/RBS表空间都有扩展,怀疑有大量的磁盘排序/回滚段请求导致扩展(因为事务请求的数据实在太多了)
3、statspack报告(statspack3_4.txt):
是第2次运行同样的存储过程时的前后SNAP,这个就比较快的(3分钟左右),初步分析了一下:问题语句不言自明,就是这个
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
29,912,794 1 29,912,794.0 100.0 4192360910
select et.* from ( Select enta.*,sd.FName as FPersonTypeName f
rom ( Select ed.*,svs.FSpecialityName,svs.FSpecialityTypeName,
svt.FName As FTechName ,svt.FLevel as FTechLevel, sve.FName as
FEduName,sve.FLevel As FEduLevel from ( select ente.*, ent.FAd
dress,ent.FName as FEnterpriseName from ( select ed3.* from (
还有:
Execute to Parse %: -46.32
是什么意思呢?
各位老大,还给看看有什么别的问题么?
4、分析表时报错信息(analyze_error.txt):ora-00600
谢谢大家!