4 Replies Latest reply: Dec 10, 2012 1:30 PM by Py RSS

    Default parameter for statistic generation: DBMS_STATS.SET_DATABASE_PREFS()

    Py
      Hi all,

      I am running a database version 11.2.0.3 on a RHEL 5.3 system.
      A nightly ETL job drops all tables of a certain schema and recreates them as part of this job.
      After recreation of the tables statistics are generated to make query execution faster.

      The ETL job uses following statement to gather the statistics for each table:
      EXEC dbms_stats.gather_table_stats('SCHEMA', 'TABLE', cascade => TRUE);
      As we had performance issues on the database I analysed the statistics and found that there
      were no histograms collected for the tables although the data are skewed.

      Therefore I searched a database wide parameter where I can change the default statistic collection method from
      'SIZE AUTO' to 'SIZE SKEWONLY'. I found following method: DBMS_STATS.SET_DATABASE_PREFS ()
      I used this package to set the database prefs to 'size skewonly' and recreated the statistics. All was fine.
      exec DBMS_STATS.SET_DATABASE_PREFS (pname=>'METHOD_OPT', pvalue=>'FOR ALL COLUMNS SIZE SKEWONLY');
      But one day later after the ETL job we had the same issue again. After a further analyse I saw that there were no histograms generated
      on the tables. It seems that the drop and recreation of the tables make the system wiede default parameter useless.


      Does anyone know a method how to set system wide optimizer defaults even after dropping and recreating a table?

      Thanks in advance

      Edited by: Py on 10.12.2012 18:33
        • 1. Re: Default parameter for statistic generation: DBMS_STATS.SET_DATABASE_PREFS()
          User286067
          Py wrote:
          exec DBMS_STATS.SET_DATABASE_PREFS (pname=>'METHOD_OPT', pvalue=>'FOR ALL COLUMNS SIZE SKEWONLY');
          By saying SKEWONLY you are letting oracle make the decision, maybe oracle made the decision to NOT collect histograms. Why not set it to FOR ALL INDEXED COLUMNS SIZE AUTO (or other option) in your test environment and verify? You dont have to run the ETL job, just set the pref, run the same command that ETL job would run and then verify via dictionary.

          If it is specific schema you have issue with, then you ought to be careful and use SCHEMA_PREFS, DATABASE_PREFS means all schemas excluding SYS. So, you may end up impacting other schema without intending to.

          Raj
          • 2. Re: Default parameter for statistic generation: DBMS_STATS.SET_DATABASE_PREFS()
            Max Seleznev
            >
            Does anyone know a method how to set system wide optimizer defaults even after dropping and recreating a table?
            >

            Yes. Without discussing if the parameter of your choice is the best possible solution for your problem you can always use
            SQL> exec dbms_stats.set_global_prefs('METHOD_OPT', 'FOR ALL COLUMNS SIZE SKEWONLY');
            This setting will also affect all the subsequently created tables.

            There're a few alternative solutions on top of it.

            - If your tables have the same structure once recreated, you can truncate them instead of dropping and creating.

            - Since you're collecting the stats anyway you can explicitly specify any supported parameter right in the procedure:
            SQL> EXEC dbms_stats.gather_table_stats('SCHEMA', 'TABLE', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');
            Hope it helps.
            • 3. Re: Default parameter for statistic generation: DBMS_STATS.SET_DATABASE_PREFS()
              Py
              Thank you for your answer.
              The issue is that the application is located in a validated environment. This means that there is a hugh amount of validation, testing and documentation effort if we would change the ETL routine. This would have to be done by our debelopers.

              From my side of view (DBA) it seems to be less effort to "only" change DB parameters.
              Therefore I first tried to change the parameter on the three relevant schemas with following statement
              exec dbms_stats.set_schema_prefs(<parameter>);
              But the result was that it was only applied for existing tables and not for these ones that I created after the statement. (Bahaviour is tested.)

              After this I tried the same thing on next level with dbms_stats.set_database_prefs();
              I did not try to use the "set_global_prefs" method.

              Will discuss this with my developers tomorrow if it is an option to adapt the ETL job.
              • 4. Re: Default parameter for statistic generation: DBMS_STATS.SET_DATABASE_PREFS()
                Max Seleznev
                Your situation sounds familiar, but there's no progress without the change.

                The set_global_prefs procedure works in my environment (11.2.0.2 on RH5.6).