How to Demonize Apex V Function!

Published on: 2021-10-312021-11-04 by Mustafa 2 Comments

Hi,

Answer of the question in the subject is “Using too much V in SQL statements”. Let’s see why and what should you do.

First things first, I am NOT a APEX developer. I am a DBA and Database Developer but some of my clients are using APEX and I am dealing with many sql functions that uses V function in apex. Since the day 1, I did not like this V function because developers using it in very different ways and it returns VARCHAR2 but it uses for all kind of compares without any conversion functions (this is not on the V function of course).

So, what is the problem here? At my clients’ codes, V function is used heavily in sql statements. Some queries that I dealt with lead me to investigate V function because even if the query has no performance issue, still, it takes too much to complete. When I checked the V function, I find out that it is not DETERMINISTIC (which shouldn’t be because of the logic of DETERMINISTIC but I will talk about this later). Let’s do some tests but before I must explain a few things. I hacked the code of V a little bit and add some logging codes to demonstrate how it is worked and performance issues about it but I won’t explain how I do it (probably many of you can do it easily) and share any source code of it. whenever a V function is called my logging code will write the parameter name of v function into “my_log” table.

I added my log codes, set an apex session in my sql developer session and run these selects:

delete my_log;

select v('OBJECT_ID'),v('OBJECT_ID'),v('OBJECT_ID'),v('OBJECT_ID') from dual;

select log_data from my_log;

OBJECT_ID
OBJECT_ID
OBJECT_ID
OBJECT_ID

delete my_log;

select v('OBJECT_ID'),v('OBJECT_ID'),v('OBJECT_ID'),v('OBJECT_ID') from dual;

select log_data from my_log;

OBJECT_ID

I delete log table first then call a select statement with four V function in select list run over DUAL table and then check the log table, as you see it is called 4 times. So same data is read 4 times and 4 plsql function call is occurred. this caused too much context switch between SQL and PLSQL and this can reduce your performance dramatically. Especially, Where clause is extremely dangerous, why? Because V function might run for all rows in the table which will cause a disaster.

drop table tmp purge;
create table tmp as select * from all_objects;

-- I set 12 into OBJECT_ID variable in apex session
delete my_log;

select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2 
from tmp 
where object_id = v('OBJECT_ID')

FET$  12  12
--------------------------------------

select Count(*) from my_log;

79647

drop table tmp purge;

create table tmp as select * from all_objects;

-- I set 12 into OBJECT_ID variable in apex session

delete my_log;

select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2

from tmp

where object_id = v('OBJECT_ID')

FET$ 12 12

--------------------------------------

select Count(*) from my_log;

79647

as you can see, this query called V function 79.647 times why? because there are 79.645 rows in the TMP table. Since I use V in Where clause, condition ( object_id = v(‘OBJECT_ID’) ) executed for every rows in the table (79.645 rows) and only 1 of the rows meets the condition so select list runs 1 times and there are 2 more V function (same with the one in where clause) so totally V called 79.647 times. I hope you see the problem here. in your application code if you use V function in your sql statements, it might be called too much!

How about an index? yep that will help:

create index i_tmp on tmp(object_id);

delete my_log;

select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2 
from tmp 
where object_id = v('OBJECT_ID');

FET$  12  12
--------------------------------------

select Count(*) from my_log;

3

create index i_tmp on tmp(object_id);

delete my_log;

select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2

from tmp

where object_id = v('OBJECT_ID');

FET$ 12 12

--------------------------------------

select Count(*) from my_log;

so it run only 3 times at this execution because this time search operation (where condition) is run on an index not table so V function is called 1 times in where clause and 2 times in select list. this means you should have indexes at least for your condition columns.

is that enough? Let me show you a pseudo code on a database:

Select Some Columns,
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...),
       Decode(V('SOME_ITEM'), ...)
From Some Tables...
Where Col1 = V('SOME_ITEM') ....

Select Some Columns,

Decode(V('SOME_ITEM'), ...),

Decode(V('SOME_ITEM'), ...)

From Some Tables...

Where Col1 = V('SOME_ITEM') ....

so many same V function call in select statement. creating and index will help you about where clause how about select list? since all items in select list cause a function call (even with same parameters) let’s test performance:

set timing on;
declare
 a varchar2(80);
begin
  for i in 1..10000 loop
  select v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), 
         v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), 
         v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), 
         v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID')
  into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a
  from dual;
  end loop;
end;
/

Elapsed: 00:00:03.229

set timing on;

declare

a varchar2(80);

begin

for i in 1..10000 loop

select v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'),

v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'),

v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID')

into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a

from dual;

end loop;

end;

Elapsed: 00:00:03.229

calling this query for 10.000 times (or you can thing this as calling sql 1 times for 10.000 rows) takes 3.2 seconds (20 same V function). if we change the code to this:

declare
 a varchar2(80);
begin
  for i in 1..10000 loop
  with my_data as (
      select /*+ no_merge */ v('OBJECT_ID') v_object_id from dual 
    )
  select v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, 
         v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, 
         v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, 
         v_object_id, v_object_id, v_object_id, v_object_id, v_object_id
  into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a
  from my_data;
  end loop;
end;
/

Elapsed: 00:00:00.553

declare

a varchar2(80);

begin

for i in 1..10000 loop

with my_data as (

select /*+ no_merge */ v('OBJECT_ID') v_object_id from dual

)

select v_object_id, v_object_id, v_object_id, v_object_id, v_object_id,

v_object_id, v_object_id, v_object_id, v_object_id, v_object_id,

v_object_id, v_object_id, v_object_id, v_object_id, v_object_id

into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a

from my_data;

end loop;

end;

Elapsed: 00:00:00.553

this query takes only 0.5 seconds. timings might significant but think about 1000 different queries that run on your database 1000 times per day and calculate spend of time for just calling V function. it is a lot! your significant amount of db time might be wasted.

Let’s improve the code a little bit more:

declare
 a varchar2(80);
 v_value varchar2(80);
begin
  v_value := v('OBJECT_ID');
  for i in 1..10000 loop
  with my_data as (
      select v_value v_object_id from dual 
    )
  select v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, 
         v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, 
         v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, 
         v_object_id, v_object_id, v_object_id, v_object_id, v_object_id
  into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a
  from my_data;
  end loop;
end;
/

Elapsed: 00:00:00.199

declare

a varchar2(80);

v_value varchar2(80);

begin

v_value := v('OBJECT_ID');

for i in 1..10000 loop

with my_data as (

select v_value v_object_id from dual

)

select v_object_id, v_object_id, v_object_id, v_object_id, v_object_id,

v_object_id, v_object_id, v_object_id, v_object_id, v_object_id,

v_object_id, v_object_id, v_object_id, v_object_id, v_object_id

into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a

from my_data;

end loop;

end;

Elapsed: 00:00:00.199

even better. if you increase number of call, you will see that first 2 plsql code timings will increase linearly but the last code (code just above) will give you almost same performance. Remember, V is a function not a variable. Use variables instead of using V if you can.

Even if these might help your system, main problem is still there: V is not DETERMINISTIC. you might change your codes as I suggested above or create more indexes but many developers also use V in VIEWS which is another and for me a bigger disaster. it is harder to modify the code you cannot use bind variables etc… What would happen if V was deterministic? I modified V and made it deterministic and let’s see the result with first test case without index:

drop table tmp purge;
create table tmp as select * from all_objects;

-- I set 12 into OBJECT_ID variable in apex session
delete my_log;

select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2 
from tmp 
where object_id = v('OBJECT_ID')

FET$  12  12
--------------------------------------

select Count(*) from my_log;

2

drop table tmp purge;

create table tmp as select * from all_objects;

-- I set 12 into OBJECT_ID variable in apex session

delete my_log;

select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2

from tmp

where object_id = v('OBJECT_ID')

FET$ 12 12

--------------------------------------

select Count(*) from my_log;

pay attention that there is no index but V is just called 2 times (1 for where clause and 1 for select list, event if select list has 2 copy). it was 79.647 without DETERMINISTIC if you remember the first result.

So making V as deterministic will solve almost every kind of problem but only problem is what I looking for here is not deterministic. Why? Let me explain what deterministic is first. if a function always returns same value with same parameters then this function is a deterministic function like TO_CHAR or SUBSTR. this allow oracle to know that if calls with same parameters are repeated, it can be cached and used first calculation result for on going calls. This way, if same function with same parameters are called in a SQL statement, that function is called only 1 times but this is not V. Result of V is not deterministic because application might change the value of a V item and this means V call with same parameters can return different results because it is just return a context variable value.

So, it shouldn’t be deterministic but it should be something that acting like deterministic for an sql statement. I don’t think any apex developer wants to see a change of V values during a select statement. I think they assume values of V won’t change during the sql statement. So, there should be something like DETERMINISTIC_FOR_CALL in Oracle database. we should be able to define plsql objects as “deterministic” while they are used in a sql statement. Their result should be calculated once and used during the sql call. I think this is also necessary for any plsql object that we can call on SQL. because current structure of plsql is kind of violating the consistency. A select statement returns rows as they were when the sql call started. even if the rows are changed during the sql call, result of the statement won’t be affected from those changes but PLSQL breaks that. when a function calculated for every row, a select statement inside the plsql object can return different results because it will be affected by on going changes. This is a database option. Database should provide us this functionality, Apex team has nothing to do about it.

By the way, I am really frustrated about data type conversions about V. since V returns VARCHAR2, many developer does not care about type conversions. if you compare a V value with a number then you should use “TO_NUMBER(V(…))” for example or at least (as I search for it) NV function (number result of V). this is another issue to me.

For now, I suggest to avoid using V in a SQL statement as much as possible and use bind variable if you can. Let’s say you have some SQL with so many V call with same parameter, what can you do? you can “force” oracle to cache them:

drop table tmp purge;
create table tmp as select * from all_objects;

set timing on;
select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5)
from (
        select v('OBJECT_ID') obj1, V('OBJECT_ID') obj2, V('USER_NAME') obj3, V('USER_NAME') obj4, V('USER_NAME') obj5, STATUS
        from   tmp
     )
group by STATUS;

Elapsed: 00:00:03.262

drop table tmp purge;

create table tmp as select * from all_objects;

set timing on;

select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5)

from (

select v('OBJECT_ID') obj1, V('OBJECT_ID') obj2, V('USER_NAME') obj3, V('USER_NAME') obj4, V('USER_NAME') obj5, STATUS

from tmp

)

group by STATUS;

Elapsed: 00:00:03.262

there are better way to write this sql but I just want to demonstrate a way. as you see this query takes 3.26 seconds to complete because of too many V calls. you can rewrite your query and cache the V results first and then use them as this:

select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5)
from (
select obj obj1, obj obj2, usr obj3, usr obj4, usr obj5, STATUS
from   tmp
         cross join (select /*+ no_merge */ v('OBJECT_ID') obj, V('USER_NAME') usr from dual) v_values
)
group by STATUS;

Elapsed: 00:00:00.192

select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5)

from (

select obj obj1, obj obj2, usr obj3, usr obj4, usr obj5, STATUS

from tmp

cross join (select /*+ no_merge */ v('OBJECT_ID') obj, V('USER_NAME') usr from dual) v_values

)

group by STATUS;

Elapsed: 00:00:00.192

I am adding a NO_MERGE hint to prevent optimizer to merge queries before query run. as you see this version returns in 0.19 seconds 15 times faster becuase V functions are called only 2 times (2 different V item is selected OBJECT_ID and USER_NAME).

Edit:

Another and maybe better solution for this problem is using your own V function and create it as deterministic:

Create Or Replace Function My_V ( P_Item   In Varchar2,
                                  P_Flow   In Number Default Null,
                                  P_Scope  In Varchar2 Default 'SESSION_AND_USER',
                                  P_Escape In Varchar2 Default 'N' ) Return Varchar2  Deterministic As
Begin
  Return V(P_Item, P_Flow, P_Scope, P_Escape);
End;
/

set timing on;
select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5)
from (
        select my_v('OBJECT_ID') obj1, my_V('OBJECT_ID') obj2, my_V('USER_NAME') obj3, my_V('USER_NAME') obj4, my_V('USER_NAME') obj5, STATUS
        from   tmp
     )
group by STATUS;

Elapsed: 00:00:00.149

Create Or Replace Function My_V ( P_Item In Varchar2,

P_Flow In Number Default Null,

P_Scope In Varchar2 Default 'SESSION_AND_USER',

P_Escape In Varchar2 Default 'N' ) Return Varchar2 Deterministic As

Begin

Return V(P_Item, P_Flow, P_Scope, P_Escape);

End;

set timing on;

select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5)

from (

select my_v('OBJECT_ID') obj1, my_V('OBJECT_ID') obj2, my_V('USER_NAME') obj3, my_V('USER_NAME') obj4, my_V('USER_NAME') obj5, STATUS

from tmp

)

group by STATUS;

Elapsed: 00:00:00.149

this is faster than previous join method. only thing that you should change V calls with MY_V.

I hope, this helps you on your systems. Now after those tests, I must connect with my clients and explain them to change their coding to gain significant db time.

wish you all healthy days.

Hierarchical Blocking Session List

Published on: 2021-10-19 by Mustafa Comment

Hi,

Today, I needed to list all blocking sessions hierarchically. As you know, all information about sessions (and blocking sessions) are in gv$session (or just v$session if you are using single instance) view. I wanted to see who is blocking who and blocker at the top. you can write simple connect by (or with clause) query but it will list blockers at the bottom so I just developed it a little bit. if I remember correctly there were a small script under rdbms/admin but it was creating some tables etc etc. I just want to get it using a simple sql statements.

here is the sql:

with blockers as (
    select /*+ materialize */ blocking_session from gv$session where blocking_session is not null
  ),
  blocker_hierarchy as (
    select /*+ materialize */ sid, blocking_session, lpad(sid, 10 + level*2, ' '), CONNECT_BY_ISLEAF leaf
    from   gv$session
    connect by nocycle prior sid = blocking_session and prior inst_id = inst_id
    start with sid in (select blocking_session from blockers)
  ),
  Main_Blockers as (
    select username, sid, blocking_session, 
           lpad(' ', (level-1)*2, ' ') ||Decode(CONNECT_BY_ROOT sid, sid, 'BLOCKER', 'BLOCKED') type, 
           'SID: ' || lpad('>', (level-1)*3, '-') || rpad(to_char(sid), 6, ' ') || 
           'User:' || username || 
           '  Current SQL: ' ||(Select substr(Sql_Text, 1, 40) from v$sqlarea where sql_id = s.sql_id) Blocking_info
    from   gv$session s
    connect by nocycle prior sid = blocking_session and prior inst_id = inst_id
    start with sid in (select sid from blocker_hierarchy 
                       where  leaf = 0 
                         and  blocking_session is null 
                         and  type <> 'BACKGROUND')
    order siblings by sid)
select * from Main_Blockers;

with blockers as (

select /*+ materialize */ blocking_session from gv$session where blocking_session is not null

blocker_hierarchy as (

select /*+ materialize */ sid, blocking_session, lpad(sid, 10 + level*2, ' '), CONNECT_BY_ISLEAF leaf

from gv$session

connect by nocycle prior sid = blocking_session and prior inst_id = inst_id

start with sid in (select blocking_session from blockers)

Main_Blockers as (

select username, sid, blocking_session,

lpad(' ', (level-1)*2, ' ') ||Decode(CONNECT_BY_ROOT sid, sid, 'BLOCKER', 'BLOCKED') type,

'SID: ' || lpad('>', (level-1)*3, '-') || rpad(to_char(sid), 6, ' ') ||

'User:' || username ||

' Current SQL: ' ||(Select substr(Sql_Text, 1, 40) from v$sqlarea where sql_id = s.sql_id) Blocking_info

from gv$session s

connect by nocycle prior sid = blocking_session and prior inst_id = inst_id

start with sid in (select sid from blocker_hierarchy

where leaf = 0

and blocking_session is null

and type <> 'BACKGROUND')

order siblings by sid)

select * from Main_Blockers;

first, I get all blockers’ sids (blockers data source in the query) then I use them to generate the hierarchy (blocker_hierarcy data source in the query) then I remove some blockers from the hierarchy because they are also blocked by some other sessions and will be listed under them.

for example, let’s say sid 10 is blocking 2 different sessions (sid 20 and 30) and also sid 30 is blocking some other session (sid 40). so this query will produce a result like this:

SID: 10  User:MUSTAFA  Current SQL: 
SID: -->20  User:DAVID  Current SQL: update t set d = 3 where d = 2
SID: -->30  User:STEPHAN  Current SQL: update t set d = 1 where d = 2
SID: ----->40    User:JOOST  Current SQL: update t set d = 1 where d = 3

SID: 10 User:MUSTAFA Current SQL:

SID: -->20 User:DAVID Current SQL: update t set d = 3 where d = 2

SID: -->30 User:STEPHAN Current SQL: update t set d = 1 where d = 2

SID: ----->40 User:JOOST Current SQL: update t set d = 1 where d = 3

you can see the hierarch in a visual way and the first 40 character of their currently running sql statements.

Hope this will be useful for you.

wish you healthy days.

Subquery Caching

Published on: 2021-10-172021-11-19 by Mustafa Comment

Hi,

I want to talk about very sweet performance “trick”. I love when I do something small and gain a lot 🙂 Today, I will demonstrate subquery caching in Oracle and try to explain how it works and it’s limitations.

Probably, you are using some plsql functions in your select (or dml) statements. There is always a package that contains some functions calculates staff or return some constant strings like “base url” or “environment name” etc. some of those functions has parameters too and we call them from SQL. Calling user defined plsql function from sql statement is a performance problem because it will cause context switch a lot. Let’s say you have function return a constant string. To see the performance difference I will add a sleep code into function so it will be easier to see performance problem.

create or replace function sf_base_url return varchar2 as 
begin
  dbms_lock.sleep(1);
  return lower('www.mustafakalayci.me');
end;
/

create or replace function sf_base_url return varchar2 as

begin

dbms_lock.sleep(1);

return lower('www.mustafakalayci.me');

end;

this is our function. you need to execute grant on DBMS_LOCK package to create this procedure. Now, we will call this function from an sql statements. let’s create a table and then call this function:

sqlplus mustafa/mustafa

create table t as select chr(level+64) my_string from dual connect by level <= 26;

set timing on
column base_url format a20
column my_string format a20



select sf_base_url() base_url, my_string
from   t
where  rownum <=2;

BASE_URL              MY_STRING
--------------------- --------------------
www.mustafakalayci.me A
www.mustafakalayci.me B

Elapsed: 00:00:02.01



select sf_base_url() base_url, my_string
from   t
where  rownum <=4;

BASE_URL              MY_STRING
--------------------- --------------------
www.mustafakalayci.me A
www.mustafakalayci.me B
www.mustafakalayci.me C
www.mustafakalayci.me D

Elapsed: 00:00:04.03

sqlplus mustafa/mustafa

create table t as select chr(level+64) my_string from dual connect by level <= 26;

set timing on

column base_url format a20

column my_string format a20

select sf_base_url() base_url, my_string

from t

where rownum <=2;

BASE_URL MY_STRING

--------------------- --------------------

www.mustafakalayci.me A

www.mustafakalayci.me B

Elapsed: 00:00:02.01

select sf_base_url() base_url, my_string

from t

where rownum <=4;

BASE_URL MY_STRING

--------------------- --------------------

www.mustafakalayci.me A

www.mustafakalayci.me B

www.mustafakalayci.me C

www.mustafakalayci.me D

Elapsed: 00:00:04.03

when I call sf_base_url function in the first select statement (2 rows) it takes 2 seconds and on the second select statement (with 4 rows) it takes 4 seconds. this proves that our function called as many times as the number of rows from the query. Did you see the problem here? this function always returns the same result but it called more than 1 times. Actually calling it just 1 time is enough. Think about it on your databases. you have business function takes 10 seconds to complete and it is called on an sql statements which returns 1000 rows. you will lose 9990 seconds (we must call it at least 1 time so 10 seconds must be spend) calling your function unnecessarily.

This is where the subquery caching comes in. We can tell Oracle to cache this function and use that value from now on. How we do it is a little bit interesting to me 🙂 We just use a subquery to call this function.

select (select sf_base_url() from dual) base_url, my_string
from   t
where  rownum <=4;

BASE_URL              MY_STRING
--------------------- --------------------
www.mustafakalayci.me A
www.mustafakalayci.me B
www.mustafakalayci.me C
www.mustafakalayci.me D

Elapsed: 00:00:01.02

select (select sf_base_url() from dual) base_url, my_string

from t

where rownum <=4;

BASE_URL MY_STRING

--------------------- --------------------

www.mustafakalayci.me A

www.mustafakalayci.me B

www.mustafakalayci.me C

www.mustafakalayci.me D

Elapsed: 00:00:01.02

as you see, I change sf_Base_url function to a subquery and select the function value from dual. Main query returns 4 rows (rownum <=4) but it only takes 1 second! our function is just called 1 times and result of it used consequent rows. That is so small change and very nice performance gain.

So far so good. how about a function with parameters? Let’s change the function a little bit and make it have 1 parameter, returning our parameter added at the end of base url .

create or replace function sf_base_url(p varchar2 := '') return varchar2 as 
begin
  dbms_lock.sleep(1);
  return lower('www.mustafakalayci.me/' || p);
end;
/

create or replace function sf_base_url(p varchar2 := '') return varchar2 as

begin

dbms_lock.sleep(1);

return lower('www.mustafakalayci.me/' || p);

end;

let’s call this from the sql and give a column value as paramater:

select sf_base_url(my_string) base_url, my_string
from   t
where  rownum <=4;

BASE_URL                                 MY_STRING
---------------------------------------- --------------------
www.mustafakalayci.me/a                   A
www.mustafakalayci.me/b                   B
www.mustafakalayci.me/c                   C
www.mustafakalayci.me/d                   D

Elapsed: 00:00:04.05

select sf_base_url(my_string) base_url, my_string

from t

where rownum <=4;

BASE_URL MY_STRING

---------------------------------------- --------------------

www.mustafakalayci.me/a A

www.mustafakalayci.me/b B

www.mustafakalayci.me/c C

www.mustafakalayci.me/d D

Elapsed: 00:00:04.05

it takes 4 seconds as expected. Now, subquery caching:

select (select sf_base_url(my_string) from dual) base_url, my_string
from   t
where  rownum <=4;

BASE_URL                                 MY_STRING
---------------------------------------- --------------------
www.mustafakalayci.me/a                  A
www.mustafakalayci.me/b                  B
www.mustafakalayci.me/c                  C
www.mustafakalayci.me/d                  D

Elapsed: 00:00:04.04

select (select sf_base_url(my_string) from dual) base_url, my_string

from t

where rownum <=4;

BASE_URL MY_STRING

---------------------------------------- --------------------

www.mustafakalayci.me/a A

www.mustafakalayci.me/b B

www.mustafakalayci.me/c C

www.mustafakalayci.me/d D

Elapsed: 00:00:04.04

it also takes 4 seconds. Subquery caching didn’t work? of course not it is used but this time our function is cached with it’s parameters. since every parameter is different, our function is called for each different parameter and every row has different my_string column value. we would see the performance gain if we have same values on our table. lets do it:

drop table t;
create table t as select 'A' my_string from dual connect by level <= 3;
insert into t select 'B' my_string from dual connect by level <= 3;
insert into t select 'C' my_string from dual connect by level <= 3;
insert into t select 'D' my_string from dual connect by level <= 3;
commit;

drop table t;

create table t as select 'A' my_string from dual connect by level <= 3;

insert into t select 'B' my_string from dual connect by level <= 3;

insert into t select 'C' my_string from dual connect by level <= 3;

insert into t select 'D' my_string from dual connect by level <= 3;

commit;

Now our table has 3 A, B and C rows. let’s run subquery caching again.

select (select sf_base_url(my_string) from dual) base_url, my_string
from   t;

BASE_URL                                 MY_STRING
---------------------------------------- --------------------
www.mustafakalayci.me/a                  A
www.mustafakalayci.me/a                  A
www.mustafakalayci.me/a                  A
www.mustafakalayci.me/b                  B
www.mustafakalayci.me/b                  B
www.mustafakalayci.me/b                  B
www.mustafakalayci.me/c                  C
www.mustafakalayci.me/c                  C
www.mustafakalayci.me/c                  C
www.mustafakalayci.me/d                  D
www.mustafakalayci.me/d                  D

BASE_URL                                 MY_STRING
---------------------------------------- --------------------
www.mustafakalayci.me/d                  D

12 rows selected.

Elapsed: 00:00:04.03

select (select sf_base_url(my_string) from dual) base_url, my_string

from t;

BASE_URL MY_STRING

---------------------------------------- --------------------

www.mustafakalayci.me/a A

www.mustafakalayci.me/b B

www.mustafakalayci.me/c C

www.mustafakalayci.me/d D

BASE_URL MY_STRING

---------------------------------------- --------------------

www.mustafakalayci.me/d D

12 rows selected.

Elapsed: 00:00:04.03

Now it returns 12 rows (3 of each letter) but it takes 4 seconds because our function called one time only for each parameter and repeated parameters are read from the cache. Work like a charm.

Is there a limit for that? how many different parameter can be cached and how it is work? Let’s test.

drop table t;

create table t as 
  select level my_string
  from   dual 
  connect by level <= 256;
  
insert into t select * from t;
commit;

drop table t;

create table t as

select level my_string

from dual

connect by level <= 256;

insert into t select * from t;

commit;

Now, our table has 256 distinct rows and then I double it by inserting all rows again. Finally, we have 512 rows on the table. Run our subquery cached select statement again.

select sf_base_url(my_string) base_url, my_string
from   t;

-- suppressed output

Elapsed: 00:00:518.6


select (select sf_base_url(my_string) from dual) base_url, my_string
from   t ;

-- suppressed output

Elapsed: 505.6 seconds

select sf_base_url(my_string) base_url, my_string

from t;

-- suppressed output

Elapsed: 00:00:518.6

select (select sf_base_url(my_string) from dual) base_url, my_string

from t ;

-- suppressed output

Elapsed: 505.6 seconds

interesting result. This table has 256 distinct value and 512 total number of rows. every row is doubled but both subquery caching and normal call takes almost same time. Did not subquery caching work? Actually, It worked but it can only cache limited number of rows. if there are more than number of rows that can be cached, new parameter values of function will overwrite an old cached values. So, our table rows are like this: 1,2,3,4…256,1,2,3,4…256 it cached some rows but new arriving rows eventually overwrite the cache and we couldn’t take advantage of subquery caching. lets order those rows:

create table t2 as select * from t order by my_string;

select (select sf_base_url(my_string) from dual) base_url, my_string
from  t2;

-- suppressed output

Elapsed: 270.8 seconds

create table t2 as select * from t order by my_string;

select (select sf_base_url(my_string) from dual) base_url, my_string

from t2;

-- suppressed output

Elapsed: 270.8 seconds

t2 has the same table with table t but rows are ordered in the first place. When we run subquery caching example on t2 it takes 270 seconds almost half of the previous version which was on table t. now it was able to use cached values before they are overwrite.

Edit:

I wrote that my tests indicates that only 8 different values has been cached not 255 but my further tests show that this is not true. I assume Oracle uses a hash function to store data and while hashing our function and variable, some collusions occur. that is why some of the function results are overwritten frequently. this is not something we can change. of course consequent same calls are cached and take advantage of subquery caching.

thank you very much for reading. any comment will be welcomed.

wish you all healthy days.

Which Column has Changed in Every Row?

Published on: 2020-11-022020-11-03 by Mustafa Comment

Hi,

Edit: Thanks to Chris Saxon who is from asktom team, checked my code and warned me about a problem. Not to overload the PGA fetch_rows function only fetches 1024 rows at once so every row set chunk size is 1024. In this case for the 1025th row, previous row information is lost because fetch_rows procedure ended and recalled. He suggested to use XStore to store data from previous run of fetch_rows and I added it. While doing that I removed some unnecessary lines too. Thanks again Chris.

PS: this is not a Polymorphic Table Function (PTF) explanation or introduction, I just wanted to share a code that I wrote before and very useful (at least for me). if you want to learn more about PTFs check https://blog.sqlora.com/en/polymorphic-table-functions-example-transposing-columns-to-rows/

PTF is a great tool that enhances the sql. I think I only need a “pointer” definition in PLSQL after PTF and that’s all I would be very happy 🙂 even in this example, it would be nice if I can use a pointer to point data collections which makes the code shorter and more readable.

About a few months ago, I debugged a code for some unnecessary rows. It comes to an sql statement which has a MINUS set operator to eliminate rows but some rows are not eliminated as needed. I found the rows and try to find out what column has different values so that row is not eliminated in MINUS but this table has so many NUMBER columns which has many decimal points and it was really hard to find which column(s) has different values.

Since I am a so lazy person to check all column by column in two rows, I created a Polymorphic Table Function (PTF) to find the differences for me and it worked as I needed. I added a new varchar2 column to the base query (select … minus select …) called DIFFERENT_COLUMNS and I put different column names, old value and new value into that column and got all of them. After solving my problems, I realized that I can use this code for something even better!!!

While looking to a log data of table (trigger base or flashback or flashback data archive), I have been asked many times “what changed”. which column(s) has changed? it is not easy and mostly required to check row by row, column by column. So, my polymorphic table function is perfect solution for that! Just provide necessary table or select statement and find all columns changed every step.

Here is my basic code:

create or replace Package Pkg_Column_Value_Differencies as
  /* Written by Mustafa KALAYCI 
     mustafakalayci.me
     find different values of columns with previous rows in query order */
  Function SF_Find_Differences (Source_Table in Table,
                                Excluded_Columns IN DBMS_TF.COLUMNS_T := null) 
    Return Table Pipelined
      Row Polymorphic Using Pkg_Column_Value_Differencies;

  Function Describe(Source_Table in out dbms_tf.table_t,
                    Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t;
  Procedure Fetch_Rows;
End;
/

create or replace Package Body Pkg_Column_Value_differencies As
  /* Written by Mustafa KALAYCI 
     mustafakalayci.me
     find different values of columns with previous rows in query order */
  Function Describe(Source_Table in out dbms_tf.table_t,
                    Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t As
    Type T_Excluded_Columns is table of Pls_Integer index By Varchar2(128);
  Begin

    For I in 1..Source_Table.Column.Count Loop
      Source_Table.Column(I).For_Read := True;
      Source_Table.Column(I).Pass_Through := True;

      if Source_Table.column(I).description.name member of excluded_columns then
        Source_Table.Column(I).For_Read := False;
      End if;

    End Loop;

    -- add a new column to output of query/table
    -- different values will be listed as string in this column
    Return Dbms_Tf.Describe_T(New_Columns => Dbms_Tf.Columns_New_T(1 => Dbms_Tf.Column_Metadata_T(Name =>'DIFFERENT_COLUMS')));   
  End Describe;

  /*****************************************************************/

  Procedure Fetch_Rows as
    V_Row_Set           Dbms_Tf.Row_Set_T;
    V_Row_Count         PLS_Integer;
    V_New_Column_Values Dbms_Tf.Tab_Varchar2_T;
    V_Column_Count      PLS_Integer;
    V_Data              Varchar2(4000);
    V_Result            PLS_Integer;
    V_Current_Column_Value Varchar2(4000);

    Type T_Arr_Previous_Column_values is table of Varchar2(4000);
    V_Previous_Column_Values T_Arr_Previous_Column_values := T_Arr_Previous_Column_values();

    -- Local Procedure to check differences and add to a variable
    Procedure LP_Add_Different_Value_Data(P_Column varchar2, P_Old_Value varchar2, P_New_Value varchar2, P_Column_Number PLS_Integer) as
    Begin
      if P_Old_Value is Null     And P_New_Value is not null Or
         P_Old_Value is Not Null And P_New_Value is null Or
         P_Old_Value <> P_New_Value
      Then
        V_Data := Substr(V_Data || P_Column || ':' || Nvl(P_Old_Value, 'NULL') || '=>' || Nvl(P_New_Value, 'NULL') || ',', 1, 4000);
      End if;

      V_Previous_Column_Values(P_Column_Number) := P_New_Value;
    End LP_Add_Different_Value_Data;

    Function LF_Init_Previous_Row_Values(P_Row_Set Dbms_Tf.Row_Set_T) Return PLS_Integer As
      V_Result PLS_Integer;
    Begin      
      if Dbms_Tf.XStore_Exists('C1') then
        /* if this is not the first row set chunk 
           then read last row value from previous row set chunk */
        For i in 1..P_Row_Set.Count Loop
          Dbms_Tf.XStore_Get('C' || i, V_Previous_Column_Values(i));
        End Loop;

        V_Result := 1; -- there were a previous row on previous row set chunk
      Else
        /*if this is the first row set chunk,
          then assign current row value as previous row */
        V_New_Column_Values(1) := Null;
        for k in 1..V_Previous_Column_Values.Count Loop
          Case P_Row_Set(k).Description.Type
            When Dbms_Tf.Type_Varchar2 Then
              V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1);
            When Dbms_Tf.Type_Char Then
              V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1);
            When Dbms_Tf.Type_Number Then
              V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Number(1));
            When Dbms_Tf.Type_Date Then
              V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Date(1));
            When Dbms_Tf.Type_Timestamp Then
              V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Timestamp(1));
          End Case;
        End Loop;
        
        V_Result := 0; -- this is the first row set chunk
      End if;    

      Return V_Result;
    End LF_Init_Previous_Row_Values;
    
    Function LF_Get_Current_Column_Value(P_Col Dbms_Tf.Column_Data_T, P_Row_Number PLS_Integer) Return Varchar2 As
    Begin
      Return Case P_Col.Description.Type
               When Dbms_Tf.Type_Varchar2  Then P_Col.Tab_Varchar2(P_Row_Number)
               When Dbms_Tf.Type_Char      Then P_Col.Tab_Varchar2(P_Row_Number)
               When Dbms_Tf.Type_Number    Then To_Char(P_Col.Tab_Number(P_Row_Number))
               When Dbms_Tf.Type_Date      Then To_Char(P_Col.Tab_Date(P_Row_Number))
               When Dbms_Tf.Type_Timestamp Then To_Char(P_Col.Tab_Timestamp(P_Row_Number))
             End;
    End;

  Begin
    /* get result of the query and generate new column values */ 
    Dbms_Tf.Get_Row_Set(V_Row_Set, V_Row_Count, V_Column_Count);

    V_Previous_Column_Values.Extend(V_Column_Count);

    -- loop for every row
    for i in 1..V_Row_Count Loop
      V_Data := Null;
      
      if i = 1 Then
        V_Result := LF_Init_Previous_Row_Values(V_Row_Set);

        Continue When V_Result = 0; -- this is the first row set chunk so this is the first row, no previous
      End if;

      --loop for all columns in base table/query 
      --and check if value is different in previous row
      for k in 1..V_Column_Count Loop
        --get current column value as varchar2
        V_Current_Column_Value := LF_Get_Current_Column_Value(V_Row_Set(k), i);

        --check column type and use related collection to read data 
        --and if previous row data is different than current add it to string
        LP_Add_Different_Value_Data(P_Column        => V_Row_Set(k).Description.Name, 
                                    P_Old_Value     => V_Previous_Column_Values(k),
                                    P_New_Value     => V_Current_Column_Value,
                                    P_Column_Number => k);
      End Loop; -- column loop

      V_New_Column_Values(i) := V_Data;
    End Loop; -- row loop

    /* set last row data into xstore so it can be read in next row set chunk */
    For i in 1..V_Column_Count Loop
      Dbms_Tf.XStore_Set('C' || i, V_Previous_Column_Values(i));
    End Loop;

    --assign generated column values to first new column.
    Dbms_Tf.Put_Col(1, V_New_Column_Values);
  exception
    when others then
      dbms_output.put_line('ERROR: ' || sqlerrm || dbms_utility.format_error_Backtrace);
      Raise;
  End Fetch_Rows;

end;
/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

create or replace Package Pkg_Column_Value_Differencies as

/* Written by Mustafa KALAYCI

mustafakalayci.me

find different values of columns with previous rows in query order */

Function SF_Find_Differences (Source_Table in Table,

Excluded_Columns IN DBMS_TF.COLUMNS_T := null)

Return Table Pipelined

Row Polymorphic Using Pkg_Column_Value_Differencies;

Function Describe(Source_Table in out dbms_tf.table_t,

Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t;

Procedure Fetch_Rows;

End;

create or replace Package Body Pkg_Column_Value_differencies As

/* Written by Mustafa KALAYCI

mustafakalayci.me

find different values of columns with previous rows in query order */

Function Describe(Source_Table in out dbms_tf.table_t,

Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t As

Type T_Excluded_Columns is table of Pls_Integer index By Varchar2(128);

Begin

For I in 1..Source_Table.Column.Count Loop

Source_Table.Column(I).For_Read := True;

Source_Table.Column(I).Pass_Through := True;

if Source_Table.column(I).description.name member of excluded_columns then

Source_Table.Column(I).For_Read := False;

End if;

End Loop;

-- add a new column to output of query/table

-- different values will be listed as string in this column

Return Dbms_Tf.Describe_T(New_Columns => Dbms_Tf.Columns_New_T(1 => Dbms_Tf.Column_Metadata_T(Name =>'DIFFERENT_COLUMS')));

End Describe;

/*****************************************************************/

Procedure Fetch_Rows as

V_Row_Set Dbms_Tf.Row_Set_T;

V_Row_Count PLS_Integer;

V_New_Column_Values Dbms_Tf.Tab_Varchar2_T;

V_Column_Count PLS_Integer;

V_Data Varchar2(4000);

V_Result PLS_Integer;

V_Current_Column_Value Varchar2(4000);

Type T_Arr_Previous_Column_values is table of Varchar2(4000);

V_Previous_Column_Values T_Arr_Previous_Column_values := T_Arr_Previous_Column_values();

-- Local Procedure to check differences and add to a variable

Procedure LP_Add_Different_Value_Data(P_Column varchar2, P_Old_Value varchar2, P_New_Value varchar2, P_Column_Number PLS_Integer) as

Begin

if P_Old_Value is Null And P_New_Value is not null Or

P_Old_Value is Not Null And P_New_Value is null Or

P_Old_Value <> P_New_Value

Then

V_Data := Substr(V_Data || P_Column || ':' || Nvl(P_Old_Value, 'NULL') || '=>' || Nvl(P_New_Value, 'NULL') || ',', 1, 4000);

End if;

V_Previous_Column_Values(P_Column_Number) := P_New_Value;

End LP_Add_Different_Value_Data;

Function LF_Init_Previous_Row_Values(P_Row_Set Dbms_Tf.Row_Set_T) Return PLS_Integer As

V_Result PLS_Integer;

Begin

if Dbms_Tf.XStore_Exists('C1') then

/* if this is not the first row set chunk

then read last row value from previous row set chunk */

For i in 1..P_Row_Set.Count Loop

Dbms_Tf.XStore_Get('C' || i, V_Previous_Column_Values(i));

End Loop;

V_Result := 1; -- there were a previous row on previous row set chunk

Else

/*if this is the first row set chunk,

then assign current row value as previous row */

V_New_Column_Values(1) := Null;

for k in 1..V_Previous_Column_Values.Count Loop

Case P_Row_Set(k).Description.Type

When Dbms_Tf.Type_Varchar2 Then

V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1);

When Dbms_Tf.Type_Char Then

V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1);

When Dbms_Tf.Type_Number Then

V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Number(1));

When Dbms_Tf.Type_Date Then

V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Date(1));

When Dbms_Tf.Type_Timestamp Then

V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Timestamp(1));

End Case;

End Loop;

V_Result := 0; -- this is the first row set chunk

End if;

Return V_Result;

End LF_Init_Previous_Row_Values;

Function LF_Get_Current_Column_Value(P_Col Dbms_Tf.Column_Data_T, P_Row_Number PLS_Integer) Return Varchar2 As

Begin

Return Case P_Col.Description.Type

When Dbms_Tf.Type_Varchar2 Then P_Col.Tab_Varchar2(P_Row_Number)

When Dbms_Tf.Type_Char Then P_Col.Tab_Varchar2(P_Row_Number)

When Dbms_Tf.Type_Number Then To_Char(P_Col.Tab_Number(P_Row_Number))

When Dbms_Tf.Type_Date Then To_Char(P_Col.Tab_Date(P_Row_Number))

When Dbms_Tf.Type_Timestamp Then To_Char(P_Col.Tab_Timestamp(P_Row_Number))

End;

Begin

/* get result of the query and generate new column values */

Dbms_Tf.Get_Row_Set(V_Row_Set, V_Row_Count, V_Column_Count);

V_Previous_Column_Values.Extend(V_Column_Count);

-- loop for every row

for i in 1..V_Row_Count Loop

V_Data := Null;

if i = 1 Then

V_Result := LF_Init_Previous_Row_Values(V_Row_Set);

Continue When V_Result = 0; -- this is the first row set chunk so this is the first row, no previous

End if;

--loop for all columns in base table/query

--and check if value is different in previous row

for k in 1..V_Column_Count Loop

--get current column value as varchar2

V_Current_Column_Value := LF_Get_Current_Column_Value(V_Row_Set(k), i);

--check column type and use related collection to read data

--and if previous row data is different than current add it to string

LP_Add_Different_Value_Data(P_Column => V_Row_Set(k).Description.Name,

P_Old_Value => V_Previous_Column_Values(k),

P_New_Value => V_Current_Column_Value,

P_Column_Number => k);

End Loop; -- column loop

V_New_Column_Values(i) := V_Data;

End Loop; -- row loop

/* set last row data into xstore so it can be read in next row set chunk */

For i in 1..V_Column_Count Loop

Dbms_Tf.XStore_Set('C' || i, V_Previous_Column_Values(i));

End Loop;

--assign generated column values to first new column.

Dbms_Tf.Put_Col(1, V_New_Column_Values);

exception

when others then

dbms_output.put_line('ERROR: ' || sqlerrm || dbms_utility.format_error_Backtrace);

Raise;

End Fetch_Rows;

end;

this package has it’s own polymorphic table functions (SF_Find_Differences) which has two parameter, first is the table or query that you want to find differences with previous rows. By saying “previous” I am using default order what you provided to my PTF I am not ordering data in any way. I will explain second parameter later. let’s make an example:

/********** SAMPLE DATA **************/
drop table emp purge;
exec dbms_lock.sleep(10);

create table emp as select * from hr.employees;
commit;
exec dbms_lock.sleep(10);
update emp set first_name = 'Mustafa' where employee_id = 100;
commit;
exec dbms_lock.sleep(10);
update emp set last_name = 'KALAYCI' where employee_id = 100;
commit;
exec dbms_lock.sleep(10);
update emp set salary = 35000, commission_pct = 0.6 where employee_id = 100; 
commit;

/********** SAMPLE DATA **************/

drop table emp purge;

exec dbms_lock.sleep(10);

create table emp as select * from hr.employees;

commit;

exec dbms_lock.sleep(10);

update emp set first_name = 'Mustafa' where employee_id = 100;

commit;

exec dbms_lock.sleep(10);

update emp set last_name = 'KALAYCI' where employee_id = 100;

commit;

exec dbms_lock.sleep(10);

update emp set salary = 35000, commission_pct = 0.6 where employee_id = 100;

commit;

this will be my sample data (flashback query). one problem is while you are looking for a historical data mostly you will be checking one specified row history not all rows. so you must filter rows using primary key or something. Unfortunately you can not pass a “subquery” to PTF but you can pass a WITH data source.

So, I just created a dummy emp table and then updated employee with id 100. first change first_name then last_name then salary and commission_pct both. here is my query:

with history_data as (
    select versions_starttime, versions_endtime, 
           e.employee_id as id, e.first_name, e.last_name,
           e.salary, e.commission_pct
    from emp versions between scn minvalue and maxvalue e 
    where employee_id = 100 order by versions_endtime
  )
select * 
from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data);


VERSIONS_STARTTIME     VERSIONS_ENDTIME       ID  FIRST_NAME  LAST_NAME  SALARY COMMISSION_PCT  DIFFERENT_COLUMS
NULL                   01-NOV-20 11.58.31 PM  100 Steven      King       24000  NULL            NULL
01-NOV-20 11.58.31 PM  01-NOV-20 11.58.40 PM  100 Mustafa     King       24000  NULL            "VERSIONS_STARTTIME":NULL=>01-NOV-20 11.58.31.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.31.000000000 PM=>01-NOV-20 11.58.40.000000000 PM,"FIRST_NAME":Steven=>Mustafa,
01-NOV-20 11.58.40 PM  01-NOV-20 11.58.52 PM  100 Mustafa     KALAYCI    24000  NULL            "VERSIONS_STARTTIME":01-NOV-20 11.58.31.014177000 PM=>01-NOV-20 11.58.40.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.40.000000000 PM=>01-NOV-20 11.58.52.000000000 PM,"LAST_NAME":King=>KALAYCI,
01-NOV-20 11.58.52 PM  NULL                   100 Mustafa     KALAYCI    35000  .6              "VERSIONS_STARTTIME":01-NOV-20 11.58.40.014177000 PM=>01-NOV-20 11.58.52.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.52.000000000 PM=>NULL,"SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6,

with history_data as (

select versions_starttime, versions_endtime,

e.employee_id as id, e.first_name, e.last_name,

e.salary, e.commission_pct

from emp versions between scn minvalue and maxvalue e

where employee_id = 100 order by versions_endtime

)

select *

from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data);

VERSIONS_STARTTIME VERSIONS_ENDTIME ID FIRST_NAME LAST_NAME SALARY COMMISSION_PCT DIFFERENT_COLUMS

NULL 01-NOV-20 11.58.31 PM 100 Steven King 24000 NULL NULL

01-NOV-20 11.58.31 PM 01-NOV-20 11.58.40 PM 100 Mustafa King 24000 NULL "VERSIONS_STARTTIME":NULL=>01-NOV-20 11.58.31.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.31.000000000 PM=>01-NOV-20 11.58.40.000000000 PM,"FIRST_NAME":Steven=>Mustafa,

01-NOV-20 11.58.40 PM 01-NOV-20 11.58.52 PM 100 Mustafa KALAYCI 24000 NULL "VERSIONS_STARTTIME":01-NOV-20 11.58.31.014177000 PM=>01-NOV-20 11.58.40.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.40.000000000 PM=>01-NOV-20 11.58.52.000000000 PM,"LAST_NAME":King=>KALAYCI,

01-NOV-20 11.58.52 PM NULL 100 Mustafa KALAYCI 35000 .6 "VERSIONS_STARTTIME":01-NOV-20 11.58.40.014177000 PM=>01-NOV-20 11.58.52.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.52.000000000 PM=>NULL,"SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6,

I think this is so cool 🙂 if you check the last column it will be listing what is changed (which columns). As you can see I use a WITH clause to define specific historical data and order it using VERSIONS_ENDTIME. One problem is, I wanted to see when these changes happend (versions_starttime and endtime) but since they are also columns in this query my DIFFERENT_COLUMNS has those changed columns too! that is where the second parameter gets involved. second parameter is EXCLUDED column list from change control. so:

with history_data as (
    select versions_starttime, versions_endtime, 
           e.employee_id as id, e.first_name, e.last_name,
           e.salary, e.commission_pct
    from emp versions between scn minvalue and maxvalue e 
    where employee_id = 100 order by versions_endtime
  )
select * 
from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data, columns(versions_starttime, versions_endtime));


VERSIONS_STARTTIME     VERSIONS_ENDTIME       ID  FIRST_NAME  LAST_NAME  SALARY COMMISSION_PCT  DIFFERENT_COLUMS
NULL                   01-NOV-20 11.58.31 PM  100 Steven      King       24000  NULL            NULL
01-NOV-20 11.58.31 PM  01-NOV-20 11.58.40 PM  100 Mustafa     King       24000  NULL            "FIRST_NAME":Steven=>Mustafa,
01-NOV-20 11.58.40 PM  01-NOV-20 11.58.52 PM  100 Mustafa     KALAYCI    24000  NULL            "LAST_NAME":King=>KALAYCI,
01-NOV-20 11.58.52 PM  NULL                   100 Mustafa     KALAYCI    35000  .6              "SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6,

with history_data as (

select versions_starttime, versions_endtime,

e.employee_id as id, e.first_name, e.last_name,

e.salary, e.commission_pct

from emp versions between scn minvalue and maxvalue e

where employee_id = 100 order by versions_endtime

)

select *

from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data, columns(versions_starttime, versions_endtime));

VERSIONS_STARTTIME VERSIONS_ENDTIME ID FIRST_NAME LAST_NAME SALARY COMMISSION_PCT DIFFERENT_COLUMS

NULL 01-NOV-20 11.58.31 PM 100 Steven King 24000 NULL NULL

01-NOV-20 11.58.31 PM 01-NOV-20 11.58.40 PM 100 Mustafa King 24000 NULL "FIRST_NAME":Steven=>Mustafa,

01-NOV-20 11.58.40 PM 01-NOV-20 11.58.52 PM 100 Mustafa KALAYCI 24000 NULL "LAST_NAME":King=>KALAYCI,

01-NOV-20 11.58.52 PM NULL 100 Mustafa KALAYCI 35000 .6 "SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6,

much clear. on the second row, Steven becomes Mustafa, on the third row, King becomes KALAYCI and on the last one, salary and commission_pct are changed. it is more easier to track it or listing in your application.

Of course this is not a complete code, I just checked for varchar2, number, date and timestamp columns but not the others. Also didn’t work on “formatting” like date and timestamp data types which can be done but still pretty nice.

I wanted to share this with whole community. I hope this helps.

Edit: while checking whether old value and new value are different or not, I used if conditions but I would like to use SYS_OP_MAP_NONNULL function. unfortunately SYS_OP_MAP_NONNULL is not defined in plsql.

Edit: I made a change about excluding columns. instead of for loop I used “member of” clause.

wish you healthy corona & earthquake free days. Unfortunately, an earthquake happened in my country and neighbor countries. Rescue teams are still trying to save people. I hope this never happens in any place and no one experience this pain.

SQL Macro Part2 column_value = nvl(:param, column_value)

Published on: 2020-10-302021-09-23 by Mustafa Comment

Hi,

Since I am working on “SQL Macro”, I am keep testing things. so please see my previous post: http://mustafakalayci.me/2020/10/28/sql-macro-is-finally-here/

as mentioned in previous post, is it possible to get rid of column_value = nvl(:param, column_value) conditions? since they become a huge pain in big and complex queries. I provided a very cumbersome solution in previous post., I wanted to test and do more about it.

As I said I wasn’t able to read any parameter value in sql macro except numbers. So today I tried COLLECTIONS (Array) and got different results. Let’s start with COLUMNS. we can send a column list using DBMS_TF package:

create or replace function sf_group_any_Table (p dbms_Tf.table_t, p_columns dbms_tf.columns_t) return varchar2 sql_macro as
begin
  return 'select '||p_columns(1)||', count(*) from p ' ||
         ' group by  ' || p_columns(1);
end;
/

select * from sf_group_any_Table(hr.employees, columns(department_id));

DEPARTMENT_ID   COUNT(*)
------------- ----------
           50         45
           40          1
          110          2
           90          3
           30          6
           70          1
                       1
           10          1
           20          2
           60          5
          100          6
           80         34

select * from sf_group_any_Table(hr.departments, columns(location_id));

LOCATION_ID   COUNT(*)
----------- ----------
       1700         21
       1400          1
       2400          1
       1500          1
       1800          1
       2500          1
       2700          1

create or replace function sf_group_any_Table (p dbms_Tf.table_t, p_columns dbms_tf.columns_t) return varchar2 sql_macro as

begin

return 'select '||p_columns(1)||', count(*) from p ' ||

' group by ' || p_columns(1);

end;

select * from sf_group_any_Table(hr.employees, columns(department_id));

DEPARTMENT_ID COUNT(*)

------------- ----------

50 45

40 1

110 2

90 3

30 6

70 1

10 1

20 2

60 5

100 6

80 34

select * from sf_group_any_Table(hr.departments, columns(location_id));

LOCATION_ID COUNT(*)

----------- ----------

1700 21

1400 1

2400 1

1500 1

1800 1

2500 1

2700 1

so we are able to group any table based on a column and get count of it. since P_Columns is an array, we can send more columns in it but how can we use them dynamically in sql macro?

create or replace function sf_group_any_Table (p dbms_Tf.table_t, p_columns dbms_tf.columns_t) return varchar2 sql_macro as
  my_cols varchar2(4000);
begin
  /* beaware of SQL INJECTION */ 
  for i in 1..p_columns.count loop
    my_cols := my_cols || p_columns(i) || ',';
  end loop;
  my_cols := rtrim(my_cols, ',');
  return 'select '||my_cols||', count(*) from p ' ||
         ' group by  ' || my_cols;
end;
/

select * from sf_group_any_Table(hr.employees, columns(department_id, last_name));

DEPARTMENT_ID LAST_NAME                   COUNT(*)
------------- ------------------------- ----------
           60 Hunold                             1
           30 Himuro                             1
           50 Markle                             1
...
...

select * from sf_group_any_Table(hr.departments, columns(location_id, manager_id));


LOCATION_ID MANAGER_ID   COUNT(*)
----------- ---------- ----------
       2400        203          1
       1700                    16
       1700        200          1
       1700        114          1
       1800        201          1
       1500        121          1
       1700        108          1
...
...

create or replace function sf_group_any_Table (p dbms_Tf.table_t, p_columns dbms_tf.columns_t) return varchar2 sql_macro as

my_cols varchar2(4000);

begin

/* beaware of SQL INJECTION */

for i in 1..p_columns.count loop

my_cols := my_cols || p_columns(i) || ',';

end loop;

my_cols := rtrim(my_cols, ',');

return 'select '||my_cols||', count(*) from p ' ||

' group by ' || my_cols;

end;

select * from sf_group_any_Table(hr.employees, columns(department_id, last_name));

DEPARTMENT_ID LAST_NAME COUNT(*)

------------- ------------------------- ----------

60 Hunold 1

30 Himuro 1

50 Markle 1

...

select * from sf_group_any_Table(hr.departments, columns(location_id, manager_id));

LOCATION_ID MANAGER_ID COUNT(*)

----------- ---------- ----------

2400 203 1

1700 16

1700 200 1

1700 114 1

1800 201 1

1500 121 1

1700 108 1

...

great we can use plsql to generate column list and use it in sql statement but of course this could be a little bit dangerous because we started to use “string concatenation”. Not much but a little bit because columns in DBMS_TF.COLUMS_T array must be actual columns of the previous parameter or you got an error.

since we can use collections and data is passed to plsql (unlike varchar2 parameter) may be we can use user defined collections to pass a parameter and we can! that is really excited me at first(not then).

So since I wanted to eliminate unnecessary conditions like ” column1 = nvl(:param1, column1″ if :param1 is null, I planned to pass those column name and parameter value and check them in sql macro. if parameter value is not null then add it to sql condition.

here is VERY basic version:

Create Type T_Condition As Object(
  Condition_Column_Name Varchar2(128),
  Condition_Value Varchar2(4000) -- not used column_value because 
);
/

Create Type T_Arr_Condition Is Table Of T_Condition;
/

Create Or Replace Function Sf_Generate_Sql (P Dbms_Tf.Table_T, P_Columns Dbms_Tf.Columns_T, P_Where T_Arr_Condition) Return Varchar2 Sql_Macro As
  My_Cols Varchar2(4000);
  Mycondition Varchar2(4000) ;
Begin
  /* be aware of injections! */
  For I In 1..P_Columns.Count Loop
    My_Cols := My_Cols || P_Columns(I) || ',';
  End Loop;
  
  For I In 1..P_Where.Count Loop
    If P_Where(I).Condition_Value Is Not Null Then
      Mycondition := Mycondition || P_Where(I).Condition_Column_Name || ' = ' || P_Where(I).Condition_Value || ' and ';
    End If;
  End Loop;
  Mycondition := Substr(Mycondition, 1, Length(Mycondition)-4);
  
  If Mycondition Is Not Null Then
    Mycondition := ' where '|| Mycondition ;
  End If;
  
  My_Cols := Rtrim(My_Cols, ',');
  
  Return 'select '||My_Cols||' from p ' ||Mycondition;
  
End;
/

set autotrace on

Select * 
From Sf_Generate_Sql(Hr.Employees, 
                     Columns(Employee_Id, Department_Id, First_Name, Salary), 
                     T_Arr_Condition(T_Condition('EMPLOYEE_ID', '')));

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME               SALARY
----------- ------------- -------------------- ----------
        100            90 Steven                    24000
        101            90 Neena                     17000
        102            90 Lex                       17000
        103            60 Alexander                  9000
        104            60 Bruce                      6000
...
...

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  1926 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  1926 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME               SALARY
----------- ------------- -------------------- ----------
        100            90 Steven                    24000


Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    18 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    18 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Create Type T_Condition As Object(

Condition_Column_Name Varchar2(128),

Condition_Value Varchar2(4000) -- not used column_value because

);

Create Type T_Arr_Condition Is Table Of T_Condition;

Create Or Replace Function Sf_Generate_Sql (P Dbms_Tf.Table_T, P_Columns Dbms_Tf.Columns_T, P_Where T_Arr_Condition) Return Varchar2 Sql_Macro As

My_Cols Varchar2(4000);

Mycondition Varchar2(4000) ;

Begin

/* be aware of injections! */

For I In 1..P_Columns.Count Loop

My_Cols := My_Cols || P_Columns(I) || ',';

End Loop;

For I In 1..P_Where.Count Loop

If P_Where(I).Condition_Value Is Not Null Then

Mycondition := Mycondition || P_Where(I).Condition_Column_Name || ' = ' || P_Where(I).Condition_Value || ' and ';

End If;

End Loop;

Mycondition := Substr(Mycondition, 1, Length(Mycondition)-4);

If Mycondition Is Not Null Then

Mycondition := ' where '|| Mycondition ;

End If;

My_Cols := Rtrim(My_Cols, ',');

Return 'select '||My_Cols||' from p ' ||Mycondition;

End;

set autotrace on

Select *

From Sf_Generate_Sql(Hr.Employees,

Columns(Employee_Id, Department_Id, First_Name, Salary),

T_Arr_Condition(T_Condition('EMPLOYEE_ID', '')));

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME SALARY

----------- ------------- -------------------- ----------

100 90 Steven 24000

101 90 Neena 17000

102 90 Lex 17000

103 60 Alexander 9000

104 60 Bruce 6000

...

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 107 | 1926 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME SALARY

----------- ------------- -------------------- ----------

100 90 Steven 24000

Execution Plan

----------------------------------------------------------

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 18 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Viola! if I send null as the value of employee_id, just “select * from hr.employees” statement is run and a full table scan is done but when I send 100 as employee_id value “where employee_id = 100” condition is added.

PS: I am aware that my conditions are just an equality conditions and no AND/OR operator selection or grouping conditions (parenthesises for related OR conditions) etc etc etc. this is just an example after all. I will write a proper version of it when I decided to use it.

set autotrace on;

Select * 
From Sf_Generate_Sql(Hr.Employees, 
                     Columns(Employee_Id, Department_Id, First_Name, Salary), 
                     T_Arr_Condition(T_Condition('EMPLOYEE_ID', '100'),
                                     T_Condition('DEPARTMENT_ID', '90'),
                                     T_Condition('SALARY', '')));

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME               SALARY
----------- ------------- -------------------- ----------
        100            90 Steven                    24000


Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    18 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    18 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPLOYEES"."DEPARTMENT_ID"=90)
   2 - access("EMPLOYEES"."EMPLOYEE_ID"=100)

set autotrace on;

Select *

From Sf_Generate_Sql(Hr.Employees,

Columns(Employee_Id, Department_Id, First_Name, Salary),

T_Arr_Condition(T_Condition('EMPLOYEE_ID', '100'),

T_Condition('DEPARTMENT_ID', '90'),

T_Condition('SALARY', '')));

EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME SALARY

----------- ------------- -------------------- ----------

100 90 Steven 24000

Execution Plan

----------------------------------------------------------

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 18 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("EMPLOYEES"."DEPARTMENT_ID"=90)

2 - access("EMPLOYEES"."EMPLOYEE_ID"=100)

as you can see, I send 90 as department_id value and it is in my conditions but value of salary is null and it is not in my conditions. so did we get rid of those conditions? well not exactly.

event if it seems working (and it is working) I did not used any “bind variable” in my examples. those parameters are constant values and so the query. this is the actual running code for my last example:

select “EMPLOYEE_ID”,”DEPARTMENT_ID”,”FIRST_NAME”,”SALARY” from p where EMPLOYEE_ID = 100 and DEPARTMENT_ID = 90

so no bind variables. Let’s use one:

var column1 varchar2(128);
var value1 varchar2(1000);

exec :column1 := 'EMPLOYEE_ID';
exec :value1 := '100';

select *
from Sf_Generate_Sql(hr.employees,
                     columns(employee_id, department_ID, first_name, salary),
                     T_Arr_Condition(T_Condition(:column1, :value1)));
             
from Sf_Generate_Sql(hr.employees,
     *
ERROR at line 2:
ORA-62565: The SQL Macro method failed with error(s).
ORA-06531: Reference to uninitialized collection
ORA-06512: at "MUSTAFA.Sf_Generate_Sql", line 10
ORA-06512: at line 6

var column1 varchar2(128);

var value1 varchar2(1000);

exec :column1 := 'EMPLOYEE_ID';

exec :value1 := '100';

select *

from Sf_Generate_Sql(hr.employees,

columns(employee_id, department_ID, first_name, salary),

T_Arr_Condition(T_Condition(:column1, :value1)));

from Sf_Generate_Sql(hr.employees,

ERROR at line 2:

ORA-62565: The SQL Macro method failed with error(s).

ORA-06531: Reference to uninitialized collection

ORA-06512: at "MUSTAFA.Sf_Generate_Sql", line 10

ORA-06512: at line 6

unfortunately, when I used bind variable I got an error from line 10 of my Sf_Generate_Sql sql macro and line 10 is where the P_Where collection is read. so somehow SQL does not initiate this collection when I used bind variable and/or it is not passed to sql macro. Even if I tried the plsql version, it is not working:

declare
  x1 varchar2(80) := 'department_id';
  x2 varchar2(80):= '50';
  t_c1 t_condition := t_condition(x1,x2);
  x_count number;
  t_arr_c1 t_arr_condition := t_arr_condition(t_c1);
begin
  select * into x_count from Sf_Generate_Sql(hr.employees, columns(employee_id, department_ID, first_name, salary), t_arr_c1);
end;
/

Error report -
ORA-06550: line 8, column 30:
PL/SQL: ORA-62565: The SQL Macro method failed with error(s). 
ORA-06531: Reference to uninitialized collection
ORA-06512: at "MUSTAFA.SF_GENERATE_SQL", line 10
ORA-06512: at line 6

declare

x1 varchar2(80) := 'department_id';

x2 varchar2(80):= '50';

t_c1 t_condition := t_condition(x1,x2);

x_count number;

t_arr_c1 t_arr_condition := t_arr_condition(t_c1);

begin

select * into x_count from Sf_Generate_Sql(hr.employees, columns(employee_id, department_ID, first_name, salary), t_arr_c1);

end;

Error report -

ORA-06550: line 8, column 30:

PL/SQL: ORA-62565: The SQL Macro method failed with error(s).

ORA-06531: Reference to uninitialized collection

ORA-06512: at "MUSTAFA.SF_GENERATE_SQL", line 10

ORA-06512: at line 6

so I can not use it right now. since this is a 20c feature may be we should wait for 21c for more details and more options.

Edit:

I also wanted to add one more example, Let’s get only specified type of columns from any table:

Create Or Replace Function Sf_Get_Columns (P_Table Dbms_Tf.Table_T, P_Type Number default 1) Return Varchar2 Sql_Macro As
  My_Cols Varchar2(4000);
  Current_col boolean := true;
Begin
  
  For I In 1..P_Table.Column.Count Loop
    if P_Type = 1 and P_Table.Column(i).description.type = DBMS_TF.Type_Number then --number
      Current_col := true;
    elsif P_Type = 2 and P_Table.Column(i).description.type = DBMS_TF.Type_Varchar2 then --varchar2
      Current_col := true;
    elsif P_Type = 3 and P_Table.Column(i).description.type = DBMS_TF.Type_Date then -- date
      Current_col := true;
    elsif P_Type = 4 and P_Table.Column(i).description.type = DBMS_TF.Type_Clob then -- clob
      Current_col := true;
    elsif P_Type = 5 and P_Table.Column(i).description.type = DBMS_TF.Type_Blob then -- blob
      Current_col := true;
    else
      Current_col := false;
    end if;
    
    if Current_col then
      My_Cols := My_Cols || P_Table.Column(i).Description.Name || ',';
    end if;

    Current_col := false;
  End Loop;
  
  My_Cols := Rtrim(My_Cols, ',');

  if My_Cols is null then -- if no columns in specified type
    Return 'select * from dual where 1=2';
  else
    Return 'select '||My_Cols||' from P_Table ';
  end if;
  
End;
/

select * from Sf_Get_Columns(hr.employees, 1);

EMPLOYEE_ID     SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------- ---------- -------------
        100      24000                                      90
        101      17000                       100            90
        102      17000                       100            90
        103       9000                       102            60
        104       6000                       103            60
...
...

select * from Sf_Get_Columns(hr.employees, 2);

FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         JOB_ID
-------------------- ------------------------- ------------------------- -------------------- ----------
Steven               King                      SKING                     515.123.4567         AD_PRES
Neena                Kochhar                   NKOCHHAR                  515.123.4568         AD_VP
Lex                  De Haan                   LDEHAAN                   515.123.4569         AD_VP
Alexander            Hunold                    AHUNOLD                   590.423.4567         IT_PROG
Bruce                Ernst                     BERNST                    590.423.4568         IT_PROG
...

select * from Sf_Get_Columns(hr.employees, 3);

HIRE_DATE
-------------------
2003-06-17 00:00:00
2005-09-21 00:00:00
2001-01-13 00:00:00
2006-01-03 00:00:00
...
...

Create Or Replace Function Sf_Get_Columns (P_Table Dbms_Tf.Table_T, P_Type Number default 1) Return Varchar2 Sql_Macro As

My_Cols Varchar2(4000);

Current_col boolean := true;

Begin

For I In 1..P_Table.Column.Count Loop

if P_Type = 1 and P_Table.Column(i).description.type = DBMS_TF.Type_Number then --number

Current_col := true;

elsif P_Type = 2 and P_Table.Column(i).description.type = DBMS_TF.Type_Varchar2 then --varchar2

Current_col := true;

elsif P_Type = 3 and P_Table.Column(i).description.type = DBMS_TF.Type_Date then -- date

Current_col := true;

elsif P_Type = 4 and P_Table.Column(i).description.type = DBMS_TF.Type_Clob then -- clob

Current_col := true;

elsif P_Type = 5 and P_Table.Column(i).description.type = DBMS_TF.Type_Blob then -- blob

Current_col := true;

else

Current_col := false;

end if;

if Current_col then

My_Cols := My_Cols || P_Table.Column(i).Description.Name || ',';

end if;

Current_col := false;

End Loop;

My_Cols := Rtrim(My_Cols, ',');

if My_Cols is null then -- if no columns in specified type

Return 'select * from dual where 1=2';

else

Return 'select '||My_Cols||' from P_Table ';

end if;

End;

select * from Sf_Get_Columns(hr.employees, 1);

EMPLOYEE_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

----------- ---------- -------------- ---------- -------------

100 24000 90

101 17000 100 90

102 17000 100 90

103 9000 102 60

104 6000 103 60

...

select * from Sf_Get_Columns(hr.employees, 2);

FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER JOB_ID

-------------------- ------------------------- ------------------------- -------------------- ----------

Steven King SKING 515.123.4567 AD_PRES

Neena Kochhar NKOCHHAR 515.123.4568 AD_VP

Lex De Haan LDEHAAN 515.123.4569 AD_VP

Alexander Hunold AHUNOLD 590.423.4567 IT_PROG

Bruce Ernst BERNST 590.423.4568 IT_PROG

...

select * from Sf_Get_Columns(hr.employees, 3);

HIRE_DATE

-------------------

2003-06-17 00:00:00

2005-09-21 00:00:00

2001-01-13 00:00:00

2006-01-03 00:00:00

...

I defined 1 as number, 2 as varchar2 etc etc. so I can get only NUMBER columns from a table (any table actually) or only varchar2 columns.

SQL Macro has a great functionality improvement for SQL in my opinion. since 20c is not accessible yet I can not try SCALAR sql macro but I am confident that it will save us to write many same sql fragments for many different sql statements.

wish you healthy days.

SQL Macro is finally here!

Published on: 2020-10-282020-10-29 by Mustafa Comment

Hi,

I was waiting for this for a long time. Finally we can use sql macro in Oracle database. Actually I was excited when I first read about this but after my tests, I stuck at some points. I was hoping to use this as “parameterized views” as in, send a parameter and use it to choose which select statement runs and I am kind of able to do that but not I expected. I have read about the documentation but not much detail. I will mentioned about some “odd” staff that I found about sql macro at the end of this post.

And this could help us about the conditions of: “where customer_name = nvl(:Cust_name, customer_name”. I think many of you know this and suffer it right 🙂 if parameter is null then bring all of them but if it is not null then just bring matched rows.

EDIT: bummer! thanks to Mike Cutz from oracle community, showed me a mistake on my performance compare below. while I was testing, I used constant ‘EMPLOYEES’ value instead of bind variable. so I am changing that part.

So, what is sql macro. Sql macro is an SQL add-on to enhance the sql capability. mostly we use PLSQL when we need to do something complex and can not do it just by sql but this cause “context switch”. you can think this as, there are two engines in Oracle, one is SQL and the other PLSQL. two engines does not do other’s job. if a plsql statement requires to run an SQL then statements is sent to SQL engine and if sql engine finds a plsql (a function in select statement for ex) then it is sent to plsql engine to run. going back and forward between these engines is called “context switch” and context switch is a performance ENEMY! So we always try to do our job in just one engine but sometimes we have to use both. This is a new feature for 20c but I am running those code on 19.6.

SQL Macro will reduce the requirement of PLSQL so there won’t be context switch and we will have more performance. Also, it does help about getting better execution plans for complex queries some how. There are 2 types of SQL Macro, Scalar and Table. default is table and I will demonstrate table SM here.

Table SQL Macro works and looks like pipelined functions but remember, this is run by sql engine not by plsql.

create table tmp as select * from all_objects;
create index i_tmp1 on tmp(object_name);
create index i_tmp2 on tmp(object_id);
create index i_tmp3 on tmp(data_object_id);
create index i_tmp4 on tmp(created, object_name);
create index i_tmp5 on tmp(owner, created);

exec dbms_stats.gather_Table_Stats(user, 'TMP', estimate_percent=>100, cascade => true);

create or replace function sf_test(p_objecT_name varchar2) return varchar2 SQL_MACRO as
begin

  return 'select * from tmp
          where object_name = sf_test.p_object_name';  
end;
/

create table tmp as select * from all_objects;

create index i_tmp1 on tmp(object_name);

create index i_tmp2 on tmp(object_id);

create index i_tmp3 on tmp(data_object_id);

create index i_tmp4 on tmp(created, object_name);

create index i_tmp5 on tmp(owner, created);

exec dbms_stats.gather_Table_Stats(user, 'TMP', estimate_percent=>100, cascade => true);

create or replace function sf_test(p_objecT_name varchar2) return varchar2 SQL_MACRO as

begin

return 'select * from tmp

where object_name = sf_test.p_object_name';

end;

I just created a dummy table and created SF_TEST sql macro. sf_test looks like just a regular function with one varchar2 parameter but at the definition we use SQL_MACRO before “as” keyword. and then just return a string which has a select statement. here is usage:

select * from sf_Test('EMPLOYEES');

OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
HR     EMPLOYEES                                        75462...
OE     EMPLOYEES                                        75518...

select * from sf_Test('EMPLOYEES');

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID...

HR EMPLOYEES 75462...

OE EMPLOYEES 75518...

as you see our sql macro worked as a pipelined function and returned object information (TMP table) with named EMPLOYEES. basically it is just running the select statement in the sql macro and this operation is done in sql engine (well at least mostly…).

let’s make another example:

create or replace function sf_fetch_n_rows (p_number_of_rows number) return varchar2 sql_macro as
begin
  return 'select * 
          from tmp
          fetch first sf_fetch_n_rows.p_number_of_rows rows only';  
end;
/

select * from sf_fetch_n_rows(2);

OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
SYS    TS$                                              16...
SYS    ICOL$                                            20...

select * from sf_fetch_n_rows(4);

OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
SYS    TS$                                              16...
SYS    ICOL$                                            20...
SYS    C_FILE#_BLOCK#                                   8...
SYS    I_OBJ2                                           37...

create or replace function sf_fetch_n_rows (p_number_of_rows number) return varchar2 sql_macro as

begin

return 'select *

from tmp

fetch first sf_fetch_n_rows.p_number_of_rows rows only';

end;

select * from sf_fetch_n_rows(2);

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID...

SYS TS$ 16...

SYS ICOL$ 20...

select * from sf_fetch_n_rows(4);

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID...

SYS TS$ 16...

SYS ICOL$ 20...

SYS C_FILE#_BLOCK# 8...

SYS I_OBJ2 37...

cool right? I can parameterized the number of rows to return from an sql statement.

Let’s take it further:

create or replace function sf_fetch_n_rows_from_any_table (p_number_of_rows number, p_table dbms_tf.TABLE_T) return varchar2 sql_macro as
begin
  return 'select * 
          from sf_fetch_n_rows_from_any_table.p_table
          fetch first sf_fetch_n_rows_from_any_table.p_number_of_rows rows only';  
end;
/

select * from sf_fetch_n_rows_from_any_table(4, hr.employees);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE           JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         2003-06-17 00:00:00 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         2005-09-21 00:00:00 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         2001-01-13 00:00:00 AD_VP           17000                       100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         2006-01-03 00:00:00 IT_PROG          9000                       102            60

create or replace function sf_fetch_n_rows_from_any_table (p_number_of_rows number, p_table dbms_tf.TABLE_T) return varchar2 sql_macro as

begin

return 'select *

from sf_fetch_n_rows_from_any_table.p_table

fetch first sf_fetch_n_rows_from_any_table.p_number_of_rows rows only';

end;

select * from sf_fetch_n_rows_from_any_table(4, hr.employees);

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------

100 Steven King SKING 515.123.4567 2003-06-17 00:00:00 AD_PRES 24000 90

101 Neena Kochhar NKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP 17000 100 90

102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 00:00:00 AD_VP 17000 100 90

103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 00:00:00 IT_PROG 9000 102 60

this is really cool! we can pass a “table” as parameter (which is come with polymorphic table functions actually) to our sql macro and run our query based on this table. in my example I passed 4 (number of rows) and “hr.employees” table. please be careful, hr.employees is not a varchar2 variable! this is not a string concatenation operation. Now we can get any rows from any table:

select * from sf_fetch_n_rows_from_any_table(1, DBA_CONNECTION_TESTS);

P CONNECTION_TEST SQL_CONNECTION_TEST    SERVICE_NAME E
- --------------- ----------------------------------- -
Y SQL_TEST        SELECT 1 FROM DUAL                  Y

select * from sf_fetch_n_rows_from_any_table(3, hr.departments);

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700

select * from sf_fetch_n_rows_from_any_table(1, DBA_CONNECTION_TESTS);

P CONNECTION_TEST SQL_CONNECTION_TEST SERVICE_NAME E

- --------------- ----------------------------------- -

Y SQL_TEST SELECT 1 FROM DUAL Y

select * from sf_fetch_n_rows_from_any_table(3, hr.departments);

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

10 Administration 200 1700

20 Marketing 201 1800

30 Purchasing 114 1700

So, let’s get back to performance enhancement. Actually I am not sure about the exact result but I run a few simple test and it showed, execution plans are more accurate. here is an example:

set autotrace traceonly

var p_object_id number;
var p_object_name varchar2(128);
var p_object_type varchar2(128);
var p_owner varchar2(128);

exec :p_object_name := 'EMPLOYEES';

    select *
    from tmp
    where object_id = nvl(:p_object_id,object_id)
      and object_name = nvl(:p_object_name,object_name)
      and object_type = nvl(:p_object_type,object_type)
      and owner = nvl(:p_owner,owner);

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2707160492

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |     2 |   962 |   377   (2)| 00:00:01 |
|   1 |  VIEW                                  | VW_ORE_A06AD640 |     2 |   962 |   377   (2)| 00:00:01 |
|   2 |   UNION-ALL                            |                 |       |       |            |          |
|*  3 |    FILTER                              |                 |       |       |            |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| TMP             |     1 |   133 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | I_TMP2          |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    FILTER                              |                 |       |       |            |          |
|*  7 |     TABLE ACCESS FULL                  | TMP             |     1 |   133 |   375   (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:P_OBJECT_ID IS NOT NULL)
   4 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND
              "OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))
   5 - access("OBJECT_ID"=:P_OBJECT_ID)
   6 - filter(:P_OBJECT_ID IS NULL)
   7 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND
              "OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))

set autotrace traceonly

var p_object_id number;

var p_object_name varchar2(128);

var p_object_type varchar2(128);

var p_owner varchar2(128);

exec :p_object_name := 'EMPLOYEES';

select *

from tmp

where object_id = nvl(:p_object_id,object_id)

and object_name = nvl(:p_object_name,object_name)

and object_type = nvl(:p_object_type,object_type)

and owner = nvl(:p_owner,owner);

2 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2707160492

----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 962 | 377 (2)| 00:00:01 |

| 1 | VIEW | VW_ORE_A06AD640 | 2 | 962 | 377 (2)| 00:00:01 |

| 2 | UNION-ALL | | | | | |

|* 3 | FILTER | | | | | |

|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TMP | 1 | 133 | 2 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | I_TMP2 | 1 | | 1 (0)| 00:00:01 |

|* 6 | FILTER | | | | | |

|* 7 | TABLE ACCESS FULL | TMP | 1 | 133 | 375 (2)| 00:00:01 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter(:P_OBJECT_ID IS NOT NULL)

4 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND

"OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))

5 - access("OBJECT_ID"=:P_OBJECT_ID)

6 - filter(:P_OBJECT_ID IS NULL)

7 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND

"OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))

now, I make my first query a little bit complex with using some NVL functions and bind variables. now I use 4 bind variables (owner, object_name, object_type, object_id) but only OBJECT_NAME has a value and others are null. also please remember I created and index on OBJECT_NAME on TMP table. so in this query I am looking for all objects with name EMPLOYEES but since Oracle needs to check all values if they are null or not, execution plan becomes more complex and cost is higher. the equivalent version of this query (with this bind variable values) is:

Select * from tmp where object_name = ‘EMPLOYEES’;

and this is a very simple query which can use the index on TMP table.

EDIT: I deleted this part and rewriting it please see my previous edit at top.

I will discuss about reading variable values in sql macro after that but as a sneak peek, you can not read any variable value in sql macro except NUMBER types. so to eliminate the problem above (column = nvl(paramater, column)) we can at least do something by adding more number variables:

set autotrace traceonly;

set serveroutput on;

var table_name varchar2(128);
exec :table_name := 'EMPLOYEES';

create or replace function sf_test(p_owner varchar2, p_owner_null number,
                                   p_object_name varchar2,  p_object_name_null number,
                                   p_object_id number,  p_object_id_null number,
                                   p_object_type varchar2,  p_object_type_null number) return varchar2 sql_macro as
  v_sql varchar2(4000) :=  'select * from tmp ';
  v_conditions varchar2(4000);
begin
  if p_owner_null = 0 then
    v_conditions := v_conditions || ' owner = sf_test.p_owner and ';
  end if;

  if p_object_name_null = 0 then
    v_conditions := v_conditions || ' object_name = sf_test.p_object_name and ';
  end if;

  if p_object_id_null = 0 then
    v_conditions := v_conditions || ' object_id = sf_test.p_object_id and ';
  end if;

  if p_object_type_null = 0 then
    v_conditions := v_conditions || ' object_type = sf_test.p_object_type and ';
  end if;
  
  if v_conditions is not null then
    v_conditions := ' where ' || v_conditions ;
  end if;
  
  v_conditions := substr(v_conditions, 1, length(v_conditions)-4); --just remove last " and "
  
  --dbms_output.put_line(v_Sql || v_conditions);
    
  return v_Sql ||v_conditions ;  
end;
/

select * from sf_Test(null,1, :table_name,0, null,1,null,1);

Execution Plan
----------------------------------------------------------
Plan hash value: 194862134

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     1 |   133 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TMP    |     1 |   133 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | I_TMP1 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"=:TABLE_NAME)

set autotrace traceonly;

set serveroutput on;

var table_name varchar2(128);

exec :table_name := 'EMPLOYEES';

create or replace function sf_test(p_owner varchar2, p_owner_null number,

p_object_name varchar2, p_object_name_null number,

p_object_id number, p_object_id_null number,

p_object_type varchar2, p_object_type_null number) return varchar2 sql_macro as

v_sql varchar2(4000) := 'select * from tmp ';

v_conditions varchar2(4000);

begin

if p_owner_null = 0 then

v_conditions := v_conditions || ' owner = sf_test.p_owner and ';

end if;

if p_object_name_null = 0 then

v_conditions := v_conditions || ' object_name = sf_test.p_object_name and ';

end if;

if p_object_id_null = 0 then

v_conditions := v_conditions || ' object_id = sf_test.p_object_id and ';

end if;

if p_object_type_null = 0 then

v_conditions := v_conditions || ' object_type = sf_test.p_object_type and ';

end if;

if v_conditions is not null then

v_conditions := ' where ' || v_conditions ;

end if;

v_conditions := substr(v_conditions, 1, length(v_conditions)-4); --just remove last " and "

--dbms_output.put_line(v_Sql || v_conditions);

return v_Sql ||v_conditions ;

end;

select * from sf_Test(null,1, :table_name,0, null,1,null,1);

Execution Plan

----------------------------------------------------------

Plan hash value: 194862134

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 133 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TMP | 1 | 133 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_TMP1 | 1 | | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_NAME"=:TABLE_NAME)

we can shape our sql in sql macro. as I said since I can only read NUMBER variables, I added parameters that indicates whether the real parameter is null or not and based on this information I constracted my sql statements.

What is “odd” about this?

when I see the first examples, I thought to create condition list using the parameter values. if a parameter is not null then add “column = p_parameter1” to query but something is different in this environment. even if we are writing a “function” and can use plsql commands like “if then else” or for loop, we can not read parameter values (even more oddly) except NUMBERS.

set autotrace off;
set serveroutput on;

create or replace function sf_test_output(p_param number) return varchar2 sql_macro as
    begin
      dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);
      return 'select sf_test_output.p_param from dual';
    end;
    /


select * from sf_test_output(10);

   P_PARAM
----------
        10

1 row selected.

parameter p_param is 10

set autotrace off;

set serveroutput on;

create or replace function sf_test_output(p_param number) return varchar2 sql_macro as

begin

dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);

return 'select sf_test_output.p_param from dual';

end;

select * from sf_test_output(10);

P_PARAM

----------

1 row selected.

parameter p_param is 10

everything is normal here, I am showing the value using dbms_output and also my select is fetching parameter value from dual and both okay. query returned 1 row with value 10 and output is showing correct value.

Let’s do it with a varchar2:

create or replace function sf_test_output(p_param varchar2) return varchar2 sql_macro as
        begin
          dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);
          return 'select sf_test_output.p_param from dual';
        end;
        /

Function created.


select * from sf_test_output('ABC');

P_P
---
ABC

1 row selected.

parameter p_param is

create or replace function sf_test_output(p_param varchar2) return varchar2 sql_macro as

begin

dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);

return 'select sf_test_output.p_param from dual';

end;

Function created.

select * from sf_test_output('ABC');

P_P

---

ABC

1 row selected.

parameter p_param is

as you see, when parameter type is varchar2, then I can not read it inside the sql macro! my output is null (parameter p_param is) but query returned the value of parameter. same thing happens for date type too. So I can not use “if p_param = ‘ABC’ then” condition because p_param is null inside the sql macro but I can use “if p_param = 1 then” if p_param is a number.

I am running these codes on version 19.6 but also run them livesql.oracle.com which is 19.8 and same result.

I don’t know why it is acting like this maybe a bug maybe a feature 😀 I told that this is run by sql not plsql but I think this is not entirely true. some plsql engine is involved here but parameter values are not passed to plsql engine except numbers. of course this is just a guess I am not sure about it but I would like to use parameter values inside the sql macro.

wish you all healthy, corona free days.

Timestamp Arithmetic and Well Known Mistakes

Published on: 2020-07-28 by Mustafa Comment

Hi,

Lately, I started to see many developer uses timestamp types (like timestamp or timestamp with time zone) wrong especially in time arithmetic.

So as a short reminder, adding and subtracting numbers to/from a DATE value will add/subtract number of days:

Select Sysdate "TODAY",
       Sysdate + 1 "TOMORROW", --add 1 day
       Sysdate - 1 "YESTERDAY", --subtract 1 day
       Sysdate + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour
       Sysdate - (1/24) "ONE HOUR BEFORE", -- remove 1 hour
       Sysdate + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently
       Sysdate + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently
From   Dual;

TODAY               TOMORROW            YESTERDAY           ONE HOUR LATER      ONE HOUR BEFORE     ONE MINUTE LATER   ONE SECOND LATER
------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2020-07-28 14:07:38 2020-07-29 14:07:38 2020-07-27 14:07:38 2020-07-28 15:07:38 2020-07-28 13:07:38 2020-07-28 14:08:38 2020-07-28 14:07:39

Select Sysdate "TODAY",

Sysdate + 1 "TOMORROW", --add 1 day

Sysdate - 1 "YESTERDAY", --subtract 1 day

Sysdate + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour

Sysdate - (1/24) "ONE HOUR BEFORE", -- remove 1 hour

Sysdate + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently

Sysdate + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently

From Dual;

TODAY TOMORROW YESTERDAY ONE HOUR LATER ONE HOUR BEFORE ONE MINUTE LATER ONE SECOND LATER

------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------

2020-07-28 14:07:38 2020-07-29 14:07:38 2020-07-27 14:07:38 2020-07-28 15:07:38 2020-07-28 13:07:38 2020-07-28 14:08:38 2020-07-28 14:07:39

Basically that is all if you use add and subtract of course we have “INTERVAL” data type which I am about to explain.

This usage is not wrong if you work with DATE but if you are using TIMESTAMP types then there is a small side effect.

Select Systimestamp "TODAY",
       Systimestamp + 1 "TOMORROW", --add 1 day
       Systimestamp - 1 "YESTERDAY", --subtract 1 day
       Systimestamp + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour
       Systimestamp - (1/24) "ONE HOUR BEFORE", -- remove 1 hour
       Systimestamp + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently
       Systimestamp + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently
From   Dual;

TODAY                                 TOMORROW            YESTERDAY           ONE HOUR LATER      ONE HOUR BEFORE     ONE MINUTE LATER    ONE SECOND LATER
------------------------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2020-07-28 14:14:21.490000 +03:00   2020-07-29 14:14:21 2020-07-27 14:14:21 2020-07-28 15:14:21 2020-07-28 13:14:21 2020-07-28 14:15:21 2020-07-28 14:14:22

Select Systimestamp "TODAY",

Systimestamp + 1 "TOMORROW", --add 1 day

Systimestamp - 1 "YESTERDAY", --subtract 1 day

Systimestamp + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour

Systimestamp - (1/24) "ONE HOUR BEFORE", -- remove 1 hour

Systimestamp + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently

Systimestamp + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently

From Dual;

TODAY TOMORROW YESTERDAY ONE HOUR LATER ONE HOUR BEFORE ONE MINUTE LATER ONE SECOND LATER

------------------------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------

2020-07-28 14:14:21.490000 +03:00 2020-07-29 14:14:21 2020-07-27 14:14:21 2020-07-28 15:14:21 2020-07-28 13:14:21 2020-07-28 14:15:21 2020-07-28 14:14:22

so adding and subtracting did their’s job but if you look carefully there is no fractional seconds! Because they are not TIMESTAMP anymore, they are DATE. adding ans subtracting on timestamp types will convert result into a DATE. Let’s check if they are really date:

Select Dump(sysdate) ,
       Dump(Systimestamp + 0) 
From   Dual;

DUMP(SYSDATE)                      DUMP(SYSTIMESTAMP+0)
---------------------------------- ----------------------------------
Typ=13 Len=8: 228,7,7,28,14,24,5,0 Typ=13 Len=8: 228,7,7,28,14,24,5,0

Select Dump(sysdate) ,

Dump(Systimestamp + 0)

From Dual;

DUMP(SYSDATE) DUMP(SYSTIMESTAMP+0)

---------------------------------- ----------------------------------

Typ=13 Len=8: 228,7,7,28,14,24,5,0 Typ=13 Len=8: 228,7,7,28,14,24,5,0

dump is a function that shows you how oracle store that data internally. in this example you can see both Typ number are 13 and 13 is one of date type which Oracle uses and yes there are more than one date type in oracle but they are not visible to us. For example return type of SYSDATE function and a table date column has different types:

An error has occurred. Please try again later.

as you see table date column type number is 12 not 13. so they are different but it’s not relevant to us right now.

PS: just in case if you wonder how this data is a date:

Typ=13 Len=8: 228,7,7,28,14,27,25,0

(228+(256*7)) = 2020 and rest is just the month day hour minute and second.

if I returned to main subject adding to a timestamp type will convert it to a DATE and if you use this type in your sql like:

Select *
From   Orders
Where  Created_timestamp >= (Systimestamp -1)

Select *

From Orders

Where Created_timestamp >= (Systimestamp -1)

you will be comparing timestamp (created_timestamp column in orders table) with a DATE (systimestamp -1). of course these are convertible types but still Oracle needs to do a convert operation. to avoid that you can use INTERVAL data types.

I found INTERVAL types more clear to myself.

Select Systimestamp "TODAY",
       Systimestamp + Interval '1' Day "TOMORROW", --add 1 day
       Systimestamp + Interval '1' HOUR "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour
       Systimestamp + Interval '1' MINUTE "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently
       Systimestamp + Interval '1' SECOND "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently
From   Dual;

TODAY                             TOMORROW                             ONE HOUR LATER                       ONE MINUTE LATER                     ONE SECOND LATER
--------------------------------- ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------
2020-07-28 14:36:57.099000 +03:00 2020-07-29 14:36:57.099000000 +03:00 2020-07-28 15:36:57.099000000 +03:00 2020-07-28 14:37:57.099000000 +03:00 2020-07-28 14:36:58.099000000 +03:00

Select Systimestamp "TODAY",

Systimestamp + Interval '1' Day "TOMORROW", --add 1 day

Systimestamp + Interval '1' HOUR "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour

Systimestamp + Interval '1' MINUTE "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently

Systimestamp + Interval '1' SECOND "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently

From Dual;

TODAY TOMORROW ONE HOUR LATER ONE MINUTE LATER ONE SECOND LATER

--------------------------------- ------------------------------------ ------------------------------------ ------------------------------------ ------------------------------------

2020-07-28 14:36:57.099000 +03:00 2020-07-29 14:36:57.099000000 +03:00 2020-07-28 15:36:57.099000000 +03:00 2020-07-28 14:37:57.099000000 +03:00 2020-07-28 14:36:58.099000000 +03:00

as you can see Interval type are so simple and clean. we just write what we want to add or subtract and result is base type what you pass (TIMESTAMP With Time Zone in my example).

this type of interval has a fault to me. you can not pass number of Day/Hour/… as a parameters. this means you can not use “Interval V_Number_of_Days Day” for example but of course there is a solution, INTERVAL functions NumToDSinterval and NumToYMinterval.

NumToDSinterval = Number To DaySecond Interval

NumToYMinterval = Number To Year Month Interval

Select NumToDSinterval(1, 'DAY'), NumToDSinterval(1, 'HOUR')
From dual;

NUMTODSINTERVAL(1,'DAY')      NUMTODSINTERVAL(1,'HOUR')
----------------------------- ---------------------------------------------------------------------------
+000000001 00:00:00.000000000 +000000000 01:00:00.000000000

Select NumToDSinterval(1, 'DAY'), NumToDSinterval(1, 'HOUR')

From dual;

NUMTODSINTERVAL(1,'DAY') NUMTODSINTERVAL(1,'HOUR')

----------------------------- ---------------------------------------------------------------------------

+000000001 00:00:00.000000000 +000000000 01:00:00.000000000

so you can use:

Select Systimestamp "TODAY",
       Systimestamp + NumToDSinterval(1, 'Day') "TOMORROW", --add 1 day
       Systimestamp + NumToDSinterval(1, 'HOUR') "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour
       Systimestamp + NumToDSinterval(1, 'MINUTE') "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently
       Systimestamp + NumToDSinterval(1, 'SECOND') "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently
From   Dual;

Select Systimestamp "TODAY",

Systimestamp + NumToDSinterval(1, 'Day') "TOMORROW", --add 1 day

Systimestamp + NumToDSinterval(1, 'HOUR') "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour

Systimestamp + NumToDSinterval(1, 'MINUTE') "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently

Systimestamp + NumToDSinterval(1, 'SECOND') "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently

From Dual;

and result will be same with INTERVAL ‘1’DAY/HOUR…. Also please remember that you can use interval types with DATEs too.

hope this helps you in coding.

wish you a great day.

Is It Possible to Change SQL Statement in the Database?

Published on: 2020-07-042020-07-04 by Mustafa Comment

YES!

Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change about the sql. Well, in this case instead of asking to someone else, you can change that sql in the database before it runs!

After 12c, Oracle gave us an API, Sql Translation Framework, to run a different sql statement than the one sent by the application (or any client). We can affect a sql statements by using hints and database parameters but this is a whole new area. instead of “select * from customers” you can force Oracle to run “select count(*) from invoices“! you can change data source and event column list!

Main purpose of Sql Translation Framework to decrease the cost of converting a non Oracle application to an Oracle application. What! Let’s say you have small third party application which is run on Microsoft SQL Server database. Let say this is a small reporting tool and it is running some Select statements. you can easily move tables from sql server to Oracle but changing application could take much more longer time. At this point any basic select statement can run on any database for example “select * from invoices”; so no need to change anything in here but in sql server there is a useful command called TOP which allows you to get top n rows after an order by. So if your small reporting application runs this query: “select top 10 * from invoices order by amount desc” Oracle can not run this because TOP is a sql server command not Oracle.

Sql Translation Framework is involving at this point and allow you to run a different sql than the one issued. Whenever “select top 10 * from invoices order by amount desc” select statement issued we can tell Oracle to run “select * from invoices order by amount desc fetch first 10 rows only” statement. Pretty cool.

So this is the main purpose but since we can change the sql statements, this can let us to change poorly designed sql statements. Of course the main solution should be changing the source (change the sql which is called from the application) but this might not be possible all the time. for example, an application which we can not alter the source code might be running these queries (maybe we don’t have the source code or it is too expansive to ask for a change) or there might be an emergency, let’s say somehow a poor sql statement has been deployed to production system and it causes a very big performance problem. Since it might be fixed right away and deploying whole application all over again can take too much time. In this kind of situations, we can simply use sql translation framework.

I will demonstrate simple sql translation not an application translation (it will require more steps).

Steps to translate a sql to another:

create a translation profile: main record for your tranlations. there could be more than one sql in a translation profile. you can simply think that as a translations groups to managed easily.
register an sql to translate: the query you want to translate and the query you want translated to.
grant necessary privileges to users who will use translations
set sql_translation_profile parameter and 10601 event

That’s it.

exec dbms_sql_translator.create_profile('TEST1');

begin
  dbms_sql_translator.register_sql_translation('TEST1','select top 10 * from hr.employees',
  'select * from hr.employees fetch first 10 rows only');
end;
/

alter session set sql_translation_profile = test1;
alter session set events = '10601 trace name context forever, level 32';

select top 10 * from hr.employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE           JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17/06/2003 00:00:00 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21/09/2005 00:00:00 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13/01/2001 00:00:00 AD_VP           17000                       100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03/01/2006 00:00:00 IT_PROG          9000                       102            60
        104 Bruce                Ernst                     BERNST                    590.423.4568         21/05/2007 00:00:00 IT_PROG          6000                       103            60
        105 David                Austin                    DAUSTIN                   590.423.4569         25/06/2005 00:00:00 IT_PROG          4800                       103            60
        106 Valli                Pataballa                 VPATABAL                  590.423.4560         05/02/2006 00:00:00 IT_PROG          4800                       103            60
        107 Diana                Lorentz                   DLORENTZ                  590.423.5567         07/02/2007 00:00:00 IT_PROG          4200                       103            60
        108 Nancy                Greenberg                 NGREENBE                  515.124.4569         17/08/2002 00:00:00 FI_MGR          12008                       101           100
        109 Daniel               Faviet                    DFAVIET                   515.124.4169         16/08/2002 00:00:00 FI_ACCOUNT       9000                       108           100

exec dbms_sql_translator.create_profile('TEST1');

begin

dbms_sql_translator.register_sql_translation('TEST1','select top 10 * from hr.employees',

'select * from hr.employees fetch first 10 rows only');

end;

alter session set sql_translation_profile = test1;

alter session set events = '10601 trace name context forever, level 32';

select top 10 * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------

100 Steven King SKING 515.123.4567 17/06/2003 00:00:00 AD_PRES 24000 90

101 Neena Kochhar NKOCHHAR 515.123.4568 21/09/2005 00:00:00 AD_VP 17000 100 90

102 Lex De Haan LDEHAAN 515.123.4569 13/01/2001 00:00:00 AD_VP 17000 100 90

103 Alexander Hunold AHUNOLD 590.423.4567 03/01/2006 00:00:00 IT_PROG 9000 102 60

104 Bruce Ernst BERNST 590.423.4568 21/05/2007 00:00:00 IT_PROG 6000 103 60

105 David Austin DAUSTIN 590.423.4569 25/06/2005 00:00:00 IT_PROG 4800 103 60

106 Valli Pataballa VPATABAL 590.423.4560 05/02/2006 00:00:00 IT_PROG 4800 103 60

107 Diana Lorentz DLORENTZ 590.423.5567 07/02/2007 00:00:00 IT_PROG 4200 103 60

108 Nancy Greenberg NGREENBE 515.124.4569 17/08/2002 00:00:00 FI_MGR 12008 101 100

109 Daniel Faviet DFAVIET 515.124.4169 16/08/2002 00:00:00 FI_ACCOUNT 9000 108 100

as you can see I run an “invalid” sql statements. top 10 is a structure works on sql server but now I am able to run it in Oracle becuase I translated it into FETCH FIRST clause. please remember only exact sql will be translated. What I mean is if I add a order by or where clause to my translated query it won’t work:

select top 10 * from hr.employees order by employee_id;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 13 Column: 12

select top 10 * from hr.employees order by employee_id;

ORA-00923: FROM keyword not found where expected

00923. 00000 - "FROM keyword not found where expected"

*Cause:

*Action:

Error at Line: 13 Column: 12

Oracle does not know the version with “order by ” so you must add it too.

Also I mentioned about we can use this to implement a new version for poorly designed statements. Let’s test a union / union all query:

create table tmp_translate as
Select 1 group_id, Level level_id, dbms_Random.string('X', 100) my_string
from dual 
connect by level < 500000;

insert into tmp_translate
select 2 group_id, 500000+level_id, my_string from tmp_translate;

create table tmp_translate as

Select 1 group_id, Level level_id, dbms_Random.string('X', 100) my_string

from dual

connect by level < 500000;

insert into tmp_translate

select 2 group_id, 500000+level_id, my_string from tmp_translate;

this is our test table. table contains 2 sets of data and LEVEL_ID is unique so same row won’t be listed in both group. Let’s assume our developer has written this sql:

conn mustafa/mustafa

set timing on

  select Count(*)
  from   (
           select level_id, my_string from tmp_translate where group_id = 1
           union
           select level_id, my_string from tmp_translate where group_id = 2
         );
....
Elapsed: 00:00:01.09

conn mustafa/mustafa

set timing on

select Count(*)

from (

select level_id, my_string from tmp_translate where group_id = 1

union

select level_id, my_string from tmp_translate where group_id = 2

);

....

Elapsed: 00:00:01.09

Query takes 1.09 seconds. this query has a logical error. union is unnecessary, union all would return same data (since level_id is unique) and that’s because union all would work faster. if we can not change the source of the query then we can use sql translation framework:

Begin
    dbms_sql_translator.register_sql_translation('TEST1','  select Count(*)
      from   (
               select level_id, my_string from tmp_translate where group_id = 1
               union
               select level_id, my_string from tmp_translate where group_id = 2
             )'
    ,
    '  select Count(*)
     from   (
              select level_id, my_string from tmp_translate where group_id = 1
              union all
              select level_id, my_string from tmp_translate where group_id = 2
            )');
end;
/

Begin

dbms_sql_translator.register_sql_translation('TEST1',' select Count(*)

from (

select level_id, my_string from tmp_translate where group_id = 1

union

select level_id, my_string from tmp_translate where group_id = 2

' select Count(*)

from (

select level_id, my_string from tmp_translate where group_id = 1

union all

select level_id, my_string from tmp_translate where group_id = 2

)');

end;

there is one important thing here. consider ENTER character because if you are using it will be carriage return and new line but in linux it is just new line. so while testing you must be careful about enter char.

now let’s test it again:

  select Count(*)
      from   (
               select level_id, my_string from tmp_translate where group_id = 1
               union
               select level_id, my_string from tmp_translate where group_id = 2
             );

  COUNT(*)
----------
    999998

Elapsed: 00:00:00.14

select Count(*)

from (

select level_id, my_string from tmp_translate where group_id = 1

union

select level_id, my_string from tmp_translate where group_id = 2

);

COUNT(*)

----------

999998

Elapsed: 00:00:00.14

as you can see performance of the query has increased almost 10 times. that is because it worked as union all not union. unfortunately you can not understand it by checking execution plan because it is the same cursor so you will be seeing old execution plan. That’s also why I compare timings. Of course you can check SQL_TRANSLATION_PROFILE_ID in V$Session while running the query.

This is a great tool but don’t get too involved into this. Creating too many translations can cause too much headache. you might loose in them. So I suggest you to use it wisely.

By the way this also works with bind variables too:

begin
dbms_sql_translator.register_sql_translation('TEST1','select * from hr.employees where employee_id = :emp_id',
'select * from hr.locations where rownum < 3 and :emp_id = :emp_id');
end;
/

begin

dbms_sql_translator.register_sql_translation('TEST1','select * from hr.employees where employee_id = :emp_id',

'select * from hr.locations where rownum < 3 and :emp_id = :emp_id');

end;

after running select * from hr.employees where employee_id = :emp_id with any emp id, you will get 3 rows from locations table. if you are careful you will see that I added a :emp_id = :emp_id condition to translated query. that is because same bind variables must be listed on the second query otherwise you will get an error.

I hope this helps you. There are too many different things in it so this is just an introduction.

have a nice day.

V$SqlStats vs V$SqlStats_Plan_Hash, different columns?

Published on: 2020-07-012020-07-01 by Mustafa Comment

Hi there,

V$SQLSTATSis a beautiful view for information about sql statements. you might say v$sql has that information too which is correct but V$SQLSTATSis much more faster than v$sql and keeps data about sql statements for a longer time than v$sql.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS.html

However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool).

so it is a very useful view to check. V$SQLSTATS contains one row for one sql statements. that will summarize some information about sql statements because that sql may have different execution plans but V$SQLSTATS will sum up them. if you want to get data for same sql with different execution plans then you must check V$SQLSTATS_PLAN_HASH view which is very similar to V$SQLSTATS. only difference is V$SQLSTATS_PLAN_HASH has more than one row for each sql_id with different execution plan.

Please mind that V$SQLSTATS also has PLAN_HASH_VALUE column to identify execution plan but this column stores only the first cursor’s execution plan not the others.

So when you check the columns for V$SQLSTATS_PLAN_HASH view at the documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS_PLAN_HASH.html it says “same with V$SQL_STATS” but that is not entirely correct (and also not wrong). When you compare the columns (db version 19.6) you will see that those columns are not listed in V$SQLSTATS_PLAN_HASH

DIRECT_READS
DELTA_DIRECT_READS
CON_DBID
OBSOLETE_COUNT
AVOIDED_EXECUTIONS
DELTA_AVOIDED_EXECUTIONS

so my initial thought was that should be a documentation bug but then I checked underlying X$ tables; X$KKSSQLSTAT and X$KKSSQLSTAT_PLAN_HASH (easy to understand which one is which). Those X$ tables has the exact same columns (including the columns above) so documentation is partially correct. These X$ tables (virtual tables, views, memory arrays whatever you say) literally contains same data but definition of V$SQLSTATS_PLAN_HASH view has missing columns. I assume these columns will be added on further releases. Both of the views are great to explore the sql statements.

wish you health days.

All lives matter.

Performance Gain of NOCOPY Parameters

Published on: 2020-06-262022-08-31 by Mustafa Comment

Hi,

Almost every Oracle database uses PLSQL codes like packages/procedures/functions etc. Complex systems with complex codes pass many arguments (parameters) many times between plsql objects. Passing parameters might be the most executed operations in a database. So how well do you pass your parameters?

I assume you know about types of parameters like IN or OUT so I won’t explain it all over. Basically out let you modify the parameter inside a plsql block and return that value back to caller variable. You might think this is kind of a pointer operation but it is not. when you modify a parameter inside a block that new value won’t be affecting caller variable. it will store on a temporary variable and after plsql block completed modified value will be assign back to caller variable.

adding a NOCOPY to your in out variable actually make it a kind of pointer and whenever you modifed your parameter in plsql block it will affect the caller variable even if plsql block raise an exception! without nocopy, since data is copied to a new variable every procedure call, it will increase your memory consumption and timing. especially if you are using big variables like CLOB or PLSQL Collections (arrays).

So Let’s test different variable types, call them many times and see if there is a difference when we use NOCOPY.

I didn’t think about the sample code so it is a little bit messy. Basically, I am just using overloaded functions with different variable types and pass them to a procedure many times.

PS: I am setting plsql_optimize_level to 1 to prevent Oracle modify my sloppy code. Since I don’t actually run a business logic and Oracle can remove many of those code like assigning a dummy variable which is never used.

this is my package code:

alter session set plsql_optimize_level=1;

create or replace package pkg_test_parameter as
  procedure sp_main(p in out varchar2, p_method varchar2, p_loop_counter pls_integer);
  procedure sp_main(p in out date, p_method varchar2, p_loop_counter pls_integer);
  procedure sp_main(p in out number, p_method varchar2, p_loop_counter pls_integer);
  procedure sp_main(p in out clob, p_method varchar2, p_loop_counter pls_integer);
  procedure sp_main(p in out sys.ODCIVARCHAR2LIST, p_method varchar2, p_loop_counter pls_integer);
end;
/

create or replace package body pkg_test_parameter as
  
  procedure sp_process(p in out varchar2) as
  begin
    p := Substr(p,1,1000) || 'a';
  end;
  procedure sp_process_nocopy(p in out nocopy varchar2) as
  begin
    p := Substr(p,1,1000) || 'a';
  end;

  procedure sp_process(p in out date) as
  begin
    p := Trunc(p, 'MONTH')+1;
  end;
  procedure sp_process_nocopy(p in out nocopy date) as
  begin
    p := Trunc(p, 'MONTH')+1;
  end;

  procedure sp_process(p in out number) as
  begin
    p := p +1;
  end;
  procedure sp_process_nocopy(p in out nocopy number) as
  begin
    p := p +1;
  end;

  procedure sp_process(p in out clob) as
  begin
    p := p || to_clob('a');
  end;
  procedure sp_process_nocopy(p in out nocopy clob) as
  begin
    p := p || to_clob('a');
  end;

  procedure sp_process(p in out sys.ODCIVARCHAR2LIST) as
    x number;
  begin
    x := p.count;
  end;
  procedure sp_process_nocopy(p in out nocopy sys.ODCIVARCHAR2LIST) as
    x number;
  begin
    x := p.count;
  end;
  
  procedure sp_main(p in out varchar2, p_method varchar2, p_loop_counter pls_integer) as
  begin
    for i in 1..p_loop_counter loop
      if p_method = 'NOCOPY' then
        sp_process_nocopy(p);
      else
        sp_process(p);
      end if;
    end loop;
  end;
  procedure sp_main(p in out date, p_method varchar2, p_loop_counter pls_integer) as
  begin
    for i in 1..p_loop_counter loop
      if p_method = 'NOCOPY' then
        sp_process_nocopy(p);
      else
        sp_process(p);
      end if;
    end loop;
  end;
  procedure sp_main(p in out number, p_method varchar2, p_loop_counter pls_integer) as
  begin
    for i in 1..p_loop_counter loop
      if p_method = 'NOCOPY' then
        sp_process_nocopy(p);
      else
        sp_process(p);
      end if;
    end loop;
  end;
  procedure sp_main(p in out clob, p_method varchar2, p_loop_counter pls_integer) as
  begin
    for i in 1..p_loop_counter loop
      if p_method = 'NOCOPY' then
        sp_process_nocopy(p);
      else
        sp_process(p);
      end if;
    end loop;
  end;

  procedure sp_main(p in out sys.ODCIVARCHAR2LIST, p_method varchar2, p_loop_counter pls_integer) as
  begin
    for i in 1..p_loop_counter loop
      if p_method = 'NOCOPY' then
        sp_process_nocopy(p);
      else
        sp_process(p);
      end if;
    end loop;
  end;

end;
/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

alter session set plsql_optimize_level=1;

create or replace package pkg_test_parameter as

procedure sp_main(p in out varchar2, p_method varchar2, p_loop_counter pls_integer);

procedure sp_main(p in out date, p_method varchar2, p_loop_counter pls_integer);

procedure sp_main(p in out number, p_method varchar2, p_loop_counter pls_integer);

procedure sp_main(p in out clob, p_method varchar2, p_loop_counter pls_integer);

procedure sp_main(p in out sys.ODCIVARCHAR2LIST, p_method varchar2, p_loop_counter pls_integer);

end;

create or replace package body pkg_test_parameter as

procedure sp_process(p in out varchar2) as

begin

p := Substr(p,1,1000) || 'a';

end;

procedure sp_process_nocopy(p in out nocopy varchar2) as

begin

p := Substr(p,1,1000) || 'a';

end;

procedure sp_process(p in out date) as

begin

p := Trunc(p, 'MONTH')+1;

end;

procedure sp_process_nocopy(p in out nocopy date) as

begin

p := Trunc(p, 'MONTH')+1;

end;

procedure sp_process(p in out number) as

begin

p := p +1;

end;

procedure sp_process_nocopy(p in out nocopy number) as

begin

p := p +1;

end;

procedure sp_process(p in out clob) as

begin

p := p || to_clob('a');

end;

procedure sp_process_nocopy(p in out nocopy clob) as

begin

p := p || to_clob('a');

end;

procedure sp_process(p in out sys.ODCIVARCHAR2LIST) as

x number;

begin

x := p.count;

end;

procedure sp_process_nocopy(p in out nocopy sys.ODCIVARCHAR2LIST) as

x number;

begin

x := p.count;

end;

procedure sp_main(p in out varchar2, p_method varchar2, p_loop_counter pls_integer) as

begin

for i in 1..p_loop_counter loop

if p_method = 'NOCOPY' then

sp_process_nocopy(p);

else

sp_process(p);

end if;

end loop;

end;

procedure sp_main(p in out date, p_method varchar2, p_loop_counter pls_integer) as

begin

for i in 1..p_loop_counter loop

if p_method = 'NOCOPY' then

sp_process_nocopy(p);

else

sp_process(p);

end if;

end loop;

end;

procedure sp_main(p in out number, p_method varchar2, p_loop_counter pls_integer) as

begin

for i in 1..p_loop_counter loop

if p_method = 'NOCOPY' then

sp_process_nocopy(p);

else

sp_process(p);

end if;

end loop;

end;

procedure sp_main(p in out clob, p_method varchar2, p_loop_counter pls_integer) as

begin

for i in 1..p_loop_counter loop

if p_method = 'NOCOPY' then

sp_process_nocopy(p);

else

sp_process(p);

end if;

end loop;

end;

procedure sp_main(p in out sys.ODCIVARCHAR2LIST, p_method varchar2, p_loop_counter pls_integer) as

begin

for i in 1..p_loop_counter loop

if p_method = 'NOCOPY' then

sp_process_nocopy(p);

else

sp_process(p);

end if;

end loop;

end;

I will test VARCHAR2, DATE, NUMBER, CLOB and a COLLECTION of Varchar2. Call every each of them with and without NOCOPY and check the timings.

set serveroutput on;

/*****************VARCHAR2*********************/
declare
  x_dummy varchar2(2000) := 'a';
  s timestamp;
begin
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy, 'COPY', 40000000);
  dbms_output.put_line('VARCHAR2 COPY  : ' ||(systimestamp -s));
  
  x_dummy := 'a';
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 40000000);
  dbms_output.put_line('VARCHAR2 NOCOPY: ' ||(systimestamp -s));
end;
/

/*****************DATE*********************/
declare
  x_dummy date := trunc(sysdate);
  s timestamp;
begin
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy, 'COPY', 10000000);
  dbms_output.put_line('DATE COPY  : ' ||(systimestamp -s));
  
  x_dummy := trunc(sysdate);
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 10000000);
  dbms_output.put_line('DATE NOCOPY: ' ||(systimestamp -s));
end;
/

/*****************NUMBER*********************/
declare
  x_dummy number := 99999999;
  s timestamp;
begin
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy, 'COPY', 40000000);
  dbms_output.put_line('NUMBER COPY  : ' ||(systimestamp -s));
  
  x_dummy := 99999999;
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 40000000);
  dbms_output.put_line('NUMBER NOCOPY: ' ||(systimestamp -s));
end;
/

/*****************CLOB*********************/
declare
  x_dummy clob := 'a';
  s timestamp;
begin
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy, 'COPY', 200000);
  dbms_output.put_line('CLOB COPY  : ' ||(systimestamp -s));
  
  x_dummy := 'a';
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 200000);
  dbms_output.put_line('CLOB NOCOPY: ' ||(systimestamp -s));
end;
/

/*****************COLLECTION*********************/
declare
  x_dummy sys.ODCIVARCHAR2LIST := sys.ODCIVARCHAR2LIST();
  s timestamp;
begin
  x_dummy.extend(1000);
  for i in 1..x_dummy.count loop
    x_dummy(i) := lpad('a', 1000, 'a');
  end loop;
  
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy, 'COPY', 100000);
  dbms_output.put_line('ARRAY COPY  : ' ||(systimestamp -s));
  
  
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 100000);
  dbms_output.put_line('ARRAY NOCOPY: ' ||(systimestamp -s));
end;
/

set serveroutput on;

/*****************VARCHAR2*********************/

declare

x_dummy varchar2(2000) := 'a';

s timestamp;

begin

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy, 'COPY', 40000000);

dbms_output.put_line('VARCHAR2 COPY : ' ||(systimestamp -s));

x_dummy := 'a';

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 40000000);

dbms_output.put_line('VARCHAR2 NOCOPY: ' ||(systimestamp -s));

end;

/*****************DATE*********************/

declare

x_dummy date := trunc(sysdate);

s timestamp;

begin

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy, 'COPY', 10000000);

dbms_output.put_line('DATE COPY : ' ||(systimestamp -s));

x_dummy := trunc(sysdate);

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 10000000);

dbms_output.put_line('DATE NOCOPY: ' ||(systimestamp -s));

end;

/*****************NUMBER*********************/

declare

x_dummy number := 99999999;

s timestamp;

begin

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy, 'COPY', 40000000);

dbms_output.put_line('NUMBER COPY : ' ||(systimestamp -s));

x_dummy := 99999999;

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 40000000);

dbms_output.put_line('NUMBER NOCOPY: ' ||(systimestamp -s));

end;

/*****************CLOB*********************/

declare

x_dummy clob := 'a';

s timestamp;

begin

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy, 'COPY', 200000);

dbms_output.put_line('CLOB COPY : ' ||(systimestamp -s));

x_dummy := 'a';

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 200000);

dbms_output.put_line('CLOB NOCOPY: ' ||(systimestamp -s));

end;

/*****************COLLECTION*********************/

declare

x_dummy sys.ODCIVARCHAR2LIST := sys.ODCIVARCHAR2LIST();

s timestamp;

begin

x_dummy.extend(1000);

for i in 1..x_dummy.count loop

x_dummy(i) := lpad('a', 1000, 'a');

end loop;

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy, 'COPY', 100000);

dbms_output.put_line('ARRAY COPY : ' ||(systimestamp -s));

s := systimestamp;

pkg_Test_parameter.sp_main(x_dummy,'NOCOPY', 100000);

dbms_output.put_line('ARRAY NOCOPY: ' ||(systimestamp -s));

end;

My sp_main procedure gets 3 arguments, first is the tested type parameter, second is calling type, COPY or NOCOPY and third one is number of calls.

So big types like clob and collection has the biggest difference. What surprises me are NUMBER and DATE types. they have almost no difference when used NOCOPY.

Here are the results:

VARCHAR2 COPY  : +000000000 00:00:21.672000000
VARCHAR2 NOCOPY: +000000000 00:00:14.823000000

DATE COPY  : +000000000 00:00:06.627000000
DATE NOCOPY: +000000000 00:00:06.220000000

NUMBER COPY  : +000000000 00:00:07.792000000
NUMBER NOCOPY: +000000000 00:00:06.628000000

CLOB COPY  : +000000000 00:00:33.215000000
CLOB NOCOPY: +000000000 00:00:04.886000000

ARRAY COPY  : +000000000 00:00:17.959000000
ARRAY NOCOPY: +000000000 00:00:00.022000000

VARCHAR2 COPY : +000000000 00:00:21.672000000

VARCHAR2 NOCOPY: +000000000 00:00:14.823000000

DATE COPY : +000000000 00:00:06.627000000

DATE NOCOPY: +000000000 00:00:06.220000000

NUMBER COPY : +000000000 00:00:07.792000000

NUMBER NOCOPY: +000000000 00:00:06.628000000

CLOB COPY : +000000000 00:00:33.215000000

CLOB NOCOPY: +000000000 00:00:04.886000000

ARRAY COPY : +000000000 00:00:17.959000000

ARRAY NOCOPY: +000000000 00:00:00.022000000

as you can see Varchar2 has almost twice of the difference and please consider, when you work with bigger variable data and more than one variable, difference will be much more than this.

As I said before, CLOB and the COLLECTION has the biggest difference. I mostly use NOCOPY with my collection and clob parameters even if I won’t be modifying them. Of course that requires a little bit more attention. Modifying by mistake it can cause headaches.

Using NOCOPY for those parameters is definitely increase your performance but also can cause some inconsistent situations. Let’s say you pass a collection using NOCOPY to a procedure and started to modify it in the procedure (in a loop maybe) after doing some job your plsql raise an unexpected error. In this case your variable will be partially modified and this is not something we desire. you must be careful using NOCOPY against this kind of situations. When you did not use a NOCOPY, you won’t be dealing with this and your collection (or other type of variable) will be consistent.

I hope this help you increase your codes performance.

I wish you healthy days.

Edit:

Since number is one of the smallest type it is not much possible to demonstrate performance gain with just one parameter. I just added 15 more number parameter to my test procedures and result is like this:

NUMBER COPY : +000000000 00:00:13.327000000
NUMBER NOCOPY: +000000000 00:00:08.126000000

1 2	NUMBER COPY : +000000000 00:00:13.327000000 NUMBER NOCOPY: +000000000 00:00:08.126000000