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 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.
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.
Current scenario:
- Name of cluster: Cluster01
- Number of nodes : 3
- Name of RAC database (Non-CDB): orcl
- Number of RAC database instances : 3
Overview:
This article will cover the steps needed to test ABTC for parallel scans.
- Check the size of the default buffer cache and number of buffers in it for all the instances.
- Ensure that full database caching is disabled on all the instances.
- Check the value of the parameter
_SMALL_TABLE_THRESHOLD. - Verify that Hr.big_table1 qualifies as a big table and can leverage ABTC.
- Set
PARALLEL_DEGREE_POLICY = AUTOon all the instances - Set
DB_BIG_TABLE_CACHE_PERCENT_TARGETto 0 (default) on all the instances so that ABTC is disabled on all the instances. - Verify that serial scan of
HR.BIG_TABLE1results in direct path reads. - Configure Big table Cache to 25% on all the instances.
- 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.
- Query the big table in parallel and verify that its fragments get distributed in big table cache across instances.
- Set
DB_BIG_TABLE_CACHE_PERCENT_TARGETto 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. - Set
DB_BIG_TABLE_CACHE_PERCENT_TARGETback 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. - Drop table
HR.BIG_TABLE1and verify that another big tableHR.BIG_TABLE_PARThas 3 partitions, each of which qualify as a big segment. - Scan partitions p1 and p2 of the table
HR.BIG_TABLE1– Data from both the partitions p1 and p2 is distributed across instances. - To disable caching the data from a partition in all instance except one, set parameter
PARALLEL_LOCAL_FORCE = TRUEon instance 1. - Issue parallel query on partition p3 from instance orcl1 and verify that blocks of partition p1 are cached in ABTC of instance orcl1 only.
Demonstration:
- Check the size of the default buffer cache and number of buffers in it for all the instances:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> SELECT inst_id, name,block_size,buffers FROM gv$buffer_pool order by 1; INST_ID NAME BLOCK_SIZE BUFFERS ---------- ---------------------------------------- ---------- ---------- 1 DEFAULT 8192 <span style="color: red; font-weight: bold;">18760</span> 2 DEFAULT 8192 <span style="color: red; font-weight: bold;">18760</span> 3 DEFAULT 8192 <span style="color: red; font-weight: bold;">18760</span> SQL> select inst_id, COMPONENT, CURRENT_SIZE/1024/1024 SIZE_MB from gv$sga_dynamic_components where component = 'DEFAULT buffer cache' order by 1; INST_ID COMPONENT SIZE_MB ---------- ------------------------------ ---------- 1 DEFAULT buffer cache <span style="color: red; font-weight: bold;">160</span> 2 DEFAULT buffer cache <span style="color: red; font-weight: bold;">160</span> 3 DEFAULT buffer cache <span style="color: red; font-weight: bold;">160</span> |
- Ensure that full database caching is disabled on all the instances.
|
1 2 3 4 5 6 7 8 |
SQL>select inst_id, force_full_db_caching from gv$database order by 1; INST_ID FORCE_FULL_DB_CACHING ---------- ------------------------------ 1 <span style="color: red; font-weight: bold;">NO</span> 2 <span style="color: red; font-weight: bold;">NO</span> 3 <span style="color: red; font-weight: bold;">NO</span> |
- Check the value of the parameter
_SMALL_TABLE_THRESHOLD, since an object occupying number blocks higher than this value is considered big.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> SELECT a.ksppinm "Parameter", a.ksppdesc "Description", c.ksppstvl "Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p WHERE a.indx = b.indx AND a.indx = c.indx AND p.name(+) = a.ksppinm AND UPPER(a.ksppinm) IN ( UPPER('_db_block_buffers'), UPPER('_small_table_threshold')); Parameter Description Value ------------------------------ ------------------------------ --------------- _db_block_buffers Number of database blocks cach 18760 ed in memory: hidden parameter _small_table_threshold lower threshold level of table <span style="color: red; font-weight: bold;">375</span> size for direct reads |
- Verify that
Hr.big_table1qualifies as a big table and can leverage ABTC.
|
1 2 3 4 5 |
SQL> Select blocks from dba_tables where table_name = 'BIG_TABLE1'; BLOCKS ---------- <span style="color: red; font-weight: bold;">3928</span> |
- Set
PARALLEL_DEGREE_POLICY = AUTOon all the instances - Set
DB_BIG_TABLE_CACHE_PERCENT_TARGETto 0 (default) on all the instances so that ABTC is disabled on all the instances.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL>Alter system set PARALLEL_DEGREE_POLICY = AUTO ; alter system set db_big_table_cache_percent_target=0 scope=both; select inst_id, name, value from gv$parameter where name in ('parallel_degree_policy','db_big_table_cache_percent_target') order by 2,1; INST_ID NAME VALUE ---------- ---------------------------------------- --------------- 1 db_big_table_cache_percent_target 0 2 db_big_table_cache_percent_target 0 3 db_big_table_cache_percent_target 0 1 parallel_degree_policy AUTO 2 parallel_degree_policy AUTO 3 parallel_degree_policy AUTO |
- Verify that serial scan of
HR.BIG_TABLE1results in direct path reads.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SQL> select value from v$mystat where statistic#= (select statistic# from v$statname where name='table scans (direct read)'); VALUE ----- <span style="color: red; font-weight: bold;">0</span> SQL>set autot traceonly statistics select count(*) from hr.big_table1; set autot off Statistics ---------------------------------------------------------- 91 recursive calls 0 db block gets 3937 consistent gets <span style="color: red; font-weight: bold;">3861 physical reads</span> 0 redo size 542 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select value from v$mystat where statistic#= (select statistic# from v$statname where name='table scans (direct read)'); VALUE ----- <span style="color: red; font-weight: bold;">1</span> |
If we re-execute the query, almost the same number of physical reads appearing again confirm that a direct read was performed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL>set autot traceonly statistics select count(*) from hr.big_table1; set autot off Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3861 consistent gets <span style="color: red; font-weight: bold;">3858</span> physical reads 0 redo size 542 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
- Configure Big Table Cache to 25% on all the instances.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL>alter system set db_big_table_cache_percent_target = 25; select inst_id, name, value from gv$parameter where name in ('parallel_degree_policy','db_big_table_cache_percent_target') order by 2,1; INST_ID NAME VALUE ---------- ---------------------------------------- -------------------- 1 db_big_table_cache_percent_target 25 2 db_big_table_cache_percent_target 25 3 db_big_table_cache_percent_target 25 1 parallel_degree_policy AUTO 2 parallel_degree_policy AUTO 3 parallel_degree_policy AUTO |
We can verify that 25% of the buffer cache has been ‘reserved’ for big table caching on all the instances and no objects are cached in ABTC presently.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from gv$bt_scan_cache order by 1; INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP ---------- --------------- ------------ ---------------- --------------- 1 <span style="color: red; font-weight: bold;">25</span> 0 0 1000 2 <span style="color: red; font-weight: bold;">25</span> 0 0 1000 3 <span style="color: red; font-weight: bold;">25</span> 0 0 1000 SQL> Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem from gv$bt_scan_obj_temps b, dba_objects o where b.dataobj# = o.data_object_id order by 1,2; <span style="color: red; font-weight: bold;">no rows selected</span> |
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SQL>select value from v$mystat where statistic#= (select statistic# from v$statname where name='table scans (direct read)'); VALUE ----- <span style="color: red; font-weight: bold;">2</span> SQL> set autot traceonly statistics set timing on; select count(*) from hr.big_table1; set timing off; set autot off Elapsed: 00:00:00.63 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3861 consistent gets <span style="color: red; font-weight: bold;">3858 physical reads</span> 0 redo size 542 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>col value for 999 select value from v$mystat where statistic#= (select statistic# from v$statname where name='table scans (direct read)'); VALUE ----- <span style="color: red; font-weight: bold;">3</span> SQL>select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from gv$bt_scan_cache order by 1; INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP ---------- --------------- ------------ ---------------- --------------- 1 25 <span style="color: red; font-weight: bold;">0</span> 0 1000 2 25 <span style="color: red; font-weight: bold;">0</span> 0 1000 3 25 <span style="color: red; font-weight: bold;">0</span> 0 1000 |
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
SQL>select value from v$mystat where statistic#= (select statistic# from v$statname where name='table scans (direct read)'); VALUE ----- <span style="color: red; font-weight: bold;">3</span> SQL> set autot traceonly statistics set timing on; select <span style="color: red; font-weight: bold;">/*+ full(big) parallel(big) */</span> count(*) from hr.big_table1 big; set timing off; set autot off Elapsed: <span style="color: red; font-weight: bold;">00:00:01.80</span> Statistics ---------------------------------------------------------- 232 recursive calls 0 db block gets 4708 consistent gets <span style="color: red; font-weight: bold;">3869 physical reads</span> 0 redo size 542 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select value from v$mystat where statistic#= (select statistic# from v$statname where name='table scans (direct read)'); VALUE ----- <span style="color: red; font-weight: bold;">3</span> SQL>select inst_id, BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from gv$bt_scan_cache order by 1; INST_ID BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP ---------- --------------- ------------ ---------------- --------------- 1 25 <span style="color: red; font-weight: bold;">1 1479</span> 1000 2 25 <span style="color: red; font-weight: bold;">1 1170</span> 1000 3 25 <span style="color: red; font-weight: bold;">1 1209</span> 1000 SQL> Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem from gv$bt_scan_obj_temps b, dba_objects o where b.dataobj# = o.data_object_id order by 1; INST_ID OBJECT_NAME SIZE_IN_BLKS TEMPERATURE POLICY CACHED_IN_MEM ---------- --------------- ------------ ----------- ---------- ------------- 1 BIG_TABLE1 <span style="color: red; font-weight: bold;">1479</span> 1000 <span style="color: red; font-weight: bold;">MEM_ONLY 1479</span> 2 BIG_TABLE1 <span style="color: red; font-weight: bold;">1170</span> 1000 <span style="color: red; font-weight: bold;">MEM_ONLY 1170</span> 3 BIG_TABLE1 <span style="color: red; font-weight: bold;">1209</span> 1000 <span style="color: red; font-weight: bold;">MEM_ONLY 1209</span> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> set timing on; select /*+ full(big) parallel(big) */ count(*) from hr.big_table1 big; set timing off; set autot off Elapsed: <span style="color: red; font-weight: bold;">00:00:00.08</span> Statistics ---------------------------------------------------------- 18 recursive calls 0 db block gets 4556 consistent gets <span style="color: red; font-weight: bold;">0 physical reads</span> |
- Set
DB_BIG_TABLE_CACHE_PERCENT_TARGETto 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL>alter system set db_big_table_cache_percent_target=0 sid = 'orcl3'; select inst_id, name, value from gv$parameter where name in ('parallel_degree_policy','db_big_table_cache_percent_target') order by 2,1; INST_ID NAME VALUE ---------- ---------------------------------------- -------------------- 1 db_big_table_cache_percent_target 25 2 db_big_table_cache_percent_target 25 <span style="color: red; font-weight: bold;">3 db_big_table_cache_percent_target 0</span> 1 parallel_degree_policy AUTO 2 parallel_degree_policy AUTO 3 parallel_degree_policy AUTO SQL> Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem from gv$bt_scan_obj_temps b, dba_objects o where b.dataobj# = o.data_object_id order by 1; INST_ID OBJECT_NAME SIZE_IN_BLKS TEMPERATURE POLICY CACHED_IN_MEM ---------- ------------ ----------------- ----------- --------------- ------------- 1 BIG_TABLE1 1479 2000 MEM_ONLY 1479 2 BIG_TABLE1 1170 2000 MEM_ONLY 1170 <span style="color: red; font-weight: bold;">3 BIG_TABLE1</span> 1209 2000 <span style="color: red; font-weight: bold;">DISK</span> 1209 |
- Set
DB_BIG_TABLE_CACHE_PERCENT_TARGETback 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> alter system set db_big_table_cache_percent_target=25 sid = 'orcl3'; select inst_id, name, value from gv$parameter where name in ('parallel_degree_policy','db_big_table_cache_percent_target') order by 2,1; INST_ID NAME VALUE ---------- ---------------------------------------- -------------------- 1 db_big_table_cache_percent_target 25 2 db_big_table_cache_percent_target 25 <span style="color: red; font-weight: bold;">3 db_big_table_cache_percent_target 25</span> 1 parallel_degree_policy AUTO 2 parallel_degree_policy AUTO 3 parallel_degree_policy AUTO SQL> Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem from gv$bt_scan_obj_temps b, dba_objects o where b.dataobj# = o.data_object_id order by 1; INST_ID OBJECT_NAME SIZE_IN_BLKS TEMPERATURE POLICY CACHED_IN_MEM ---------- ------------ ----------------- ----------- --------------- ------------- 1 BIG_TABLE1 1479 2000 MEM_ONLY 1479 2 BIG_TABLE1 1170 2000 MEM_ONLY 1170 <span style="color: red; font-weight: bold;">3 BIG_TABLE1 1209 2000 MEM_ONLY 1209</span> |
- Drop table
HR.BIG_TABLE1so that its blocks are no longer cached in ABTC. Verify that the tableHR.BIG_TABLE_PARThas 3 partitions, each of which qualifies as a big segment.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL>drop table hr.big_table1 purge; Select inst_id, object_name, size_in_blks, temperature, policy, cached_in_mem from gv$bt_scan_obj_temps b, dba_objects o where b.dataobj# = o.data_object_id order by 1; <span style="color: red; font-weight: bold;">no rows selected</span> SQL> Select table_name, partition_name, blocks from dba_tab_partitions where table_name = 'BIG_TABLE_PART'; TABLE_NAME PARTITION_NAME BLOCKS --------------- --------------- ---------- BIG_TABLE_PART <span style="color: red; font-weight: bold;">P3 1370</span> BIG_TABLE_PART <span style="color: red; font-weight: bold;">P2 502</span> BIG_TABLE_PART <span style="color: red; font-weight: bold;">P1 2085</span> |
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
sql> 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> SQL> Select inst_id, object_name Table_name , subobject_name Partition_name, size_in_blks, temperature, policy, cached_in_mem from gv$bt_scan_obj_temps b, dba_objects o where b.dataobj# = o.data_object_id order by 2, 3, 1; INST_ID TABLE_NAME PARTITION_NAME SIZE_IN_BLKS TEMPERATURE POLICY CACHED_IN_MEM ---------- --------------- --------------- ----------------- ----------- --------------- ------------- <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> <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> <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> SQL> 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>; SQL> Select inst_id, object_name Table_name , subobject_name Partition_name, size_in_blks, temperature, policy, cached_in_mem from gv$bt_scan_obj_temps b, dba_objects o where b.dataobj# = o.data_object_id order by 2, 3, 1; INST_ID TABLE_NAME PARTITION_NAME SIZE_IN_BLKS TEMPERATURE POLICY CACHED_IN_MEM ---------- --------------- --------------- ----------------- ----------- --------------- ------------- <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> <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> <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> <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> <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> <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> |
- To disable caching the data from a partition in all the instances except one, set parameter
PARALLEL_LOCAL_FORCE = TRUEon instance 1.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ORCL1>show parameter parallel_force_local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_force_local boolean FALSE ORCL1>ALter system set parallel_force_local=true; show parameter parallel_force_local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_force_local boolean <span style="color: red; font-weight: bold;">TRUE</span> ORCL1> sho parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string <span style="color: red; font-weight: bold;">orcl1</span> |
- Issue parallel query on partition p3 from instance orcl1 and verify that blocks of partition p1 are cached in ABTC of instance orcl1 only.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span style="color: red; font-weight: bold;">ORCL1</span>> 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> ORCL1>Select inst_id, object_name Table_name , subobject_name Partition_name, size_in_blks, temperature, policy, cached_in_mem from gv$bt_scan_obj_temps b, dba_objects o where b.dataobj# = o.data_object_id order by 2, 3, 1; INST_ID TABLE_NAME PARTITION_NAME SIZE_IN_BLKS TEMPERATURE POLICY CACHED_IN_MEM ---------- --------------- --------------- ----------------- ----------- --------------- ------------- 1 BIG_TABLE_PART P1 635 1000 MEM_ONLY 635 2 BIG_TABLE_PART P1 663 1000 MEM_ONLY 663 3 BIG_TABLE_PART P1 741 1000 MEM_ONLY 741 1 BIG_TABLE_PART P2 156 1000 MEM_ONLY 156 2 BIG_TABLE_PART P2 172 1000 MEM_ONLY 172 3 BIG_TABLE_PART P2 156 1000 MEM_ONLY 156 <span style="color: red; font-weight: bold;">1 BIG_TABLE_PART P3 1336 1000 MEM_ONLY 1336</span> |
Summary:
- 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.
- 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.
- 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.
- Partitioning can be combined with “ABTC” to considerably reduce the amount of data to be cached.
Load comments