檢查Oracle資料庫中不合理的sql語句

oracle|資料|資料庫|語句 代碼:
　　select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem ;　
　　
　　
　　上面的sql語句是查詢shared pool中佔用記憶體超過100K的sql語句。
　　
　　這個sql可以非常有效檢查出Oracle shared pool中那些嚴重佔用記憶體的sql，根據我的經驗，絕大多數有問題的sql語句都會在這裡留下痕迹，通過在這裡找出有問題的sql語句並進行修改，再反覆運行這個sql指令碼，直到所以有問題的sql都處理完畢，這就是對Oracle資料庫在sql上面的最好的最佳化，可以保證不會因為程式員的sql語句問題導致Oracle資料庫的效能問題。
　　
　　共用池主要由庫緩衝區（共用SQL區和PL/SQL區）和資料字典緩衝區組成。詳細的情況你隨便找一本介紹Oracle記憶體結構的書上面都有講到。我就不把書上的內容給你列印一遍了，自己去看。
　　
　　select * from v$sgastat; --顯式SGA的狀態資訊。
　　
　　有的人寫的SQL語句非常複雜，嵌套了好幾層，SQL語句本身寫的很差，就有可能會佔用很大的SQL地區。
　　
　　其實現實的很多情況是本來不需要那麼複雜的sql的時候，由於程式員水平不夠，寫了那種很糟糕很複雜的sql，造成的資料庫效能問題。
　　
　　另外還有一個常識問題，卻很容易被忽略。比如：
　　
　　代碼:
　　select * from table_name where id = 1;
　　select * from table_name where id = 2;　
　　對於這種帶參數的sql，id = ? 這個地方叫做站位符(Placeholder)。
　　
　　拿PHP為例，很多人喜歡這樣寫代碼
　　
　　代碼:
　　$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");　
　　
　　
　　這種寫法，對於Oracle資料庫來說，完全就是兩條不同的sql語句，
　　代碼:
　　select * from table_name where id = 1;
　　select * from table_name where id = 2;　
　　每次查詢都要進行sql語句的執行解析，並且每個sql都會分配一個地區來存放sql解析後的二進位可執行代碼。試想，要是id不同的10萬個sql呢？Oracle就會分配10萬個sql地區來分別存放10萬個這樣的id不同的sql語句。對於一個資料庫驅動的Web網站這樣情況下，SGA開的再大，也會很快被耗盡share pool的，最後報一個ORA-4031錯誤。資料庫就串連不上了，只好重起。
　　
　　正確的寫法應該是：
　　
　　代碼:
　　$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資料庫就知道你實際上用的都是同一條sql語句，會以這樣的形式：
　　select * from table_name where id = :1
　　解析執行後存放在sql地區裡面，當以後再有一樣的sql的時候，把參數替換一下，就立刻執行，不需要再解析sql了。既加快了sql執行速度，也不會佔有過多SGA的share pool。
　　
　　可惜的是，很多程式員明知道這個問題，卻意識不到問題的嚴重性，因為上面那種寫法，編程的時候很靈活，sql語句可以動態構造，實現起來很容易，後面那種寫法，sql語句是寫死的，參數不能再變了，編程經常會非常麻煩。
　　
　　很多資料庫的效能問題都是這樣造成的。
　　
　　有興趣在一個生產系統中，用上面sql檢查一下，看看是否選擇出來的是否l有很多都是一樣的sql語句，只是參數不同，如果是這樣的話，就說明程式員的代碼寫的有問題。