签到成功

知道了

CNDBA社区CNDBA社区

Oracle 12C R2-新特性-ADG支持SQL Tuning Advisor

2018-01-07 13:48 3792 0 原创 Oracle 12C
作者: Expect-乐

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.

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

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

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

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

创建的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

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问:1573265次
  • 积分:1957
  • 等级:核心会员
  • 排名:第4名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ