8 Replies Latest reply: Feb 14, 2013 12:30 AM by 914789 RSS

    dynamic sampling for dbms_stats.lock_table_stats

    914789
      Hi,

      For a 11.2.0.3 version database, the requirement is to have temporary tables non-analyzed.
      Accidently these temporary tables were analyzed using gather_table_stats, and application faced some serious issues.

      1- To avoid any accidental gather stats on temporary tables is the below approach valid:
      - Delete exiisting stats on the table.
      - Lock the table stats using dbms_stats.lock_table_stats
      - Any attempts to gather stats would now result in error.

      2- Once the stats have been deleted and stats being locked using dbms_stats, would oracle perform dynamic sampling.
      ( I'm Considering dynamic sampling is good compared to stats gathered for temporary tables in this scenario )

      Kindly suggest.

      Rgds,
      Vijay

      Edited by: 911786 on Feb 7, 2013 2:44 AM
        • 1. Re: dynamic sampling for dbms_stats.lock_table_stats
          Robert Geier
          If you rely on dynamic sampling you can get unpredictable results, and there can be a performance cost.

          Are the stats on the table representative of the data ? Often with temporary tables stats are collected when the table is empty, so don't represent the data, so better to populate the table manually, collect stats, then lock stats.

          Note that dbms_stats can be run with the force option to override locked stats, so locking does not guarantee stats don't change. It depends on how you collect stats ...
          • 2. Re: dynamic sampling for dbms_stats.lock_table_stats
            914789
            Hi Robert,

            Thank you for the suggestions, very helpful in a generic scenario.

            The application/product hit a bug with temporary tables being analyzed and it's a known issue for them.
            Their R&D team adviced to delete the stats on temporary tables and these should never be gathered in future.

            As a precautionary measure, we thought of locking the table stats once the stats are deleted, to avoid any accidental stats gather. ( with an exception of 'force')
            In an ideal case, for this application, stats will never be gathered on temporary tables, it just happened accidently once.

            My doubt is,
            With deleted stats and locked stats, will the temporary tables be benefitted with the dynamic sampling.
            Or shall we just delete the stats and do not lock the stats.

            Rgds,
            Vijay
            • 3. Re: dynamic sampling for dbms_stats.lock_table_stats
              Robert Geier
              "The application/product hit a bug with temporary tables being analyzed".

              Personally I would focus on investigating and fixing the bug rather than removing statistics.
              • 4. Re: dynamic sampling for dbms_stats.lock_table_stats
                Hemant K Chitale
                Personally I would focus on investigating and fixing the bug rather than removing statistics.
                Well.. there are application designs that use such tables as "staging" tables where data is loaded, queried and truncated. The wrong statistics could mean that the queries could perform poorly --- particularly if every day the data is different.

                A strategy of removing statistics is perfectly reasonable in such cases.


                Hemant K Chitale
                • 5. Re: dynamic sampling for dbms_stats.lock_table_stats
                  Robert Geier
                  The problem is reported as a "bug" (e.g an error message or code failure) not a bad execution plan.

                  If statistics on a table cause the code to fail, then that bug should be investigated instead of removing all statistics.

                  If the problem was incorrectly reported as a bug, and is just a bad execution plan, then I agree that dynamic sampling will work, but if the data each day is similar in volume and distribution it would still be better to pre-collect stats on a populated table.
                  • 6. Re: dynamic sampling for dbms_stats.lock_table_stats
                    Dom Brooks
                    If there is a single set of statistics that works for all scenarios then, imho, those should be set and locked.

                    Otherwise dynamic sampling may be an adequate compromise assuming that the time taken to do the dynamic sampling is low compared to execution time and bearing in mind that dynamic sampling does not prevent shared sql - i.e. the assumption is that the numbers dynamic sampling come up with are good for any subsequent executions of that execution plan and, for example, that dynamic sampling will not sample an empty table and come up with a plan that is not good for any subsequent executions of that plan when the table is not empty.
                    • 7. Re: dynamic sampling for dbms_stats.lock_table_stats
                      Hemant K Chitale
                      The vendor likely calls it a "bug" for lack of a better way to explain the behaviour of the optimizer when delaing with such tables. They go on to say that it is "a known issue". The "workaround" is the right action plan.



                      Hemant K Chitale
                      • 8. Re: dynamic sampling for dbms_stats.lock_table_stats
                        914789
                        Hi All,

                        Thanks a lot for the valuable discussion and suggestions.

                        The appliication resulted in "SQL Timeout Exception:{mercury}execution timeout expired" during data sync activity. I might have created confusion calling it a 'bug'.
                        It's a known issue for product R&D team and they adviced never to analyze the temporary tables.

                        Rgds,
                        Sanjay