关于执行计划的稳定性

显示全部楼层 · 2014-2-19 11:55:14

SQL> set autotrace traceonly
SQL>
SQL> alter session set optimizer_mode = 'RULE';
会话已更改。
SQL>
SQL> alter session set create_stored_outlines = zfyj_catalog;
会话已更改。
SQL>
SQL> SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHY
LJ,0)),
2
SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0
)),
3
SUM(NVL(A.DYZE,0))
4
FROM YL_DYXMBD A, YL_LTXJBXX B, VW_RYJBXX C
5 WHERE A.GRBH = B.GRBH AND
6
A.GRBH = C.GRBH AND
7
C.DWBH = :b1 AND
8
B.JZSJ = :b2 AND
9
B.YWZJBZ = '2';

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=RULE (Cost=4414 Card=1 Bytes=91)
10 SORT (AGGREGATE)
21 NESTED LOOPS (OUTER) (Cost=4414 Card=3 Bytes=273)
32 NESTED LOOPS (Cost=4412 Card=3 Bytes=240)
43 NESTED LOOPS (Cost=2360 Card=4105 Bytes=246300)
54 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost

=307 Card=4105 Bytes=90310)
65
INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-

UNIQUE) (Cost=9 Card=4105)
74 TABLE ACCESS (BY INDEX ROWID) OF 'YL_DYXMBD' (Cost

=1 Card=1 Bytes=38)
87
INDEX (UNIQUE SCAN) OF 'SYS_C007434' (UNIQUE)
93 TABLE ACCESS (BY INDEX ROWID) OF 'YL_LTXJBXX' (Cost=

1 Card=1 Bytes=20)
109 INDEX (UNIQUE SCAN) OF 'SYS_C007420' (UNIQUE)
112 INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (NON-U

NIQUE) (Cost=1 Card=1 Bytes=11)

Statistics
----------------------------------------------------------

7recursive calls

0db block gets
26434consistent gets

0physical reads

0redo size
465bytes sent via SQL*Net to client
274bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SQL>
SQL> alter session set use_stored_outlines = zfyj_catalog;
会话已更改。
SQL>
SQL> alter session set optimizer_mode = 'CHOOSE';
会话已更改。
SQL>
SQL> SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHY
LJ,0)),
2
SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0
)),
3
SUM(NVL(A.DYZE,0))
4
FROM YL_DYXMBD A, YL_LTXJBXX B, VW_RYJBXX C
5 WHERE A.GRBH = B.GRBH AND
6
A.GRBH = C.GRBH AND
7
C.DWBH = :b1 AND
8
B.JZSJ = :b2 AND
9
B.YWZJBZ = '2';

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=4414 Card=1 Bytes=91

)
10 SORT (AGGREGATE)
21 NESTED LOOPS (OUTER) (Cost=4414 Card=3 Bytes=273)
32 NESTED LOOPS (Cost=4412 Card=3 Bytes=240)
43 NESTED LOOPS (Cost=2360 Card=4105 Bytes=246300)
54 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost

=307 Card=4105 Bytes=90310)
65
INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-

UNIQUE) (Cost=9 Card=4105)
74 TABLE ACCESS (BY INDEX ROWID) OF 'YL_DYXMBD' (Cost

=1 Card=1 Bytes=38)
87
INDEX (UNIQUE SCAN) OF 'SYS_C007434' (UNIQUE)
93 TABLE ACCESS (BY INDEX ROWID) OF 'YL_LTXJBXX' (Cost=

1 Card=1 Bytes=20)
109 INDEX (UNIQUE SCAN) OF 'SYS_C007420' (UNIQUE)
112 INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (NON-U

NIQUE) (Cost=1 Card=1 Bytes=11)

Statistics
----------------------------------------------------------

7recursive calls

0db block gets
26434consistent gets

0physical reads

0redo size
465bytes sent via SQL*Net to client
274bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SQL>
SQL> alter session set use_stored_outlines = zfyj_catalog;
会话已更改。
SQL>
SQL> SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHY
LJ,0)),
2
SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0
)),
3
SUM(NVL(A.DYZE,0))
4
FROM YL_DYXMBD A, YL_LTXJBXX B, VW_RYJBXX C
5 WHERE A.GRBH = B.GRBH AND
6
A.GRBH = C.GRBH AND
7
C.DWBH = '39101' AND
8
B.JZSJ = '200503' AND
9
B.YWZJBZ = '2';

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=459 Card=1 Bytes=91)
10 SORT (AGGREGATE)
21 NESTED LOOPS (OUTER) (Cost=459 Card=3 Bytes=273)
32 HASH JOIN (Cost=458 Card=3 Bytes=240)
43 NESTED LOOPS (Cost=450 Card=244 Bytes=14152)
54 TABLE ACCESS (FULL) OF 'YL_LTXJBXX' (Cost=328 Card

=244 Bytes=4880)
64 TABLE ACCESS (BY INDEX ROWID) OF 'YL_DYXMBD' (Cost

=1 Card=1 Bytes=38)
76
INDEX (UNIQUE SCAN) OF 'SYS_C007434' (UNIQUE)
83 INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_GRBH_RYZT' (UNI

QUE) (Cost=13 Card=4105 Bytes=90310)
92 INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (NON-U

NIQUE) (Cost=1 Card=1 Bytes=11)

Statistics
----------------------------------------------------------

7recursive calls

0db block gets
5233consistent gets

0physical reads

0redo size
487bytes sent via SQL*Net to client
274bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SQL>
SQL> alter session set use_stored_outlines = zfyj_catalog;
会话已更改。
SQL>
SQL> variable c1 char(10);
SQL> variable c2 char(6);
SQL> execute :c1 := '340300';
PL/SQL 过程已成功完成。
SQL> execute :c2 := '200503';
PL/SQL 过程已成功完成。
SQL>
SQL> SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHY
LJ,0)),
2
SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0
)),
3
SUM(NVL(A.DYZE,0))
4
FROM YL_DYXMBD A, YL_LTXJBXX B, VW_RYJBXX C
5 WHERE A.GRBH = B.GRBH AND
6
A.GRBH = C.GRBH AND
7
C.DWBH = :c1 AND
8
B.JZSJ = :c2 AND
9
B.YWZJBZ = '2';

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=459 Card=1 Bytes=91)
10 SORT (AGGREGATE)
21 NESTED LOOPS (OUTER) (Cost=459 Card=3 Bytes=273)
32 HASH JOIN (Cost=458 Card=3 Bytes=240)
43 NESTED LOOPS (Cost=450 Card=244 Bytes=14152)
54 TABLE ACCESS (FULL) OF 'YL_LTXJBXX' (Cost=328 Card

=244 Bytes=4880)
64 TABLE ACCESS (BY INDEX ROWID) OF 'YL_DYXMBD' (Cost

=1 Card=1 Bytes=38)
76
INDEX (UNIQUE SCAN) OF 'SYS_C007434' (UNIQUE)
83 INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_GRBH_RYZT' (UNI

QUE) (Cost=13 Card=4105 Bytes=90310)
92 INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (NON-U

NIQUE) (Cost=1 Card=1 Bytes=11)

Statistics
----------------------------------------------------------

7recursive calls

0db block gets
5229consistent gets

0physical reads

0redo size
465bytes sent via SQL*Net to client
274bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SQL>
SQL>

SQL> set autotrace off
SQL>
SQL> show parameter opti_
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
filesystemio_options
stringasynch
object_cache_optimal_size
integer 102400
optimizer_dynamic_sampling integer 1
optimizer_features_enable
string9.2.0
optimizer_index_caching
integer 0
optimizer_index_cost_adj
integer 50
optimizer_max_permutations integer 2000
optimizer_mode
stringRULE
SQL>
SQL> alter session set use_stored_outlines = zfyj_catalog;
会话已更改。
SQL>
SQL> alter session set optimizer_mode = 'CHOOSE';
会话已更改。
SQL>
SQL> variable b1 char(10);
SQL> variable b2 char(6);
SQL> execute :b1 := '340300';
PL/SQL 过程已成功完成。
SQL> execute :b2 := '200503';
PL/SQL 过程已成功完成。
SQL>
SQL> set autotrace traceonly
SQL>
SQL> SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHY
LJ,0)),
2
SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0
)),
3
SUM(NVL(A.DYZE,0))
4
FROM YL_DYXMBD A, YL_LTXJBXX B, VW_RYJBXX C
5 WHERE A.GRBH = B.GRBH AND
6
A.GRBH = C.GRBH AND
7
C.DWBH = :b1 AND
8
B.JZSJ = :b2 AND
9
B.YWZJBZ = '2';

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=4414 Card=1 Bytes=91

)
10 SORT (AGGREGATE)
21 NESTED LOOPS (OUTER) (Cost=4414 Card=3 Bytes=273)
32 NESTED LOOPS (Cost=4412 Card=3 Bytes=240)
43 NESTED LOOPS (Cost=2360 Card=4105 Bytes=246300)
54 TABLE ACCESS (BY INDEX ROWID) OF 'TB_GRJBXX' (Cost

=307 Card=4105 Bytes=90310)
65
INDEX (RANGE SCAN) OF 'IDX_TB_GRJBXX_DWBH' (NON-

UNIQUE) (Cost=9 Card=4105)
74 TABLE ACCESS (BY INDEX ROWID) OF 'YL_DYXMBD' (Cost

=1 Card=1 Bytes=38)
87
INDEX (UNIQUE SCAN) OF 'SYS_C007434' (UNIQUE)
93 TABLE ACCESS (BY INDEX ROWID) OF 'YL_LTXJBXX' (Cost=

1 Card=1 Bytes=20)
109 INDEX (UNIQUE SCAN) OF 'SYS_C007420' (UNIQUE)
112 INDEX (RANGE SCAN) OF 'IDX_TB_DWJBXX_DWBH_DWZT' (NON-U

NIQUE) (Cost=1 Card=1 Bytes=11)

Statistics
----------------------------------------------------------

0recursive calls

0db block gets
26432consistent gets

0physical reads

0redo size
449bytes sent via SQL*Net to client
274bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SQL>

千问 · 2014-2-19 11:55:14

参考文档为biti的执行计划稳定性.
http://blog.itpub.net/category/330/831
1.把绑定变量改为常量不能使用执行计划稳定性
2.把绑定变量的名称修改后不能使用执行计划稳定性
存储过程中的sql语句为:
select c1,c2,...
from t1,t2
where t1.c1 = t2.c3
and t1.c2 = p_c2;
对p_c2如何处理才能使用存储大纲?

千问 · 2014-2-19 11:55:14

最初由 husthxd 发布
[B]参考文档为biti的执行计划稳定性.
http://blog.itpub.net/category/330/831
1.把绑定变量改为常量不能使用执行计划稳定性
2.把绑定变量的名称修改后不能使用执行计划稳定性
存储过程中的sql语句为:
select c1,c2,...
from t1,t2
where t1.c1 = t2.c3
and t1.c2 = p_c2;
对p_c2如何处理才能使用存储大纲? [/B]
如果p_c2在编程时使用了bind var,
那就可以使用stored outline啦。

千问 · 2014-2-19 11:55:14

p_c2是存储过程中定义的一个变量.

千问 · 2014-2-19 11:55:14

p_c2虽然是存储过程定义的一个变量，但是在sql 语句里面，oracle 不管变量定义名称是什么，一律是按顺序标号的 :1 ,:2 ...

千问 · 2014-2-19 11:55:14

最初由 biti_rainy 发布
[B]p_c2虽然是存储过程定义的一个变量，但是在sql 语句里面，oracle 不管变量定义名称是什么，一律是按顺序标号的 :1 ,:2 ... [/B]
在toad中显示为:b1,:b2.....

千问 · 2014-2-19 11:55:14

最初由 biti_rainy 发布
[B]p_c2虽然是存储过程定义的一个变量，但是在sql 语句里面，oracle 不管变量定义名称是什么，一律是按顺序标号的 :1 ,:2 ... [/B]
意思就是说在sqlplus中给
SELECT SUM(NVL(A.JCYLJ,0)), SUM(NVL(A.GDXYLJ,0)), SUM(NVL(A.GRZHYLJ,0)),

SUM(NVL(A.HLF,0)), SUM(NVL(A.DFBCLXDY,0)), SUM(NVL(A.HYGDXBT,0)),

SUM(NVL(A.DYZE,0))
FROM YL_DYXMBD A, YL_LTXJBXX B, VW_RYJBXX C
WHERE A.GRBH = B.GRBH AND

A.GRBH = C.GRBH AND

C.DWBH = :b1 AND

B.JZSJ = :b2 AND

B.YWZJBZ = '2'
创建了存储大纲,在存储过程中就可以使用了?
而不用管
C.DWBH = :b1 ANDB.JZSJ = :b2 AND中是b1/b2还是c1/c2,d1/d2?
btw:在用户login的时候会执行execute immediate 'alter session set use_stored_outlines = zfyj_catalog';

千问 · 2014-2-19 11:55:14

是的，oracle的sql 解析的时候和原来定义变量名称无关

千问 · 2014-2-19 11:55:14

3k.
受教.

千问 · 2014-2-19 11:55:14

我最近也在研究这个问题.看看我的文档,不知是否能你有些帮助.
http://space.itpub.net/?uid-9375-action-viewspace-itemid-606147