7 Replies Latest reply: Apr 30, 2012 3:10 AM by 848473 RSS

    update stats

    848473
      I have to create a script for update my stat's .currently i am using "analyze table user2.T1 COMPUTE statistics". and this will give me the result of only table t1

      but i want to result of all table whichever is available under U1.how i can create ?
        • 1. Re: update stats
          hitgon
          connect the username
          SELECT 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname =>'||CHR(39)||'CONTRACT'||CHR(39)||', tabname =>'||CHR(39)||TNAME||CHR(39)||', method_opt => ''FOR ALL COLUMNS'', granularity => ''ALL'', cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE);'
          FROM TAB;
          • 2. Re: update stats
            848473
            there is no result after execution of this query.
            • 3. Re: update stats
              hitgon
              Hello

              $sqlplus username/password
              SQL> SELECT 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname =>'||CHR(39)||'CONTRACT'||CHR(39)||', tabname =>'||CHR(39)||TNAME||CHR(39)||', method_opt => ''FOR ALL COLUMNS'', granularity => ''ALL'', cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE);'
              FROM TAB;

              Regards
              Hitgon

              Edited by: hitgon on Apr 30, 2012 1:07 PM
              • 4. Re: update stats
                848473
                After execution it gives the below result.


                'EXECUTEDBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'||CHR(39)||'CONTRACT'||CHR(39)||
                --------------------------------------------------------------------------------
                EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname =>'CONTRACT', tabname =>'T1', meth
                od_opt => 'FOR ALL COLUMNS', granularity => 'ALL', cascade => TRUE, degree => DB
                MS_STATS.DEFAULT_DEGREE);
                • 5. Re: update stats
                  Antonio Navarro
                  It is very easy using DB Control/Grid Control.

                  SERVER tab, under QUERY OPTIMIZER link, click on MANAGE OPTIMIZER STATISTICS

                  HTH
                  Antonio NAVARRO
                  • 6. Re: update stats
                    848473
                    I have to run it manually on windows command so later i can put in job scheduler to collect the stat for all table . is there any ways to collct the stats for all table?
                    • 7. Re: update stats
                      839439
                      845470 wrote:
                      I have to run it manually on windows command so later i can put in job scheduler to collect the stat for all table . is there any ways to collct the stats for all table?
                      Follow the suggestion provided by NIkolay i.e, use the OEM and take the SQL from OEM(i.e, from show sql tab) and put the batch mode and futher schedule them as per requirements .


                      --neeraj