获取统计信息
1）查看库高速缓存中的执行计划，方法1：
HAOHAO@ncjs> select /*+ gather_plan_statistics */ empno,ename from scott.emp where ename='KING';
EMPNO ENAME
---------- ----------
7839 KING
已用时间: 00: 00: 00.03
HAOHAO@ncjs> set serveroutput off
HAOHAO@ncjs> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID 1qscmtg78f5n0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ empno,ename from scott.emp where
ename='KING'
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
已选择18行。
已用时间: 00: 00: 00.06
2）
HAOHAO@ncjs> select /*+ my_emptest */ empno,ename from scott.emp where ename='KING';
EMPNO ENAME
---------- ----------
7839 KING
已用时间: 00: 00: 00.01
HAOHAO@ncjs> Select a.SQL_ID,a.CHILD_NUMBER,a.SQL_TEXT From v$sql a Where a.SQL_TEXT Like '%my_emptest%';
SQL_ID CHILD_NUMBER SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------
cssuw4qjh02k8 0 Select a.SQL_ID,a.CHILD_NUMBER,a.SQL_TEXT From v$sql a Where a.SQL_TEXT Like '%my_emptest%'
cajrtavb148dr 0 Select a.SQL_ID,a.CHILD_NUMBER,a.SQL_TEXT,a.SQL_FULLTEXT From v$sql a Where a.SQL_TEXT Like '%my_emptest%'
28swg8p101btp 0 select /*+ my_emptest */ empno,ename from scott.emp where ename='KING'
74nz0zbj76j2k 0 select /*+ my_emptest */ empno,ename from scott.emp where ename='KING'
已用时间: 00: 00: 00.06
HAOHAO@ncjs> select * from table(dbms_xplan.display_cursor('28swg8p101btp',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
SQL_ID 28swg8p101btp, child number 0
-------------------------------------
select /*+ my_emptest */ empno,ename from scott.emp where ename='KING'
Plan hash value: 3956160932
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
已选择23行。
已用时间: 00: 00: 00.03
SQL> select * from table(dbms_xplan.display_cursor('28swg8p101btp',0,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 28swg8p101btp, child number 0
-------------------------------------
select /*+ my_emptest */ empno,ename from scott.emp where ename='KING'
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("ENAME"='KING')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
28 rows selected
3）
HAOHAO@ncjs> select /*+ gather_plan_statistics MY_TEST2 */ empno,ename from scott.emp where ename='KING';
EMPNO ENAME
---------- ----------
7839 KING
已用时间: 00: 00: 00.00
HAOHAO@ncjs> Select a.SQL_ID,a.CHILD_NUMBER,a.SQL_TEXT From v$sql a Where a.SQL_TEXT Like '%MY_TEST2%';
SQL_ID CHILD_NUMBER SQL_TEXT
-------------------------------------------------------------------
1fd54dn5kh18d 0 Select a.SQL_ID,a.CHILD_NUMBER,a.SQL_TEXT From v$sql a Where a.SQL_TEXT Like '%MY_TEST2%'
2gczrrt5q42ng 0 select /*+ gather_plan_statistics MY_TEST2 */ empno,ename from scott.emp where ename='KING'
f2k2f6fhy1qtj 0 select /*+ gather_plan_statistics MY_TEST2 */ empno,ename from scott.emp where ename='KING'
0017rdb4yan7a 0 Select a.SQL_ID,a.CHILD_NUMBER,a.SQL_TEXT,a.SQL_FULLTEXT From v$sql a Where a.SQL_TEXT Like '%MY_TEST2%'
已用时间: 00: 00: 00.01
HAOHAO@ncjs> select * from table(dbms_xplan.display_cursor('f2k2f6fhy1qtj',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f2k2f6fhy1qtj, child number 0
-------------------------------------
select /*+ gather_plan_statistics MY_TEST2 */ empno,ename from scott.emp
where ename='KING'
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 7 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
已选择18行。
已用时间: 00: 00: 00.07
HAOHAO@ncjs>
4）
HAOHAO@ncjs> explain plan for select empno,ename from scott.emp where ename='KING';
HAOHAO@ncjs> select * from table(dbms_xplan.display(format=>'ALL'));
select * from table(dbms_xplan.display(format=>'ALL'))
*
第 1 行出现错误:
ORA-00907: 缺失右括号
HAOHAO@ncjs> select * from table(dbms_xplan.display('ALL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
ERROR: cannot get definition for table 'ALL'
ORA-44002: 对象名无效
HAOHAO@ncjs> select * from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
已选择23行。
HAOHAO@ncjs> select * from table(dbms_xplan.display(null,Null,'ALL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
已选择23行。
已用时间: 00: 00: 00.03
5）
HAOHAO@ncjs> select empno,ename from scott.emp where ename='KING';
EMPNO ENAME
---------- ----------
7839 KING
已用时间: 00: 00: 00.00
HAOHAO@ncjs> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -COST BYTES'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
SQL_ID ayqnbx5fq2fzr, child number 0
-------------------------------------
select empno,ename from scott.emp where ename='KING'
Plan hash value: 3956160932
---------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|
---------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 | 10 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
已选择23行。
已用时间: 00: 00: 00.04
HAOHAO@ncjs> Select /*+ gather_plan_statistics */ empno,ename From scott.emp e,scott.dept d Where e.deptno=d.deptno And e.ename='JONES';
EMPNO ENAME
---------- ----------
7566 JONES
已用时间: 00: 00: 00.00
HAOHAO@ncjs> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fv8uzmxqqny2k, child number 0
-------------------------------------
Select /*+ gather_plan_statistics */ empno,ename From scott.emp
e,scott.dept d Where e.deptno=d.deptno And e.ename='JONES'
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 2 | 1 | 2 |00:00:00.01 | 16 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("E"."ENAME"='JONES' AND "E"."DEPTNO" IS NOT NULL))
已选择18行。
已用时间: 00: 00: 00.04
HAOHAO@ncjs> select /*+ gather_plan_statistics */ empno,ename from scott.emp where ename='KING';
EMPNO ENAME
---------- ----------
7839 KING
已用时间: 00: 00: 00.01
HAOHAO@ncjs> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -COST BYTES'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
SQL_ID 1qscmtg78f5n0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ empno,ename from scott.emp where ename='KING'
Plan hash value: 3956160932
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 10 | 1 |00:00:00.01 | 8 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')
已选择17行。
已用时间: 00: 00: 00.03
6)
HAOHAO@ncjs> Select /*+ gather_plan_statistics */ empno,ename From scott.emp e,scott.dept d Where e.deptno=d.deptno And e.ename='JONES';
EMPNO ENAME
---------- ----------
7566 JONES
已用时间: 00: 00: 00.00
HAOHAO@ncjs> select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fv8uzmxqqny2k, child number 0
-------------------------------------
Select /*+ gather_plan_statistics */ empno,ename From scott.emp
e,scott.dept d Where e.deptno=d.deptno And e.ename='JONES'
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("E"."ENAME"='JONES' AND "E"."DEPTNO" IS NOT NULL))
已选择18行。
书中一些特性在Oracle11G才会有，如format参数设置、Reads显示灯。
待续
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14710393/viewspace-751804/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
不错的blog
请登录后发表评论
登录
全部评论
最新文章
- Install and configure VMware_vSphere 5
- Docker run OGG Classic Architecture container from CIFS with docker-volume-netsh
- 【OERR】 oerr ora 错误 12.2.0.1 【20000-39999】
- Oracle Online Redefinition在线重定义（下）
- Oracle Online Redefinition在线重定义（上）
- Oracle Online Redefinition在线重定义（中）
- Oracle中查找依赖的无效对象(invalid object)
- goldengate部署实施案例
- goldengate常用函数使用说明
- goldengate基于表复制
