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
- 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

24 comments:

  1. 1Prabhakar KaranamMarch 21, 2016 at 9:32 AM

    Nice blog..thanks

    ReplyDelete
  2. Nice Demonstration. Very soft way of explanation.

    Let's say we have 20 different packages in schema, they using several this kind of Inline view query functions like Get_Employee_dept_name( P_department_id in number ) return varchar2, Using result cache feature. isn't it heavy on memory? or simply how to release the cache memory again If if feel some performance issue..

    ReplyDelete
    Replies
    1. Result cache has a defined size, and data is maintained by LRU algo.

      Delete
  3. Very nice demonstration.
    But one point want to bring - Result Cache can't be used in OLTP systems where the table(s) referenced in the function are volatile(transactional in nature), because result cache gets invalidated everytime there's DML activity on the table(s), causing function to be re-executed when the function is called next time.
    Result Cache could have been made a write through cache(similar to buffer cache behavior), that would have helped Queries calling functions(having underlying OLTP tables in them)

    ReplyDelete
    Replies
    1. IMHO, I feel Result Cache can be used in OLTP systems but yes with little caution. OLTP does not mean to modify each and every record for each table, so it depends if you use or abuse a feature.

      Delete
    2. You should use the result cache function only with static datasets (eg, materialized views) or tables which change *much* less frequently than they are queried.

      Delete
  4. Why is their a "sleep" in your code, I agree with you inline SQL is normally faster. Typically, people create helper functions to make their SQL simpler. I like Oracle syntax better than SQL 92 syntax.

    ReplyDelete
    Replies
    1. there is sleep function call to simulate slowness in function of one second per execution.

      Delete
  5. Nice post! Thanks for sharing this.

    A question about deterministic functions- would there be any situations where the function caches an answer too long? For instance, if a row in the table is updated, would future calls to the function pick up the new value?

    ReplyDelete
    Replies
    1. The cache for deterministic function is maintained for the life time of single fetch (a query execution may have multiple fetches to get complete result).
      So if you call the same query/function again (even in same session) the function will be executed again, and there is no chance of getting older data.
      The purpose of deterministic function is to avoid multiple call of single function in a single query execution (actually single fetch).

      RESULT_CACHE can helpful where we want to cache result for multiple execution of same query/function (ever in different sessions), and invalidated cache is maintained by Oracle itself. We should not add result_cache with tables having high DML activities.

      I hope it answers your doubt.

      Delete
  6. Very nice write up. Our records are versioned, 500,000 records with 1 to infinity versions (never seen more than 20 versions). We want the latest version. We have functions setup to get the latest version. I found however, that using the function was slower than putting the exact same code into the sql statement as a subquery in the where. Even though we have indexes, I believe it was doing a full table scan with each call to the function.

    ReplyDelete
  7. Great article Nimish! Not to mention the time spent in switching from SQL to PL/SQL mode.

    ReplyDelete
  8. Thanks.. Good examples

    ReplyDelete
  9. Nice article. Thanks for sharing.

    ReplyDelete
  10. Thanks for the article. I always thought we need an index on the determistic function to work. Isn't it so? And what would be difference between the result_cache and the determistic function?

    ReplyDelete
  11. could you pls tell me real time example for Deterministic function

    ReplyDelete
    Replies
    1. any function which returns same results paired with same parameters, is a candidate for deterministic.

      Delete
    2. I explain the same in interview but interviewer is asking give me real time example

      Delete
    3. he might be asking if u hv used it in ur project

      Delete
    4. hi Nimish
      In the below function you have used keyword DETERMINISTIC
      my question is if we didn't use the keyword DETERMINISTIC then also function will return dname associated with deptno then what is the use of DETERMINISTIC keyword

      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;

      Delete
    5. 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.

      Delete