二．訪問Table的方式
ORACLE 采用兩種訪問表中記錄的方式:
a. 全表掃描
全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀入多個(gè)數(shù) 據(jù)塊(database block)的方式優(yōu)化全表掃描。
b. 索引掃描
你可以采用基于ROWID的訪問方式情況,提高訪問表的效率, ROWID包含了表中記錄的物理位置信息.ORACLE采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系. 通常索引提供了快速訪問ROWID的方法,因此那些基于索引列的查詢就可以得到性能上的提高.
其中ORACLE對(duì)索引又有兩種訪問模式.
a)索引唯一掃描 ( INDEX UNIQUE SCAN)
大多數(shù)情況下, 優(yōu)化器通過WHERE子句訪問INDEX.
例如:
表LOADING有兩個(gè)索引 : 建立在LOADING列上的唯一性索引LOADING_PK和建立在MANAGER列上的非唯一性索引IDX_MANAGER.
SELECT loading
FROM LOADING
WHERE LOADING = ‘ROSE HILL’;
在內(nèi)部 , 上述SQL將被分成兩步執(zhí)行, 首先 , LOADING_PK 索引將通過索引唯一掃描的方式被訪問 , 獲得相對(duì)應(yīng)的ROWID, 通過ROWID訪問表的方式執(zhí)行下一步檢索.
如果被檢索返回的列包括在INDEX列中,ORACLE將不執(zhí)行第二步的處理(通過ROWID訪問表). 因?yàn)闄z索數(shù)據(jù)保存在索引中, 單單訪問索引就可以完全滿足查詢結(jié)果.
下面SQL只需要INDEX UNIQUE SCAN 操作.
SELECT LOADING
FROM LOADING
WHERE LOADING = ‘ROSE HILL’;
b)索引范圍查詢(INDEX RANGE SCAN)
適用于兩種情況:
1. 基于一個(gè)范圍的檢索
2. 基于非唯一性索引的檢索
例1:
SELECT LOADING
FROM LOADING
WHERE LOADING LIKE ‘M%’;
WHERE子句條件包括一系列值, ORACLE將通過索引范圍查詢的方式查詢LODGING_PK . 由于索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描
低一些.
例2:
SELECT LOADING
FROM LOADING
WHERE MANAGER = ‘BILL GATES’;
這個(gè)SQL的執(zhí)行分兩步, IDX_MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過ROWID訪問表得到LOADING列的值. 由于IDX_MANAGER是一個(gè)非唯一性的索引,數(shù)據(jù)庫(kù)不能對(duì)它執(zhí)行索引唯一掃描.
由于SQL返回LOADING列,而它并不存在于IDX_MANAGER索引中, 所以在索引范圍查詢后會(huì)執(zhí)行一個(gè)通過ROWID訪問表的操作.
WHERE子句中, 如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(WILDCARD)開始, 索引將不被采用.
SELECT LOADING
FROM LOADING
WHERE MANAGER LIKE ‘％HANMAN’;
在這種情況下，ORACLE將使用全表掃描.
三．SQL調(diào)優(yōu)的本質(zhì)就是調(diào)整執(zhí)行計(jì)劃。
在好多情況下，oracle自動(dòng)選擇的執(zhí)行計(jì)劃并不是最優(yōu)的，這時(shí)需要我們?nèi)斯とジ深A(yù)。(什么是執(zhí)行計(jì)劃?)
對(duì)SQL調(diào)優(yōu)基本步驟：
a) 捕獲SQL語句
b) 產(chǎn)生SQL語句的執(zhí)行計(jì)劃；
c) 驗(yàn)證統(tǒng)計(jì)信息(SQL語句涉及到的表格是否做過分析)，表格信息(結(jié)果集的記錄數(shù)，索引)，字段上面數(shù)據(jù)分布特點(diǎn)
d) 通過手工收集到的信息，形成自己理想的執(zhí)行計(jì)劃。
e) 如果做過分析，則重新分析相關(guān)表格或者做柱狀圖分析。
f) 如果沒有做過分析，則通過嘗試不同的Hint，從而獲得合適的執(zhí)行計(jì)劃。
g) 當(dāng)我們正常無法調(diào)優(yōu)到位時(shí)，可以打開10053事件打開優(yōu)化器的跟蹤，看看Oracle如何選擇的.
alter session set events='10053 trace name context forever,level 2';
四．如何捕獲SQL語句
捕獲SQL語句的方法有如下幾種：
1．SQL TRACE或10046跟蹤某個(gè)模塊。
2．PERFSTAT性能統(tǒng)計(jì)包，使用方法見附錄二。
3．V$SQL，V$SESSION_WAIT，V$SQL_TEXT
五．如何查看執(zhí)行計(jì)劃
查看SQL語句的執(zhí)行計(jì)劃有以下幾種：
1．Set autotrace on(set autotrace traceonly exp)
2．Explain plan for …..
@?/rdbms/admin/utlxpls.sql
3．V$SQL_PLAN視圖
column operation format a16
column "Query Plan" format a60
column options format a15
column object_name format a20
column id format 99
select id,lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '
||decode(id,0,'Cost = '||position) "Query Plan"
from (select *
from v$sql_plan
where address='&a') sql_plan
start with id = 0
connect by prior id = parent_id
/
4．第三方工具，如pl/sql developer,TOAD
六．SQL語句主要的連接方法
a) Nested-loop join
適合于小表(幾千條，幾萬條記錄)與大表做聯(lián)接
在聯(lián)接列上有索引。
分內(nèi)表和外表(驅(qū)動(dòng)表)，靠近from子句的是內(nèi)表。從效率上講，小表應(yīng)該作外表，大表應(yīng)該作內(nèi)表，即大表查詢時(shí)走索引。
COST= Access cost of A(驅(qū)動(dòng)表) + (access cost of B * number of rows from A)
成本計(jì)算方法：
設(shè)小表100行，大表100000行。
兩表均有索引：
如果小表在內(nèi)，大表在外(驅(qū)動(dòng)表)的話，則掃描次數(shù)為：
100000+100000*2 (其中2表示IO次數(shù)，一次索引，一次數(shù)據(jù))
如果大表在內(nèi)，小表在外(驅(qū)動(dòng)表)的話，則掃描次數(shù)為：
100+100*2.
兩表均無索引：
如果小表在內(nèi)，大表在外的話，則掃描次數(shù)為：
100000+100*100000
如果大表在內(nèi)，小表在外的話，則掃描次數(shù)為：
100+100000*100
注意：如果一個(gè)表有索引，一個(gè)表沒有索引，ORACLE會(huì)將沒有索引的表作驅(qū)動(dòng)表。如果兩個(gè)表都有索引，則外表作驅(qū)動(dòng)表。如果兩個(gè)都沒索引的話，則也是外表作驅(qū)動(dòng)表。
基本的執(zhí)行計(jì)劃如下所示：
NESTED LOOPS
TABLE ACCESS (BY ROWID) OF our_outer_table
INDEX (..SCAN) OF outer_table_index(….)
TABLE ACCESS (BY ROWID) OF our_inner_table
INDEX (..SCAN) OF inner_table_index(….)
b) Hash join
適合于大表與大表，小表(幾十萬，幾百萬)與大表之間的聯(lián)連。
聯(lián)接列上不需要索引。
基本執(zhí)行計(jì)劃如下：
HASH JOIN
TABLE ACCESS (….) OF tableA
TABLE ACCESS (….) OF tableB
cost= (access cost of A * number of hash partitions of B) + access cost of B
可以看出主要成本在于A表是否可以被Cache。Hash_area_size的大小將決定Hash Join的主要成本。可以看出Hash Join的成本和返回集合并沒有直接的關(guān)系，所以當(dāng)返回結(jié)果集比較大的時(shí)候一般具有較好的性能。
為了加快hash join的速度，可以調(diào)大hash_area_size和pga_aggregate_target（默認(rèn)為25M）的值。
c) Sort Merge join
每一個(gè)Row Source在Join列上均排序。
然后兩個(gè)排序后的Row Source合并后，作一個(gè)結(jié)果集返回。
Sort/Merge Join僅僅對(duì)equal Join有效。
基本執(zhí)行計(jì)劃
MERGE (JOIN)
SORT (JOIN)
TABLE ACCESS (….) OF tableA
SORT (JOIN)
TABLE ACCESS (….) OF tableB
cost= access cost of A + access cost of B +(sort cost of A + sort cost of B)
可以看出Sort的成本是Merge Join的主要構(gòu)成部分。這樣sort_area_size的大小將很大程度決定Merge Join的大小。同樣如果A表或者B表已經(jīng)經(jīng)過排序的，那么Merge Join往往具有很好的性能。其不會(huì)走索引。
沒有驅(qū)動(dòng)表的概念，即時(shí)響應(yīng)能力較差。
七．一般情況下最常見的5種問題
1. Statement not written for indexes 25%
2. Indexes are missing or inappropriate 16%
3. Use of single-column index merge 15%
4. Misuse of nested loop, sort merge, or hash join 12%
5. Misuse of IN, EXISTS, NOT IN, NOT EXISTS, or table joins 8%
不過在我們這里，最常見的問題是在第2條，第3條，第4條。
1． Statement not written for indexes
類似于這樣的：
SELECT account_name, trans_date, amount
FROM transaction
WHERE SUBSTR(account_name,1,7) = ' CAPITAL';
WHERE account_name LIKE 'CAPITAL%';
Account_date 日期
To_char(Account_date,’YYYY-MM-DD:HH24:MI:SS’)=’200508XXX’;
Account_date=to_date(‘200508….’,’yyyy-mm-dd);
2．Indexes are missing or inappropriate
例如REP_C021中有這樣一句：
select SUBSIDIARYID,260,' 300電話卡',
sum(decode(feetype, 1, ceil(duration / 60))) +
sum(decode(feetype, 0, ceil(duration / 60))),
sum(decode(feetype, 1, ceil(duration / 60))),
sum(decode(feetype, 0, ceil(duration / 60))),0
from cardsusage200508 a, service b
where a.caller = b.servicecode and
(b.property = i_property or i_property is null) and
a.cdrtype = 102
group by SUBSIDIARYID, 260, ' 300電話卡';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'CARDSUSAGE200508'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SERVICE'
5 4 INDEX (UNIQUE SCAN) OF 'SERVICE_CODE'
我們?nèi)∑渲械膕elect語句進(jìn)行調(diào)優(yōu)。在調(diào)整之前，原select語句需要6分鐘左右。
12:19:20 SQL> select cdrtype,count(*) from cardsusage200508
12:20:12 2 group by cdrtype;
CDRT COUNT(*)
---- ----------
102 637
106 1973757
107 2390097
112 46016
113 20
針對(duì)cardsuage200508表格的特性，我們?cè)贑DRTYPE字段上建立一個(gè)位圖索引CARDSUSAGE_CDRTYPE_BTIDX。
將SQL語句加上以下Hint：
select /*+ INDEX(A, CARDSUSAGE_CDRTYPE_BTIDX)*/
SUBSIDIARYID,260,' 300電話卡',
sum(decode(feetype, 1, ceil(duration / 60))) +
sum(decode(feetype, 0, ceil(duration / 60))),
sum(decode(feetype, 1, ceil(duration / 60))),
sum(decode(feetype, 0, ceil(duration / 60))),0
from cardsusage200508 a, service b
where a.caller = b.servicecode and
(b.property = i_property or i_property is null) and
a.cdrtype = 102
group by SUBSIDIARYID, 260, ' 300電話卡';
這樣調(diào)整后，只需要幾秒鐘即可出來。
3. Use of single-column index merge
復(fù)合索引有的時(shí)候比單列索引效率更高。根據(jù)where子句中的具體情況，有 時(shí)可以建立復(fù)合索引。例如：
select a.AccountNum,a.ChargeID,a.Total,b.ItemID,
b.Amount,c.billingcycle
from charge_bill a, chargedetail_bill b, Account c
where a.AccountNum > 1 and a.AccountNum <= 1969618 and
a.status = '0' and a.InvoiceID is null and c.paymentmethod != '7' and
a.Total > 0 and a.AccountNum = c.AccountNum and
a.ChargeID = b.ChargeID
order by a.AccountNum, a.ChargeID, b.ItemID;
這樣的SQL語句執(zhí)行需要3分27秒。
我們做了以下優(yōu)化：
在charge_bill表格的accountnum,status,total,invoiceid列上建立一個(gè)復(fù)合索引。這樣上述SQL語句需要40秒左右。
Resume Service過程中有這么一句：
SELECT NVL(SUM(A.FEE),0)
FROM ACCOUNTBALANCE A,INVOICE B
WHERE A.OBJECTID = B.INVOICEID AND A.ACCOUNTNUM = :b1
AND B.BILLINGBEGINDATE < TO_DATE(:b2,'yyyymmdd');
該語句需要執(zhí)行大概72000次。整個(gè)過程執(zhí)行大概需要100分鐘左右。
將:b1以具體的值代替，這條SQL語句執(zhí)行很快，大概0.1秒左右。
我們做了以下優(yōu)化：
在invoiceid,billingbegindate列上創(chuàng)建了一個(gè)索引idx_invoice_hc。
將上述SQL語句改成：
select /*+ use_nl(a,b) index(b,IDX_INVOICE_HC)*/ nvl(sum(a.fee),0)
from accountbalance a,invoice b
where a.objectid=b.invoiceid and a.accountnum=m_accountnum
and b.billingbegindate
這樣一來，該過程的執(zhí)行時(shí)間快的時(shí)候大概在10分鐘左右，慢的時(shí)候(IO異常緊張的時(shí))大概在30分鐘左右。
4. Misuse of nested loop, sort merge, or hash join
表格之間的連接方式和連接順序都將極大的影響SQL語句的性能。這種問 題在平時(shí)最常見。ORACLE在處理5張或5張以上的表格的連接時(shí)候，很容 易出問題。一般情況下，謹(jǐn)記前面表格之間的連接原則，即可以處理此類問 題。
例如：
select b.SUBSIDIARYID,
c.paymentmethod || ':' || nvl(subscribertype, '9999999'),
'gsm',count(*),sum(decode(untelLOCALCHARGE,
0,decode(duration,0,1,
decode(sign(duration - 1800),
1, 2 + trunc((duration - 1201) / 600),
2)), trunc((duration + 599) / 600))),
sum(nvl(GSMCHARGE, 0)),nvl(property, '0'),
SUM(trunc((duration + 599) / 600))
from rt_untelecomusage a ,service b, account c
where a.starttime >
to_date(to_char(add_months(to_date('200508 ', 'YYYYMM'), -1),
'YYYYMM') || '20235959',
'YYYYMMDDHH24MISS') and
a.starttime < to_date('200508 ' || '21', 'YYYYMMdd') and
gsmcharge > 0 and a.serviceid = b.serviceid and
b.accountnum = c.accountnum
group by b.SUBSIDIARYID,
c.paymentmethod || ':' || nvl(subscribertype, '9999999'),
'gsm',nvl(property, '0');
該語句原先需要4，5個(gè)小時(shí)左右。
優(yōu)化：
alter session set hash_area_size=300000000;
select /*+ use_hash(b,c) ordered NO_EXPAND full(a) use_hash(a)*/ b.SUBSIDIARYID,c.paymentmethod || ':' || nvl(subscribertype, '9999999'),
'gsm',count(*), sum(decode(untelLOCALCHARGE,0,decode(duration,0, 1,
decode(sign(duration - 1800), 1,2 + trunc((duration - 1201) / 600), 2)),
trunc((duration + 599) / 600))),sum(nvl(GSMCHARGE, 0)),
nvl(property, '0'),SUM(trunc((duration + 599) / 600))
from service b, account c,untelecomusage_200508 a
where a.starttime >
to_date(to_char(add_months(to_date('200508', 'YYYYMM'), -1),
'YYYYMM') || '20235959',
'YYYYMMDDHH24MISS') and
a.starttime < to_date('200508' || '21', 'YYYYMMdd') and
gsmcharge > 0 and a.serviceid = b.serviceid and
b.accountnum = c.accountnum
group by b.SUBSIDIARYID,c.paymentmethod || ':' || nvl(subscribertype, '9999999'),'gsm',nvl(property, '0');
這樣優(yōu)化后，只需要40分鐘左右即可。
八．案例
1． 循環(huán)Update操作
以下過程太慢了， 半個(gè)小時(shí)連5000條記錄都未處理，總 共有7萬多條。
declare
cursor c1 is
select caller
from zxx_sms_step where chargemonth=200504 and fee is null;
icnt number;
begin
icnt:=0;
for m_c1 in c1 loop
update zxx_sms_step a set fee=
(select nvl(sum(pascharge),0) from ipasimport_200504 where caller=m_c1.caller and pastag in (1243,1251))
where caller=m_c1.caller and chargemonth=200504;
icnt:=icnt+1;
if icnt=500 then
exit;
end if;
end loop;
end;
這樣的SQL語句，建議先將update中的子查詢生成一張中間表，然后再update。
alter session set hash_area_size=400000000 ;
select /*+use_hash(a,b)*/ b.caller,nvl(sum(a.pascharge),0) from ipasimport_200504 a,zxx_sms_step b
where b.chargemonth=200504 and b.fee is null
and a.caller=b.caller and a.pastag in (1243,1251)
group by b.caller;
這樣10分鐘不到就可產(chǎn)生中間表，然后再update只需幾分鐘即可。
2． 部分表格未做統(tǒng)計(jì)信息分析
網(wǎng)通OA系統(tǒng)自從oracle服務(wù)器從pc服務(wù)器上遷到小型機(jī)上后，其CPU利用率經(jīng)常沖到很高。而其中每一個(gè)進(jìn)程在某個(gè)瞬間將占用40%左右的CPU。這些進(jìn)程都是通過jdbc thin client 連過來的。
通過抓取其sql_text，發(fā)現(xiàn)以下兩條SQL語句不正常。
1.
SQL> select D.flow_inid,D.step_inco,D.deal_man,D.agen_men,D.time_set,D.peri_man,
2 S2.fsub_set,S2.fsub_id,F.mtbl_stru,F.doc_name,F.svr_name
3 from deal_info D,step_inst S1,step_def S2,flow_inst F
4 where D.step_inco=S1.step_inco and S1.flow_id=S2.flow_id
5 and S1.step_code=S2.step_code and S1.flow_inid=F.flow_inid and D.step_type=5
6 and D.fsub_flag is not null and D.fsub_flag=1 and rownum<=1;
其執(zhí)行計(jì)劃和統(tǒng)計(jì)信息如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=1077)
1 0 COUNT (STOPKEY)
2 1 NESTED LOOPS (Cost=22 Card=1 Bytes=1077)
3 2 NESTED LOOPS (Cost=21 Card=1 Bytes=360)
4 3 NESTED LOOPS (Cost=20 Card=1 Bytes=150)
5 4 TABLE ACCESS (FULL) OF 'STEP_INST' (Cost=2 Card=9 Bytes=153)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'DEAL_INFO' (Cost=2 Card=1 Bytes=133)
7 6 INDEX (RANGE SCAN) OF 'DEAL_INFO_STEP_INCO' (NON-UNIQUE) (Cost=2
8 3 TABLE ACCESS (BY INDEX ROWID) OF 'FLOW_INST' (Cost=1 Card=1 Bytes=210)
9 8 INDEX (UNIQUE SCAN) OF 'PK_FLOW_INST' (UNIQUE)
10 2 TABLE ACCESS (BY INDEX ROWID) OF 'STEP_DEF' (Cost=1 Card=1 Bytes=717)
11 10 INDEX (UNIQUE SCAN) OF 'STEP_DEF_PK11119358638593' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
270626 consistent gets
273 physical reads
0 redo size
1079 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
這條SQL語句執(zhí)行的時(shí)間也不長(zhǎng)，就幾秒鐘，但是我們看到consistent gets很高有27萬多，這個(gè)操作就是消耗CPU的禍?zhǔn)住膱?zhí)行計(jì)劃來看，其執(zhí)行計(jì)劃顯然不可理，問題出在表格的連接順序上面，應(yīng)該是deal_info表格做為驅(qū)動(dòng)表先訪問。
檢查這些表格的統(tǒng)計(jì)分析，發(fā)現(xiàn)step_def表格未做分析，對(duì)該表格做統(tǒng)計(jì)信息分析，并對(duì)deal_info表做柱狀圖分析后：
analyze table deal_info compute statistics for all indexed columns;
其執(zhí)行計(jì)劃正是我們所想要的，同時(shí)consistent gets也只有200左右，該操作所消耗的CPU也下降到了1%。
2.表格的柱狀圖信息沒有分析：
SELECT SO.SO_NBR, so_type.name,STATUS.STS_WORDS, SO.REMARKS, SO.CHECK_TYPE,CTRL_ASGN.DISPATCHED_DATE,
CTRL_ASGN.PRE_ALARM_DATE, CTRL_ASGN.ALARM_DATE
from SO,SO_HANDLE, CTRL_ASGN,so_type,status
WHERE SO_HANDLE.SO_NBR=SO.SO_NBR AND SO.SO_NBR=CTRL_ASGN.SO_NBR
AND SO_HANDLE.HANDLE_TYPE_ID=1017
and so.so_type_id=so_type.so_type_id and so.PRIORITY=status.sts_id and status.table_name='SO'
AND STATUS.column_name ='PRIORITY' AND SO_HANDLE.WORK_AREA_ID= 300101
AND SO.STATE= 'B' AND SO.HALT ='N'
AND CTRL_ASGN.STATE = 'B'
AND CTRL_ASGN.STS = 'D';
該SQL語句執(zhí)行時(shí)間要2分鐘左右。
執(zhí)行計(jì)劃如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'STATUS'
6 5 INDEX (RANGE SCAN) OF 'PK_STATUS' (UNIQUE)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'CTRL_ASGN'
8 7 INDEX (RANGE SCAN) OF 'CTRL_ASGN_0002'
9 3 TABLE ACCESS (BY INDEX ROWID) OF 'SO'
10 9 INDEX (UNIQUE SCAN) OF 'PK_SO' (UNIQUE)
11 2 TABLE ACCESS (BY INDEX ROWID) OF 'SO_TYPE'
12 11 INDEX (UNIQUE SCAN) OF 'PK_SO_TYPE' (UNIQUE)
13 1 TABLE ACCESS (BY INDEX ROWID) OF 'SO_HANDLE'
14 13 INDEX (RANGE SCAN) OF 'PK_SO_HANDLE' (UNIQUE)
我們收集表格信息和結(jié)果集的信息:
SQL> select count(*) from CTRL_ASGN;
COUNT(*)
----------
1832469
SQL> select count(*) from status;
COUNT(*)
----------
1718
SQL> select count(*) from so;
COUNT(*)
----------
300296
SQL> select count(*) from so_type;
COUNT(*)
----------
265
SQL> select count(*) from so_handle;
COUNT(*)
----------
1296263
select count(*) from ctrl_asgn where CTRL_ASGN.STATE = 'B' AND CTRL_ASGN.STS = 'D';
COUNT(*)
----------
331490
select count(*) from so where SO.STATE= 'B' AND SO.HALT ='N';
COUNT(*)
----------
361
select count(*) from so_handle where SO_HANDLE.HANDLE_TYPE_ID=1017 and SO_HANDLE.WORK_AREA_ID= 300101;
COUNT(*)
----------
30086
通過對(duì)上面這些信息進(jìn)行分析，我們可以發(fā)現(xiàn)這個(gè)問題也可以歸結(jié)為表格之間的連接順序上面。通過將SO表做柱狀圖分析后，該SQL語句只需1秒鐘即可出來。
Analyze table so compute statistics for all indexed columns;
執(zhí)行計(jì)劃變成如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=273 Card=32 Bytes=3936)
1 0 NESTED LOOPS (Cost=273 Card=32 Bytes=3936)
2 1 NESTED LOOPS (Cost=153 Card=30 Bytes=2730)
3 2 HASH JOIN (Cost=33 Card=30 Bytes=2130)
4 3 NESTED LOOPS (Cost=31 Card=30 Bytes=1620)
5 4 TABLE ACCESS (FULL) OF 'STATUS' (Cost=2 Card=1 Bytes=25)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'SO' (Cost=29 Card=59 Bytes=1711)
7 6 INDEX (RANGE SCAN) OF 'SO_0003' (NON-UNIQUE) (Cost=2 Card=59)
8 3 TABLE ACCESS (FULL) OF 'SO_TYPE' (Cost=1 Card=128 Bytes=2176)
9 2 TABLE ACCESS (BY INDEX ROWID) OF 'SO_HANDLE' (Cost=4 Card=280 Bytes=5600)
10 9 INDEX (RANGE SCAN) OF 'PK_SO_HANDLE' (UNIQUE) (Cost=3 Card=280)
11 1 TABLE ACCESS (BY INDEX ROWID) OF 'CTRL_ASGN' (Cost=4 Card=13620 Bytes=435840)
12 11 INDEX (RANGE SCAN) OF 'CTRL_ASGN_0003' (NON-UNIQUE) (Cost=2 Card=13620)
3． Not exists的使用
--停機(jī)保號(hào)用戶數(shù)(除欠費(fèi))
select 'XJ'||1||'180','停機(jī)保號(hào)用戶數(shù)',count(distinct serviceid),1,'200509',groupid from cbq_lch_usage0
where subsidiaryid=1 and subid<>'02' and subid<>'06' and status='7' and
serviceid not in (select serviceorderid from cbq_qf_usage1 where status<>'3' and status <> '8')
group by 'XJ'||1||'180','停機(jī)保號(hào)用戶數(shù)',1,'200509',groupid ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (GROUP BY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'CBQ_LCH_USAGE0'
4 2 TABLE ACCESS (FULL) OF 'CBQ_QF_USAGE1'
Elapsed: 13:48:26.85
調(diào)整：
not in 改成not exists
create index idx_serviceorderid on cbq_qf_usage1(serviceorderid) nologging;
select 'XJ'||1||'180','停機(jī)保號(hào)用戶數(shù)',count(distinct serviceid),1,'200509',a.groupid
from cbq_lch_usage0 a
where a.subsidiaryid=1 and a.subid<>'02' and a.subid<>'06' and a.status='7'
and not exists(select 1 from cbq_qf_usage1 b where status<>'3' and status<>'8' and a.serviceid=b.serviceorderid)
group by 'XJ'||1||'180','停機(jī)保號(hào)用戶數(shù)',1,'200509',a.groupid;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (GROUP BY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'CBQ_LCH_USAGE0'
4 2 TABLE ACCESS (BY INDEX) OF 'CBQ_QF_USAGE1'
5 4 INDEX (RANGE SCAN) OF 'IDX_SERVICEORDERID'
Elapsed: 00:00:01.36
九．其他
1．SELECT子句中避免使用 ‘ * ‘
當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動(dòng)態(tài) SQL列引用 ‘*’ 是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法. 實(shí)際上,ORACLE在解析的過程中, 會(huì)將’*’ 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時(shí)間.
2．用TRUNCATE替代DELETE
3．使用表的別名(Alias)
當(dāng)在SQL語句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來,就可以減少解析的時(shí)間并減少那些由Column歧義引起的語法錯(cuò)誤.
4.索引的等級(jí)
一般情況索引等級(jí)如下：
a) 等式比較比范圍比較要高。
b) 唯一性索引比非唯一性索引要高。
c) 一般情況下單列索引等級(jí)要比復(fù)合索引高，但如果where子句中包含所 有復(fù)合索引的字段，則復(fù)合索引等級(jí)高。
例如：
SELECT col1, ...
FROM emp
WHERE emp_name = 'GURRY'
AND emp_no = 127
AND dept_no = 12
Index1 (emp_name)
Index2 (emp_no, dept_no, emp_name)
ORACLE將使用索引Index2。
5.統(tǒng)計(jì)信息分析
在現(xiàn)實(shí)當(dāng)中，有關(guān)analyze分析有以下兩種誤區(qū)：
a) 只要對(duì)主要的或者關(guān)鍵的表格做分析即可。其實(shí)正確的應(yīng)該是需要對(duì)所有涉及到的表格都做過分析。
b) 做一次分析后即可高枕無憂。事實(shí)上，一旦做過分析后，就應(yīng)該定期更新這些統(tǒng)計(jì)信息，以保證統(tǒng)計(jì)信息的正確性。
6．Exists總比In快
有許多人認(rèn)為用Exists總比用In要快,這也是一個(gè)誤區(qū)。有時(shí)用in反而比用Exists快。
他們之間的區(qū)別如下：
IN subquery，首先執(zhí)行subquery，由subquery來驅(qū)動(dòng)父查詢。而Exists子查詢則由父查詢來驅(qū)動(dòng)子查詢。這就是兩者之間的區(qū)別。
所以如果子查詢小的話，則可以采用in會(huì)快一些，如果子查詢大的話，則采用exists會(huì)快一些。
7．>與>=
大于或小于操作符一般情況下是不用調(diào)整的，因?yàn)樗兴饕蜁?huì)采用索引查找，但有的情況下可以對(duì)它進(jìn)行優(yōu)化，如一個(gè)表有100萬記錄，一個(gè)數(shù)值型字段A，
30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。
那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了，因?yàn)锳>2時(shí)ORACLE會(huì)先找出
為2的記錄索引再進(jìn)行比較，而A>=3時(shí)ORACLE則直接找到=3的記錄索引。
8. 使用索引來避免排序
索引是排好序的，在某些情況下可以使用索引來避免排序。
SELECT acc_name, acc_surname
FROM account acct
ORDER BY 1;
SELECT /*+ INDEX_ASC(acct acc_ndx1) */ acc_name,acc_surname
FROM account acct;
9.大對(duì)象操作
a)Big Insert
(1)direct insert(serial and parallel)
insert /*+append*/into tab1 select * from tab2;
Insert /*+append parallel(emp,8)*/ into emp select * from emp_bak;
(2)nologging
insert into tab1 nologging select * from tab2;
(3)Large extent size
更大的extent可以獲得更好的insert性能。
(5)Large rollback segment
b)Large Index Create
大的索引extent size值
大的Sort_area_size值
采用nologging
采用parallel
大的臨時(shí)表空間
alter session sort_area_size=100000000;
create index xxx on aa(ab) nologging parallel 2;
c)Large Delete
分幾次delete。
附錄一
Hint全集
174. /*+ALL_ROWS*/
表明對(duì)語句塊選擇基于開銷的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化.例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
175. /*+FIRST_ROWS*/
表明對(duì)語句塊選擇基于開銷的優(yōu)化方法,并獲得最佳響應(yīng)時(shí)間,使資源消耗最小化.例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
176. /*+CHOOSE*/
表明如果數(shù)據(jù)字典中有訪問表的統(tǒng)計(jì)信息,將基于開銷的優(yōu)化方法,并獲得最佳的吞吐量;表明如果數(shù)據(jù)字典中沒有訪問表的統(tǒng)計(jì)信息,將基于規(guī)則開銷的優(yōu)化方法;例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
177. /*+ RULE*/
表明對(duì)語句塊選擇基于規(guī)則的優(yōu)化方法.例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
178. /*+ FULL(TABLE)*/
表明對(duì)表選擇全局掃描的方法.例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';
179. /*+ROWID(TABLE)*/
提示明確表明對(duì)指定表根據(jù)ROWID進(jìn)行訪問.例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';
180. /*+CLUSTER(TABLE)*/
提示明確表明對(duì)指定表選擇簇掃描的訪問方法,它只對(duì)簇對(duì)象有效.例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
181. /*+ INDEX(TABLE INDEX_NAME)*/
/*+index(table ind_name) index(table ind_name)*/
表明對(duì)表選擇索引的掃描方法.例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
182. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對(duì)表選擇索引升序的掃描方法.例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';
183. /*+INDEX_COMBINE*/
為指定表選擇位圖訪問路經(jīng),如果INDEX_COMBINE中沒有提供作為參數(shù)的索引,將選擇出位圖索引的布爾組合方式.例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE
184. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明確命令優(yōu)化器使用索引作為訪問路徑.例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;
185. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明對(duì)表選擇索引降序的掃描方法.例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';
186. /*+INDEX_FFS(TABLE INDEX_NAME)*/
對(duì)指定的表執(zhí)行快速全索引掃描,而不是全表掃描的辦法.例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
187. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明確進(jìn)行執(zhí)行規(guī)劃的選擇,將幾個(gè)單列索引的掃描合起來.例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';
188. /*+USE_CONCAT*/
對(duì)查詢中的WHERE后面的OR條件進(jìn)行轉(zhuǎn)換為UNION ALL的組合查詢.例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
189. /*+NO_EXPAND*/
對(duì)于WHERE后面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基于優(yōu)化器對(duì)其進(jìn)行擴(kuò)展.例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
190. /*+NOWRITE*/
禁止對(duì)查詢塊的查詢重寫操作.
191. /*+REWRITE*/
可以將視圖作為參數(shù).
192. /*+MERGE(TABLE)*/
能夠?qū)σ晥D的各個(gè)查詢進(jìn)行相應(yīng)的合并.例如:
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) Va WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
193. /*+NO_MERGE(TABLE)*/
對(duì)于有可合并的視圖不再合并.例如:
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;
194. /*+ORDERED*/
根據(jù)表出現(xiàn)在FROM中的順序,ORDERED使ORACLE依此順序?qū)ζ溥B接.例如:
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;
195. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進(jìn)行連接,并把指定表作為內(nèi)部表.例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
196. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合并排序連接方式連接起來.例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
197. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過哈希連接方式連接起來.例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
198. /*+DRIVING_SITE(TABLE)*/
強(qiáng)制與ORACLE所選擇的位置不同的表進(jìn)行查詢執(zhí)行.例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
199. /*+LEADING(TABLE)*/
將指定的表作為連接次序中的首表.
200. /*+CACHE(TABLE)*/
當(dāng)進(jìn)行全表掃描時(shí),CACHE提示能夠?qū)⒈淼臋z索塊放置在緩沖區(qū)緩存中最近最少列表LRU的最近使用端例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
201. /*+NOCACHE(TABLE)*/
當(dāng)進(jìn)行全表掃描時(shí),CACHE提示能夠?qū)⒈淼臋z索塊放置在緩沖區(qū)緩存中最近最少列表LRU的最近使用端，例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
202. /*+APPEND*/
直接插入到表的最后,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
203. /*+NOAPPEND*/
通過在插入語句生存期內(nèi)停止并行模式來啟動(dòng)常規(guī)插入.
insert /*+noappend*/ into test1 select * from test4;
附錄二
STATSPACK包的使用指南
1.oracle8.1.6開始引進(jìn)statspack，statspack是診斷oracle性能的強(qiáng)有力的工具。
2.安裝前準(zhǔn)備
A.首先是系統(tǒng)參數(shù)的確認(rèn)：
job_query_processes：為了建立自動(dòng)任務(wù)，執(zhí)行數(shù)據(jù)收集，該參數(shù)要大于0
time_statistics：為了收集操作系統(tǒng)計(jì)時(shí)信息等，需要將其設(shè)置為TRUE
B.建議最好是單獨(dú)的為perfstat用戶（即安裝statspack要建的用戶）單獨(dú)建立數(shù)據(jù)表空間和臨時(shí)表空間，數(shù)據(jù)表空間至少要有100M的空閑空間，否則創(chuàng)建statspack對(duì)象會(huì)失敗，如果打算長(zhǎng)期使用statspack，可以考慮建稍大些的數(shù)據(jù)表空間。
3.安裝
A.安裝腳本
安裝的腳本所在目錄是$ORACLE_HOME/rdbms/admin，在oracle8.1.6版本安裝腳本是statscre.sql，之后 8.1.7版本開始就是spcreate.sql，安裝所需用戶在9i之前的需要internal或者擁有sysdba權(quán)限的用戶，9i需要的用戶是 sys（9i已經(jīng)不存在internal用戶了）
執(zhí)行安裝腳本如下：
SQL> @$ORACLE_HOME/rdbms/admin/spcreate
B. 在安裝過程中，需要填寫perfstat用戶的密碼，并且選擇perfstat用戶的數(shù)據(jù)表空間和臨時(shí)表空間，安裝完成之后，察看相應(yīng)的.lis文件檢查安裝是否正確無誤，有問題可以通過spdrop.sql完成statspack的卸載，重新運(yùn)行spcreate.sql完成statspack的安裝。
4. 測(cè)試
最簡(jiǎn)單的statspack報(bào)告生成，運(yùn)行兩次statspack.snap，然后運(yùn)行spreport.sql生成一個(gè)基于兩個(gè)時(shí)間點(diǎn)的報(bào)告。如果是8.1.7.3之前版本的Oracle，需要修改spcpkg.sql，要將substr修改為substrb，如下位置：
select l_snap_id
, p_dbid
, p_instance_number
, substr(sql_text,1,31) ? substrb(sql_text,1,31)
建立簡(jiǎn)單的statspack報(bào)告過程如下：
SQL> execute statspack.snap (i_snap_level=>10)
PL/SQL procedure successfully completed.
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> @$ORACLE_HOME/rdbms/admin/spreport
Spreport的執(zhí)行過程中會(huì)列出需要選擇的快照，你需要填寫該報(bào)告描述的開始和結(jié)束的快照序號(hào)，并填寫報(bào)告的文件名，當(dāng)然可以不填，使用默認(rèn)的報(bào)告文件名，默認(rèn)的會(huì)生成在目錄$ORACLE_HOME/rdbms/admin中
這樣就可以驗(yàn)證statspack已經(jīng)正確的安裝完成了
自動(dòng)收集statspack快照
正常在真正的環(huán)境下，我們是需要連續(xù)的采樣一段時(shí)間，這樣生成的statspack才能更好的反映系統(tǒng)的現(xiàn)狀，我們是可以通過spauto.sql來自動(dòng)收集數(shù)據(jù)的。
主要可能會(huì)設(shè)計(jì)到修改如下部分的內(nèi)容
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
commit;
end;
/
主要是修改1/24這個(gè)值，目前是一個(gè)小時(shí)自動(dòng)收集一次數(shù)據(jù)，如果要改動(dòng)為半個(gè)小時(shí)收集一次數(shù)據(jù)就修改為1/48,同理，進(jìn)行或大或小的修改。
執(zhí)行后，可以在spauto.lis文件中看到當(dāng)前自動(dòng)收集數(shù)據(jù)的job號(hào)等信息。當(dāng)想要生成statspack報(bào)告的時(shí)候，只要選擇任何兩個(gè)不跨越停機(jī)時(shí)間的快照序號(hào)就可以了。注意，statspack是不能跨越停機(jī)的。