Migrating Stored Outlines to SQL Plan Baselines

huangtingzhong 发布于 2014-06-26 19:57调优抢沙发

测试环境：11.2.0.3.0

欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw

1，创建一个Outline

SQL> create outline outline_test on select count(*) from scott.test where owner=’SCOTT’;

Outline created.

2,开始迁移

SQL> var rpt clob;

SQL> set serveroutput on;

SQL> exec :rpt := dbms_spm.migrate_stored_outline(attribute_name => ‘CATEGORY’, attribute_value => ‘DEFAULT’);

PL/SQL procedure successfully completed.

3，迁移后，确认是否迁移成功

SQL> select name,migrated from dba_outlines;

NAME MIGRATED

—————————– ————

OUTLINE_TEST MIGRATED

这里可以发现migrated已经标记为migrated，下次迁移的时候就不会再迁移了。

SQL> @sql_spm.sql

SQL> set echo off

ENABLE:ACCTPE

FIXED PARSING CREATED

SQL_HANDLE PLAN_NAME ORIGIN AUTOPURGE COST SCHEMA MODIFIED SQL_TEXT

—————————— —————————— ————– ——————– ——– ————— —————– ————————————————–

SQL_2f030a70eeeec40f OUTLINE_TEST STORED-OUTLINE YES.YES.NO.NO 8 SYS 11-23 05.11-23 05 select count(*) from scott.test where owner=’SCOTT

‘

认真就输