gather_plan_statistics提示的使用，获取详细的执行统计信息

SQL> set linesize 150

SQL> set pagesize 2000

SQL> set autotrace traceonly exp

SQL> select avg(SALARY),DEPARTMENT_NAME from

employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;

Execution Plan

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

Plan hash value: 3294250112

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

| Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |     | 27 | 621 |   5  (20)| 00:00:01 |

|   1 |  HASH GROUP BY       |     | 27 | 621 |   5  (20)| 00:00:01 |

|   2 |   NESTED LOOPS       |     | 106 |  2438 |   4   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL       | EMPLOYEES   | 107 | 749 |   3   (0)| 00:00:01 |

|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |   1 | 16 |   1   (0)| 00:00:01 |

|*  5 |     INDEX UNIQUE SCAN       | DEPT_ID_PK  |   1 |     |   0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

SQL> show parameter cursor_sharing

NAME      TYPE VALUE

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

cursor_sharing      string EXACT

SQL>  show parameter statistics_level

NAME      TYPE VALUE

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

statistics_level      string TYPICAL

SQL> set autotrace off;

SQL> select    avg(SALARY),DEPARTMENT_NAME from

employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;

SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

或 select * from table(dbms_xplan.display_cursor(sql_id => 'd9ayadktcs0g2',format => 'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT

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

SQL_ID bctzu9xuxay18, child number 0

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

select   avg(SALARY),DEPARTMENT_NAME from employees e,departments d

where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME

Plan hash value: 3294250112

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

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

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

|   1 |  HASH GROUP BY       |     |    1 |    27 |     11 |00:00:00.01 |   219 |

|   2 |   NESTED LOOPS       |     |    1 |   106 |    106 |00:00:00.01 |   219 |

|   3 |    TABLE ACCESS FULL       | EMPLOYEES   |    1 |   107 |    107 |00:00:00.01 |     7 |

|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 107 |     1 |    106 |00:00:00.01 |   212 |

|*  5 |     INDEX UNIQUE SCAN       | DEPT_ID_PK  | 107 |     1 |    106 |00:00:00.01 |   106 |

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

Predicate Information (identified by operation id):

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

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

SQL> select t.*

from v$sql s

   , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID' ;

Enter value for sql_id: bctzu9xuxay18

old   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID'

new   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = 'bctzu9xuxay18'

PLAN_TABLE_OUTPUT

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

SQL_ID bctzu9xuxay18, child number 0

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

select avg(SALARY),DEPARTMENT_NAME from

employees e,departments d where

e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME

Plan hash value: 3294250112

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

| Id  | Operation       | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

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

|   1 |  HASH GROUP BY       |     |    1 |    27 |   621 |     5  (20)| 00:00:01 |     11 |00:00:00.01 |   219 |

|   2 |   NESTED LOOPS       |     |    1 |   106 |  2438 |     4 (0)| 00:00:01 |    106 |00:00:00.01 |   219 |

|   3 |    TABLE ACCESS FULL       | EMPLOYEES   |    1 |   107 |   749 |     3 (0)| 00:00:01 |    107 |00:00:00.01 |     7 |

|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 107 |     1 |    16 |     1 (0)| 00:00:01 |    106 |00:00:00.01 |   212 |

|*  5 |     INDEX UNIQUE SCAN       | DEPT_ID_PK  | 107 |     1 |       |     0 (0)|       |    106 |00:00:00.01 |   106 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1

   3 - SEL$1 / [email protected]$1

   4 - SEL$1 / [email protected]$1

   5 - SEL$1 / [email protected]$1

Predicate Information (identified by operation id):

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

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Column Projection Information (identified by operation id):

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

   1 - "DEPARTMENT_NAME"[VARCHAR2,30], AVG("SALARY")[22]

   2 - "SALARY"[NUMBER,22], "DEPARTMENT_NAME"[VARCHAR2,30]

   3 - "SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]

   4 - "DEPARTMENT_NAME"[VARCHAR2,30]

   5 - "D".ROWID[ROWID,10]

SQL> alter session set statistics_level=ALL;  ---不推荐

Session altered.