oracle查询视图慢死了 求解决办法

Question

CREATE OR REPLACE VIEW system.V_MOM30100_ALL AS
SELECT no, refno, rwh, gzh, type, refseq, jh, jhname, gxh, gxdh, num, cjgd, operator,
      operatorname, creater, , sj, createtime, usefix, usefixname, pays, planfinish,
      factfinish,pzrw,delx
FROM system.mom30100
UNION
SELECT no, refno, rwh, gzh, type, refseq, jh, jhname, gxh, gxdh, num, cjgd, operator,
      operatorname, creater, , sj, createtime, usefix, usefixname, pays, planfinish,
      factfinish,pzrw,delx
FROM mom30100_bak;
---------------这个视图大约100万条数据、
CREATE OR REPLACE VIEW system.V_MOM30101_ALL AS
SELECT no, seq, num, invalidnum, creater, checker, checkname, sj, createtime, facthours,
      pays, finish, serial,readytime ,readycontent ,stoptime ,stopreason,ccreason,invalidreason
FROM SYSTEM.mom30101unionSELECT no, seq, num, invalidnum, creater, checker, checkname, sj, createtime, facthours,
      pays, finish, serial,readytime ,readycontent ,stoptime ,stopreason,ccreason,invalidreason
FROM SYSTEM.mom30101_bak;---------------这个视图大约100万条数据
CREATE OR REPLACE VIEW system.V_工序明细表_ALL AS
SELECT 任务号 ,   工装号, 件号, 工序号, 工序名称, system.left(工序内容,250) as 工序内容, 单件定额, 实际件数, 工作者, 检验员,
      加工使用设备, 开始时间, 结束时间, 实做工时, 录入员, 合格标识, 不合格次数,
      导致零件报废否, 备注, Wwbz, Wwhm, Hwbz, Hwhm, de_lx, finish,invalid,yjm,ejm,sjm,gyghljh,gyghgxh,jyde,ifcheck
FROM system.工序明细表
UNION 
SELECT  任务号,  工装号, 件号, 工序号, 工序名称, system.left(工序内容,250) as 工序内容, 单件定额, 实际件数, 工作者, 检验员,
      加工使用设备, 开始时间, 结束时间, 实做工时, 录入员, 合格标识, 不合格次数,
      导致零件报废否, 备注, Wwbz, Wwhm, Hwbz, Hwhm, de_lx, finish,invalid,yjm,ejm,sjm,gyghljh,gyghgxh,jyde,ifcheck
FROM system.工序明细表_bak;
-------------------------这个视图100多万条数据select '','','',operatorlist.operator,operatorlist.operatorname ,
sum(degs - coalesce(infactbonus,0)),ls_dt,
mom20100.zzgd,coalesce(v_工序明细表_all.de_lx,' '),coalesce(v_mom30100_all.pzrw,'6')
from operatorlist,v_mom30101_all ,v_mom30100_all ,mom20100,v_工序明细表_all,任务分配表
where coalesce(balance,'0')='0' and v_mom30101_all.no =operatorlist.no and v_mom30101_all.no =v_mom30100_all.no and
v_工序明细表_all.任务号=v_mom30100_all.rwh and v_工序明细表_all.工装号=v_mom30100_all.gzh and v_工序明细表_all.件号=v_mom30100_all.jh and v_工序明细表_all.工序号=v_mom30100_all.gxh and
v_mom30100_all.refno = mom20100.momno and v_mom30100_all.refseq = mom20100.seq and v_mom30100_all.type = mom20100.type
and mom20100.gxh = v_mom30100_all.gxh and mom20100.type='3'  and
  v_mom30101_all.seq =operatorlist.seq and to_char(v_mom30101_all.sj,'yyyy-mm')='2008-05' and mom20100.zzgd like '041' and operatorlist.operator like '%' group by mom20100.zzgd,operatorlist.operator,operatorlist.operatorname,coalesce(v_工序明细表_all.de_lx,' '),v_mom30100_all.pzrw;
---其中的operatorlist与mom20100都100多万数据量 这个查询非常的慢 查询很久都不出来 有什么好办法解决下

Answer 1

我遇到过，单独查询视图很快，但是两个SQL语句查询同一个视图后再联合查询就慢了
当时我删除了基表的统计信息，然后就OK了analyze table 基表 delete statistics;analyze table 基表 compute statistics ;你可以试试！

Answer 2

你的这2个语句是删除什么的啊 是2个都要执行吗
CREATE OR REPLACE VIEW system.V_MOM30100_ALL AS
SELECT no, refno, rwh, gzh, type, refseq, jh, jhname, gxh, gxdh, num, cjgd, operator,
  operatorname, creater, , sj, createtime, usefix, usefixname, pays, planfinish,
  factfinish,pzrw,delx
FROM system.mom30100
UNION
SELECT no, refno, rwh, gzh, type, refseq, jh, jhname, gxh, gxdh, num, cjgd, operator,
  operatorname, creater, , sj, createtime, usefix, usefixname, pays, planfinish,
  factfinish,pzrw,delx
FROM mom30100_bak;
---------------这个视图大约100万条数据、
比方说这个视图 我就要执行
analyze table system.mom30100
 delete statistics;analyze table system.mom30100
 compute statistics ;
analyze table system.mom30100_bak
 delete statistics;analyze table system.mom30100_bak
 compute statistics 
是这个意思吗

Answer 3

引用 2 楼 lvze521lvze 的回复:
引用 1 楼 gelyon 的回复:
我遇到过，单独查询视图很快，但是两个SQL语句查询同一个视图后再联合查询就慢了
当时我删除了基表的统计信息，然后就OK了analyze table 基表 delete statistics;analyze table 基表 compute statistics ;你可以试试！你的这2个语句是删除什么的啊 是2个都要执行吗
……对的，就是删除你基表的统计信息，然后重新手动统计一次，看看怎么样？
我问下 然后重新手动统计一次是什么意思啊 我现在查询时快了 怎么都没数据了呢 

Answer 4

analyze table 基表 delete statistics;analyze table 基表 compute statistics ;
这两个语句 究竟是做什么的啊 谁知道 给我讲下 谢谢 

Answer 5

感谢你的解答啊 你这么处理确实解决了我的问题 
我想再请教下
analyze table 基表 delete statistics;analyze table 基表 compute statistics ;这2个具体都做了什么 为什么这么处理之后 速度超慢的问题就解决了
还有就是这2个语句是一次执行就完事 还是说以后慢了 还要这么执行啊 因为我的数据库是从sql server 2000导到oracle里的 自己又补得主键 我实在想不通为什么这么处理就解决问题了呢 

Answer 6

analyze table是关于表分析的，这个在9i以后已经过时了，9i以后用 dbms_stats.gather_XXX_stats，xxx可以是table, index等等。如果楼主对表分析还不太清楚，那么找人指点一下吧，距离你把语句调好还有比较长的距离。

Answer 7

目的 就是分析表整理 未使用的数据库 empty_blocks,提高查询速度 
analyze table 基表 delete statistics;  
--分析表 分析时间不保存到user_tables的last_analyzedanalyze table 基表 compute statistics ;--分析表 保存到user_tables 

Answer 8

目的 就是分析表整理 未使用的数据快blocks empty_blocks,提高查询速度分析表后 可以去查 user_tables中的信息 SQL> select num_rows,blocks,empty_blocks from user_tables where table_name='TB1'
  2  /  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
         4          1            0SQL> analyze table TB1 compute statistics
  2  /表已分析。SQL> select num_rows,blocks,empty_blocks from user_tables where table_name='TB1'
  2  /  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
         4          1            6

Answer 9

1.这2个具体都做了什么 为什么这么处理之后 速度超慢的问题就解决了2.还有就是这2个语句是一次执行就完事 还是说以后慢了 还要这么执行啊 因为我的数据库是从sql server 2000导到oracle里的 自己又补得主键 我实在想不通为什么这么处理就解决问题了呢 这是2个问题啊  其实我最关心的是第二个问题 因为上来那个查询视图的语句是在存储过程里  一旦存储过程处理慢了 我难道就要再执行这些语句吗 为什么我从sql server 2000导过来的数据 不执行这些语句就那么慢呢

Answer 10

我的乖乖  那意思是说 只要碎片多了 就要整理 这不现实吧  难道oracle数据库 都要不定时的“碎片整理”吗我查询视图慢的问题 只有这个办法解决吗 有没有更好的办法

Answer 11

这就说明你的基表的统计信息过旧了，对于每个SQL语句来说，Oracle都有一个执行的计划，可以看做是如何去执行这条SQL语句的，是走索引还是全表扫描，表之间的连接方式是hash连接还是nl连接等等，Oracle就是根据这些统计信息来选择SQL如何执行更高效的。
如果你表的统计信息过旧了，那么Oracle在选择执行计划的时候就有可能选择错误的执行计划，导致效率比较低。
那两个语句的目的就是让Oracle重新去收集表的统计信息，Oracle的优化器会根据这个统计信息来选择不同的执行计划。
关于这部分内容，你可以网上收集下资料慢慢研究研究。

Answer 12

看看 这个
http://blog.chinaunix.net/u2/60332/showart_495441.html

Answer 13

对于表的统计信息，如7楼majy所说，从9i还是10g都不需要手动分析了，Oracle会自动进行收集统计信息。只有在表上没有统计信息或从上次收集至今该表的数据变动量超过10%时，oracle才会根据job去自动收集一次。oracle默认有个GATHER_STATS_JOB的job，在晚间10点到上午6点间进行统计信息收集，具体可以从下面这个视图里查到实际情况。
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';如下是《Performance Tuning Guide》里的内容
14.2.1 GATHER_STATS_JOB
Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have:
?Missing statistics
?Stale statisticsThis job is created automatically at database creation time and is managed by the Scheduler. The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.
The GATHER_STATS_JOB job gathers optimizer statistics by calling the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).

Answer 14

analyze table 基表 delete statistics;analyze table 基表 compute statistics ;这2个语句是删除统计信息的意思吗  那重新手动收集一次是什么啊 是执行一次sql语句 就是重新手动收集了？

Answer 15

我原来的基表中 相关条件字段都没建立索引 后来基表建立索引了 也没好用  不过执行了GELON的给我的语句之后 速度立马上来了 以前都几十分钟不出来  现在就2分 多种