This discussion is archived
13 Replies Latest reply: Mar 31, 2013 9:29 PM by Hussein Sawwan-Oracle Branched to a new discussion. RSS

Gather Schema statistics Concurrent request....

User332967 Newbie
Currently Being Moderated
Hi There

Gather Schema Statistics ('ALL') concurrent request taking long running time even not completed after 8 hours, Pleases help me out in this regard how to collect statistics for all schemas in Oracle Ebs environment.

Application: Oracle Ebs R12(12.1.2)
DB: 11.1.0.7
OS: Linux5*86X64

looking forward.

Regards,
Mohsin
  • 1. Re: Gather Schema statistics Concurrent request....
    SurinderSingh Newbie
    Currently Being Moderated
    Hi,

    Please run Gather Schema Statistics Schema Wise or Product Top. The estimate percent can be set between 35% to 55% depending upon your ERP database.

    See the following examples


    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'AP',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'AR',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'OM',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'FA',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    Regards
    Surinder Singh
  • 2. Re: Gather Schema statistics Concurrent request....
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl elaborate on what the other parameters being passed to this concurrent program are. Are you using the GATHER AUTO option ? Is this program being run in off-peak hours ? Pl see these MOS Docs

    Why Does Gather Schema Statistics Take So Long To Complete? [ID 1497109.1]
    Concurrent Processing - How To Gather Statistics On Oracle Applications Release 11i and/or Release 12 - Concurrent Process,Temp Tables, Manually [ID 419728.1]
    Tuning performance on eBusiness suite [ID 744143.1]

    HTH
    Srini
  • 3. Re: Gather Schema statistics Concurrent request....
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Surinder Singh wrote:
    Hi,

    Please run Gather Schema Statistics Schema Wise or Product Top. The estimate percent can be set between 35% to 55% depending upon your ERP database.

    See the following examples


    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'AP',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'AR',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'OM',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'FA',ESTIMATE_PERCENT =>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    Regards
    Surinder Singh
    DBMS_STATS should not be used in an EBS environment - instead, use FND_STATS.

    How Often Should Gather Schema Statistics Program be Run? [ID 168136.1]
    Concurrent Processing - How To Gather Statistics On Oracle Applications Release 11i and/or Release 12 - Concurrent Process,Temp Tables, Manually [ID 419728.1]     To BottomTo Bottom     

    HTH
    Srini
  • 4. Re: Gather Schema statistics Concurrent request....
    User332967 Newbie
    Currently Being Moderated
    Hi Surinder

    Thanks for reply. I think DBMS_STATS is not good in Ebs environment and have use fnd_stats rather then dbms_stats but i am gathering statistics through ebs concurrent program " Gather Schema statistics " and it taking long time as i mentioned early.

    Regards,
    Mohsin
  • 5. Re: Gather Schema statistics Concurrent request....
    User332967 Newbie
    Currently Being Moderated
    Hi Srini

    Thanks a lot for providing helpful information. The program is scheduled in peak off timing but i am not using gather auto. please find below concurrent program parameters.

    Gather Schema Statistics - ALL, 10, , NOBACKUP, , LASTRUN, GATHER , , Y

    Need your advice regarding gather auto.

    Regards,
    Mohsin

    Edited by: 920138 on Mar 29, 2013 10:19 PM
  • 6. Re: Gather Schema statistics Concurrent request....
    SurinderSingh Newbie
    Currently Being Moderated
    Hi,

    You can use on Command Prompt when connected as Apps user.The estimate percent can be set between 35% to 55% depending upon your schema size.

    exec fnd_stats.gather_schema_stats(SCHEMANAME=>'NAME OF YOUR SCHEMA');

    Regards
    Surinder Singh

    Edited by: Surinder Singh on Mar 30, 2013 3:24 PM
  • 7. Re: Gather Schema statistics Concurrent request....
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl see the explanation in these MOS Docs - GATHER_AUTO will only gather stats for those tables where the number of rows exceeds a threshold defined by another parameter in the same code

    Tuning performance on eBusiness suite [ID 744143.1]
    Why Does Gather Schema Statistics Take So Long To Complete? [ID 1497109.1]

    HTH
    Srini
  • 8. Re: Gather Schema statistics Concurrent request....
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    920138 wrote:
    Hi There

    Gather Schema Statistics ('ALL') concurrent request taking long running time even not completed after 8 hours, Pleases help me out in this regard how to collect statistics for all schemas in Oracle Ebs environment.

    Application: Oracle Ebs R12(12.1.2)
    DB: 11.1.0.7
    OS: Linux5*86X64

    looking forward.

    Regards,
    Mohsin
    How long does it take normally to run "Gather Schema Statistics" in your instance? It is normal for this concurrent program to run for that long if you do not have enough resources or if there are contention of the resources.

    Thanks,
    Hussein
  • 9. Re: Gather Schema statistics Concurrent request....
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Thanks a lot for providing helpful information. The program is scheduled in peak off timing but i am not using gather auto. please find below concurrent program parameters.

    Gather Schema Statistics - ALL, 10, , NOBACKUP, , LASTRUN, GATHER , , Y

    Need your advice regarding gather auto.
    How long does it take for a single schema? Does the program complete successfully then?

    Thanks,
    Hussein
  • 10. Re: Gather Schema statistics Concurrent request....
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Gather Schema Statistics - ALL, 10, , NOBACKUP, , LASTRUN, GATHER , , Y
    These are the default parameters and it should work with no issues.
    Need your advice regarding gather auto.
    EBPERF FAQ - Collecting Statistics in Oracle EBS 11i and R12 [ID 368252.1]

    Thanks,
    Hussein
  • 11. Re: Gather Schema statistics Concurrent request....
    User332967 Newbie
    Currently Being Moderated
    Hi Srini

    Thanks a lot for your support issue has been resolved after runing below command.

    exec dbms_stats.gather_schema_stats('APPS',cascade=>true,estimate_percent=>5);

    Regards,
    Mohsin
  • 12. Re: Gather Schema statistics Concurrent request....
    User332967 Newbie
    Currently Being Moderated
    Hi Hussein

    Thanks for reply. Actully there was some statistics locked on some table,so issue is resolved after runing below commands.

    select distinct owner, table_name, stattype_locked
    from dba_tab_statistics where stattype_locked is not null;

    exec dbms_stats.unlock_schema_stats('SYS')

    exec dbms_stats.unlock_table_stats('SYS','WRH$_SYSSTAT');

    Regards,
    Mohsin
  • 13. Re: Gather Schema statistics Concurrent request....
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    920138 wrote:
    Hi Hussein

    Thanks for reply. Actully there was some statistics locked on some table,so issue is resolved after runing below commands.

    select distinct owner, table_name, stattype_locked
    from dba_tab_statistics where stattype_locked is not null;

    exec dbms_stats.unlock_schema_stats('SYS')

    exec dbms_stats.unlock_table_stats('SYS','WRH$_SYSSTAT');

    Regards,
    Mohsin
    Thanks for the update and for sharing the solution.

Legend

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