High Version Count due to USER_BIND_PEEK_MISMATCH After Flushing Shared Pool (Doc ID 2770037.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Enabling SPM or SQLPROFILE or flush shared pool on active concurrent high executing SQL, generates new child cursor for every execution of the SQL:
  1, bind sql with sql profile:
  select version_count,EXECUTIONS,SQL_PROFILE from v$sqlarea where sql_id='test'

  VERSION_COUNT EXECUTIONS SQL_PROFILE
  -------------------- -------------------- ----------------------------------------
  1 1633596 coe_test_2

  This profile has _optim_peek_user_binds set to false
  SQL>select * from table(dbms_xplan.display_cursor('TEST', 1, 'BASIC LAST +OUTLINE'));

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

  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  | Id | Operation | Name |
  -----------------------------------------------------------------
  | 0 | SELECT STATEMENT | |
  | 1 | SORT ORDER BY | |
  | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| Test |
  | 3 | INDEX RANGE SCAN | IDX_TEST |
  -----------------------------------------------------------------

  Outline Data
  -------------

  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------
  /*+
  BEGIN_OUTLINE_DATA
  IGNORE_OPTIM_EMBEDDED_HINTS
  OPTIMIZER_FEATURES_ENABLE('19.1.0')
  DB_VERSION('19.1.0')
  OPT_PARAM('_b_tree_bitmap_plans' 'false')
  OPT_PARAM('_index_join_enabled' 'false')
  OPT_PARAM('_optim_peek_user_binds' 'false')
  OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
  OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
  OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
  OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
  OPT_PARAM('_optimizer_use_feedback' 'false')
  OPT_PARAM('optimizer_index_cost_adj' 30)
  ALL_ROWS
  OUTLINE_LEAF(@"SEL$1")
  INDEX_RS_ASC(@"SEL$1" "Q"@"SEL$1" ("T_TV_MINI_VIP"."VIP_ID"))
  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "Q"@"SEL$1")
  END_OUTLINE_DATA
  */

  43 rows selected.

  2, flush shared pool:
  SQL>select version_count,EXECUTIONS,SQL_PROFILE from v$sqlarea where sql_id='TEST';

  VERSION_COUNT EXECUTIONS SQL_PROFILE
  -------------------- -------------------- ----------------------------------------
  250 130696 coe_TEST_2067382713

  SQL>select count(*) from v$session where username = 'SCOTT' and PROGRAM = 'JDBC Thin Client';

  COUNT(*)
  --------------------
  250
  And the version count seems equal to the number of concurrent session which is actively running this sql

  3, then recreated the sql profile without _optim_peek_user_binds
  SQL>select * from table(dbms_xplan.display_cursor('gxgqcus579rxc', 1, 'BASIC LAST +OUTLINE'));
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  | Id | Operation | Name |
  -----------------------------------------------------------------
  | 0 | SELECT STATEMENT | |
  | 1 | SORT ORDER BY | |
  | 2 | TABLE ACCESS BY INDEX ROWID BATCHED|TEST |
  | 3 | INDEX RANGE SCAN | IDX_TEST |
  -----------------------------------------------------------------

  Outline Data
  -------------

  PLAN_TABLE_OUTPUT
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  /*+
  BEGIN_OUTLINE_DATA
  IGNORE_OPTIM_EMBEDDED_HINTS
  OPTIMIZER_FEATURES_ENABLE('19.1.0')
  DB_VERSION('19.1.0')
  OPT_PARAM('_b_tree_bitmap_plans' 'false')
  OPT_PARAM('_index_join_enabled' 'false')
  OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
  OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
  OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
  OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')

  PLAN_TABLE_OUTPUT
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  OPT_PARAM('_optimizer_use_feedback' 'false')
  OPT_PARAM('optimizer_index_cost_adj' 30)
  ALL_ROWS
  OUTLINE_LEAF(@"SEL$1")
  INDEX_RS_ASC(@"SEL$1" "Q"@"SEL$1" ("T_TV_MINI_VIP"."VIP_ID"))
  BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "Q"@"SEL$1")
  END_OUTLINE_DATA
  */

  4, flush share pool again, the version count does not increase:
  SQL>select version_count,EXECUTIONS,SQL_PROFILE from v$sqlarea where sql_id='TEST';

  VERSION_COUNT EXECUTIONS SQL_PROFILE
  -------------------- -------------------- --------------------------------------------------
  1 5691401 coe_TEST_2

   

Cause

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