set autotrace on 產生不準確的執行計劃

lsq_008發表於2012-01-13
SQL> select count(*) from test;

  COUNT(*)
----------
    500000

SQL> select count(*) from test where id=498671;

  COUNT(*)
----------
         1

在id上有索引，顯然如果查where id=498671肯定會走索引：
 
SQL> exec :x :=498671

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> set autot on
SQL> select * from test where id = :x;

        ID NAME
---------- ------------------------------------------------------------
    498671 498671


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   499K|  4386K|   269   (3)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST |   499K|  4386K|   269   (3)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=TO_NUMBER(:X))


Statistics
----------------------------------------------------------
        710  recursive calls
          0  db block gets
        116  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          1  rows processed

autotrace顯示的執行計劃是全表掃描，而實際的執行計劃真的如此嗎？

SQL> set autot off
SQL> select sql_id ,sql_text from v$sqlarea where sql_text like '%where id = :x%';

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
32t8xz6c64um5
select sql_id ,sql_text from v$sqlarea where sql_text like '%where id = :x%'

8gaqs0a8u2327
select * from test where id = :x

f3b7q3xr8angs
EXPLAIN PLAN SET STATEMENT_ID='PLUS1053' FOR select * from test where id = :x


SQL> select * from table(dbms_xplan.display_cursor('8gaqs0a8u2327','','basic +peeked_binds'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from test where id = :x

Plan hash value: 2473784974

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|   2 |   INDEX RANGE SCAN          | IDX_TEST |
------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 498671


19 rows selected.

再來看不用繫結變數時autotrace顯示的執行計劃：

SQL> set autot on                         
SQL> select * from test where id =498671;

        ID NAME
---------- ------------------------------------------------------------
    498671 498671


Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974

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

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

   2 - access("ID"=498671)


Statistics
----------------------------------------------------------
       1097  recursive calls
          0  db block gets
        201  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         27  sorts (memory)
          0  sorts (disk)
          1  rows processed

這一次是正確的執行計劃。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-714889/，如需轉載，請註明出處，否則將追究法律責任。

相關文章