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
toTRUE
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%';
- On the shards run the
following.
Automatic Statistics Gathering
-
Set
COORDINATOR_TRIGGER_SHARD
toTRUE
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; /
Parent topic: Query and DML Execution