SQL> ALTER SYSTEM SET statistics_level=all;
SQL> grant create session to sharding_advisor_user;
SQL> grant alter session to sharding_advisor_user;
SQL> grant select on v_$sql_plan to sharding_advisor_user;
SQL> grant select on v_$sql_plan_statistics_all to sharding_advisor_user;
SQL> grant select on gv_$sql_plan to sharding_advisor_user;
SQL> grant select on gv_$sql_plan_statistics_all to sharding_advisor_user;
SQL> grant select on DBA_HIST_SQLSTAT to sharding_advisor_user;
SQL> grant select on dba_hist_sql_plan to sharding_advisor_user;
SQL> grant select on dba_hist_snapshot to sharding_advisor_user;

The Sharding Advisor command-line utility, GWSADV, runs from $ORACLE_HOME/bin.

Run the Sharding Advisor from the command line, as shown here.

$ gwsadv -u username -p password -c –w sch=\(schema1,schema2\)

Where -u and -p are the user name and password of the user that runs the Sharding Advisor.

Use the capture workload parameter, -c, the first time you run Sharding Advisor against an existing query workload, to capture the predicate information from the source's GV$SQL_PLAN_STATISTICS_ALL view. You don't need to use -c in subsequent queries on the same workload.

The required -w flag indicates that Sharding Advisor uses the query workload for sharding configuration generation and ranking.

In this case, the sch parameter specifies a list of schemas to run Sharding Advisor against. There are several other options you can use with Sharding Advisor, detailed in Sharding Advisor Usage and Options.