会出来新的一个Explain Plan Window，可以选中sql按F5，它一般会自动New那个Window，不行的话你也可以手工press the New button on the toolbar and select Explain Plan Window. you can type the SQL statement, after pressing the Execute button on the toolbar the execution plan is displayed in the bottom half of the window:
1.sqlplus 设置参数 set atuotrace on SET AUTOTRACE OFF --No AUTOTRACE report is generated. This is the default. SET AUTOTRACE ON EXPLAIN --The AUTOTRACE report shows only the optimizer execution path. SET AUTOTRACE ON STATISTICS --The AUTOTRACE report shows only the SQL statement execution statistics. SET AUTOTRACE ON --The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics. SET AUTOTRACE TRACEONLY --Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed. 然后执行语句 在语句的结果后面会有执行计划。 2.sqlplus 使用DBMS_XPLAN包。 详尽信息见10g官方文档B19306_01\B19306_01\appdev.102\b14258\d_xplan.htm#sthref9740 例子： 示例： SQL> EXPLAIN PLAN FOR 2 SELECT * FROM test;已解释。SQL> SET LINESIZE 130 SQL> SET PAGESIZE 0 SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); Plan hash value: 1357081020-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 48 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 16 | 48 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------已选择8行。3.plsql developer 在sql window 窗口写sql，然后按F5，则会出现执行计划窗口。
主要看全表扫的和cost，或者执行计划走错了，hash_join等，比如你这里有很多table access full，一般需要加索引,大表where条件里的列一般需要有索引列。有些执行计划跟预期不太一致，可以加hint，analysis table等优化。
DBA一般是从动态性能视图V$sqlarea等里抓出来最消耗资源的sql。
深入理解的话建议看下oracle的官方的书:Performance Tuning Guide and Reference，
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533.pdf
会出来新的一个Explain Plan Window，可以选中sql按F5，它一般会自动New那个Window，不行的话你也可以手工press the New button on the toolbar and select Explain Plan Window. you can type the SQL statement, after pressing the Execute button on the toolbar the execution plan is displayed in the bottom half of the window:
1.sqlplus
设置参数 set atuotrace on
SET AUTOTRACE OFF --No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN --The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS --The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON --The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY --Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed. 然后执行语句
在语句的结果后面会有执行计划。
2.sqlplus
使用DBMS_XPLAN包。
详尽信息见10g官方文档B19306_01\B19306_01\appdev.102\b14258\d_xplan.htm#sthref9740
例子：
示例：
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM test;已解释。SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 48 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 16 | 48 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------已选择8行。3.plsql developer
在sql window 窗口写sql，然后按F5，则会出现执行计划窗口。
主要看的是你运行的那个SQL，进行的是不是全表扫描，以及cost是大还是小。