Why Does Gather Schema Statistics Take So Long To Complete?
(Doc ID 1497109.1)
Last updated on FEBRUARY 22, 2023
Applies to:
Oracle E-Business Suite 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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |