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

    Gather Schema statistics Concurrent request....

    User332967
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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.