檢查Oracle數(shù)據(jù)庫(kù)里不合理的sql語(yǔ)句的方案
以下的文章主要介紹的是如何正確的檢查Oracle數(shù)據(jù)庫(kù)里不合理的相關(guān)sql語(yǔ)句，我們首先是以代碼的方式來(lái)引出相關(guān)內(nèi)容的介紹，如果你對(duì)其相關(guān)的實(shí)際操作有興趣的話(huà)，你就可以對(duì)以下的文章點(diǎn)擊觀(guān)看了。
代碼:
- select sql_text ,
sharable_mem from v$sql where sharable_mem > @#100000@# order by sharable_mem ;
上面的sql語(yǔ)句是查詢(xún)shared pool中占用內(nèi)存超過(guò)100K的sql語(yǔ)句。
這個(gè)sql可以非常有效的檢查出Oracle shared pool中那些嚴(yán)重占用內(nèi)存的sql，根據(jù)我的經(jīng)驗(yàn)，絕大多數(shù)有問(wèn)題的sql語(yǔ)句都會(huì)在這里留下痕跡，通過(guò)在這里找出有問(wèn)題的sql語(yǔ)句并進(jìn)行修改，再反復(fù)運(yùn)行這個(gè)sql腳本，直到所以有問(wèn)題的sql都處理完畢，這就是對(duì)Oracle數(shù)據(jù)庫(kù)在sql上面的***的優(yōu)化，可以保證不會(huì)因?yàn)槌绦騿T的sql語(yǔ)句問(wèn)題導(dǎo)致Oracle數(shù)據(jù)庫(kù)的性能問(wèn)題。
共享池主要由庫(kù)緩沖區(qū)（共享SQL區(qū)和PL/SQL區(qū)）和數(shù)據(jù)字典緩沖區(qū)組成。詳細(xì)的情況你隨便找一本介紹Oracle內(nèi)存結(jié)構(gòu)的書(shū)上面都有講到。我就不把書(shū)上的內(nèi)容給你打印一遍了，自己去看。
- select * from v$sgastat;
顯式SGA的狀態(tài)信息。
有的人寫(xiě)的SQL語(yǔ)句非常復(fù)雜，嵌套了好幾層，SQL語(yǔ)句本身寫(xiě)的很差，就有可能會(huì)占用很大的SQL區(qū)域。
其實(shí)現(xiàn)實(shí)的很多情況是本來(lái)不需要那么復(fù)雜的sql的時(shí)候，由于程序員水平不夠，寫(xiě)了那種很糟糕很復(fù)雜的sql，造成的Oracle數(shù)據(jù)庫(kù)性能問(wèn)題。
另外還有一個(gè)常識(shí)問(wèn)題，卻很容易被忽略。比如：
代碼:
- select * from table_name where id = 1;
- select * from table_name where id = 2;
對(duì)于這種帶參數(shù)的sql，id = ? 這個(gè)地方叫做站位符(Placeholder)。
拿PHP為例，很多人喜歡這樣寫(xiě)代碼
代碼:
- $sql = "select * from table_name where id = ";
- $id=1;
- $stmt = ociparse($conn,$sql.$id);
- ociexecute($stmt);
- ......
- $id = 2;
- $stmt = ociparse($conn,$sql.$id);
- ociexecute($stmt);
拿Java為例，是這樣的：
代碼:
- String sql = "select * from table_name where id = ";
- Statement stmt = conn.createStatement();
- rset = stmt.executeQuery(sql+"1");
- ......
- rset = stmt.executeQuery(sql+"2");
這種寫(xiě)法，對(duì)于Oracle數(shù)據(jù)庫(kù)來(lái)說(shuō)，完全就是兩條不同的sql語(yǔ)句，
代碼:
- select * from table_name where id = 1;
- select * from table_name where id = 2;
每次查詢(xún)都要進(jìn)行sql語(yǔ)句的執(zhí)行解析，并且每個(gè)sql都會(huì)分配一個(gè)區(qū)域來(lái)存放sql解析后的二進(jìn)制可執(zhí)行代碼。試想，要是id不同的10萬(wàn)個(gè)sql呢？Oracle就會(huì)分配10萬(wàn)個(gè)sql區(qū)域來(lái)分別存放10萬(wàn)個(gè)這樣的id不同的sql語(yǔ)句。對(duì)于一個(gè)數(shù)據(jù)庫(kù)驅(qū)動(dòng)的Web網(wǎng)站這樣情況下，SGA開(kāi)的再大，也會(huì)很快被耗盡share pool的，***報(bào)一個(gè)ORA-4031錯(cuò)誤。Oracle數(shù)據(jù)庫(kù)就連接不上了，只好重起。
正確的寫(xiě)法應(yīng)該是：
代碼:
- $stmt = ociparse($conn,"select * from table_name where id = :id");
- ocibindbyname($stmt,":id",&$id, 12);
- $id =1;
- ociexecute($stmt);
- ...
- $id = 2;
- ociexecute($stmt);
代碼:
- PreparedStatement pstmt = conn.prepareStatement("select * from table_name where id = ?");
- pstmt.setInt(1,1);
- rset = pstmt.executeQuery();
- ...
- pstmt.setInt(1,2);
- rset = pstmt.executeQuery();
這樣Oracle數(shù)據(jù)庫(kù)就知道你實(shí)際上用的都是同一條sql語(yǔ)句，會(huì)以這樣的形式：
- select * from table_name where id = :1
解析執(zhí)行后存放在sql區(qū)域里面，當(dāng)以后再有一樣的sql的時(shí)候，把參數(shù)替換一下，就立刻執(zhí)行，不需要再解析sql了。既加快了sql執(zhí)行速度，也不會(huì)占有過(guò)多SGA的share pool。
可惜的是，很多程序員明知道這個(gè)問(wèn)題，卻意識(shí)不到問(wèn)題的嚴(yán)重性，因?yàn)樯厦婺欠N寫(xiě)法，編程的時(shí)候很靈活，sql語(yǔ)句可以動(dòng)態(tài)構(gòu)造，實(shí)現(xiàn)起來(lái)很容易，后面那種寫(xiě)法，sql語(yǔ)句是寫(xiě)死的，參數(shù)不能再變了，編程經(jīng)常會(huì)非常麻煩。
很多Oracle數(shù)據(jù)庫(kù)的性能問(wèn)題都是這樣造成的。
有興趣在一個(gè)生產(chǎn)系統(tǒng)中，用上面sql檢查一下，看看是否選擇出來(lái)的是否l有很多都是一樣的sql語(yǔ)句，只是參數(shù)不同，如果是這樣的話(huà)，就說(shuō)明程序員的代碼寫(xiě)的有問(wèn)題。
【編輯推薦】
