EBS 19c DB收集统计信息

在CDB执行
sqlplus / as sysdba
exec dbms_stats.gather_schema_stats(ownname=>'SYS',options=>'GATHER AUTO');
exec dbms_stats.gather_schema_stats(ownname=>'SYSTEM',options=>'GATHER AUTO');
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;


在PDB的system用户下运行
sqlplus system/manager@PROD
exec dbms_stats.gather_schema_stats(ownname=>'SYS',options=>'GATHER AUTO');
exec dbms_stats.gather_schema_stats(ownname=>'SYSTEM',options=>'GATHER AUTO');
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;

--exec dbms_stats.gather_database_stats(estimate_percent=>100,degree=>48,cascade=>true,granularity=>'ALL',no_invalidate=>false);
set timing on
exec dbms_stats.set_global_prefs('CONCURRENT','TRUE');
exec dbms_stats.gather_database_stats(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade=>true, degree=>48);
exec dbms_stats.set_global_prefs('CONCURRENT','FALSE');

在PDB的apps用户下运行
sqlplus apps/apps@PROD
exec fnd_stats.GATHER_SCHEMA_STATS(schemaname =>'ALL' ,estimate_percent =>'100' ,degree => '48',options =>'GATHER' );

sqlplus apps/apps@PROD
exec fnd_stats.gather_schema_statistics(schemaname=>'ALL',estimate_percent=>dbms_stats.auto_sample_size,degree => '48',options=>'GATHER');
alter system set parallel_max_servers=64 scope=memory sid='*';
alter system set parallel_servers_target=64 scope=memory sid='*';
set timing on;
exec fnd_stats.gather_schema_statistics(schemaname=>'ALL',estimate_percent=>dbms_stats.auto_sample_size,degree => '48',options=>'GATHER AUTO');

-----------------------------------------整库收集脚本
--!date
--conn / as sysdba;
--exec dbms_stats.delete_database_stats;

!date
conn apps/apps;
exec fnd_stats.gather_schema_statistics ('ALL',degree=>16,options=>'GATHER AUTO');
!date
conn / as sysdba;
exec DBMS_STATS.DELETE_DICTIONARY_STATS;
!date
exec DBMS_STATS.GATHER_DICTIONARY_STATS;
!date
EXEC dbms_stats.gather_fixed_objects_stats;
!date
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS', TABNAME => 'X$KTFBUE',ESTIMATE_PERCENT=>100,degree=>16);
!date
execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'NOWORKLOAD');
!date

exec fnd_stats.gather_schema_statistics('MRP');
exec fnd_stats.gather_schema_statistics('AFA',estimate_percent => 100,degree=>64);
exec fnd_stats.gather_schema_statistics('AFA',estimate_percent => 100,degree=>64);

exec fnd_stats.gather_table_stats('GL','GL_JE_SEGMENT_VALUES',100,degree=>64);
exec fnd_stats.gather_table_stats('GL','GL_TEMPORARY_COMBINATIONS',100,degree=>64);
exec fnd_stats.gather_table_stats('GL','GL_INTERFACE',100,degree=>64);
exec fnd_stats.gather_table_stats('GL','GL_JE_HEADERS',100,degree=>64);
exec fnd_stats.gather_table_stats('GL','GL_BALANCES',100,degree=>64);
exec fnd_stats.gather_table_stats('GL','GL_JE_LINES',100,degree=>64);
exec fnd_stats.gather_table_stats('GL','GL_IMPORT_REFERENCES',100,degree=>64);

exec fnd_stats.gather_table_stats('APPLSYS','FND_CONCURRENT_PROCESSES',100,degree=>64);