Problems with PL/SQL functions in SQL

Many Oracle database developers prefer PL/SQL functions, storage, and so on to simplify the code.

Recommended reading:

Use PL/SQL to execute java storage to obtain the MAC address

For example:

Select empno, ENAME, DNAME, loc from emp, dept where emp. DEPTNO = DEPT. DEPTNO;

Developers may think that such an SQL statement is too long (Here we assume that the SQL statement is too long). They like to use functions like this:

Create function F_GETDEPTINFO (pdeptno number, PTYPE VARCHAR2)

RETURN VARCHAR2

V_DEPTINFO VARCHAR2 (50 );

BEGIN

If ptype = 'dname' THEN

Select dname into V_DEPTINFO from dept where deptno = PDEPTNO;

End if;

If ptype = 'loc 'THEN

Select loc into V_DEPTINFO from dept where deptno = PDEPTNO;

End if;

RETURN V_DEPTINFO;

END;

 

Some also add exception handling to the function and return some custom values (I have not added them here)

Finally, the SQL statement is rewritten as: SELECT EMPNO, ENAME, F_GETDEPTINFO (DEPTNO, 'dname'), F_GETDEPTINFO (DEPTNO, 'loc ') FROM EMP;

In this way, developers think that SQL is much more concise, and some may think that performance will improve. PL/SQL functions are frequently used in SQL, especially when processing complex business logic.

Because you are unfamiliar with the database, especially not good at SQL table connection and other methods, the use of a large number of functions leads to increasingly slow application changes. therefore, we recommend that TX developers learn more about the database principles.

Here, we will analyze several problems that exist when PL/SQL functions are used in SQL:

1. those familiar with oracle databases know that SQL engines and PL/SQL engines exist in oracle databases, which are used to process SQL statements and PLSQL statements respectively. after 9i, SQL statements and PL/SQL statements can share the same

Parser, but the parsed statement will still be switched between the two engines, which will inevitably bring performance overhead.

Example:

Directly execute SQL statements

At 11:17:33 SCOTT @ orcl> set autot trace
At 11:18:05 SCOTT @ orcl> select rownum from dual connect by rownum <= 1e6;

1000000 rows selected.

Elapsed: 00:00:10. 03

Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
0 consistent gets
0 physical reads
0 redo size
11846828 bytes sent via SQL * Net to client
733734 bytes encoded ed via SQL * Net from client
66668 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000000 rows processed

We can see that the performance is basically on the network I/O. It takes 10.03 seconds.

Next we will use PL/SQL functions in SQL to accomplish the same function.

At 11:25:11 SCOTT @ orcl> create function plsql_function (p_number in number)
11:25:14 2 RETURN NUMBER
11:25:14 3 BEGIN
11:25:14 4 RETURN p_number;
11:25:14 5 END plsql_function;
11:25:15/

Function created.

Elapsed: 00:00:00. 04
At 11:25:28 SCOTT @ orcl> SELECT plsql_function (ROWNUM) t FROM dual connect by rownum <= 1e6;

1000000 rows selected.

Elapsed: 00:00:13. 50

Statistics
----------------------------------------------------------
17 recursive cballs
0 db block gets
24 consistent gets
0 physical reads
0 redo size
11846823 bytes sent via SQL * Net to client
733734 bytes encoded ed via SQL * Net from client
66668 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000000 rows processed

In this case, not only the network I/O, but also the cache I/O takes 13.50 seconds.

With autotrace, we feel that using PL/SQL functions in SQL is slower than using SQL statements directly.

Note: The AUTOTRACE information is not obvious here. You can use the DBMS_HPROF package introduced earlier to view detailed performance information.

Here we will analyze the SQL _TRACE of the above two statements.

The SQL _TRACE directly queried using SQL statements is as follows:

Parsing in cursor #11 len = 48 dep = 0 uid = 84 oct = 3 lid = 84 tim = 1359750169623584 hv = 2961471920 ad = '3a9180b0' sqlid = '67j3zkks88ydh'
Select rownum from dual connect by rownum <= 1e6
END OF STMT
PARSE #11: c = 5999, e = 5932, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 0, og = 1, plh = 1731520519, tim = 1359750169623579
EXEC #11: c = 0, e = 218, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, plh = 1731520519, tim = 1359750169624022
FETCH #11: c = 1000, e = 194, p = 0, cr = 0, cu = 0, mis = 0, r = 1, dep = 0, og = 1, plh = 1731520519, tim = 1359750169624591
FETCH #11: c = 0, e = 136, p = 0, cr = 0, cu = 0, mis = 0, r = 15, dep = 0, og = 1, plh = 1731520519, tim = 1359750169625626
...
FETCH #11: c = 0, e = 17, p = 0, cr = 0, cu = 0, mis = 0, r = 15, dep = 0, og = 1, plh = 1731520519, tim = 1359750173026323

* ** 04:23:05. 560
STAT #11 id = 1 cnt = 22441 pid = 0 pos = 1 obj = 0 op = 'count (cr = 0 pr = 0 pw = 0 time = 51932 us )'
STAT #11 id = 2 cnt = 22441 pid = 1 pos = 1 obj = 0 op = 'connect by without filtering (cr = 0 pr = 0 pw = 0 time = 30133 us)'
STAT #11 id = 3 cnt = 1 pid = 2 pos = 1 obj = 0 op = 'fast DUAL (cr = 0 pr = 0 pw = 0 time = 0 us cost = 2 size = 0 card = 1)'
CLOSE #11: c = 1000, e = 511, dep = 0, type = 0, tim = 1359750185560442

It can be seen that parsing takes a little TIME, and the subsequent EXE does not produce CPU time, and only 218 of elapse TIME.

 

Let's take a look at the trace of the SQL statement using PL/SQL functions:

Parsing in cursor #10 len = 66 dep = 0 uid = 84 oct = 3 lid = 84 tim = 1359749602256931 hv = 2764084342 ad = '3a90d680 'sqlid = '9739cfkkc153q'
SELECT plsql_function (ROWNUM) t FROM dual connect by rownum <= 1e6
END OF STMT
PARSE #10: c = 7999, e = 9102, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 0, og = 1, plh = 1731520519, tim = 1359749602256928
EXEC #10: c = 1000, e = 151, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, plh = 1731520519, tim = 1359749602257357
FETCH #10: c = 0, e = 186, p = 0, cr = 0, cu = 0, mis = 0, r = 1, dep = 0, og = 1, plh = 1731520519, tim = 1359749602257894
...
FETCH #10: c = 95986, e = 95756, p = 0, cr = 0, cu = 0, mis = 0, r = 9, dep = 0, og = 1, plh = 1731520519, tim = 1359749617924141
STAT #10 id = 1 cnt = 1000000 pid = 0 pos = 1 obj = 0 op = 'count (cr = 0 pr = 0 pw = 0 time = 2208907 us )'
STAT #10 id = 2 cnt = 1000000 pid = 1 pos = 1 obj = 0 op = 'connect by without filtering (cr = 0 pr = 0 pw = 0 time = 1259599 us)'
STAT #10 id = 3 cnt = 1 pid = 2 pos = 1 obj = 0 op = 'fast DUAL (cr = 0 pr = 0 pw = 0 time = 0 us cost = 2 size = 0 card = 1)'

The parsing time, execution time, and FETCH are all higher than above.

 

In particular, the performance of user-defined functions is lower than that of built-in functions, for example, some developed TX listparts functions that do not know 11g can convert a single column to a single row (each element of an ordered connection column). They write their own functions to complete this function, in the end, its performance is not as good as the built-in functions.

1 2 Next Page