autotrace显示绑定变量执行计划不准确

来源：互联网发布：老马说编程编辑：程序博客网时间：2024/05/02 06:11

SQL> var a varchar2(10);
SQL> exec :a:='INVALID';

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select object_id,status from t1 where status=:a;
OBJECT_ID STATUS
---------- -------
      92 INVALID
      93 INVALID
      94 INVALID
      95 INVALID
      96 INVALID
      97 INVALID
      98 INVALID
      99 INVALID
   100 INVALID
   73871 INVALID
   73846 INVALID

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 23680 | 462K| 284 (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1 | 23680 | 462K| 284 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("STATUS"=:A)

Statistics
----------------------------------------------------------
      403  recursive calls
      0  db block gets
      99  consistent gets
      19  physical reads
      0  redo size
   2543  bytes sent via SQL*Net to client
      589  bytes received via SQL*Net from client
      8  SQL*Net roundtrips to/from client
      7  sorts (memory)
      0  sorts (disk)
      99  rows processed

SQL> select status,count(1) from t1 group by status;

STATUS COUNT(1)
------- ----------
IND             2
VALID       70939
INVALID       99

SQL> select object_id ,status from t1 where status='INVALID';

OBJECT_ID STATUS
---------- -------
      92 INVALID
      93 INVALID
      94 INVALID
      95 INVALID
      96 INVALID
      97 INVALID
      98 INVALID
      99 INVALID
   100 INVALID
   73871 INVALID
   73846 INVALID
Execution Plan
----------------------------------------------------------
Plan hash value: 14748218

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

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT          |       | 79 |  1580 |    3 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T1    | 79 |  1580 |    3 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | INDE_T1 | 79 |    |    1 (0)| 00:00:01 |

--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("STATUS"='INVALID')

SQL>

因为使用绑定变量的时候oracle会进行peek bind，但是使用autotrace的时候是没有办法使用的因此有可能出现错的执行计划，遇到这种情况的时候最好使用dbms_xplan来展示或者使用10046来展示真正的执行计划，看下面的的测试

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>

SQL> create table t2 as select * from t1;

Table created.

SQL>
SQL> create index ind_t2_status on t2(status) ;

Index created.

SQL>
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ZYF','T2',method_opt => 'for all indexed columns  size skewonly',cascade => TRUE,no_invalidate => FALSE);

PL/SQL procedure successfully completed.

SQL> select status,count(1) from t1 group by status;

STATUS COUNT(1)
------- ----------
INVALID       130
VALID    2016984
IND          62

SQL>  var a varchar2(10);
SQL>  exec :a:='INVALID';

PL/SQL procedure successfully completed.

SQL>
SQL>  set autot trace
SELECT  object_id,status FROM  T2  WHERE  STATUS = :a;SQL>

130 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 672K| 12M|  7898 (1)| 00:01:35 |
|*  1 |  TABLE ACCESS FULL| T2 | 672K| 12M|  7898 (1)| 00:01:35 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("STATUS"=:A)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      112  consistent gets             --->这个很奇怪，表那么大，如果走全表的话，consistent gets 这个值太不正常了
      0  physical reads
      0  redo size
   3378  bytes sent via SQL*Net to client
      612  bytes received via SQL*Net from client
      10  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      130  rows processed

--查看

SQL> set lines 180
SQL>
SQL>
SQL> select object_id,status FROM t2 where status='INVALID';

130 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1740296156

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |             | 361 |  7220 |    9 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T2          | 361 |  7220 |    9 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | IND_T2_STATUS | 361 |    |    3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("STATUS"='INVALID')

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      112  consistent gets             -->这个竟然和全表扫描的值一样
      0  physical reads
      0  redo size
   3378  bytes sent via SQL*Net to client
      612  bytes received via SQL*Net from client
      10  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      130  rows processed

SQL> SELECT  /*+ full(T2) */* FROM  T2  WHerE  STATUS = :a;

130 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 672K| 62M|  7903 (1)| 00:01:35 |
|*  1 |  TABLE ACCESS FULL| T2 | 672K| 62M|  7903 (1)| 00:01:35 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("STATUS"=:A)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   28743  consistent gets                         -->从这个看，说明第一个full t2的全表扫描显示的有问题.
      0  physical reads
      0  redo size
   10986  bytes sent via SQL*Net to client
      612  bytes received via SQL*Net from client
      10  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      130  rows processed

SQL>

--这里我们做个10046的trace跟踪一下最原始的带绑定变量的sql
注意我把select 从大写改为小写，为生成一个新的执行计划

sql> exit

sqlplus zyf/zyf

SQL>
SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL>  select object_id,status from T2  WHERE  STATUS = :a;

OBJECT_ID STATUS
---------- -------
   56921 INVALID
   56966 INVALID
   64886 INVALID
   ....


SQL>
SQL> alter session set events '10046 trace name context off';

Session altered.

--分析trace

tkprof nitmsdb1_ora_1462.trc zyf.log

$ more zyf.log

select object_id,status
from
   T2  WHERE  STATUS = :a


call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.00    0.00       0       0       0          0
Execute    1    0.00    0.00       0       0       0          0
Fetch    10    0.00    0.00       0       112       0       130
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    12    0.00    0.00       0       112       0       130

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         130       130       130  TABLE ACCESS BY INDEX ROWID T2 (cr=112 pr=0 pw=0 time=3113 us cost=9 size=722
0 card=361)
         130       130       130 INDEX RANGE SCAN IND_T2_STATUS (cr=12 pr=0 pw=0 time=244 us cost=3 size=0 ca
rd=361)(object id 65914)

发现query=112 ，访问表不是全表扫描，而是访问索引