1、Row-by-Row Processing
1 DECLARE 2 CURSOR C1 IS 3 SELECT Prod_Id, Cust_Id, Time_Id, Amount_Sold 4 FROM Sales 5 WHERE Amount_Sold > 100; 6 C1_Rec C1%ROWTYPE; 7 l_Cust_First_Name Customers.Cust_First_Name%TYPE; 8 l_Cust_Last_Name Customers.Cust_Last_Name%TYPE; 9 BEGIN10 FOR C1_Rec IN C1 LOOP11 -- Query customer details12 SELECT Cust_First_Name, Cust_Last_Name13 INTO l_Cust_First_Name, l_Cust_Last_Name14 FROM Customers15 WHERE Cust_Id = C1_Rec.Cust_Id;16 --17 -- Insert in to target table18 --19 INSERT INTO Top_Sales_Customers20 (Prod_Id,21 Cust_Id,22 Time_Id,23 Cust_First_Name,24 Cust_Last_Name,25 Amount_Sold)26 VALUES27 (C1_Rec.Prod_Id,28 C1_Rec.Cust_Id,29 C1_Rec.Time_Id,30 l_Cust_First_Name,31 l_Cust_Last_Name,32 C1_Rec.Amount_Sold);33 END LOOP;34 COMMIT;35 END;
花费:00:00:10.93
程序声明了游标变量C1,并且使用FOR LOOP游标显式的打开，每一行从游标中检索出来的数据，程序都将查询customer表查询first_name和last_name这两列，随后将向top_sales_customers插入数据。
存在的问题:
即使LOOP中的语句，进行了很高级别的优化，程序执行的过程中，也可能花费大量的时间，假设查询customers花费01秒，insert语句花费0.1秒，那么每次LOOP循环花费0.2秒。如果游标检索出100000条数据，那么总共花费的时间为100000乘以0.2，20000秒大约是5.5小时。
另一个问题是:SQL语句在PL/SQ的循环中，所以执行的过程中，将会造成PL/SQL和SQL引擎的相互交互。这种交互被称为上下文交互，上下文交互增加程序的执行时间，并造成了不必要的CUP负担，所以必须减少上下文的交互，通过减少或者消除这两种环境的交互。
通常你必须避免row-by-row处理数据，如下所示，避免了使用PL/SQL引擎
---- Insert in to target table--INSERT INTO Top_Sales_Customers (Prod_Id, Cust_Id, Time_Id, Cust_First_Name, Cust_Last_Name, Amount_Sold) SELECT s.Prod_Id, s.Cust_Id, s.Time_Id, c.Cust_First_Name, c.Cust_Last_Name, s.Amount_Sold FROM Sales s, Customers c WHERE s.Cust_Id = c.Cust_Id AND s.Amount_Sold > 100;
花费:00:00:00.26
两者花费的时间比较明显，在开发的过程中不推荐一行一行的处理。
2、Nested Row-by-Row Processing
可以在P/SQL中使用嵌套的游标，将从一级游标中查询出来的数据，放置到二级游标中进行输入的插入，查询出来后，在放置到三级游标中进行数据的过滤，然后在进行数据的更新，假如一级查询出20条数，二级查询出30条数，三级查询出来40条数据，那么总的执行次数就是20*30*40数据。
1 DECLARE 2 CURSOR C1 AS 3 SELECT N1 FROM T1; 4 CURSOR C2(p_N1) AS 5 SELECT N1, N2 FROM T2 WHERE N1 = p_N1; 6 CURSOR C3(p_N1, p_N2) AS 7 SELECT Text 8 FROM T3 9 WHERE N1 = p_N110 AND N2 = p_N2;11 BEGIN12 FOR C1_Rec IN C1 LOOP13 FOR C2_Rec IN C2(C1_Rec.N1) LOOP14 FOR C3_Rec IN C3(C2_Rec.N1, C2_Rec.N2) LOOP15 -- execute some sql here; 16 UPDATE … SET ..where n1=c3_rec.n1 AND n2=c3_rec.n2;17 EXCEPTION18 WHEN19 No_Data_Found THEN20 INSERT into… END;21 NULL;22 END LOOP;23 END LOOP;24 END LOOP; COMMIT; END;
除了性能之外，当在进行数据处理的过程中，假如发现了数据，进行更新，没有发现进行数据的插入，可以使用merge来减少SQL引擎和PL/SQL引擎的负载，merge可以将insert和update进行合并，如果存在数据将进行更新，否则进行插入数据。
可以进行重写上述的过程如下:使用merge进行优化
1 MERGE INTO Fact1 2 USING (SELECT DISTINCT C3.N1, C3.N2 3 FROM T1, T2, T3 4 WHERE T1.N1 = T2.N1 5 AND T2.N1 = T3.N1 6 AND T2.N2 = T3.N2) t 7 ON (Fact1.N1 = t.N1 AND Fact1.N2 = t.N2) 8 WHEN MATCHED THEN 9 UPDATE SET ..10 WHEN NOT MATCHED THEN11 INSERT ..;12 COMMIT;
3、Lookup Queries
Lookup Queries经常被用于变量的值构成和执行数据的校验，执行它可能造成性能上的问题
例如:
1 DECLARE 2 CURSOR C1 IS 3 SELECT Prod_Id, Cust_Id, Time_Id, Amount_Sold 4 FROM Sales 5 WHERE Amount_Sold > 100; 6 l_Cust_First_Name Customers.Cust_First_Name%TYPE; 7 l_Cust_Last_Name Customers.Cust_Last_Name%TYPE; 8 l_Country_Id Countries.Country_Id%TYPE; 9 l_Country_Name Countries.Country_Name%TYPE;10 BEGIN11 FOR C1_Rec IN C1 LOOP12 -- Query customer details13 SELECT Cust_First_Name, Cust_Last_Name, Country_Id14 INTO l_Cust_First_Name, l_Cust_Last_Name, l_Country_Id15 FROM Customers16 WHERE Cust_Id = C1_Rec.Cust_Id;17 -- Query to get country_name18 SELECT Country_Name19 INTO l_Country_Name20 FROM Countries21 WHERE Country_Id = l_Country_Id;22 --23 -- Insert in to target table24 --25 INSERT INTO Top_Sales_Customers26 (Prod_Id,27 Cust_Id,28 Time_Id,29 Cust_First_Name,30 Cust_Last_Name,31 Amount_Sold,32 Country_Name)33 VALUES34 (C1_Rec.Prod_Id,35 C1_Rec.Cust_Id,36 C1_Rec.Time_Id,37 l_Cust_First_Name,38 l_Cust_Last_Name,39 C1_Rec.Amount_Sold,40 l_Country_Name);41 END LOOP;42 COMMIT;43 END;
每次都要进行国家名字的查询，这样子效率很不高，有两种优化方式:
1、使用join进行连接。
2、定义一个联合数据，用来缓存结果和在之后的查询中重复利用array，将(country_id, country_name) 在嵌套表中进行键值对的映射，在查询数据的时候，首先使用集合的EXISTS方法，判断，该国家ID对应的名字是否已经映射到了嵌套表中，否则进行查询，将结果放到嵌套表中。如下:
1 DECLARE 2 CURSOR C1 IS 3 SELECT Prod_Id, Cust_Id, Time_Id, Amount_Sold 4 FROM Sales 5 WHERE Amount_Sold > 100; 6 l_Country_Names Country_Names_Type; 7 l_Country_Id Countries.Country_Id%TYPE; 8 l_Country_Name Countries.Country_Name%TYPE; 9 l_Cust_First_Name Customers.Cust_First_Name%TYPE;10 l_Cust_Last_Name Customers.Cust_Last_Name%TYPE;11 TYPE Country_Names_Type IS TABLE OF VARCHAR2(40) INDEX BY PLS_INTEGER;12 l_Country_Names Country_Names_Type;--定义嵌套表13 BEGIN14 FOR C1_Rec IN C1 LOOP15 -- Query customer details16 SELECT Cust_First_Name, Cust_Last_Name, Country_Id17 INTO l_Cust_First_Name, l_Cust_Last_Name, l_Country_Id18 FROM Customers19 WHERE Cust_Id = C1_Rec.Cust_Id;20 -- Check array first before executing a SQL statement21 IF (l_Country_Names.Exists(l_Country_Id)) THEN22 l_Country_Name := l_Country_Names(l_Country_Id);23 ELSE 24 SELECT Country_Name25 INTO l_Country_Name26 FROM Countries27 WHERE Country_Id = l_Country_Id;28 -- Store in the array for further reuse29 l_Country_Names(l_Country_Id) := l_Country_Name;30 END IF;31 --判断嵌套表中是否有值32 -- Insert in to target table33 --34 INSERT INTO Top_Sales_Customers35 (Prod_Id,36 Cust_Id,37 Time_Id,38 Cust_First_Name,39 Cust_Last_Name,40 Amount_Sold,41 Country_Name)42 VALUES43 (C1_Rec.Prod_Id,44 C1_Rec.Cust_Id,45 C1_Rec.Time_Id,46 l_Cust_First_Name,47 l_Cust_Last_Name,48 C1_Rec.Amount_Sold,49 l_Country_Name);50 END LOOP;51 COMMIT;52 END;
基于嵌套表的技巧可以可以应用到其他的场景中，消除一些不必要的工作。
4、Excessive Access to DUAL
过量的访问dual表，导致上下文的交互，会对性能产生伤害，在进行一些数字或者日期的运算的时候，不必要借助dual表来实现，因为在PL/SQL中可以直接访问所有的函数，并进行运算，要减少在程序中访问dual表。
5、Populating Master-Detail Rows
在开发的过程中，可以借用returning子句，来返回相应的信息，一般返回的主键ID，
1 INSERT INTO customers (cust_id, ...)2 VALUES (cust_id_seq.nextval,...)3 RETURNING cust_id into l_cust_id;4 ...5 INSERT INTO customer_transactions (cust_id, ...)6 VALUES (l_cust_id,...)7 ...
但是，也可以先定义一个变量，例如
v_id = sys_guid();insert into table1 values(v_id......);insert into table2 values(sys_guid(),v_id.....)
可以先声明一个变量，在插入数据之前，对ID进行赋值，在进行数据的插入操作。
6、Excessive Function Calls
防止不必要的函数调用，对函数进行优化，可以一次完成的事情，坚决不再两次完成。
7、Costly Function Calls
注意:一般不要在循环中调用函数，假如数据量会造成严重的性能问题。
(这里所说的循环-包括，对一个表中的数据进行查询，包含多行，但是每一行的都要进行函数的调用，所以也造成了循环查询的问题)
可以对函数进行优化，对函数建立索引:例如:
函数:
1 CREATE OR REPLACE FUNCTION calculate_epoch (d in date)2 RETURN NUMBER DETERMINISTIC IS3 l_epoch number;4 BEGIN5 l_epoch := (d - TO_DATE('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))6 * 24 *60 *60 ;7 RETURN l_epoch;8 END calculate_epoch;9 / 建立的索引:
1 CREATE INDEX compute_epoch_fbi ON sales2 (calculate_epoch(time_id))3 Parallel (degree 4);
通过给函数建立索引，可以提高过程的执行效率，大大减少运行时间。
Oracle 11G提供了一个新的解决方案，
在建立函数的时候，为函数设置缓存
1 DROP INDEX compute_epoch_fbi; 2 CREATE OR REPLACE FUNCTION calculate_epoch (d in date) 3 RETURN NUMBER DETERMINISTIC RESULT_CACHE IS 4 l_epoch number; 5 BEGIN 6 l_epoch := (d - TO_DATE('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS')) 7 * 24 *60 *60 ; 8 RETURN l_epoch; 9 END calculate_epoch;10 / 可以使用result_cache来解决问题，当使用该参数后，效率明显提高。
8、Excessive Commits
频繁的提交会造成会产生更多的redo log(重做日志)，需要写日志进程频繁的刷新缓存数据到日志文件，这样会造成数据的不一致性。应该减少提交的次数，可以使用批量提交，大约1000-5000行提交一次数据。
9、Excessive Parsing
不要在PL/SQL的循环中使用LOOP循环，因为每一次执行都会进行解析，造成解析上的问题，但是，相反的可以使用绑定变量来提高性能，避免这个问题。
Summary
SQL is a set language and PL/SQL is a procedural language(SQL是一个集合的语言，但是PL/SQL是一个过程化的语言)
在编写PL/SQL的过程中，应该遵守以下的规则:
• Solve query problems using SQL. Think in terms of sets! It’s easier to tune queries written in SQL than to tune, say, PL/SQL programs having nested loops to essentially execute queries using row-at-a-time processing.(解决查询问题，使用SQL，有时候，SQL语句一句话就可以完成，但是在PL/SQL中，需要使用嵌套循环)
• If you must code your program in PL/SQL, try offloading work to the SQL engine as much as possible. (如果需要在PL/SQL中进行编程，尽可能的减少访问SQL引擎，减少PL/SQL引擎和SQL引擎的交互)• Use bulk processing facilities available in PL/SQL if you must use loop-based processing. Reduce unnecessary work in PL/SQL such as unnecessary execution of functions or excessive access to DUAL (可以使用批量操作，减少循环的处理，减少不必要的函数执行和DUAL表的访问)• Use single-row, loop-based processing only as a last resort.(对于单行数据，在万不得已的时候，使用循环)--