Maintaining Incremental statistics for partition maintenance operation

Starting with Oracle 12c, DBMS_STATS can create a synopsis for a non-partition table and this synopsis can be used to maintain incremental statistics as part of a partition exchange operation without having to explicitly gather the statistics on the partition after the exchange.

rajesh@ORA12C> create table t1

  2  partition by list(x)

  3  ( partition p1 values(1) ,

  4    partition p2 values(2) )

  5  as

  6  select owner,object_type,object_name,

  7             mod(rownum,2)+1 x

  8  from all_objects a ;

Table created.

rajesh@ORA12C> exec dbms_stats.set_table_prefs(user,'T1','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.

rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

rajesh@ORA12C> select num_rows,blocks,last_analyzed

  2  from user_tables

  3  where table_name ='T1';

  NUM_ROWS     BLOCKS LAST_ANALYZED

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

     90143        628 17-MAR-2016 05:56:49 pm

1 row selected.

rajesh@ORA12C> select partition_name,column_name,num_distinct,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T1'

  4  order by partition_name,column_name;

PARTITION_ COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED

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

P1         OBJECT_NAME            42676 17-MAR-2016 05:56:48 pm

P1         OBJECT_TYPE               38 17-MAR-2016 05:56:48 pm

P1         OWNER                     33 17-MAR-2016 05:56:48 pm

P1         X                          1 17-MAR-2016 05:56:48 pm

P2         OBJECT_NAME            42368 17-MAR-2016 05:56:49 pm

P2         OBJECT_TYPE               37 17-MAR-2016 05:56:49 pm

P2         OWNER                     33 17-MAR-2016 05:56:49 pm

P2         X                          1 17-MAR-2016 05:56:49 pm

8 rows selected.

rajesh@ORA12C>

So far created a partition table with Incremental preference set on the table and gathered statistics on it.

rajesh@ORA12C> alter table t1 add partition p3 values(3);

Table altered.

rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

rajesh@ORA12C>

Now, created an empty partition and gather stats on it to have synopsis created for it.

Looking into the statistics, show this.

rajesh@ORA12C> select num_rows,blocks,last_analyzed

  2  from user_tables

  3  where table_name ='T1';

  NUM_ROWS     BLOCKS LAST_ANALYZED

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

     90143        628 17-MAR-2016 05:58:20 pm

1 row selected.

rajesh@ORA12C>

rajesh@ORA12C> select partition_name,column_name,num_distinct,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T1'

  4  order by partition_name,column_name;

PARTITION_ COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED

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

P1         OBJECT_NAME            42676 17-MAR-2016 05:56:48 pm

P1         OBJECT_TYPE               38 17-MAR-2016 05:56:48 pm

P1         OWNER                     33 17-MAR-2016 05:56:48 pm

P1         X                          1 17-MAR-2016 05:56:48 pm

P2         OBJECT_NAME            42368 17-MAR-2016 05:56:49 pm

P2         OBJECT_TYPE               37 17-MAR-2016 05:56:49 pm

P2         OWNER                     33 17-MAR-2016 05:56:49 pm

P2         X                          1 17-MAR-2016 05:56:49 pm

P3         OBJECT_NAME                0 17-MAR-2016 05:58:19 pm

P3         OBJECT_TYPE                0 17-MAR-2016 05:58:19 pm

P3         OWNER                      0 17-MAR-2016 05:58:19 pm

P3         X                          0 17-MAR-2016 05:58:19 pm

12 rows selected.

rajesh@ORA12C>

Now, let us load a non-partitioned table with statistics and synopsis created on it.

rajesh@ORA12C> create table t2

  2  as

  3  select owner,object_type,object_name, 3 x

  4  from all_objects a ;

Table created.

rajesh@ORA12C> exec dbms_stats.set_table_prefs(user,'T2','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.

rajesh@ORA12C> exec dbms_stats.set_table_prefs(user,'T2','INCREMENTAL_LEVEL','TABLE');

PL/SQL procedure successfully completed.

rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T2');

PL/SQL procedure successfully completed.

rajesh@ORA12C>

Let us review the statistics on table T1 before the exchanging partition.

rajesh@ORA12C> select num_rows,blocks,last_analyzed

  2  from user_tables

  3  where table_name ='T1';

  NUM_ROWS     BLOCKS LAST_ANALYZED

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

     90143        628 17-MAR-2016 05:58:20 pm

1 row selected.

rajesh@ORA12C>

rajesh@ORA12C> select partition_name,column_name,num_distinct,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T1'

  4  order by partition_name,column_name;

PARTITION_ COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED

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

P1         OBJECT_NAME            42676 17-MAR-2016 05:56:48 pm

P1         OBJECT_TYPE               38 17-MAR-2016 05:56:48 pm

P1         OWNER                     33 17-MAR-2016 05:56:48 pm

P1         X                          1 17-MAR-2016 05:56:48 pm

P2         OBJECT_NAME            42368 17-MAR-2016 05:56:49 pm

P2         OBJECT_TYPE               37 17-MAR-2016 05:56:49 pm

P2         OWNER                     33 17-MAR-2016 05:56:49 pm

P2         X                          1 17-MAR-2016 05:56:49 pm

P3         OBJECT_NAME                0 17-MAR-2016 05:58:19 pm

P3         OBJECT_TYPE                0 17-MAR-2016 05:58:19 pm

P3         OWNER                      0 17-MAR-2016 05:58:19 pm

P3         X                          0 17-MAR-2016 05:58:19 pm

12 rows selected.

rajesh@ORA12C>

Exchanging the partition P3 with table T2.

rajesh@ORA12C>

rajesh@ORA12C> alter table t1

  2  exchange partition p3

  3  with table t2 ;

Table altered.

Looking into the statistics shows this.

rajesh@ORA12C> select num_rows,blocks,last_analyzed

  2  from user_tables

  3  where table_name ='T1';

  NUM_ROWS     BLOCKS LAST_ANALYZED

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

     90143        628 17-MAR-2016 05:58:20 pm

1 row selected.

rajesh@ORA12C> select partition_name,column_name,num_distinct,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T1'

  4  order by partition_name,column_name;

PARTITION_ COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED

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

P1         OBJECT_NAME            42676 17-MAR-2016 05:56:48 pm

P1         OBJECT_TYPE               38 17-MAR-2016 05:56:48 pm

P1         OWNER                     33 17-MAR-2016 05:56:48 pm

P1         X                          1 17-MAR-2016 05:56:48 pm

P2         OBJECT_NAME            42368 17-MAR-2016 05:56:49 pm

P2         OBJECT_TYPE               37 17-MAR-2016 05:56:49 pm

P2         OWNER                     33 17-MAR-2016 05:56:49 pm

P2         X                          1 17-MAR-2016 05:56:49 pm

P3         OBJECT_NAME            51936 17-MAR-2016 06:02:52 pm

P3         OBJECT_TYPE               40 17-MAR-2016 06:02:52 pm

P3         OWNER                     33 17-MAR-2016 06:02:52 pm

P3         X                          1 17-MAR-2016 06:02:52 pm

12 rows selected.

rajesh@ORA12C>

What happened is as part of exchange partition the database swapped the segment data and statistics of table T2 with partition P3 and updated the synopsis.  But no changes to global statistics.  So don’t be shocked!!!

The next time we do stats gathering on table T1 database will make use of the synopsis from partition P3 to update its global statistics.

rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

rajesh@ORA12C> select num_rows,blocks,last_analyzed

  2  from user_tables

  3  where table_name ='T1';

  NUM_ROWS     BLOCKS LAST_ANALYZED

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

    180255       1237 17-MAR-2016 06:13:47 pm

1 row selected.

rajesh@ORA12C> select partition_name,column_name,num_distinct,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T1'

  4  order by partition_name,column_name;

PARTITION_ COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED

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

P1         OBJECT_NAME            42676 17-MAR-2016 05:56:48 pm

P1         OBJECT_TYPE               38 17-MAR-2016 05:56:48 pm

P1         OWNER                     33 17-MAR-2016 05:56:48 pm

P1         X                          1 17-MAR-2016 05:56:48 pm

P2         OBJECT_NAME            42368 17-MAR-2016 05:56:49 pm

P2         OBJECT_TYPE               37 17-MAR-2016 05:56:49 pm

P2         OWNER                     33 17-MAR-2016 05:56:49 pm

P2         X                          1 17-MAR-2016 05:56:49 pm

P3         OBJECT_NAME            51936 17-MAR-2016 06:02:52 pm

P3         OBJECT_TYPE               40 17-MAR-2016 06:02:52 pm

P3         OWNER                     33 17-MAR-2016 06:02:52 pm

P3         X                          1 17-MAR-2016 06:02:52 pm

12 rows selected.

rajesh@ORA12C>

You can see how the global statistics got updated, but no changes to partition level statistics.

When repeated this test in 11g, we see this.

rajesh@ORA11G> alter table t1

  2  exchange partition p3

  3  with table t2 ;

Table altered.

rajesh@ORA11G> select num_rows,blocks,last_analyzed

  2  from user_tables

  3  where table_name ='T1';

  NUM_ROWS     BLOCKS LAST_ANALYZED

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

     84692        592 17-MAR-2016 06:17:40 pm

1 row selected.

rajesh@ORA11G>

rajesh@ORA11G> select partition_name,column_name,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T1'

  4  order by partition_name,column_name;

PARTITION_ COLUMN_NAME     LAST_ANALYZED

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

P1         OBJECT_NAME     17-MAR-2016 06:17:21 pm

P1         OBJECT_TYPE     17-MAR-2016 06:17:21 pm

P1         OWNER           17-MAR-2016 06:17:21 pm

P1         X               17-MAR-2016 06:17:21 pm

P2         OBJECT_NAME     17-MAR-2016 06:17:22 pm

P2         OBJECT_TYPE     17-MAR-2016 06:17:22 pm

P2         OWNER           17-MAR-2016 06:17:22 pm

P2         X               17-MAR-2016 06:17:22 pm

P3         OBJECT_NAME     17-MAR-2016 06:18:08 pm

P3         OBJECT_TYPE     17-MAR-2016 06:18:08 pm

P3         OWNER           17-MAR-2016 06:18:08 pm

P3         X               17-MAR-2016 06:18:08 pm

12 rows selected.

rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

rajesh@ORA11G> select num_rows,blocks,last_analyzed

  2  from user_tables

  3  where table_name ='T1';

  NUM_ROWS     BLOCKS LAST_ANALYZED

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

    169386       1165 17-MAR-2016 06:18:44 pm

1 row selected.

rajesh@ORA11G>

rajesh@ORA11G> select partition_name,column_name,num_distinct,last_analyzed

  2  from user_part_col_statistics

  3  where table_name ='T1'

  4  order by partition_name,column_name;

PARTITION_ COLUMN_NAME     NUM_DISTINCT LAST_ANALYZED

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

P1         OBJECT_NAME            40196 17-MAR-2016 06:17:21 pm

P1         OBJECT_TYPE               38 17-MAR-2016 06:17:21 pm

P1         OWNER                     31 17-MAR-2016 06:17:21 pm

P1         X                          1 17-MAR-2016 06:17:21 pm

P2         OBJECT_NAME            40436 17-MAR-2016 06:17:22 pm

P2         OBJECT_TYPE               37 17-MAR-2016 06:17:22 pm

P2         OWNER                     30 17-MAR-2016 06:17:22 pm

P2         X                          1 17-MAR-2016 06:17:22 pm

P3         OBJECT_NAME            49920 17-MAR-2016 06:18:44 pm

P3         OBJECT_TYPE               40 17-MAR-2016 06:18:44 pm

P3         OWNER                     31 17-MAR-2016 06:18:44 pm

P3         X                          1 17-MAR-2016 06:18:44 pm

12 rows selected.

rajesh@ORA11G>

So gathering the stats after the exchange partition operation in 11g, has resulted in changes to both global and partition level statistics. But in 12c no changes to partition level statistics, it make use of the synopsis to refresh the global statistics.

Yet another rule for un-indexed foreign keys

Un-indexed foreign keys are the number one reason for deadlocks in database.  Here is a scenario observed recently in a customer code base.

Here is the criteria for the test case.

• The child table should have more than one un-indexed foreign key to parent table.
• Primary key on child table should have *any* one of those un-indexed foreign key column on the leading edge of primary key definition. 
• A trigger has the *potential* to change a primary key column in parent.  (You can see from the trigger that this primary key will never *change*, but I don’t think optimizer is smart enough to deduce that, the moment the database see “:new.primary_key_col” as a potential assignment target, it assumes the worst)

Here is the test case to demonstrate that.

rajesh@ORA11G> set feedback off

rajesh@ORA11G> create table p(x1 int primary key,data varchar2(10));

rajesh@ORA11G> create table c(x int, x1 references p,x2 references p,

  2                     data varchar2(10),

  3                     constraint c_pk primary key(x2,x));

rajesh@ORA11G> insert into p values(1,'Data1');

rajesh@ORA11G> insert into p values(2,'Data2');

rajesh@ORA11G> commit;

rajesh@ORA11G> create or replace trigger p_trig

  2  before insert or update on P

  3  for each row

  4  declare

  5    k int;

  6  begin

  7    if 1=2 then

  8     :new.x1 := k ;

  9    end if;

 10  end;

 11  /

rajesh@ORA11G> set feedback on

rajesh@ORA11G>

rajesh@ORA11G> select * from p;

        X1 DATA

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

         1 Data1

         2 Data2

2 rows selected.

rajesh@ORA11G> select * from c;

no rows selected

When we tried to update the parent table (a non-key column) which in-turn executed the trigger and looking at the code base  “:new.primary_key_column = k” primary key as assignment target which is bounded inside a False Fake statement  1=2 , will never be True in this case, so the primary key doesn’t change.

But optimizer and Pl/SQL compiler is not able to deduce that and places a Full table lock on the child table.

rajesh@ORA11G> update p set data ='XX' where x1=1;

1 row updated.

rajesh@ORA11G> select t2.object_name, t2.object_type, t1.locked_mode

  2  from v$locked_object t1,

  3       user_objects t2

  4  where t1.object_id = t2.object_id ;

OBJECT_NAME                    OBJECT_TYPE         LOCKED_MODE

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

C                              TABLE                         3

P                              TABLE                         3

2 rows selected.

Remember lock_mode=3 is Row Exclusive Table lock (SX) is more restrictive than a share table lock. Only one transaction at a time can acquire an SX lock on a given table. An SX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table.

When a different transaction tries to place Full Table lock on child table will lead to dead lock.

rajesh@ORA11G> declare

  2     pragma autonomous_transaction;

  3  begin

  4     update p set data ='YY' where x1=2;

  5     rollback;

  6  end;

  7  /

declare

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at line 4

rajesh@ORA11G>

Is this a bug? To be honest, I don’t know. The resolution/workaround is of course, as per normal - add those indexes on foreign key columns.

Addendum:

o   This behavior is still reproducible on the latest version of oracle (12.1.0.2)

o   If primary key is not defined on the child table, then no deadlock.

o   If the primary key on the child table don’t include the foreign key column then no deadlock.  ( that is if primary key is defined on C(X) then no deadlock)

o   If the primary key on the child table don’t include the foreign key column in its leading edge then no deadlock ( that is if primary key is defined on C(X,X2) or C(X,X3) then no deadlock, instead if primary  key is defined as C(X2,X) or C(X3,X) then deadlock).

Automatic Big Table Caching (ABTC)

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.