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

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

631403 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    [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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks you, this was just what I needed!
  • 4. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
    631403 Newbie
    Currently Being Moderated
    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
    682237 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    682237 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Are you trying to execute as SYS?
  • 9. Re: Oracle 10g to Oracle 11g - GATHER_STATS_JOB no longer available
    682237 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    682237 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    682237 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Just did a quick search and most of the notes says, Yes. You have to run adstats.sql.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points