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 onHR@ 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-03AD_PRES         24000                                      90Execution 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 sysdbaConnected.SYS@ prod> select sql_id , child_number from v$sql where sql_text like 'select /*oooooooooooo*/%' ;SQL_ID        CHILD_NUMBER------------- ------------gtx1pw85bgqz0            0SYS@ 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 = 100Plan 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走的是全表扫描。