玉面飞龙的BLOG

Explain plan有陷阱，有时候Explain plan看到的执行计划和实际的执行计划不一样。SQL_TRACE也有陷阱，tkprof看到的执行计划和应用程序实际执行的计划不一样。

先观察为什么SQL_TRACE可能产生不一致的执行计划。

SQL> create table t as select * from dba_objects order by object_name;
Table created.

SQL> create index tidxon_objectid on t(object_id);
Index created.

SQL> create index tidxon_created on t(created);
Index created.

SQL> analyze table t compute statistics;
Table analyzed.

SQL> alter session set optimizer_mode=choose;
Session altered.

SQL> var a number;
SQL> var b number;
SQL> exec :a:=1000;
PL/SQL procedure successfully completed.

SQL> exec :b:=1100;
PL/SQL procedure successfully completed.

SQL> var c varchar2(20)
SQL> exec :c:=to_char(sysdate+500,’yyyy-mm-dd’);
PL/SQL procedure successfully completed.

alter session set hash_join_enabled=false;

SQL> select object_id,created from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100;
no rows selected

执行几次；从v$sql_plan中找到执行计划

PLAN_TABLE_OUTPUT
——————————————————————————–
| Id  | Operation                    |  Name           | Rows  | Bytes | Cost  |
——————————————————————————–
|   0 | SELECT STATEMENT             |                 |       |       |    11 |
|*  1 |  FILTER                      |                 |       |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T               |     4 |    44 |    11 |
|*  3 |    INDEX RANGE SCAN          | TIDXON_CREATED  |    76 |       |     2 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 - filter(:A<=:B)
2 - filter(”OBJECT_ID”>=:A AND “OBJECT_ID”<=:B)
3 - access(”CREATED”>TO_DATE(:C,’yyyy-mm-dd’)+100)
Note: cpu costing is off

为什么会使用TIDXON_CREATED，而不使用tidxon_objectid ？是因为
NAME                    VALUE
——————— ——–
_optim_peek_user_binds     TRUE

观察该SQL的统计信息：分析4次，执行4次
SQL> select hash_value,sql_text,OPEN_VERSIONS,LOADED_VERSIONS,
USERS_OPENING,EXECUTIONS,LOADS,FIRST_LOAD_TIME,PARSE_CALLS from v$sql where hash_value=3905077835;
HASH_VALUE
———-
SQL_TEXT
————————————————————————————————————————————
OPEN_VERSIONS LOADED_VERSIONS USERS_OPENING EXECUTIONS      LOADS FIRST_LOAD_TIME
————- ————— ————- ———- ———- ———————————————————
PARSE_CALLS
———–
3905077835
select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100
0               1             0          4          1 2007-04-04/01:29:56
4

SQL> select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100;

观察该SQL的统计信息：分析5次，执行5次
SQL>  select hash_value,sql_text,OPEN_VERSIONS,LOADED_VERSIONS,USERS_OPENING,EXECUTIONS,LOADS,FIRST_LOAD_TIME,PARSE_CALLS
from v$sql where hash_value=3905077835;

HASH_VALUE
———-
SQL_TEXT
————————————————————————————————————————————
OPEN_VERSIONS LOADED_VERSIONS USERS_OPENING EXECUTIONS      LOADS FIRST_LOAD_TIME
————- ————— ————- ———- ———- ———————————————————
PARSE_CALLS
———–
3905077835
select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100
0               1             0          5          1 2007-04-04/01:29:56
5

关键在这里，设置sql_trace

SQL> alter session set sql_trace=true;
Session altered.

执行2次
SQL> exec :a:=1000;

SQL> exec :b:=1001;

SQL> exec :c:=to_char(sysdate-100,’yyyy-mm-dd’);

SQL> select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100;

SQL> select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100;

SQL>alter session set sql_trace=false;
Session altered.

关闭SQL_TRACE,再察看统计信息

SQL> select hash_value,sql_text,OPEN_VERSIONS,LOADED_VERSIONS,USERS_OPENING,EXECUTIONS,LOADS,FIRST_LOAD_TIME,PARSE_CALLS from v$sql where hash_value=3905077835;

HASH_VALUE
———-
SQL_TEXT
————————————————————————————————————————————
OPEN_VERSIONS LOADED_VERSIONS USERS_OPENING EXECUTIONS      LOADS FIRST_LOAD_TIME
————- ————— ————- ———- ———- ———————————————————
PARSE_CALLS
———–
3905077835
select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100
0               1             0          5          1 2007-04-04/01:29:56
5

3905077835
select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100
0               1             0          2          1 2007-04-04/01:29:56
2

会发现该SQL有2个Child了，新Child分析了2次，执行了2次；这说明当trace sql的时候，自动会新生成一个Child SQL；sql trace为这个新的Child SQL
产生了下面的跟踪文件。 Misses parse = 1 表示该Child SQL作了硬分析，就意味着该Child SQL执行计划有可能会发生改变。

********************************************************************************
select *
from
t where object_id>=:a and object_id<=:b and created > to_date(:c,
‘yyyy-mm-dd’) +100

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.01       0.00          0         10          0           4
——- ——  ——– ———- ———- ———- ———-  ———-
total        8      0.01       0.00          0         10          0           4
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31
Rows     Row Source Operation
——-  —————————————————
2  FILTER
2   TABLE ACCESS BY INDEX ROWID T
2    INDEX RANGE SCAN TIDXON_OBJECTID (object id 12293)
********************************************************************************

这里Child #2使用的是TIDXON_OBJECTID，而不是 Child #1的TIDXON_CREATED。这就是sql_trace的陷阱。

为什么会不同，因为_optim_peek_user_binds=TRUE

在次执行

SQL> select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100;

发现Child #1 的统计信息变化了，Child #2无变化。

SQL> select hash_value,sql_text,OPEN_VERSIONS,LOADED_VERSIONS,USERS_OPENING,EXECUTIONS,LOADS,FIRST_LOAD_TIME,PARSE_CALLS from v$sql where hash_value=3905077835;

HASH_VALUE
———-
SQL_TEXT
————————————————————————————————————————————
OPEN_VERSIONS LOADED_VERSIONS USERS_OPENING EXECUTIONS      LOADS FIRST_LOAD_TIME
————- ————— ————- ———- ———- ———————————————————
PARSE_CALLS
———–
3905077835
select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100
0               1             0          6          1 2007-04-04/01:29:56
6

3905077835
select * from t where object_id>=:a and object_id<=:b and created > to_date(:c,’yyyy-mm-dd’) +100
0               1             0          2          1 2007-04-04/01:29:56
2

这说明SQL TRACE会影响到游标的共享，因为Hard Parse的原因会产生与实际SQL运行不一样的执行计划。
可以从V$SQL_SHARED_CURSOR中看到SQL Plan不相同的原因。

在此例中，设置_optim_peek_user_binds=false,sql_trace就不会产生陷阱了。