Oracle E-Business Suite Release 12 uses cost–based optimization in order to choose the most efficient execution plan for SQL statements.Gather Schema Statistics is the favourite one among Apps DBA's and developers. Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.
On Oracle Applications 12.1.3,
Gather schema statistics (GSS) is completing with error in our instances.
When attempting to run Gather Schema Statistics request, the following error occurs:
Error
*******
**Starts**13-APR-2019 23:59:45
**Ends**14-APR-2019 05:30:40
ORA-0000: normal, successful completion
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 24 internal_flag= NOBACKUP
stats on table AQ$_WF_CONTROL_P is locked
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
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Successfully resubmitted concurrent program FNDGSCST with request ID 4981147 to start at 21-APR-2019 00:00:00 (ROUTINE=AFPSRS)
Solution
***********
1. Please follow Doc ID 375351.1 to unlock the tables:
AQ$_WF_CONTROL_P
FND_CP_GSM_IPC_AQTBL
FND_SOA_JMS_IN
FND_SOA_JMS_OUT
Do the following select from dba_tab_stats to show all tables with locked statistics on them:
SQL> select owner,
table_name, stattype_locked from dba_tab_statistics
where stattype_locked is not null
and owner not in ('SYS');
From DB node run below to unlock tables
SQL>exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');
SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');
SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_IN');
SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_OUT');
2. Please perform the following action plan from Doc ID 781813.1 :
Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.
SQL> create table FND_HISTOGRAM_COLS_BKP as select * from FND_HISTOGRAM_COLS;
-- Identify duplicate rows
SQL>select table_name, column_name, count(*) from
FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;
-- Use above results on the following SQL to delete duplicates
SQL>delete from FND_HISTOGRAM_COLS
where table_name =
'&TABLE_NAME'
and column_name =
'&COLUMN_NAME'
and rownum=1;
-- Use following SQL to delete obsoleted rows
SQL>delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name ='&TABLE_NAME'
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null
);
SQL>commit;
3. Run "Gather Schema Statistics" program again.
Fix worked successfully for our env.
