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 BEGIN 10 FOR C1_Rec IN C1 LOOP 11 -- Query customer details 12 SELECT Cust_First_Name, Cust_Last_Name 13 INTO l_Cust_First_Name, l_Cust_Last_Name 14 FROM Customers 15 WHERE Cust_Id = C1_Rec.Cust_Id; 16 -- 17 -- Insert in to target table 18 -- 19 INSERT INTO Top_Sales_Customers 20 (Prod_Id, 21 Cust_Id, 22 Time_Id, 23 Cust_First_Name, 24 Cust_Last_Name, 25 Amount_Sold) 26 VALUES 27 (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_N1 10 AND N2 = p_N2; 11 BEGIN 12 FOR C1_Rec IN C1 LOOP 13 FOR C2_Rec IN C2(C1_Rec.N1) LOOP 14 FOR C3_Rec IN C3(C2_Rec.N1, C2_Rec.N2) LOOP 15 -- execute some sql here; 16 UPDATE … SET ..where n1=c3_rec.n1 AND n2=c3_rec.n2; 17 EXCEPTION 18 WHEN 19 No_Data_Found THEN 20 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 THEN 11 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 BEGIN 11 FOR C1_Rec IN C1 LOOP 12 -- Query customer details 13 SELECT Cust_First_Name, Cust_Last_Name, Country_Id 14 INTO l_Cust_First_Name, l_Cust_Last_Name, l_Country_Id 15 FROM Customers 16 WHERE Cust_Id = C1_Rec.Cust_Id; 17 -- Query to get country_name 18 SELECT Country_Name 19 INTO l_Country_Name 20 FROM Countries 21 WHERE Country_Id = l_Country_Id; 22 -- 23 -- Insert in to target table 24 -- 25 INSERT INTO Top_Sales_Customers 26 (Prod_Id, 27 Cust_Id, 28 Time_Id, 29 Cust_First_Name, 30 Cust_Last_Name, 31 Amount_Sold, 32 Country_Name) 33 VALUES 34 (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 BEGIN 14 FOR C1_Rec IN C1 LOOP 15 -- Query customer details 16 SELECT Cust_First_Name, Cust_Last_Name, Country_Id 17 INTO l_Cust_First_Name, l_Cust_Last_Name, l_Country_Id 18 FROM Customers 19 WHERE Cust_Id = C1_Rec.Cust_Id; 20 -- Check array first before executing a SQL statement 21 IF (l_Country_Names.Exists(l_Country_Id)) THEN 22 l_Country_Name := l_Country_Names(l_Country_Id); 23 ELSE 24 SELECT Country_Name 25 INTO l_Country_Name 26 FROM Countries 27 WHERE Country_Id = l_Country_Id; 28 -- Store in the array for further reuse 29 l_Country_Names(l_Country_Id) := l_Country_Name; 30 END IF; 31 --判断嵌套表中是否有值 32 -- Insert in to target table 33 -- 34 INSERT INTO Top_Sales_Customers 35 (Prod_Id, 36 Cust_Id, 37 Time_Id, 38 Cust_First_Name, 39 Cust_Last_Name, 40 Amount_Sold, 41 Country_Name) 42 VALUES 43 (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 IS 3 l_epoch number; 4 BEGIN 5 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 sales 2 (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.(对于单行数据，在万不得已的时候，使用循环)
--