5 Replies Latest reply on Mar 23, 2017 3:22 PM by Prashant Umap

    Gather Schema Statistics Running Since10days

    Ramaraju

      Hi All,

      We have EBS R12.2.4 instance. Gather Schema Statistics is running sinc 10 days.

      Could you please help on this issue.

       

      Thanks,

      Ramaraju

        • 1. Re: Gather Schema Statistics Running Since10days
          mdtaylor

          That seems pretty long. 

           

          How large is your database?

          How many processors do you have on the database server? 

          What is the speed/class of your processors (E5-2609, E7-8890) etc?

          What is your OS?

          Is this running in the cloud?

          How many parallel_max_servers do you have in init.ora?

          What is the speed of your storage?

          Are there any waiters or blockers (select * from dba_waiters, select * from dba_blockers)?

          Have you run bde_chk_cbo.sql lately?

           

          bde_chk_cbo.sql - EBS initialization parameters - Healthcheck (Doc ID 174605.1)

          • 2. Re: Gather Schema Statistics Running Since10days
            Venkat.S

            Is it first time running?

            Is there any changes on your system like upgrade etc?

             

            if it first time, you may have to follow the information provided by mdtaylor.

             

            if it is not first time and no changes on your system, I would suggest below.

             

            May be your GSS ran into stale mode, please gather the sql and hash value information, if nothing is running , I would say clear all blocking sessions (if any on GSS related tables) , clear any INACTIVE sessions and try again.

             

            if nothing helps, ultimately try with bouncing the database.

             

            Thanks,

            Venkat

            • 3. Re: Gather Schema Statistics Running Since10days
              John_K

              Is it actually running or is it just the concurrent request that says it is running? I.e. have you checked that the process is actually running on the database? On rare occasions when a process terminates abnormally the concurrent manager doesn't pick up that fact and it looks like it's still running.

              • 4. Re: Gather Schema Statistics Running Since10days
                mdtaylor

                In Oracle Applications Manager, click on Running Requests, then AUDSID, then SQL Hash to see which object GSS is hanging on.  Review the following documents.

                 

                Gather Schema Statistics FNDGSCST Concurrent Request Runs Very Slow Suddenly (Doc ID 1616469.1)

                Gather Schema Statistics Failed Due To Locks (Doc ID 2168100.1)

                Why Does Gather Schema Statistics Take So Long To Complete? (Doc ID 1497109.1)

                Bug 9852027 : GATHER SCHEMA STATISTICS FOR HRI SCHEMA IS NEVER ENDING

                 

                I had to export and import HRI_ADM_MTHD_RANGES once to get GSS To complete in an instance.

                • 5. Re: Gather Schema Statistics Running Since10days
                  Prashant Umap

                  Hello Rama,

                  Thanks for the post.

                   

                  Could you please confirm the following -

                  - Are you running the GSS for the first time ?

                  - What are the parameters you are using ?

                  - If its possible - can you bounce the db and run the GSS ?

                  Please check the following -

                  Should dbms_stats.gather_system_stats be used with E-Business 11i or R12? Note - Doc ID 368252.1

                  For E-Business Suite, for non-Exadata databases, it is recommend to gather system statistics with NOWORKLOAD option, as in:

                  execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'NOWORKLOAD');

                   

                  ORA-20005 WHEN GATHERING STATISTICS FOR APPS 11i / R12 IN 10G or 11G [ID 375351.1]

                   

                  Regards

                  Prashant Umap