如何显示执行计划的真实开销?

执行计划如何显示e-rows,a-rows？

 

今天一个学生问我在执行计划如何显示E-rows,A-rows

通常有两种方式：

 

1.  加入hint:

 

SQL> create table daodao (a integer);

 

表已创建。

 

SQL> insert into daodao select object_idfrom dba_objects;

 

已创建 76167 行。

 

SQL> commit;

 

提交完成。

 

SQL> create index daodao_idx ondaodao(a);

 

索引已创建。

 

SQL> select /*+ gather_plan_statistics*/  * from daodao where a= 5;

 

        A

----------

        5

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

SQL_ID gqtauwwjxp3tv, child number 0

-------------------------------------

select /*+ gather_plan_statistics */  * from daodao where a= 5

 

Plan hash value: 671796503

 

--------------------------------------------------------------------------------

---------

| Id | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time  |

Buffers |

--------------------------------------------------------------------------------

---------

|   0| SELECT STATEMENT |            |      1 |       |      1 |00:00:00.01 |

     3 |

|*  1|  INDEX RANGE SCAN| DAODAO_IDX |      1 |     1 |      1 |00:00:00.01 |

     3 |

--------------------------------------------------------------------------------

---------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   1- access("A"=5)

 

Note

-----

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

 

 

 

2.  修改会话参数:

 

SQL> show parameter  statis

optimizer_use_pending_statistics     boolean    FALSE

statistics_level                     string      TYPICAL

timed_os_statistics                  integer     0

timed_statistics                     boolean     TRUE

SQL> alter session setstatistics_level=all;

 

会话已更改。

 

SQL> select   * from daodao where a= 5;

        5

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

SQL_ID autxwkucwhux6, child number 0

-------------------------------------

select  * from daodao where a= 5

 

Plan hash value: 671796503

 

--------------------------------------------------------------------------------

---------

| Id | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time  |

Buffers |

--------------------------------------------------------------------------------

---------

|   0| SELECT STATEMENT |            |      1 |       |      1 |00:00:00.01 |

     3 |

|*  1|  INDEX RANGE SCAN| DAODAO_IDX |      1 |     1 |      1 |00:00:00.01 |

     3 |

--------------------------------------------------------------------------------

---------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   1- access("A"=5)

 

Note

-----

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

 

 

已选择22行。