SQL Tuning examples : Part 1

Avoiding unnecessary function calls to optimize SQL statements

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

     

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