1.说明

通过dblink，可以在主库上优化备库。

In remote tuning, the database on which you initiate a tuning task differs from the database in which the tuning process executes or in which results are stored. For example, a standby database can have its own workload of queries, some of which may require tuning. You can issue SQL Tuning Advisor statements on a standby database. A standby-to-primary database link enables DBMS_SQLTUNE to write data to and read data from the primary database. The link is necessary because the standby database, which is read-only, cannot write the SQL tuning data.

在主库上优化备库的工作负载

通过在DBMS_SQLTUNE存储过程中指定database_link_to参数来优化备库的工作负载，该参数默认值是null，表示优化的是本地数据库。

创建的dblink的所有者必须是SYS,可以被默认权限用户是SYS$UMF访问。http://www.cndba.cn/Expect-le/article/2553

创建dblinkhttp://www.cndba.cn/Expect-le/article/2553http://www.cndba.cn/Expect-le/article/2553

CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';

表1 通过使用指向主库的dblink来优化备库的工作负载。http://www.cndba.cn/Expect-le/article/2553

Step Statement Issued on Standby Database Result
1 CREATE_TUNING_TASK DBMS_SQLTUNE creates the task data in the primary database using the standby-to-primary database link.
2 EXECUTE_TUNING_TASK DBMS_SQLTUNE uses the database link to read the SQL Tuning Advisor task data stored in the primary database. The tuning analysis occurs on the standby database, but DBMS_SQLTUNE writes the results remotely to the primary database.
3 REPORT_TUNING_TASK DBMS_SQLTUNE uses the database link to read the SQL Tuning Advisor report data from the primary database, and then constructs the report locally on the standby database.
4 ACCEPT_SQL_PROFILE DBMS_SQLTUNE uses the database link to write the SQL profile data remotely to the primary database.

2.具体例子

VARIABLE tname VARCHAR2(30);
VARIABLE query VARCHAR2(500);

EXEC :tname := 'my_task';
EXEC :query := 'SELECT /*+ FULL(t)*/ col1 FROM table1 t WHERE col1=9000'; 

BEGIN 
:tname := DBMS_SQLTUNE.CREATE_TUNING_TASK(
            sql_text         => :query
          , task_name        => :tname
          , database_link_to => 'lnk_to_pri' );
END;
/

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:tname);

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:tname) FROM DUAL;

BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name        => :tname
,   name             => 'prof'
,   task_owner       => 'SYS'
,   replace          => TRUE
,   database_link_to => 'lnk_to_pri' );
END;
/

更多详细信息，请参考官方文档：
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-tuning-advisor.html#GUID-1980AAEB-E975-46D7-A816-94BB4237E77Ehttp://www.cndba.cn/Expect-le/article/2553http://www.cndba.cn/Expect-le/article/2553http://www.cndba.cn/Expect-le/article/2553

版权声明：本文为博主原创文章，未经博主允许不得转载。

ADG SQL Tuning Advisor