12 Replies Latest reply: Sep 30, 2013 7:09 PM by jgarry RSS

    Gather Stats taking time

    user7202581

      All,

       

      Below is a gather stats procedure we use to gather stats.Is there any modifications to the parameters that can be done to optimize this?

       

      CREATE OR REPLACE PROCEDURE PRESTG.SP_PRESTG_GATHER_TBL_STATS(schema_name VARCHAR2)

      AUTHID CURRENT_USER

      AS

      type typ_tbl is table of varchar2(30)

      index by pls_integer;

      tbl typ_tbl;

      idx typ_tbl;

      NUMBER(10):=0;

      BEGIN

      execute immediate 'SELECT table_name FROM prestg_tbl_xref' BULK COLLECT into tbl;

       

        n:=SQL%ROWCOUNT;

       

      dbms_output.put_line(n);

       

        IF n>0 THEN

      FOR i IN tbl.first..tbl.last

      LOOP

      EXECUTE immediate 'begin sys.dbms_stats.gather_table_stats(ownname=>'''||schema_name||''',tabname=>'''||tbl(i)||''',block_sample=>TRUE); END;';

      END LOOP;

       

        ELSE

      raise_application_error(-20001,'No Tables');

        END IF;

       

      execute immediate 'select index_name from all_indexes where owner='''|| schema_name||''' and table_name in

      (SELECT table_name FROM prestg_tbl_xref)' bulk collect into idx;

       

        n:=SQL%ROWCOUNT;

       

        IF n>0 THEN

      FOR i IN idx.first..idx.last

      LOOP

      EXECUTE immediate 'begin sys.dbms_stats.gather_index_stats(ownname=>'''||schema_name||''',indname=>'''||idx(i)||'''); END;';

      END LOOP;

       

        END IF;

       

      END SP_PRESTG_GATHER_TBL_STATS;

      /

       

      Regards,

      Narayan

        • 1. Re: Gather Stats taking time
          jgarry

          The most performance can be obtained by not doing something.  Why are you doing this, again?

          • 2. Re: Gather Stats taking time
            sb92075

            since V10 Oracle collects needed statistics once every 24 hours by default.

             

            please post SQL & results that show your posted code is really necessary.

            • 3. Re: Gather Stats taking time
              user7202581

              Gather stats is now auto scheduled.This procedure is called by Informatica after processing.

              Will making changes to any of the parameters help reduce the time for the gather stats collection?

               

              Regards,

              Narayan

              • 4. Re: Gather Stats taking time
                Alvaro

                What was the problem with the default job of statistics collection?

                 

                As per your code, you don't even make any type of filtering based on the staleness of the statistics. If the stats are not stale,it is unlikely you will get any changes on your execution plan by re-gathering stats on it.

                 

                Also, why not use CASCADE=>true instead of invoking dbms_stats on every single index ?

                 

                EDIT: You can use degree option for parallelism on stats collection and speeding up overall process, but it will consume more CPU and I/O

                • 5. Re: Gather Stats taking time
                  user7202581

                  Thanks for your inputs.

                  What difference does CASCADE=TRUE make over invoking dbms_stats?

                   

                  Thanks for your inputs on the parallelism option.

                  • 6. Re: Gather Stats taking time
                    jgarry

                    I think he is saying he wants to gather the stats after they are newly loaded by informatica.  That would likely be a reasonable thing to do.  It would also be time consuming.  The question should become, is there a set of stats that could be imported or reverted to quickly that would accurately reflect the data, or could there be some sampling or specific histogram collection to make the stats better, or good enough and quicker.

                    • 7. Re: Gather Stats taking time
                      user7202581

                      Yes basically these are the questions I have.Since this gather stats is time consuming we are looking at options which will make this process quicker.

                       

                      Regards,

                      Narayan

                      • 8. Re: Gather Stats taking time
                        jgarry

                        Once you get much beyond the default stats gathering, it becomes dependent on how you are querying the data, and what version you are on.  If you have a limited number of queries with a limited range of bind values sent in parameters, that could be different than a DSS system with all sorts of strange requests, which could be different still from an analytics system where full partition scans are the norm.  The version determines what options you have for stabilizing query plans.

                         

                        If you have correct plans being generated for lots of data which the default would be stale anyways, you might consider importing a standard set of stats for that data.  If you have consistently increasing index keys you might want to always generate it 100%.  It depends.

                        • 9. Re: Gather Stats taking time
                          SomeoneElse

                          > What difference does CASCADE=TRUE make over invoking dbms_stats?

                           

                          It will gather stats on the table *plus* all the indexes for that table.  One big advantage of cascade (I think) is that it can do all the work in parallel where your procedure will gather the indexes one at a time.  (and cascade is a lot easier to code)

                           

                          http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#i1036461

                          • 10. Re: Gather Stats taking time
                            user7202581

                            Thanks for your inputs.One last Q.How to find out if the Index keys are increasing?

                            • 11. Re: Gather Stats taking time
                              sb92075

                              user7202581 wrote:

                               

                              Thanks for your inputs.One last Q.How to find out if the Index keys are increasing?

                              when INDEX KEY is populated by a SEQUENCE

                              • 12. Re: Gather Stats taking time
                                jgarry

                                You need to know how your app works.  For example, if you are adding sales orders, they usually go upwards in number.  If you have multiple divisions concatenated to order numbers, each of those usually go up - but they might skew differently as different divisions have different order volumes.