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.

Reason:  

Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie  FND_ORACLE_USERID  and  FND_APPLICATIONS_TL
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.

33 comments:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +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

Rithi Rawat said...

Outstanding blog thanks for sharing such wonderful blog with us ,after long time came across such knowlegeble blog. keep sharing such informative blog with us.

machine learning training in chennai
best training insitute for machine learning
machine learning course in Chennai

VRITPROFESSIONALS said...

Nice post. Thanks for sharing! I want people to know just how good this information is in your blog. It’s interesting content and Great work.

Thanks & Regards,
VRIT Professionals,
No.1 Leading Web Designing Training Institute In Chennai.

And also those who are looking for
Web Designing courses training institutes in Chennai
HTML courses training institutes in Chennai
CSS courses training institutes in Chennai
Bootstrap courses training institutes in Chennai
Photoshop courses training institutes in Chennai
PHP & Mysql courses training institutes in Chennai
SEO courses training institutes in Chennai
Testing courses training institutes in Chennai

Rithi Rawat said...

Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.
machine learning with python course in chennai
machine learning course in chennai
best training insitute for machine learning
Android training in velachery
PMP training in chennai

Sadhana Rathore said...

Awesome post with lots of data and I have bookmarked this page for my reference. Share more ideas frequently.
AngularJS Training in Chennai
AngularJS course in Chennai
ReactJS Training in Chennai
R Programming Training in Chennai
Data Science Training in Chennai
Data Science course in Chennai
AngularJS Training in OMR
AngularJS Training in Porur

Robotic Process Automation Tutorial said...

Really useful information. Thank you so much for sharing.It will help everyone.Keep Post. RPA training in chennai | RPA Uipath training in chennai | RPA training in Chennai with placement

Robotic Process Automation Tutorial said...

Really useful information. Thank you so much for sharing.It will help everyone.Keep Post. RPA training in chennai | RPA Uipath training in chennai | RPA training in Chennai with placement

Roja Priya said...

Thank you for sharing your article. Great efforts put it to find the list of articles which is very useful to know, Definitely will share the same to other forums.

best openstack training in chennai | openstack course fees in chennai | openstack certification in chennai | openstack training in chennai velachery

Roja Priya said...

Thank you for sharing your article. Great efforts put it to find the list of articles which is very useful to know, Definitely will share the same to other forums.

https://www.credosystemz.com/courses/openstack-training-chennai/

IT Tutorials said...

Really useful information. Thank you so much for sharing.It will help everyone.Keep Post. RPA training in chennai | RPA training in Chennai with placement | UiPath training in Chennai | UiPath Chennai

suresh said...

Very impressive thanks for sharing

DevOps Training in Chennai

Cloud Computing Training in Chennai

IT Software Training in Chennai


siva said...

Great Article… I love to read your articles because your writing style is too good,
its is very very helpful for all of us and I never get bored while reading your article because,
they are becomes a more and more interesting from the starting lines until the end.
data science online training
python online training
uipath online training
data science with python online training
rpa online training

sathish said...

Well done! Pleasant post! This truly helps me to discover the solutions for my inquiry. Trusting, that you will keep posting articles having heaps of valuable data. You're the best! 
angularjs online training

apache spark online training

informatica mdm online training

devops online training

aws online training

rose said...

We are a group of volunteers and starting a new initiative in a community. Your blog provided us valuable information to work on.You have done a marvellous job!

Microsoft Azure online training
Selenium online training
Java online training
Java Script online training
Share Point online training


anusha said...


data science training in chennai Data Science Training in Chennai with real time projects. We are Best Data Science Training Institute in Chennai. Our Data Science training courses are taught by Experts.

IT Tutorials said...


Get the most advanced RPA Course by RPA Professional expert. Just attend a FREE Demo session about how the RPA Tools get work.
For further details call us @ 9884412301 | 9600112302
RPA training in chennai | UiPath training in chennai

creative web solution said...

Are you looking for a maid for your home to care your baby,patient care taker, cook service or a japa maid for your pregnent wife we are allso providing maid to take care of your old parents.we are the best and cheapest service provider in delhi for more info visit our site and get all info.

maid service provider in South Delhi
maid service provider in Dwarka
maid service provider in Gurgaon
maid service provider in Paschim Vihar
cook service provider in Paschim Vihar
cook service provider in Dwarka
cook service provider in south Delhi
baby care service provider in Delhi NCR
baby care service provider in Gurgaon
baby care service provider in Dwarka
baby service provider in south Delhi
servant service provider in Delhi NCR
servant service provider in Paschim Vihar
servant Service provider in South Delhi
japa maid service in Paschim Vihar
japa maid service in Delhi NCR
japa maid service in Dwarka
japa maid service in south Delhi
patient care service in Paschim Vihar
patient care service in Delhi NCR
patient care service in Dwarka
Patient care service in south Delhi

creative web solution said...

such a nice post thanks for sharing this with us really so impressible and attractive post
are you searching for a caterers service provider in Delhi or near you then contact us and get all info and also get best offers and off on pre booking

caterers services sector 29 gurgaon
caterers services in west Delhi
event organizers rajouri garden
wedding planners in Punjabi bagh
party organizers in west Delhi
party organizers Dlf -phase-1
wedding planners Dlf phase-1
wedding planners Dlf phase-2
event organizers  Dlf phase-3
caterers services Dlf phase-4
caterers services Dlf phase-5

creative web solution said...

Alleyaaircool is the one of the best home appliances repair canter in all over Delhi we deals in repairing window ac, Split ac , fridge , microwave, washing machine, water cooler, RO and more other home appliances in cheap rates

Window AC Repair in vaishali
Split AC Repair in indirapuram
Fridge Repair in kaushambi
Microwave Repair in patparganj
Washing Machine Repair in vasundhara
Water Cooler Repair in indirapuram
RO Service AMC in vasundhara
Any Cooling System in vaishali
Window AC Repair in indirapuram

creative web solution said...

Totalsolution is the one of the best home appliances repair canter in all over Delhi we deals in repairing window ac, Split ac , fridge , microwave, washing machine, water cooler, RO and more other home appliances in cheap rates

LCD, LED Repair in Janakpuri
LCD, LED Repair in Dwarka
LCD, LED Repair in Vikaspuri
LCD, LED Repair in Uttam Nagar
LCD, LED Repair in Paschim Vihar
LCD, LED Repair in Rohini
LCD, LED Repair in Punjabi Bagh
LCD, LED Repair in Delhi. & Delhi NCR
LCD, LED Repair in Delhi. & Delhi NCR
Washing Machine repair on your doorstep
Microwave repair on your doorstep

Chiến SEOCAM said...

Un article intéressant et intéressant. Merci de partager

lều xông hơi mini

mua lều xông hơi ở đâu

lều xông hơi gia đình

bán lều xông hơi

xông hơi hồng ngoại