DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Friday, February 17, 2012

Gather Schema Statistics - Oracle Applications

Gather  Schema  Statistics :
Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

The cost-based optimization (CBO)  uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.

As a general rule, run Gather Schema Statistics under the following circumstances:

1.  After there has been a significant change in data in either content or volume.

2.  After importing data.

3.  Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.

4.  Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.

Estimate Percentage / Modification threshold defines the percentage which should be used to initiate gather stats for those objects which have actually changed beyond the threshold.
 The default is 10% (i.e. meaning any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).

How to run Gather Schema Statistics concurrent program:

1. Log on to Oracle Applications with
    Responsibility = System Administrator

2. Submit Request Window
    Navigate to: Concurrent > Requests

3. Query for the Gather Schema Statistics

4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering  ‘ALL’  to gather statistics for every schema in the database

5. Submit the Gather Schema Statistics program

Parameters :
Schema Name:  Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas

Percent:  The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100

Degree:  The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum ofparallel_max_servers and cpu_count.

Backup Flag:  NOBACKUP is used, then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.

Restart Request ID:  In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.
History Mode:  Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior

Gather Options:  GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default

Modifications Threshold:  Applicable only to GATHER AUTO and LIST AUTO Options

Invalidate Dependent Cursors:  This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated.

How to Gather the Statistics of Custom Schema when we submit the concurrent request called Gather Schema Statistics :

When we submit Gather Schema Stats with Parameter  ALL, concurrent request will complete successfully, and DBAs will not realize that custom schemas are not analyzed.

Sql > select count(table_name)  from  dba_tables  where  last_analyzed  is  not null  and  owner= <custom_schema_name>;

Here you realize none of the tables in custom schema are analyzed.

Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.


Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
          from fnd_oracle_userid a,
         fnd_product_installations b
         where a.oracle_id = b.oracle_id
         order by sname;
Note : When custom schemas are created the entry is not made in  FND_PRODUCT_INSTALLATIONS  and hence it is not picked up in the above query.
Solution :
How can we make an entry in fnd_product_installations so that it is picked up by Gather Schema Stats. Follow below steps
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.


Sridevi K said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.

amilu B said...

Good piece of work ,enjoyed reading it. The precision given here is good. Hoping a lot from you. Thankyou for sharing.

python training in chennai

python training in bangalore

python online training

python training in pune

simbu said...

Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.
java training in chennai

java training in bangalore

java online training

java training in pune

shalinipriya said...

Thanks a lot for sharing us about this update. Hope you will not get tired on making posts as informative as this. 
Data Science Training in Chennai
Data science training in bangalore
Data science online training
Data science training in pune
Data science training in kalyan nagar

digi mark said...

Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 

rpa training in Chennai

rpa training in Chennai

rpa training in Chennai

rpa training in velachery

rpa training in tambaram

rpa training in sholinganallur

rpa training in anna nagar

rpa online training

john jersy said...

Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
python training in rajajinagar
Python training in btm
Python training in usa

gkr ragini said...

This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 
DevOps online Training
DevOps Training in USA

Revathy A said...

I have been meaning to write something like this on my website and you have given me an idea. Cheers.

angularjs Training in chennai
angularjs Training in chennai

angularjs-Training in tambaram

angularjs-Training in sholinganallur

angularjs-Training in velachery

param jothi said...

Really you have done great job,There are may person searching about that now they will find enough resources by your post
Blueprism training in Chennai

Blueprism training in Bangalore

thulasi ragini said...

Does your blog have a contact page? I’m having problems locating it but, I’d like to shoot you an email. I’ve got some recommendations for your blog you might be interested in hearing.
AWS Training in Chennai |Best Amazon Web Services Training in Chennai
AWS Training in Rajaji Nagar | Amazon Web Services Training in Rajaji Nagar

Best AWS Training Institute in BTM Layout Bangalore ,AWS Coursesin BTM

Ananya Krishnan said...

Good job in presenting the correct content with the clear explanation. The content looks real with valid information. Good Work

DevOps is currently a popular model currently organizations all over the world moving towards to it. Your post gave a clear idea about knowing the DevOps model and its importance.

Good to learn about DevOps at this time.

devops training in chennai | devops training in chennai with placement | devops training in chennai omr | devops training in velachery | devops training in chennai tambaram | devops institutes in chennai | devops certification in chennai | trending technologies list 2018

gowthunan said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
iosh course in chennai

Anand said...

Nice Stuff!

Java Training in Chennai
Python Training in Chennai
IOT Training in Chennai
Selenium Training in Chennai
Data Science Training in Chennai
FSD Training in Chennai
MEAN Stack Training in Chennai