Active Data Guard (ADG) databases are widely used to offload reporting or ad hoc query-only jobs from the primary database. Reporting workload profile is different from the primary database and often requires tuning. Which tool is used to tune SQL workloads running on an ADG database?

The SQL Tuning Advisor is the designated Oracle tool for analyzing problematic SQL statements and recommending corrective actions. It can generate recommendations such as creating SQL profiles, restructuring the statement, or creating new indexes. On an Active Data Guard (ADG) standby, where reporting workloads run, the SQL Tuning Advisor can be used to capture and tune the specific queries running there, which may have different performance characteristics than on the primary OLTP system. This allows for targeted optimization of the reporting workload without impacting the primary.

A. Standby Statspack is a legacy performance data collection tool, superseded by AWR. It is not a tuning advisory tool.
B. In-Memory Active Session History (ASH) is a diagnostic data source that shows what sessions were active, but it is not an advisory tool that provides tuning recommendations.
C. Automatic Database Diagnostic Monitor (ADDM) does not run on a physical standby database, even in Active Data Guard mode.
D. Automatic Workload Repository (AWR) is the infrastructure for collecting and storing performance statistics. It provides the data, but the SQL Tuning Advisor is the tool that analyzes it to give advice.

1. Oracle Corporation. (2023). Oracle Database SQL Tuning Guide, 23c. Chapter 6, "Using SQL Tuning Advisor". The guide describes the advisor's function to "analyze high-volume SQL statements and generate tuning recommendations."

2. Oracle Corporation. (2023). Oracle Database VLDB and Partitioning Guide, 23c. Section 10.2.2, "Using Active Data Guard for Reporting and Test". This section explicitly mentions, "You can use tools such as SQL Tuning Advisor and SQL Performance Analyzer on the standby database."