The Sharding Advisor is a client-side, command-line tool that you run against any non-sharded, production, 10g or later release, Oracle Database that you are considering migrating to an Oracle Sharding environment.

The Sharding Advisor analysis provides you with the information you need to design a schema that maximizes performance while reducing duplicated data in the new sharded database environment.

The following are benefits of using Sharding Advisor to aid you with schema design.

• Maximize query workload performance
• Minimize multi-shard operations requiring cross-shard joins
• Maximize parallelism for complex queries (spread query execution across all shards)
• Minimize the amount of duplicated data on each shard

The Sharding Advisor utility, GWSADV, is installed with Oracle Database as a standalone tool, and connects to your database using authenticated OCI connections.

To get an understanding of your schema and other preferences, Sharding Advisor asks you questions as part of an interactive dialog.

Sharding Advisor then connects to the existing non-sharded database, also called the source, analyzes its schema and query workload, and produces a set of alternative designs for the sharded database, including recommendations for an effective sharding key, which tables to shard, and which tables to duplicate on all shards.

Sharding configurations are ranked in terms of query performance, with the ranking favoring configurations that maximize single and multi-shard queries that do not require cross-shard joins, while minimizing multi-shard queries that require cross-shard joins.

You choose the design that best fits your requirements. The designs are ranked by the advisor, so if you don't have specific preferences you can choose the highest ranked design by default.

Run the Sharding Advisor command-line tool against your existing, non-sharded Oracle Database to obtain recommended Oracle Sharding configurations.

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;

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

To minimize the impact on a live production system, you can run the Sharding Advisor on a copy of the database schema and workload, located on a different server than the production system.

The following procedure uses the HR schema as an example.

Do the following steps on the source (production) database server.

1. Export the schema using Data Pump Export.

   > expdp system/password SCHEMAS=HR DIRECTORY=HR_DIR CONTENT=METADATA_ONLY
    DUMPFILE=hr_metadata.dmp LOGFILE=hr_exp.lst

2. Export the Automatic Work Repository (AWR) snapshot.

   SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql

Do the following steps on the target database server.

3. Copy the dump files from the source to the target.

   For example, copy the dump files to /scratch/dump.

4. Create a user that can run Sharding Advisor on the schema.

   SQL> CREATE USER hr IDENTIFIED BY password;

5. Create (or replace) the dump file directory variable that Data Pump Import can reference.

   SQL> CREATE DIRECTORY HR_DIR AS '/scratch/dump'
   
   SQL> CREATE OR REPLACE DIRECTORY  HR_DIR AS '/scratch/dump'

6. Import the schema.

   > impdp system/password DIRECTORY=HR_DIR DUMPFILE=hr.dmp LOGFILE=imp.lst SCHEMAS=HR

7. Load the AWR data.

   SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql

8. Now you can run Sharding Advisor on the target, non-production, copy of the database with the user you created.

   > gwsadv –u hr –p password –c -awr_snap_begin begin_timestamp –awr_snap_end end_timestamp -w

Sharding Advisor discovers the table families for each potential sharding column that it extracts from the query workload, and ranks the table families based on query classification rules and a ranking algorithm.

SELECT rank, tableName as tname, tabletype as type,
           tablelevel as tlevel, parent, shardby as shardBy,
           shardingorreferencecols as cols, unenforceableconstraints,
           sizeoftable  
FROM SHARDINGADVISOR_CONFIGURATIONS
ORDER BY rank, tlevel, tname, parent;

There are some aspects of database sharding to take into consideration when deciding which configuration to choose for your sharded database.

To review the sharding configurations and related information, you can query the following output database tables, which are stored in the same schema as your source database.

• SHARDINGADVISOR_CONFIGURATIONS Table
  Each row of the SHARDINGADVISOR_CONFIGURATIONS table represents a table in a ranked sharded configuration, and provides information about whether to shard or duplicate it, and if sharded, its level in a table family hierarchy, its parent table, root table sharding key, foreign key reference constraints, and table size per shard.
• SHARDINGADVISOR_CONFIGDETAILS Table
  Each row of the SHARDINGADVISOR_CONFIGDETAILS table represents a ranked sharding configuration, and provides the number and collective size, per shard, of each type of table, the number of each type of query, and based on your source database's current workload, an estimated cost.
• SHARDINGADVISOR_QUERYTYPES Table
  Each row of the SHARDINGADVISOR_QUERYTYPES table represents a query in the workload, and lists the query type and SQL ID. Note that the same query can be of a different query type depending on the sharding configuration.

Because the Sharding Advisor output is contained in regular database tables, you can run many kinds of SQL queries against them to look at the output from different perspectives.

SELECT rank, tableName as tname, tabletype as type,
           tablelevel as tlevel, parent, shardby as shardBy,
           shardingorreferencecols as cols, unenforceableconstraints,
           sizeoftable  
FROM SHARDINGADVISOR_CONFIGURATIONS
ORDER BY rank, tlevel, tname, parent;

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy,
        shardingorreferencecols as cols, unenforceableconstraints,
        sizeoftable
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE rank = 1 AND tabletype = 'S' 
ORDER BY tlevel, tname, parent;

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy,
        shardingorreferencecols as cols, unenforceableconstraints,
        sizeoftable 
FROM SHARDINGADVISOR_CONFIGURATIONS
WHERE tabletype = 'S'
ORDER BY rank, tlevel, tname, parent;

SELECT rank, tableName as tname, tabletype as type,
           tablelevel as tlevel, parent, shardby as shardBy,
           shardingorreferencecols as cols, unenforceableconstraints,
           sizeoftable   
FROM SHARDINGADVISOR_CONFIGURATIONS   
WHERE rank = 1 AND tabletype = 'D'   
ORDER BY tlevel, tname, parent;

SELECT COUNT(*)
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE tablename = 'TABLE_NAME' AND tablelevel = 0;

SELECT rank, tableName as tname, tabletype as type,
        tablelevel as tlevel, parent, shardby as shardBy, 
        shardingorreferencecols as cols 
FROM SHARDINGADVISOR_CONFIGURATIONS 
WHERE tabletype = 'S'
    AND rank IN
        (SELECT rank 
        FROM SHARDINGADVISOR_CONFIGURATIONS
        WHERE tablename = 'TABLE_NAME' and tablelevel = 0)
ORDER BY rank, tlevel, tname, parent;

SELECT rank, chosenbyuser,
        numshardedtables as stabs, sizeofshardedtables as sizestabs,
        numduplicatedtables as dtabs,
        sizeofduplicatedtables as sizedtabs,
        numsingleshardqueries as numssq,
        nummultishardqueries as nummsq,
        numcrossshardqueries as numcsq, cost
FROM SHARDINGADVISOR_CONFIGDETAILS
ORDER BY rank;

SELECT rank,
        numshardedtables as stabs, sizeofshardedtables as sizestabs,
        numduplicatedtables as dtabs,
        sizeofduplicatedtables as sizedtabs,
        numsingleshardqueries as numssq,
        nummultishardqueries as nummsq,
        numcrossshardqueries as numcsq, cost
FROM SHARDINGADVISOR_CONFIGDETAILS
WHERE CHOSENBYUSER = ‘Y’
ORDER BY RANK;

Sharding Advisor is a client-side utility that connects to the non-sharded database using authenticated OCI connections.

• The Sharding Advisor requires the appropriate credentials (user name and password) to connect to the non-sharded source database. Sharding Advisor can be run as a different user than the user that owns the source database schema that the Sharding Advisor analyzes. This user must have SELECT privileges on the tables in the non-sharded schema.
• The user needs SELECT privileges on the GV$SQL_PLAN and GV$SQL_PLAN_STATISTICS_ALL views, and on the DBA_HIST_SQL_PLAN, DBA_HIST_SQLSTAT, and DBA_HIST_SNAPHSOT tables. The user does not need any other special privileges.
• Sharding Advisor is not vulnerable to privilege escalation and denial of service.
• Sharding Advisor does not store or expose any sensitive data such as passwords, database service names, or user names.
• Sharding Advisor does not expose sensitive details about the inner workings of the product.
• Sharding Advisor does not include any interfaces or APIs which are not externally documented.
• Sharding Advisor does not require any insecure protocols to be enabled.
• Sharding Advisor does not use any insecure modes of operation.
• Sharding Advisor does not store any data or other information in any files.
• All connections to the database are through authenticated OCI connections.
• There are no SETUID executables created.
• No new grants to PUBLIC are done.
• No new default schemas are created, but Sharding Advisor internal tables are created under the user that is used to run Sharding Advisor.