Oracle优化——如何查看语句的准确的执行计划（explainplan可能不是真实的）

oracle优化——如何查看语句的准确的执行计划（explainplan可能不是真实的）

建虚拟索引
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走的是全表扫描。