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

來源:互聯網
上載者:User
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語句,只是參數不同,如果是這樣的話,就說明程式員的代碼寫的有問題。

相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。