Gather Schema Statistics in Oracle EBS

https://dbissues.blogspot.com/2018/06/gather-schema-statistics.html

Introduction: When the data is updated continuously by updating, inserting or deleting of by users (Functional/Technical/End users), it becomes necessary to gather statistics as the performance of database goes down. Cost-Based Optimizer (CBO) - The CBO uses database statistics to generate several execution plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation. Oracle E-Business Suite provides a set of procedures in the FND_STATS package to facilitate collection of these statistics. FND_STATS uses the DBMS_STATS package to gather statistics.

Run Gather Scheme From Back End:

Use the following command to gather schema statistics:
exec fnd_stats.gather_schema_statistics(‘ONT’) < For a specific schema >
exec fnd_stats.gather_schema_statistics(‘ALL’) < For all schemas >

How to run Gather Schema Statistics in ALL Schema's:

1: Run Single Request .




2: Type Gather Schema with percentage as below and press TAB.




3: The following image appears.




4: Fill the form like below.




5: Press OK and then press Submit.




6: Gather Schema is in Process.

Parameters Introduction:

Schema Name: In this parameter you have specify schema name Or All For all schema name.

Estimate Percent: Not Allowed in 11g oracle works here automatically.

Degree: Cpu x cores

Backup Flag: Backup previous last run gather stats.

Restart Request ID: Submit request id in case of error restart through request after when issue was resolved.

History Mode: Save history of last run gather stats.

Gather Options: Auto Gather which run only on incremental changes.

Modification 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).

Invalidate Dependent Cursor:

===========Error:===========

**Starts**03-JAN-2018 22:03:26

ORACLE error 20005 in FDPSTP

Cause: FDPSTP failed due to ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "APPS.FND_STATS", line 780

ORA-06512: at line 1

The SQL statement being executed at the time of the error was: and was exe

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 2 internal_flag= NOBACKUP

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

ORA-20005: object statistics are locked (stattype = ALL)

+---------------------------------------------------------------------------+

Query to Check Locked Schema Objects:

select table_name, stattype_locked from dba_tab_statistics where owner = '<schema>' and stattype_locked is not null;

Query to Check All Locked Schema Objects:

select table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Solution:

Unlock:

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER,' FND_CP_GSM_IPC_AQTBL');

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER,'FND_SOA_JMS_IN');

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER,'FND_SOA_JMS_OUT');

Run Gather Schema:

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'FND_CP_GSM_IPC_AQTBL');

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'FND_SOA_JMS_IN');

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'FND_SOA_JMS_OUT'); 

To generate unlock statement for all tables in the schema you can use following,
select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name ||”’);’ from dba_tab_statistics where owner = ‘<schema>” and stattype_locked is not null;