Skip to Main Content
  • Questions

  • How Result cache is managed in 12c Pluggable Database (PDB)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ishan.

Asked: October 31, 2017 - 1:36 pm UTC

Last updated: November 03, 2017 - 7:08 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Team,

I ma having one scenario, where I am setting up my application in 3 pluggable db instances under single CDB. As per my app requirement, I have to create synonym for dbms_result_cache in all 3 PDBs. As the public synonym for dbms_result_cache is created at CDB level. I am getting error while creating synonym in other PDBs. (ORA-00955: name is already used by an existing object as it is already created at Container Database (CDB) level)

If synonym is created at CDB level, How it will manage the result cache for same app for different pdbs. i.e how it will identified the result cache for which pdb is calling and how it will provide correct values for each call for result cache for same code execution in different PDBs.

appreciate if you can provide sample example for the same.

Thanks in advance.

and Connor said...

Looks like currently it is a "free for all" for the pdb's to compete for the result cache. The reference

https://docs.oracle.com/database/122/REFRN/RESULT_CACHE_MAX_SIZE.htm#REFRN10272

says the the size is not settable at pdb level.

We can see this in effect by using two pdb's and 'swamping' the result cache. Here's my first one

SQL> show con_name

CON_NAME
------------------------------
PDB122A
SQL> show parameter result_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 15424K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
SQL> create table t as select * from dba_objects;

Table created.

SQL> insert into t select * from t;

72716 rows created.

SQL> insert into t select * from t;

145432 rows created.

SQL> insert into t select * from t;

290864 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name = 'T';

    BLOCKS
----------
     12169

1 row selected.

--
-- Now generate 50 different result cache queries
--

SQL>
SQL> declare
  2    type tlist is table of t%rowtype index by pls_integer;
  3    r tlist;
  4    rc sys_refcursor;
  5  begin
  6  for i in 1 .. 50 loop
  7    open rc for 'select /*+ result_cache */ t'||i||'.* from t t'||i||' where rownum <= 5000';
  8    fetch rc bulk collect into r;
  9    close rc;
 10  end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>
SQL> select name, status, space_overhead from V$RESULT_CACHE_OBJECTS
  2  where name like '%result%'
  3  order by 1;

NAME                                     STATUS    SPACE_OVERHEAD
---------------------------------------- --------- --------------
select /*+ result_cache */ t26.* from t  Published          14528
t26 where rownum <= 5000

select /*+ result_cache */ t27.* from t  Published          14528
t27 where rownum <= 5000

select /*+ result_cache */ t28.* from t  Published          14528
t28 where rownum <= 5000

select /*+ result_cache */ t29.* from t  Published          14528
t29 where rownum <= 5000

select /*+ result_cache */ t30.* from t  Published          14528
t30 where rownum <= 5000

select /*+ result_cache */ t31.* from t  Published          14528
t31 where rownum <= 5000

select /*+ result_cache */ t32.* from t  Published          14528
t32 where rownum <= 5000

select /*+ result_cache */ t33.* from t  Published          14528
t33 where rownum <= 5000

select /*+ result_cache */ t34.* from t  Published          14528
t34 where rownum <= 5000

select /*+ result_cache */ t35.* from t  Published          14528
t35 where rownum <= 5000

select /*+ result_cache */ t36.* from t  Published          14528
t36 where rownum <= 5000

select /*+ result_cache */ t37.* from t  Published          14528
t37 where rownum <= 5000

select /*+ result_cache */ t38.* from t  Published          14528
t38 where rownum <= 5000

select /*+ result_cache */ t39.* from t  Published          14528
t39 where rownum <= 5000

select /*+ result_cache */ t40.* from t  Published          14528
t40 where rownum <= 5000

select /*+ result_cache */ t41.* from t  Published          14528
t41 where rownum <= 5000

select /*+ result_cache */ t42.* from t  Published          14528
t42 where rownum <= 5000

select /*+ result_cache */ t43.* from t  Published          14528
t43 where rownum <= 5000

select /*+ result_cache */ t44.* from t  Published          14528
t44 where rownum <= 5000

select /*+ result_cache */ t45.* from t  Published          14528
t45 where rownum <= 5000

select /*+ result_cache */ t46.* from t  Published          14528
t46 where rownum <= 5000

select /*+ result_cache */ t47.* from t  Published          14528
t47 where rownum <= 5000

select /*+ result_cache */ t48.* from t  Published          14528
t48 where rownum <= 5000

select /*+ result_cache */ t49.* from t  Published          14528
t49 where rownum <= 5000

select /*+ result_cache */ t50.* from t  Published          14528
t50 where rownum <= 5000


25 rows selected.


So I tried to slam 50 large results into the result cache, and you can see that I've only managed to hold onto the last 25 I executed. Now I'll do the same thing in a different PDB in the same container. I'll alias my queries slightly differently (with an 'x' suffix) so we know that they are not the ones above.

SQL> show con_name

CON_NAME
------------------------------
PDB99
SQL> show parameter result_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 15424K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
SQL> create table t as select * from dba_objects;

Table created.

SQL> insert into t select * from t;

72772 rows created.

SQL> insert into t select * from t;

145544 rows created.

SQL> insert into t select * from t;

291088 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name = 'T';

    BLOCKS
----------
     12169

1 row selected.

SQL> declare
  2    type tlist is table of t%rowtype index by pls_integer;
  3    r tlist;
  4    rc sys_refcursor;
  5  begin
  6  for i in 1 .. 50 loop
  7    open rc for 'select /*+ result_cache */ tx'||i||'.* from t tx'||i||' where rownum <= 5000';
  8    fetch rc bulk collect into r;
  9    close rc;
 10  end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> select name, status, space_overhead from V$RESULT_CACHE_OBJECTS
  2  where name like '%result%'
  3  order by 1;

NAME                                               STATUS    SPACE_OVERHEAD
-------------------------------------------------- --------- --------------
select /*+ result_cache */ tx26.* from t tx26 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx27.* from t tx27 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx28.* from t tx28 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx29.* from t tx29 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx30.* from t tx30 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx31.* from t tx31 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx32.* from t tx32 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx33.* from t tx33 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx34.* from t tx34 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx35.* from t tx35 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx36.* from t tx36 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx37.* from t tx37 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx38.* from t tx38 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx39.* from t tx39 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx40.* from t tx40 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx41.* from t tx41 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx42.* from t tx42 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx43.* from t tx43 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx44.* from t tx44 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx45.* from t tx45 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx46.* from t tx46 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx47.* from t tx47 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx48.* from t tx48 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx49.* from t tx49 wher Published          14530
e rownum <= 5000

select /*+ result_cache */ tx50.* from t tx50 wher Published          14530
e rownum <= 5000


25 rows selected.

SQL>
SQL>
SQL>


Once again, we could only fit 25 queries in.

Now ... if the result cache's were isolated between pdb's, then this would not have an impact on our first pdb. But when we head back to PDB122A and re-run our query:

SQL>
SQL> select name, status, space_overhead from V$RESULT_CACHE_OBJECTS
  2  where name like '%result%'
  3  order by 1;

no rows selected

SQL>


Our result cache has been "stolen" from us.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PDB-Isolation

Racer I., November 03, 2017 - 9:49 am UTC

Hi,

Ok. You probably have to work with coordinating this between PDBs (size the cache bigger). Also has the potential of better resource usage. If one PDB unterutilizes the result cache that leaves more room for others.

But the OP question is interesting. Are the results tagged with their PDB-ID? Or can you get another PDBs cached result if the SQL-Statement looks the same?

regards,
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library