欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-03 17:58:18
oracle优化——如何查看语句的准确的执行计划(explainplan可能不是真实的) 建虚拟索引 hr@ prod> create index test_ix...

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