select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Parameters
cursor_sharing string EXACT
_optim_peek_user_binds boolean FALSE
filesystemio_options string setall
optimizer_features_enable string 11.2.0.2
optimizer_index_caching integer 80
optimizer_index_cost_adj integer 20
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean FALSE
cursor_space_for_time boolean FALSE
open_cursors integer 1150
session_cached_cursors integer 50
shared_pool_size big integer 2G
SQL
select account_id,tracker_id from entity.BALANCE_SUMMARY where (AVAILABLE_CREDIT - available_debit) < 0 and TRACKER_ID = 'USD_BAL';
Child cursors
select sql_id,child_number CN,plan_hash_value phv,executions,elapsed_time/executions/1000000 "sec" ,IS_BIND_SENSITIVE SEN,is_bind_aware AWR,IS_SHAREABLE SHA,LAST_LOAD_TIME LOAD_T,last_active_time ACTIVE_T,substr(sql_fulltext,1,40) from v$sql where sql_id='&sql_id'
SQL_ID CN PHV EXECUTIONS sec S A S LOAD_T ACTIVE_T SUBSTR(SQL_FULLTEXT,1,40)
-------------- ---------- ---------- ---------- ---------- - - - ------------------------- ------------------------- --------------------------------------------------------------------------------
57bum9kjbnxdz 0 2727387875 3 563.398406 N N N 2012-07-29/14:45:12 29.Jul.12/14:54:43 select account_id,tracker_id from BALANC
57bum9kjbnxdz 1 2727387875 3 35.8117037 N N N 2012-07-29/14:54:43 29.Jul.12/14:55:19 select account_id,tracker_id from BALANC
57bum9kjbnxdz 2 2727387875 1 33.519571 N N N 2012-07-29/14:55:20 29.Jul.12/14:55:53 select account_id,tracker_id from BALANC
57bum9kjbnxdz 3 2727387875 1 36.006123 N N N 2012-07-15/11:39:48 15.Jul.12/11:40:23 select account_id,tracker_id from BALANC
57bum9kjbnxdz 4 2727387875 1 36.72405 N N N 2012-07-15/11:45:35 15.Jul.12/11:46:12 select account_id,tracker_id from BALANC
57bum9kjbnxdz 5 2727387875 1 32.484454 N N N 2012-07-15/11:49:38 15.Jul.12/11:50:11 select account_id,tracker_id from BALANC
57bum9kjbnxdz 6 2727387875 1 36.068105 N N N 2012-07-15/12:09:47 15.Jul.12/12:10:23 select account_id,tracker_id from BALANC
57bum9kjbnxdz 7 2727387875 1 33.906725 N N N 2012-07-15/12:15:35 15.Jul.12/12:16:08 select account_id,tracker_id from BALANC
57bum9kjbnxdz 8 2727387875 2021 296.927694 N N Y 2012-07-15/12:19:38 29.Jul.12/14:45:11 select account_id,tracker_id from BALANC
57bum9kjbnxdz 9 2727387875 1 35.093866 N N N 2012-07-29/14:58:18 29.Jul.12/14:58:53 select account_id,tracker_id from BALANC
57bum9kjbnxdz 10 2727387875 1 33.708294 N N N 2012-07-29/15:03:18 29.Jul.12/15:03:51 select account_id,tracker_id from BALANC
57bum9kjbnxdz 11 2727387875 209 625.510006 N N Y 2012-07-29/15:23:15 31.Jul.12/05:46:30 select account_id,tracker_id from BALANC
57bum9kjbnxdz 14 2727387875 1854 80.5482565 N N Y 2012-05-22/12:04:20 04.Jun.12/09:14:11 select account_id,tracker_id from BALANC
57bum9kjbnxdz 20 2727387875 1873 97.0443074 N N Y 2012-06-04/10:13:39 17.Jun.12/10:56:46 select account_id,tracker_id from BALANC
14 rows selected.
Shared_cursors
Enter value for sql_id: 57bum9kjbnxdz
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094F177B80
CHILD_NUMBER = 0
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094F5E4368
CHILD_NUMBER = 1
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094F66A4D0
CHILD_NUMBER = 2
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094F5FE5F0
CHILD_NUMBER = 3
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094F30B408
CHILD_NUMBER = 4
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094EFF2C40
CHILD_NUMBER = 5
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094E9AD978
CHILD_NUMBER = 6
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094D9C6F50
CHILD_NUMBER = 7
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094D9F47E0
CHILD_NUMBER = 8
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094DF8DE30
CHILD_NUMBER = 9
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094E82CB60
CHILD_NUMBER = 10
ROLL_INVALID_MISMATCH = Y
USE_FEEDBACK_STATS = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094DB66C48
CHILD_NUMBER = 11
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094DC3C398
CHILD_NUMBER = 14
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------
SQL_TEXT = select account_id,tracker_id from BALANCE_SUMMARY where (available_credit - available_debit) < 0 and tracker_id = 'USD_BAL'
SQL_ID = 57bum9kjbnxdz
ADDRESS = 000000094F177DD0
CHILD_ADDRESS = 000000094A0B2EF0
CHILD_NUMBER = 20
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------
I believe 'ROLL_INVALID_MISMATCH' is due to object statistics change and 'USE_FEEDBACK_STATS' is Cardinality Feedback. But i am totally unaware on why Optimizer is making different child cursors ?
Is there any way to know what is causing such large number of child cursors ? Why cursors are not being shared ?