针对stored outline做得几个测试
作者：jametong | 分类： 大话技术 | 标签： plan stability, stored outlines | 日期：2010-02-10
主要的测试代码以及测试方法都依赖于之前翻译的两篇Jonathan Lewis的关于stored outlines的两篇文章:
Oracle 8i/9i中的执行计划稳定性
在Oracle 9中伪造存储概要
此文的原文为 针对stored outline做得几个测试
1. 测试场景
- os : Red Hat Enterprise Linux AS release 4 (Nahant Update 6)
- Db Version: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
2. 初始化环境.
sys@test>@tmp sys@test>create user james identified by james default tablespace users temporary tablespace temp; User created. Elapsed: 00:00:00.00 sys@test>grant create session,create table,create procedure,create any outline,alter session to james; Grant succeeded. Elapsed: 00:00:00.01 sys@test>alter user james quota unlimited on users; User altered. Elapsed: 00:00:00.00 sys@test> sys@test>@conn james/james sys@test>SET TERMOUT OFF james@test> james@test>set feedback off james@test>alter session set cursor_sharing = exact 2 / Elapsed: 00:00:00.00 james@test>alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss' 2 / Elapsed: 00:00:00.00 james@test>SET TERMOUT ON james@test>set feedback on james@test>set timing on james@test> james@test>create table so_demo ( 2 n1 number, 3 n2 number, 4 v1 varchar2(10) 5 ); Table created. Elapsed: 00:00:00.05 james@test> james@test>insert into so_demo values (1,1,'One'); 1 row created. Elapsed: 00:00:00.00 james@test> james@test>create index sd_i1 on so_demo(n1); Index created. Elapsed: 00:00:00.01 james@test>create index sd_i2 on so_demo(n2); Index created. Elapsed: 00:00:00.00 james@test> james@test>analyze table so_demo compute statistics; Table analyzed. Elapsed: 00:00:00.01 james@test> james@test>!cat > c_proc.sql <create or replace procedure get_value ( 2 i_n1 in number, 3 i_n2 in number, 4 io_v1 out varchar2 5 ) 6 as 7 begin 8 select v1 9 into io_v1 10 from so_demo 11 where n1 = i_n1 12 and n2 = i_n2 13 ; 14 end; 15 / Procedure created. Elapsed: 00:00:00.02 james@test>_END_ SP2-0042: unknown command "_END_" - rest of line ignored. james@test>!wrap iname=c_proc.sql PL/SQL Wrapper: Release 9.2.0.8.0- 64bit Production on Tue Feb 09 23:29:26 2010 Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved. Processing c_proc.sql to c_proc.plb james@test>@c_proc.plb james@test>
3. 得到当前此SQL的存储概要.
james@test>var m_value varchar2(10); james@test>alter session set create_stored_outlines = demo; Session altered. Elapsed: 00:00:00.00 james@test>exec get_value(1, 1, :m_value); PL/SQL procedure successfully completed. Elapsed: 00:00:00.03 james@test>alter session set create_stored_outlines = false; Session altered. Elapsed: 00:00:00.00 james@test>col category format a20 james@test>select name, category, used, sql_text from user_outlines where category = 'DEMO'; NAME CATEGORY USED SQL_TEXT ------------------------------ -------------------- --------- -------------------------------------------------------------------------------- SYS_OUTLINE_100209233527397 DEMO UNUSED SELECT V1 FROM SO_DEMO WHERE N1 = :B2 AND N2 = :B1 1 row selected. Elapsed: 00:00:00.01 james@test>col hint format a30 james@test>select name, stage, hint from user_outline_hints where name = 'SYS_OUTLINE_100209233527397'; NAME STAGE HINT ------------------------------ ---------- ------------------------------ SYS_OUTLINE_100209233527397 3 NO_EXPAND SYS_OUTLINE_100209233527397 3 ORDERED SYS_OUTLINE_100209233527397 3 NO_FACT(SO_DEMO) SYS_OUTLINE_100209233527397 3 INDEX(SO_DEMO SD_I1) SYS_OUTLINE_100209233527397 2 NOREWRITE SYS_OUTLINE_100209233527397 1 NOREWRITE 6 rows selected. Elapsed: 00:00:00.00 james@test>
4. 第一种改变存储概要的方法, 也即通过直接修改存储概要表来调整.
4.1 生成新的用来伪造的存储概要
james@test>create or replace outline so_fix
2 for category demo on
3 select /*+ index(so_demo, sd_i2) */ v1
4 from so_demo
5 where n1 = 1
6 and n2 = 2;
Outline created.
Elapsed: 00:00:00.01
james@test>
james@test>select name, category, used, sql_text from user_outlines where name = 'SO_FIX';
NAME CATEGORY USED SQL_TEXT
------------------------------ -------------------- --------- --------------------------------------------------------------------------------
SO_FIX DEMO UNUSED select /*+ index(so_demo, sd_i2) */ v1
from so_demo
where n1 = 1
and n2 = 2
1 row selected.
Elapsed: 00:00:00.00
james@test>select name, stage, hint from user_outline_hints where name = 'SO_FIX';
NAME STAGE HINT
------------------------------ ---------- ------------------------------
SO_FIX 3 NO_EXPAND
SO_FIX 3 ORDERED
SO_FIX 3 NO_FACT(SO_DEMO)
SO_FIX 3 INDEX(SO_DEMO SD_I2)
SO_FIX 2 NOREWRITE
SO_FIX 1 NOREWRITE
6 rows selected.
Elapsed: 00:00:00.01
4.2 进入outln Schema Crack系统的outln相关表, 调换两个存储概要的实际内容.
outln@test>@tmp
outln@test>update outln.ol$hints
2 set ol_name =
3 decode(
4 ol_name,
5 'SO_FIX','SYS_OUTLINE_100209233527397',
6 'SYS_OUTLINE_020503165427311','SO_FIX'
7 )
8 where ol_name in ('SYS_OUTLINE_100209233527397','SO_FIX');
12 rows updated.
Elapsed: 00:00:00.00
outln@test>
outln@test>update outln.ol$ ol1
2 set hintcount = (
3 select hintcount
4 from ol$ ol2
5 where ol2.ol_name in ('SYS_OUTLINE_100209233527397',' SO_FIX')
6 and ol2.ol_name != ol1.ol_name
7 )
8 where
9 ol1.ol_name in ('SYS_OUTLINE_100209233527397','SO_FIX');
2 rows updated.
Elapsed: 00:00:00.00
outln@test>commit;
Commit complete.
Elapsed: 00:00:00.00
outln@test>
4.3 打开使用stored outlines的参数, 测试是否确实使用了存储概要, 并附上10046 Trace得到的信息.
james@test>@tmp james@test>var m_value varchar2(10); james@test>alter session set use_stored_outlines = demo; Session altered. Elapsed: 00:00:00.00 james@test>alter session set events '10046 trace name context forever,level 12'; Session altered. Elapsed: 00:00:00.00 james@test>exec get_value(1, 1, :m_value); PL/SQL procedure successfully completed. Elapsed: 00:00:00.03 james@test>alter session set events '10046 trace name context off'; Session altered. Elapsed: 00:00:00.00 james@test>alter session set use_stored_outlines = false; Session altered. Elapsed: 00:00:00.00 james@test>col spid new_value spid james@test>select spid from v$process where addr = ( 2 select paddr from v$session a,v$mystat b where a.sid = b.sid and rownum
sys@test>@plan
Enter value for hash_value: 3638091068
SQL_TEXT
----------------------------------------------------------------
SELECT V1 FROM SO_DEMO WHERE N1 = :B2 AND N2 = :B1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID| SO_DEMO | 1 | 7 | 3 (34)|
|* 2 | INDEX RANGE SCAN | SD_I2 | 1 | | 2 (50)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SO_DEMO"."N1"=:B2)
2 - access("SO_DEMO"."N2"=:B1)
sys@test>
此时, 可以发现, 调整过后的存储概要已经发生作用了, 没有使用最初的索引SD_I1而是使用了我们指定的索引SD_I2.
5. 使用在另一个Schema创建View的方式来替换存储概要.
5.1 首先清除掉前面测试使用的存储概要, 并重新生成存储概要.
james@test>var m_value varchar2(10); james@test>alter session set create_stored_outlines = demo; Session altered. Elapsed: 00:00:00.01 james@test>exec get_value(1, 1, :m_value); PL/SQL procedure successfully completed. Elapsed: 00:00:00.05 james@test>alter session set create_stored_outlines = false; Session altered. Elapsed: 00:00:00.00 james@test> james@test>col category format a15 james@test>select name, category, used, sql_text from user_outlines where category = 'DEMO'; NAME CATEGORY USED SQL_TEXT ------------------------------ --------------- --------- -------------------------------------------------------------------------------- SYS_OUTLINE_100210003729028 DEMO UNUSED SELECT V1 FROM SO_DEMO WHERE N1 = :B2 AND N2 = :B1 1 row selected. Elapsed: 00:00:00.01 james@test>col hint format a30 james@test>l 1* select name, stage, hint from user_outline_hints where name = 'SYS_OUTLINE_100210003729028' james@test>/ NAME STAGE HINT ------------------------------ ---------- ------------------------------ SYS_OUTLINE_100210003729028 3 NO_EXPAND SYS_OUTLINE_100210003729028 3 ORDERED SYS_OUTLINE_100210003729028 3 NO_FACT(SO_DEMO) SYS_OUTLINE_100210003729028 3 INDEX(SO_DEMO SD_I1) ******************** SYS_OUTLINE_100210003729028 2 NOREWRITE SYS_OUTLINE_100210003729028 1 NOREWRITE 6 rows selected.
5.2 登录一个新的用户, 创建一个View并重新编译这个存储概要.
james2@test>create or replace view so_demo as 2 select /*+ index(so_demo,sd_i2) */ 3 * 4 from james.so_demo; View created. Elapsed: 00:00:00.07 james2@test>alter outline sys_outline_100210003729028 rebuild; Outline altered. Elapsed: 00:00:00.01 james2@test>@conn james/james Elapsed: 00:00:00.00 Elapsed: 00:00:00.00 james@test>col category format a15 james@test>select name, category, used, sql_text from user_outlines where category = 'DEMO'; NAME CATEGORY USED SQL_TEXT ------------------------------ --------------- --------- -------------------------------------------------------------------------------- SYS_OUTLINE_100210003729028 DEMO UNUSED SELECT V1 FROM SO_DEMO WHERE N1 = :B2 AND N2 = :B1 1 row selected. Elapsed: 00:00:00.01 james@test>col hint format a30 james@test>select name, stage, hint from user_outline_hints where name = 'SYS_OUTLINE_100210003729028' 2 / NAME STAGE HINT ------------------------------ ---------- ------------------------------ SYS_OUTLINE_100210003729028 3 NO_EXPAND SYS_OUTLINE_100210003729028 3 ORDERED SYS_OUTLINE_100210003729028 3 NO_FACT(SO_DEMO) SYS_OUTLINE_100210003729028 3 INDEX(SO_DEMO SD_I2) ******************** SYS_OUTLINE_100210003729028 2 NOREWRITE SYS_OUTLINE_100210003729028 1 NOREWRITE SYS_OUTLINE_100210003729028 1 NOREWRITE 7 rows selected. Elapsed: 00:00:00.00
关注使用星号标识的两行, 你将发现存储概要确实已经通过这种方式替换掉了
6. 使用在新Schema中创建一个类似的新表来实现改变存储概要的方法.
6.1. 通过在原Schema重新编译outline使其回到最初状态.
james@test>alter outline SYS_OUTLINE_100210003729028 rebuild; Outline altered. Elapsed: 00:00:00.01 james@test>select name, stage, hint from user_outline_hints where name = 'SYS_OUTLINE_100210003729028'; NAME STAGE HINT ------------------------------ ---------- ------------------------------ SYS_OUTLINE_100210003729028 3 NO_EXPAND SYS_OUTLINE_100210003729028 3 ORDERED SYS_OUTLINE_100210003729028 3 NO_FACT(SO_DEMO) SYS_OUTLINE_100210003729028 3 INDEX(SO_DEMO SD_I1) ***************** SYS_OUTLINE_100210003729028 2 NOREWRITE SYS_OUTLINE_100210003729028 1 NOREWRITE 6 rows selected. Elapsed: 00:00:00.00 james@test>
6.2. 在新的Schema创建对应表,以及索引SD_I2. 并重新编译存储概要.
james2@test>l 1 create table so_demo ( 2 n1 number, 3 n2 number, 4 v1 varchar2(10) 5* ) james2@test>/ Table created. Elapsed: 00:00:00.02 james2@test>create index sd_i2 on so_demo(n2); Index created. Elapsed: 00:00:00.08 james2@test>alter outline sys_outline_100210003729028 rebuild; Outline altered. Elapsed: 00:00:00.00 james@test>col hint format a30 james@test>select name, stage, hint from user_outline_hints where name = 'SYS_OUTLINE_100210003729028'; NAME STAGE HINT ------------------------------ ---------- ------------------------------ SYS_OUTLINE_100210003729028 3 NO_EXPAND SYS_OUTLINE_100210003729028 3 ORDERED SYS_OUTLINE_100210003729028 3 NO_FACT(SO_DEMO) SYS_OUTLINE_100210003729028 3 INDEX(SO_DEMO SD_I2) ********************* SYS_OUTLINE_100210003729028 2 NOREWRITE SYS_OUTLINE_100210003729028 1 NOREWRITE
这个时候, 你将发现存储概要也改成我们最初想要的结果了, 也即用索引SD_I2替换掉了SD_I1.
