直接复制mos的内容了哈，Diagnostic Methodology To Troubleshoot Performance Problems in Active Dataguard (read only standby database) Environment (Doc ID 2269132.1)

因为你没提过版本，所以你看看Mos对于你的版本提供了哪些查询办法
MAIN CONTENT
Following are the list of diagnostic tools can be used to troubleshoot the Database / SQL Performance problems in an ADG (Active Data Guard) environment (read-only standby database).

1. Standby Statspack

This can be used to collect data from a standby database that is opened in read-only.
As a starting point, use Standby Statspack to identify Top SQL (by CPU, Elapsed Time, Buffer Gets, etc.).

Standby Statspack can be used to periodically analyze where database time is being spent.
Identify poorly performing queries from Standby Statspack using the Top SQL sections (‘SQL ordered by CPU’, ‘SQL ordered by Elapsed Time’).
You may also generate a report on a specific SQL Hash ID, for more information see My Oracle Support Document 1081044.1.
Document 454848.1 - Installing and Using Standby Statspack in 11g
Document 1081044.1 - Provide SQL report function using STANDBY STATSPACK and Active Data Guard (Doc ID 1081044.1)
2. AWR report (DB Version 12.2 and newer only)

Starting with Oracle Database 12c Release 2 (12.2), Automatic Workload Repository (AWR) data can be captured for Active Data Guard (ADG) standby databases.

This feature enables analyzing any performance-related issues for ADG standby databases.

AWR can be used to periodically analyze where database time is being spent
Identify poorly performing queries from AWR using the Top SQL Sections (‘SQL Ordered by CPU’ & ‘SQL Ordered by Elapsed Time’)
Document 2409808.1 How to Generate AWRs in Active Data Guard Standby Databases
3. In-Memory Active Session History (ASH)

Monitor real-time query performance using Active Session History (ASH).
Real-time statistics can be collected on an Active Data Guard 11g Release standby database using in-memory ASH.
ASH reports provide analysis of transient performance problems that typically last for a few minutes.
ASH also performs scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL_ID.
Only ASH history will not be available in ADG environment.
For documentation on ASH see:
https://docs.oracle.com/database/121/TGDBA/sampling.htm#TGDBA276
4. Real-Time SQL Monitoring

Beginning with Oracle Database 11g Release 2 (11.2.0.2) users may use Real-Time SQL Monitoring Active Report for a particular execution of long running SQL (accruing more than 5s of CPU or I/O) with Active Data Guard standby databases.
Alternatively, the active report can be directly produced using command line by invoking the PL/SQL procedure dbms_sqltune.report_sql_monitor() using “active” as the report type.

For example, the following SQL*Plus script shows how to generate an active report for the statement that was monitored last by Oracle:
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool report.html
select dbms_sqltune.report_sql_monitor(sql_id => ‘<sql_id>’, sql_exec_id => &3, type=>‘active’) from dual;
spool off
Note: SQL_ID, SQL_EXEC_ID are available in V$SQL_MONITOR

Document 1380492.1 - Monitoring SQL Statements with Real-Time SQL Monitoring

The Real-Time SQL Monitoring Detail report also supports Active Data Guard as of Oracle Database 11g Release 2 (11.2.0.2).
SQL Details Active report is a new type of interactive report powered by Enterprise Manager UI technology hosted on OTN. Other active reports are SQL Monitor and SQL Performance Analyzer.

5. 10046 & 10053 trace can be collected in read-only standby database for offending SQLs to diagnose further.

Document 376442.1 - How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
Document 225598.1 - How to Obtain Tracing of Optimizer Computations (EVENT 10053)
6. Using SQLT XTRSBY Method

This SQLT method is used to analyze a SQL executed on a Data Guard or stand-by read-only database. You need to know the SQL_ID or the HASH_VALUE of the SQL to be analyzed.

Create on Primary database a link to read-only database connecting as any user that has access to the data dictionary. A DBA account would be fine:

CREATE PUBLIC DATABASE LINK V1123 CONNECT TO mydba IDENTIFIED by mydba_password
USING ‘(DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)
(HOST=HIDDEN HOSTNAME)(PORT=1521))(CONNECT_DATA=(SID = V1123)))’;
If the SQL is still in memory in the read-only database, then XTRSBY finds it and provides a set of diagnostics files, else XTRSBY errors out.

Important performance statistics, like actual number of rows per execution plan operation, will be available if the SQL was parsed while parameter STATISTICS_LEVEL was set to ALL when the SQL was hard-parsed in the read-only database.
You can also produce same valuable performance statistics by including the following CBO hint in your SQL: /*+ GATHER_PLAN_STATISTICS /.
On 11g you may want your SQL to contain the following CBO Hints for enhanced diagnostics: /+ GATHER_PLAN_STATISTICS MONITOR */.

1. When this method is used, it asks for the SQLTXPLAIN password, which is needed to export the SQLT repository corresponding to this execution of XTRSBY.
2. XTRSBY takes 3 parameters: the SQL id, the DB_LINK id, and the SQLTXPLAIN password.
3. This method requires the application user executing SQLT to be granted the SQLT_USER_ROLE role.
4. To use this XTRSBY method, be sure SQLT has been installed on the Primary first, and replicated into the read-only database.
5. Then connect into SQL*Plus in Primary and execute the sqlt/run/sqltxtrsby.sql script passing the SQL_ID or HASH_VALUE followed by the DB_LINK.

cd sqlt/run

sqlplus apps

SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK]
SQL> START sqltxtrsby.sql 0w6uydn50g8cx sqltxplain_password V1123
SQL> START sqltxtrsby.sql 2524255098 sqltxplain_password v1123

Refer Document 1614107.1 - SQLT Usage Instructions for XTRSBY method.

7. SQL Health Check Report

The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise. The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

SQLHC can be used in Dataguard or any read-only database.
On a read-only instance, only the “Observations” section with the results of the health-checks will be missing. Other info like diagnostics, plans, statistics are all collected.
The SQL for which this script is executed must be memory-resident, ie available in memory to get the information.

sqlplus / as sysdba

SQL> START [path]sqlhc.sql [T|D|N] [SQL_ID]
SQL> START sqlhc.sql T 51x6yr9ym5hdc

Refer Document 1366133.1 - SQL Tuning Health-Check Script (SQLHC)

8. Systemstate dump and Hanganalyze traces can be collected in ADG environment.

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
SQL> oradebug dump systemstate 258
SQL> oradebug tracefile_name

Operations Allowed in ADG (read-only standby database) environment

Issue SELECT statements, including queries that require multiple sorts that leverage TEMP segments
Use ALTER SESSION and ALTER SYSTEM statements
Use SET ROLE
Call stored procedures
Use database links (dblinks) to write to remote databases
Use stored procedures to call remote procedures via dblinks
Use SET TRANSACTION READ ONLY for transaction level read consistency
Issue complex queries (such as grouping SET queries and WITH CLAUSE queries)
Operations Not Allowed in ADG (read-only standby database) environment

Any DMLs (excluding simple SELECT statements)
Any DDLs
Query accessing local sequences
DMLs to local temporary tables
Grant or Revoke commands
SQL Tuning Advisors

Tuning Considerations for Slow Running SQL statements in ADG environment

ADG environment uses the same optimizer statistics generated by its primary database, including system statistics.
It also uses the same SQL plan baselines and SQL profiles used by the primary database (SQL plan baselines are the set of accepted plans for a SQL statement that have been proven to perform well).
SQL Profiles & SPM baselines can be created in primary database for offending SQLs and those are replicated to standby automatically by Data Guard.
Optimizer/Database parameters changed at primary database in session / system level are NOT replicated to standby database.
If any anomalies found in database or optimizer parameters setting across the primary / standby database, such parameters can be set in standby on par with primary database to achieve the better performance.
Following My Oracle Support Documents can be referred to create custom SQL profiles, SPM baselines & to use SQL tuning advisors in Primary database for offending SQL statements:-