Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'

Vsevolod Afanassiev

    11.2 on Linux

     

    The application is written in Java and uses Java programming framework where UPDATE statements are auto-generated. This means that every column gets updated and the values come from bind variables. So a table with 50 columns gets an UPDATE statement with 50 bind variables.

     

    UPDATE TABLE_A SET COLUMN1 = :1, COLUNM2 = :2, COLUMN3 = :3 ... WHERE COLUMN0 = :50

     

    This UPDATE gets high version count due to bind_mismatch as determined from v$sql_shared_cursor: there are

    about 1000 child cursors for this UPDATE.

     

    High version count leads to waits on 'cursor: mutex X' and 'cursor: mutex S'. The waits are significant, with dozens of sessions waiting on these events, all for this UPDATE. I've tried flushing this cursor using dbms_shared_pool.purge and it didn't work. Flushing the entire shared pool with ALTER SYSTEM FLUSH SHARED_POOL works but may have its own side effects.

     

    For bind mismatch to occur a bind variable needs to be re-defined between executions:

     

    http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/09/26/why-my-execution-plan-has-not-been-shared-part-iii

     

    And this is exactly what I am seeing in V$SQL_BIND_CAPTURE: the same bind variable may be defined either as VARCHAR2(32) or VARCHAR2(128) or VARCHAR2(2000). As there are so many bind variables there are many combinations and we get high version count.

     

    What could be done about it other than changing the application?

      • 1. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
        AndrewSayer

        Vsevolod Afanassiev wrote:

         

        11.2 on Linux

         

        The application is written in Java and uses Java programming framework where UPDATE statements are auto-generated. This means that every column gets updated and the values come from bind variables. So a table with 50 columns gets an UPDATE statement with 50 bind variables.

         

        UPDATE TABLE_A SET COLUMN1 = :1, COLUNM2 = :2, COLUMN3 = :3 ... WHERE COLUMN0 = :50

         

        This UPDATE gets high version count due to bind_mismatch as determined from v$sql_shared_cursor: there are

        about 1000 child cursors for this UPDATE.

         

        High version count leads to waits on 'cursor: mutex X' and 'cursor: mutex S'. The waits are significant, with dozens of sessions waiting on these events, all for this UPDATE. I've tried flushing this cursor using dbms_shared_pool.purge and it didn't work. Flushing the entire shared pool with ALTER SYSTEM FLUSH SHARED_POOL works but may have its own side effects.

         

        For bind mismatch to occur a bind variable needs to be re-defined between executions:

         

        http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/09/26/why-my-execution-plan-has-not-been-shared-part-iii

         

        And this is exactly what I am seeing in V$SQL_BIND_CAPTURE: the same bind variable may be defined either as VARCHAR2(32) or VARCHAR2(128) or VARCHAR2(2000). As there are so many bind variables there are many combinations and we get high version count.

         

        What could be done about it other than changing the application?

        How many columns are in this table?

        Why is the Java program not using the same data types as the table?

         

        What is the framework you are using? Have you googled around to see what other people that faced the same awful performance did?


        Maybe consider writing your own SQL.

         

        -edit

        You might have a lot better luck with JOOQ, I've never used it before but I've only heard good things https://www.jooq.org/

        • 2. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
          Vsevolod Afanassiev

          The table has 50 columns. Java program uses the same type VARCHAR2 as the column but with varying length.

           

          I noticed this behaviour in several databases with applications developed by different vendors.

          So far significant waits on 'cursor: mutex X/S' have been observed only in one database.

          • 3. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
            Stefan Koehler

            Hello Vsevolod,

             

            > And this is exactly what I am seeing in V$SQL_BIND_CAPTURE: the same bind variable may be defined either as VARCHAR2(32) or VARCHAR2(128) or VARCHAR2(2000). As there are so many bind variables there are many combinations and we get high version count. What could be done about it other than changing the application?

             

            Well the obvious and one and only correct answer in this case would be: Fix this damn application. There is no reason to define a bind variable with different VARCHAR2 length - define the variable the same way as the column - that's it. However you can try to work-around this issue with event 10503 but this is only a work-around - not a fix.

             

            Best Regards

            Stefan Koehler

            Independent Oracle performance consultant and researcher

            • 4. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
              Jonathan Lewis

              This isn't necessarily the fault of the application - unless things have changed in recent versions of Oracle. The choice of size of "incoming" bind variable isn't completely under the control of the client code, there's some Oracle layer getting in the way to minimise the memory usage when an incoming value is smaller than the declared maximum length:  https://jonathanlewis.wordpress.com/2007/01/05/bind-variables/

               

              One option is to try and force the first calls to the update to use the maximum length for every bind variable; alternatively, as a damage limitation exercise, you could use the markhot() procedure to generate multiple parent cursors for the same SQL text - that might be enough to spread the child cursors widely enough to minimise the contention.

               

              begin

                      dbms_shared_pool.markhot(

                              hash            =>'71fc6ccf9a3265368492ec9fc05b785b',

                              namespace       =>0,

                              global          =>true

                      );

              end;

              /

               

              Where "hash" is the "full hash value" of the statement, which you can find by querying v$db_object_cache:

               

              select

                      hash_value,

                      full_hash_value,

                      namespace,

                      child_latch,

                      property        hot_flag,

                      executions,

                      invalidations

              from

                      v$db_object_cache

              where

                      name like '{some obvious bit of your SQL statement}'

              ;

               

              (Or you could search using the hash_value from v$sql).

               

              You can also set this with an alter system, or as a startup parameter like:

               

              _kgl_debug="hash='cc7d5ecdcc9e7c0767456468efe922ea' namespace=0 debug=33554432"

               

              This latter is still the best way, even in the versions that have exposed markhot() in dbms_shared_pool.

               

               

               

              Regards

              Jonathan Lewis

              1 位用户发现它有用
              • 5. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                Vsevolod Afanassiev

                Thanks for responding, so this is generic issue, not limited to a particular application.

                What should happen after I use dbms_shared_pool.markhot() ? Is Oracle going to create multiple entries in V$SQLAREA with different hash_value/sql_id?

                • 6. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                  Stefan Koehler

                  Hello Jonathan,

                   

                  > This isn't necessarily the fault of the application - unless things have changed in recent versions of Oracle.

                   

                  AFAIK this is exactly what happened. Sigrid has done a nice talk about this at the DOAG conference a few years ago (starting at slide 17): https://recurrentnull.files.wordpress.com/2015/11/raising_the_fetch_size_good_or_bad.pdf

                  IMHO dbms_shared_pool.markhot is just another work-around (like event 10503) but the real problem is the application and/or the used driver.

                   

                   

                  @Vsevolod

                  > What should happen after I use dbms_shared_pool.markhot() ? Is Oracle going to create multiple entries in V$SQLAREA with different hash_value/sql_id?

                   

                  Yes. Please have a look at my Tweet about this feature - it looks like this: https://twitter.com/OracleSK/status/864445425222389760

                   

                  Best Regards

                  Stefan Koehler

                  Independent Oracle performance consultant and researcher

                  • 7. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                    Jonathan Lewis

                    Vsevolod Afanassiev wrote:

                     

                    Thanks for responding, so this is generic issue, not limited to a particular application.

                    What should happen after I use dbms_shared_pool.markhot() ? Is Oracle going to create multiple entries in V$SQLAREA with different hash_value/sql_id?

                    It's possible that it really is coded explicitly into the application, of course, but it's more likely to be something about the libraries between the application and the database so "generic issue" would be my first suspect.

                     

                    Yes - you get multiple parent cursor (with different SQL_IDs) and each will get their own chain of child cursors. The choice of parent cursor seems to be dictated by the process id (but maybe the session id) of the statement executing the statement. This does mean that (over time) each parent will end up getting as many child cursors as you currently see - which means a lot more shared pool memory being used for the statement - but each parent will have fewer sessions competing to search its chain; so your trade-off should be memory vs. CPU and contention.

                     

                    I'll be writing up a blog note tonight about this, I've just discovered something I started about 3 years ago so it's time I finished it.

                     

                    Regards

                    Jonathan Lewis

                    • 8. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                      Jonathan Lewis

                      Stefan Koehler wrote:

                       

                      IMHO dbms_shared_pool.markhot is just another work-around (like event 10503) but the real problem is the application and/or the used driver.

                       

                       

                      Yes. Please have a look at my Tweet about this feature - it looks like this: https://twitter.com/OracleSK/status/864445425222389760

                       

                       

                      I can't remember whether or not we exchanged a couple of emails when you did that tweet.

                       

                      Agreed it's possible that the application really could be doing this explicitly, but I think the second option is far more likely - it's implicit and the anomaly is controlled by some feature of the driver.

                       

                      Regards

                      Jonathan Lewis

                      • 9. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                        Dmitry Remizov

                        Let me suggest one more workaround.

                         

                        You can call one "fake" UPDATE at the very beginning of your application (with apriori  false condition ) with very lengthy binds.

                         

                        The idea behind:

                        If you have bind set like:

                        VARCHAR2(128), VARCHAR2(128),VARCHAR2(128),...., VARCHAR2(128)

                        Oracle would never create something like

                        VARCHAR2(32), VARCHAR2(128), VARCHAR2(32), ..... VARCHAR2(128)

                        or

                        VARCHAR2(128), VARCHAR2(32), VARCHAR2(32), ..... VARCHAR2(32)

                         

                        .So your goal is to create bind set with

                        VARCHAR2(4000),VARCHAR2(4000),...
                        or VARCHAR2(4001),VARCHAR2(4001),... depends on your NLS settings

                        and live with it

                        • 10. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                          AndrewSayer

                          user882251 wrote:

                           

                          Let me suggest one more workaround.

                           

                          You can call one "fake" UPDATE at the very beginning of your application (with apriori false condition ) with very lengthy binds.

                           

                          The idea behind:

                          If you have bind set like:

                          VARCHAR2(128), VARCHAR2(128),VARCHAR2(128),...., VARCHAR2(128)

                          Oracle would never create something like

                          VARCHAR2(32), VARCHAR2(128), VARCHAR2(32), ..... VARCHAR2(128)

                          or

                          VARCHAR2(128), VARCHAR2(32), VARCHAR2(32), ..... VARCHAR2(32)

                           

                          .So your goal is to create bind set with

                          VARCHAR2(4000),VARCHAR2(4000),...
                          or VARCHAR2(4001),VARCHAR2(4001),... depends on your NLS settings

                          and live with it

                          Are you suggesting that if a child cursor that was parsed with bind variable lengths greater than what the SQL is being called with now, Oracle will just use the old child cursor? That seems to go against what the OP is experiencing, you could imagine that there wouldn't be quite so many child cursors if that is the case.

                           

                          Additionally, how do you add the false condition without changing the sql_id?

                          • 11. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                            AndrewSayer

                            Vsevolod Afanassiev wrote:

                             

                            Thanks for responding, so this is generic issue, not limited to a particular application.

                            What should happen after I use dbms_shared_pool.markhot() ? Is Oracle going to create multiple entries in V$SQLAREA with different hash_value/sql_id?

                            Id be interested in hearing how you got on with this. How much extra memory was required for all these parent cursors? The sharable memory column in v$sqlArea is what I would go by. You have a fairly small SQL here, so I imagine the trade off is a no-brainer, I could also imagine similar scenarios where the additional shared pool memory required might be enough to trigger some ASMM reallocation and you loose a substantial amount of buffer cache as sacrifice. Of course, it's a cost & reward based decision.

                            • 12. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                              Jonathan Lewis

                              Andrew Sayer wrote:

                               

                              Are you suggesting that if a child cursor that was parsed with bind variable lengths greater than what the SQL is being called with now, Oracle will just use the old child cursor? That seems to go against what the OP is experiencing, you could imagine that there wouldn't be quite so many child cursors if that is the case.

                               

                              Additionally, how do you add the false condition without changing the sql_id?

                               

                              Andrew,

                               

                              The topic of "start with a big one" came up in the comments to the posting I linked to earlier on - https://jonathanlewis.wordpress.com/2007/01/05/bind-variables/ It's not obvious why this should work, but perhaps it's simply the case that when Oracle is searching the child chain for a match it accepts the first child where the bind variables are not small than the incoming bind variables.

                               

                              Given there are 5 possible lengths that Oracle uses internally (0,32,128,2000,4000) and the OP has 50 bind variables, the number of possible combinations is huge so it's not surprising to see hundreds of child cursors.

                               

                              To implement the dirty trick it might be sufficient to have a procedure that simply did "insert a row with maximum lengths; rollback".

                               

                              Regards

                              Jonathan Lewis

                              • 13. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                                AndrewSayer

                                Jonathan Lewis wrote:

                                 

                                Andrew Sayer wrote:

                                 

                                Are you suggesting that if a child cursor that was parsed with bind variable lengths greater than what the SQL is being called with now, Oracle will just use the old child cursor? That seems to go against what the OP is experiencing, you could imagine that there wouldn't be quite so many child cursors if that is the case.

                                 

                                Additionally, how do you add the false condition without changing the sql_id?

                                 

                                Andrew,

                                 

                                The topic of "start with a big one" came up in the comments to the posting I linked to earlier on - https://jonathanlewis.wordpress.com/2007/01/05/bind-variables/ It's not obvious why this should work, but perhaps it's simply the case that when Oracle is searching the child chain for a match it accepts the first child where the bind variables are not small than the incoming bind variables.

                                 

                                Given there are 5 possible lengths that Oracle uses internally (0,32,128,2000,4000) and the OP has 50 bind variables, the number of possible combinations is huge so it's not surprising to see hundreds of child cursors.

                                 

                                To implement the dirty trick it might be sufficient to have a procedure that simply did "insert a row with maximum lengths; rollback".

                                 

                                Regards

                                Jonathan Lewis

                                Jonathan,

                                 

                                I was working under the assumption that even though the statement had plenty of columns, only a few were actually useful to the child cursor (the bind variables in the SET clause are hopefully not important). Although, looking back, we only have one column in the filter... and it's also a generically named column that's probably also a varchar2 (which raises questions), and we definitely have more than 5 child cursors. You're right though, 50 variables with 5 possible lengths would give an upper limit of <humongous> so if we're only seeing about 1000 child cursors, it does give some weight to the suggestion. I'm not completely confident that this is the way that it should behave and it would definitely be something to add to the ever growing list of checks after each patch.

                                 

                                Aside, in 11.2.0.3 (and possibly later), I have seen child cursors with duplicate sql_id and child numbers (it looped at 99), this makes me a little suspicious about any counts using child cursors as obviously some likely assumptions can not be made. That said, I have no reason to believe anything fishy has happened with the numbers given here.

                                 

                                Cheers,

                                Andrew

                                • 14. Re: Auto-generated UPDATE/High Version count due to bind_mismatch/waits on 'cursor: mutex X' and 'cursor: mutex S'
                                  Jonathan Lewis

                                  Andrew,

                                   

                                   

                                  Aside, in 11.2.0.3 (and possibly later), I have seen child cursors with duplicate sql_id and child numbers (it looped at 99), this makes me a little suspicious about any counts using child cursors as obviously some likely assumptions can not be made. That said, I have no reason to believe anything fishy has happened with the numbers given here.

                                   

                                  Possibly something to do with Oracle marking parent cursors obsolete when they get too many children.  There was a patch some time around 11g that required an event to set the limit where a parent would go obsolete, then this turned into the hidden parameter _cursor_obsolete_threshold.  I think that the value was initially 100, but in 11.2.0.4 the parameter defaults to 1024 (and it's 8192 in 12.2 !)

                                   

                                  An obsolete parent would be aged out of the cursor cache with all its children, but while it was still in the cache a process wouldn't have to search its chain.

                                   

                                  Regards

                                  Jonathan Lewis

                                  1 2 上一个 下一个