SELECT twkf_flow_instance.instance_status,twkf_flow_instance.custom_type,twkf_flow_instance.created_time,
twkf_flow_instance.terminated_reason,twkf_flow_instance.logic_end_time, tjjsp_zxspbd.fwnr,
tjjsp_zxspbd.sbdx, tjjsp_zxspbd.slbh,tjjsp_zxspbd.slbu, tjjsp_zxspbd.yqbjrq
FROM
twkf_flow_instance INNER JOIN tjjsp_zxspbd
ON twkf_flow_instance.flow_id = tjjsp_zxspbd.flow_instance_id
WHERE twkf_flow_instance.created_time > sssuser.fapp_cdate('2008-11-01 00:00:00')
AND twkf_flow_instance.created_time < sssuser.fapp_cdate('2008-11-08 00:00:00')
AND tjjsp_zxspbd.FWBH NOT IN('122-180','122-170','122-160','122-290','122-280','122-270','122-010','122-260','122-250','122-240','122-230','122-220','122-210','122-200','122-190','122-150','122-140','122-130','122-120','122-110','122-080','122-070','122-060','122-050','122-040','122-030','122-020','122-100','122-090','038-096','038-095','038-094','038-093','038-092','038-091','038-090','038-080','038-070','038-060','009-140','066-070','066-040','066-064','066-062','066-063','066-061','066-060')
ORDER BY twkf_flow_instance.created_time 整个SQL语句就是上面这样子,以前是可以用的,但前几天数据库出了次故障,这句就读不出数据了,但更奇怪的是把order by ..这句注释掉居然可以读取出数据 请各位大虾帮忙解决一下。。数据库是Oracle9i
twkf_flow_instance.terminated_reason,twkf_flow_instance.logic_end_time, tjjsp_zxspbd.fwnr,
tjjsp_zxspbd.sbdx, tjjsp_zxspbd.slbh,tjjsp_zxspbd.slbu, tjjsp_zxspbd.yqbjrq
FROM
twkf_flow_instance INNER JOIN tjjsp_zxspbd
ON twkf_flow_instance.flow_id = tjjsp_zxspbd.flow_instance_id
WHERE twkf_flow_instance.created_time > sssuser.fapp_cdate('2008-11-01 00:00:00')
AND twkf_flow_instance.created_time < sssuser.fapp_cdate('2008-11-08 00:00:00')
AND tjjsp_zxspbd.FWBH NOT IN('122-180','122-170','122-160','122-290','122-280','122-270','122-010','122-260','122-250','122-240','122-230','122-220','122-210','122-200','122-190','122-150','122-140','122-130','122-120','122-110','122-080','122-070','122-060','122-050','122-040','122-030','122-020','122-100','122-090','038-096','038-095','038-094','038-093','038-092','038-091','038-090','038-080','038-070','038-060','009-140','066-070','066-040','066-064','066-062','066-063','066-061','066-060')
ORDER BY twkf_flow_instance.created_time 整个SQL语句就是上面这样子,以前是可以用的,但前几天数据库出了次故障,这句就读不出数据了,但更奇怪的是把order by ..这句注释掉居然可以读取出数据 请各位大虾帮忙解决一下。。数据库是Oracle9i
SELECT a.instance_status,
a.custom_type,
a.created_time,
a.terminated_reason,
a.logic_end_time,
b.fwnr,
b.sbdx,
b.slbh,
b.slbu,
b.yqbjrq
FROM twkf_flow_instance a,tjjsp_zxspbd b
WHERE a.flow_id=b.flow_instance_id and a.created_time>sssuser.fapp_cdate( '2008-11-01 00:00:00 ') AND a.created_time < sssuser.fapp_cdate( '2008-11-08 00:00:00 ')
AND b.FWBH NOT IN( '122-180 ', '122-170 ', '122-160 ', '122-290 ', '122-280 ', '122-270 ', '122-010 ', '122-260 ', '122-250 ', '122-240 ', '122-230 ', '122-220 ', '122-210 ', '122-200 ', '122-190 ', '122-150 ', '122-140 ', '122-130 ', '122-120 ', '122-110 ', '122-080 ', '122-070 ', '122-060 ', '122-050 ', '122-040 ', '122-030 ', '122-020 ', '122-100 ', '122-090 ', '038-096 ', '038-095 ', '038-094 ', '038-093 ', '038-092 ', '038-091 ', '038-090 ', '038-080 ', '038-070 ', '038-060 ', '009-140 ', '066-070 ', '066-040 ', '066-064 ', '066-062 ', '066-063 ', '066-061 ', '066-060 ');
--sssuser.fapp_cdate,如果是sssuser下的处理日期的函数就对了。
--从这条语句看是用不到group by的。
twkf_flow_instance.custom_type,
twkf_flow_instance.created_time,
twkf_flow_instance.terminated_reason,
twkf_flow_instance.logic_end_time,
tjjsp_zxspbd.fwnr,
tjjsp_zxspbd.sbdx,
tjjsp_zxspbd.slbh,
tjjsp_zxspbd.slbu,
tjjsp_zxspbd.yqbjrq FROM twkf_flow_instance
INNER JOIN tjjsp_zxspbd ON twkf_flow_instance.flow_id =
tjjsp_zxspbd.flow_instance_id WHERE twkf_flow_instance.created_time >
sssuser.fapp_cdate('2008-11-01 00:00:00 ')
AND twkf_flow_instance.created_time <
sssuser.fapp_cdate('2008-11-08 00:00:00 ')
AND tjjsp_zxspbd.FWBH NOT IN
('122-180 ', '122-170 ', '122-160 ', '122-290 ', '122-280 ',
'122-270 ', '122-010 ', '122-260 ', '122-250 ', '122-240 ',
'122-230 ', '122-220 ', '122-210 ', '122-200 ', '122-190 ',
'122-150 ', '122-140 ', '122-130 ', '122-120 ', '122-110 ',
'122-080 ', '122-070 ', '122-060 ', '122-050 ', '122-040 ',
'122-030 ', '122-020 ', '122-100 ', '122-090 ', '038-096 ',
'038-095 ', '038-094 ', '038-093 ', '038-092 ', '038-091 ',
'038-090 ', '038-080 ', '038-070 ', '038-060 ', '009-140 ',
'066-070 ', '066-040 ', '066-064 ', '066-062 ', '066-063 ',
'066-061 ', '066-060 ') ORDER BY twkf_flow_instance.created_time
这是给整理过的代码没任何问题。再说order by 只是排序根本不应该影响数据。我觉得不太可能你再仔细检查下。
感谢这位朋友,但是问题是的确是这个情况,不加order by 的话在pl-sql下运行可以取出1000多条记录,但加了order by 却一条都取不出来,我现在有点怀疑是不是还是数据库空间的问题
另,LZ oracle版本
数据库版本是9.2.0.1 AIX版本的,跑在IBM P670上面to sulins :
数据库调优具体指什么?我以前主要是在开发方面,数据库的调整方面是个菜鸟。。to butchroller:
总数据量在50-60万,to wffffc :
只读取出来什么都没有,不是读取很慢感谢各位,
ORDER BY to_char(a.created_time,'yyyy-mm-dd') ---有数据
看看你的alert_SID.log里出现什么错误没有?
show parameter shared_pool_size; --......再按下修改后试一下:
alter system set cursor_sharing = similar scope=spfile;
alter system set shared_pool_size= 350m scope=spfile;
alter system set shared_pool_reserved_size= 35m scope=spfile;
select * from v$pgastat;alter session set sql_trace=true;观察一下你的排序空间以及相关的临时表空间的使用情况,以及当前执行情况.select * from v$session_longops where sid=you session id
这一句运行的结果:
NAME VALUE
aggregate PGA target parameter 134217728.00
aggregate PGA auto target 89985024.00
global memory bound 6710272.00
total PGA inuse 35431424.00
total PGA allocated 120566784.00
maximum PGA allocated 135143424.00
total freeable PGA memory 5963776.00
PGA memory freed back to OS 224387072000.00
total PGA used for auto workareas 1327104.00
maximum PGA used for auto workareas 23789568.00
total PGA used for manual workareas 0.00
maximum PGA used for manual workareas 0.00
over allocation count 0.00
bytes processed 644043993088.00
extra bytes read/written 478926292992.00
cache hit percentage 57.35 select * from v$sort_usage
这一句运行没结果。。另外:
如何查看排序空间?
我的第一感觉是临时表空间出问题了,太小或者文件有坏块
另外,lz,你试着执行完这条语句(带order by 的),看看有什么提示没有
SELECT STATEMENT, GOAL = CHOOSE 1730 1027 1826006
HASH JOIN 1730 1027 1826006
TABLE ACCESS BY INDEX ROWID SSSUSER TWKF_FLOW_INSTANCE 6 1028 1657136
INDEX RANGE SCAN SSSUSER IDX_INSTANCE_CREATED_TIME 2 400
TABLE ACCESS FULL SSSUSER TJJSP_ZXSPBD 1517 66854 11097764
这是加了order 的
SELECT STATEMENT, GOAL = CHOOSE 1992 1027 1826006
SORT ORDER BY 1992 1027 1826006
HASH JOIN 1730 1027 1826006
TABLE ACCESS BY INDEX ROWID SSSUSER TWKF_FLOW_INSTANCE 6 1028 1657136
INDEX RANGE SCAN SSSUSER IDX_INSTANCE_CREATED_TIME 2 400
TABLE ACCESS FULL SSSUSER TJJSP_ZXSPBD 1517 66854 11097764
是在sqlplus环境下执行的吗?