1 2 Previous Next 15 Replies Latest reply: Jul 24, 2009 2:12 PM by Srini Chavali-Oracle RSS

    Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available

    631403
      Oracle 11.0.1.7:
      It looks like since Oracle 11g there is not GATHER_STATS_JOB being provided. Is there a similar job that we can schedule that is available? What should we do?

      Edited by: user628400 on Jun 10, 2009 8:52 AM
        • 1. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
          591186
          [http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28310/tasks006.htm#CIHJJDBB]

          [Automated Database Maintenance Task Management in Oracle Database|http://www.oracle-base.com/articles/11g/AutomatedDatabaseMaintenanceTaskManagement_11gR1.php]
          • 2. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
            Mike Dietrich-Oracle
            It's now called "Optimizer Statistics Gathering".
            According to Note:731935.1 you'll start it this way manually:

            exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

            Regards
            Mike
            • 3. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
              Emerson
              Thanks you, this was just what I needed!
              • 4. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                631403
                When I query dba_scheduler_job_run_details I see
                JOB_NAME     STATUS     
                MGMT_CONFIG_JOB_1     SUCCEEDED     
                MGMT_CONFIG_JOB     SUCCEEDED     
                MGMT_CONFIG_JOB     SUCCEEDED     
                MGMT_STATS_CONFIG_JOB     SUCCEEDED     
                MGMT_STATS_CONFIG_JOB_1     SUCCEEDED     
                MGMT_CONFIG_JOB_1     SUCCEEDED     
                Are these the jbos that collect stats? How do I know what kind of stats these are? It looks like there are various type of stats high, low, extended etc.

                For a busy OLTP application what kind of db stats is recommended?
                • 5. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                  Rerry
                  Sir,

                  I get error as below while running SQL> @adstats.sql script while performing Gather statistics for SYS schema to upgrade oracle 9i to 11g.As per you it called "Optimizer Statistics Gathering". Should I run adstats.sql script or not? If not what steps should I perform to Gather statistics for SYS schema?

                  ERROR at line 1:
                  ORA-27476: "SYS.GATHER_STATS_JOB" does not exist
                  ORA-06512: at "SYS.DBMS_ISCHED", line 3429
                  ORA-06512: at "SYS.DBMS_SCHEDULER", line 2395
                  ORA-06512: at line 2
                  ORA-06512: at line 23

                  Please throw light on this error to resolve it.



                  Thanks & Regards
                  Rerry
                  • 6. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                    591186
                    adstats.sql script
                    Post the script.
                    Check metalink note: DBMS_SCHEDULER fails with ORA-27476 ORA-27475 -787802.1
                    Received Error While Running Dbms_scheduled Auto Gatherstats Under External User-787798.1





                    HTH
                    Anantha
                    • 7. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                      Rerry
                      Hi,
                      Please find below adstats.sql script.

                      REM dbdrv: none
                      REM
                      REM +=========================================================================+
                      REM | Copyright (c) 2004 Oracle Corporation, Redwood Shores, California, USA
                      REM | All Rights Reserved
                      REM | Applications Division
                      REM +=========================================================================+
                      REM |
                      REM | $Header: adstats.sql 115.0.11586.2 2005/05/27 18:26:41 hxue noship $
                      REM |
                      REM |
                      REM | FILENAME
                      REM | adstats.sql
                      REM |
                      REM | DESCRIPTION
                      REM | SQL script that gathers stats in 10g database.
                      REM | This should be run immediately after the 10g upgrade.
                      REM | This SQL script must be run as SYS user, from the ORACLE_HOME
                      REM | on the Database Server.
                      REM |
                      REM | USAGE
                      REM |
                      REM | sqlplus '/ as sysdba' @adstats.sql
                      REM |
                      REM | HISTORY
                      REM | 04-25-2005 RAHKUMAR CREATED
                      REM +=========================================================================+
                      set verify off;
                      WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
                      WHENEVER OSERROR EXIT FAILURE ROLLBACK;
                      connect / as sysdba
                      set serveroutput on
                      select '--- adstats.sql started at '||
                      to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||' ---' " "
                      from dual;
                      prompt
                      prompt Checking for the DB version
                      prompt
                      declare
                      dbver number;
                      logins_mode varchar2(50);
                      begin
                      select
                      to_number(substr(version,1,instr(version,'.'))), logins
                      into dbver, logins_mode
                      from v$instance
                      where rownum=1;
                      if dbver < 10
                      then
                      dbms_output.put_line('This procedure can only run while connected');
                      dbms_output.put_line('to a 10g or later database. Procedure exiting.');
                      else
                      if (logins_mode <> 'RESTRICTED')
                      then
                      dbms_output.put_line('This procedure can only run while database has been opened ');
                      dbms_output.put_line('in restricted mode. Procedure exiting.');
                      else
                      execute immediate 'begin
                      dbms_scheduler.disable(''GATHER_STATS_JOB'');
                      dbms_stats.gather_schema_stats(''SYS'',degree=>30,estimate_percent=>100,cascade=>TRUE);
                      dbms_stats.gather_fixed_objects_stats;
                      end;';
                      end if;
                      end if;
                      end;
                      /
                      select '--- adstats.sql ended at '||
                      to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||' ---' " "
                      from dual;
                      commit;
                      exit;

                      As per note while running the script it's give below error.

                      SQL> GRANT ALTER ON GATHER_STATS_JOB TO OPT$ORACLE;
                      GRANT ALTER ON GATHER_STATS_JOB TO OPT$ORACLE
                      *
                      ERROR at line 1:
                      ORA-00942: table or view does not exist

                      Please give the solution as I stucked at end of 11g upgrade.

                      Thanks & Regards,
                      Rerry
                      • 8. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                        591186
                        Are you trying to execute as SYS?
                        • 9. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                          Rerry
                          Hi,

                          Yes I was connected as sysdba before running the script.

                          $ sqlplus "/ as sysdba"

                          Regards,
                          Rerry
                          • 10. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                            591186
                            Have you upgraded the Database to 11g? or Are you in the middle?

                            Are you using manual steps for upgrade to 11g?
                            If you're still in 10g and gathering stats before upgrade, you should be using the one mentioned below.

                            EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
                            Check Metalink note: Complete Checklist for Manual Upgrades to 11gR1 - 429825.1

                            Edited by: Anantha R on Jul 24, 2009 11:25 AM
                            • 11. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                              Rerry
                              Hi,

                              As per "Interoperability Notes Oracle E-Business Suite Release 11i with Oracle Database 11g Release 1 (11.1.0)", I have perform all steps and used DBUA to upgrade DB.Even front end is also working ok and show DB 11g.Is it compulsary to Gather statistics for SYS schema by runnig adstats.sql script? I have upgraded to 11g form 9i.

                              Regards,
                              Rerry
                              • 12. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                                591186
                                Is your database running in restricted mode?

                                I guess you've copied the adstat.sql from $APPLE_TOP to database server. Its better create an SR with Oracle support.
                                • 13. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                                  Rerry
                                  Hi,

                                  I have run adstats.sql script after database running in restricted mode.Yes I've copied the adstat.sql from $APPLE_TOP to database server.But it's necessary to run this script?

                                  Regards,
                                  Rerry
                                  • 14. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
                                    591186
                                    Just did a quick search and most of the notes says, Yes. You have to run adstats.sql.
                                    1 2 Previous Next