關鍵字 ORACLE PERFORMANCE TUNING PL/SQL
出處 http://www.dbasupport.com
39. 總是使用索引的第一個列
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引.
譯者按:
這也是一條簡單而重要的規則. 見以下實例.
SQL> create table multiindexusage ( inda number , indb number
, descr varchar2(10));
Table created.
SQL> create index multindex on multiindexusage(inda,indb);
Index created.
SQL> set autotrace traceonly
SQL> select * from multiindexusage where inda = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)
SQL> select * from multiindexusage where indb = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'
很明顯, 當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引
40. ORACLE內部操作
當執行查詢時,ORACLE采用了內部的操作. 下表顯示了幾種重要的內部操作.
ORACLE Clause
內部操作
ORDER BY
SORT ORDER BY
UNION
UNION-ALL
MINUS
MINUS
INTERSECT
INTERSECT
DISTINCT,MINUS,INTERSECT,UNION
SORT UNIQUE
MIN,MAX,COUNT
SORT AGGREGATE
GROUP BY
SORT GROUP BY
ROWNUM
COUNT or COUNT STOPKEY
Queries involving Joins
SORT JOIN,MERGE JOIN,NESTED LOOPS
CONNECT BY
CONNECT BY
41. 用UNION-ALL 替換UNION ( 如果有可能的話)
當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合并, 然后在輸出最終結果前進行排序.
如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高.
舉例:
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
譯者按:
需要注意的是，UNION ALL 將重復輸出兩個結果集合中相同記錄. 因此各位還是
要從業務需求分析使用UNION ALL的可行性.
UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內存. 對于這
塊內存的優化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量
Select substr(name,1,25) "Sort Area Name",
substr(value,1,15) "Value"
from v$sysstat
where name like 'sort%'
42. 使用提示(Hints)
對于表的訪問,可以使用兩種Hints.
FULL 和 ROWID
FULL hint 告訴ORACLE使用全表掃描的方式訪問指定表.
例如:
SELECT /*+ FULL(EMP) */ *
FROM EMP
WHERE EMPNO = 7893;
ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問表.
通常, 你需要采用TABLE ACCESS BY ROWID的方式特別是當訪問大表的時候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.
如果一個大表沒有被設定為緩存(CACHED)表而你希望它的數據在查詢結束是仍然停留
在SGA中,你就可以使用CACHE hint 來告訴優化器把數據保留在SGA中. 通常CACHE hint 和 FULL hint
一起使用.
例如:
SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *
FROM WORK;
索引hint 告訴ORACLE使用基于索引的掃描方式. 你不必說明具體的索引名稱
例如:
SELECT /*+ INDEX(LODGING) */ LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES';
在不使用hint的情況下, 以上的查詢應該也會使用索引,然而,如果該索引的重復值過多而你的優化器是CBO, 優化器就可能忽略索引.
在這種情況下, 你可以用INDEX hint強制ORACLE使用該索引.
ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE,
USE_HASH 等等.
譯者按:
使用hint , 表示我們對ORACLE優化器缺省的執行路徑不滿意,需要手工修改.
這是一個很有技巧性的工作. 我建議只針對特定的,少數的SQL進行hint的優化.
對ORACLE的優化器還是要有信心(特別是CBO) |