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

Last updated on JULY 28, 2016

Applies to:

Oracle EBS Applications Performance - Version 11.0.1 to 12.2 [Release 11 to 12.2]
Information in this document applies to any platform.

Goal

One executes gather stats every Friday on the production database using the gather schema statistics (GSS).  The strategy is to gather stats for all schema with estimate percent 10 one week end and gather stats for a specific schema with an estimate percent 30 the other weekend.


Observations:

1. Regarding gather stats for all schema: Gather stats for all schema does not complete till Monday morning and needs to be terminated. However on test instance it complete within 24hrs. Wait event observed "Library cache lock". We have 8GB shared pool.


2. Regarding gather stats for specific schema: Gather stats for INV schema (171GB) only sometime complete within 12hrs, but some times keeps running till monday morning and needs to be terminated. Wait event "PX Deq Credit: need buffer".

Kindly suggest following:

1. What could be the possible causes due to which gather stats runs for longer time than normal.


2. What ideal estimate percent needs to be set
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms