1 2 Previous Next 16 Replies Latest reply: Nov 29, 2012 10:51 AM by Max Seleznev Go to original post RSS
      • 15. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
        User393329
        Hi Max, I obtained the statisticts for each table with dbms_stats.gather_table_stats(ownname=x,tabname=z,cascade=true)

        I'm going to investigate more about Oracle's "secret" parameters.

        Thanks!
        • 16. Re: Oracle 11g with OPTIMIZER_MODE=RULE go faster!!
          Max Seleznev
          I'm glad you were able to achieve better performance with downgrading an optimizer behavior.

          I agree that it hurts to lose all the new features of the optimizer by doing it. Please look into different versions of plan stability to avoid setting optimizer parameter and still keep an optimal execution plan. SQL plan baselines would be the best approach in 11g.

          As an alternative you might want to try gathering stats with relatively high sample size and no histograms e.g.
          DBMS_STATS.GATHER_TABLE_STATS(
          ...
          estimate_percent => 30,                                         -- If it's not a terribly huge table the higher the better
          method_opt        => 'FOR ALL COLUMNS SIZE 1');      -- removes histograms if there were any
          It's recommended to make sure your old stats are preserved. Stats history is usually maintained automatically but there's an option to import them manually.

          Please be careful with "underscore" parameters. In general it's not advisable to set them unless recommended by Oracle Support.
          1 2 Previous Next