1 2 Previous Next 16 Replies Latest reply: Aug 1, 2012 11:29 AM by OraDBA02 RSS

    why multiple child cursors 11.2.0.2 ?

    OraDBA02
      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 ?
        • 1. Re: why multiple child cursors 11.2.0.2 ?
          dba-india
          Set cursor_sharing to SIMILAR and see what u r getting.
          • 2. Re: why multiple child cursors 11.2.0.2 ?
            912595
            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 ?
            Your issue is more likely issue related to dbms_stats with AUTO_INVALIDATE option.
            ROLL_INVALID_MISMATCH - rolling invalidation window exceeded 
            This is caused by the rolling invalidation capability in DBMS_STATS. The child cannot be shared as it's invalidation window is exceeded.
            SO my guess is you might be gathering STATS for table quite to often. Please check whats the frequency of gathering stats.

            Check> Note:557661.1 Rolling Cursor Invalidations with DBMS_STATS in Oracle10g (Doc ID 557661.1)
            • 3. Re: why multiple child cursors 11.2.0.2 ?
              rakesh_kumar
              add "invalidations" in your v$sql and post the results

              post the results for the below code aslo
              select SQL_ID ,CHILD_NUMBER, AUTH_CHECK_MISMATCH
              from v$sql_shared_cursor
              where sql_id =<sql_id> 
              Regards,
              Rakesh
              • 4. Re: why multiple child cursors 11.2.0.2 ?
                OraDBA02
                 select SQL_ID ,CHILD_NUMBER, AUTH_CHECK_MISMATCH
                from v$sql_shared_cursor where sql_id ='57bum9kjbnxdz';
                
                SQL_ID         CHILD_NUMBER A
                -------------- ------------ -
                57bum9kjbnxdz             0 N
                57bum9kjbnxdz             1 N
                57bum9kjbnxdz             2 N
                57bum9kjbnxdz             8 N
                57bum9kjbnxdz             9 N
                57bum9kjbnxdz            10 N
                57bum9kjbnxdz            11 N
                57bum9kjbnxdz            14 N
                57bum9kjbnxdz            20 N
                
                9 rows selected.
                • 5. Re: why multiple child cursors 11.2.0.2 ?
                  OraDBA02
                  Adding INVALIDATIONS
                  BHAVIK_DBA: FPS2> select sql_id,child_number CN,plan_hash_value phv,invalidations from v$sql where sql_id='57bum9kjbnxdz';
                  
                  SQL_ID                 CN        PHV INVALIDATIONS
                  -------------- ---------- ---------- -------------
                  57bum9kjbnxdz           0 2727387875             0
                  57bum9kjbnxdz           1 2727387875             0
                  57bum9kjbnxdz           2 2727387875             0
                  57bum9kjbnxdz           8 2727387875             0
                  57bum9kjbnxdz           9 2727387875             0
                  57bum9kjbnxdz          10 2727387875             0
                  57bum9kjbnxdz          11 2727387875             0
                  57bum9kjbnxdz          14 2727387875             0
                  57bum9kjbnxdz          20 2727387875             0
                  
                  9 rows selected.
                  • 6. Re: why multiple child cursors 11.2.0.2 ?
                    rcc50886
                    query v$sql_shared_cursor for that sql_id and find which coulmn has value 'Y'
                    • 7. Re: why multiple child cursors 11.2.0.2 ?
                      OraDBA02
                      query v$sql_shared_cursor for that sql_id and find which coulmn has value 'Y'
                      That is already posted in opening thread.
                      • 8. Re: why multiple child cursors 11.2.0.2 ?
                        rcc50886
                        did you check this artical
                        http://prutser.wordpress.com/2009/07/16/rolling-cursor-invalidation/
                        • 9. Re: why multiple child cursors 11.2.0.2 ?
                          OraDBA02
                          Thanks for sharing the article.
                          I believe, my case is different from what is mentioned in article.
                          I am not seeing any INVALIDATION even 5 hr (_optimizer_invalidation_period is set to default 18000) after dbms_stats on table/index .
                          TABLE_NAME                     PAR   NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZED,'D SAMPLE_SIZE
                          ------------------------------ --- ---------- ---------- ------------------------ -----------
                          BALANCE_SUMMARY                NO    76282640    1595441 29-jul-12:09:07:12           3814132
                          
                          INDEX_NAME                     LAST_ANALYZED
                          ------------------------------ --------------------
                          BALANCE_SUMMARY_PK             29.Jul.12/09:07:47
                          select sql_id,child_number CN,plan_hash_value phv,invalidations,LAST_LOAD_TIME from v$sql where sql_id='57bum9kjbnxdz' order by 5;
                          
                          SQL_ID                 CN        PHV INVALIDATIONS LAST_LOAD_TIME
                          -------------- ---------- ---------- ------------- --------------------
                          57bum9kjbnxdz          14 2727387875             0 2012-05-22/12:04:20
                          57bum9kjbnxdz          20 2727387875             0 2012-06-04/10:13:39
                          57bum9kjbnxdz           8 2727387875             0 2012-07-15/12:19:38
                          57bum9kjbnxdz           0 2727387875             0 2012-07-29/14:45:12
                          57bum9kjbnxdz           1 2727387875             0 2012-07-29/14:54:43
                          57bum9kjbnxdz           2 2727387875             0 2012-07-29/14:55:20
                          57bum9kjbnxdz           9 2727387875             0 2012-07-29/14:58:18
                          57bum9kjbnxdz          10 2727387875             0 2012-07-29/15:03:18
                          57bum9kjbnxdz          11 2727387875             0 2012-07-29/15:23:15
                          
                          9 rows selected.
                          • 10. Re: why multiple child cursors 11.2.0.2 ?
                            rcc50886
                            Can you post "reason" column value of table v$sql_shared_cursor (it will be in xml format)

                            -Thanks
                            • 11. Re: why multiple child cursors 11.2.0.2 ?
                              OraDBA02
                               select sql_id,child_number,reason from v$sql_shared_cursor where sql_id='57bum9kjbnxdz';
                              
                              SQL_ID        CHILD_NUMBER REASON
                              ------------- ------------ --------------------------------------------------------------------------------
                              57bum9kjbnxdz            0 <ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)<
                                                         /reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kks
                                                         fcxe>0</dnum_kksfcxe></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>3</I
                                                         D><reason>Optimizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><k
                                                         xscfl4>4194560</kxscfl4><dnum_kksfcxe>0</dnum_kksfcxe></ChildNode><ChildNode><Ch
                                                         ildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)</reason><size>
                                                         3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>0</dnum_k
                                                         ksfcxe></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Opti
                                                         mizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560
                                                         </kxscfl4><dnum_kksfcxe>0</dnum_kksfcxe></ChildNode>
                              
                              57bum9kjbnxdz            1 <ChildNode><ChildNumber>1</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)<
                                                         /reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kks
                                                         fcxe>1</dnum_kksfcxe></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>3</I
                                                         D><reason>Optimizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><k
                                                         xscfl4>4194560</kxscfl4><dnum_kksfcxe>1</dnum_kksfcxe></ChildNode><ChildNode><Ch
                                                         ildNumber>1</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)</reason><size>
                                                         3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>1</dnum_k
                                                         ksfcxe></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>3</ID><reason>Opti
                                                         mizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560
                                                         </kxscfl4><dnum_kksfcxe>1</dnum_kksfcxe></ChildNode>
                              
                              57bum9kjbnxdz            2 <ChildNode><ChildNumber>2</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)<
                                                         /reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kks
                                                         fcxe>2</dnum_kksfcxe></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>3</I
                                                         D><reason>Optimizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><k
                                                         xscfl4>4194560</kxscfl4><dnum_kksfcxe>2</dnum_kksfcxe></ChildNode><ChildNode><Ch
                                                         ildNumber>2</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)</reason><size>
                                                         3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>2</dnum_k
                                                         ksfcxe></ChildNode><ChildNode><ChildNumber>2</ChildNumber><ID>3</ID><reason>Opti
                                                         mizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560
                                                         </kxscfl4><dnum_kksfcxe>2</dnum_kksfcxe></ChildNode>
                              
                              57bum9kjbnxdz            8 <ChildNode><ChildNumber>8</ChildNumber><ID>34</ID><reason>Rolling Invalidate Win
                                                         dow Exceeded(2)</reason><size>0x0</size><details>already_processed</details></Ch
                                                         ildNode><ChildNode><ChildNumber>8</ChildNumber><ID>3</ID><reason>Optimizer misma
                                                         tch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><
                                                         dnum_kksfcxe>8</dnum_kksfcxe></ChildNode>
                              
                              57bum9kjbnxdz            9 <ChildNode><ChildNumber>9</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)<
                                                         /reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kks
                                                         fcxe>9</dnum_kksfcxe></ChildNode><ChildNode><ChildNumber>9</ChildNumber><ID>3</I
                                                         D><reason>Optimizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><k
                                                         xscfl4>4194560</kxscfl4><dnum_kksfcxe>9</dnum_kksfcxe></ChildNode>
                              
                              57bum9kjbnxdz           10 <ChildNode><ChildNumber>10</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)
                                                         </reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kk
                                                         sfcxe>10</dnum_kksfcxe></ChildNode><ChildNode><ChildNumber>10</ChildNumber><ID>3
                                                         </ID><reason>Optimizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg
                                                         ><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>10</dnum_kksfcxe></ChildNode>
                              
                              57bum9kjbnxdz           11 <ChildNode><ChildNumber>11</ChildNumber><ID>34</ID><reason>Rolling Invalidate Wi
                                                         ndow Exceeded(3)</reason><size>2x4</size><invalidation_window>1337678170</invali
                                                         dation_window><ksugctm>1337679260</ksugctm></ChildNode><ChildNode><ChildNumber>1
                                                         1</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window Exceeded(2)</reason>
                                                         <size>0x0</size><details>already_processed</details></ChildNode>
                              
                              57bum9kjbnxdz           14 <ChildNode><ChildNumber>14</ChildNumber><ID>34</ID><reason>Rolling Invalidate Wi
                                                         ndow Exceeded(2)</reason><size>0x0</size><details>already_processed</details></C
                                                         hildNode><ChildNode><ChildNumber>14</ChildNumber><ID>34</ID><reason>Rolling Inva
                                                         lidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>133880218
                                                         2</invalidation_window><ksugctm>1338802539</ksugctm></ChildNode>
                              
                              57bum9kjbnxdz           20 <ChildNode><ChildNumber>20</ChildNumber><ID>34</ID><reason>Rolling Invalidate Wi
                                                         ndow Exceeded(2)</reason><size>0x0</size><details>already_processed</details></C
                                                         hildNode><ChildNode><ChildNumber>20</ChildNumber><ID>34</ID><reason>Rolling Inva
                                                         lidate Window Exceeded(3)</reason><size>2x4</size><invalidation_window>133993060
                                                         6</invalidation_window><ksugctm>1339931772</ksugctm></ChildNode>
                              
                              
                              9 rows selected.
                              • 12. Re: why multiple child cursors 11.2.0.2 ?
                                rcc50886
                                also post following query output:
                                select SQL_ID,CHILD_NUMBER,PLAN_HASH_VALUE,IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE from v$sql
                                where sql_id = &sql_id;
                                The above one will tells us whether the new child cursors are result of Adaptive cursor sharing feature (a.k.a cardinality feedback)
                                • 13. Re: why multiple child cursors 11.2.0.2 ?
                                  OraDBA02
                                  This is already posted in first correspondence.
                                  CF is definitely used as we can see USE_FEEDBACK is set to TRUE.

                                  However, i am not sure why Oracle is still holding child_cursors. Need to find out reasons for not aging them out !
                                  • 14. Re: why multiple child cursors 11.2.0.2 ?
                                    rcc50886
                                    according to your above post, both IS_BIND_SENSITIVE and IS_BIND_AWARE are set to 'N' for all child cursors.

                                    It specifies that child cursors are NOT a result of ACS.

                                    -Thanks,
                                    1 2 Previous Next