plsql 執行sql檔案_Oracle PL/SQL調優技巧分享

原創作者：如人飲水冷暖自知

責任編輯：AcDante

前言

開門見山，今天和大家聊聊如何對PL/SQL代碼進行優化，以及如何編寫高效的PL/SQL代碼，如果您是開發DBA，或者您是資料庫開 發人員，對于PL/SQL苦苦糾結應該如何優化，請耐心把本篇文章看完，文章内容涉及的知識點較多，案例也相對較多，請您跟緊我的思 路，耐心看完，謝謝。為了更好的閱讀體驗，我們主要分享如下主題：

• PL / SQL優化器 
• 候選人調優 
• 減少CPU開銷如何 
• 批量SQL和批量綁定 
• 為多個轉換連結管道化的表函數 
• 并行更新大表 
• 收集關于使用者定義辨別符的資料 
• 分析和跟蹤PL/SQL程式 
• 編譯用于本機執行的PL/SQL單元

PL/SQL優化器

1. 在Oracle10g之前，PL/SQL編譯器是将源文本翻譯成系統代碼，而沒有應用很多更改來提高性能。到了Oracle 11gR2版本 以後PL/SQL使用了一個優化器，它可以重新排列代碼以獲得更好的性能 
2. 優化器在預設情況下是啟用的。在極少數情況下，如果優化器的開銷使非常大的應用程式的編譯太慢，我們可以通過設定編 譯參數PLSQL_OPTIMIZE_LEVEL=1而不是其預設值2來降低優化。在更罕見的情況下，PL/SQL可能會比預期更早地抛出異常，或 者根本不會抛出異常。設定PLSQL_OPTIMIZE_LEVEL=1可以防止重新排列代碼

子程式内聯

1. 編譯器可以執行的一種優化是子程式内聯。子程式内聯使用被調用子程式的副本替換子程式調用(如果被調用和調用的子程式 位于同一程式單元中)。要允許子程式内聯，要麼接受PLSQL_OPTIMIZE_LEVEL編譯參數的預設值(即2)，要麼将其設定為3
2. 使用PLSQL_OPTIMIZE_LEVEL=2，必須指定每個子程式與内聯的pragma，如果子程式被重載，則前面的pragma應用于具 有該名稱的每個子程式
3. PLSQL_OPTIMIZE_LEVEL=3的情況下，PL/SQL編譯器尋找機會進行内聯子程式。我們不需要指定要内聯的子程式。但是， 我們可以使用内聯pragma(使用前面的文法)為内聯賦予一個子程式較高的優先級
4. 如果子程式内聯降低了特定PL/SQL程式的性能，那麼使用PL/SQL分層分析器(在Oracle Database Advanced Application Developer指南中進行了解釋)來識别我們希望關閉内聯的子程式。要關閉子程式的内聯，就需要使用内聯pragma

調優查詢中的function調用，進而降低cpu使用率

1. 如果查詢過程中使用了自定義函數，那麼可能日常生産oltp環境會被調用幾百萬甚至上千萬次，我們通過函數索引作用在自 定義函數上，這樣可以緩存每一行的函數值。
2. 如果查詢将一個列傳遞給自定義函數，那麼查詢就不能在該列上使用使用者建立的索引，是以查詢可能會為表的每一行産生(可 能非常大)調用該函數。要最小化函數調用的數量，需要使用嵌套查詢。讓内部查詢将結果集過濾為少量的行，并讓外部查詢僅為這 些行調用該函數
3. 來看一個例子,兩個查詢生成相同的結果集，但是第二個查詢比第一個查詢更有效

我們來看一個例子

調整子查詢調用

1. 如果子程式有OUT或IN OUT參數，有時可以通過使用NOCOPY提示(在“NOCOPY”中描述)聲明這些參數來減少其調用開銷
2. 預設情況下，PL/SQL按值傳遞和傳入子程式參數。在運作子程式之前，PL/SQL将每個OUT和IN OUT參數複制到一個臨時變 量中，該變量在子程式執行期間儲存參數的值。如果子程式正常退出，則PL/SQL将臨時變量的值複制到對應的實際參數。如果子程 序以未處理的異常退出，則PL/SQL不會更改實際參數的值
3. 當OUT或IN OUT參數表示大型資料結構(如集合、記錄和ADT執行個體)時，複制它們會減慢執行速度并增加記憶體使用—特别是
4. 對于ADT執行個體 對于ADT方法的每次調用，PL/SQL都會複制ADT的每個屬性。如果該方法正常退出，那麼PL/SQL将應用該方法對屬性所做 的任何更改。如果方法以未處理的異常退出，則PL/SQL不會更改屬性
5. 如果您的程式在子程式以未處理的異常結束時不要求OUT或IN OUT參數保留其預調用值，則在參數聲明中包含NOCOPY提 示。NOCOPY提示請求(但不確定)編譯器通過引用而不是值傳遞相應的實際參數。有關NOCOPY的更多資訊直接參考Oracle官網， 由于篇幅問題這裡就不過多介紹了。
6. 如果編譯器遵循調用do_nothing2的NOCOPY提示，那麼do_nothing2的調用比do_nothing1的調用快

我們來看一個例子

優化計算密集型PL/SQL代碼

1. 由于PL/SQL應用程式通常是圍繞循環建構的，是以優化循環本身和其中的代碼非常重要。如果我們必須不止一次地周遊結 果集，或者在周遊結果集時發出其他查詢，那麼可能能夠更改原始查詢，以得到我們想要的結果。這時候我們通常允許程式組合多 個查詢的SQL集合操作符
2. 避免使用NUMBER資料類型族(在“NUMBER資料類型族”中描述)中使用資料類型。這些資料類型在内部以一種設計用于 可移植性、任意規模和精度的格式表示，而不是為了性能。對這些類型的資料的操作使用庫算法，而對PLS_INTEGER、 BINARY_FLOAT和BINARY_DOUBLE類型的資料的操作使用硬體算法
3. 對于本地整數變量，使用PLS_INTEGER和BINARY_INTEGER資料類型。對于不具有NULL值、不需要溢出檢查并且在性能關 鍵代碼中不使用的變量，使用PLS_INTEGER的SIMPLE_INTEGER

避免在性能關鍵型代碼中使用受限制的子類型

在性能關鍵型代碼中，避免使用受限制的子類型(在“受限制的子類型”中描述)。對受限制子類型的變量或參數的每個指派 都需要在運作時進行額外檢查，以確定所指派不違反限制

最小化隐式資料類型轉換

1. 在運作時，如果需要，PL/SQL在不同資料類型之間隐式(自動)轉換。例如，如果将PLS_INTEGER變量配置設定給數字變量，那 麼PL/SQL将PLS_INTEGER值轉換為數字值(因為值的内部表示不同)
2. 如果要将變量插入表列或從表列中指定值，則為該變量指定與表列相同的資料類型
3. 使每個字面值與指派給它的變量或出現在其中的表達式的資料類型相同。
4. 将值從SQL資料類型轉換為PL/SQL資料類型，然後在表達式中使用轉換後的值
5. 例如，将數字值轉換為PLS_INTEGER值，然後在表達式中使用PLS_INTEGER值。PLS_INTEGER操作使用硬體運算，是以它 們比使用庫運算的NUMBER運算要快

批量sql綁定

1. 當我們嘗試在pl/sql運作SELECT INTO或DML語句，PL/SQL引擎将查詢或DML語句發送到SQL引擎。SQL引擎運作查詢或 DML語句，并将結果傳回給PL/SQL引擎
2. 組成批量SQL的PL/SQL特性是FORALL語句和bulk COLLECT子句。FORALL語句将DML語句從PL/SQL分批發送到SQL，而 不是一次發送一條。BULK COLLECT子句将結果從SQL分批傳回到PL/SQL，而不是一次傳回一個。如果查詢或DML語句影響更多 資料庫行，那麼批量SQL可以顯著提高性能
3. 對于内綁定和外綁定，bulk SQL使用批量綁定;也就是說，它一次綁定整個值集合。對于n個元素的集合，bulk SQL使用一 個操作來執行相當于n個SELECT INTO或DML語句的操作。使用批量SQL的查詢可以傳回任意數量的行，而不需要為每一行使用 FETCH語句
4. FORALL語句是bulk SQL的一個特性，它将DML語句從PL/SQL分批發送到SQL，而不是一次發送一條。要了解FORALL語 句，我們首先考慮下面小案例中的FOR循環語句。它每次将這些DML語句從PL/SQL發送到SQL引擎

我們來看一個例子

利用批量綁定後的代碼如下：

1. FORAL比等效的FOR循環語句快得多。然而，FOR循環通常語句可以包含多個DML語句，而FORALL語句隻能包含一個。 FORALL語句發送給SQL的DML語句的批處理隻在它們的值和WHERE子句中有所不同。這些子句中的值必須來自現有的、填充的集合
2. 再看整合上面例子裡的代碼，下面的案例是将相同的集合元素插入兩個資料庫表中，第一個表使用FOR循環語句，第二個表 使用FORALL語句，并顯示每個語句所花費的時間

我們再看具體的執行結果截圖

關于稀疏集合的FORALL語句

1. 如果FORALL語句bounds子句引用稀疏集合，則僅指定現有索引值，使用子句的索引或值。除了按字元串索引的關聯數組外，可 以為任何集合使用索引。隻能對由PLS_INTEGER索引的PLS_INTEGER元素的集合使用值。
2. 由PLS_INTEGER索引的PLS_INTEGER元素集合可以是索引集合;也就是說，指向另一個集合(索引集合)的元素的指針的集合
3. 索引集合用于處理具有不同FORALL語句的相同集合的不同子集。與其将原始集合的元素複制到表示子集(可能會占用大量時間和 記憶體)的新集合中，不如使用索引集合表示每個子集，然後在不同FORALL語句的VALUES子句中使用每個索引集合
4. 使用帶有index OF子句的FORALL語句用稀疏集合的元素填充表。然後，它使用帶有子句值的兩個FORALL語句用集合的子集填 充兩個表

我們來看一個例子

結果集截圖如下

FORALL語句未處理的異常行為

1. 在沒有SAVE exception子句的FORALL語句中，如果一個DML語句引發一個未處理的異常，那麼PL/SQL将停止FORALL語 句并復原以前DML語句所做的所有更改
2. 若要立即處理FORALL語句中引發的異常，需要省略SAVE exceptions子句并編寫适當的異常處理程式。(有關異常處理程式 的資訊這裡我們不多講，還是看官網，沒辦法擴充太多内容資訊))如果一條DML語句引發了一個已處理的異常，那麼PL/SQL復原該 語句所做的更改，但不復原以前的DML語句所做的更改
3. 來看一個例子，FORALL語句被設計為運作三個UPDATE語句。但是，第二個會引發異常。異常處理程式處理異常，顯示錯誤消息并送出第一個UPDATE語句所做的更改。第三條UPDATE語句從不運作

我們來看一個例子

1. 那麼我們如何在FORALL語句完成以後在處理異常呢，若要允許FORALL語句在某些DML語句失敗的情況下繼續執行，請包 含SAVE exception子句。當DML語句失敗時，PL/SQL不會引發異常;相反，它儲存有關失敗的資訊。在FORALL語句完成之 後，PL/SQL為FORALL語句(ORA-24381)引發一個異常。在ORA-24381的異常處理程式中，可以從隐式遊标屬性
2. SQL%BULK_EXCEPTIONS獲得關于每個DML語句失敗的資訊，以下為Oracle官方部分介紹：
3. SQL%BULK_EXCEPTIONS is like an associative array of information about the DML statements that failed during the most recently run FORALL statement.
4. SQL%BULK_EXCEPTIONS.COUNT is the number of DML statements that failed. If
5. SQL%BULK_EXCEPTIONS.COUNT is not zero, then for each index value i from 1 through
6. SQL%BULK_EXCEPTIONS.COUNT:
7. SQL%BULK_EXCEPTIONS(i).ERROR_INDEX is the number of the DML statement that failed.
8. SQL%BULK_EXCEPTIONS(i).ERROR_CODE is the Oracle Database error code for the failure.
9. For example, if a FORALL SAVE EXCEPTIONS statement runs 100 DML statements, and the tenth and sixty-fourth ones fail with error codes ORA-12899 and ORA-19278, respectively, then:

• SQL%BULK_EXCEPTIONS.COUNT = 2
• SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10
• SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899
• SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64
• SQL%BULK_EXCEPTIONS(2).ERROR_CODE = 19278
• FORALL語句包含SAVE exception子句。
•  異常處理部分為ORA-24381提供了一個異常處理程式，當批量操作引發并儲存異常時，PL/SQL将隐式地引發内部定義的異 常。這個示例為ORA-24381提供了使用者定義的名稱dml_errors。

我們來看一個例子

dml_errors的異常處理程式使用SQL%BULK_EXCEPTIONS和SQLERRM(以及一些本地變量)來顯示錯誤消息，以及導緻錯誤的語句、集合項和字元串。看下面的代碼示例

稀疏集合和SQL%BULK_EXCEPTIONS

如果FORALL語句bounds子句引用了一個稀疏集合，那麼要找到導緻DML語句失敗的集合元素，必須逐個周遊這些元素，直到 找到索引為SQL%BULK_EXCEPTIONS(i). error_index的元素。然後，如果FORALL語句使用VALUES OF子句引用指向另一個集合的 指針集合，則必須找到另一個集合的元素，該集合的索引是SQL%BULK_EXCEPTIONS(i). error_index

使用SQL%BULK_ROWCOUNT顯示插入的FORALL語句中每個INSERT SELECT構造的行數，使用SQL%ROWCOUNT顯示插入的 行數

結果截圖顯示如下：

使用SELECT BULK COLLECT INTO語句将兩個資料庫列選擇為兩個集合(嵌套表)

帶有BULK COLLECT子句的FETCH語句(也稱為FETCH BULK COLLECT語句)将整個結果集提取到一個或多個集合變量中，來看下面的例子：

代碼太長，哈哈，截圖截不下，隻能複制原代碼，下面的例子是使用FETCH BULK COLLECT語句将結果集提取到記錄的集合(嵌 套表)中

為多個轉換連結管道化的表函數

1. 表函數是使用者定義的PL/SQL函數，它傳回行集合(嵌套的表或varray)。我們可以通過調用select語句中的table子句中的table函 數，從這個集合中進行選擇，就好像它是一個資料庫表一樣
2. 表函數可以接受一組行作為輸入(也就是說，它可以有一個輸入參數，該參數是一個嵌套的表、varray或遊标變量)。是以，表函 數tf1的輸出可以輸入到表函數tf2，表函數tf2的輸出可以輸入到表函數tf3，
3. 要提高表函數的性能，可以:

• 1.使用PARALLEL_ENABLE選項使函數能夠并行執行。
• 2.支援并行執行的函數可以并發運作。
• 3.使用Oracle流将函數結果直接發送到下一個程序。
• 4.流消除了程序之間的中間階段。有關Oracle Streams的資訊，請參見Oracle Streams概念和管理。這超出本次分享範圍了，隻 是單純提供一個技術idea
• 5.管道化函數的結果，帶有管道化選項,也就是，我經常和600團隊成員推薦的pipelined

4.管道化的表函數在處理行之後立即将行傳回給它的調用程式，并繼續處理行。響應時間有所改善，因為在查詢傳回單個結果行之 前，不需要構造整個集合并将其傳回到伺服器。(另外，函數需要更少的記憶體，因為對象緩存不需要物化整個集合)。

建立管道化的表函數

1. 管道化的表函數必須是獨立函數或包函數,管道化表函數傳回的值的資料類型必須是在模式級别或包中定義的集合類型(是以，它 不能是關聯數組類型)。集合類型的元素必須是SQL資料類型，而不是僅由PL/SQL支援的資料類型(如PLS_INTEGER和BOOLEAN)
2. 可以使用SQL資料類型ANYTYPE、ANYDATA和ANYDATASET動态封裝和通路任何其他SQL類型(包括對象和集合類型)的類型描 述、資料執行個體和資料執行個體集。我們還可以使用這些類型建立未命名類型，包括匿名集合類型
3. 建立包含管道表函數f1的包，然後從f1傳回的行集合中進行選擇

管道化的表函數作為轉換函數

具有遊标變量參數的流水線表函數可以用作轉換函數。使用遊标變量，函數擷取輸入行。函數使用PIPE ROW語句将轉換後的行 或行傳輸到調用程式。如果FETCH和PIPE ROW語句位于循環語句中，則函數可以轉換多個輸入行

如何把cursor傳遞給管道函數

直接看例子吧，這裡隻需要具體的調用方式即可，sql工作原理和上面解釋的内容相同

下面是一個綜合案例：

如何并行更新大表

1. DBMS_PARALLEL_EXECUTE包使我們能夠在兩個進階步驟中并行地增量更新大型表中的資料:
2. 将表中的行集分組為更小的塊。
3. 并行地對塊應用所需的UPDATE語句，每次處理完塊後送出。
4. 在更新大量資料時，建議使用這種技術。它的優點是:
5. 在相對較短的時間内，一次隻鎖定一組行，而不是鎖定整個表。
6. 如果在整個操作之前發生故障，我們不會丢失已經完成的工作
7. 下面是我在2016年寫的綜合例子，大家感興趣可以借鑒，切記，更新大表用dbms_parallel_execute

如何分析和跟蹤PL/SQL程式

以下截圖中的API，是PLSQL性能調優的利器，也是我之前做Oracle資料倉庫期間，經常調優用到的程式包，這裡也推薦給大家：

1. Profiler API實作為PL/SQL包DBMS_PROFILER，它的服務計算PL/SQL程式在每一行和每個子程式中花費的時間，并将這些 統計資料儲存在資料庫表中，我們可以查詢這些資料字典表來分析程式
2. TRACE API(被實作為PL/SQL包DBMS_TRACE，其服務通過子程式或異常跟蹤執行，并将這些統計資訊儲存在資料庫表 中，我在之前分享的公衆号文章中提到過trace的程式跟蹤，如果您感興趣，不妨回顧一下，真的很好用。

總

結

陳

詞

1. 優化PL/SQL程式代碼是需要和業務應用緊密結合起來的工作，當然，像您學習Oracle Troubleshooting，分析awr報 告，rac報錯一樣，PL/SQL同樣也存在工作原理，熟知這些工作原理，我們才能懂得如何使用方法和函數讓程式跑的更快。
2. 今天我們主要學習的方向包括PL/SQL優化器概念性介紹
3. 批量綁定的使用方法和工作原理
4. 管道函數方式降低cpu使用率和遞歸循環調用
5. 如何更新一張超級大表，用什麼方式更新
6. 三個用來進行PL/SQL程式代碼分析的利器，利器沒有展開講，以後如果您對我們文章感興趣，我們可以單獨通過案例講解 方式幫助您如何使用這三個API來定位問題，謝謝