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.
Nicely done, excellent tip. Of course, in simple cases like this it would be even better to remove the PL/SQL function call entirely – but in more complex cases it might not be feasible.
You’re quite right Jeff, I definitely discourage the use of PL/SQL functions within SQL. The use of the functions in this particular application are generally warranted for various reasons. Those reasons are slowly being engineered out so a future release might see the removal of the function calls entirely. Steps are taken to reduce the PL/SQL overhead with things like results caching too.
I use Windows 8.1 and PLSQL DEVELOPER.
How can I view Output using
alter session set events ‘10046 trace name context forever, level 4’
If you really mean PLSQL Developer, a product from Allround Automations (https://www.allroundautomations.com/plsqldev.html) then unfortunately I’m not familiar with that product and cannot say how DBMS_OUTPUT can be viewed. If you meant Oracle’s SQL Developer product (http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index-097090.html) then you can view the output by bringing up the DBMS_OUTPUT window (menu item: View | Dbms_Output) and using the green plus icon to add your database connection to it.
As for the trace file, the files are written to a directory on the database server and are not displayed via DBMS_OUTPUT. Assuming you’re using Oracle 11g or later you can find the name of the trace file using the following query from within your session:
WHERE name = ‘Default Trace File’
I hope this helps you.
Pingback: Performance DataAccess Oracle and ODP.NET « El TecnoBaúl de Kiquenet