ABTC introduced with Oracle12c (12.1.0.2) specifically for the
parallel or serial scanning segments in main memory. The idea is simple:
certain dedicated part of buffer cache is reserved for storing large objects or
parts of it (like partitions / sub-partitions), so that certain queries can
benefit from the storage in the cache, which otherwise would not be in the
buffer cache.
In order to decide which objects are stored in big table cache, ABTC
uses multiple criteria, not only the size of the object and size of the cache.
Unlike the standard buffer cache behavior based on the block orientation level
LRU algorithm, big table caches make use of the “temperature” of the objects –
not just blocks.
It is important to note the ABTC in RAC environment is supported only
for the parallel queries, but in single instance database it is supported for
serial queries too. The parameter that supports ABTC is “db_big_table_cache_percent_target”
which is initialized to the value of zero by default.
rajesh@ORA12C> select count(*) from sales;
COUNT(*)
----------
500000
1 row selected.
rajesh@ORA12C> select count(*) from customers;
COUNT(*)
----------
720368
1 row selected.
rajesh@ORA12C> select count(*) from big_table;
COUNT(*)
----------
1000000
1 row selected.
rajesh@ORA12C> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> show parameter db_big_table_cache
NAME
TYPE VALUE
--------------------------------------------- ----------- -------------------------
db_big_table_cache_percent_target string 0
rajesh@ORA12C> show parameter parallel_degree_policy
NAME
TYPE VALUE
--------------------------------------------- -----------
-------------------------
parallel_degree_policy string MANUAL
rajesh@ORA12C> show parameter memory_max_target
NAME
TYPE VALUE
--------------------------------------------- -----------
-------------------------
memory_max_target big integer 612M
rajesh@ORA12C> show parameter memory_target
NAME
TYPE VALUE
--------------------------------------------- -----------
-------------------------
memory_target big integer
612M
rajesh@ORA12C> set timing off
rajesh@ORA12C> set feedback off
rajesh@ORA12C> select count(distinct cust_id) from sales;
COUNT(DISTINCTCUST_ID)
----------------------
6811
rajesh@ORA12C> select count(distinct cust_id) from sales;
COUNT(DISTINCTCUST_ID)
----------------------
6811
rajesh@ORA12C> set feedback on
rajesh@ORA12C>
Initially
this feature is not enabled, we now use a simple query against SALES table that
is not stored in buffer cache. It will be full table scan followed by some
amount of physical reads.
rajesh@ORA12C> select count(distinct cust_id) from sales;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4254621453
----------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 13 | 682
(3)|
| 1 | SORT AGGREGATE |
| 1 | 13 | |
| 2 | VIEW | VW_DAG_0 | 6811 | 88543 | 682
(3)|
| 3 | HASH GROUP BY |
| 6811 | 34055 | 682
(3)|
| 4 | TABLE ACCESS FULL|
SALES | 500K|
2441K| 670 (1)|
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2412 consistent gets
2409 physical reads
0 redo size
557 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
rajesh@ORA12C>
For safety, we check the behavior of V$BH (v$bh in this case list
those blocks that are present in the buffer cache)
rajesh@ORA12C> select table_name,blocks
2 from user_tables
3 where table_name='SALES';
TABLE_NAME BLOCKS
---------- ----------
SALES 2457
1 row selected.
rajesh@ORA12C> select t1.object_name,count(*)
2 from user_objects t1,
3 v$bh t2
4 where object_name ='SALES'
5 and t1.data_object_id = t2.objd
6 group by t1.object_name ;
OBJECT_NAME
COUNT(*)
------------------------------ ----------
SALES 1
1 row selected.
rajesh@ORA12C>
The
table has close to 2457 block’s allocated by just one block is present in the
buffer cache. Another way of monitoring the info would be via session
statistics.
rajesh@ORA12C> select s1.name,s2.value
2 from v$statname s1,
3 v$mystat s2
4 where s1.statistic# = s2.statistic#
5 and s2.sid = userenv('sid')
6 and ( s1.name like '%warehousing%' or
7 s1.name like '%table%' )
8 and s2.value <> 0
9 order by 2 desc ;
NAME
VALUE
-------------------------------------------------- ----------
table scan rows gotten 1709539
table scan disk non-IMC rows gotten 1548488
table scan blocks gotten 11497
table fetch by rowid 3227
table scans (short tables) 148
table fetch continued row 38
table scans (long tables) 3
table scans (direct read) 3
8 rows selected.
As
we can see , the statistics “table
scan(direct read)” and “table
scan(long tables)” the value 3 , so there are three table scans on “long
table” via “direct path reads” since this session is established ( we full
scanned the SALES table thrice so far in
this session)
To
monitor the new big table caching, two more v$ views are available.
V$BT_SCAN_CACHE
– display the status of different areas of big table caches.
V$BT_SCAN_OBJ_TEMPS
– lists the active objects in big table caches.
rajesh@ORA12C> select
bt_cache_alloc,bt_cache_target,object_count,
2 memory_buf_alloc,min_cached_temp
3 from v$bt_scan_cache ;
BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC
MIN_CACHED_TEMP
-------------- --------------- ------------ ----------------
---------------
0 0 0 0 1000
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> select t2.object_name,t1.cached_in_mem,
2 t1.size_in_blks,t1.policy,t1.temperature
3 from v$bt_scan_obj_temps t1,
4 user_objects t2
5 where t1.dataobj# = t2.data_object_id ;
no rows selected
rajesh@ORA12C>
The
results of both the queries shows that no big table caching is used at this
moment. The column MIN_CACHED_TEMP indicate that an internal heap is used to
decide on the objects in the big table cache. The value of 1000 indicates that
the minimum temperature that an object must have in-order to be stored in Big
Table cache.
Next
we will set this parameter “db_big_table_cache_percent_target” to 20, that
means 20% of buffer cache is used for Big Table cache, the remaining 80% is
available for other workloads in the database.
rajesh@ORA12C> alter system set
db_big_table_cache_percent_target=20;
System altered.
rajesh@ORA12C> select count(distinct cust_id) from sales;
COUNT(DISTINCTCUST_ID)
----------------------
6811
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> /* continue below sql-workloads from different
session
rajesh@ORA12C> set feedback off
rajesh@ORA12C> select sum(object_id) from customers;
rajesh@ORA12C> select count(distinct cust_id) from sales;
rajesh@ORA12C> select sum(object_id) from customers;
rajesh@ORA12C> select count(distinct cust_id) from sales;
rajesh@ORA12C> select avg(object_id) from big_table;
rajesh@ORA12C> select avg(object_id) from big_table;
rajesh@ORA12C> set feedback on
rajesh@ORA12C> */
rajesh@ORA12C>
rajesh@ORA12C> select
bt_cache_alloc,bt_cache_target,object_count,
2 memory_buf_alloc,min_cached_temp
3 from v$bt_scan_cache;
BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC
MIN_CACHED_TEMP
-------------- --------------- ------------ ----------------
---------------
.199913157 20 3 4604 1000
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> select t2.object_name,t1.cached_in_mem,
2 t1.size_in_blks,t1.policy,t1.temperature
3 from v$bt_scan_obj_temps t1,
4 user_objects t2
5 where t1.dataobj# = t2.data_object_id ;
OBJECT_NAME
CACHED_IN_MEM SIZE_IN_BLKS POLICY
TEMPERATURE
------------------------------ ------------- ------------
---------- -----------
SALES 2457 2457 MEM_ONLY 3000
CUSTOMERS 2147 12360 MEM_PART 2000
BIG_TABLE 0 16016 DISK 2000
3 rows selected.
The
table SALES is almost entirely in-memory (see policy = MEM_ONLY) and the part
of the table CUSTOMERS is available in memory and rest of it at disk (since we
don’t have enough room to cache the entire object to memory) and the BIG_TABLE
is completely available at desk.
The
decision on caching of table is controlled by the column value TEMPRATURE. The
minimum temperature required is 1000 as we saw in the beginning. Every visit to
an object, the database increases the temperature of the corresponding object,
so that only “hot” objects remain in the cache.
In
the next step we increase the Big Table cache using the below statement and
look at its altered results.
rajesh@ORA12C> alter system set
db_big_table_cache_percent_target=75;
System altered.
rajesh@ORA12C> select
bt_cache_alloc,bt_cache_target,object_count,
2 memory_buf_alloc,min_cached_temp
3 from v$bt_scan_cache;
BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC
MIN_CACHED_TEMP
-------------- --------------- ------------ ----------------
---------------
.579331307 75 3 4604 1000
1 row selected.
rajesh@ORA12C> select t2.object_name,t1.cached_in_mem,
2 t1.size_in_blks,t1.policy,t1.temperature
3 from v$bt_scan_obj_temps t1,
4 user_objects t2
5 where t1.dataobj# = t2.data_object_id ;
OBJECT_NAME
CACHED_IN_MEM SIZE_IN_BLKS POLICY
TEMPERATURE
------------------------------ ------------- ------------
---------- -----------
SALES 2457 2457 MEM_ONLY 3000
CUSTOMERS 12360 12360 MEM_ONLY 2000
BIG_TABLE 2455 16016 MEM_PART 2000
3 rows selected.
This
time we had enough room to hold the entire CUSTOMERS table in cache in addition
to the SALES table.
Now
running the similar query will produce this statistics.
rajesh@ORA12C> set autotrace traceonly explain statistics
rajesh@ORA12C> select count(distinct cust_id) from sales;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4254621453
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 13 | 682
(3)| 00:00:01 |
| 1 | SORT AGGREGATE | |
1 | 13 | | |
| 2 | VIEW | VW_DAG_0 | 6811 | 88543 | 682
(3)| 00:00:01 |
| 3 | HASH GROUP BY |
| 6811 | 34055 | 682
(3)| 00:00:01 |
| 4 | TABLE ACCESS
FULL| SALES | 500K|
2441K| 670 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2415
consistent gets
0 physical reads
0 redo size
557 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
rajesh@ORA12C> set autotrace off
rajesh@ORA12C>
The
absence of physical reads indicate that this segments got scanned through Big
Table caching rather than “direct path reads” from disk into PGA.
But
taking a look at the session statistics, table scans (long tables) and (direct
read) resulted in no changes – that confirms no physical IO took place as part
of this query execution. (the segment got scanned through Big table cache)
rajesh@ORA12C> select s1.name,s2.value
2 from v$statname s1,
3 v$mystat s2
4 where s1.statistic# = s2.statistic#
5 and s2.sid = userenv('sid')
6 and ( s1.name like '%warehousing%' or
7 s1.name like '%table%' )
8 and s2.value <> 0
9 order by 2 desc ;
NAME
VALUE
-------------------------------------------------- ----------
table scan rows gotten 3077776
table scan disk non-IMC rows gotten 2756901
table scan blocks gotten 21975
table fetch by rowid 5267
data warehousing scanned blocks 4818
data warehousing scanned blocks - memory 4818
table scans (short tables) 206
table fetch continued row 44
table scans (long tables) 3
table scans (direct read) 3
data warehousing scanned objects 2
11 rows selected.
No comments:
Post a Comment