PL/SQL functions and cursor fetch size

After reviewing some top SQL performance statistics I noticed one query with executions with the 10,000s but fetches in the millions. The query was embedded in a package routine that simply opened a ref cursor, allowing the associated application to pull the results on demand. After working out how many rows the cursor returned it was clear that the application was fetching one row at a time.

The application opening the cursor was written in C# and used ODP.Net to access the database. By default ODP.Net uses a 64k buffer to fetch data so for some reason it was under the impression that it could only retrieve one row at a time from the cursor. Back to the cursor…

The cursor contained a number of calculated columns and these calculations were done using PL/SQL functions, i.e. it resembled:

SELECT col1
,      col2
,      plsql_func1(...)
,      plsql_func2(...)
FROM   ...

The following package code uses the EMP and DEPT tables to illustrate the problem with the above strategy. The package contains a routine that opens a cursor containing EMP details. Some of the attributes are straight from the table, some are calculated using internal SQL functions and one uses a PL/SQL function to return the department name from the department id.

CREATE OR REPLACE PACKAGE pkg_emp
AS

TYPE t_emp_rec IS RECORD (empno          emp.empno%TYPE
                         ,formatted_name emp.ename%TYPE
                         ,hiredate       emp.hiredate%TYPE
                         ,dname          dept.dname%TYPE
                         ,mgr_flag       VARCHAR2(1));

TYPE t_emp_cur IS REF CURSOR RETURN t_emp_rec;

-- Function to return the department name from it id
FUNCTION dept_name (p_deptno  IN emp.deptno%TYPE)
RETURN dept.dname%TYPE;
   
-- Routine to return employee details   
PROCEDURE get_emps (p_emp_cur OUT t_emp_cur);

END pkg_emp;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp
AS

-- Function to return the department name from it id
FUNCTION dept_name (p_deptno  IN emp.deptno%TYPE)
RETURN dept.dname%TYPE
AS
   l_dname  dept.dname%TYPE;
BEGIN
   SELECT dname
   INTO   l_dname
   FROM   dept
   WHERE  deptno = p_deptno;
   
   RETURN l_dname;
EXCEPTION 
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END dept_name;

-- Routine to return employee details   
PROCEDURE get_emps (p_emp_cur OUT t_emp_cur)
AS
BEGIN
   OPEN p_emp_cur
   FOR
   SELECT e.empno
   ,      INITCAP(e.ename) AS formatted_name -- SQL function calculation
   ,      e.hiredate
   ,      dept_name (e.deptno) AS dname -- PL/SQL function calculation
   ,      CASE WHEN EXISTS (SELECT 'x'
                            FROM   emp e1
                            WHERE  e1.mgr = e.empno)
               THEN 'Y'
               ELSE 'N'
          END AS mgr_flag -- more complex SQL function calculation
   FROM   emp e;
END get_emps;

END pkg_emp;
/

We can get Oracle to tell us how it sees the cursor returned by the GET_EMPS routine by using DBMS_SQL.DESCRIBE. The following displays the cursor columns and the maximum length each can have.

DECLARE
  l_emp_cur   pkg_emp.t_emp_cur;
  l_cur_no    NUMBER;
  l_col_cnt   INTEGER;
  l_rec_tab   DBMS_SQL.DESC_TAB;
BEGIN

  pkg_emp.get_emps (l_emp_cur);
  l_cur_no := DBMS_SQL.TO_CURSOR_NUMBER (l_emp_cur);
 
  DBMS_SQL.DESCRIBE_COLUMNS(l_cur_no, l_col_cnt, l_rec_tab);

  FOR i IN 1..l_col_cnt
  LOOP
     DBMS_OUTPUT.PUT_LINE('Column ' || l_rec_tab(i).col_name || ' max length of ' || TO_CHAR(l_rec_tab(i).col_max_len));
  END LOOP;
 
  DBMS_SQL.CLOSE_CURSOR(l_cur_no);
END;
/

The output from the above is:

Column EMPNO max length of 22
Column FORMATTED_NAME max length of 10
Column HIREDATE max length of 7
Column DNAME max length of 4000
Column MGR_FLAG max length of 1

Looking at the DNAME, which is the PL/SQL function calculated attribute, we see a maximum length of 4,000. This might appear strange given that the function is declared to return a string based on the DEPT.DNAME column, which is defined as VARCHAR2(14). However Oracle only uses the base data type and ignores the length constraint. This means that Oracle will consider the PL/SQL function to be capable of returning a string of the maximum length allowed, which is 4,000 bytes. It’s worth noting that this only affects PL/SQL function calls. Calls to Oracle’s internal functions do not suffer from this problem as evidenced by the FORMATTED_NAME and MGR_FLAG columns.

Going back to the original ORDP.Net application, should a cursor return 8 or more PL/SQL function calculated columns then it will exceed half of the 64k buffer and will be restricted to fetching one row at a time.

How do we overcome this problem? The answer is to wrap the PL/SQL function calls in a CAST to tell Oracle the actual length it can expect from the functions, i.e.:

   SELECT e.empno
   ,      INITCAP(e.ename) AS formatted_name -- SQL function calculation
   ,      e.hiredate
   ,      CAST(dept_name (e.deptno) AS VARCHAR2(14)) AS dname -- PL/SQL function calculation
   ,      CASE WHEN EXISTS (SELECT 'x'
                            FROM   emp e1
                            WHERE  e1.mgr = e.empno)
               THEN 'Y'
               ELSE 'N'
          END AS mgr_flag -- more complex SQL function calculation
   FROM   emp e;

Running our DBMS_SQL.DESCRIBE routine using the CAST function gives us to result we want:

Column EMPNO max length of 22
Column FORMATTED_NAME max length of 10
Column HIREDATE max length of 7
Column DNAME max length of 14
Column MGR_FLAG max length of 1

After adding in the CAST calls the number of fetches required by the C# application dropped to a much more reasonable 2, instead of the 600+ it had been doing previously.

Advertisement