BT_CACHE_TARGET Is Re-adjusted To 0 When A DataPump Job is Started (Doc ID 2060627.1)

Last updated on OCTOBER 26, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

  BT_CACHE_TARGET is wiped out by a simple DataPump job as you can see in the below scenario:

- New 12.1.0.2 database, empty database, non-RAC

- Set db_big_table_cache_percent_target:

SQL> alter system set db_big_table_cache_percent_target='66' scope=spfile;

System altered.

SQL>  shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  2927336 bytes
Variable Size             306185496 bytes
Database Buffers          297795584 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.
SQL> show parameter big

NAME                                 TYPE        VALUE
------------------------------------ -----------------------------------------
db_big_table_cache_percent_target    string      66

SQL> show parameter wareh

NAME                                 TYPE        VALUE
------------------------------------ -----------------------------------------
_data_warehousing_scan_buffers       boolean     TRUE

SQL> show parameter parallel_degree_policy

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

SQL> select * from V$BT_SCAN_CACHE;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP  CON_ID
-------------- --------------- ------------ -------------------------------------------------------------------------
    .660157699              66            0 0            1000  0

- Create an empty schema:

create user tc identified by tc;
grant connect,resource,dba to tc

Note: No objects created in this schema.

- Start a simple DataPump export in serial mode to unload TC schema:

> expdp directory=dptest dumpfile=tcdmp schemas=tc

immediately after Master and Worker DataPump processes are started and in log file we see:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=dptest dumpfile=tcdmp schemas=tc
Estimate in progress using BLOCKS method..................

the cached parameter value in SGA gets re-adjusted to 0 (but parameter value itself is not modified).

SQL>  select * from V$BT_SCAN_CACHE;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP    CON_ID
-------------- --------------- ------------ ---------------------------------------------------------------------------
 .000087777               0            0                0 1000          0

Note: There is no other workload besides the DataPump job.

   While BT_CACHE_ALLOC is dynamic dependent on the workload, BT_CACHE_TARGET should not change unless customer manually change the parameter db_big_table_cache_percent_target.

Changes

As part of DataPump job, parallel_degree_policy is reset and it disabled ABTC on non-RAC.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms