關於評審開發人員的sql語句

dbhelper發表於2014-11-26
在平時的工作中,經常會有一些開發人員提出一些資料庫相關的一些問題。可能問的最多的就是sql語句了。
按照一個標準的流程,開發提交的sql語句在完成一系列測試之後,在生產部署前,還需要dba來進行稽核。如果是緊急的補丁,也一定不要漏了這個問題。
有時候是開發嫌麻煩,要不就是開發嫌dba麻煩,這個review的過程還是很必要的。
在之前的系統遷移中,印象比較深的一個例子就是,開發寫了一個Pl/sql,在測試環境中因為沒有大量的資料做測試,測試環境美發現任何問題,結果在生產環境部署的時候就直接提交給客戶,dba沒有做review,等我發現的時,已經是馬上要開始系統升級的時候了。當時感覺不妥而且很鬱悶,但是如果要調優,時間也來不及了。就硬著頭皮開始系統升級。結果在其它部分進展都很順利,就唯獨這個Pl/sql有嚴重的問題,本來預計半個小時內就要跑完的程式,結果最後硬生生的跑了4個小時,眼看著rollback的時間越來越近了。在這個煎熬的過程中也在同時做效能調優,大半夜的在那調優,背後一大堆人看著,很不自在。
最後發現用幾個簡單的sql語句就完全可以替代pl/sql,在一個臨時的表中進行了測試,結果不到1分鐘就執行完了。
更多的調優細節,可以參考:生產系統pl/sql調優案例 http://blog.itpub.net/23718752/viewspace-1172818/
囉嗦了一大堆,就是想說明指令碼複審的重要性,可能開發和dba進行指令碼審查的角度不一樣,開發側重於業務,dba側重要系統。可能結合起來效果就好一些。
舉一個例子。
今天開發找我複審一些一個補丁指令碼,需要修復一些資料,他們的思路就是建立一個臨時的表(不是臨時表),然後給臨時表加個索引,和一個大表進行關聯update,update結束後,把這個臨時的表給刪除。
create table temp_c_rate_fix_expr_date as
select rc_rate_seq_no,expiration_date,last_cyc_expr_date,(SELECT End_Date FROM Cycle_Control WHERE Cycle_Control.Cycle_Code=c_rates.Cycle_Code AND Trunc(expiration_date,'dd') BETWEEN Start_Date AND End_Date) shoudate,
service_receiver_id
FROM C_RATES WHERE sys_creation_date = to_date('20140818','yyyymmdd') and (expiration_date is not null or expiration_date != null)
and (last_cyc_expr_date = null or last_cyc_expr_date is null);

create unique index temp_c_rate_1ux on temp_c_rate_fix_expr_date (rc_rate_seq_no,service_receiver_id) nologging ;

update ( select rc.last_cyc_expr_date OLD, tmp.shoudate NEW, rc.dl_update_stamp DL

                from c_rates rc, temp_c_rate_fix_expr_date tmp

                where tmp.rc_rate_seq_no = rc.rc_rate_seq_no and tmp.service_receiver_id = rc.service_receiver_id)

                set OLD = NEW,

                DL = :patch_id ;
COMMIT ;

drop table temp_c_rate_fix_expr_date;
commit;

這個思路我不反對,其實也是蠻高效的,而且易於控制。
但是細細檢視指令碼,還是發現了一些問題
首先是關於null的部分,開發人員不是很理解null的用途,其實expiration_date != null這種語句是錯誤的,需要糾正一下。
create table temp_c_rate_fix_expr_date as
select rc_rate_seq_no,expiration_date,last_cyc_expr_date,(SELECT End_Date FROM Cycle_Control WHERE Cycle_Control.Cycle_Code=c_rates.Cycle_Code AND Trunc(expiration_date,'dd') BETWEEN Start_Date AND End_Date) shoudate,
service_receiver_id
FROM C_RATES WHERE sys_creation_date = to_date('20140818','yyyymmdd') and (expiration_date is not null or expiration_date != null)
and (last_cyc_expr_date = null or last_cyc_expr_date is null);

還有因為這個表是一個臨時表,所以只限於這個補丁部署中使用,可以採用nologging模式,效能要高很多。
因為表比較大,所以可以考慮加入並行。
所以這個部分的改進如下:
alter session force parallel ddl parallel 8;
create table temp_c_rate_fix_expr_date nologging as
select rc_rate_seq_no,expiration_date,last_cyc_expr_date,(SELECT End_Date FROM Cycle_Control WHERE Cycle_Control.Cycle_Code=c_rates.Cycle_Code AND Trunc(expiration_date,'dd') BETWEEN Start_Date AND End_Date) shoudate,
service_receiver_id
FROM C_RATES WHERE sys_creation_date = to_date('20140818','yyyymmdd') and (expiration_date is not null )
and (last_cyc_expr_date is null );


update的部分其實也可以好好斟酌一下。如果根據第一步建立的臨時表,表中資料很少的時候,是可以考慮不用建立索引的。如果資料量大可以考慮建立索引。
最後一部分是刪除臨時表,可能開發對這個存在一定的誤解。在drop 完成之後又使用了commit,這個是不必要的。
drop table temp_c_rate_fix_expr_date;
commit;


可能看似很細小的錯誤,也可能引起很嚴重的問題。如果能在指令碼上把把關,最後一道防線就是安全的。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1347009/,如需轉載,請註明出處,否則將追究法律責任。

相關文章