Thursday, May 10, 2012

How to execute Gather schema from backend

exec dbms_stats.gather_schema_stats('AP',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
To execute the corresponding FND_STATS procedures from SQL*Plus to gather CBO stats for one or all schemas, or for a particular table, use the following examples:
# sqlplus apps/<apps_pwd>
SQL> exec fnd_stats.gather_schema_statistics('MRP');      <- One schema
SQL> exec fnd_stats.gather_schema_statistics('ALL');      <- All schemas
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES'); <- One table
SQL> begin            <- Partitioned table
  2      fnd_stats.gather_table_stats(ownname     => 'APPLSYS',
  3                                   tabname     => 'WF_ITEM_ACTIVITY_STATUSES',
  4                                   granularity => 'PARTITION');
  5  end;
  6  /

No comments:

Post a Comment