Use PL/SQL functions in SQL

In SQL, PL/SQL functions are used. Many oracle database developers prefer PL/SQL functions and storage to simplify the code. for example, select empno, ENAME, DNAME, loc from emp, dept where emp. DEPTNO = DEPT. DEPTNO; for such an SQL statement, developers may think it is too long (Here we suppose it is too long). They like to use functions, such as: CREATE FUNCTION F_GETDEPTINFO (PDEPTNO NUMBER, PTYPE VARCHAR2) RETURN VARCHAR2 ASV_DEPTINFO VARCHAR2 (50); beginif ptype = 'dname' thenselect dname into V_DEPTINFO from dept where deptno = PDEPTNO; end if; if ptype = 'loc 'thenselect LOC INTO V_DEPTINFO from dept where deptno = PDEPTNO; end if; RETURN V_DEPTINFO; END; some will add exception handling to the function and RETURN some custom values. (I didn't add it here.) Finally, the SQL statement is rewritten to 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. familiar with oracle Data The database owner knows that the oracle database contains the SQL engine and PL/SQL engine, which are used to process SQL statements and PLSQL statements respectively. although after 9i, SQL statements and PL/SQL statements can share the same parser, the parsed statements will still be switched between the two engines, which will inevitably lead to performance overhead. example: directly execute the SQL statement [SQL] 11:17:33 SCOTT @ orcl> set autot trace 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 consi Stent 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 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. [SQL] 11:25:11 SCOTT @ orcl> CREATE FUNCTION plsql_function (p_number IN NUMBER) 11:25:14 2 RETURN NUMBER AS 11:25:14 3 BEGIN 11:25:14 4 RETURN p_number; 11:25:14 5 END plsql_function; 11:25:15 6/Function created. elapsed: 00:00:00. 04 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 118 46823 bytes sent via SQL * Net to client 733734 bytes provisioned ed via SQL * Net from client 66668 SQL * Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1000000 rows processed not only has network I/O, but also has cache I/O. the time consumed is 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 <= 1e6END of stmtparse #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 you can see that the parsing takes a little time, the subsequent EXE does not generate cpu time, and only has 218 elapse time. let's take a look at the trace: P of the SQL statement using PL/SQL functions. Arsing 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 <= 1e6END of stmtparse #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 p OS = 1 obj = 0 op = 'fast DUAL (cr = 0 pr = 0 pw = 0 time = 0 us cost = 2 size = 0 card = 1) 'We can see that the resolution time, execution time, and FETCH are 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. 2. there is still a serious problem when PL/SQL functions are used in SQL. there is a situation where the read consistency is damaged. for example, CREATE a test TABLE EMP_TEST [SQL] 13:19:18 SCOTT @ orcl> CREATE Table EMP_TEST AS 13:19:27 2 SELECT ROWNUM R FROM DUAL CONNECT BY LEVEL <1000; TABLE created. elaps Ed: 00:00:00. 20 here we use the sleep function of DBMS_LOCK to simulate the execution of time-consuming SQL statements: [SQL] 13:35:18 SCOTT @ orcl> CREATE OR REPLACE FUNCTION sleepr (p_sleep IN NUMBER) 13:36:30 2 return number as 13:36:30 3 BEGIN 13:36:30 4 dbms_lock.sleep (1); 13:36:30 5 RETURN 0; 13:36:30 6 END sleepr; 13:36:31 7/Function created. elapsed: 00:00:00. 03 CREATE a test FUNCTION: [SQL] CREATE OR REPLACE FUNCTION fun_test (p_number IN NUMBER) RETURN NUMBER AS v emp _ Test. r % type; BEGIN select r into v from emp_test where r = p_number; RETURN v; END fun_test; in SESSION 1, we execute this SQL: -- SESSION 1: [SQL] 13:38:02 SCOTT @ orcl> SELECT fun_test (r) 13:38:08 2, r 13:38:08 3, sleepr (1) 13:38:08 4 FROM emp_test 13:38:08 5 WHERE rownum <20; FUN_TEST (R) r sleepr (1) ----------- ---------- 1 1 0 2 2 0 3 3 0 4 0 null 5 0 null 6 0 null 7 0 null 8 0 null 9 0 null 10 0 null 11 0 null 12 0 null 13 0 null 14 0 15 15 0 16 16 0 17 0 18 18 0 19 19 0 19 rows selected. elapsed: 00:00:23. 87 Note: set null 'null' is SET in SQLPLUS. The SQL statement we run in SESSION 2 is as follows: -- SESSION 2: [SQL] 13:37:02 SCOTT @ orcl> UPDATE emp_test SET r = 0 WHERE r <15; 14 rows updated. elapsed: 00:00:00. 01 13:38:14 SCOTT @ orcl> commit; Commit complete. elapsed: 00:00:00. 00 pay attention to the time when two SQL statements are executed. After Session 1 executes the SQL statement, execute the SQL statement of Session 2 and submit it immediately (pay attention to the time before the command line) according to the read consistency Session 2 is not updated when Session 1 is queried. Therefore, the result of Session 1 should be the result before the update, however, the update of Session 2 directly affects session 1, that is, the read consistency is damaged. due to SESSION 2 update, the corresponding R value cannot be found in the PLSQL function executed by SESSION 1, so the returned result is blank. this is dangerous because it does not get the expected result. in this case, the general solution is: 1.SET transaction read only; 2.FOR UPDATE locking; 3. use flashback query to query data at a specified time; 4. set the transaction isolation level to serialize. In general, this situation occurs. Developers usually use 2, locking, which is another performance overhead... 3. CBO is difficult to optimize PL/SQL functions that appear in SQL conditions, because PL/SQL functions in SQL do not have statistical information. For CBO, the most important thing is the statistical information. therefore, CBO uses its default analysis and processing method to treat PL/SQL functions. example: [SQL] 13:19:18 SCOTT @ orcl> CREATE TABLE EMP_TEST AS 13:19:27 2 SELECT ROWNUM R FROM DUAL CONNECT BY LEVEL <1000; Table created. elapsed: 00:00:00. 20 13:19:28 SCOTT @ orcl> EXEC DBMS_STATS.GATHER_TABLE_STATS (user, 'emp_test'); PL/SQL procedure successfully completed. elapsed: 00:00:00. 19 13:20:08 SCOTT @ orcl> create or replace function fun_test (p_number in number) 13:20:20 2 return number as 13:20:20 3 BEGIN 13:20:20 4 RETURN p_number; 13:20:20 5 END fun_test; 13:20:21 6/Function created. elapsed: 00:00:00. 01 13:20:22 SCOTT @ orcl> alter session set events '10053 trace name context forever, level 1'; Session altered. elapsed: 00:00:00. 00 13:20:36 SCOTT @ orcl> set autotrace traceonly Explain 13:20:45 SCOTT @ orcl> SELECT * FROM EMP_TEST 13:20:54 2 WHERE FUN_TEST (R) = 1; Elapsed: 00:00:00. 06 Execution Plan hash value: 3124080142 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ----------------------------------------------------- ------------------------- | 0 | select statement | 10 | 40 | 3 (0) | 00:00:01 | * 1 | table access full | EMP_TEST | 10 | 40 | 3 (0) | 00:00:01 | identified by operation id: --------------------------------------------------------------- 1-filter ("FUN_TEST" ("R") = 1) 13:21:18 SCOTT @ orcl> alter session set ev Ents '10053 trace name context off'; Session altered. elapsed: 00:00:00. 00 view TRACE information generated by 10053: * ************************************* BASE statistical information ************************ Table Stats:: Table: EMP_TEST Alias: EMP_TEST # Rows: 999 # Blks: 5 AvgRowLen: 4.00 Access path analysis for EMP_TEST ********************************** * *** single table access path Single Table Cardinality Estimation f Or EMP_TEST [EMP_TEST] No default cost defined for function FUN_TEST No default selecti1_defined for function FUN_TEST Table: EMP_TEST Alias: EMP_TEST Card: Original: 999.000000 Rounded: 10 Computed: 9.99 Non Adjusted: 9.99 Access Path: TableScan Cost: 3.10 Resp: 3.10 Degree: 0 Cost_io: 3.00 Cost_cpu: 3232407 Resp_io: 3.00 Resp_cpu: 3232407 Best: AccessPath: TableScan Cost: 3.10 Degree: 1 Resp: 3.10 Card: 9.99 Bytes: 0CBO does not have the statistical information of the function FUN_TEST and cannot obtain the selection degree. Therefore, the default 1% selection degree is used for processing. although PL/SQL functions in SQL have many problems and performance overhead, oracle has been trying to narrow the gap between SQL and PL/SQL. For example, you can use HINT such as NO_MERGE and QB_NAME to reduce the number of PL/SQL function executions in SQL, oracle also provides function result set caching, scalar query caching, and DETERMINISTIC functions to reduce the number of function executions. for CBO, oracle also provides function-based indexes, provides extended statistics, extended optimizer, and custom statistics functions to improve the performance of PL/SQL functions in SQL. --- but in general, we should try our best to use SQL instead of PL/SQL functions (that is, the problems that can be solved with SQL, and never use PL/SQL ), if PL/SQL functions are required, you can use views, create virtual columns, and other technologies to avoid using PL/SQL functions in SQL.