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.