對sql語句的優化問題
參照sql例句:
Select * from employees where employee_id=100;
Update employees set employee_id =101 where employee_id=3;
優化問題:
執行sql語句的時間明顯長於之前,需要對整體的作業系統進行優化分析。
思路分析:
(通過對sql語句執行的各個過程進行分析以來查詢優化項)。
SQL 語句執行過程詳細分析:
<1> 開啟遊標cursor:
遊標cursor在伺服器端分配記憶體以來對sql語句進行快取區處理(複用遊標可以提高效能)。
<2> 解析:
語法校驗:語法校驗時不需要SGA,系統伺服器程式直接進行處理,有錯直接就會顯示出來。
語義校驗:語義校驗需要資料字典中的資訊來支撐(資料字典存有使用者資訊,使用者的許可權資訊,所有資料物件的結構資訊,表的約束條件,統計分析資料庫的檢視等,資料字典邏輯存放在system表空間中,物理存放在資料檔案中)。
在讀取表的大量結構資訊的時候,因為從磁碟資料檔案直接取會導致系統效能緩慢,oracle引入了Data Dictionary Cache 元件以用來在記憶體中直接快取資料字典中的資訊,避免了系統從磁碟直接讀取資訊,提高了系統效能。如果Data Dictionary Cache 元件記憶體很小,或者快取的資訊不是本次所需要的資料,就需要從磁碟讀取資訊,會直接影響系統效能,此時,適當的增加Shared pool 以便間接的增加Data Dictionary Cache的記憶體,快取更多的資料字典資訊。(Data Dictionary Cache 元件提高了sql語句解析語義計劃的效率)。
生成執行計劃:當完成語法,語義校驗後,生成執行計劃並且快取到shared pool 的library cache中以便下一次執行同樣的SQL語句時不需要重新執行之前的操作(生成新的執行計劃叫硬解析,直接複用之前的執行計劃叫軟解析)。反覆做硬解析CPU浪費很嚴重,影響系統效能。因此可以通過適當增加library cache的記憶體大小,檢視SQL語句並繫結變數,或者刪除不常用的SQL執行計劃以來將更多頻繁使用的sql語句執行計劃快取到library cache中,以此來提高系統效能。
(shared pool 提高了對sql語句解析計劃階段的效率)
<3> 執行SQL語句
為了保證執行效率的提高,需要將資料從磁碟上的資料檔案快取到DataBase buffer cache中,直接從記憶體中對錶進行操作,(執行階段需考慮全盤掃描和走索引不同的情況,當有大量重複資料時全盤掃描比較快,當資料分佈不均勻時索引比較快),在DataBase buffer cache中建立keep池,recycle池,default池(keep池用來快取那些經常會被訪問的表,並且使用的緩衝區獨立於DEFAULT池和recycle池,平時要適當減少keep池的記憶體分配,使keep池的命中率維持在接近100%的數值。Recycle池用來儲存那些不希望儲存在記憶體中的表。一般沒有細化記憶體分配時,系統預設只有一個default池。)
<4>提取結果並儲存
因為最終的結果是要通過net網路返回到應用伺服器(客戶服務端)上,所以如果網路出現問題,同樣會導致使用者執行sql語句時返回結果的效能降低。
sqlprocess提取結果並把最終結果返回給客戶端時,如果面臨order by 排序時,將要進行PGA sort記憶體排,如果資料量很大,PGA不能滿足記憶體排所需空間時,就需要與磁碟上的臨時表空間進行儲存分配交換,這個磁碟分配交換將要對臨時檔案頻繁進行寫和讀,因為對磁碟頻繁操作,導致系統效能大大降低。此時,需要將PGA優化,如果不得不使用臨時檔案時,就需要建立多個臨時檔案,併發降低讀寫壓力,以達到優化效果。
<5>close cursor
頻繁對cursor進行開,關操作會降低遊標的複用能力,降低系統效能。(找平衡點)
鎖等待也可能是導致sql語句執行效能降低的一個原因,所謂的鎖等待:就是一個事務a對一個資料表進行ddl或是dml操作時,系統就會對該表加上表級的排它鎖,此時其他的事務對該表進行操作的時候會等待a提交或是回滾後,才可以繼續b的操作。當一個使用者a進行ddl或者dml操作卻沒提交或者回滾,使用者b對相同行進行操作就會一直等待使用者a提交或者回滾,導致sql語句執行時間增長。
當對錶進行DML操作時:
<1><2>解析執行如上所示;
<3> 執行SQL語句
為了保證修改資料的動作能夠被資料庫恢復過來,引入了redo機制,如果redo資訊足夠多時,當系統頻繁向磁碟寫入日誌檔案時就容易導致系統效能低下,降低整個資料庫的執行效率,因此為了提高日誌提交效率,引入了Redolog buffer cache元件。當發現問題為日誌檔案時,應該適當增加Redolog buffer cache的記憶體,redo file的大小和日誌檔案組以來優化dml操作的效能問題。
<4><5>如上
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29366942/viewspace-1062236/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20211229]toad下優化sql語句注意的問題.txt優化SQL
- SQL語句優化SQL優化
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- 優化 SQL 語句的步驟優化SQL
- MySQL之SQL語句優化MySql優化
- [20201210]sql語句優化.txtSQL優化
- [20200320]SQL語句優化的困惑.txtSQL優化
- [20181114]一條sql語句的優化.txtSQL優化
- SQL優化案例-單表分頁語句的優化(八)SQL優化
- [20211221]分析sql語句遇到的問題.txtSQL
- Sql語句本身的優化-定位慢查詢SQL優化
- [20200324]SQL語句優化的困惑2.txtSQL優化
- SQL語句優化的原則與方法QOSQL優化
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句?JavaSQL
- MySQL 52個SQL效能優化策略SQL語句彙總MySql優化
- 50個SQL語句(MySQL版) 問題十四MySql
- 計算節點會對收到的SQL 語句做解析、優化、執行並SQL優化
- 20180417PLSQL中sql語句格式化與註解問題SQL
- soar-PHP - SQL 語句優化器和重寫器的 PHP 擴充套件包、 方便框架中 SQL 語句調優PHPSQL優化套件框架
- SQL語句最佳化SQL
- sql語句執行順序與效能優化(1)SQL優化
- MySql常用30種SQL查詢語句優化方法MySql優化
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- [20230329]記錄除錯sql語句遇到的問題.txt除錯SQL
- 低效sql語句執行緩慢引起的大量佔用伺服器的CPU問題處理 (優化心得)SQL伺服器優化
- 資料庫sql的優化問題的面試題資料庫SQL優化面試題
- MySQL——優化ORDER BY語句MySql優化
- [20181119]使用sql profile優化問題.txtSQL優化
- [20211229]sql語句包含中文儲存clob的編碼問題.txtSQL
- 騰訊雲TDSQL PostgreSQL版 -最佳實踐 |優化 SQL 語句SQL優化
- SQL語句SQL
- SQL語句IN的用法SQL
- (xml中sql語句為紅)解決No data sources are configured to run this SQL and provide advanced的問題XMLSQLIDE
- MySQL系列6 - join語句的優化MySql優化
- [20210205]警惕toad下優化直方圖相關sql語句.txt優化直方圖SQL
- ORACLE中sql語句----運算子的優先順序OracleSQL
- Oracle優化案例-單表分頁語句的優化(八)Oracle優化