Automatic SQL Tuning Sets (ASTS) in Oracle 19c and 20c

A well performing SQL statement starts all of sudden to misbehave? Fixing such queries is very much embedded into Oracle Autonomous’ strategy and here is how Automatic SQL Tuning Sets help.

What is the Automatic SQL Tuning Set? – Nigel Bayliss explains it all in a short informative blog post where he describes it as a system-maintained SQL tuning set that is an incredibly useful source of historic SQL performance metrics and execution plans. You can use ASTS to repair SQL performance regressions very quickly using SQL plan management.

An example of how to fix a SQL statement using SQL Plan management can be found in a previous post of Nigel entitled Repairing SQL Performance Regression with SQL Plan Management. Check this github example or who to “fix a misbehaving SQL query”.

Automatic SQL Tuning Sets were introduced in Oracle 19.7 where they were enabled by default. In 19.8 and 20c, they are disabled by default and MOS Doc ID 2686869.1 gives examples on how to enable and disable them:

 
exec DBMS_AUTO_TASK_ADMIN.ENABLE(
Client_Name => 'Auto STS Capture Task',
Operation => NULL,
Window_name => NULL);

You can check if the task is disabled or enabled by running:

 
SELECT Task_Name, Enabled 
FROM DBA_AUTOTASK_SCHEDULE_CONTROL
WHERE Task_Name = 'Auto STS Capture Task';

The MOS note above shows also how to check the amount of space consumed by all SQL tuning sets and also the statement counts per SQL tuning set:

 
SELECT 
Decode(SQLSet_Name,'SYS_AUTO_STS','ASTS','NON-ASTS'), count(*)
FROM DBA_SQLSet_Statements
GROUP BY Decode(SQLSet_Name,'SYS_AUTO_STS','ASTS','NON-ASTS');

Oracle recommends that you enable ASTS and one reason for this is that Automatic Indexing relies on the ASTS.

The view DBA_SQLSET_STATEMENTS from the query above displays information about the SQL statements, along with their statistics that form all SQL tuning sets in the database. Worth keeping it in mind. Franck Pachot showed interesting screenshots on the topic.

In 20c, the data dictionary views related to ASTS changed a bit, so the queries form 19c will not work. Oracle moved it to DBA_AUTOTASK_SETTINGS.

The 20c run the following:

 
SELECT Task_Name, Enabled, Interval Task_Interval_in_Seconds
FROM DBA_AUTOTASK_SETTINGS
WHERE Task_Name = 'Auto STS Capture Task';

You can check details about the status of the task by running:

 
SELECT Task_Name, Status
FROM DBA_AUTOTASK_SCHEDULE_CONTROL
WHERE Task_Name = 'Auto STS Capture Task';

Do not worry that Oracle captures a lot of SQL statements – that is the idea after all as you improve the likelihood that you can repair queries that previously ran very quickly and did not show up in AWR. SQL statement performance regressions are repaired automatically and the automatic mode is enabled by setting the DBMS_SPM parameter ALTERNATE_PLAN_BASELINE to AUTO on supported platforms and it is currently equivalent to “AUTOMATIC_WORKLOAD_REPOSITORY+CURSOR_CACHE+SQL_TUNING_SET”.

Check also Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0 by Mike Dietrich.

Sharding Advisor and Federated Sharding in Oracle Database 20c

“Shards of glass can cut and wound or magnify a vision.” – Terry Tempest Williams

Same with databases: a single shared database can be a performance bottleneck, a sharded database can magnify your IT vision stragegy.

Oracle RAC and Oracle Data Guard meet more than 99% of the scalability and availability needed by business applications but sometimes infinite availability is also required.

Sharding is an architectural concept which aims at high scalability and absolute availability by splitting the database into a collection of independent physical databases. But this is from the perspective of the DBA. From the perspective of an application, a sharded database looks like a single database; the number of shards, and the distribution of data across those shards, are completely transparent to the application.

Sharding was introduced with Oracle 12c and MOS Doc ID 2226341.1 is the master note for Oracle Sharding. What is important to know is that you can shard database tables by consistent hash (system-managed sharding), by range or list (user-defined sharding), or a combination (composite sharding).

In Oracle 20c, Oracle came with 2 new advisors: Oracle Autonomous Database Advisor and the Oracle Sharding Advisor. The Oracle Sharding Reference is good page to bookmark!

Sharding Advisor is a standalone command-line tool that helps you redesign a database schema so that you can efficiently migrate an existing non-sharded Oracle Database to an Oracle sharding environment. Sharding Advisor analyzes your existing database schema and produces a ranked list of possible sharded database designs.

The Sharding Advisor is a an OS command line tool called gwsadv and by default the workload is captured from V$SQL_PLAN_STATISTICS_ALL.

Here is how you invoke/run the Oracle Sharding Advisor:

 

[oracle@julian ~]$ gwsadv -n 138.121.142.24:1521 -s novopdb1.laika.oraclevcn.com -u julian -p abc -c -w

********** WELCOME TO THE SHARDING ADVISOR **********

Sharding Advisor: Release 20.0 - Development on Sun Jul 26 2020 07:16:07
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
  

Altogether there are 10 possible parameters/flags for the tool, only 3 are mandatory: the username -u, the password -p and -w which directs Sharding Advisor to use the query workload for sharding configuration generation and ranking. The -c parameter is required only on a first run or on a changed workload. For the remaining 6 non-mandatory flags, check the Sharding Advisor Usage and Options.

Note that the sch flag specifies the list of schemas for the Sharding Advisor if you want to run it as a different user which you create specifically for the Sharding Advisor. That user will need SELECT privileges on GV$SQL_PLAN and GV$SQL_PLAN_STATISTICS_ALL, and on the DBA_HIST_SQL_PLAN, DBA_HIST_SQLSTAT, and DBA_HIST_SNAPHSOT tables. The user does not need any other special privileges.

Once you start the Sharding Advisor with the command above, you will be asked you some questions to help in the sharded database design, the first question being on how many shards you are planning to have:

 
** The number of shards in a sharded configuration is an important parameter **
** that can influence scalability, fault isolation, and availability. **

ENTER THE ESTIMATED NUMBER OF PRIMARY SHARDS FOR YOUR CURRENT CONFIGURATION (1-1000): 4

*** CAPTURING QUERY WORKLOAD PREDICATES ... ***

*** ANALYZING USER SCHEMA ... ***
  

Usually, a database schema has some tables with multiple foreign key constraints. Sharding has a limitation that a sharded table can only have one foreign key constraint that links it to another table in the same table family. As a result, for every table that has more than one foreign key constraint, a single constraint must be chosen to be enforced in the sharded schema. You will be asked questions like this:

 
** The 'JULIAN.ORDERS' table has multiple foreign key constraints as shown below. **

** 1. 'JULIAN.ORDERS' is related to primary table 'JULIAN.ORDER_TYPE' by foreign key 'ORDER_TYPE_FK' with a fan-out of '1.8' **
** 2. 'JULIAN.ORDERS' is related to primary table 'JULIAN.CLIENTS' by foreign key 'CLIENTS_FK1' with a fan-out of '1.3' **

ENTER WHICH OF THESE CONSTRAINTS YOU WANT TO CONSIDER FOR TABLE FAMILY ENUMERATION
(AS COMMA-SEPARATED RANGES OR ENTER '0' TO CHOOSE NONE): 1
   

Note: Fan-out, in any tree, is number of pointers to child nodes in a node. In Oracle, in this case, fan-out is the ratio of number of rows in the referencing table to that of the referenced table (parent/primary table).

To review the Sharding Advisor, use the following 6 output tables out of which only the first 3 are documented:

– SHARDINGADVISOR_CONFIGURATIONS
– SHARDINGADVISOR_CONFIGDETAILS
– SHARDINGADVISOR_QUERYTYPES
– SHARDINGADVISOR_PREDS
– SHARDINGADVISOR_ECPREDS
– SHARDINGADVISOR_IMPORTANT_TABS

Check also the following Sharding Advisor Output Review SQL Examples.

There are few restrictions to the Sharding Advisor:

– The source database must be Oracle Database 10g or later
– The Sharding Advisor discovers the table families based on primary key-foreign key relationships, so if the schema does not have any primary key-foreign key constraints, sharding by PARENT clause is recommended
– Sharding Advisor recommends only single-table family, system-managed sharding (sharding by reference) configurations if the source database has foreign key constraints
– Sharding Advisor recommends sharding using the PARENT clause if the source database does not have foreign key constraints

Oracle have declarative rapid deployment automation for Sharding available in OCI Marketplace as well as on-premises with Terraform and Kubernitis. Check the Oracle blog article Sharding Oracle Database Cloud Service by Shailesh Dwivedi.

===

In Oracle 20c, you can also create a Sharded Database from multiple existing databases – this is called Federated Sharding.

It is now possible to convert a set of existing physical independent databases running the same application into a sharded database without modifying the database schemas or the application.

Oracle Sharding, in a federated sharding configuration, treats each independent physical database as a shard, and thus you can run multi-shard queries on all the shards.

You can create a federated sharding configuration with minor version mismatches between the shards. For example, the “North America” region could be on Oracle 20.2 and the “Europe” region could be on Oracle 20.3. But all database shards and the shard catalog must be on Oracle Database 20c or later. The databases must have the same schemas and only minor differences are supported. For example, a table can have an extra column or a missing column in one of the databases.

Often there are application upgrades which means that new tables, new columns, new constraint are added or column data types are being modified. When part of an overall Federated Sharding configuration, Oracle Sharding handles the schema differences caused by an application upgrade, as long as the overall schema structure stays the same.

To create a Federated Sharding environment using existing databases, you define the database layout just as you would for user-defined sharding, using GDSCTL commands. You need to execute these 7 steps in order to deploy a Federated Sharding configuration.

Just as an example, here is how you convert tables to duplicated or sharded tables:

 
ALTER TABLE catalog EXTERNALLY DUPLICATED;
ALTER TABLE orders EXTERNALLY SHARDED;

These are the limitations on Federated Sharding:

– There is no concept of chunk in a federated sharding configuration, so the GDSCTL MOVE CHUNK command is not supported
– Application sharding key-based routing is not supported
– The existing databases, before being added to a federated sharding configuration, must be upgraded to Oracle Database 20c or later

If you managed to bare reading until this last paragraph, please check also Partitioning vs. Federation vs. Sharding. Its last paragraph too…