098 - [Oracle PL/SQL] Compiler - plsql_optimize_level

최적화 기본값은 2 입니다.

 

 

 

 

 

 

 

• This enables procedure inlining, which is an optimization process that replaces procedure calls with a copy of the body of the procedure to be called.

 

 

 

• 최적화 기본값 확인

col name for a30
col value for a10


SELECT name, value
FROM v$parameter
WHERE name ='plsql_optimize_level' ;
--------------------------------------------
NAME                           VALUE     
------------------------------ ----------
plsql_optimize_level           2

 

 

 

• 최적화 값이 0 인 경우, 실행 시간 측정

alter session set PLSQL_OPTIMIZE_LEVEL = 0;

SELECT name, value
FROM v$parameter
WHERE name ='plsql_optimize_level' ;
--------------------------------------------
NAME                           VALUE     
------------------------------ ----------
plsql_optimize_level           0 



DECLARE
 A NUMBER; 
 B NUMBER; 
 C NUMBER;
 BEGIN
 DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'hh:mi:ss'));
 FOR I IN 1..100000000
 LOOP
 A := 1;
 B := 1;
 C := A+1;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'hh:mi:ss'));

 END;
----------------
11:18:37
11:18:45

 

 

 

• 최적화 값이 2 인 경우, 실행 시간 측정

alter session set PLSQL_OPTIMIZE_LEVEL = 2;

SELECT name, value
FROM v$parameter
WHERE name ='plsql_optimize_level' ;
--------------------------------------------
NAME                           VALUE     
------------------------------ ----------
plsql_optimize_level           2 



DECLARE
 A NUMBER; 
 B NUMBER; 
 C NUMBER;
 BEGIN
 DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'hh:mi:ss'));
 FOR I IN 1..100000000
 LOOP
 A := 1;
 B := 1;
 C := A+1;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'hh:mi:ss'));

 END;
 --------------
11:20:01
11:20:01