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访问。
创建dblink
CREATE DATABASE LINK lnk_to_pri CONNECT TO SYS$UMF IDENTIFIED BY password USING 'inst1';
表1 通过使用指向主库的dblink来优化备库的工作负载。
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-94BB4237E77E
版权声明:本文为博主原创文章,未经博主允许不得转载。
ADG SQL Tuning Advisor