sql優化是DBA的日常工作，sql的性能好壞極大的影響了資料庫的性能。一條性能很差的sql很有可能導緻資料庫當機。為了滿足sql的量化分析和優化，oracl提供了大量的工具。如：explain、autotrace、sql trace、sql profile、sql access advisor、sql tuning advisor、ADDM、ASH、AWR等。
sql的性能好壞，通常情況下我們有幾個量化名額：
時間消耗：elapsed time、cpu time
記憶體消耗：db block gets、consistent gets
I/O消耗 ：physical reads，physical writes
解析次數：parses、hard prases、soft prases
如果要分析某條SQL的性能問題，通常我們要先看SQL的執行計劃，看看SQL的每一步執行是否存在問題。如果一條SQL平時執行的好好的，卻有一天突然性能很差，如果排除了系統資源和阻塞的原因，那麼基本可以斷定是執行計劃出了問題。
檢視執行計劃的方法：
一、explain plan：
（1）最古老的檢視方法：
建立plan_table表：
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
分析sql語句執行計劃
explain plan for select * from emp;
檢視sql語句執行計劃
@$ORACLE_HOME/rdbms/admin/utlxpls.sql或者@$ORACLE_HOME/rdbms/admin/utlxplp.sql
（2）dbms_xplan：
在10g之後，我通過dbms_xplan包檢視執行計劃更加友善，功能也更強大了。
dbms_xplan.display --檢視v$sql中的執行計劃
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
dbms_xplan.display_cursor --顯示任何加載到corsor cache的執行計劃
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
dbms_xplan.display_awr --顯示awr中語句的執行計劃
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
分析sql語句執行計劃
explain plan [SET STATEMENT_ID=''] for ...
檢視sql語句執行計劃
select * from table(dbms_xplan.display);
或者
select PLAN_TABLE_OUTPUT from table(dbms_xplan.display());
二、autotrace
autotrace的文法：
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
一般sys和system使用者set autotrace不會出現問題，但是其他普通使用者set autotrace的時候可能會遇到SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled的錯誤，解決辦法參照： http://blog.csdn.net/dbaheng/article/details/16826719
SCOTT @oemrep>set autotrace traceonly
SCOTT @oemrep>select * from 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 | 448 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1630 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
三、sql trace
産生sql trace的方法有很多種。根據我們的需求，可以執行不同的語句來産生我們所需要的sql trace。
（1） alter system set
目前會話的sql trace
alter session set sql_trace=true;
alter session set sql_trace=false;
目前執行個體的sql trace
alter system set sql_trace=true;
alter system set sql_trace=flase;
（2） dbms_session包
目前會話的sql trace
exec dbms_session.set_sql_trace(true);
exec dbms_session.set_sql_trace(false);
（3） dbms_system and dbms_monitor包
在10R2以前，oracle資料庫使用dbms_system來實作對其他session的sql trace，該包功有很多功能，但也存在着很多缺陷。在10GR2以後，資料庫預設沒有dbms_system包，dbms_system 包也從官方文檔中消失了。取而代之的是dbms_monitor，oracle極力推薦使用dbms_monitor包。
使用dbms_system指定會話的sql trace
exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
我們主要介紹下dbms_monitor包:
統計資訊收集
1. 以用戶端辨別符，收集統計資訊
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE(client_id => 'OE.OE');--client_id in the CLIENT_IDENTIFIER column in V$SESSION.
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_DISABLE(client_id => 'OE.OE');
2.收集服務，子產品，行為統計資訊
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'ACCTG', module_name => 'PAYROLL');
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(service_name => 'ACCTG', module_name => 'GLEDGER', action_name => 'INSERT ITEM');
3.檢視收集的統計資訊
The accumulated global statistics for the currently enabled statistics can be displayed with the DBA_ENABLED_AGGREGATIONS view.
The accumulated statistics for a specified client identifier can be displayed in the V$CLIENT_STATS view.
The accumulated statistics for a specified service can be displayed in V$SERVICE_STATS view.
The accumulated statistics for a combination of specified service, module, and action can be displayed in the V$SERV_MOD_ACT_STATS view.
The accumulated statistics for elapsed time of database calls and for CPU use can be displayed in the V$SERVICEMETRIC view.
端到端的sql trace
1.用戶端辨別符級trace
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE
DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE
2.服務，子產品，acitve級trace
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE
例：
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'ACCTG', module_name => 'PAYROLL', waits => TRUE,binds => FALSE,instance_name => 'inst1');
3. 會話級trace
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);
DBMS_MONITOR.SESSION_TRACE_DISABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL);
例：
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,waits => TRUE, binds => FALSE);
4. 執行個體，資料庫級trace
DBMS_MONITOR.DATABASE_TRACE_ENABLE(
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL,
plan_stat IN VARCHAR2 DEFAULT NULL);
DBMS_MONITOR.DATABASE_TRACE_DISABLE(
instance_name IN VARCHAR2 DEFAULT NULL);
例：
EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits => TRUE, binds => FALSE, instance_name => 'inst1');
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)