Avoiding unnecessary function calls to optimize SQL statements

I recently delivered a Code Demo Session in my Organization, which was about "Avoiding unnecessary function calls to optimize SQL statements". With this post I am sharing same details

For this demo I am using my favorite SCOTT schema and created following function "get_dept" with "dbms_lock.sleep(1)" so that every call to a function takes 1 second.

CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
   RETURN dept.dname%TYPE
IS
   l_dname   dept.dname%TYPE;
BEGIN
   DBMS_LOCK.sleep (1);

   SELECT dname
     INTO l_dname
     FROM dept
    WHERE deptno = p_deptno;

   RETURN l_dname;
END;
/

Let me use get_dept function with following simple query on EMP table which has 14 records.

SQL> select empno, ename, deptno, get_dept(deptno) dname from emp;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------------
      7839 KING               10 ACCOUNTING
      7698 BLAKE              30 SALES
      7782 CLARK              10 ACCOUNTING
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7902 FORD               20 RESEARCH
      7369 SMITH              20 RESEARCH
      7499 ALLEN              30 SALES
      7521 WARD               30 SALES
      7654 MARTIN             30 SALES
      7844 TURNER             30 SALES
      7876 ADAMS              20 RESEARCH
      7900 JAMES              30 SALES
      7934 MILLER             10 ACCOUNTING

14 rows selected.
Elapsed: 00:00:14.09

It took near 14 seconds for 14 records as get_dept function was called 14 times once for each row.

Now as we can see here that there are only 3 departments in EMP table, so is there any way we can optimize it by avoiding unnecessary function calls? Yes there are plenty of ways. Lets discuss them one by one.

1) SCALAR SUB-QUERY CACHING
Oracle Database cache results for scalar sub-query basically in a in-memory hash table and does not need to execute them again with the same value, instead Oracle Database uses scalar sub-query cache to get the results. This Cache is maintained by Oracle only for the life time of the query. So if you execute the same query again in same or other session, Oracle will setup a new in-memory hash table for maintaining the cache.

Let us rewrite our query with Scalar Sub-Query and check the performance

SQL> SELECT empno, ename, deptno,
  2         (SELECT get_dept (deptno) FROM DUAL) dname
  3    FROM emp;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------------
      7839 KING               10 ACCOUNTING
      7698 BLAKE              30 SALES
      7782 CLARK              10 ACCOUNTING
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7902 FORD               20 RESEARCH
      7369 SMITH              20 RESEARCH
      7499 ALLEN              30 SALES
      7521 WARD               30 SALES
      7654 MARTIN             30 SALES
      7844 TURNER             30 SALES
      7876 ADAMS              20 RESEARCH
      7900 JAMES              30 SALES
      7934 MILLER             10 ACCOUNTING
14 rows selected.

Elapsed: 00:00:03.05

Wow it got executed in almost 3 seconds, as there where only 3 distinct values in deptno column of EMP table. Oracle seems to use Scalar Sub-Query cache very effectively.


2) DETERMINISTIC Functions:
We can specify a Function DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments. When deterministic function is called Oracle Database attempts to use previously calculated results (in the same fetch) whenever possible, rather than re-executing the function. The calculated values are not usable in current session or any other session. Note: function is cached in SQL, but the caching is limited to a single fetch. A single SQL execution may have multiple fetches.

Let us modify our function to be deterministic and check the performance.

CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
   RETURN dept.dname%TYPE
   DETERMINISTIC
IS
   l_dname   dept.dname%TYPE;
BEGIN
   DBMS_LOCK.sleep (1);

   SELECT dname
     INTO l_dname
     FROM dept
    WHERE deptno = p_deptno;

   RETURN l_dname;
END;
/

SQL> select empno, ename, deptno, get_dept(deptno) dname from emp;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------------
      7839 KING               10 ACCOUNTING
      7698 BLAKE              30 SALES
      7782 CLARK              10 ACCOUNTING
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7902 FORD               20 RESEARCH
      7369 SMITH              20 RESEARCH
      7499 ALLEN              30 SALES
      7521 WARD               30 SALES
      7654 MARTIN             30 SALES
      7844 TURNER             30 SALES
      7876 ADAMS              20 RESEARCH
      7900 JAMES              30 SALES
      7934 MILLER             10 ACCOUNTING

14 rows selected.
Elapsed: 00:00:04.20

As we can see here that execution time went down to near 4 seconds which was originally near 14 seconds. We can notice that using scalar sub-query was little faster because Deterministic cache is maintained per fetch instead of total SQL execution. I prefer to modify my function to deterministic only when I know that this function is used in various SQLs and all SQL can not be modified (or required alot of effort) with scalar sub-query.


3. RESULT CACHE
With Oracle 11g Enterprise Edition, we now have way to maintain the cache across multiple sessions. Result Cache is very helpful with repetitive queries or PL/SQL functions. Cache is stored in Result Cache Area of Shared Pool and same or other session may use the cached values if we run the query again. This feature provides a significant performance benefit with PL/SQL function
being called in SQL or even in any PL/SQL loop. Let us try RESULT_CACHE

CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
   RETURN dept.dname%TYPE
   RESULT_CACHE
IS
   l_dname   dept.dname%TYPE;
BEGIN
   DBMS_LOCK.sleep (1);

   SELECT dname
     INTO l_dname
     FROM dept
    WHERE deptno = p_deptno;

   RETURN l_dname;
END;
/

SQL> select empno, ename, deptno, get_dept(deptno) dname from emp;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------------
      7839 KING               10 ACCOUNTING
      7698 BLAKE              30 SALES
      7782 CLARK              10 ACCOUNTING
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7902 FORD               20 RESEARCH
      7369 SMITH              20 RESEARCH
      7499 ALLEN              30 SALES
      7521 WARD               30 SALES
      7654 MARTIN             30 SALES
      7844 TURNER             30 SALES
      7876 ADAMS              20 RESEARCH
      7900 JAMES              30 SALES
      7934 MILLER             10 ACCOUNTING

14 rows selected.

Elapsed: 00:00:03.03

RESULT_CACHE Worked as expected, took near 3 seconds to execute the query by avoiding the unnecessary function calls and picking up the result from cache. As we know that SUB-QUERY Caching and DETERMINISTIC function cache results for life time of single query execution and RESULT_CACHE can work at multi-session, why not re-run the same query again.

SQL> select empno, ename, deptno, get_dept(deptno) dname from emp;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------------
      7839 KING               10 ACCOUNTING
      7698 BLAKE              30 SALES
      7782 CLARK              10 ACCOUNTING
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7902 FORD               20 RESEARCH
      7369 SMITH              20 RESEARCH
      7499 ALLEN              30 SALES
      7521 WARD               30 SALES
      7654 MARTIN             30 SALES
      7844 TURNER             30 SALES
      7876 ADAMS              20 RESEARCH
      7900 JAMES              30 SALES
      7934 MILLER             10 ACCOUNTING

14 rows selected.

Elapsed: 00:00:00.02

Whoa !!! Zero seconds !!! GET_DEPT function was not ever called once because all the data to be returned was already in Result Cache Area of Shared Pool. If we try this query in another session, it would return results in zero second. RESULT CACHE is a great feature but it comes only with cost of Enterprise Edition.

I hope you have enjoyed reading this article and have learnt something new. Please comments out your thoughts and feedback !!!

Related Posts
- Why Primary Key Foreign Key Relationship and Join Elimination
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- Why my distinct query is not using the Index?
- Auto Increment Column Performance Enhancements
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- Why do I prefer COALESCE over NVL
- DBMS_PROFILER: How to analyze pl/sql performance
- Query optimization tips for Oracle