Oracle 如何在12c可插拔数据库 (PDB) 中管理结果缓存

嗨，团队，

我有一个场景，我在单个CDB下的3个可插拔数据库实例中设置我的应用程序。根据我的应用程序要求，我必须在所有3个pdb中创建dbms_result_cache的同义词。因为dbms_result_cache的公共同义词是在CDB级别创建的。我在其他pdb中创建同义词时出错。(ORA-00955: 名称已被现有对象使用，因为它已在容器数据库 (CDB) 级别创建)

如果同义词是在CDB级别创建的，它将如何管理不同pdb的同一应用程序的结果缓存。即，它将如何识别pdb正在调用的结果缓存，以及它将如何为不同pdb中相同代码执行的结果缓存的每次调用提供正确的值。

如果您可以提供相同的示例示例，请不胜感激。

提前谢谢。

看起来目前pdb竞争结果缓存是 “所有人免费的”。参考

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

表示大小在pdb级别不可设置。

我们可以通过使用两个pdb并 “淹没” 结果缓存来看到这一点。这是我的第一个

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.

所以我试图将50个大结果猛击到结果缓存中，你可以看到我只设法保留了我执行的最后25个。现在，我将在同一容器中的不同PDB中执行相同的操作。我的查询别名略有不同 (后缀为 “x”)，因此我们知道它们不是上面的查询。

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>

再一次，我们只能容纳25个查询。

现在...如果结果缓存在pdb之间隔离，则不会对我们的第一个pdb产生影响。但是当我们回到PDB122A并重新运行我们的查询时:

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>

我们的结果缓存被我们 “偷走” 了。