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走的是全表扫描。
