Wrong Result Sometimes Occur When Set DB_BIG_TABLE_CACHE_PERCENT_TARGET Parameter (Doc ID 2471008.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• After setting the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter and updating the large table data in large quantity, if select the table, the data before the update is selected
• Following is an example testcase:
  1.Select's result before update.

  SQL> alter system flush shared_pool;
  SQL> alter system flush buffer_cache;
  SQL> alter system switch logfile;
  SQL> alter system checkpoint;

  SQL> select yyy,count(*) from sqltest
  2 group by yyy;

  YYY COUNT(*)
  ---------- ----------
  1 1500000

  2.execute update

  SQL> update sqltest set yyy = yyy+1;

  SQL> commit;

  SQL> update sqltest set yyy = yyy+1;

  3.Select's result after update.

  SQL> select yyy,count(*) from sqltest
  2 group by yyy;

  YYY COUNT(*)
  ---------- ----------
  2 309780 <- Data before update
  3 1190220

  SQL> select yyy,count(*) from sqltest
  2 group by yyy;

  YYY COUNT(*)
  ---------- ----------
  2 157752 <- To gradually decrease
  3 1342248

  SQL> select yyy,count(*) from sqltest
  2 group by yyy;

  YYY COUNT(*)
  ---------- ----------
  3 1500000 <- All updated data

   
• In this case, it occurs when the following parameters are set and started up:
  
  shared_pool_size = 1G
  large_pool_size = 64M
  streams_pool_size = 32M
  pga_aggregate_limit = 4G
  sga_target = 9G
  db_cache_size = 6016M
  db_big_table_cache_percent_target=90
    

Changes

 Set the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.