通过迁移OUTLINE，实现执行计划迁移，使得测试环境再现生产环境效率问题

[Lian...@gmail.com]

1.使用源环境system用户对相关用户赋权，解锁
ALTER USER outln ACCOUNT UNLOCK;

GRANT CREATE ANY OUTLINE TO TEST;


2.源环境TEST用户
CREATE OUTLINE problem_outline FOR CATEGORY problem_category ON
SELECT *
FROM test1, test2
WHERE test1.a = test2.a


3.从源环境导出outline
unix版: exp outln/outln file=outline.dmp tables='OL$' 'OL$HINTS' 'OL
$NODES' query=\"where CATEGORY=\'PROBLEM_CATEGORY\'\"
windows版: exp outln/outln@ora10g file=outline.dmp tables='OL$' 'OL
$HINTS' 'OL$NODES' query=\"where CATEGORY='PROBLEM_CATEGORY'\"

有query时，exp会报EXP-00091，可以无视这个错误


4.使用源环境system用户对相关用户收回赋权，加锁
ALTER USER outln ACCOUNT LOCK;

REVOKE CREATE ANY OUTLINE FROM TEST;

---------------------------------------

5. 用sys用户赋权
GRANT EXECUTE ON DBMS_OUTLN TO TEST;


6. DROP掉重名OUTLINE
BEGIN
SYS.OUTLN_PKG.clear_used ('PROBLEM_OUTLINE');
SYS.OUTLN_PKG.drop_unused;
END;


7. 用sys用户收回赋权
REVOKE EXECUTE ON DBMS_OUTLN FROM TEST;


8. 将outline导入目标环境
imp outln/outln@ora10g file=outline.dmp tables='OL$' 'OL$HINTS' 'OL
$NODES' ignore=y


9. 设置会话级参数use_stored_outlines
ALTER SESSION SET use_stored_outlines=problem_category;


10. 开始测试


11. ALTER SESSION SET use_stored_outlines=false;

[Lian...@gmail.com]

修改了exp和drop outline部分(第3、5、6、7步)：

1.使用源环境system用户对相关用户赋权，解锁
ALTER USER outln ACCOUNT UNLOCK;

GRANT CREATE ANY OUTLINE TO TEST;

2.源环境TEST用户
CREATE OUTLINE problem_outline FOR CATEGORY problem_category ON
SELECT *
FROM test1, test2
WHERE test1.a = test2.a

3.从源环境导出outline
unix版: exp outln/outln file=outline.dmp tables='OL$' 'OL$HINTS' 'OL

$NODES' query=\"where OL_NAME=\'PROBLEM_OUTLINE\'\" direct=y

windows版: exp outln/outln@ora10g file=outline.dmp tables='OL$' 'OL

$HINTS' 'OL$NODES' query=\"where OL_NAME='PROBLEM_OUTLINE'\" direct=y

有query时，exp会报EXP-00091，可以无视这个错误

4.使用源环境system用户对相关用户收回赋权，加锁
ALTER USER outln ACCOUNT LOCK;

REVOKE CREATE ANY OUTLINE FROM TEST;

---------------------------------------

5. 用system用户赋权
GRANT DROP ANY OUTLINE TO TEST;

6. DROP掉重名OUTLINE
DROP OUTLINE problem_outline;

7. 用system用户收回赋权
REVOKE DROP ANY OUTLINE FROM TEST;