DBA手記 - optimizer_mode影響一個SQL語句是否可以執(zhí)行
2024-07-21 02:05:45
供稿：網(wǎng)友
今天現(xiàn)場人員報告說：同樣的數(shù)據(jù)，同樣的sql，在一個產(chǎn)品數(shù)據(jù)庫中可以執(zhí)行，但是在測試數(shù)據(jù)庫中總是報錯。
檢查步驟如下：
1。在兩個數(shù)據(jù)庫中分別運行sql，驗證是否如現(xiàn)場人員報告的情況，結(jié)果屬實。
2。查看sql語句，了解sql的含義，此時發(fā)現(xiàn)該sql編寫不太理想，改寫以后在兩個數(shù)據(jù)庫中都運行正常，不過這是其它的問題，此處不表
3。檢查在兩個庫中，該sql的執(zhí)行計劃是否相同，結(jié)果不同。
4。檢查兩個庫的版本是否相同，結(jié)果相同。
5。檢查兩個庫中的優(yōu)化模式是否相同，結(jié)果不同，此時用alter session修改運行報錯的那個數(shù)據(jù)庫的優(yōu)化模式，再次查看執(zhí)行計劃，發(fā)現(xiàn)已經(jīng)相同了，再次運行sql，發(fā)現(xiàn)可以正常運行。
6。對于此案例，到上面第5步已經(jīng)可以結(jié)束了，如果第5步中發(fā)現(xiàn)優(yōu)化模式相同，那么這步就繼續(xù)可以查看兩個庫中兩張表的統(tǒng)計信息是否不同
7。如果第6步中還是相同，那么繼續(xù)檢查其它優(yōu)化相關(guān)的參數(shù)，比如optimizer_index_cost_adj等
8。如果還相同，那么去查metalink，google，通常可以發(fā)現(xiàn)這是一個oracle的bug，確認自己的情況是否屬于這個bug。。。
上面是發(fā)現(xiàn)一個問題時候我個人的大致處理方法，也許可以給newbies一些幫助。
下面是本次案例中的一些sql操作記錄和備注。
interiorid字段是varchar2(100)的類型，存儲著一些數(shù)字或者字符，下面的sql在使用to_number函數(shù)時報錯。
sql> alter session set optimizer_mode=choose;
session altered.
sql> select interiorid, constdisplayname
2 from (select interiorid, constdisplayname
3 from globalconst
4 where globalconst = 'status')
5 where to_number(interiorid) < 4
6 order by to_number(interiorid);
where to_number(interiorid) < 4
*
error at line 5:
ora-01722: invalid number
此時的執(zhí)行計劃是全表掃描，而且由于報1722錯誤，所以很明顯是因為oracle第一步執(zhí)行的是全表掃描查詢所有to_number(interiorid) < 4的記錄，而由于interiorid字段中含有非數(shù)字字符，所以報錯。
sql> alter session set optimizer_mode=first_rows;
session altered.
sql> select interiorid, constdisplayname
2 from (select interiorid, constdisplayname
3 from globalconst
4 where globalconst = 'status')
5 where to_number(interiorid) < 4
6 order by to_number(interiorid);
interiorid constdisplayname
---------- ----------------------------------------
0 正常
1 銷戶
2 凍結(jié)
3 鎖定
execution plan
----------------------------------------------------------
0 select statement optimizer=first_rows (cost=5 card=1 bytes=2
2)
1 0 sort (order by) (cost=5 card=1 bytes=22)
2 1 table access (by index rowid) of 'globalconst' (cost=3 c
ard=1 bytes=22)
3 2 index (range scan) of 'pk_globalconst' (unique) (cost=
2 card=1)
修改優(yōu)化模式，sql開始使用pk進行索引掃描，該索引是globalconst+interiorid構(gòu)成的聯(lián)合主鍵，因為globalconst= 'status'的所有記錄interiorid字段都確實是數(shù)字，所以這次sql正常執(zhí)行了。
sql> alter session set optimizer_mode=choose;
session altered.
sql> select interiorid, constdisplayname
2 from globalconst
3 where globalconst = 'status'
4 and to_number(interiorid) < 4
5 order by 1;
interiorid constdisplayname
---------- ----------------------------------------
0 正常
1 銷戶
2 凍結(jié)
3 鎖定
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=3 card=1 bytes=22)
1 0 sort (order by) (cost=3 card=1 bytes=22)
2 1 table access (full) of 'globalconst' (cost=1 card=1 byte
s=22)
我們把sql換一種寫法，雖然執(zhí)行計劃顯示的仍然是全表掃描，但是可以推測此時oracle使用了globalconst = 'status'作為filter的條件，滿足條件的再判斷是否to_number(interiorid) < 4，而因為globalconst = 'status'的記錄interiorid字段都是數(shù)字，所以sql正常執(zhí)行。
假設(shè)我們再insert一條globalconst = 'status'并且interiorid不是數(shù)字的記錄，再次執(zhí)行sql，會發(fā)現(xiàn)又報1722錯誤。
sql> alter session set optimizer_mode=first_rows;
session altered.
sql> select to_number(interiorid), constdisplayname
2 from globalconst
3 where globalconst = 'status'
4 and to_number(interiorid) < 4
5 order by 1;
to_number(interiorid) constdisplayname
--------------------- ----------------------------------------
0 正常
1 銷戶
2 凍結(jié)
3 鎖定
execution plan
----------------------------------------------------------
0 select statement optimizer=first_rows (cost=5 card=1 bytes=2
2)
1 0 sort (order by) (cost=5 card=1 bytes=22)
2 1 table access (by index rowid) of 'globalconst' (cost=3 c
ard=1 bytes=22)
3 2 index (range scan) of 'pk_globalconst' (unique) (cost=
2 card=1)
修改優(yōu)化模式，sql開始使用pk進行索引掃描，此時sql跟沒有修改前一樣，自然也是可以正常執(zhí)行的
