ORACLE 查询的速度很快，把查询的数据插入到表中就很慢，什么原因？

Question

如题，一条通过GROUP BY 统计汇总的查询语句得到的数据量是29万行，该查询速度是8秒多，但是把这个查询结果插入到一个新表就很慢，新表无论是NOLOGGING 还是CREATE TABLE TABLENAME AS SELECT......或者是APPEND的速度都是龟速，两三个小时都完成不了（新表什么都没有建）这时什么问题导致的？

Answer 1

而且我刚才又测试了一下，把另外一个表的数据量为30多列20万行的数通过语句CREATE TABLE AS 到另外一张表用时才87秒，我怀疑跟SELECT 语句的类型有关，如果CREATE TABLE AS或INSERT 后面是直接SELECT * FROM TABLE WHERE....就很快，
而后面的SELECT 的是VIEW或者有子查询、GROUP BY 、SUM、COUNT等的就会很慢，实际情况是不是这样呢？

Answer 2

这种问题估计原因会很多
(1) GROUP BY 统计汇总的查询语句得到的数据量是29万行，该查询速度是8秒多
--------------------------------------------------------------------
执行这条语句之前，是否别人已经执行过该语句。因为Oracle会有SQL指令缓存。相同的SQL不一定真的去表中查询。(2)通常情况下,几十万的数据量，如果sum、group by时间长，比较常见的原因是临时表空间不够或者SGA区不够之类。但两三个钟，实在有点夸张。(3)insert的效率锁资源、回滚段、索引数目、表空间等资源相关。但如果两三行记录，不会到两三个钟的。(4)个人认为如下方面可能嫌疑最大
   (a)锁资源方面问题。LZ看看执行该动作的session当前是什么状态，是否是在等待什么资源，或者其它状态异常？
   (b)如果是由程序执行的，则更偏向于程序存在bug。
   (c)检查与该select相关的对象是否被死锁。
   其中a是关键，LZ需要看看慢的时候，该session的状态如何，用toad或者oem等工具都可以看到。

Answer 3

(3)insert的效率和锁资源、回滚段、索引数目、表空间等资源相关。但如果两三行记录，不会到两三个钟的。

Answer 4

不知道你是不是在PL/SQL developer 执行的。
默认只是提取前10行吧。你试着提取全部，看时间是不是还是8秒

Answer 5

不是两三行，是3列29万行的数据，至于争用，当前数据库只有我一个人用，新表是没有索引的，有没有跟系统内部争用就不知道，还没有检查过，不过就像上面提到那个例子
我又通过CREATE TABLE TABLENAME AS...语句把系统表(应该是视图吧)tab拷到新的表中，有20万行记录（当前用户下表的数量是有这么多），运行了30多分钟还没有运行就人为停掉了，由此我才会猜测：
       如果CREATE TABLE AS或INSERT 后面是直接SELECT * FROM TABLE WHERE....就很快，而后面的SELECT 的是VIEW或者有子查询、GROUP BY 、SUM、COUNT等的就会很慢

Answer 6

每次执行前我都会先运行这两句话的：
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
临时表空间不够那更不可能，在执行的时候临时表空间都没有增长过，用SQL语句监控也没有使用到，而且该用户的使用自己独有的临时表空间，跟系统临时表空间TEMP没关系

Answer 7

这个是PL/SQL developer执行的，提取全部29万行的数据都不需要1分钟

Answer 8

还是强调，想办法弄清楚工作慢时的该session情况。只有这个才是可以提供真实的判断依据,在这里凭空猜，或者从你提供的情况来看，似乎是不应该出现这种问题的。如果CREATE TABLE AS或INSERT 后面是直接SELECT * FROM TABLE WHERE....就很快，而后面的SELECT 的是VIEW或者有子查询、GROUP BY 、SUM、COUNT等的就会很慢
--------------------------------------------------------------------------------------------
在百万级的表处理中，即便是普通PC上一般也不会超过10分钟。还有一点，是不是你的表不是本地的表，而是通过database link创建的同义词之类，如果这样就有可能和物理表数据库实例的资源以及网络情况相关。

Answer 9

除非你用OEM或者用DBA权限去查过表空间的使用情况，单这样判断，貌似理由不够。出现问题还是需要老老实实的查。

Answer 10

用oem或者脚本跟踪下，提取下统计信息，根据统计信息分析吧，这样猜也不能从根本上解决问题。

Answer 11

表的字段类型是否有问题，当遇到和视图类型不一致，要进行强制进行转换，耗费了时间
dblink测试，与网络情况的问题