CREATE TABLE array_test(x CHAR(10)) TABLESPACE users;
/***************************************************************************************/
【plsql_optimize_level】参数优化范围不包括存储过程中commit行为优化
ALTER SESSION SET plsql_optimize_level=2;
CREATE OR REPLACE PROCEDURE pol_proc AS
v_val INT;
BEGIN
SELECT VALUE
INTO v_val
FROM v$mystat ms
WHERE ms.statistic# =
(SELECT n.statistic#
FROM v$statname n
WHERE n.NAME = 'redo synch writes');
FOR loop_idx IN 1 .. 20
LOOP
INSERT INTO array_test VALUES (loop_idx);
COMMIT;
END LOOP;
SELECT VALUE - v_val
INTO v_val
FROM v$mystat ms
WHERE ms.statistic# =
(SELECT n.statistic#
FROM v$statname n
WHERE n.NAME = 'redo synch writes');
COMMIT;
dbms_output.put_line('The redo writed ' || v_val || ' times.');
END;
/
BEGIN
pol_proc;
END;
/
--The redo writed 0 times.
ALTER SESSION SET plsql_optimize_level=0;
ALTER PROCEDURE pol_proc COMPILE;
BEGIN
pol_proc;
END;
/
--The redo writed 0 times.
CREATE OR REPLACE PROCEDURE pol_proc AS
v_val INT;
BEGIN
SELECT VALUE
INTO v_val
FROM v$mystat ms
WHERE ms.statistic# =
(SELECT n.statistic#
FROM v$statname n
WHERE n.NAME = 'redo synch writes');
FOR loop_idx IN 1 .. 20
LOOP
INSERT INTO array_test VALUES (loop_idx);
COMMIT WRITE IMMEDIATE WAIT;
END LOOP;
SELECT VALUE - v_val
INTO v_val
FROM v$mystat ms
WHERE ms.statistic# =
(SELECT n.statistic#
FROM v$statname n
WHERE n.NAME = 'redo synch writes');
COMMIT;
dbms_output.put_line('The redo writed ' || v_val || ' times.');
END;
/
BEGIN
pol_proc;
END;
/
--The redo writed 20 times.
/***************************************************************************************/
【plsql_optimize_level】参数优化行预取
SELECT COUNT(*) FROM array_test;
--220
ALTER SESSION SET plsql_optimize_level=0;
DECLARE
v_val INT;
BEGIN
SELECT VALUE
INTO v_val
FROM v$segstat t
WHERE t.statistic_name = 'logical reads' AND
t.obj# = (SELECT object_id
FROM user_objects
WHERE object_name = upper('array_test'));
FOR v_cur IN (SELECT x FROM array_test)
LOOP
NULL;
END LOOP;
SELECT VALUE - v_val
INTO v_val
FROM v$segstat t
WHERE t.statistic_name = 'logical reads' AND
t.obj# = (SELECT object_id
FROM user_objects
WHERE object_name = upper('array_test'));
dbms_output.put_line('Logical reads is ' || v_val);
END;
--Logical reads is 240
ALTER SESSION SET plsql_optimize_level=1;
--再次执行匿名块,得到如下结果:
--Logical reads is 224
ALTER SESSION SET plsql_optimize_level=2;
--再次执行匿名块,得到如下结果:
--Logical reads is 16
/***************************************************************************************/
【结论】
1.plsql_optimize_level参数的优化范围不改变存储过程中提交机制;
2.如果你在子程序中使用游标for循环,且在编译前plsql_optimize_
level参数设置为2,则编译器会使用行预取功能。
3.plsql_optimize_level的设置决定了pl/sql编译器单元对待编译对象的
【优化深度】,它的可取舍范围为0-2,设置值越高则表示【你】要求编译
器尽力优化你提交的程序单元;当然【它】的尽力是体现在资源消耗上的。
PS : 如果大家发现这个参数的其它影响范围,还请参与讨论。
参照:
. http://docs.oracle.com/cd/B12037_01/server.101/b10755/initparams163.htm
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28269347/viewspace-746461/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28269347/viewspace-746461/