Subquery Caching – Keep Learning Keep Living

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.

Leave a Reply Cancel reply