Efficient function calls for Oracle SQL optimization

Original address: Https://oracle-base.com/articles/misc/efficient-function-calls-from-sql

Http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

1. Description of the problem

We say a function is deterministic (deterministic), and when the same output is always returned for the same input, built-in functions such as ABS in Oracle, no matter how many times the call, ABS (-1) always returns 1. Assuming this function is called in SQL, there is a performance waste if the same input data is present and the function is re-executed each time it is called.

Create TableFunc_test (ID Number);--Inserting DataINSERT  intofunc_testSELECT  Case          when  Level = Ten  Then 3          whenMOD ( Level,2)= 0  Then 2         ELSE 1       END fromDualconnect by  Level <= Ten;COMMIT;

Create or Replace function slow_function (       p_in  numberreturn number     is begin   sys.dbms_lock.sleep (1);   return p_in; End;

Sql> SetTiming on; SQL> SelectSlow_function (ID) fromfunc_test; Slow_function (ID)-----------------                1                2                1                2                1                2                1                2                1                3Tenrows selectedexecutedinch 10.046Seconds

The above SQL execution time is 10.046 seconds, indicating that the Slow_function method is executed for each row of data. Because Slow_function is deterministic: for Slow_function (1) To return 1, the above SQL is causing a performance waste.

2. Scalar Quantum query cache (subquery caching)

The scalar-Quantum query cache reduces the number of SQL-to-function calls through the cached results, and the SQL for the scalar query uses only 3.057 seconds, which means that SQL calls to slow_function occur only three times.

Sql> Select(SelectSlow_function (ID) fromDual fromfunc_test; (selectslow_function (ID) Fromdu------------------------------                             1                             2                             1                             2                             1                             2                             1                             2                             1                             3Tenrows selectedexecutedinch 3.057Seconds

When using a scalar subquery, Oracle creates a small hash table in memory to cache the subquery results, which can cache up to 255 sub-query results:

Select (select Slow_function (ID) from dual) from func_test;
Id	Slow_function (ID)

When the first query Slow_function (1), because the hash table is not cached, so the need to execute slow_function, when the second query Slow_function (1) can be directly from the hash table results, Instead of calling Slow_function again. Even if a hash conflict is possible, and the hash table supports only 255 buckets, the scalar quantum query is always good for performance improvement.

3. Deterministic

Oracle uses keyword deterministic to table names a function is deterministic, and deterministic functions can be used to create functions-based indexes.

Create or Replace function slow_function (       p_in  numberreturn number  Deterministicisbegin  sys.dbms_lock.sleep (1);   return p_in; End;

The deterministic cache is limited by how much data is fetch from the server each time, and the cache is valid only for the lifetime of the current fetch, whereas the scalar subquery is valid within the current query.

Assuming that the SET arraysize 1 deterministic keyword does not help with performance, set ArraySize 15, or only the cached results per 15 data can be reused.

4. Result_cache

Oracle uses the keyword Result_cache to cache the results returned by the function, which can be shared by the session.

Create or Replace function  Number  return number result_cacheisbegin  Sys.dbms_ Lock.sleep (1);   return p_in; End;

Functions that pass the keyword Result_cache are significantly faster after they have been executed.

5. Even though deterministic and result_cache can be improved, we should always use a scalar subquery, because deterinisitic and result_cache are just cache results and do not reduce the switching of SQL and Plsql context quality checks , the interaction between the SQL engine and the Plsql engine will always occur and will not reduce the CPU consumption.

A scalar subquery can also be used in a WHERE clause to improve query performance.

6. Read consistency issues in function calls

Calling function in SQL, assuming that there is also SQL in the function, can occur when the isolation level is read Committed, and it is possible to have non-repeatable read and fantasy reads. Read consistency can be guaranteed through dbms_flashback.

EXEC Dbms_flashback.enable_at_time (systimestamp); SELECT slow_function (ID)  from    func_test; EXEC dbms_flashback.disable;

　　

Efficient function calls for Oracle SQL optimization