Peter Ramm's Oracle performance analysis stuff

If using user.defined PL/SQL functions in SQL, it is crucial for the optimizer to know if a function is deterministic or not. Deterministic means, that the result of a function call is stable for subsequent calls with the same call paramater values. You need to tell the optimizer that the result can be considered as stable by adding the keyword DETERMINISTIC to the function declaration. CREATE OR REPLACE FUNCTION MyFunc() RETURN NUMBER DETERMINISTIC ... If knowing that a function is deterministic, the SQL engine can cache the function results for repeated calls of this function. This may make a huge difference for the SQL execution effort. Consider an example: A table MyTab has 1 mio. records. A package function does some calculations returning a value that is used as filter criteria at access on MyTab. CREATE TABLE MyTab(ID NUMBER); INSERT INTO MyTab SELECT Level FROM DUAL CONNECT BY LEVEL Now let's check what happens. SET SERVEROUTPUT ON; SELECT * FROM MyTab WHERE ID = My...