Home > Oracle > Memory flushing and PDBs

Memory flushing and PDBs


In a multitenant database environment, PDBs are plugged into a CDB container sharing a single SGA.  All the PDB objects are loaded into a single SGA component and there are no ISPDB_MODIFIABLE SGA parameters.  Looking at the V$LATCH_CHILDREN, all the CBC latches are defined under CON_ID  1 which is CDB$ROOT.


SQL> show CON_ID

CON_ID

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

1

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> select con_id ,count(*) from v$latch_children where name='cache buffers chains' group by con_id;

CON_ID   COUNT(*)

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

0    16384

1

 

And from a PDB, the CBC structures are still belongs to the ROOT container.

1

SQL> show con_id

CON_ID

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

3

SQL> show con_name

CON_NAME

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

SALESDB

SQL>  select con_id ,count(*) from v$latch_children where name='cache buffers chains' group by con_id;

CON_ID   COUNT(*)

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

0      16384

Also, looking at the buffer  cache ‘working set’ , both the PDBs and CDB are sharing a single buffer cache working set.  From CDB


SQL> show con_id

CON_ID

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

1

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> select SET_ID,DBWR_NUM,CNUM_SET,POOL_ID from x$kcbwds;

SET_ID   DBWR_NUM   CNUM_SET    POOL_ID

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

1          0          0          1

2          0          0          1

3          0          0          2

4          0          0          2

5          0      76713          3

6          0      76713          3

7          0          0          4

8          0          0          4

9          0          0          5

10          0          0          5

11          0          0          6

12          0          0          6

13          0          0          7

14          0          0          7

15          0          0          8

16          0          0          8

16 rows selected.

And the PDB  also pointing to the same working set.


SQL> show con_id

CON_ID

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

3

SQL> show con_name

CON_NAME

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

SALESDB

SQL> select SET_ID,DBWR_NUM,CNUM_SET,POOL_ID from x$kcbwds;

SET_ID   DBWR_NUM   CNUM_SET    POOL_ID

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

1          0          0          1

2          0          0          1

3          0          0          2

4          0          0          2

5          0      76713          3

6          0      76713          3

7          0          0          4

8          0          0          4

9          0          0          5

10          0          0          5

11          0          0          6

12          0          0          6

13          0          0          7

14          0          0          7

15          0          0          8

16          0          0          8

16 rows selected.

 

So, what will happen if we flush buffer cache from any container?  Since all the containers are sharing the same SGA, I was expecting oracle will flush out all the blocks from buffer cache despite of which PDB /CDB it is coming from.  But, I was totally wrong.  Oracle was able to identify the ownership and flushed out only the blocks belongs that specific container.

Both PDB and CDB shows no cached objects from the schema THOMAS.


SQL> show con_name

 

CON_NAME

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

SALESDB

SQL> set linesize 200

col object_name for a30

col owner for a30

select CON_ID,object_name,owner,STATE

from dba_objects o, x$bh b

where o.object_id = b.obj and owner  in ('THOMAS') and state <> 0;

&nbsp;

no rows selected

And from the CDB


SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> set linesize 200

col object_name for a30

col owner for a30

select CON_ID,object_name,owner,STATE

from dba_objects o, x$bh b

where o.object_id = b.obj and owner  in ('THOMAS') and state <> 0;

&nbsp;

no rows selected

After selecting from the object, I can see the blocks are cached in the PDB.


SQL> select object_name from thomas.test where object_id=456;

OBJECT_NAME

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

STMT_AUDIT_OPTION_MAP

&nbsp;

SQL> set linesize 200

col object_name for a30

col owner for a30

select CON_ID,object_name,owner,STATE

from dba_objects o, x$bh b

where o.object_id = b.obj and owner  in ('THOMAS') and state <> 0;

&nbsp;

CON_ID OBJECT_NAME                    OWNER                               STATE

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

3 I_TEST                         THOMAS                                  1

3 I_TEST                         THOMAS                                  1

3 I_TEST                         THOMAS                                  1

3 I_TEST                         THOMAS                                  1

3 I_TEST                         THOMAS                                  1

 

Now I flushed the buffer cache from the CDB$ROOT expecting it will flush out all the blocks from the buffer cache including PDBs.


SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> !date

Thu Dec 19 11:53:50 UTC 2013

SQL> alter system flush buffer_cache;

System altered.

But contrary to my expectation, I was able to find the blocks cached in the buffer cache.


SQL> show con_name

CON_NAME

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

SALESDB

SQL> !date

Thu Dec 19 11:54:08 UTC 2013

SQL> set linesize 200

col object_name for a30

col owner for a30

select CON_ID,object_name,owner,STATE

from dba_objects o, x$bh b

where o.object_id = b.obj and owner  in ('THOMAS') and state <> 0; SQL> SQL> SQL>   2    3

CON_ID OBJECT_NAME                    OWNER                               STATE

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

3 I_TEST                         THOMAS                                  1

3 I_TEST                         THOMAS                                  1

3 I_TEST                         THOMAS                                  1

3 I_TEST                         THOMAS                                  1

3 I_TEST                         THOMAS                                  1

Even though Oracle is using a single buffer cache for PDBs and CDB, still it is able to manage flushing caches and able to flush out only caches belongs to the current container.

About these ads
Categories: Oracle Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: