1) Prepare for Studying Performance
1@@@@ Create a package sf_timer for studying performance @@@Create package sf_timer header SYS@ocm> !cat sf_timer_pkg_header.sql CREATE OR REPLACE PACKAGE sf_timer IS /* Specification of get/set for "factor" */ PROCEDURE set_factor ( factor_in IN NUMBER); FUNCTION factor RETURN NUMBER; /* Capture current value in DBMS_UTILITY.get_time */ PROCEDURE start_timer ( context_in IN VARCHAR2 := NULL ); /* Construct message showing time elapsed since call to capture*/ FUNCTION elapsed_message ( prefix_in IN VARCHAR2 := NULL , adjust_in IN NUMBER := 0 , reset_in IN BOOLEAN := TRUE , reset_context_in IN VARCHAR2 := NULL) RETURN VARCHAR2; /* Display message of elapsed time */ PROCEDURE show_elapsed_time ( prefix_in IN VARCHAR2 := NULL , adjust_in IN NUMBER := 0 , reset_in IN BOOLEAN := TRUE ); END sf_timer; / SYS@ocm> @sf_timer_pkg_header.sql Package created. @@@Create package body SYS@ocm> !cat sf_timer_pkg_body.sql CREATE OR REPLACE PACKAGE BODY sf_timer IS g_last_timing NUMBER := NULL; -- store the last time g_last_context VARCHAR2(32767) := NULL; -- store context g_factor NUMBER := NULL; -- for factor g_onoff BOOLEAN := TRUE; -- toggle on/off g_repeats NUMBER := 100; -- for repeat times g_base_timing NUMBER := NULL; -- Calibrated base timing PROCEDURE set_factor ( factor_in IN NUMBER ) IS BEGIN g_factor := factor_in; END set_factor; FUNCTION factor RETURN NUMBER IS retval NUMBER := g_factor; BEGIN RETURN retval; END; /* Save current time and context to package variables */ PROCEDURE start_timer ( context_in IN VARCHAR2 := NULL ) IS BEGIN g_last_timing := DBMS_UTILITY.get_cpu_time; g_last_context := context_in; END; /* Grab the current time before doing anything esle. use get_cpu_time above and get_time here */ FUNCTION elapsed_time RETURN NUMBER IS l_end_time PLS_INTEGER := DBMS_UTILITY.get_cpu_time; BEGIN IF g_onoff THEN --use a huge 2 power 32 ? RETURN ( MOD( l_end_time - g_last_timing + POWER(2,32) , POWER(2,32) ) ); END IF; END; /* Construct message for display of elapsed time. Programmer can include a prefix to the message and also ask that the last timing variable be reset/updated. This saves a separate call to elapsed */ FUNCTION elapsed_message ( prefix_in IN VARCHAR2 := NULL , adjust_in IN NUMBER := 0 , reset_in IN BOOLEAN := TRUE , reset_context_in IN VARCHAR2 := NULL ) RETURN VARCHAR2 IS l_current_timing NUMBER; l_retval VARCHAR2(32767) := NULL; --p01 private function adj_time to adjust time FUNCTION adj_time ( time_in IN BINARY_INTEGER , factor_in IN INTEGER , precision_in IN INTEGER ) RETURN VARCHAR2 IS BEGIN RETURN ( TO_CHAR( ROUND( (time_in - adjust_in) / (100*factor_in) , precision_in ) ) ); END adj_time; --p02 private function formatted_time to FUNCTION formatted_time ( time_in IN BINARY_INTEGER , context_in IN VARCHAR2 := NULL ) RETURN VARCHAR2 IS l_retval VARCHAR2(32767) := NULL; BEGIN IF context_in IS NOT NULL THEN l_retval := ' since '|| g_last_context; END IF; l_retval := prefix_in ||' - Elapsed CPU '|| l_retval ||': ' || adj_time(time_in, 1, 3) || ' seconds.'; IF g_factor IS NOT NULL THEN l_retval := l_retval || ' Factored: ' || adj_time(time_in, g_factor, 5) || ' seconds. '; END IF; RETURN l_retval; END formatted_time; BEGIN IF g_onoff THEN IF g_last_timing IS NULL THEN l_retval := NULL; ELSE /* Construct message with context of last call to elapsed */ l_retval := formatted_time ( elapsed_time(), g_last_context ); g_last_context := NULL; END IF; IF reset_in THEN start_timer ( reset_context_in ); END IF; END IF; RETURN l_retval; END elapsed_message; PROCEDURE show_elapsed_time ( prefix_in IN VARCHAR2 := NULL , adjust_in IN NUMBER := 0 , reset_in IN BOOLEAN := TRUE ) IS BEGIN IF g_onoff THEN DBMS_OUTPUT.put_line ( elapsed_message (prefix_in , adjust_in, reset_in) ); END IF; END show_elapsed_time; END sf_timer; / SYS@ocm> @sf_timer_pkg_body.sql Package body created. 2@@@@Example to use ~ Cache a single value, such as the name of the current user the global variable g_user hold the value of STANDARD.USER. If you calling the function repetitively, It would be return from cache, so more speed then run STANDARD.USER function each time. Use the constant is fastest. SYS@ocm> !cat tmp.sql CREATE OR REPLACE PACKAGE thisuser IS cname CONSTANT VARCHAR2(30) := USER; FUNCTION name RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY thisuser IS g_user VARCHAR2(30) := USER; FUNCTION name RETURN VARCHAR2 IS BEGIN RETURN g_user; END; END; / SYS@ocm> @tmp.sql Package created. Package body created. @@@Package Constant is fastest, but danger, It is public SYS@ocm> !cat test_thisuser.sql CREATE OR REPLACE PROCEDURE test_thisuser ( count_in IN PLS_INTEGER ) IS l_name all_users.username%TYPE; BEGIN ----1. Use package function testuser.name sf_timer.start_timer; FOR i IN 1 .. count_in LOOP l_name := thisuser.name; END LOOP; sf_timer.show_elapsed_time('Packaged Function'); ----2. Use the Package Constant sf_timer.start_timer; FOR i IN 1 .. count_in LOOP l_name := thisuser.cname; END LOOP; sf_timer.show_elapsed_time('Packaged Contant '); ----3. Use the built-in function STANDARD.USER sf_timer.start_timer; FOR i IN 1 .. count_in LOOP l_name := USER; END LOOP; sf_timer.show_elapsed_time('USER Function'); END test_thisuser; / BEGIN test_thisuser(10000000); END; / SYS@ocm> @test_thisuser.sql Procedure created. Packaged Function - Elapsed CPU : 2.03 seconds. Packaged Contant - Elapsed CPU : .47 seconds. USER Function - Elapsed CPU : 78.04 seconds. PL/SQL procedure successfully completed. referencing "Oracle PL/SQL Program Design 5th Edition"