PL/SQL用戶指南與參考--PL/SQL應用程序性能調優
2024-07-21 02:45:03
供稿：網友
第十二章 PL/SQL應用程序性能調優
1、PL/SQL性能問題的緣由
應基于PL/SQL的應用程序施行效率低下時，通常是因為不好的SQL話語、編程步驟，對PL/SQL基礎掌握糟糕或是亂用共享內存儲器促成的。
•PL/SQL中不好的SQL話語
PL/SQL編程看上去相對照較簡單，由于它們的復雜內容都掩藏在SQL話語中，SQL話語經常分擔大量的工作。這乃是為何不好的SQL話語是施行效率低下的重要緣故了。如若一個程序中包孕很多不好的SQL話語，那么，無論是PL/SQL話語寫的有何其美都是于事無補的。
如其SQL話語減低了我們的程序速度的話，將要按底下列表中的步驟分析一下子它們的執行計劃和性能，其后從新編纂SQL話語。比如，查詢優化器的揭示就可能會排除掉問題，如沒有必要的全表掃描。
一.EXPLAIN PLAN話語
二.施用TKPROF的SQL Trace效能
三.Oracle Trace效能
•糟糕的編程習氣
正常，糟糕的編程習氣也會給程序帶回負面影響。這種情況下，即使是有心得的程序員寫出的代碼也也許妨礙性能發揮。
至于給定的一項任務，無論是所選的程序語言有何等適合，編纂品質較差的子程序(比如，一個很慢的分門別類或檢索函數)或許毀掉整個性能。假設有一個急需被應用程序頻繁調用的查詢函數，如其這個函數不是應用哈?；蚨址?，而是直接運用線性查尋，就會大大影響效率。糟糕的程序指的是那些帶有從未有過運用過的變量的，傳送沒有必要的參數的，把初始化或計算放到用不著的循環中施行的程序之類。
•內置函數的反復
PL/SQL提供了好多高度優化過的函數，如REPLACE、TRANSLATE、SUBSTR、INSTR、RPAD和LTRIM等。不用手工編撰我們自各兒的版本，由于內置函數已經是很高效率的了。即或內置函數的效能遠遠超過我們的亟需，也不用手工兌現它們效能的子集。
•低效的流程統制話語
在計算邏輯表達式值的時分，PL/SQL運用短路的計算模式。且不說，萬一結果可以被確定下來，PL/SQL就會終止余下的表達式計算。比如，下部的OR表達式，應sal比1500小的時分，操作符左邊的值乃是TRUE，之所以PL/SQL就不會再計算操作符右側表達式的值：
IF (sal < 1500) OR (comm IS NULL) THEN
...
END IF;
現下，考慮下部的AND表達式：
IF credit_ok(cust_id) AND (loan < 5000) THEN
...
END IF;
在上面的函數中，布爾函數credit_ok老是被調用。但是，如其我們向底下這么退換兩個表達式的位置：
IF (loan < 5000) AND credit_ok(cust_id) THEN
...
END IF;
那么，函數惟獨在表達式loan < 5000的值為TRUE的時分才會被調用，這種狀況也適用于EXIT-WHEN話語。
•隱式的數據門類變換
運行時，PL/SQL能把構造化不同的數據部類進展隱式的變換。譬如說，把PLS_INTEGER變量賦給一個NUMBER變量，因為它們的內在表現形式不一樣，之所以就會引起隱式地數據部類變換。
避免隱式的門類變換可以改進性能。如次面的例證，15是一個有符號的4字節數目字，在加法演算事前，PL/SQL務必把它轉換成Oracle的數目字門類。但是，浮點數15.0運用22字節的Oracle數目字表現，之所以就沒有必要開展變換。
DECLARE
n NUMBER;
c CHAR(五);
BEGIN
n := n + 15; -- converted
n := n + 15.0; -- not converted
...
END;
這邊再有一個事例：
DECLARE
c CHAR(五);
BEGIN
c := 25; -- converted
c := '25'; -- not converted
...
END;
•不適當的數目字門類宣言
數據門類NUMBER和它的子部類都是22字節，數據庫格式的數目字，它們易于移栽并且能順應于不同的長度與精密度。當我們亟需宣言一個整型變量時，快要應用PLS_INTEGER，它是最高效率的數目字門類。這是由于PLS_INTEGER所需的內存儲器要比INTEGER和NUMBER門類要少。一樣， PLS_INTEGER運用機器演算，之所以它的演算速度要比BINARY_INTEGER、INTEGER或NUMBER快得多。
另外，INTEGER、NATURAL、NATURALN、POSITIVE、POSITIVEN和SIGNTYPE都是受約束的子門類。之所以，它們的變量急需在運行時稽查精密度，這就會影響到效率。
•用不著的NOT NULL約束
PL/SQL中，施用NOT NULL約束也會羅致性能虧耗。如次例所示：
PROCEDURE calc_m IS
m NUMBER NOT NULL := 零;
a NUMBER;
b NUMBER;
BEGIN
...
m := a + b;
...
END;
由于m是受NOT NULL約束的，表達式a + b的值就會賦給暫時變量，其后PL/SQL會對這個暫時變量作判空測試。如其變量不是空，它的值就能賦給m，要不然就會出現異常。但是，如若m不是有約束限制的話，結果值就會直接賦給m。更高效率的寫法如次：
PROCEDURE calc_m IS
m NUMBER; -- no constraint
a NUMBER;
b NUMBER;
BEGIN
...
m := a + b;
IF m IS NULL THEN -- enforce constraint programmatically
...
END IF;
END;
注意，NATURALN和POSTIVEN都是NOT NULL，之所以它們也同樣會影響性能。
•VARCHAR二變量的長度宣言
至于VARCHAR2部類，我們在內存儲器應用和效率上亟需作出一個衡量。關于VARCHAR二(長度>=2000)變量，PL/SQL動態分配內存儲器來寄存現實值，但至于VARCHAR二(長度<2000)變量，PL/SQL能之前分配足夠的內存儲器。之所以，如其我們把一樣一個500字節的值放入一個VARCHAR二(2000)和一個VARCHAR二(1999)變量中，后者會多占用1499個字節的內存儲器。
•亂用PL/SQL程序中的共享內存儲器
第一回調用封裝子程序時，整個包會被加載到共享內存儲器池。之所以，之后調用包內相干子程序時，就不再亟需讀取磁盤了，這么會加緊我們的代碼會實施速度。但是，如其包從內存儲器中掃除以后，我們在從新引述它的時分，就必須從新加載它。
我們可以經過正確地設立共享內存儲器池大小來改進性能。一定要準保共享內存儲器有足夠空間來寄存被頻繁施用的包，但空間也不用過大，省得鋪張內存儲器。
•護持包(Pinned Packages)
此外一個改進性能的步驟便是把頻繁運用的包護持到共享內存儲器池中。應一個包被護持下來后，它就不會被Oracle正常所采取的起碼日前施用(LRU) 算法掃除。甭管池有多滿或是我們訪問包有何等頻繁，包始終會被保持在池中的。我們可以利用系統包DBMS_SHARED_POOL把包護持下來。
•可接續重用包
為了相助我們治理內存儲器的運用，PL/SQL提供了編譯指示SERIALLY_REUSABLE，它能讓我們把某些包標記為可接續重用的 (serially reusable)。如其一個包的狀態只在服務器呼叫時間內所急需，那么我們就可以對這個包應用這個標記了(比如，一個對服務器的OCI調用或是服務器對服務器RPC)。
關于這么的包所分配的內存儲器會放到系統大局區(SGA)中，而不是分配到獨力的用戶所施用的用戶大局區(UGA)。那樣，包的工作區就可以被重復運用。應服務器調用完結的時分，內存儲器就會被歸還共享池。次次包被重用時，它的公共變量就會被初始化作它們的默認值或NULL。
一個包所需的工作區最大個數乃是目前運用這個包的用戶數，這個數目字一般要小于登錄用戶數。SGA內存儲器的增長量要大于UGA內存儲器的減縮量。而且，如其Oracle要回收SGA內存儲器的話，它就會把沒施用的工作區開展逾期處置。
關于沒包體的包來說，我們可以應用在包說明中施用下邊語法編纂編譯指示：
PRAGMA SERIALLY_REUSABLE;
至于有包體的包來說，我們務必在說明和包體中編纂編譯指示。我們不能只在包體中編纂編譯指示。底下的例證演練了何以在一個接續重用包中應用一個公共變量：
CREATE PACKAGE pkg一 IS
PRAGMA SERIALLY_REUSABLE;
num NUMBER := 零;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg一;
/
CREATE PACKAGE BODY pkg一 IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state(n NUMBER) IS
BEGIN
pkg一.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put_line('Num: ' || pkg一.num);
END;
END pkg一;
/
BEGIN
/* Initialize package state. */
pkg一.init_pkg_state(四);
/* On same server call, print package state. */
pkg一.print_pkg_state; -- prints 四
END;
/
-- subsequent server call
BEGIN
-- the package public variable is initialized
-- to the default value automatically
pkg一.print_pkg_state; -- prints 零
END;
2、確定PL/SQL的性能問題
當我們開發越來越大的PL/SQL應用程序時，便在所難免要遇到性能問題。之所以，PL/SQL為我們提供了Profiler API來辨析運行時舉動并相助我們辨認性能瓶頸。PL/SQL也提供了一個Trace API用于追蹤服務器端的程序施行。我們可以施用Trace來追蹤子程序或異常的施行。
一、Profiler API：DBMS_PROFILER包
Profiler API由PL/SQL包DBMS_PROFILER兌現，它提供了征集并保留運行時的統計信息。這些信息會被封存在數據表中，供我們查詢。比如，我們可以曉得PL/SQL每行和每個兒程序施行所花消的時間長短。
要應用Profiler，先開啟一個性能測評對話，充分地運作我們的應用程序以便達到足夠的代碼覆蓋率，其后把收集到的信息保留在數據庫中，終止性能估測對話。具體方法如次：
一.調用DBMS_PROFILER包中的start_profiler進程，把一個詮釋與性能估測對話聯系關系。
二.運作要被估測的應用程序。
三.重復調用歷程flush_data把收集到的數據保留下來并開釋內存儲器。
四.調用stop_profiler進程終止對話。
Profiler會追蹤程序的施行，計算每行和每個兒程序所開銷的時間。我們可以用收集到的數據相助改進性能。比如，我們可以集中處置那些運作慢的子程序。
•分析收集到的性能數據
下一步要判斷出為啥實施某些代碼段或訪問某些數據結構要費用大量的時間。憑借查詢出來的性能數據來找出問題點。把問題集中到那些消耗時間長的子程序和包，盡量的優化SQL話語、循環和遞歸函數等。
•施用追蹤數據改進程序性能
應用我們的分析結果從新編撰那些施行效率低下的算法。比如，在急遽暴脹的數據中，我們可能要亟需運用二分法來頂替線性搜尋。
二、Trace API：包DBMS_TRACE
在大而復雜的應用程序中，很難追蹤子程序的調用。如其施用追蹤API，我們就能看到子程序的施行順序。追蹤API是由包DBMS_TRACE兌現的，并提供了追蹤子程序或異常的服務。
要應用追蹤，先要開啟一個追蹤對話，運作程序，其后終止追蹤對話。應程序執行時，追蹤數據就會把搜集并保留到數據庫中。在一個對話中，我們可以采取如次方法來施行追蹤操作：
一.可選方法，抉擇要追蹤的某個特定的子程序。
二.調用DBMS_TRACE包中的set_plsql_trace開啟追蹤。
三.運作要追蹤的應用程序。
四.調用進程clear_plsql_trace來終止追蹤。
•統制追蹤
追蹤特大型應用程序可能會制造出大量的難以治理的數據。在開啟追蹤事前，我們可以取舍是不是限制要搜集的數據量。
另外，還可以抉擇追蹤級別。比如，如若我們可以抉擇追蹤所有的子程序和異常或是只追蹤選出的子程序和異常。
3、PL/SQL性能優化特點
我們可以運用下邊的PL/SQL特點和步驟來優化應用程序：
一.應用當地動態SQL優化PL/SQL
二.應用批量綁定優化PL/SQL
三.施用NOCOPY編譯器揭示優化PL/SQL
四.應用RETURNING子句優化PL/SQL
五.施用外部程序優化PL/SQL
六.應用對象部類和聚合優化PL/SQL
這些簡略易用的特點可以顯明的增高應用程序的施行速度。
一、運用當地動態SQL優化PL/SQL
有點程序務必要實施一些惟獨在運行時才力確定下來的SQL話語，這些話語被稱為動態SQL話語。早先，要實施動態SQL話語就必須應用包DBMS_SQL。現下，我們可以在PL/SQL中直接運用被稱為本土動態SQL的接口來施行各種動態SQL話語。
本土動態SQL更簡單使用，而且實施速度也要比DBMS_SQL包快。鄙人面的例證中，我們宣言一個游標變量，其后把它與一個能回到數據表emp記要的動態的SELECT話語聯系關系下床：
DECLARE
TYPE empcurtyp IS REF CURSOR;
emp_cv empcurtyp;
my_ename VARCHAR二(15);
my_sal NUMBER := 1000;
BEGIN
OPEN emp_cv FOR 'SELECT ename, sal FROM emp WHERE sal > :s'
USING my_sal;
...
END;
二、應用批量綁定優化PL/SQL
應SQL在聚合的循環內執行時，PL/SQL和SQL發動機間的頻繁切換就會影響到施行速度。比如，下頭的UPDATE話語就在FOR話語中不住發送到SQL發動機：
DECLARE
TYPE numlist IS VARRAY(20) OF NUMBER;
depts numlist := numlist(十, 30, 70, .. .); -- department numbers
BEGIN
...
FOR i IN depts.FIRST .. depts.LAST LOOP
...
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
END LOOP;
END;
在這么的情況下，如若SQL話語影響到四行或更多行數據時，應用批量綁定就會顯著地增高性能。比如，底下的UPDATE話語可以一次就把整個嵌套表的數據發送到SQL發動機中：
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
要想盡最大或許地拔高性能，我們就需要像下部這么編寫程序：
一.如其一條INSERT、UPDATE或DELETE話語在循環內施行，而且摘引到會合中的元素，那么，就把它放到FORALL話語中去。
二.如其SELECT INTO、FETCHE INTO或RETURNING INTO子句引述了一個聚合，那就配合BULK COLLECT子句一行應用。
三.如若或許的話，盡可能在應用程序和服務器其間運用主數組傳遞聚合。
四.如若DML操作失敗時且不是很重大的問題，就可以在FORALL話語中運用SAVE EXCEPTIONS，其后在之后的循環中施用%BULK_EXCEPTIONS屬性匯報或驅除差錯。
不用忽略這些小問題，由于它們可以相助我們分析流程統制和程序的依賴性。
三、施用NOCOPY編譯器提醒優化PL/SQL
默許情況下，OUT和IN OUT形式的參數都是按值傳接的。來講，一個IN OUT實參會把它的復本拷貝到對應的形參中。其后，如其程序實施準確的話，這個值又會從新賦給OUT和IN OUT的實參。
但實參是聚合、記要和對象范例這么的大的數據結構時，生成一個復本會極大地減低施行效率并耗費大量內存儲器的。為懂得決這個問題，我們可以應用編譯器揭示NOCOPY，它能讓編譯器把OUT和IN OUT參數按摘引傳送。下例中，我們就能讓編譯器按摘引傳送IN OUT參數my_unit：
DECLARE
TYPE platoon IS VARRAY(200) OF soldier;
PROCEDURE reorganize(my_unit IN OUT NOCOPY platoon) IS ...
BEGIN
...
END;
END;
四、應用RETURNING子句優化PL/SQL
正常，應用程序亟需失去SQL操作所影響到的行信息。INSERT、UPDATE和DELETE話語都可以包孕一個RETURNING子句，這么就能回來處置過的字段信息。也就不要在INSERT、UPDATE以后或DELETE頭里應用SELECT來查詢影響到的數據。這么也能夠減小網絡流量，縮水CPU時間，急需更小量的游標和服務器內存儲器需求。
鄙人面的事例中，我們就在更新雇職工資的與此同時，把現階段雇員的名稱和新的薪資賦給PL/SQL變量：
PROCEDURE update_salary(emp_id NUMBER) IS
"name" VARCHAR二(15);
new_sal NUMBER;
BEGIN
UPDATE emp
SET sal = sal * 1.1
WHERE empno = emp_id RETURNING ename, sal INTO "name", new_sal;
-- Now do computations involving name and new_sal
END;
五、運用外部程序優化PL/SQL
PL/SQL提供了調用其余語言編撰的程序的接口。PL/SQL可以從程序中調用其余語言所編纂的基準庫。這就能夠增高可重用性、高效率性和程序的模塊化。
PL/SQL是專程用以進展SQL事務處理的。有點任務在像C這么的低階語言中處置起來會愈加有效。
為了增高施行速度，我們可以用C語言從新編纂受計算量限制的程序。除此以外，我們還可以把這么的程序從客戶端移栽到服務器端，這么可以減小網絡流量，更有效地利用資源。
比如，我們用C語言寫一個運用圖形對象門類的步驟，把它打包到動態鏈接庫(DLL)中，并在PL/SQL中登記，其后我們就能從應用程序中調用它。運行時，庫會被動態地加載，為了保險起見，它會在一個獨自的地址空間運作。
六、施用對象部類和聚合優化PL/SQL
會合門類和對象門類在對真實全世界中的實業開展數據建模時能相助我們提高效率。復雜的實業和關系會被直接投射到對象門類中。而且，一個構建很好的對象模型能夠化除多表聯接，減小來回往復之類，故此改進應用程序性能。
客戶端程序，包括PL/SQL程序，可以宣言對象和聚合，把它們作為參數傳接，存放在數據庫中，檢索之類。一樣，對象部類還可以把數據操作進展打包，把數據維護代碼從SQL腳本中移出，把PL/SQL塊放入步驟中。
對象和會合在儲存和檢索方面愈加高效率，由于它們是作為一個通體開展操作的。一樣，對象門類還能和數據庫調整在一起，利用Oracle自身所提供的易擴縮性和性能改進等優點。
七、編譯當地實施的PL/SQL代碼
我們可以把PL/SQL歷程編譯利潤地代碼放到共享庫中，這么就能拔高它的施行速度。歷程還可以被轉換成C代碼，其后用普普通通的C編譯器編譯，聯接到 Oracle過程中。我們可以在Oracle提供的包和我們自個兒編纂的過程中應用這項技術。這么編譯出來的歷程可以在各種服務器環境中工作。由于這項技術對從PL/SQL中調用的SQL話語提高效率并不明顯，之所以它一般應用在計算度高而實施SQL時間未幾的PL/SQL歷程上。
要增高一個或多個進程的施行速度，我們可以這么施用這項技術：
一.更新makefile并為我們的系統鍵入適當的途徑和其余值。makefile途徑是$ORACLE_HOME/plsql/spnc_makefile.mk。
二.經過運用ALTER SYSTEM或ALTER session下令，或經過更新初始化資料，設立參數PLSQL_COMPILER_FLAGS來包孕值NATIVE。默認設置包孕的值是INTERPRETED，我們務必把它從參數值中剔除。
三.運用下部幾個步驟編譯一個或多個進程：
一.施用ALTER PROCEDURE或ALTER PACKAGE下令從新編譯歷程或整個包。
二.剔除歷程并從新創建。
三.施用CREATE OR REPLACE從新編譯歷程。
四.運作SQL*Plus腳本建立一組Oracle系統包。
五.用帶有PLSQL_COMPILER_FLAGS=NATIVE的初始化資料創造數據庫。在創造數據庫時，用UTLIRP腳本運作并編譯Oracle系統包。
四.要確定我們所做的方法是不是有效，可以查詢數據詞典來查看進程是不是是被編譯為當地施行，查詢用的視圖是USER_STORED_SETTINGS、 DBA_STORED_SETTINGS和ALL_STORED_SETTINGS。比如，要查看MY_PROC的狀態，我們可以輸入：
SELECT param_value
FROM user_stored_settings
WHERE param_name = 'PLSQL_COMPILER_FLAGS'
AND object_name = 'MY_PROC';
PARAM_VALUE字段值為NATIVE時，代表進程是被編譯當地實施的，不然便是INTERPRETED。
進程編譯后就會被轉到共享庫，它們會被自動地聯接到Oracle歷程中。我們不需要重新啟動數據庫，或是把共享庫放到除此以外一個地方。我們可以在儲存歷程其間重復調用它們，不管它們是以默許模式(interpreted)編譯，當地實施模式編譯仍是采取兩種混同的編譯模式。
由于PLSQL_COMPILER_FLAGS設立是封存在每個進程的庫單元里的，應被編譯利潤地施行的進程失靈時，在從新編譯的時分還會采取以前的編譯形式。
我們可以透過ALTER SYSTEM或ALTER SESSION下令，或透過設立初始化資料中的參數來統制PL/SQL當地編譯的舉動：
一.PLSQL_COMPILER_FLAGS
二.PLSQL_NATIVE_LIBRARY_DIR (由于保險緣故，不能施用ALTER SESSION開展設立)
三.PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
四.PLSQL_NATIVE_MAKE_UTILITY
五.PLSQL_NATIVE_MAKE_FILE_NAME
編譯當地施行的PL/SQL歷程舉例來說：
CONNECT scott/tiger;
SET serveroutput ON;
ALTER SESSION SET plsql_native_library_dir='/home/orauser/lib';
ALTER SESSION SET plsql_native_make_utility='gmake';
ALTER SESSION SET plsql_native_make_file_name='/home/orauser/spnc_makefile.mk';
ALTER SESSION SET plsql_compiler_flags='NATIVE';
CREATE OR REPLACE PROCEDURE hello_native_compilation AS
BEGIN
dbms_output.put_line('hello world');
SELECT SYSDATE FROM dual;
END;
進程編譯時，我們可以看到各種被執行的編譯和聯接下令。其后歷程就立即可以被調用，直接在Oracle過程中被作為共享庫直接運作
本文來源：
我的異常網
java Exception
Dotnet Exception
Oracle Exception
707 - java.lang.ClassNotFoundException
708 - java.lang.NoClassDefFoundError
709 - org.springframework.beans.factory.BeanCreationException
710 - org.springframework.orm.hibernate3.HibernateSystemException: ids for this class must be manually assigned before calling save
711 - java.lang.ExceptionInInitializerError
712 - og4j:WARN No appenders could be found for logger org.apache.commons.digester.Digester.sax
713 - org.hibernate.exception.GenericJDBCException: Cannot open connection
714 - org.hibernate.exception.SQLGrammarException: Cannot open connection
715 - ervlet class org.springframework.web.servlet.DispatcherServlet for servlet dispatch could not be loaded because the requested class was not found in the classpath
716 - javax.naming.AuthenticationException
717 - java.sql.SQLException: ORA-00907
718 - JAVA異常處理機制
719 - 服務器端拋出異常如何處理
本文來自CSDN博客，轉載請標明出處：http://blog.csdn.net/liangdiwei/archive/2009/12/22/5051849.aspx
