Full Database Caching in #Oracle 12c

If your Oracle Database fits into the buffer cache, we will put all segments into it now upon access – no more direct reads:

SQL> connect / as sysdba
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select sum(bytes)/1024/1024 as mb from v$datafile;

	MB
----------
      1424

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE
------------
	1872

The requirements are met here, I don’t need to force anything:

SQL> select force_full_db_caching from v$database;

FORCE_FUL
---------
NO

SQL> select bytes/1024/1024 as mb from dba_segments 
     where owner='ADAM' and segment_name='SALES';

	MB
----------
       600

SQL> select count(*) from adam.sales;

  COUNT(*)
----------
  10000000

SQL> show parameter parallel_degree_policy

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
parallel_degree_policy		     string
MANUAL

The table got accessed once and is now loaded into the buffer cache. PARALLEL_DEGREE_POLICY is on the default MANUAL, remember that.

SQL> connect adam/adam
Connected.
SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads					   42
physical reads						    0
physical reads direct					    0

SQL> select count(*) from sales;

  COUNT(*)
----------
  10000000

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads					75620
physical reads						    0
physical reads direct					    0

Only logical reads, because the table is in the buffer cache. Also for parallel queries:

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select * from v$pq_sesstat;

STATISTIC					   LAST_QUERY SESSION_TOTAL	CON_ID
-------------------------------------------------- ---------- ------------- ----------
Queries Parallelized					    1		  1	     0
DML Parallelized					    0		  0	     0
DDL Parallelized					    0		  0	     0
DFO Trees						    1		  1	     0
Server Threads						    4		  0	     0
Allocation Height					    4		  0	     0
Allocation Width					    1		  0	     0
Local Msgs Sent 					  156		156	     0
Distr Msgs Sent 					    0		  0	     0
Local Msgs Recv'd					  156		156	     0
Distr Msgs Recv'd					    0		  0	     0
DOP							    4		  0	     0
Slave Sets						    1		  0	     0

13 rows selected.

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads				       152410
physical reads						    0
physical reads direct					    0

SQL> select name,value from v$sysstat where name='physical reads direct';

NAME							VALUE
-------------------------------------------------- ----------
physical reads direct					   21

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select name,value from v$sysstat where name='physical reads direct';

NAME							VALUE
-------------------------------------------------- ----------
physical reads direct					   21

Looks like I did In-Memory Parallel Query although PARALLEL_DEGREE_POLICY is on MANUAL, doesn’t it? Just for comparison, I did the same with an 11g version:

SQL> select banner from v$version;

BANNER
-------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select sum(bytes)/1024/1024 as mb from v$datafile;

	MB
----------
      1090

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE
------------
	1200

SQL> select bytes/1024/1024 as mb from dba_segments where owner='ADAM' and segment_name='SALES';

	MB
----------
       528


SQL> select count(*) from adam.sales;

  COUNT(*)
----------
  10000000

SQL> show parameter parallel_degree_policy

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ---------
parallel_degree_policy		     string			       MANUAL

SQL> connect adam/adam
Connected.
SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							       26
physical reads									0
physical reads direct								0

SQL> select count(*) from sales;

  COUNT(*)
----------
  10000000

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							    67465
physical reads								    67433
physical reads direct							    67433

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select * from v$pq_sesstat;

STATISTIC				   LAST_QUERY SESSION_TOTAL
------------------------------------------ ---------- -------------
Queries Parallelized				    1		  1
DML Parallelized				    0		  0
DDL Parallelized				    0		  0
DFO Trees					    1		  1
Server Threads					    4		  0
Allocation Height				    4		  0
Allocation Width				    1		  0
Local Msgs Sent 				  116		116
Distr Msgs Sent 				    0		  0
Local Msgs Recv'd				  116		116
Distr Msgs Recv'd				    0		  0

11 rows selected.

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							   135115
physical reads								   134866
physical reads direct							   134866

As you can see, I got direct reads in 11g for both the serial and the parallel query.
Do not confuse this feature with the In-Memory Option, it is much less sophisticated than that. But it doesn’t come with an extra charge at least 🙂

, ,

  1. #1 by Hemant K Chitale on December 2, 2015 - 11:26

    V$MYSTAT when running ParallelQuery shouldn’t be reporting that ‘direct path read’ count because the ‘direct path read’ would have been done by the PX Server sessions, not my own session.

  2. #2 by Uwe Hesse on December 2, 2015 - 11:56

    Hemant, thanks for your comment. I have added the output of V$SYSSTAT to show that there was no increase in the number of direct reads after the parallel query. Looks like V$MYSTAT does reflect the direct reads done by the parallel query in the 11g part of the demo, though, doesn’t it? Can’t be the query coordinator (my session) who does the direct reads there.
    Another good way to show the absence of direct reads upon parallel query with full database caching would have been VSEGMENT_STATISTICS:
    SQL> select statistic_name,value from v$segment_statistics where owner=’ADAM’ and OBJECT_NAME=’SALES’ and statistic_name like ‘%reads%’;

    STATISTIC_NAME VALUE
    —————————————- ———-
    logical reads 229136
    physical reads 75562
    physical reads direct 0
    optimized physical reads 0

    Just didn’t think about it while I first wrote the article 🙂

  3. #3 by jimroll on December 2, 2015 - 12:06

    Hi, Uwe. Thanks for your post.
    Have you checked, whats happend when DB size becomes more than buffer pool?

  4. #4 by Uwe Hesse on December 2, 2015 - 12:12

    Hi Jim, no I didn’t. Without ALTER DATABASE FORCE FULL DATABASE CACHING, I suppose that the system will just revert to the pre-12c behavior and do direct reads again.

  5. #5 by jimroll on December 2, 2015 - 12:27

    It’s interesting to know, what happens in “force full database caching” mode. However, my databases are bigger than buffer pool and they are grows up for all time. So this feature, unfortunatlty, is not my option ((

  6. #6 by fouedgray on December 2, 2015 - 15:23

    Thank you for sharing

  7. #7 by markus on December 9, 2015 - 17:18

    hello Uwe,
    what about indizes? are they obsolete?
    would the optimizer then always use fulltable-scan instead of index-walking ?

  8. #8 by Uwe Hesse on December 10, 2015 - 09:36

    Markus, indexes can be still useful for the same reason as before when your tables were not all residing in the buffer cache: It takes time to scan through all blocks of the table. Especially when you need just a small fraction of rows, indexes may still speed up the query significantly.

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

%d bloggers like this: