查看ORACLE的执行计划

最新推荐文章于 2012-12-25 15:42:03 发布

lylnina0421

最新推荐文章于 2012-12-25 15:42:03 发布

阅读量465

点赞数

分类专栏： oracle 文章标签： AUTOTRACE autotrace Explain explain sql SQL Sql

本文链接：https://blog.csdn.net/lylnina0421/article/details/8433749

版权

oracle 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

方法一：EXPLAIN，不执行该语句的情况下，列出语句的执行计划。

方法二：AUTOTRACE,需要执行该语句，再执行完成后，列出语句的执行计划。

两种方法都需要都需要用到PLAN_TABLE表。对于9i以前的版本（10g以后不用），需要执行以下步骤，创建该表

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utlxplan.sql

执行以下步骤可以让所有用户都能使用该表

CREATE PUBLIC SYNONYM plan_table FOR plan_table;
GRANT ALL ON plan_table TO PUBLIC;

方法一：EXPLAIN

常规使用语法：
explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ]
FOR < sql_statement >
STATEMENT_ID：为分析的SQL语句设置ID，用于区分PLAN_TABLE中的其他语句。
SQL_STATEMENT：需要分析的SQL语句

事例：

SQL> explain plan set statement_id='TEST_EMP' for
2 select * from scott.emp;

Explained.

使用DBMS_XPLAN.DISPLAY来查看执行计划

SQL> set linesize 200

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','TEST_EMP','ALL'));

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | | |
---------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
13 recursive calls
12 db block gets
22 consistent gets
0 physical reads
0 redo size
1826 bytes sent via SQL*Net to client
480 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
21 rows processed

查看plan_table表可以发现，实际上explain的执行计划都存在这个表里，dbms_xplan实际上是显示表中的内容

SQL> select distinct statement_id from plan_table;

STATEMENT_ID
------------------------------------------------------------
TEST_EMP

方法二：AUTOTRACE

使用前需要创建一个名为plustrace的role

sqlplus / as sysdba @$ORACLE_HOME/sqlplus/admin/plustrce.sql

执行以下步骤可以让所有用户都能使用autotrace功能

SQL>grant plustrace to public;

语法：

set autotrace on; 打开、显示结果+统计+执行计划

set autotrace off; 关闭

set autotrace on statistics; 打开、显示结果+统计

set autotrace on explain; 打开、显示结果+执行计划

set autotrace traceonly; 打开、不显示结果、仅显示统计+执行计划

set autotrace traceonly explain; 打开、不显示结果、仅显示执行计划

set autotrace traceonly statistics; 打开、不显示结果、仅显示统计

另：一般配合set timing on使用（查看语句执行时间），关闭时set timing off

SQL> set autotrace traceonly;
SQL> select * from scott.emp;

14 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| EMP |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          8 consistent gets
          0 physical reads
          0 redo size
       1591 bytes sent via SQL*Net to client
        469 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         14 rows processed

SQL> set autot off;

==================================================================================================

注意：

1. set autotrace on功能会执行语句，如果只打算分析语句，但不想执行（如update、insert等），请用explain或PL/SQL等第三方工具。

2. explain仅能分析“属于”用户的对象、如果仅有select权限，explain会返回一个错误。

（后续注意2测试中并没报错）

3. 逻辑读=db block gets + consistent gets

4. 如果测试physical reads，会发现同一个语句执行一次后，第二次physical reads就为0了，这是因为语句第一次执行后，将数据写入了buffer_cache中，这时可以清除buffer_cache。

SQL> alter system flush buffer_cache;

System altered.

==================================================================================================

注意1测试：

SQL> desc e;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ENAME                                              VARCHAR2(10)
SAL                                                NUMBER(7,2)

SQL> select * from e;

no rows selected

SQL> explain plan set statement_id='INSERT_E' for
2 insert into e select ename,sal from emp;

Explained.

SQL> select * from e;

no rows selected

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','INSERT_E','ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |    14 |   140 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| EMP |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - SEL$1 / EMP@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]

18 rows selected.

==================================================================================================

注意2测试：

SQL> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn / as sysdba
Connected.
SQL> grant all on hr.employees to scott;

Grant succeeded.

SQL> conn scott/oracle
Connected.
SQL> select count(*) from hr.employees;

COUNT(*)
----------
107

SQL> explain plan set statement_id='SELECT_HR' for
2 select * from hr.employees;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','SELECT_HR','ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |   107 | 7276 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| EMPLOYEES |   107 | 7276 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - SEL$1 / EMPLOYEES@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPLOYEES"."EMPLOYEE_ID"[NUMBER,22],
       "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
       "EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
       "EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20],
       "EMPLOYEES"."HIRE_DATE"[DATE,7], "EMPLOYEES"."JOB_ID"[VARCHAR2,10],

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       "EMPLOYEES"."SALARY"[NUMBER,22], "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22]
       , "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
       "EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]

25 rows selected.

SQL> conn / as sysdba
Connected.

SQL> revoke all on hr.employees from scott;

Revoke succeeded.

SQL> grant select on hr.employees to scott;

Grant succeeded.

SQL> conn scott/oracle
Connected.
SQL> explain plan set statement_id='SELECT_HR2' for
2 select * from hr.employees;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','SELECT_HR2','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - SEL$1 / EMPLOYEES@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

25 rows selected.

SQL> select OWNER , TABLE_NAME from all_tables where table_name='EMPLOYEES';

OWNER
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
HR
EMPLOYEES

==================================================================================================

lylnina0421

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
查看ORACLE的执行计划

方法一：EXPLAIN，不执行该语句的情况下，列出语句的执行计划。方法二：AUTOTRACE,需要执行该语句，再执行完成后，列出语句的执行计划。两种方法都需要都需要用到PLAN_TABLE表。对于9i以前的版本（10g以后不用），需要执行以下步骤，创建该表sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utlxplan.sql
复制链接

扫一扫

专栏目录