{"id":73118,"date":"2016-04-13T14:06:37","date_gmt":"2016-04-13T14:06:37","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/automatic-big-table-caching-in-rac\/"},"modified":"2021-07-14T13:07:08","modified_gmt":"2021-07-14T13:07:08","slug":"automatic-big-table-caching-in-rac","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/automatic-big-table-caching-in-rac\/","title":{"rendered":"Automatic Big Table Caching in RAC"},"content":{"rendered":"<p>In an earlier article, <a href=\"https:\/\/allthingsoracle.com\/oracle-database-12-1-0-2c-automatic-big-table-cache\/\">I discussed Automatic big table cache<\/a> (ABTC) which is a new feature introduced in Oracle Database 12.1.0.2c. Automatic big table cache is an optional section of the buffer cache reserved for storing large objects. It tracks only big tables and automatically caches scanned big objects in big table cache thereby avoiding direct path reads. It caches partial objects when objects cannot be fully cached. It is primarily designed to enhance performance for data warehouse workloads, but also improves performance in mixed workloads. Though it can be used for Serial as well as parallel full scans of large objects in Single Instance environments, it is supported only for parallel (not serial) full scans of large objects in Oracle Real Application Clusters (Oracle RAC) configurations.<\/p>\n<p>In my last article, I demonstrated ABTC for serial scans in a 12.1.0.2c single instance database. In this article, I will demonstrate ABTC for parallel scans in a 12.1.0.2c RAC database.<\/p>\n<h2>Current scenario:<\/h2>\n<ul>\n<li>Name of cluster: Cluster01<\/li>\n<li>Number of nodes : 3<\/li>\n<li>Name of RAC database (Non-CDB): orcl<\/li>\n<li>Number of RAC database instances : 3<\/li>\n<\/ul>\n<h2>Overview:<\/h2>\n<p>This article will cover the steps needed to test ABTC for parallel scans.<\/p>\n<ul>\n<li>Check the size of the default buffer cache and number of buffers in it for all the instances.<\/li>\n<li>Ensure that full database caching is disabled on all the instances.<\/li>\n<li>Check the value of the parameter <code>_SMALL_TABLE_THRESHOLD<\/code>.<\/li>\n<li>Verify that Hr.big_table1 qualifies as a big table and can leverage ABTC.<\/li>\n<li>Set <code>PARALLEL_DEGREE_POLICY = AUTO<\/code> on all the instances<\/li>\n<li>Set\u00a0<code>DB_BIG_TABLE_CACHE_PERCENT_TARGET<\/code>\u00a0to 0 (default) on all the instances so that ABTC is disabled on all the instances.<\/li>\n<li>Verify that serial scan of <code>HR.BIG_TABLE1<\/code> results in direct path reads.<\/li>\n<li>Configure Big table Cache to 25% on all the instances.<\/li>\n<li>Query the big table in serial to verify that direct reads take place and that it does not get cached in big table cache as serial scans are not supported in RAC.<\/li>\n<li>Query the big table in parallel and verify that its fragments get distributed in big table cache across instances.<\/li>\n<li>Set\u00a0<code>DB_BIG_TABLE_CACHE_PERCENT_TARGET<\/code>\u00a0to 0 on instance 3 and verify that in that instance, the fragment of table which was cached in big table cache is no longer cached.<\/li>\n<li>Set\u00a0<code>DB_BIG_TABLE_CACHE_PERCENT_TARGET<\/code>\u00a0back to 25 on instance 3 and verify that the table data which was allocated to big table cache on instance 3, has again been read from disk into the big table cache.<\/li>\n<li>Drop table <code>HR.BIG_TABLE1<\/code> and verify that another big table <code>HR.BIG_TABLE_PART<\/code> has 3 partitions, each of which qualify as a big segment.<\/li>\n<li>Scan partitions p1 and p2 of the table <code>HR.BIG_TABLE1<\/code> &#8211; Data from both the partitions p1 and p2 is distributed across instances.<\/li>\n<li>To disable caching the data from a partition in all instance except one, set parameter <code>PARALLEL_LOCAL_FORCE = TRUE<\/code> on instance 1.<\/li>\n<li>Issue parallel query on partition p3 from instance orcl1 and verify that blocks of partition p1 are cached in ABTC of instance orcl1 only.<\/li>\n<\/ul>\n<h2>Demonstration:<\/h2>\n<ul>\n<li>Check the size of the default buffer cache and number of buffers in it for all the instances:<\/li>\n<\/ul>\n<pre>SQL&gt; SELECT inst_id, name,block_size,buffers FROM gv$buffer_pool order by 1;\r\n\r\n   INST_ID NAME                                     BLOCK_SIZE    BUFFERS\r\n---------- ---------------------------------------- ---------- ----------\r\n         1 DEFAULT                                        8192      <span style=\"color: red; font-weight: bold;\">18760<\/span>\r\n         2 DEFAULT                                        8192      <span style=\"color: red; font-weight: bold;\">18760<\/span>\r\n         3 DEFAULT                                        8192      <span style=\"color: red; font-weight: bold;\">18760<\/span>\r\n\r\nSQL&gt;   select inst_id, COMPONENT, CURRENT_SIZE\/1024\/1024 SIZE_MB\r\n       from gv$sga_dynamic_components\r\n        where component = 'DEFAULT buffer cache'\r\n        order by 1;\r\n\r\n   INST_ID COMPONENT                         SIZE_MB\r\n---------- ------------------------------ ----------\r\n         1 DEFAULT buffer cache                  <span style=\"color: red; font-weight: bold;\">160<\/span>\r\n         2 DEFAULT buffer cache                  <span style=\"color: red; font-weight: bold;\">160<\/span>\r\n         3 DEFAULT buffer cache                  <span style=\"color: red; font-weight: bold;\">160<\/span><\/pre>\n<ul>\n<li>Ensure that full database caching is disabled on all the instances.<\/li>\n<\/ul>\n<pre>SQL&gt;select inst_id, force_full_db_caching \r\n    from gv$database order by 1;\r\n    \r\n   INST_ID FORCE_FULL_DB_CACHING\r\n---------- ------------------------------\r\n         1 <span style=\"color: red; font-weight: bold;\">NO<\/span>\r\n         2 <span style=\"color: red; font-weight: bold;\">NO<\/span>\r\n         3 <span style=\"color: red; font-weight: bold;\">NO<\/span><\/pre>\n<ul>\n<li>Check the value of the parameter <code>_SMALL_TABLE_THRESHOLD<\/code>, since an object occupying number blocks higher than this value is considered big.<\/li>\n<\/ul>\n<pre>SQL&gt;  SELECT  a.ksppinm  \"Parameter\", a.ksppdesc \"Description\", c.ksppstvl \"Value\"\r\n      FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p\r\n      WHERE a.indx = b.indx AND a.indx = c.indx\r\n       AND p.name(+) = a.ksppinm\r\n       AND UPPER(a.ksppinm) IN ( UPPER('_db_block_buffers'),  \r\n                                 UPPER('_small_table_threshold'));\r\n\r\n\r\nParameter                      Description                    Value\r\n------------------------------ ------------------------------ ---------------\r\n_db_block_buffers              Number of database blocks cach 18760\r\n                               ed in memory: hidden parameter\r\n\r\n_small_table_threshold         lower threshold level of table <span style=\"color: red; font-weight: bold;\">375<\/span>\r\n                                size for direct reads<\/pre>\n<ul>\n<li>Verify that <code>Hr.big_table1<\/code> qualifies as a big table and can leverage ABTC.<\/li>\n<\/ul>\n<pre>SQL&gt; Select blocks from dba_tables where table_name = 'BIG_TABLE1';\r\n     \r\n    BLOCKS\r\n----------\r\n      <span style=\"color: red; font-weight: bold;\">3928<\/span><\/pre>\n<ul>\n<li>Set <code>PARALLEL_DEGREE_POLICY = AUTO<\/code> on all the instances<\/li>\n<li>Set\u00a0<code>DB_BIG_TABLE_CACHE_PERCENT_TARGET<\/code>\u00a0to 0 (default) on all the instances so that ABTC is disabled on all the instances.<\/li>\n<\/ul>\n<pre>SQL&gt;Alter system set PARALLEL_DEGREE_POLICY = AUTO  ; \r\n    alter system set db_big_table_cache_percent_target=0 scope=both; \r\n    select inst_id, name, value \r\n    from gv$parameter\r\n    where name in ('parallel_degree_policy','db_big_table_cache_percent_target')\r\n    order by 2,1;\r\n\r\n INST_ID NAME                                     VALUE\r\n---------- ---------------------------------------- ---------------\r\n         1 db_big_table_cache_percent_target        0\r\n         2 db_big_table_cache_percent_target        0\r\n         3 db_big_table_cache_percent_target        0\r\n         1 parallel_degree_policy                   AUTO\r\n         2 parallel_degree_policy                   AUTO\r\n         3 parallel_degree_policy                   AUTO<\/pre>\n<ul>\n<li>Verify that serial scan of <code>HR.BIG_TABLE1<\/code> results in direct path reads.<\/li>\n<\/ul>\n<pre>SQL&gt; select value from v$mystat where statistic#=\r\n        (select statistic# from v$statname\r\n          where name='table scans (direct read)');\r\nVALUE\r\n-----\r\n    <span style=\"color: red; font-weight: bold;\">0<\/span>\r\nSQL&gt;set autot traceonly statistics \r\n    select count(*) from hr.big_table1;\r\n    set autot off\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n         91  recursive calls\r\n          0  db block gets\r\n       3937  consistent gets\r\n       <span style=\"color: red; font-weight: bold;\">3861  physical reads<\/span>\r\n          0  redo size\r\n        542  bytes sent via SQL*Net to client\r\n        551  bytes received via SQL*Net from client\r\n          2  SQL*Net roundtrips to\/from client\r\n          5  sorts (memory)\r\n          0  sorts (disk)\r\n          1  rows processed\r\n\r\nSQL&gt; select value from v$mystat where statistic#=\r\n        (select statistic# from v$statname\r\n        where name='table scans (direct read)');\r\nVALUE\r\n-----\r\n    <span style=\"color: red; font-weight: bold;\">1<\/span><\/pre>\n<p>If we re-execute the query, almost the same number of physical reads appearing again confirm that a direct read was performed.<\/p>\n<pre>SQL&gt;set autot traceonly statistics \r\n    select count(*) from hr.big_table1;\r\n    set autot off\r\n \r\n\r\nStatistics\r\n----------------------------------------------------------\r\n          0  recursive calls\r\n          0  db block gets\r\n       3861  consistent gets\r\n       <span style=\"color: red; font-weight: bold;\">3858<\/span>  physical reads\r\n          0  redo size\r\n        542  bytes sent via SQL*Net to client\r\n        551  bytes received via SQL*Net from client\r\n          2  SQL*Net roundtrips to\/from client\r\n          0  sorts (memory)\r\n          0  sorts (disk)\r\n          1  rows processed<\/pre>\n<ul>\n<li>Configure Big Table Cache to 25% on all the instances.<\/li>\n<\/ul>\n<pre>SQL&gt;alter system set db_big_table_cache_percent_target = 25;\r\n     select inst_id, name, value from gv$parameter \r\n     where name in    ('parallel_degree_policy','db_big_table_cache_percent_target')\r\n     order by 2,1;\r\n\r\n   INST_ID NAME                                     VALUE\r\n---------- ---------------------------------------- --------------------\r\n         1 db_big_table_cache_percent_target        25\r\n         2 db_big_table_cache_percent_target        25\r\n         3 db_big_table_cache_percent_target        25\r\n         1 parallel_degree_policy                   AUTO\r\n         2 parallel_degree_policy                   AUTO\r\n         3 parallel_degree_policy                   AUTO<\/pre>\n<p>We can verify that 25% of the buffer cache has been \u2018reserved\u2019 for big table caching on all the instances and no objects are cached in ABTC presently.<\/p>\n<pre>SQL&gt; select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP \r\n     from gv$bt_scan_cache\r\n      order by 1;\r\n\r\n   INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP\r\n---------- --------------- ------------ ---------------- ---------------\r\n         1              <span style=\"color: red; font-weight: bold;\">25<\/span>            0                0            1000\r\n         2              <span style=\"color: red; font-weight: bold;\">25<\/span>            0                0            1000\r\n         3              <span style=\"color: red; font-weight: bold;\">25<\/span>            0                0            1000\r\n\r\nSQL&gt; Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem from \r\ngv$bt_scan_obj_temps  b, dba_objects o \r\nwhere b.dataobj#  = o.data_object_id\r\norder by 1,2;\r\n    \r\n<span style=\"color: red; font-weight: bold;\">no rows selected<\/span><\/pre>\n<ul>\n<li>Query the big table in serial and verify that direct reads take place and it does not get cached in big table cache as serial scans are not supported in RAC.<\/li>\n<\/ul>\n<pre>SQL&gt;select value from v$mystat where statistic#=\r\n       (select statistic# from v$statname\r\n        where name='table scans (direct read)');\r\nVALUE\r\n-----\r\n    <span style=\"color: red; font-weight: bold;\">2<\/span>\r\nSQL&gt; set autot traceonly statistics\r\n     set timing on;\r\n     select count(*) from hr.big_table1;\r\n     set timing off;\r\n     set autot off\r\n\r\nElapsed: 00:00:00.63\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n          1  recursive calls\r\n          0  db block gets\r\n       3861  consistent gets\r\n       <span style=\"color: red; font-weight: bold;\">3858  physical reads<\/span>\r\n          0  redo size\r\n        542  bytes sent via SQL*Net to client\r\n        551  bytes received via SQL*Net from client\r\n          2  SQL*Net roundtrips to\/from client\r\n          0  sorts (memory)\r\n          0  sorts (disk)\r\n          1  rows processed\r\n\r\nSQL&gt;col value for 999\r\n     select value from v$mystat where statistic#=\r\n       (select statistic# from v$statname\r\n        where name='table scans (direct read)');\r\n  \r\nVALUE\r\n-----\r\n    <span style=\"color: red; font-weight: bold;\">3<\/span>\r\n\r\nSQL&gt;select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP \r\n     from gv$bt_scan_cache\r\n     order by 1; \r\n  \r\n   INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP\r\n---------- --------------- ------------ ---------------- ---------------\r\n         1              25            <span style=\"color: red; font-weight: bold;\">0<\/span>                0            1000\r\n         2              25            <span style=\"color: red; font-weight: bold;\">0<\/span>                0            1000\r\n         3              25            <span style=\"color: red; font-weight: bold;\">0<\/span>                0            1000<\/pre>\n<ul>\n<li>Query the big table in parallel to verify that direct path reads are not performed and that fragments of the whole of the table are distributed in big table cache across all three instances.<\/li>\n<\/ul>\n<pre>SQL&gt;select value from v$mystat where statistic#=\r\n       (select statistic# from v$statname\r\n        where name='table scans (direct read)');\r\nVALUE\r\n-----\r\n    <span style=\"color: red; font-weight: bold;\">3<\/span>\r\n\r\nSQL&gt; set autot traceonly statistics\r\n     set timing on;\r\n     select <span style=\"color: red; font-weight: bold;\">\/*+ full(big) parallel(big) *\/<\/span> count(*) from hr.big_table1 big;\r\n     set timing off;\r\n     set autot off\r\n\r\nElapsed: <span style=\"color: red; font-weight: bold;\">00:00:01.80<\/span>\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n        232  recursive calls\r\n          0  db block gets\r\n       4708  consistent gets\r\n       <span style=\"color: red; font-weight: bold;\">3869  physical reads<\/span>\r\n          0  redo size\r\n        542  bytes sent via SQL*Net to client\r\n        551  bytes received via SQL*Net from client\r\n          2  SQL*Net roundtrips to\/from client\r\n         10  sorts (memory)\r\n          0  sorts (disk)\r\n          1  rows processed\r\n\r\nSQL&gt; select value from v$mystat where statistic#=\r\n       (select statistic# from v$statname\r\n        where name='table scans (direct read)');\r\n  \r\nVALUE\r\n-----\r\n    <span style=\"color: red; font-weight: bold;\">3<\/span>  \r\n\r\nSQL&gt;select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP \r\n     from gv$bt_scan_cache\r\n     order by 1;  \r\n\r\n    INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP\r\n---------- --------------- ------------ ---------------- ---------------\r\n         1              25            <span style=\"color: red; font-weight: bold;\">1             1479<\/span>            1000\r\n         2              25            <span style=\"color: red; font-weight: bold;\">1             1170<\/span>            1000\r\n         3              25            <span style=\"color: red; font-weight: bold;\">1             1209<\/span>            1000\r\n\r\nSQL&gt; Select inst_id, object_name, size_in_blks, temperature,\r\n            policy, cached_in_mem \r\n     from  gv$bt_scan_obj_temps  b, dba_objects o\r\n     where b.dataobj#  = o.data_object_id\r\n     order by 1;\r\n\r\n   INST_ID OBJECT_NAME     SIZE_IN_BLKS TEMPERATURE POLICY     CACHED_IN_MEM\r\n---------- --------------- ------------ ----------- ---------- -------------\r\n         1 BIG_TABLE1              <span style=\"color: red; font-weight: bold;\">1479<\/span>        1000 <span style=\"color: red; font-weight: bold;\">MEM_ONLY            1479<\/span>\r\n         2 BIG_TABLE1              <span style=\"color: red; font-weight: bold;\">1170<\/span>        1000 <span style=\"color: red; font-weight: bold;\">MEM_ONLY            1170<\/span>\r\n         3 BIG_TABLE1              <span style=\"color: red; font-weight: bold;\">1209<\/span>        1000 <span style=\"color: red; font-weight: bold;\">MEM_ONLY            1209<\/span><\/pre>\n<p>If we re-execute the query you can see that the time taken has reduced considerably, from 00:00:01.80 to 00:00:00.08, and that physical reads have also reduced from 3869 to 0 as all the data is read from big table cache.<\/p>\n<pre>SQL&gt; set timing on;\r\n     select \/*+ full(big) parallel(big) *\/ count(*) from hr.big_table1 big;\r\n     set timing off;\r\n     set autot off\r\n \r\nElapsed: <span style=\"color: red; font-weight: bold;\">00:00:00.08<\/span>\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n         18  recursive calls\r\n          0  db block gets\r\n       4556  consistent gets\r\n          <span style=\"color: red; font-weight: bold;\">0  physical reads<\/span><\/pre>\n<ul>\n<li>Set\u00a0<code>DB_BIG_TABLE_CACHE_PERCENT_TARGET<\/code>\u00a0to 0 on instance 3 and verify that the part of the table which was cached in big table cache of that instance is no longer cached.<\/li>\n<\/ul>\n<pre>SQL&gt;alter system set db_big_table_cache_percent_target=0 sid = 'orcl3'; \r\n    select inst_id, name, value \r\n    from gv$parameter \r\n    where name in ('parallel_degree_policy','db_big_table_cache_percent_target') \r\n    order by 2,1;\r\n\r\n   INST_ID NAME                                     VALUE\r\n---------- ---------------------------------------- --------------------\r\n         1 db_big_table_cache_percent_target        25\r\n         2 db_big_table_cache_percent_target        25\r\n         <span style=\"color: red; font-weight: bold;\">3 db_big_table_cache_percent_target        0<\/span>\r\n         1 parallel_degree_policy                   AUTO\r\n         2 parallel_degree_policy                   AUTO\r\n         3 parallel_degree_policy                   AUTO\r\n\r\nSQL&gt; Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem \r\n     from gv$bt_scan_obj_temps  b, dba_objects o \r\n     where b.dataobj#  = o.data_object_id order by 1;  \r\n\r\n   INST_ID OBJECT_NAME       SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM\r\n---------- ------------ ----------------- ----------- --------------- -------------\r\n         1 BIG_TABLE1                1479        2000 MEM_ONLY                1479\r\n         2 BIG_TABLE1                1170        2000 MEM_ONLY                1170\r\n         <span style=\"color: red; font-weight: bold;\">3 BIG_TABLE1<\/span>                1209        2000 <span style=\"color: red; font-weight: bold;\">DISK<\/span>                    1209<\/pre>\n<ul>\n<li>Set\u00a0<code>DB_BIG_TABLE_CACHE_PERCENT_TARGET<\/code>\u00a0back to 25 on instance 3 and verify that the table data which was allocated to big table cache on instance 3, has again been read from disk into the big table cache.<\/li>\n<\/ul>\n<pre>SQL&gt; alter system set db_big_table_cache_percent_target=25 sid = 'orcl3'; \r\n     select inst_id, name, value from gv$parameter \r\n     where name in ('parallel_degree_policy','db_big_table_cache_percent_target') \r\n     order by 2,1;\r\n\r\n   INST_ID NAME                                     VALUE\r\n---------- ---------------------------------------- --------------------\r\n         1 db_big_table_cache_percent_target        25\r\n         2 db_big_table_cache_percent_target        25\r\n         <span style=\"color: red; font-weight: bold;\">3 db_big_table_cache_percent_target        25<\/span>\r\n         1 parallel_degree_policy                   AUTO\r\n         2 parallel_degree_policy                   AUTO\r\n         3 parallel_degree_policy                   AUTO\r\n\r\nSQL&gt; Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem \r\n      from gv$bt_scan_obj_temps  b, dba_objects o \r\n      where b.dataobj#  = o.data_object_id order by 1;  \r\n\r\n   INST_ID OBJECT_NAME       SIZE_IN_BLKS TEMPERATURE POLICY          CACHED_IN_MEM\r\n---------- ------------ ----------------- ----------- --------------- -------------\r\n         1 BIG_TABLE1                1479        2000 MEM_ONLY                1479\r\n         2 BIG_TABLE1                1170        2000 MEM_ONLY                1170\r\n         <span style=\"color: red; font-weight: bold;\">3 BIG_TABLE1                1209        2000 MEM_ONLY                1209<\/span><\/pre>\n<ul>\n<li>Drop table <code>HR.BIG_TABLE1<\/code> so that its blocks are no longer cached in ABTC. Verify that the table <code>HR.BIG_TABLE_PART<\/code> has 3 partitions, each of which qualifies as a big segment.<\/li>\n<\/ul>\n<pre>SQL&gt;drop table hr.big_table1 purge;\r\n    Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem \r\nfrom gv$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id order by 1;  \r\n\r\n<span style=\"color: red; font-weight: bold;\">no rows selected<\/span>\r\n\r\nSQL&gt; Select table_name, partition_name, blocks from dba_tab_partitions\r\n     where table_name = 'BIG_TABLE_PART';\r\n\r\nTABLE_NAME      PARTITION_NAME      BLOCKS\r\n--------------- --------------- ----------\r\nBIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P3                    1370<\/span>\r\nBIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P2                     502<\/span>\r\nBIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P1                    2085<\/span><\/pre>\n<ul>\n<li>Scan partitions p1 and p2 of the partitioned table in parallel. Data from both the partitions p1 and p2 is distributed across all three instances.<\/li>\n<\/ul>\n<pre>sql&gt; select <span style=\"color: red; font-weight: bold;\">\/*+ parallel *\/<\/span> count(*) from hr.big_table_part partition <span style=\"color: red; font-weight: bold;\">(p1);<\/span>\r\n\r\nSQL&gt; Select inst_id, object_name Table_name , subobject_name Partition_name,\r\n            size_in_blks, temperature, policy, cached_in_mem \r\n     from gv$bt_scan_obj_temps  b, dba_objects o where b.dataobj#  = o.data_object_id order by 2, 3, 1;      \r\n\r\n   INST_ID TABLE_NAME      PARTITION_NAME       SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM\r\n---------- --------------- --------------- ----------------- ----------- --------------- -------------\r\n         <span style=\"color: red; font-weight: bold;\">1<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P1<\/span>                            635        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">635<\/span>\r\n         <span style=\"color: red; font-weight: bold;\">2<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P1<\/span>                            663        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">663<\/span>\r\n         <span style=\"color: red; font-weight: bold;\">3<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P1<\/span>                            741        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">741<\/span>\r\n \r\nSQL&gt; select <span style=\"color: red; font-weight: bold;\">\/*+ parallel *\/<\/span> count(*) from hr.big_table_part partition <span style=\"color: red; font-weight: bold;\">(p2)<\/span>;\r\n\r\nSQL&gt; Select inst_id, object_name Table_name , subobject_name Partition_name, \r\n            size_in_blks, temperature, policy, cached_in_mem \r\n         from gv$bt_scan_obj_temps  b, dba_objects o \r\n         where b.dataobj#  = o.data_object_id order by 2, 3, 1; \r\n\r\n   INST_ID TABLE_NAME      PARTITION_NAME       SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM\r\n---------- --------------- --------------- ----------------- ----------- --------------- -------------\r\n         <span style=\"color: red; font-weight: bold;\">1<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P1<\/span>                            635        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">635<\/span>\r\n         <span style=\"color: red; font-weight: bold;\">2<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P1<\/span>                            663        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">663<\/span>\r\n         <span style=\"color: red; font-weight: bold;\">3<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P1<\/span>                            741        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">741<\/span>\r\n         <span style=\"color: red; font-weight: bold;\">1<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P2<\/span>                            156        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">156<\/span>\r\n         <span style=\"color: red; font-weight: bold;\">2<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P2<\/span>                            172        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">172<\/span>\r\n         <span style=\"color: red; font-weight: bold;\">3<\/span> BIG_TABLE_PART  <span style=\"color: red; font-weight: bold;\">P2<\/span>                            156        1000 MEM_ONLY                  <span style=\"color: red; font-weight: bold;\">156<\/span><\/pre>\n<ul>\n<li>To disable caching the data from a partition in all the instances except one, set parameter <code>PARALLEL_LOCAL_FORCE = TRUE<\/code> on instance 1.<\/li>\n<\/ul>\n<pre>ORCL1&gt;show parameter parallel_force_local\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\nparallel_force_local                 boolean     FALSE\r\n\r\nORCL1&gt;ALter system set parallel_force_local=true;\r\n      show parameter parallel_force_local\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\nparallel_force_local                 boolean     <span style=\"color: red; font-weight: bold;\">TRUE<\/span>\r\n\r\nORCL1&gt; sho parameter instance_name\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\ninstance_name                        string      <span style=\"color: red; font-weight: bold;\">orcl1<\/span><\/pre>\n<ul>\n<li>Issue parallel query on partition p3 from instance orcl1 and verify that blocks of partition p1 are cached in ABTC of instance orcl1 only.<\/li>\n<\/ul>\n<pre><span style=\"color: red; font-weight: bold;\">ORCL1<\/span>&gt; select <span style=\"color: red; font-weight: bold;\">\/*+ parallel *\/<\/span> count(*) from hr.big_table_part partition <span style=\"color: red; font-weight: bold;\">(p3);<\/span>\r\n\r\nORCL1&gt;Select inst_id, object_name Table_name , subobject_name Partition_name, \r\n       size_in_blks, temperature, policy, cached_in_mem \r\n      from gv$bt_scan_obj_temps  b, dba_objects o \r\n      where b.dataobj#  = o.data_object_id order by 2, 3, 1; \r\n\r\n   INST_ID TABLE_NAME      PARTITION_NAME       SIZE_IN_BLKS TEMPERATURE POLICY  CACHED_IN_MEM\r\n---------- --------------- --------------- ----------------- ----------- --------------- -------------\r\n         1 BIG_TABLE_PART  P1                            635        1000 MEM_ONLY                  635\r\n         2 BIG_TABLE_PART  P1                            663        1000 MEM_ONLY                  663\r\n         3 BIG_TABLE_PART  P1                            741        1000 MEM_ONLY                  741\r\n         1 BIG_TABLE_PART  P2                            156        1000 MEM_ONLY                  156\r\n         2 BIG_TABLE_PART  P2                            172        1000 MEM_ONLY                  172\r\n         3 BIG_TABLE_PART  P2                            156        1000 MEM_ONLY                  156\r\n         <span style=\"color: red; font-weight: bold;\">1 BIG_TABLE_PART  P3                           1336        1000 MEM_ONLY                 1336<\/span><\/pre>\n<h2>Summary:<\/h2>\n<ul>\n<li>Automatic Big Table Caching can be used for serial as well as parallel full scans of large objects in Single Instance environments, it is supported only for parallel (not serial) full scans of large objects in Oracle Real Application Clusters (Oracle RAC) configurations.<\/li>\n<li>When parameter PARALLEL_LOCAL_FORCE = FALSE(default), querying a big object in parallel causes its fragments to be cached across the big table caches of all the instances.<\/li>\n<li>When parameter PARALLEL_LOCAL_FORCE = TRUE on an instance, querying a big object from that instance causes that object to be cached in the big table cache of that instance only. This can be employed to implement application partitioning in RAC.<\/li>\n<li>Partitioning can be combined with \u201cABTC\u201d to considerably reduce the amount of data to be cached.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In an earlier article, I discussed Automatic big table cache (ABTC) which is a new feature introduced in Oracle Database 12.1.0.2c. Automatic big table cache is an optional section of the buffer cache reserved for storing large objects. It tracks only big tables and automatically caches scanned big objects in big table cache thereby avoiding direct path reads. It caches&hellip;<\/p>\n","protected":false},"author":316217,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73118","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316217"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73118"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73118\/revisions"}],"predecessor-version":[{"id":91615,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73118\/revisions\/91615"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73118"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}