13 Replies Latest reply on Mar 2, 2015 3:14 PM by mtefft

    Reached limit on 'colored SQL' - any workarounds?

    mtefft

      We are trying to use DBMS_WOKRLOAD_REPOSITORY.ADD_COLORED_SQL to capture our rarely-used batch SQL. (We have had problems with SQL that usually runs in seconds, but suddenly picks a plan that runs for hours instead. We want to force all the SQL produced by these batch processes into AWR so that we have a source for creating baselines.

       

      So, while trying this out, we were surprised to get this error:

      ORA-13534: Current SQL count(100) reached maximum allowed (100)


      I was surprised to see this, since there is no mention of such a limit anywhere else I looked. Has anyone else encountered this? Was there any workaround?


      I find it hard to believe that everyone who used this feature has stopped short of 100 SQL statements.


      Thanks,

      Mike


        • 1. Re: Reached limit on 'colored SQL' - any workarounds?

          13534, 00000, "Current SQL count(%s) reached maximum allowed (%s)"

          // *Cause:  The operation failed because the maximum allowed colored

          //          SQL was exceeded.

          // *Action: Check the count for colored SQL.

           

          http://docs.oracle.com/database/121/ARPLS/d_workload_repos.htm#ARPLS69144

          • 2. Re: Reached limit on 'colored SQL' - any workarounds?
            JohnWatson2

            I hit the same limit in a quick test:

             

            orclz>

            orclz> begin

              2  for i in 1..110 loop

              3  dbms_workload_repository.add_colored_sql(lpad(to_char(i),13,'x'));

              4  end loop;

              5  end;

              6  /

            begin

            *

            ERROR at line 1:

            ORA-13534: Current SQL count(100) reached maximum allowed (100)

            ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 751

            ORA-06512: at line 3

             

             

            orclz> select * from v$version;

             

            BANNER                                                                              CON_ID

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

            Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0

            PL/SQL Release 12.1.0.2.0 - Production                                                    0

            CORE    12.1.0.2.0      Production                                                                0

            TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                  0

            NLSRTL Version 12.1.0.2.0 - Production                                                    0

             

            orclz>

            • 3. Re: Reached limit on 'colored SQL' - any workarounds?
              Jonathan Lewis

              It looks like it's hard coded; a trace shows that the code selects count(*) from the wrm$_colored_sql table, and then terminates if the count is 100 or over. There is no prior SQL run to read a value that could be that limiting value. 

              I did wonder if the value for TOPNSQL was used in some way (100 happens to be the default value for statistics_level = ALL)  but setting a specific value didn't make any difference. The tiny limit on colored SQL is a little surprising since, by comparison, you can set topNsql to several thousand.

               

              Regards

              Jonathan Lewis

              1 person found this helpful
              • 4. Re: Reached limit on 'colored SQL' - any workarounds?
                mtefft

                Forgot to include the version:

                BANNER

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

                Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

                PL/SQL Release 11.2.0.4.0 - Production

                CORE    11.2.0.4.0      Production

                TNS for Linux: Version 11.2.0.4.0 - Production

                NLSRTL Version 11.2.0.4.0 - Production

                • 5. Re: Reached limit on 'colored SQL' - any workarounds?
                  jgarry

                  I don't quite get it, but it is expected behavior to not appear in top sql unless it exceeds defined report thresholds. Someone should ask MOS why 100, since one would think you'd want this stuff on some report. SQL Statements Marked with DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL may not Appear in the AWR Report (Doc ID 1926068.1)

                  • 6. Re: Reached limit on 'colored SQL' - any workarounds?
                    mtefft

                    The purpose of ADD_COLORED_SQL is to instruct AWR to retain information about the specified SQL, regardless of whether it was a 'Top N' query or not.

                     

                    It is true that an AWR report may not show the colored SQL, because the criteria for an AWR report is not necessarily the same as what gets into AWR in the first place. But our interest is not really in the AWR reports - we want to make sure the plans are captured into AWR. Once in AWR,  we can use DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY followed by DBMS_SPM.LOAD_PLANS_FROM_SQLSET to build baselines for the blindingly-fast plans.

                    • 7. Re: Reached limit on 'colored SQL' - any workarounds?
                      Dom Brooks

                      > Once in AWR,  we can use DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY followed by DBMS_SPM.LOAD_PLANS_FROM_SQLSET to build baselines for the blindingly-fast plans.

                       

                      Perhaps you are baselining too much / Why do you need baselines for all these statements which don't normally feature in AWR?

                       

                      This limit is surprising but the fact that we've not really heard about this limit before is maybe an indicator of something?

                      • 8. Re: Reached limit on 'colored SQL' - any workarounds?
                        Jonathan Lewis

                        jgarry wrote:

                         

                        I don't quite get it, but it is expected behavior to not appear in top sql unless it exceeds defined report thresholds. Someone should ask MOS why 100, since one would think you'd want this stuff on some report. SQL Statements Marked with DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL may not Appear in the AWR Report (Doc ID 1926068.1)

                         

                        The thought I had in mind was that the 100 limit probably wasn't set because of a performance threat, given that the relevant extraction code would be similar to the code used to extract the Top N (... which allows up to 5,000 statements to be captured).

                         

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Reached limit on 'colored SQL' - any workarounds?
                          Jonathan Lewis

                          Dom Brooks wrote:

                           

                           

                          This limit is surprising but the fact that we've not really heard about this limit before is maybe an indicator of something?

                           

                          The cynic in me says the reason we haven't heard about the limit before is that references to the whole colored_sql thing are so low-key in the manuals that very few people realise it's even available.  (Like to many other little features.)

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: Reached limit on 'colored SQL' - any workarounds?
                            mtefft

                            > Perhaps you are baselining too much / Why do you need baselines for all these statements which don't normally feature in AWR?

                             

                            They are not normally in AWR because they run infrequently (weekly or monthly batch) and they have usually run fast (less than a minute).

                             

                            We want baselines for them because a bad plan can cause a query like this to run for hours and impact the SLAs. This is not theoretical - I have had multiple incidents like this over the past year, from unrelated applications, where a plan went bad but we did not have the previous 'good' plan in AWR and so we could not implement a baseline.

                             

                            We may need to move instead to enabling automatic collection of baselines. I have been reluctant to do that because the first plan constructed for a SQL statement becomes an accepted baseline, so i9t's important to have an Evolve process in place, etc.

                            • 11. Re: Reached limit on 'colored SQL' - any workarounds?
                              mtefft

                              The cynic in me says: some developer set the limit at 100 so he could exercise the 'you have reached your limit' test case more easily, and never set it back to the intended limit of 32k or whatever...

                              • 12. Re: Reached limit on 'colored SQL' - any workarounds?
                                jgarry

                                The cynic in me says people brainstormed this one day a couple of versions ago, someone came up with the code, but no one ever revisited it because they got no feedback as to how it was being used in the real world.

                                • 13. Re: Reached limit on 'colored SQL' - any workarounds?
                                  mtefft

                                  FYI, in response to my SR asking about a workaround, an enhancement request has been opened:

                                  Bug 20628661 : ORA-13534: CURRENT SQL COUNT REACHED MAXIMUM ALLOWED

                                   

                                  In my SR I suggested wording to actually communicate the intent of the enhancement request, such as: increase the number of SQL statements that can be colored by DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL, with a suggestion of 50,000 (which is the limit on DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(TOPNSQL).

                                   

                                  But the analyst said they know what the enhancement request is really about, by other means. So I'm sure it is nothing at all like the end of 'Raiders of the Lost Ark'.