Availability → Data Guard
SQL Tuning Advisor has been enhanced so that tuning can be initiated on one database but the actual tuning process is executed remotely on a different database. This allows DBAs to offload tuning of the primary database workload to an Active Data Guard standby and tuning of Active Data Guard SQL workloads on Active Data Guard itself. When offloading SQL tuning of primary database workloads to an Active Data Guard standby, the SQL tuning process is initiated from the primary database, but the expensive tuning process is executed remotely on the Active Data Guard standby and results are written back to the primary database using database links. When tuning Active Data Guard workloads, the entire SQL tuning process is executed locally at the Active Data Guard standby while maintaining the read-only nature of the database. This is accomplished by gathering the required information over database links from the primary database and writing back any database state changes (such as SQL profile implementation) over to the primary database. SQL profile recommendations implemented on the primary database are applied to the Active Data Guard standby using the redo apply mechanism.
Business Benefit: SQL Tuning Advisor's remote SQL tuning capability supports tuning of Active Data Guard workloads and offloading tuning of primary database workloads to an Active Data Guard standby. The business benefits of this feature are as follows: (1) Improved manageability of Active Data Guard databases through support of application tuning. (2) Better return on investment through utilization of available resources on Active Data Guard for primary database workload tuning. (3) Improved application performance of Active Data Guard and primary database workloads. (4) Improved read performance of demanding applications such as in reader-farm environments. (5) Flexibility in initiating SQL Tuning Advisor and storing results remotely in a different database. (6) Support for end-to-end tuning and testing of workflows when used with SQL Performance Analyzer, or otherwise.
Notes:
EE, Exa, and ODA: Extra cost option; license included with Oracle GoldenGate
CLOUD: Active Data Guard DML Redirection is only available in OCI