对于自适应游标共享的一点补充(五)
0 0.000007 0.000017
00000000DD40C0E0 2679189014 082txyqgv2bhq 0 =VR 0 0.000007 0.000008
SQL> exec :vr := 1
PL/SQL 过程已成功完成。
SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;
SUM(ID)
----------
1
SQL> @show_sql
SQL_TEXT EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y Y
SQL> exec :vr := 8
PL/SQL 过程已成功完成。
SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;
SUM(ID)
----------
24512
SQL> @show_sql
SQL_TEXT EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y Y
SQL> @show_sel
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 2679189014 082txyqgv2bhq 3 =VR 0 0.000007 0.001074
00000000DD40C0E0 2679189014 082txyqgv2bhq 2 =VR 0 0.000007 0.000034
00000000DD40C0E0 2679189014 082txyqgv2bhq 1 =VR 0 0.000007 0.000017
00000000DD40C0E0 2679189014 082txyqgv2bhq 0 =VR 0 0.000007 0.000008
SQL> exec :vr := 14
PL/SQL 过程已成功完成。
SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;
SUM(ID)
----------
100659200
SQL>
@show_sql
SQL_TEXT EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y Y
SQL> @show_sel
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 2679189014 082txyqgv2bhq 4 =VR 0 0.000007 0.068751
00000000DD40C0E0 2679189014 082txyqgv2bhq 3 =VR 0 0.000007 0.001074
00000000DD40C0E0 2679189014 082txyqgv2bhq 2 =VR 0 0.000007 0.000034
00000000DD40C0E0 2679189014 082txyqgv2bhq 1 =VR 0 0.000007 0.000017
00000000DD40C0E0 2679189014 082txyqgv2bhq 0 =VR 0 0.000007 0.000008
SQL> exec :vr := 15
PL/SQL 过程已成功完成。
SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;
SUM(ID)
----------
SQL> @show_sql
SQL_TEXT EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr 1 Y Y N
select /*+ b