8 Replies Latest reply: Mar 4, 2013 5:09 AM by Aman.... RSS

    Why my SQL Plans age very quickly ?

    Chiwatel
      Hi All,

      I am working on 11g R2 (Windows) and I am doing some tuning of a particular query. When I run several times the query, it is fairly quick but what I noticed is that the plan ages out very quickly. So when I run the same query 5 minutes later, it seems that Oracle parse it again and it takes much more time.

      Also, When I want to see the plan in dbms_xplan.display_cursor after these 5 minutes I have the following error:

      NOTE: cannot fetch plan for SQL_ID: avcd12sdzr3, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

      Does someone know why my plan is aging so quickly and how I can fix this ?

      Many thanks.
        • 1. Re: Why my SQL Plans age very quickly ?
          sb92075
          Chiwatel wrote:
          Hi All,

          I am working on 11g R2 (Windows) and I am doing some tuning of a particular query. When I run several times the query, it is fairly quick but what I noticed is that the plan ages out very quickly. So when I run the same query 5 minutes later, it seems that Oracle parse it again and it takes much more time.

          Also, When I want to see the plan in dbms_xplan.display_cursor after these 5 minutes I have the following error:

          NOTE: cannot fetch plan for SQL_ID: avcd12sdzr3, CHILD_NUMBER: 1
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

          Does someone know why my plan is aging so quickly and how I can fix this ?

          Many thanks.
          post both EXPLAIN PLAN for fast & slow executions
          • 2. Re: Why my SQL Plans age very quickly ?
            Hemant K Chitale
            Two possibilities
            1) Your SHARED_POOL_SIZE is small causing aging more frequently
            2) There is some DDL (e.g. an ALTER) being executed against one or more of the objects (tables) underlying the query causing the query and plan to be aged out


            Hemant K Chitale
            • 3. Re: Why my SQL Plans age very quickly ?
              Nikolay Savvinov
              Hi,

              three distinct scenarios are possible here: loads, invalidations or sharing. I would start by looking in V$SQL_SHARED_CURSOR, V$SQL and DBA_HIST_SQLSTAT to determine which of the three is taking place. "Loads" means that the plan gets aged out of the shared pool more or less "naturally", either because the shared pool is not sufficiently big, or because there is a lot of other stuff in it, or because its fragmentation is causing the space to be used less efficiently and thus internal housekeeping kick in more often. "Invalidations" means that the cursor is marked invalid because of DDL or statistics updated against an object referenced in the query. There are columns called "loads" and "invalidations" in V$SQL and DBA_HIST_SQLSTAT views that may help you understand if either of the two scenarios is taking place.

              The third scenario, sharing, is a bit more subtle. It could be that for example the size of one of the bind variables is changing considerably once in a while, and as a result the cursor cannot be reused. In this case, a new child cursor would be parsed, and the reason why existing cursor couldn't be shared could me in most cases found in V$SQL_SHARED_CURSOR view.

              Best regards,
              Nikolay
              • 4. Re: Why my SQL Plans age very quickly ?
                Chiwatel
                Thanks Hemant and Nikolay.

                The SGA is about 2 Gigs so I guess it is big enough and there was no DDL when I tested the query.

                What is strange is that there was normally no activity and the database when I run my tests this is why it was very strange to me that the plan aged out.

                Anyway, I will try your suggestions and let you know !

                Many thanks.
                • 5. Re: Why my SQL Plans age very quickly ?
                  rahulras
                  Can you run this query and post the output here
                  select pool, sum(bytes)/(1024 * 1024) size_mb from v$sgastat group by pool ;
                  This will show size of various pools in SGA.

                  Many times, there is some application (written in 3GL language) which generates SQLs with hardcoded literals. So, same SQL with different values hardcoded in the SQL are executing on database e.g. select name from customer where id='ABCD' and select name from customer where id='EFGH' and select name from customer where id='IJKL' are 3 different SQLs if we don't use bind variables.

                  Try this query
                  select sql_id, first_load_time, sql_text from v$sqlarea;
                  If this query show plenty of similar looking SQLs with different SQL_IDs, these SQLs are flooding the cache with SQLs and older plans are flushed out quickly.
                  • 6. Re: Why my SQL Plans age very quickly ?
                    Nikolay Savvinov
                    Hi,
                    Chiwatel wrote:
                    Thanks Hemant and Nikolay.

                    The SGA is about 2 Gigs so I guess it is big enough
                    What are you comparing this to?
                    and there was no DDL when I tested the query.
                    DDL is just one of the possibilities. There's also rolling invalidation due to updates stats, which may cause the cursor to get invalidated hours after the stats changed. There could be even more complex scenarios, e.g. invalidations caused by shared pool being resized by ASMM internal mechanisms.

                    But before guessing, it's best to simply check statistics on loads and invalidations, it could be that you will find your answer there.
                    What is strange is that there was normally no activity and the database when I run my tests this is why it was very strange to me that the plan aged out.
                    It does sound strange, but let's not forget that a database is an extremely complex system where a lot is going on behind the scene.
                    Anyway, I will try your suggestions and let you know !
                    OK, good luck with that!

                    Best regards,
                    Nikolay
                    • 7. Re: Why my SQL Plans age very quickly ?
                      Chiwatel
                      Hi Rahulras,

                      Here is the result of your query :
                      Pool                    Size in MB
                                           744,631866455078125
                      java pool             16
                      streams pool             16
                      shared pool             352
                      large pool             16
                      does this mean that the SGA is 744 MB ?

                      Thanks !
                      • 8. Re: Why my SQL Plans age very quickly ?
                        Aman....
                        Chiwatel wrote:
                        Hi Rahulras,

                        Here is the result of your query :
                        Pool                    Size in MB
                                             744,631866455078125
                        java pool             16
                        streams pool             16
                        shared pool             352
                        large pool             16
                        does this mean that the SGA is 744 MB ?
                        Not the SGA in total but only the variable  part of it!

                        Aman....