Gathering Optimizer Statistics on Sharded Tables

You can gather statistics on sharded tables from the coordinator database.

The statistic preference parameter COORDINATOR_TRIGGER_SHARD, when set to TRUE on all of the shards, allows the coordinator database to import the statistics gathered on the shards.

The PL/SQL procedures DBMS_STATS.GATHER_SCHEMA_STATS() and DBMS_STATS.GATHER_TABLE_STATS() gather statistics on sharded tables and duplicated tables in the shards and in the coordinator database. See also, REPORT_GATHER_TABLE_STATS Function.

Manual Statistics Gathering

Set COORDINATOR_TRIGGER_SHARD to TRUE on all of the shards.

This step is performed only one time and only on the shards. If, for example, you have a schema named sharduser:
```
connect / as sysdba
EXECUTE DBMS_STATS.SET_SCHEMA_PREFS('SHARDUSER','COORDINATOR_TRIGGER_SHARD','TRUE');
```

Gather statistics across the shards.

The user should be an all-shards user and needs to have privileges to access dictionary tables.

On the shards run the following.

connect sharduser/password
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SHARDUSER', options => 'GATHER');

When all shards are completed, to pull aggregated statistics run the following on the coordinator.
```
connect sharduser/password
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SHARDUSER', options => 'GATHER');
```

Check the statistics on all of the shards.

connect sharduser/password

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
  col TABLE_NAME form a40
  set pagesize 200 linesize 200

SELECT TABLE_NAME, NUM_ROWS, sharded, duplicated, last_analyzed
  FROM user_tables
  WHERE table_name not like 'MLOG%' and table_name not like 'RUPD%'
  and table_name not like 'USLOG%';

Automatic Statistics Gathering

Set COORDINATOR_TRIGGER_SHARD to TRUE on all of the shards.

This step is performed only one time and only on the shards. If, for example, you have a schema named sharduser:
```
connect / as sysdba
EXECUTE DBMS_STATS.SET_SCHEMA_PREFS('SHARDUSER','COORDINATOR_TRIGGER_SHARD','TRUE');
```

Schedule a job to pull aggregated statistics on the shards and on the coordinator database.

The user should be an all-shards user and must have privileges to access dictionary tables.

Start the following job on the shards:

connect sharduser/password
BEGIN 
DBMS_SCHEDULER.CREATE_JOB ( 
   job_name => 'Gather_Stats_2', 
   job_type => 'PLSQL_BLOCK',
   job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ''DEMO'', options => ''GATHER''); END;',
   start_date => SYSDATE,
   repeat_interval => 'freq=daily;byday=MON,TUE,WED,THU,FRI,SAT,SUN;byhour=14;byminute=10;bysecond=00',
   end_date => NULL,
   enabled => TRUE,
   comments => 'Gather table statistics');
END; 
/

After the job on all of the shards is finished, start the following job on the coordinator.

connect sharduser/password
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'Gather_Stats_2',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ''DEMO'', options => ''GATHER''); END;',
   start_date           =>  SYSDATE,
   repeat_interval      => 'freq=daily;byday=MON,TUE,WED,THU,FRI,SAT,SUN;byhour=15;byminute=10;bysecond=00', 
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments             => 'Gather table statistics');
END;
/