This week end i read about two kinds of plsql functions, that run faster in sql.
1) plsql functions declared and defined in WITH clause of sql statements ( and be aware of HINT: with_plsql when your top level dml statment is Inser, update, Delete or Merge commands )
2) plsql functions that are defined with UDF (user defined function) pragma.
First, we will start our discussion with WITH clause of sql statements
dba_user@PDB1> create table t
2 as
3 select rownum as x,
4 object_name ,
5 object_type
6 from all_objects ;
Table created.
dba_user@PDB1>
dba_user@PDB1> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
dba_user@PDB1> create or replace function is_number(p_x in varchar2)
2 return varchar2
3 as
4 num_exp exception;
5 pragma exception_init(num_exp,-06502);
6 begin
7 if to_number(p_x) is not null then
8 return 'Y';
9 else
10 return 'N';
11 end if;
12
13 exception
14 when num_exp then
15 return 'N';
16 end;
17 /
Function created.
dba_user@PDB1>
dba_user@PDB1> @d:\script2.sql
dba_user@PDB1> /*
dba_user@PDB1> select x,is_number(object_name),
dba_user@PDB1> is_number(object_type)
dba_user@PDB1> from t;
dba_user@PDB1>
dba_user@PDB1> with function is_num(p_x varchar2)
dba_user@PDB1> return varchar2 is
dba_user@PDB1> num_exp exception;
dba_user@PDB1> pragma exception_init(num_exp,-06502);
dba_user@PDB1> begin
dba_user@PDB1> if to_number(p_x) is not null then
dba_user@PDB1> return 'Y' ;
dba_user@PDB1> else
dba_user@PDB1> return 'N';
dba_user@PDB1> end if;
dba_user@PDB1>
dba_user@PDB1> exception
dba_user@PDB1> when num_exp then
dba_user@PDB1> return 'N';
dba_user@PDB1> end;
dba_user@PDB1> select x,is_num(object_name) ,
dba_user@PDB1> is_num(object_type)
dba_user@PDB1> from t;
dba_user@PDB1> /
dba_user@PDB1>
dba_user@PDB1> */
dba_user@PDB1> set echo off;
And Tkprof shows me this
********************************************************************************
select x,is_number(object_name),
is_number(object_type)
from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 600 1.32 1.26 0 1138 0 89721
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 602 1.32 1.27 0 1138 0 89721
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
89721 89721 89721 TABLE ACCESS FULL T (cr=1138 pr=0 pw=0 time=36630 us cost=154 size=3499119 card=89721)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 600 0.00 0.00
SQL*Net message from client 600 0.00 0.87
********************************************************************************
with function is_num(p_x varchar2)
return varchar2 is
num_exp exception;
pragma exception_init(num_exp,-06502);
begin
if to_number(p_x) is not null then
return 'Y' ;
else
return 'N';
end if;
exception
when num_exp then
return 'N';
end;
select x,is_num(object_name) ,
is_num(object_type)
from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 600 0.84 0.74 0 1138 0 89721
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 602 0.84 0.75 0 1138 0 89721
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
89721 89721 89721 TABLE ACCESS FULL T (cr=1138 pr=0 pw=0 time=49829 us cost=154 size=3499119 card=89721)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 600 0.00 0.00
SQL*Net message from client 600 0.00 0.85
********************************************************************************
WITH Clause sql statements can be used straight away with DDL's but not in DML's
dba_user@PDB1> create table t2 as
2 with function is_num(p_x in varchar2)
3 return varchar2
4 is
5 num_exp exception;
6 pragma exception_init(num_exp,-06502);
7 begin
8 if to_number(p_x) is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when num_exp then
15 return 'N' ;
16 end;
17 select rownum as x, object_name, object_type,
18 is_num(object_name) as flag1
19 from t
20 where rownum <=10;
21 /
Table created.
dba_user@PDB1> truncate table t2;
Table truncated.
dba_user@PDB1> insert into t2
2 with function is_num(p_x in varchar2)
3 return varchar2
4 is
5 num_exp exception;
6 pragma exception_init(num_exp,-06502);
7 begin
8 if to_number(p_x) is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when num_exp then
15 return 'N' ;
16 end;
17 select rownum as x, object_name, object_type,
18 is_num(object_name) as flag1
19 from t
20 where rownum <=10;
21 /
with function is_num(p_x in varchar2)
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause
so do remember your with_plsql hint when you work with WITH clause plsql declarations in dml statements.
dba_user@PDB1> insert /*+ with_plsql */ into t2
2 with function is_num(p_x in varchar2)
3 return varchar2
4 is
5 num_exp exception;
6 pragma exception_init(num_exp,-06502);
7 begin
8 if to_number(p_x) is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when num_exp then
15 return 'N' ;
16 end;
17 select rownum as x, object_name, object_type,
18 is_num(object_name) as flag1
19 from t
20 where rownum <=10;
21 /
10 rows created.
dba_user@PDB1> commit;
Commit complete.
dba_user@PDB1>
dba_user@PDB1> delete /*+ with_plsql */ from t2
2 where (x,flag1) in
3 ( with function is_num(p_x in varchar2)
4 return varchar2
5 is
6 num_exp exception;
7 pragma exception_init(num_exp,-06502);
8 begin
9 if to_number(p_x) is not null then
10 return 'Y';
11 else
12 return 'N';
13 end if;
14 exception
15 when num_exp then
16 return 'N' ;
17 end;
18 select rownum as x, is_num(object_name) as flag1
19 from t
20 where rownum <=10) ;
21 /
10 rows deleted.
dba_user@PDB1> commit;
Commit complete.
dba_user@PDB1> merge /*+ with_plsql */ into t2 using
2 ( with function is_num(p_x in varchar2)
3 return varchar2
4 is
5 num_exp exception;
6 pragma exception_init(num_exp,-06502);
7 begin
8 if to_number(p_x) is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when num_exp then
15 return 'N' ;
16 end;
17 select rownum as x, object_name, object_type,
18 is_num(object_name) as flag1
19 from t
20 where rownum <=10 ) t3
21 on (t2.x = t3.x)
22 when matched then
23 update set t2.flag1 = t3.flag1
24 when not matched then
25 insert (x,object_name,object_type,flag1)
26 values (t3.x, t3.object_name, t3.object_type, t3.flag1)
27 /
10 rows merged.
dba_user@PDB1> commit;
When you write plsql function's that heaving invoked from sql handling huge datasets, PRAGMA UDF compiler directives can be used to execute the statements efficiently.
dba_user@PDB1> create or replace function normal_function(x in varchar2)
2 return number
3 as
4 begin
5 return (length(x));
6 end;
7 /
Function created.
dba_user@PDB1> declare
2 type num_array is table of number;
3 l_array num_array := num_array();
4 l_sql varchar2(4000);
5 l_cursor sys_refcursor;
6 l_cputime number;
7 l_elapstime number;
8 begin
9 l_sql :=' select normal_function(object_name) from t ';
10
11 l_cputime := dbms_utility.get_cpu_time ;
12 l_elapstime := dbms_utility.get_time;
13 open l_cursor for l_sql;
14 fetch l_cursor bulk collect into l_array;
15 close l_cursor;
16
17 dbms_output.put_line ('Normal Function Cpu Time ='||
18 ( dbms_utility.get_cpu_time - l_cputime) ||
19 ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) ) ;
20
21 l_sql := ' with function f(x varchar2) '||
22 ' return number as '||
23 ' begin '||
24 ' return (length(x)); '||
25 ' end; '||
26 ' select f(object_name) from t ' ;
27 l_cputime := dbms_utility.get_cpu_time ;
28 l_elapstime := dbms_utility.get_time;
29 open l_cursor for l_sql;
30 fetch l_cursor bulk collect into l_array;
31 close l_cursor;
32
33 dbms_output.put_line ('WITH Clause Cpu Time ='||
34 ( dbms_utility.get_cpu_time - l_cputime) ||
35 ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) );
36 end;
37 /
Normal Function Cpu Time =30 Elaps Time =30
WITH Clause Cpu Time =6 Elaps Time =6
PL/SQL procedure successfully completed.
dba_user@PDB1>
As you can see there is a huge difference in CPU time, when the same function is redefined as PRAGMA UDF, the difference appears to be zero fold.
dba_user@PDB1>
dba_user@PDB1> create or replace function udf_function(x in varchar2)
2 return number
3 as
4 pragma udf;
5 begin
6 return (length(x));
7 end;
8 /
Function created.
dba_user@PDB1> declare
2 type num_array is table of number;
3 l_array num_array := num_array();
4 l_sql varchar2(4000);
5 l_cursor sys_refcursor;
6 l_cputime number;
7 l_elapstime number;
8 begin
9 l_sql :=' select udf_function(object_name) from t ';
10
11 l_cputime := dbms_utility.get_cpu_time ;
12 l_elapstime := dbms_utility.get_time;
13 open l_cursor for l_sql;
14 fetch l_cursor bulk collect into l_array;
15 close l_cursor;
16
17 dbms_output.put_line ('UDF Function Cpu Time ='||
18 ( dbms_utility.get_cpu_time - l_cputime) ||
19 ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) ) ;
20
21 l_sql := ' with function f(x varchar2) '||
22 ' return number as '||
23 ' begin '||
24 ' return (length(x)); '||
25 ' end; '||
26 ' select f(object_name) from t ' ;
27 l_cputime := dbms_utility.get_cpu_time ;
28 l_elapstime := dbms_utility.get_time;
29 open l_cursor for l_sql;
30 fetch l_cursor bulk collect into l_array;
31 close l_cursor;
32
33 dbms_output.put_line ('WITH Clause Cpu Time ='||
34 ( dbms_utility.get_cpu_time - l_cputime) ||
35 ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) );
36 end;
37 /
UDF Function Cpu Time =8 Elaps Time =7
WITH Clause Cpu Time =6 Elaps Time =5
PL/SQL procedure successfully completed.
dba_user@PDB1>
1) plsql functions declared and defined in WITH clause of sql statements ( and be aware of HINT: with_plsql when your top level dml statment is Inser, update, Delete or Merge commands )
2) plsql functions that are defined with UDF (user defined function) pragma.
First, we will start our discussion with WITH clause of sql statements
dba_user@PDB1> create table t
2 as
3 select rownum as x,
4 object_name ,
5 object_type
6 from all_objects ;
Table created.
dba_user@PDB1>
dba_user@PDB1> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
dba_user@PDB1> create or replace function is_number(p_x in varchar2)
2 return varchar2
3 as
4 num_exp exception;
5 pragma exception_init(num_exp,-06502);
6 begin
7 if to_number(p_x) is not null then
8 return 'Y';
9 else
10 return 'N';
11 end if;
12
13 exception
14 when num_exp then
15 return 'N';
16 end;
17 /
Function created.
dba_user@PDB1>
dba_user@PDB1> @d:\script2.sql
dba_user@PDB1> /*
dba_user@PDB1> select x,is_number(object_name),
dba_user@PDB1> is_number(object_type)
dba_user@PDB1> from t;
dba_user@PDB1>
dba_user@PDB1> with function is_num(p_x varchar2)
dba_user@PDB1> return varchar2 is
dba_user@PDB1> num_exp exception;
dba_user@PDB1> pragma exception_init(num_exp,-06502);
dba_user@PDB1> begin
dba_user@PDB1> if to_number(p_x) is not null then
dba_user@PDB1> return 'Y' ;
dba_user@PDB1> else
dba_user@PDB1> return 'N';
dba_user@PDB1> end if;
dba_user@PDB1>
dba_user@PDB1> exception
dba_user@PDB1> when num_exp then
dba_user@PDB1> return 'N';
dba_user@PDB1> end;
dba_user@PDB1> select x,is_num(object_name) ,
dba_user@PDB1> is_num(object_type)
dba_user@PDB1> from t;
dba_user@PDB1> /
dba_user@PDB1>
dba_user@PDB1> */
dba_user@PDB1> set echo off;
And Tkprof shows me this
********************************************************************************
select x,is_number(object_name),
is_number(object_type)
from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 600 1.32 1.26 0 1138 0 89721
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 602 1.32 1.27 0 1138 0 89721
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
89721 89721 89721 TABLE ACCESS FULL T (cr=1138 pr=0 pw=0 time=36630 us cost=154 size=3499119 card=89721)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 600 0.00 0.00
SQL*Net message from client 600 0.00 0.87
********************************************************************************
with function is_num(p_x varchar2)
return varchar2 is
num_exp exception;
pragma exception_init(num_exp,-06502);
begin
if to_number(p_x) is not null then
return 'Y' ;
else
return 'N';
end if;
exception
when num_exp then
return 'N';
end;
select x,is_num(object_name) ,
is_num(object_type)
from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 600 0.84 0.74 0 1138 0 89721
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 602 0.84 0.75 0 1138 0 89721
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
89721 89721 89721 TABLE ACCESS FULL T (cr=1138 pr=0 pw=0 time=49829 us cost=154 size=3499119 card=89721)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 600 0.00 0.00
SQL*Net message from client 600 0.00 0.85
********************************************************************************
WITH Clause sql statements can be used straight away with DDL's but not in DML's
dba_user@PDB1> create table t2 as
2 with function is_num(p_x in varchar2)
3 return varchar2
4 is
5 num_exp exception;
6 pragma exception_init(num_exp,-06502);
7 begin
8 if to_number(p_x) is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when num_exp then
15 return 'N' ;
16 end;
17 select rownum as x, object_name, object_type,
18 is_num(object_name) as flag1
19 from t
20 where rownum <=10;
21 /
Table created.
dba_user@PDB1> truncate table t2;
Table truncated.
dba_user@PDB1> insert into t2
2 with function is_num(p_x in varchar2)
3 return varchar2
4 is
5 num_exp exception;
6 pragma exception_init(num_exp,-06502);
7 begin
8 if to_number(p_x) is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when num_exp then
15 return 'N' ;
16 end;
17 select rownum as x, object_name, object_type,
18 is_num(object_name) as flag1
19 from t
20 where rownum <=10;
21 /
with function is_num(p_x in varchar2)
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause
so do remember your with_plsql hint when you work with WITH clause plsql declarations in dml statements.
dba_user@PDB1> insert /*+ with_plsql */ into t2
2 with function is_num(p_x in varchar2)
3 return varchar2
4 is
5 num_exp exception;
6 pragma exception_init(num_exp,-06502);
7 begin
8 if to_number(p_x) is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when num_exp then
15 return 'N' ;
16 end;
17 select rownum as x, object_name, object_type,
18 is_num(object_name) as flag1
19 from t
20 where rownum <=10;
21 /
10 rows created.
dba_user@PDB1> commit;
Commit complete.
dba_user@PDB1>
dba_user@PDB1> delete /*+ with_plsql */ from t2
2 where (x,flag1) in
3 ( with function is_num(p_x in varchar2)
4 return varchar2
5 is
6 num_exp exception;
7 pragma exception_init(num_exp,-06502);
8 begin
9 if to_number(p_x) is not null then
10 return 'Y';
11 else
12 return 'N';
13 end if;
14 exception
15 when num_exp then
16 return 'N' ;
17 end;
18 select rownum as x, is_num(object_name) as flag1
19 from t
20 where rownum <=10) ;
21 /
10 rows deleted.
dba_user@PDB1> commit;
Commit complete.
dba_user@PDB1> merge /*+ with_plsql */ into t2 using
2 ( with function is_num(p_x in varchar2)
3 return varchar2
4 is
5 num_exp exception;
6 pragma exception_init(num_exp,-06502);
7 begin
8 if to_number(p_x) is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when num_exp then
15 return 'N' ;
16 end;
17 select rownum as x, object_name, object_type,
18 is_num(object_name) as flag1
19 from t
20 where rownum <=10 ) t3
21 on (t2.x = t3.x)
22 when matched then
23 update set t2.flag1 = t3.flag1
24 when not matched then
25 insert (x,object_name,object_type,flag1)
26 values (t3.x, t3.object_name, t3.object_type, t3.flag1)
27 /
10 rows merged.
dba_user@PDB1> commit;
When you write plsql function's that heaving invoked from sql handling huge datasets, PRAGMA UDF compiler directives can be used to execute the statements efficiently.
dba_user@PDB1> create or replace function normal_function(x in varchar2)
2 return number
3 as
4 begin
5 return (length(x));
6 end;
7 /
Function created.
dba_user@PDB1> declare
2 type num_array is table of number;
3 l_array num_array := num_array();
4 l_sql varchar2(4000);
5 l_cursor sys_refcursor;
6 l_cputime number;
7 l_elapstime number;
8 begin
9 l_sql :=' select normal_function(object_name) from t ';
10
11 l_cputime := dbms_utility.get_cpu_time ;
12 l_elapstime := dbms_utility.get_time;
13 open l_cursor for l_sql;
14 fetch l_cursor bulk collect into l_array;
15 close l_cursor;
16
17 dbms_output.put_line ('Normal Function Cpu Time ='||
18 ( dbms_utility.get_cpu_time - l_cputime) ||
19 ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) ) ;
20
21 l_sql := ' with function f(x varchar2) '||
22 ' return number as '||
23 ' begin '||
24 ' return (length(x)); '||
25 ' end; '||
26 ' select f(object_name) from t ' ;
27 l_cputime := dbms_utility.get_cpu_time ;
28 l_elapstime := dbms_utility.get_time;
29 open l_cursor for l_sql;
30 fetch l_cursor bulk collect into l_array;
31 close l_cursor;
32
33 dbms_output.put_line ('WITH Clause Cpu Time ='||
34 ( dbms_utility.get_cpu_time - l_cputime) ||
35 ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) );
36 end;
37 /
Normal Function Cpu Time =30 Elaps Time =30
WITH Clause Cpu Time =6 Elaps Time =6
PL/SQL procedure successfully completed.
dba_user@PDB1>
As you can see there is a huge difference in CPU time, when the same function is redefined as PRAGMA UDF, the difference appears to be zero fold.
dba_user@PDB1>
dba_user@PDB1> create or replace function udf_function(x in varchar2)
2 return number
3 as
4 pragma udf;
5 begin
6 return (length(x));
7 end;
8 /
Function created.
dba_user@PDB1> declare
2 type num_array is table of number;
3 l_array num_array := num_array();
4 l_sql varchar2(4000);
5 l_cursor sys_refcursor;
6 l_cputime number;
7 l_elapstime number;
8 begin
9 l_sql :=' select udf_function(object_name) from t ';
10
11 l_cputime := dbms_utility.get_cpu_time ;
12 l_elapstime := dbms_utility.get_time;
13 open l_cursor for l_sql;
14 fetch l_cursor bulk collect into l_array;
15 close l_cursor;
16
17 dbms_output.put_line ('UDF Function Cpu Time ='||
18 ( dbms_utility.get_cpu_time - l_cputime) ||
19 ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) ) ;
20
21 l_sql := ' with function f(x varchar2) '||
22 ' return number as '||
23 ' begin '||
24 ' return (length(x)); '||
25 ' end; '||
26 ' select f(object_name) from t ' ;
27 l_cputime := dbms_utility.get_cpu_time ;
28 l_elapstime := dbms_utility.get_time;
29 open l_cursor for l_sql;
30 fetch l_cursor bulk collect into l_array;
31 close l_cursor;
32
33 dbms_output.put_line ('WITH Clause Cpu Time ='||
34 ( dbms_utility.get_cpu_time - l_cputime) ||
35 ' Elaps Time ='|| ( dbms_utility.get_time - l_elapstime) );
36 end;
37 /
UDF Function Cpu Time =8 Elaps Time =7
WITH Clause Cpu Time =6 Elaps Time =5
PL/SQL procedure successfully completed.
dba_user@PDB1>
No comments:
Post a Comment