同样的语句9i用30分钟出结果,10g根本出不来?我该从哪些方面调整10g呢?
我是新手,请大虾指教
9i是 win2003(32位),3G内存
10g win2008r2_64 16g内存,内存自动管理 sga 10G
同样的语句多表关联 create table as select ... 链接远程数据库
10g1个多小时没出来,9i1800多秒表建好
郁闷ing
不好意思,我是新手。不知道贴出的这些信息行吗?感觉都是在等@stiomdb。
但是9i确实1800多秒就把表建好了。10g从来没执行成功过
--------------------------------------------------------------------------------------------------------------------
----语句
create table temp_hmind_5t_111t as
select decode(a.local_net_id,'290','西安','910','咸阳','911','延安','912','榆林','913','渭南','914','商洛',
'915','安康','916','汉中','917','宝鸡','919','铜川') 地区,b.change_serv_spec_id,
a.so_nbr so,
wa.name 岗位,
a.asgn_date 到岗时间,
aa.sts_words 工单状态,
a.sts_date 状态时间,
a.remarks 备注,
a.staff_id || '-' || cc.name 施工人员,
f.nxx_nbr || f.line_nbr 逻辑号,
f.rt_line_nbr 物理号,
e.situated 用户地址,
bbb.name 产品,
bbbb.name 服务,
b.trade_name 商品名,
b.co_code 订单编码,
d.cust_name 客户名,
b.priority服务等级,
cl.cust_cat_id客户等级,
b.so_work_area_name 订单受理部门,
b.so_staff_name 订单受理人,
b.appl_date 订单受理时间,
round(sysdate-b.appl_date) 超时时长
from lspsuser.work_item_so@stiomdb a
join lspsuser.status@stiomdb aa on a.sts = aa.sts_id
and aa.table_name = 'WORK_ITEM_SO'
and aa.column_name = 'STS'
join lspsuser.so@stiomdb b on a.so_nbr = b.so_nbr
and b.state not in ('SC0403', '######')
and b.change_serv_spec_id='305000000'
join lspsuser.status@stiomdb bb on b.state = bb.sts_id
and bb.table_name = 'SO'
and bb.column_name = 'STATE'
join lspsuser.comm_serv_spec@stiomdb bbb on b.serv_spec_id =
bbb.comm_serv_spec_id
join lspsuser.change_serv_spec@stiomdb bbbb on b.change_serv_spec_id =
bbbb.chg_serv_spec_id
left join lspsuser.staff_member@stiomdb c on a.staff_id = c.staff_id
left join lspsuser.party@stiomdb cc on c.party_id = cc.party_id
join lspsuser.so_cust@stiomdb d on a.so_nbr = d.so_nbr
join lspsuser.so_addr@stiomdb e on a.so_nbr = e.so_nbr
and e.act_type'P'
join lspsuser.so_acc_nbr@stiomdb f on a.so_nbr = f.so_nbr
and f.act_type not in ('P')
join lspsuser.work_area@stiomdb wa on a.work_area_id=wa.work_area_id
left join lspsuser.cust_level@stiomdb cl on cl.cust_level_id=d.cust_level_id
where a.sts not in ('C', 'F', 'R') and a.so_work_item_type'Z'
AND round(sysdate - b.appl_date) > 5 and a.local_net_id='913'
and b.appl_date>=to_date('2005-1-1 00:00:01','yyyy-mm-dd hh24:mi:ss')
and b.appl_dateS |
|* 14 |
HASH JOIN |
| 620 | 244K| 27256 (1)| 00:05:28 |