3 Replies Latest reply on Feb 23, 2013 4:28 AM by marksmithusa

    Is this how you gather stats ?

    Y.Ramlet
      Version: 11.2.0.3/AIX 6.1

      Question1.
      Isn't this the old way to gather stats ?
      analyze table purchase_orders compute statistics;
      Question2.
      Is the below method of gathering stats fine for Partitioned and Non-partitioned tables?
      begin
      dbms_stats.gather_table_stats(OWNNAME => 'SCOTT', TABNAME=>'PURCHASE_ORDERS', estimate_percent=>100,  no_invalidate=>false);
      end;
      /
      Question3.

      If DBMS_STATS is recommended way to gather stats , Any idea what extra benefits DBMS_STATS has over ANALYZE command ?
        • 1. Re: Is this how you gather stats ?
          Nicolas.Gasparotto
          Y.Ramlet wrote:
          Version: 11.2.0.3/AIX 6.1

          Question1.
          Isn't this the old way to gather stats ?
          analyze table purchase_orders compute statistics;
          No, it has became obsolete for a couple of years and version. Use DBMS_STATS instead.
          Question2.
          Is the below method of gathering stats fine for Partitioned and Non-partitioned tables?
          begin
          dbms_stats.gather_table_stats(OWNNAME => 'SCOTT', TABNAME=>'PURCHASE_ORDERS', estimate_percent=>100,  no_invalidate=>false);
          end;
          /
          The estimate-percent is up to you, it could be interesting in some cases to use lower value than 100.
          Question3.

          If DBMS_STATS is recommended way to gather stats , Any idea what extra benefits DBMS_STATS has over ANALYZE command ?
          It's the newer version, algorithm is different.

          Nicolas.
          1 person found this helpful
          • 2. Re: Is this how you gather stats ?
            Aman....
            Y.Ramlet wrote:
            Version: 11.2.0.3/AIX 6.1

            Question1.
            Isn't this the old way to gather stats ?
            analyze table purchase_orders compute statistics;
            Yes it is and you shouldn't use it anymore.
            Question2.
            Is the below method of gathering stats fine for Partitioned and Non-partitioned tables?
            begin
            dbms_stats.gather_table_stats(OWNNAME => 'SCOTT', TABNAME=>'PURCHASE_ORDERS', estimate_percent=>100,  no_invalidate=>false);
            end;
            Yes.
            Question3.
            
            If DBMS_STATS is recommended way to gather stats , Any idea what extra benefits DBMS_STATS has over ANALYZE command ?
            If I remember correctly , Jonathan Lewis mentioned that couple of statistics don't get refreshed with the Analyze command, for example DEL_LF_ROWS etc. Other than that, more options are there with the newer method , as Nicolas mentioned.

            HTH
            Aman....
            1 person found this helpful
            • 3. Re: Is this how you gather stats ?
              marksmithusa
              Stats will be gathered if you allow the automated maintenance tasks to run. One of them is stats gathering whereby the database will gather stats using the default values during the default maintenance window.