Oracle優化——如何檢視語句的準確的執行計劃（explain plan可能不是真實的）

參見 Oracle效能優化求生指南。

建虛擬索引
HR@ prod> create index test_ix1 on employees4 ( employee_id ) nosegment;
HR@ prod> alter session set "_use_nosegment_indexes" = true ;
HR@ prod> set autotrace on
HR@ prod> select /*oooooooooooo*/  * from employees4 where employee_id = 100 ; 

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03
AD_PRES         24000                                      90



Execution Plan
----------------------------------------------------------
Plan hash value: 499133838

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   133 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES4 |     1 |   133 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IX1   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEE_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

檢視真正的執行計劃，需要知道SQL_ID和CHILD_NUBMER。
HR@ prod> conn / as sysdba
Connected.
SYS@ prod> select sql_id , child_number from v$sql where sql_text like 'select /*oooooooooooo*/%' ;

SQL_ID        CHILD_NUMBER
------------- ------------
gtx1pw85bgqz0            0

SYS@ prod> select * from table(dbms_xplan.display_cursor('gtx1pw85bgqz0' , 0 )) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  gtx1pw85bgqz0, child number 0
-------------------------------------
select /*oooooooooooo*/  * from employees4 where employee_id = 100

Plan hash value: 2568047056

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES4 |     1 |   133 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("EMPLOYEE_ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


22 rows selected.
實際中並沒有索引可以用，Oracle走的是全表掃描。