方法一:EXPLAIN,不执行该语句的情况下,列出语句的执行计划。
方法二:AUTOTRACE,需要执行该语句,再执行完成后,列出语句的执行计划。
两种方法都需要都需要用到PLAN_TABLE表。对于9i以前的版本(10g以后不用),需要执行以下步骤,创建该表
sqlplus
GRANT ALL ON plan_table TO PUBLIC;
explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ]
FOR < sql_statement >
STATEMENT_ID:为分析的SQL语句设置ID,用于区分PLAN_TABLE中的其他语句。
SQL_STATEMENT:需要分析的SQL语句
----------------------------------------------------------
Plan hash value: 2137789089
| Id
---------------------------------------------------------------------------------------------
|
|
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
------------------------------------------------------------
TEST_EMP
使用前需要创建一个名为plustrace的role
SQL> set autotrace traceonly;
SQL> select * from scott.emp;
14 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id
--------------------------------------------------------------------------
|
|
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
SQL> set autot off;
==================================================================================================
注意:
1.
2.
(后续注意2测试中并没报错)
3.
4.
SQL> alter system flush buffer_cache;
System altered.
==================================================================================================
注意1测试:
SQL> desc e;
SQL> select * from e;
no rows selected
SQL> explain plan set statement_id='INSERT_E' for
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
--------------------------------------------------------------------------
|
|
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
18 rows selected.
==================================================================================================
注意2测试:
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;
----------
SQL> explain plan set statement_id='SELECT_HR' for
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','SELECT_HR','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id
-------------------------------------------------------------------------------
|
|
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','SELECT_HR2','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id
-------------------------------------------------------------------------------
|
|
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
25 rows selected.
SQL> select OWNER , TABLE_NAME from all_tables where table_name='EMPLOYEES';
OWNER
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
HR
EMPLOYEES
==================================================================================================