淺談Oracle數據庫SQL調整與優化

孟津平 李易東

(長春理工大學 計算機科學技術學院,吉林長春 130012)

Oracle數據庫作為如今市場占有率最高的數據庫,擁有著極為突出的優勢最為顯著的是高效處理事物的能力。但作為大型數據庫難免會存在著較多問題,這些都影響數據庫對數據的高效處理。其中大部分問題都出現在數據庫應用系統性能方面,涉及到數據庫服務器、數據庫硬件、數據庫內存、數據庫參數等方面。但是在實際的數據庫操作中SQL語句的錯誤設計會導致整個查詢過程變的低效甚至癱瘓。由此,本文將對數據庫的SQL優化進行探索和研究,分析并使用合理的優化方法,從而達到數據庫高效處理的目的。

1 Oracle數據庫的查詢過程

Oracle DBA通過SQL語言對數據庫進行通信和操作,數據庫應用系統通過執行用戶或DBA提交的SQL語句完成查詢過程。

(1) 客戶端將語句發給服務器端執行,由服務器端的進程處理這些語句。

(2)服務器進程接收到SQL語句并開始解析。查詢高速緩存,查看是否存在相同語句的執行計劃。進行語法合法性檢查,看是否合乎語法規則。語言含義檢查,查看涉及的表、索引、視圖等進行解析。獲得對象解析鎖及數據訪問權限的核對。確定最佳執行計劃。

(3)綁定變量賦值。在SQL語句中如果使用了綁定變量,掃描綁定變量的聲明,給綁定變量賦值,將變量值帶入執行計劃。若在一開始解析時,SQL在高速緩沖中存在,則直接跳到該步驟。

(4)語句執行。

2 索引的合理建立

數據庫中的索引是一種排序的數據結構,是通過B樹和變形的B+樹實現的。在數據庫的使用中SQL語句的查詢速度會隨著數據量的增加變的越來越慢,因此我總結出以下幾點關于索引的使用:

(1)經常與其他表進行連接的表,在連接字段上應該建立索引。

(2)經常出現在Where子句中的字段,特別是大表的字段,應該建立索引。

(3)索引應該建在選擇性高的字段上。

(4)索引應該建在小字段上,對于大的文本字段,不要建索引。

(5)復合索引的建立需要進行仔細分析;盡量考慮用單字段索引代替。

正確選擇復合索引的主列字段,一般是選擇性較好的字段。

復合索引的幾個字段是否經常同時以AND方式出現在Where子句中？單字段查詢是否極少甚至沒有？如果是,則可以建立復合索引;否則考慮單字段索引。

如果復合索引中包含的字段經常單獨出現在Where子句中,則分解為多個單字段索引。

如果復合索引所包含的字段超過3個,那么仔細考慮其必要性,考慮減少復合的字段。

如果既有單字段索引,又有這幾個字段上的復合索引,一般可以刪除復合索引。

(6)頻繁進行數據操作的表,不要建立太多的索引。

(7)刪除無用的索引,避免對執行計劃造成負面影響。

(8)特殊字段的數據庫,如BLOB,CLOB字段不適合建立索引。

建立索引時應該首先考慮表空間和磁盤空間是否足夠,其次,在對建立索引的時候要對表進行加鎖,因此應當注意操作在業務空閑的時候進行。在建立索引的時候要對表進行全表掃描,同時還要對數據進行大量的排序操作。因此,應當調整排序區的大小。最后,建立索引的時候,可加上nologging選項。以減少在建立索引過程中產生的大量redo,從而提高執行的速度。

3 基于索引的SQL優化的研究

(1)避免在含有索引的條件上使用隱式轉換,這會使得索引失效。且SQL語句更加不容易被理解,一旦上下文環境發生改變可能無法正常運行。

(2)避免使用子查詢例如:select ename,deptno,(select sum(sal)from emp where deptno=e.deptno)tsal from emp e;執行計劃表明,上面的查詢進行了兩次全表掃描。改為select ename,deptno,sum(sal)over(partition by deptno)tsal from emp;使用分析函數后整個語句只有一次全表掃描速度增加至少一倍。

(3)當針對大量相同的列如:類別、操作員、部門ID,時使用位圖索引。在索引塊的一個索引行中存儲鍵值和起止Rowid,以及這些鍵值的位置編碼。根據鍵值查詢時可以根據起始Rowid和位圖狀態快速定位數據、做and,or,或in查詢可直接用索引的位圖進行或運算快速得出結果。

(4)表建立索引后,不斷進行增刪改等操作,會使索引中產生大量存儲碎片。這就必須要用到如下的兩種方法。

合并索引:將B樹中葉子節點的存儲碎片合并在一起,并不會改變索引的物理組織結構。

重建索引:消除存儲碎片并改變索引的全部存儲參數設置,以及存儲表空間。

4 Oracle優化思路的總結

在對數據庫的SQL語句進行優化時不能盲目優化,首先找到該數據庫的top sql,因為top sql才是這個數據庫最需要完成的工作,其好壞直接影響數據庫的使用。首先對這些語句的執行計劃進行分析,主要是找出他們的瓶頸所在,看他們是否按照預定的計劃進行查詢,再看看統計信息是否最新、是否沒有、是否不合理。一般執行計劃變慢的原因本文認為應該從以下幾點進行分析:

(1)訪問路徑的問題:在查詢的時候優化器選擇其中自認為是最優化的路徑來定位和查詢出需要的數據。但是這可能并不是預期的結果所以需要人為干預和修改。

(2)表的鏈接或索引出現問題:這種情況應該從索引創建是開始分析,哪些需要索引、是否每個索引都利用到。數據庫中如果對表頻繁操作,索引沒有定期的維護或重建索引,就有可能出現地址對應不上、查詢慢等問題。

(3)語法使用問題。

(4)物化視圖的原因。

[1]劉春菊.Oracle數據庫應用系統的性能優化[J].電子技術與軟件工程,2017,(17):180.

[2]楊嵩.淺談Oracle數據庫應用系統的性能優化[J].計算機光盤軟件與應用,2015,(03):111-112+115.

[3]莫佩宏.Oracle數據庫應用系統的性能優化[J].電子制作,2014,(16):54.