With Enhancement

While going through Oracle Database 12c New features, i saw this
better PLSQL from SQL itself. Starting Oracle Database 12c we can
have PLSQL like funtions/procedure right with in the SQL itself
and by doing this we can eliminate the context switches that might
have been required previous while executing our PLSQL functions/
procedures from a SQL statement.

So in this post i will be exploring this new feature. So first
lets create our test table and then populate it with one million
rows.

drop table t1 purge;

Table dropped.

create table t1(
n1	number
, v1	varchar2(10)
, v2	varchar2(10)
);

Table created.

insert into t1
select
	rownum
	, dbms_random.string('U', 10)
	, dbms_random.string('U', 10)
from
	dual
connect by
	level <= 1e5
;

100000 rows created.

Now i will create a function in a PLSQL package to compare the
execution times for the two executins.

create or replace package pkg
as
	function get_full_name(
		p_first varchar2
		, p_last varchar2
	)
	return varchar2;
end;
/

Package created.

show err

No errors.

create or replace package body pkg
as
	function get_full_name(
		p_first varchar2
		, p_last varchar2
	)
	return varchar2
	as
	begin
		return p_first || ' ' || p_last;
	end;
end;

Package body created.

/
show err

No errors.

Now lets get the full name firstly using the above create function
followed by putting the same logic with in SQL statements itself
and retrieve the records.

set timi on
set autotrace traceonly
select
	n1
	, pkg.get_full_name(v1, v2)
from
	t1
;

100000 rows selected.

Elapsed: 00:00:02.15

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   120K|  3167K|   137   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   120K|  3167K|   137   (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         43  recursive calls
          1  db block gets
       7241  consistent gets
          0  physical reads
        184  redo size
    4082820  bytes sent via SQL*Net to client
      73869  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed


with
	function get_full_name(
		p_first varchar2
		, p_last varchar2
	)
	return varchar2
	as
	begin
		return p_first || ' ' || p_last;
	end;
select
	n1
	, get_full_name(v1, v2)
from
	t1
/

100000 rows selected.

Elapsed: 00:00:01.74

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   120K|  3167K|   137   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   120K|  3167K|   137   (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       7188  consistent gets
          0  physical reads
          0  redo size
    4082816  bytes sent via SQL*Net to client
      73869  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

We can see that although all the statistics from execution plans are
quite yet we see our execution for the statement having placed the
logic within SQL is faster.
1.74sec as to that of 2.15sec
((2.15-1.74)/2.15)*100 = 19% faster, this all has been gained by avoiding
context switched between SQL engine and PLSQL engine.

Update: 24-Mar-2014

Today when i was going through an post by Jonathan Lewis http://jonathanlewis.wordpress.com/2013/07/01/12c-determinism/
I thought what the h*** they are doing to determistic function and Oracle is not taking advantage in this new Enhancement. So i thought to give it try and check out myself and here below is my test

drop table t1 purge;

create table t1
as
select
	1	n1
from
	all_objects
where
	rownum <= 10
;

begin
	dbms_stats.gather_table_stats(
		user
		, 't1'
	);
end;
/

create or replace function plsql_function(p_n	number)
return number
deterministic
is
begin
	dbms_output.put_line('plsql Function');
	return p_n;
end;
/
show err

set arraysize 20

with
	function sql_function(p_n	number)
	return number
	deterministic
	is
	begin
		dbms_output.put_line('sql Function');
		return p_n;
	end;
select
	plsql_function(n1)
	, sql_function(n1)
from
	t1
/

drop function plsql_function;

Output of the above script is as following which matches to that of Jonathan Lewis’s.


Table dropped.


Table created.


PL/SQL procedure successfully completed.


Function created.

No errors.

PLSQL_FUNCTION(N1) SQL_FUNCTION(N1)
------------------ ----------------
                 1                1
                 1                1
                 1                1
                 1                1
                 1                1
                 1                1
                 1                1
                 1                1
                 1                1
                 1                1

10 rows selected.

plsql Function
sql Function
plsql Function
sql Function
sql Function
sql Function
sql Function
sql Function
sql Function
sql Function
sql Function
sql Function

Function dropped.

So if any one is trying to take advantage of this enhancement then give a thought to it if you are using deterministic function.

Cheers!!!
Jagdeep Sangwan

About these ads

4 thoughts on “With Enhancement

  1. Please share the result of these two also
    with
    function full_name(
    p_first varchar2
    , p_last varchar2
    )
    return varchar2
    as
    begin
    return pkg.get_full_name(v1, v2);
    end;
    select
    n1
    , full_name(v1, v2)
    from
    t1
    /

    &

    select
    n1
    , v1||v2
    from
    t1
    /

    • Vikash,

      Here are the results of your requested queries.I have made the change in first
      query to pass the p_first and p_last as parameteres in the call to pkg.get_full_name

      with
      	function full_name(
      		p_first varchar2
      		, p_last varchar2
      	)
      	return varchar2
      	as
      	begin
      		return pkg.get_full_name(p_first, p_last);
      	end;
      select
      	n1
      	, full_name(v1, v2)
      from
      	t1
      /
      
      
      100000 rows selected.
      
      Elapsed: 00:00:02.06
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3617692013
      
      --------------------------------------------------------------------------
      | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |      |   120K|  3167K|   137   (1)| 00:00:01 |
      |   1 |  TABLE ACCESS FULL| T1   |   120K|  3167K|   137   (1)| 00:00:01 |
      --------------------------------------------------------------------------
      
      Note
      -----
         - dynamic statistics used: dynamic sampling (level=2)
      
      
      Statistics
      ----------------------------------------------------------
                4  recursive calls
                0  db block gets
             7188  consistent gets
                0  physical reads
                0  redo size
          4082812  bytes sent via SQL*Net to client
            73869  bytes received via SQL*Net from client
             6668  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
           100000  rows processed
      
      select
      	n1
      	, v1||v2
      from
      	t1
      /
      
      100000 rows selected.
      
      Elapsed: 00:00:01.14
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3617692013
      
      --------------------------------------------------------------------------
      | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |      |   120K|  3167K|   137   (1)| 00:00:01 |
      |   1 |  TABLE ACCESS FULL| T1   |   120K|  3167K|   137   (1)| 00:00:01 |
      --------------------------------------------------------------------------
      
      Note
      -----
         - dynamic statistics used: dynamic sampling (level=2)
      
      
      Statistics
      ----------------------------------------------------------
                4  recursive calls
                0  db block gets
             7188  consistent gets
                0  physical reads
                0  redo size
          3982802  bytes sent via SQL*Net to client
            73869  bytes received via SQL*Net from client
             6668  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
           100000  rows processed
      

      Here the first one is nearly to that of the context switches taking place whereas
      second one is quite faster than all others.

      • So i can conclude that now that using Pkg along side with with clause is not a good idea as tough to manage. But we can use this as an exception. further seeded functions are more effective like || or concatenate. :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s