ORACLE效能優化筆記

1. 查詢正在執行語句的執行計劃(也就是實際語句執行計劃)
    select * from v$sql_plan where hash_value = (select sql_hash_value from v$session where sid = 1111);
    其中id和parent_id表示了執行數的結構,數值最大的為最先執行比如

ID  PARENT_ID   
-------------   
0 
1 0
2 1
3 2
4 3
5 4
6 3   

 

 

則執行計劃樹為
              0
              1
              2
              3
           6     4 
                  5

2.如何設定自動跟蹤
　　用system登入
　　執行$ORACLE_HOME/rdbms/admin/utlxplan.sql建立計劃表
　　執行$ORACLE_HOME/sqlplus/admin/plustrce.sql建立plustrace角色
　　如果想計劃表讓每個使用者都能使用，則
　　SQL>create public synonym plan_table for plan_table;
　　SQL> grant all on plan_table to public;

　　如果想讓自動跟蹤的角色讓每個使用者都能使用，則
　　SQL> grant plustrace to public;
　　通過如下語句開啟/停止跟蹤
　　SET AUTOTRACE ON |OFF | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN

3.如何跟蹤自己的會話或者是別人的會話
　　跟蹤自己的會話很簡單
　　Alter session set sql_trace true|false
　　Or
　　Exec dbms_session.set_sql_trace(TRUE);

　　如果跟蹤別人的會話，需要呼叫一個包
　　exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)

　　跟蹤的資訊在user_dump_dest 目錄下可以找到或通過如下指令碼獲得檔名稱（適用於Win環境，如果是unix需要做一定修改）
　　SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora' filename
　　FROM
　　v$process p,
　　v$session s,
　　v$parameter p1,
　　v$parameter p2
　　WHERE p1.name = 'user_dump_dest'
　　AND p2.name = 'db_name'
　　AND p.addr = s.paddr
　　AND s.audsid = USERENV ('SESSIONID')
　　最後，可以通過Tkprof來解析跟蹤檔案，如
　　Tkprof 原檔案 目標檔案 sys=n

4.怎麼設定整個資料庫系統跟蹤
　　其實文件上的alter system set sql_trace=true是不成功的，但是可以通過設定事件來完成這個工作，作用相等
　　alter system set events
　　'10046 trace name context forever,level 1';

　　如果關閉跟蹤，可以用如下語句
　　alter system set events
　　'10046 trace name context off';

其中的level 1與上面的8都是跟蹤級別
　　level 1：跟蹤SQL語句，等於sql_trace=true
　　level 4：包括變數的詳細資訊
　　level 8：包括等待事件
　　level 12：包括繫結變數與等待事件

5.怎麼樣根據OS程式快速獲得DB程式資訊與正在執行的語句
　　有些時候，我們在OS上操作，象TOP之後我們得到的OS程式，怎麼快速根據OS資訊獲得DB資訊呢？
　　我們可以編寫如下指令碼：
　　$more whoit.sh
　　#!/bin/sh
　　sqlplus /nolog 100,cascade=> TRUE);
　　dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);

這是對命令與工具包的一些總結
　　<1>、對於分割槽表，建議使用DBMS_STATS，而不是使用Analyze語句。
　　　a) 可以並行進行，對多個使用者，多個Table
　　　b) 可以得到整個分割槽表的資料和單個分割槽的資料。
　　　c) 可以在不同級別上Compute Statistics：單個分割槽，子分割槽，全表，所有分割槽
　　　d) 可以倒出統計資訊
　　　e) 可以使用者自動收集統計資訊
　　<2>、DBMS_STATS的缺點
　　　a) 不能Validate Structure
　　　b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的資訊，這兩個仍舊需要使用Analyze語句。
　　　c) DBMS_STATS 預設不對索引進行Analyze，因為預設Cascade是False，需要手工指定為True
　　<3>、對於oracle 9裡面的External Table，Analyze不能使用，只能使用DBMS_STATS來收集資訊。

6.怎麼樣快速重整索引
　　通過rebuild語句，可以快速重整或移動索引到別的表空間
　　rebuild有重建整個索引數的功能，可以在不刪除原始索引的情況下改變索引的儲存引數
　　語法為
　　alter index index_name rebuild tablespace ts_name
　　storage(......);

　　如果要快速重建整個使用者下的索引，可以用如下指令碼，當然，需要根據你自己的情況做相應修改
　　SQL> set heading off
　　SQL> set feedback off
　　SQL> spool d:\index.sql
　　SQL> SELECT 'alter index ' || index_name || ' rebuild '
　　||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
　　FROM all_indexes
　　WHERE ( tablespace_name != 'INDEXES'
　　OR next_extent != ( 256 * 1024 )
　　)
　　AND wner = USER
　　SQL>spool off

　　另外一個合併索引的語句是
　　alter index index_name coalesce

　　這個語句僅僅是合併索引中同一級的leaf block，消耗不大，對於有些索引中存在大量空間浪費的情況下，有一些作用。

7.如何使用Hint提示
　　在select/delete/update後寫/*+ hint */
　　如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...

　　注意/*和+之間不能有空格，如用hint指定使用某個索引
　　select /*+ index(cbotab) */ col1 from cbotab;
　　select /*+ index(cbotab cbotab1) */ col1 from cbotab;
　　select /*+ index(a cbotab1) */ col1 from cbotab a;

　　其中
　　TABLE_NAME是必須要寫的，且如果在查詢中使用了表的別名，在hint也要用表的別名來代替表名;
　　INDEX_NAME可以不必寫，Oracle會根據統計值選一個索引;
　　如果索引名或表名寫錯了，那這個hint就會被忽略;

8.怎麼樣快速複製表或者是插入資料
　　快速複製表可以指定Nologging選項
　　如：Create table t1 nologging
　　as select * from t2;

　　快速插入資料可以指定append提示，但是需要注意noarchivelog模式下，預設用了append就是nologging模式的。 在archivelog下，需要把表設定程Nologging模式。
　　如insert /*+ append */ into t1
　　select * from t2

　　注意：如果在9i環境中並設定了FORCE LOGGING，則以上操作是無效的，並不會加快，當然，可以通過如下語句設定為NO FORCE LOGGING。
　　Alter database no force logging;
　　是否開啟了FORCE LOGGING，可以用如下語句檢視
　　SQL> select force_logging from v$database;

9.怎麼避免使用特定索引
　　在很多時候，Oracle會錯誤的使用索引而導致效率的明顯下降，我們可以使用一點點技巧而避免使用不該使用的索引，如：
　　表test,有欄位a,b,c,d，在a,b,c上建立聯合索引inx_a(a,b,c)，在b上單獨建立了一個索引Inx_b(b)。

　　在正常情況下，where a=? and b=? and c=?會用到索引inx_a，where b=?會用到索引inx_b，但是，where a=? and b=? and c=? group by b會用到哪個索引呢？在分析資料不正確（很長時間沒有分析）或根本沒有分析資料的情況下，oracle往往會使用索引inx_b。通過執行計劃的分析，這個索引的使用，將大大耗費查詢時間。

　　當然，我們可以通過如下的技巧避免使用inx_b，而使用inx_a。

　　where a=? and b=? and c=? group by b||'' --如果b是字元
　　where a=? and b=? and c=? group by b+0 --如果b是數字

　　通過這樣簡單的改變，往往可以是查詢時間提交很多倍
　　當然，我們也可以使用no_index提示，相信很多人沒有用過，也是一個不錯的方法：
　　select /*+ no_index(t,inx_b) */ * from test t
　　where a=? and b=? and c=? group by b

　　舉例：
　　本來在CM_USER上有索引IDX_CM_USER4(ACC_ID)和IDX_CM_USER8(BILL_ID),可是執行如下語句的時候很慢。
　　select * from CM_USER where  acc_id =1200007175
　　and user_status>0 and bill_id like '13%' order by acc_id,bill_id

　　用explain分析，發現執行計劃是用IDX_CM_USER8.如下查詢
　　select * from user_indexes where table_name ='CM_USER' 發現IDX_CM_USER8沒有分析過。

　　用下面語句執行計劃改變
　　select /*+INDEX(CM_USER IDX_CM_USER4)*/* from CM_USER where  acc_id =1200007175 and user_status>0 and bill_id like '13%' order by acc_id,bill_id

　　或者分析索引
　　exec dbms_stats.gather_index_stats(ownname => 'QACS1',indname => 'IDX_CM_USER8',estimate_percent => 5 );
　　可以發現執行計劃恢復正常。

10.Oracle什麼時候會使用跳躍式索引掃描
　　這是9i的一個新特性跳躍式索引掃描(Index Skip Scan).
　　例如表有索引index(a,b,c)，當查詢條件為where b=?的時候，可能會使用到索引index(a,b,c)，如，執行計劃中出現如下計劃：
　　INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)

　　Oracle的優化器(這裡指的是CBO)能對查詢應用Index Skip Scans至少要有幾個條件:
　　<1> 優化器認為是合適的。
　　<2> 索引中的前導列的唯一值的數量能滿足一定的條件（如重複值很多）。
　　<3> 優化器要知道前導列的值分佈(通過分析/統計表得到)。
　　<4> 合適的SQL語句
　　等。

11.怎麼樣建立使用虛擬索引
　　可以使用nosegment選項，如
　　create index virtual_index_name on table_name(col_name) nosegment;

　　如果在哪個session需要測試虛擬索引，可以利用隱含引數來處理
　　alter session set "_use_nosegment_indexes" = true;

　　就可以利用explain plan for select ……來看虛擬索引的效果，利用@$ORACLE_HOME/rdbms/admin/utlxpls檢視執行計劃，最後，根據需要，我們可以刪除虛擬索引，如普通索引一樣
　　drop index virtual_index_name;

　　注意：虛擬索引並不是物理存在的，所以虛擬索引並不等同於物理索引，不要用自動跟蹤去測試虛擬索引，因為那是實際執行的效果，是用不到虛擬索引的。

12.怎樣監控無用的索引
　　Oracle 9i以上，可以監控索引的使用情況，如果一段時間內沒有使用的索引，一般就是無用的索引
　　語法為：
　　開始監控：alter index index_name monitoring usage;
　　檢查使用狀態：select * from v$object_usage;
　　停止監控：alter index index_name nomonitoring usage;

　　當然，如果想監控整個使用者下的索引，可以採用如下的指令碼：
　　set heading off
　　set echo off
　　set feedback off
　　set pages 10000
　　spool start_index_monitor.sql
　　SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
　　FROM dba_indexes
　　WHERE wner = USER;
　　spool off
　　set heading on
　　set echo on
　　set feedback on
　　------------------------------------------------
　　set heading off
　　set echo off
　　set feedback off
　　set pages 10000
　　spool stop_index_monitor.sql
　　SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
　　FROM dba_indexes
　　WHERE wner = USER;
　　spool off
　　set heading on
　　set echo on
　　set feedback on

13.怎麼樣能固定我的執行計劃
　　可以使用OUTLINE來固定SQL語句的執行計劃，用如下語句可以建立一個OUTLINE
　　Create oe replace outline OutLn_Name on
　　Select Col1,Col2 from Table
　　where .......

　　如果要刪除Outline，可以採用
　　Drop Outline OutLn_Name;

　　對於已經建立了的OutLine，存放在OUTLN使用者的OL$HINTS表下面，對於有些語句，你可以使用update outln.ol$hints來更新outline，如

　　update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
　　where ol_name in ('TEST1','TEST2');

　　這樣，你就把Test1 OUTLINE與Test2 OUTLINE互換了，如果想利用已經存在的OUTLINE，需要設定以下引數
　　Alter system/session set Query_rewrite_enabled = true
　　Alter system/session set use_stored_outlines = true

14.v$sysstat中的class分別代表什麼
　　統計類別
　　1 代表事例活動
　　2 代表Redo buffer活動
　　4 代表鎖
　　8 代表資料緩衝活動
　　16 代表OS活動
　　32 代表並行活動
　　64 代表表訪問
　　128 代表除錯資訊

15.怎麼殺掉特定的資料庫會話
　　Alter system kill session 'sid,serial#';
　　或者
　　alter system disconnect session 'sid,serial#' immediate;

　　在win上，還可以採用oracle提供的orakill殺掉一個執行緒（其實就是一個Oracle程式）
　　在Linux/Unix上，可以直接利用kill殺掉資料庫程式對應的OS程式

16.怎麼快速查詢鎖與鎖等待
　　資料庫的鎖是比較耗費資源的，特別是發生鎖等待的時候，我們必須找到發生等待的鎖，有可能的話，殺掉該程式。
　　這個語句將查詢到資料庫中所有的DML語句產生的鎖，還可以發現，任何DML語句其實產生了兩個鎖，一個是表鎖，一個是行鎖。
　　可以通過alter system kill session ‘sid,serial#’來殺掉會話

　　SELECT /*+ rule */ s.username,
　　decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,
　　o.owner,o.object_name,o.object_type,
　　s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
　　FROM v$session s,v$lock l,dba_objects o
　　WHERE l.sid = s.sid
　　AND l.id1 = o.object_id(+)
　　AND s.username is NOT NULL

　　如果發生了鎖等待，我們可能更想知道是誰鎖了表而引起誰的等待，以下的語句可以查詢到誰鎖了表，而誰在等待。
　　SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
　　o.owner,o.object_name,o.object_type,s.sid,s.serial#
　　FROM v$locked_object l,dba_objects o,v$session s
　　WHERE l.object_id=o.object_id
　　AND l.session_id=s.sid
　　ORDER BY o.object_id,xidusn DESC

　　以上查詢結果是一個樹狀結構，如果有子節點，則表示有等待發生。如果想知道鎖用了哪個回滾段，還可以關聯到V$rollname，其中xidusn就是回滾段的USN

　　[Q] 如何有效的刪除一個大表(extent數很多的表)
　　[A] 一個有很多(100k)extent的表，如果只是簡單地用drop table的話，會很大量消耗CPU（Oracle要對fet$、uet$資料字典進行操作），可能會用上幾天的時間，較好的方法是分多次刪除extent，以減輕這種消耗：
　　1. truncate table big-table reuse storage;
　　2. alter table big-table deallocate unused keep 2000m ( 原來大小的n-1/n);
　　3. alter table big-table deallocate unused keep 1500m ;
　　....
　　4. drop table big-table;

17.如何收縮臨時資料檔案的大小
　　9i以下版本採用
　　ALTER DATABASE DATAFILE 'file name' RESIZE 100M類似的語句
　　9i以上版本採用
　　ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
　　注意，臨時資料檔案在使用時，一般不能收縮，除非關閉資料庫或斷開所有會話，停止對臨時資料檔案的使用。

18.怎麼清理臨時段
　　可以使用如下辦法
　　<1>、 使用如下語句檢視一下認誰在用臨時段
　　SELECT username,sid,serial#,sql_address,machine,program,
　　tablespace,segtype, contents
　　FROM v$session se,v$sort_usage su
　　WHERE se.saddr=su.session_addr

 　　<2>、 那些正在使用臨時段的程式
　　SQL>Alter system kill session 'sid,serial#';

　　<3>、把TEMP表空間回縮一下
　　SQL>Alter tablespace TEMP coalesce;

　　還可以使用診斷事件
　　<1>、 確定TEMP表空間的ts#
　　SQL> select ts#, name FROM v$tablespace;
　　TS# NAME
　　-----------------------
　　0 SYSYEM
　　1 RBS
　　2 USERS
　　3* TEMP
　　...

　　<2>、 執行清理操作
　　alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'
　　說明：
　　temp表空間的TS# 為 3*, So TS#+ 1= 4，如果想清除所有表空間的臨時段，則，TS# = 2147483647

19.怎麼樣dump資料庫內部結構，如上面顯示的控制檔案的結構
　　常見的有
　　1、分析資料檔案塊，轉儲資料檔案n的塊m
　　alter system dump datafile n block m

　　2、分析日誌檔案
　　alter system dump logfile logfilename;

　　3、分析控制檔案的內容
　　alter session set events 'immediate trace name CONTROLF level 10'

　　4、分析所有資料檔案頭
　　alter session set events 'immediate trace name FILE_HDRS level 10'

　　5、分析日誌檔案頭
　　alter session set events 'immediate trace name REDOHDR level 10'

　　6、分析系統狀態，最好每10分鐘一次，做三次對比
　　alter session set events 'immediate trace name SYSTEMSTATE level 10'

　　7、分析程式狀態
　　alter session set events 'immediate trace name PROCESSSTATE level 10'

　　8、分析Library Cache的詳細情況
　　alter session set events 'immediate trace name library_cache level 10'

20.如何獲得所有的事件程式碼
　　事件程式碼範圍一般從10000 to 10999，以下列出了這個範圍的事件程式碼與資訊
　　SET SERVEROUTPUT ON
　　DECLARE
　　err_msg VARCHAR2(120);
　　BEGIN
　　dbms_output.enable (1000000);
　　FOR err_num IN 10000..10999
　　LOOP
　　err_msg := SQLERRM (-err_num);
　　IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
　　dbms_output.put_line (err_msg);
　　END IF;
　　END LOOP;
　　END;
　　/

　　在Unix系統上，事件資訊放在一個文字檔案裡
　　$ORACLE_HOME/rdbms/mesg/oraus.msg
　　可以用如下指令碼檢視事件資訊
　　event=10000
　　while [ $event -ne 10999 ]
　　do
　　event=`expr $event + 1`
　　oerr ora $event
　　done

　　對於已經確保的/正在跟蹤的事件，可以用如下指令碼獲得
　　SET SERVEROUTPUT ON
　　DECLARE
　　l_level NUMBER;
　　BEGIN
　　FOR l_event IN 10000..10999
　　LOOP
　　dbms_system.read_ev (l_event,l_level);
　　IF l_level > 0 THEN
　　dbms_output.put_line ('Event '||TO_CHAR (l_event)||
　　' is set at level '||TO_CHAR (l_level));
　　END IF;
　　END LOOP;
　　END;
　　/

21.什麼是STATSPACK，我怎麼使用它？
　　Statspack是Oracle 8i以上提供的一個非常好的效能監控與診斷工具，基本上全部包含了BSTAT/ESTAT的功能，更多的資訊，可以參考附帶文件$ORACLE_HOME/rdbms/admin/spdoc.txt。

　　安裝Statspack:
　　cd $ORACLE_HOME/rdbms/admin
　　sqlplus "/ as sysdba" @spdrop.sql -- 解除安裝，第一次可以不需要
　　sqlplus "/ as sysdba" @spcreate.sql -- 需要根據提示輸入表空間名

　　使用Statspack:
　　sqlplus perfstat/perfstat
　　exec statspack.snap; -- 進行資訊收集統計，每次執行都將產生一個快照號
　　-- 獲得快照號，必須要有兩個以上的快照，才能生成報表
　　select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
　　@spreport.sql -- 輸入需要檢視的開始快照號與結束快照號

　　其他相關指令碼s:
　　spauto.sql - 利用dbms_job提交一個作業，自動的進行STATPACK的資訊收集統計
　　sppurge.sql - 清除一段範圍內的統計資訊，需要提供開始快照與結束快照號
　　sptrunc.sql - 清除(truncate)所有統計資訊

22. SQL語句的優化方法
　　<1> /*+ALL_ROWS*/
　　表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
　　例如:
　　SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　<2>. /*+FIRST_ROWS*/
　　表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
　　例如:
　　SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE    EMP_NO='CCBZZP';

　　<3>. /*+CHOOSE*/
　　表明如果資料字典中有訪問表的統計資訊,將基於開銷的優化方法,並獲得最佳的吞吐量;
　　表明如果資料字典中沒有訪問表的統計資訊,將基於規則開銷的優化方法;
　　例如:
　　SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';

　　<4>. /*+RULE*/
　　表明對語句塊選擇基於規則的優化方法.
　　例如:
　　SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';  

　　<5>. /*+FULL(TABLE)*/
　　表明對錶選擇全域性掃描的方法.
　　例如:
　　SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';

　　<6>. /*+ROWID(TABLE)*/
　　提示明確表明對指定表根據ROWID進行訪問.
　　例如:
　　SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
   AND EMP_NO='CCBZZP';

　　<7>. /*+CLUSTER(TABLE)*/
　　提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇物件有效.
　　例如:
　　SELECT  /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
　　WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<8>. /*+INDEX(TABLE INDEX_NAME)*/
　　表明對錶選擇索引的掃描方法.
　　例如:
　　SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE    BSEMPMS */  FROM BSEMPMS WHERE SEX='M';

　　<9>. /*+INDEX_ASC(TABLE INDEX_NAME)*/
　　表明對錶選擇索引升序的掃描方法.
　　例如:
　　SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */  FROM BSEMPMS WHERE DPT_NO='CCBZZP';

　　<10>. /*+INDEX_COMBINE*/
　　為指定表選擇點陣圖訪問路經,如果INDEX_COMBINE中沒有提供作為引數的索引,將選擇出點陣圖索引的
　　布林組合方式.
　　例如:
　　SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
　　WHERE SAL<5000000 AND HIREDATE

　　<11>. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
　　提示明確命令優化器使用索引作為訪問路徑.
　　例如:
　　SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
　　FROM BSEMPMS WHERE SAL<60000;

　　<12>. /*+INDEX_DESC(TABLE INDEX_NAME)*/
　　表明對錶選擇索引降序的掃描方法.
　　例如:
　　SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */  FROM BSEMPMS WHERE    DPT_NO='CCBZZP';

　　<13>. /*+INDEX_FFS(TABLE INDEX_NAME)*/
　　對指定的表執行快速全索引掃描,而不是全表掃描的辦法.
　　例如:
　　SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

<14>. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
　　提示明確進行執行規劃的選擇,將幾個單列索引的掃描合起來.
　　例如:
　　SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';

　　<15>. /*+USE_CONCAT*/
　　對查詢中的WHERE後面的OR條件進行轉換為UNION ALL的組合查詢.
　　例如:
　　SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

　　<16>. /*+NO_EXPAND*/
　　對於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於優化器對其進行擴充套件.
　　例如:
　　SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE  DPT_NO='TDC506' AND SEX='M';

　　<17>. /*+NOWRITE*/
　　禁止對查詢塊的查詢重寫操作.

　　<18>. /*+REWRITE*/
　　可以將檢視作為引數.

　　<19>. /*+MERGE(TABLE)*/
　　能夠對檢視的各個查詢進行相應的合併.
　　例如:
　　SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO　AND A.SAL>V.AVG_SAL;

　　<20>. /*+NO_MERGE(TABLE)*/
　　對於有可合併的檢視不再合併.
　　例如:
　　SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO　AND A.SAL>V.AVG_SAL;
  
　　<21>. /*+ORDERED*/
　　根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連線.
　　例如:
　　SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C
　　WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

　　<22>. /*+USE_NL(TABLE)*/
　　將指定表與巢狀的連線的行源進行連線,並把指定表作為內部表.
　　例如:
　　SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<23>. /*+USE_MERGE(TABLE)*/
　　將指定的表與其他行源通過合併排序連線方式連線起來.
　　例如:
　　SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<24>. /*+USE_HASH(TABLE)*/
　　將指定的表與其他行源通過雜湊連線方式連線起來.
　　例如:
　　SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
　　BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

　　<25>. /*+DRIVING_SITE(TABLE)*/
　　強制與ORACLE所選擇的位置不同的表進行查詢執行.
　　例如:
　　SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

　　<26>. /*+LEADING(TABLE)*/
　　將指定的表作為連線次序中的首表.
  
　　<27>. /*+CACHE(TABLE)*/
　　當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區快取中最近最少列表LRU的最近使用端
　　例如:
　　SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM  BSEMPMS;

　　<28>. /*+NOCACHE(TABLE)*/
　　當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區快取中最近最少列表LRU的最近使用端
　　例如:
　　SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM  BSEMPMS;

　　<29>. /*+APPEND*/
　　直接插入到表的最後,可以提高速度.
　　insert /*+append*/ into test1  select * from test4 ;
  
　　insert /*+append */ into emp nologging

　　<30>. /*+NOAPPEND*/
　　通過在插入語句生存期內停止並行模式來啟動常規插入.

　　insert /*+noappend*/ into test1  select * from test4 ;

　　<31>.parallel direct-load insert
　　sql> alter session enable parallel dml;
　　sql> insert /*+parallel(emp,2) */ into emp nologging
　　sql> select * from emp_old;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8750110/viewspace-6864/，如需轉載，請註明出處，否則將追究法律責任。