Oracle stored procedure taking more time compared to normal query

Question

I have a Oracle SQL query like below,

select * 
from employees a, department b 
where a.empoyee_id = 10
and a.dept_no = b.dept_no
and a.salary between 10000 and 20000
and a.start_date between date1 and date2 
and a.end_date between date3 and date4

Above query works fine and fetching results in seconds.

But if convert it to a stored procedure like below,

Procedure GETDATA(
   EMP_ID IN NUMBER,
   MIN_SAl IN NUMBER,
   MAX_SAL IN NUMBER,
   MIN_START_DATE IN VARCHAR2,
   MAX_START_DATE IN VARCHAR2, 
   MIN_END_DATE IN VARCHAR2,
   MAX_END_DATE IN VARCHAR2, 
   RESULT OUT dataset
)
IS
BEGIN
open RESULT FOR
   select * from employees a, department b 
   where EMPLOYEE_ID = EMP_ID AND a.dept_no = b.dept_no
    and (MIN_SAl IS NULL OR MAX_SAL IS NULL) OR (a.salary between MIN_SAl and MAX_SAL)
    and (MIN_START_DATE IS NULL OR MAX_START_DATE IS NULL) OR (a.start_date between MIN_START_DATE and MAX_START_DATE)
    and (MIN_END_DATE IS NULL OR MAX_END_DATE IS NULL) OR (a.end_date between MIN_END_DATE and MAX_END_DATE); 
END GETDATA;

exec GETDATA(10, NULL, NULL, NULL, NULL, NULL, NULL, :p)

Above stored procedure takes more than 10 seconds but where as running it alone results come within seconds. I see index is added for all the columns in where clause (salary, start_date, end_date)

I see it takes more time in date criterias. I googled and modified the start_date and end_date datatype to DATE from VARCHAR but still no luck. Why it is taking more time in stored procedure but comes within seconds running as standalone?

The queries are similar but not the same: please post execution plans using https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database/34975420#34975420 — pifor, Jun 12 '20 at 14:38
To verify where exactly is the issue, please run this query as a cursor and then traverse through the cursor and see how much time it takes. Also as suggested paste the execution plan of this query. — Atif, Jun 12 '20 at 14:48
I believe you have a bracket error. Shouldn't it be `AND (min_x IS NULL OR max_x IS NULL or x BETWEEN min_x AND max_x)`? — wolφi, Jun 12 '20 at 18:58

score 0 · Answer 1 · answered Jun 12 '20 at 18:46

Test your query with CTAS. And you will see that it will take almost same time to finish. Almost all code editors brings first available results first when you only select table.

CREATE TABLE TEST
AS
select * 
from employees a, department b 
where a.empoyee_id = 10
and a.dept_no = b.dept_no
and a.salary between 10000 and 20000
and a.start_date between date1 and date2 
and a.end_date between date3 and date4

score 0 · Answer 2 · answered Jun 12 '20 at 19:17

I believe your procedure runs slower than your SQL query because you used the brackets in the wrong order. In the SQL, salary, start- and end_date need to fit, in the procedure salary, start- OR end_date. More rows, more time.

Secondly, the syntax min_x IS NULL or max_x IS NULL or x BETWEEN min_x AND min_y may cause different execution plans. I tested this with a minimal example:

CREATE TABLE t AS SELECT * FROM all_objects;
CREATE INDEX i ON t(created);
EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 't');

SELECT /* my_static */ count(*) 
  FROM t 
  WHERE created BETWEEN DATE '2020-06-06' AND DATE '2020-06-07';

CREATE OR REPLACE PROCEDURE p(d1 DATE, d2 DATE, tag VARCHAR2) 
AS
  stmt VARCHAR2(1000);
  n NUMBER;
BEGIN
  stmt := 'SELECT /* '||tag||' */ count(*) FROM t 
            WHERE created BETWEEN :d1 AND :d2';
  EXECUTE IMMEDIATE stmt INTO n USING d1, d2;
END p;
/

CREATE OR REPLACE PROCEDURE p2(d1 DATE, d2 DATE, tag VARCHAR2) 
AS
  stmt VARCHAR2(1000);
  n NUMBER;
BEGIN
  stmt := 'SELECT /* '||tag||' */ count(*) FROM t 
            WHERE :d1 IS NULL OR :d2 IS NULL OR created BETWEEN :d1 AND :d2';
  EXECUTE IMMEDIATE stmt INTO n USING d1, d2, d1, d2;
END p2;
/

EXEC  p(DATE '2020-06-06', DATE '2020-06-07', 'my_dynamic1');
EXEC p2(DATE '2020-06-06', DATE '2020-06-07', 'my_dynamic2');

Finding the execution plans for stored procedures is a bit more involved, you need to dig around in V$SQLTEXT to find your sql_id, to feed into DBMS_XPLAN:

SELECT * FROM V$SQLTEXT 
 where sql_text LIKE ('%my_static%') 
    or sql_text LIKE ('%my_dynamic%');

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('7agywn91wbmt3',0));

Both SQL and procedure p1 have an identical execution plan

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   FILTER           |      |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| I    | >>325<|  2600 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

However, procedure p2 has a different plan, which need to scan 6840 index rows instead of 325:

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |    51 (100)|          |
|   1 |  SORT AGGREGATE       |      |     1 |     8 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I    |>>6840<| 54720 |    51   (2)| 00:00:01 |
------------------------------------------------------------------------------

Oracle stored procedure taking more time compared to normal query

2 Answers2