1.请问把SESSION KILL 掉之后,而CPU的利用并没有被释放,怎么做能把CPU给释放掉?
2.这里有个SQL文很慢谁能帮我找找原因? 为了看了方便把最前面的SELECT 的字段写成
"*"由于太长我省略了一些LEFT JOIN 表.这个SQL文导致特别慢的原因是最后面的(...) OR (...) OR (...) 不知道为什么有这个OR就慢的不得了 把这个OR 改成 AND 却很快.所以谁能解释 OR 和 AND在时间上为什么会差很多?
select * from tr_actran_dtl_tbl
left outer join ( select * from tr_actran_sal_sup_tbl) tr_actran_sal_sup_tbl on tr_actran_sal_sup_tbl.company_cd = tr_actran_dtl_tbl.company_cd AND tr_actran_sal_sup_tbl.actrns_id = tr_actran_dtl_tbl.actrns_id AND tr_actran_sal_sup_tbl.dtl_no = tr_actran_dtl_tbl.dtl_no
left outer join ( select crd_dbt_mng_sgm1_cd, sttl_acc_cd, demand_srch_id, company_cd, actrns_id, dtl_no from tr_actran_dpst_pay_tbl) tr_actran_dpst_pay_tbl on tr_actran_dpst_pay_tbl.company_cd = tr_actran_dtl_tbl.company_cd AND tr_actran_dpst_pay_tbl.actrns_id = tr_actran_dtl_tbl.actrns_id AND tr_actran_dpst_pay_tbl.dtl_no = tr_actran_dtl_tbl.dtl_no
left outer join ( select crd_dbt_mng_sgm1_cd, sttl_acc_cd, demand_srch_id, company_cd, actrns_id, dtl_no from tr_actran_imex_tbl) tr_actran_imex_tbl on tr_actran_imex_tbl.company_cd = tr_actran_dtl_tbl.company_cd AND tr_actran_imex_tbl.actrns_id = tr_actran_dtl_tbl.actrns_id AND tr_actran_imex_tbl.dtl_no = tr_actran_dtl_tbl.dtl_no
left outer join ( select * from tr_dem_bill_tbl) tr_dem_bill_tbl_sal on tr_dem_bill_tbl_sal.company_cd = tr_actran_sal_sup_tbl.company_cd AND tr_dem_bill_tbl_sal.demand_srch_id = tr_actran_sal_sup_tbl.demand_srch_id
left outer join ( select * from tr_dem_bill_tbl) tr_dem_bill_tbl_pay on tr_dem_bill_tbl_pay.company_cd = tr_actran_dpst_pay_tbl.company_cd AND tr_dem_bill_tbl_pay.demand_srch_id = tr_actran_dpst_pay_tbl.demand_srch_id
left outer join ( select * from tr_dem_bill_tbl) tr_dem_bill_tbl_imex on tr_dem_bill_tbl_imex.company_cd = tr_actran_imex_tbl.company_cd AND tr_dem_bill_tbl_imex.demand_srch_id = tr_actran_imex_tbl.demand_srch_id
left outer join ( select stf_nm_l1, stf_nm_l2, stf_nm_l3, stf_no, company_cd from pri_cost_sec_tbl where cost_sec_s_dt <= '20050222' AND cost_sec_e_dt >= '20050222' AND (((pri_cost_sec_tbl.company_cd ='HAITAC'))) ) stf_tbl1 on stf_tbl1.company_cd = tr_dem_bill_tbl_sal.company_cd AND stf_tbl1.stf_no = tr_dem_bill_tbl_sal.stf_cd
left outer join ( select stf_nm_l1, stf_nm_l2, stf_nm_l3, stf_no, company_cd from pri_cost_sec_tbl where cost_sec_s_dt <= '20050222' AND cost_sec_e_dt >= '20050222' AND (((pri_cost_sec_tbl.company_cd ='HAITAC'))) ) stf_tbl2 on stf_tbl2.company_cd = tr_dem_bill_tbl_pay.company_cd AND stf_tbl2.stf_no = tr_dem_bill_tbl_pay.stf_cd
left outer join ( select stf_nm_l1, stf_nm_l2, stf_nm_l3, stf_no, company_cd from pri_cost_sec_tbl where cost_sec_s_dt <= '20050222' AND cost_sec_e_dt >= '20050222' AND (((pri_cost_sec_tbl.company_cd ='HAITAC'))) ) stf_tbl3 on stf_tbl3.company_cd = tr_dem_bill_tbl_imex.company_cd AND stf_tbl3.stf_no = tr_dem_bill_tbl_imex.stf_cd
left outer join ( select curr_s_nm, company_cd, curr_cd from curr_tbl where (((curr_tbl.company_cd ='HAITAC'))) ) curr_tbl11 on curr_tbl11.company_cd = tr_dem_bill_tbl_sal.company_cd AND curr_tbl11.curr_cd = tr_dem_bill_tbl_sal.demand_curr_cd
left outer join ( select curr_s_nm, company_cd, curr_cd from curr_tbl where (((curr_tbl.company_cd ='HAITAC'))) ) curr_tbl12 on curr_tbl12.company_cd = tr_dem_bill_tbl_pay.company_cd AND curr_tbl12.curr_cd = tr_dem_bill_tbl_pay.demand_curr_cd
left outer join ( select curr_s_nm, company_cd, curr_cd from curr_tbl where (((curr_tbl.company_cd ='HAITAC'))) ) curr_tbl13 on curr_tbl13.company_cd = tr_dem_bill_tbl_imex.company_cd AND curr_tbl13.curr_cd = tr_dem_bill_tbl_imex.demand_curr_cd
left outer join ( select curr_s_nm, company_cd, curr_cd from curr_tbl where (((curr_tbl.company_cd ='HAITAC'))) ) curr_tbl2 on curr_tbl2.company_cd = tr_actran_dtl_tbl.company_cd AND curr_tbl2.curr_cd = tr_actran_dtl_tbl.actrns_cur_cd
left outer join ( select actrns_typ_nm_l1, actrns_typ_nm_l2, actrns_typ_nm_l3, company_cd, actrns_typ from actrns_typ_tbl where (((actrns_typ_tbl.company_cd ='HAITAC'))) ) actrns_typ_tbl on actrns_typ_tbl.company_cd = tr_actran_dtl_tbl.company_cd AND actrns_typ_tbl.actrns_typ = tr_actran_dtl_tbl.actrns_typ
left outer join ( select acc_s_nm_l1, acc_s_nm_l2, acc_s_nm_l3, company_cd, acc_cd from acc_tbl where aply_begn_dt <= '20050222' AND aply_end_dt >= '20050222' AND (((acc_tbl.company_cd ='HAITAC'))) ) acc_tbl1 on acc_tbl1.company_cd = tr_dem_bill_tbl_sal.company_cd AND acc_tbl1.acc_cd = tr_dem_bill_tbl_sal.dem_acct_cd
left outer join ( select acc_s_nm_l1, acc_s_nm_l2, acc_s_nm_l3, company_cd, acc_cd from acc_tbl where aply_begn_dt <= '20050222' AND aply_end_dt >= '20050222' AND (((acc_tbl.company_cd ='HAITAC'))) ) acc_tbl2 on acc_tbl2.company_cd = tr_dem_bill_tbl_pay.company_cd AND acc_tbl2.acc_cd = tr_dem_bill_tbl_pay.dem_acct_cd
left outer join ( select acc_s_nm_l1, acc_s_nm_l2, acc_s_nm_l3, company_cd, acc_cd from acc_tbl where aply_begn_dt <= '20050222' AND aply_end_dt >= '20050222' AND (((acc_tbl.company_cd ='HAITAC'))) ) acc_tbl3 on acc_tbl3.company_cd = tr_dem_bill_tbl_imex.company_cd AND acc_tbl3.acc_cd = tr_dem_bill_tbl_imex.dem_acct_cd
left outer join ( select itm_snm1_l1, itm_snm1_l2, itm_snm1_l3, std_itm_grp1_cd, company_cd, itm_cd from item_bas_tbl where itm_aply_begn_day <= '20050222' AND itm_aply_end_day >= '20050222' AND (((item_bas_tbl.company_cd ='HAITAC'))) ) item_bas_tbl on item_bas_tbl.company_cd = tr_actran_sal_sup_tbl.company_cd AND item_bas_tbl.itm_cd = tr_actran_sal_sup_tbl.itm_cd
left outer join ( select ut_, company_cd, ut_cd from unit_tbl where (((unit_tbl.company_cd ='HAITAC'))) ) unit_tbl on unit_tbl.company_cd = tr_actran_sal_sup_tbl.company_cd AND unit_tbl.ut_cd = tr_actran_sal_sup_tbl.actrns_qty_ut
where tr_actran_dtl_tbl.bus_u_cd = 'B1100000'
AND tr_actran_dtl_tbl.fgr_typ = '0'
and
(tr_actran_sal_sup_tbl.oem_cmpr_no = '400'
and tr_dem_bill_tbl_sal.char_sec_cd = 'B1110000'
and tr_dem_bill_tbl_sal.demand_cd = '309104'
and tr_dem_bill_tbl_sal.demand_curr_cd = '122'
and tr_dem_bill_tbl_sal.stf_cd = 'SL001'
and tr_dem_bill_tbl_sal.dem_no = '100'
and tr_dem_bill_tbl_sal.dem_day >= '20000101'
and tr_dem_bill_tbl_sal.dem_day <= '20050101'
and tr_dem_bill_tbl_sal.rsv_fld_char1 = '8'
and tr_dem_bill_tbl_sal.dem_bill_issu_typ = '0'
)
or
( tr_dem_bill_tbl_pay.char_sec_cd = 'B1110000'
and tr_dem_bill_tbl_pay.demand_cd = '309104'
and tr_dem_bill_tbl_pay.demand_curr_cd = '122'
and tr_dem_bill_tbl_pay.stf_cd = 'SL001'
and tr_dem_bill_tbl_pay.dem_no = '100'
and tr_dem_bill_tbl_pay.dem_day >= '20000101'
and tr_dem_bill_tbl_pay.dem_day <= '20050101'
and tr_dem_bill_tbl_pay.rsv_fld_char1 = '8'
and tr_dem_bill_tbl_pay.dem_bill_issu_typ = '0'
)
or
( tr_dem_bill_tbl_imex.char_sec_cd = 'B1110000'
and tr_dem_bill_tbl_imex.demand_cd = '309104'
and tr_dem_bill_tbl_imex.demand_curr_cd = '122'
and tr_dem_bill_tbl_imex.stf_cd = 'SL001'
and tr_dem_bill_tbl_imex.dem_no = '100'
and tr_dem_bill_tbl_imex.dem_day >= '20000101'
and tr_dem_bill_tbl_imex.dem_day <= '20050101'
and tr_dem_bill_tbl_imex.rsv_fld_char1 = '8'
and tr_dem_bill_tbl_imex.dem_bill_issu_typ = '0'
)
AND (((tr_actran_dtl_tbl.company_cd ='HAITAC')))
2.这里有个SQL文很慢谁能帮我找找原因? 为了看了方便把最前面的SELECT 的字段写成
"*"由于太长我省略了一些LEFT JOIN 表.这个SQL文导致特别慢的原因是最后面的(...) OR (...) OR (...) 不知道为什么有这个OR就慢的不得了 把这个OR 改成 AND 却很快.所以谁能解释 OR 和 AND在时间上为什么会差很多?
select * from tr_actran_dtl_tbl
left outer join ( select * from tr_actran_sal_sup_tbl) tr_actran_sal_sup_tbl on tr_actran_sal_sup_tbl.company_cd = tr_actran_dtl_tbl.company_cd AND tr_actran_sal_sup_tbl.actrns_id = tr_actran_dtl_tbl.actrns_id AND tr_actran_sal_sup_tbl.dtl_no = tr_actran_dtl_tbl.dtl_no
left outer join ( select crd_dbt_mng_sgm1_cd, sttl_acc_cd, demand_srch_id, company_cd, actrns_id, dtl_no from tr_actran_dpst_pay_tbl) tr_actran_dpst_pay_tbl on tr_actran_dpst_pay_tbl.company_cd = tr_actran_dtl_tbl.company_cd AND tr_actran_dpst_pay_tbl.actrns_id = tr_actran_dtl_tbl.actrns_id AND tr_actran_dpst_pay_tbl.dtl_no = tr_actran_dtl_tbl.dtl_no
left outer join ( select crd_dbt_mng_sgm1_cd, sttl_acc_cd, demand_srch_id, company_cd, actrns_id, dtl_no from tr_actran_imex_tbl) tr_actran_imex_tbl on tr_actran_imex_tbl.company_cd = tr_actran_dtl_tbl.company_cd AND tr_actran_imex_tbl.actrns_id = tr_actran_dtl_tbl.actrns_id AND tr_actran_imex_tbl.dtl_no = tr_actran_dtl_tbl.dtl_no
left outer join ( select * from tr_dem_bill_tbl) tr_dem_bill_tbl_sal on tr_dem_bill_tbl_sal.company_cd = tr_actran_sal_sup_tbl.company_cd AND tr_dem_bill_tbl_sal.demand_srch_id = tr_actran_sal_sup_tbl.demand_srch_id
left outer join ( select * from tr_dem_bill_tbl) tr_dem_bill_tbl_pay on tr_dem_bill_tbl_pay.company_cd = tr_actran_dpst_pay_tbl.company_cd AND tr_dem_bill_tbl_pay.demand_srch_id = tr_actran_dpst_pay_tbl.demand_srch_id
left outer join ( select * from tr_dem_bill_tbl) tr_dem_bill_tbl_imex on tr_dem_bill_tbl_imex.company_cd = tr_actran_imex_tbl.company_cd AND tr_dem_bill_tbl_imex.demand_srch_id = tr_actran_imex_tbl.demand_srch_id
left outer join ( select stf_nm_l1, stf_nm_l2, stf_nm_l3, stf_no, company_cd from pri_cost_sec_tbl where cost_sec_s_dt <= '20050222' AND cost_sec_e_dt >= '20050222' AND (((pri_cost_sec_tbl.company_cd ='HAITAC'))) ) stf_tbl1 on stf_tbl1.company_cd = tr_dem_bill_tbl_sal.company_cd AND stf_tbl1.stf_no = tr_dem_bill_tbl_sal.stf_cd
left outer join ( select stf_nm_l1, stf_nm_l2, stf_nm_l3, stf_no, company_cd from pri_cost_sec_tbl where cost_sec_s_dt <= '20050222' AND cost_sec_e_dt >= '20050222' AND (((pri_cost_sec_tbl.company_cd ='HAITAC'))) ) stf_tbl2 on stf_tbl2.company_cd = tr_dem_bill_tbl_pay.company_cd AND stf_tbl2.stf_no = tr_dem_bill_tbl_pay.stf_cd
left outer join ( select stf_nm_l1, stf_nm_l2, stf_nm_l3, stf_no, company_cd from pri_cost_sec_tbl where cost_sec_s_dt <= '20050222' AND cost_sec_e_dt >= '20050222' AND (((pri_cost_sec_tbl.company_cd ='HAITAC'))) ) stf_tbl3 on stf_tbl3.company_cd = tr_dem_bill_tbl_imex.company_cd AND stf_tbl3.stf_no = tr_dem_bill_tbl_imex.stf_cd
left outer join ( select curr_s_nm, company_cd, curr_cd from curr_tbl where (((curr_tbl.company_cd ='HAITAC'))) ) curr_tbl11 on curr_tbl11.company_cd = tr_dem_bill_tbl_sal.company_cd AND curr_tbl11.curr_cd = tr_dem_bill_tbl_sal.demand_curr_cd
left outer join ( select curr_s_nm, company_cd, curr_cd from curr_tbl where (((curr_tbl.company_cd ='HAITAC'))) ) curr_tbl12 on curr_tbl12.company_cd = tr_dem_bill_tbl_pay.company_cd AND curr_tbl12.curr_cd = tr_dem_bill_tbl_pay.demand_curr_cd
left outer join ( select curr_s_nm, company_cd, curr_cd from curr_tbl where (((curr_tbl.company_cd ='HAITAC'))) ) curr_tbl13 on curr_tbl13.company_cd = tr_dem_bill_tbl_imex.company_cd AND curr_tbl13.curr_cd = tr_dem_bill_tbl_imex.demand_curr_cd
left outer join ( select curr_s_nm, company_cd, curr_cd from curr_tbl where (((curr_tbl.company_cd ='HAITAC'))) ) curr_tbl2 on curr_tbl2.company_cd = tr_actran_dtl_tbl.company_cd AND curr_tbl2.curr_cd = tr_actran_dtl_tbl.actrns_cur_cd
left outer join ( select actrns_typ_nm_l1, actrns_typ_nm_l2, actrns_typ_nm_l3, company_cd, actrns_typ from actrns_typ_tbl where (((actrns_typ_tbl.company_cd ='HAITAC'))) ) actrns_typ_tbl on actrns_typ_tbl.company_cd = tr_actran_dtl_tbl.company_cd AND actrns_typ_tbl.actrns_typ = tr_actran_dtl_tbl.actrns_typ
left outer join ( select acc_s_nm_l1, acc_s_nm_l2, acc_s_nm_l3, company_cd, acc_cd from acc_tbl where aply_begn_dt <= '20050222' AND aply_end_dt >= '20050222' AND (((acc_tbl.company_cd ='HAITAC'))) ) acc_tbl1 on acc_tbl1.company_cd = tr_dem_bill_tbl_sal.company_cd AND acc_tbl1.acc_cd = tr_dem_bill_tbl_sal.dem_acct_cd
left outer join ( select acc_s_nm_l1, acc_s_nm_l2, acc_s_nm_l3, company_cd, acc_cd from acc_tbl where aply_begn_dt <= '20050222' AND aply_end_dt >= '20050222' AND (((acc_tbl.company_cd ='HAITAC'))) ) acc_tbl2 on acc_tbl2.company_cd = tr_dem_bill_tbl_pay.company_cd AND acc_tbl2.acc_cd = tr_dem_bill_tbl_pay.dem_acct_cd
left outer join ( select acc_s_nm_l1, acc_s_nm_l2, acc_s_nm_l3, company_cd, acc_cd from acc_tbl where aply_begn_dt <= '20050222' AND aply_end_dt >= '20050222' AND (((acc_tbl.company_cd ='HAITAC'))) ) acc_tbl3 on acc_tbl3.company_cd = tr_dem_bill_tbl_imex.company_cd AND acc_tbl3.acc_cd = tr_dem_bill_tbl_imex.dem_acct_cd
left outer join ( select itm_snm1_l1, itm_snm1_l2, itm_snm1_l3, std_itm_grp1_cd, company_cd, itm_cd from item_bas_tbl where itm_aply_begn_day <= '20050222' AND itm_aply_end_day >= '20050222' AND (((item_bas_tbl.company_cd ='HAITAC'))) ) item_bas_tbl on item_bas_tbl.company_cd = tr_actran_sal_sup_tbl.company_cd AND item_bas_tbl.itm_cd = tr_actran_sal_sup_tbl.itm_cd
left outer join ( select ut_, company_cd, ut_cd from unit_tbl where (((unit_tbl.company_cd ='HAITAC'))) ) unit_tbl on unit_tbl.company_cd = tr_actran_sal_sup_tbl.company_cd AND unit_tbl.ut_cd = tr_actran_sal_sup_tbl.actrns_qty_ut
where tr_actran_dtl_tbl.bus_u_cd = 'B1100000'
AND tr_actran_dtl_tbl.fgr_typ = '0'
and
(tr_actran_sal_sup_tbl.oem_cmpr_no = '400'
and tr_dem_bill_tbl_sal.char_sec_cd = 'B1110000'
and tr_dem_bill_tbl_sal.demand_cd = '309104'
and tr_dem_bill_tbl_sal.demand_curr_cd = '122'
and tr_dem_bill_tbl_sal.stf_cd = 'SL001'
and tr_dem_bill_tbl_sal.dem_no = '100'
and tr_dem_bill_tbl_sal.dem_day >= '20000101'
and tr_dem_bill_tbl_sal.dem_day <= '20050101'
and tr_dem_bill_tbl_sal.rsv_fld_char1 = '8'
and tr_dem_bill_tbl_sal.dem_bill_issu_typ = '0'
)
or
( tr_dem_bill_tbl_pay.char_sec_cd = 'B1110000'
and tr_dem_bill_tbl_pay.demand_cd = '309104'
and tr_dem_bill_tbl_pay.demand_curr_cd = '122'
and tr_dem_bill_tbl_pay.stf_cd = 'SL001'
and tr_dem_bill_tbl_pay.dem_no = '100'
and tr_dem_bill_tbl_pay.dem_day >= '20000101'
and tr_dem_bill_tbl_pay.dem_day <= '20050101'
and tr_dem_bill_tbl_pay.rsv_fld_char1 = '8'
and tr_dem_bill_tbl_pay.dem_bill_issu_typ = '0'
)
or
( tr_dem_bill_tbl_imex.char_sec_cd = 'B1110000'
and tr_dem_bill_tbl_imex.demand_cd = '309104'
and tr_dem_bill_tbl_imex.demand_curr_cd = '122'
and tr_dem_bill_tbl_imex.stf_cd = 'SL001'
and tr_dem_bill_tbl_imex.dem_no = '100'
and tr_dem_bill_tbl_imex.dem_day >= '20000101'
and tr_dem_bill_tbl_imex.dem_day <= '20050101'
and tr_dem_bill_tbl_imex.rsv_fld_char1 = '8'
and tr_dem_bill_tbl_imex.dem_bill_issu_typ = '0'
)
AND (((tr_actran_dtl_tbl.company_cd ='HAITAC')))
用alter system kill session进行删除的时候,如果在系统时间内没有删除,则把标记给为killed。这时候再用该命令去删除的时候,是不在起作用的!要用系统删除命令(运行中执行):orakill /?
"*"由于太长我省略了一些LEFT JOIN 表.这个SQL文导致特别慢的原因是最后面的(...) OR (...) OR (...) 不知道为什么有这个OR就慢的不得了 把这个OR 改成 AND 却很快.所以谁能解释 OR 和 AND在时间上为什么会差很多?
对这个问题,我想简单的可以认为这样,and 是从结果中除去数据;而or却相反,是去检索出数据,再减去重复的!所以性能上自然就相差很多!
问题2:or表示的是并集,and表示的交集,多个的交集肯定比多个的并集小很多了,所以快很多!
对于第二个问题,由于你的语句太长,你不如把表结构和目的写出来,让大家帮你写一个,自己去琢磨那个好!