ORACLE SQL 索引（值得研读）

客官°小女子只卖身不卖艺 2022-08-27 03:59 212阅读 0赞

ORACLE SQL TUNING  
一．优化器模式  
ORACLE的优化器共有3种:  
a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性)  
为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须定期更新统计信息，以保证数据库中的对象统计信息(object statistics)的准确性.  
如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。

二．访问Table的方式  
ORACLE 采用两种访问表中记录的方式:  
a. 全表扫描  
全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数 据块(database block)的方式优化全表扫描。  
  
b. 索引扫描  
你可以采用基于ROWID的访问方式情况,提高访问表的效率, ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

其中ORACLE对索引又有两种访问模式.  
a)索引唯一扫描 ( INDEX UNIQUE SCAN)  
大多数情况下, 优化器通过WHERE子句访问INDEX.  
例如:  
表LOADING有两个索引 : 建立在LOADING列上的唯一性索引LOADING\_PK和建立在MANAGER列上的非唯一性索引IDX\_MANAGER.  
SELECT loading  
FROM LOADING  
WHERE LOADING = ‘ROSE HILL’;  
在内部 , 上述SQL将被分成两步执行, 首先 , LOADING\_PK 索引将通过索引唯一扫描的方式被访问 , 获得相对应的ROWID, 通过ROWID访问表的方式执行下一步检索.  
如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通过ROWID访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.  
下面SQL只需要INDEX UNIQUE SCAN 操作.  
SELECT LOADING  
FROM LOADING  
WHERE LOADING = ‘ROSE HILL’;  
  
b)索引范围查询(INDEX RANGE SCAN)  
适用于两种情况:  
1. 基于一个范围的检索  
2. 基于非唯一性索引的检索  
例1:  
SELECT LOADING  
FROM LOADING  
WHERE LOADING LIKE ‘M%’;  
  
WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式查询LODGING\_PK . 由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描  
低一些.  
例2:  
SELECT LOADING  
FROM LOADING  
WHERE MANAGER = ‘BILL GATES’;  
这个SQL的执行分两步, IDX\_MANAGER的索引范围查询(得到所有符合条件记录的ROWID) 和下一步同过ROWID访问表得到LOADING列的值. 由于IDX\_MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描.  
  
由于SQL返回LOADING列,而它并不存在于IDX\_MANAGER索引中, 所以在索引范围查询后会执行一个通过ROWID访问表的操作.  
WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.  
SELECT LOADING  
FROM LOADING  
WHERE MANAGER LIKE ‘％HANMAN’;  
在这种情况下，ORACLE将使用全表扫描.

三．SQL调优的本质就是调整执行计划。  
在好多情况下，oracle自动选择的执行计划并不是最优的，这时需要我们人工去干预。(什么是执行计划?)

对SQL调优基本步骤：  
a) 捕获SQL语句  
b) 产生SQL语句的执行计划；  
c) 验证统计信息(SQL语句涉及到的表格是否做过分析)，表格信息(结果集的记录数，索引)，字段上面数据分布特点  
d) 通过手工收集到的信息，形成自己理想的执行计划。  
e) 如果做过分析，则重新分析相关表格或者做柱状图分析。  
f) 如果没有做过分析，则通过尝试不同的Hint，从而获得合适的执行计划。  
g) 当我们正常无法调优到位时，可以打开10053事件打开优化器的跟踪，看看Oracle如何选择的.  
alter session set events='10053 trace name context forever,level 2';  
  
四．如何捕获SQL语句  
捕获SQL语句的方法有如下几种：  
1．SQL TRACE或10046跟踪某个模块。  
2．PERFSTAT性能统计包，使用方法见附录二。  
3．V$SQL，V$SESSION\_WAIT，V$SQL\_TEXT  
五．如何查看执行计划  
查看SQL语句的执行计划有以下几种：  
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  
适合于小表(几千条，几万条记录)与大表做联接  
在联接列上有索引。

分内表和外表(驱动表)，靠近from子句的是内表。从效率上讲，小表应该作外表，大表应该作内表，即大表查询时走索引。

COST= Access cost of A(驱动表) + (access cost of B \* number of rows from A)

成本计算方法：  
设小表100行，大表100000行。

两表均有索引：  
如果小表在内，大表在外(驱动表)的话，则扫描次数为：  
100000+100000\*2 (其中2表示IO次数，一次索引，一次数据)  
如果大表在内，小表在外(驱动表)的话，则扫描次数为：  
100+100\*2.

两表均无索引：  
如果小表在内，大表在外的话，则扫描次数为：  
100000+100\*100000  
如果大表在内，小表在外的话，则扫描次数为：  
100+100000\*100

注意：如果一个表有索引，一个表没有索引，ORACLE会将没有索引的表作驱动表。如果两个表都有索引，则外表作驱动表。如果两个都没索引的话，则也是外表作驱动表。

基本的执行计划如下所示：  
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

适合于大表与大表，小表(几十万，几百万)与大表之间的联连。  
联接列上不需要索引。

基本执行计划如下：  
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的成本和返回集合并没有直接的关系，所以当返回结果集比较大的时候一般具有较好的性能。

为了加快hash join的速度，可以调大hash\_area\_size和pga\_aggregate\_target（默认为25M）的值。

c) Sort Merge join

每一个Row Source在Join列上均排序。  
然后两个排序后的Row Source合并后，作一个结果集返回。  
Sort/Merge Join仅仅对equal Join有效。

基本执行计划  
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的主要构成部分。这样sort\_area\_size的大小将很大程度决定Merge Join的大小。同样如果A表或者B表已经经过排序的，那么Merge Join往往具有很好的性能。其不会走索引。

没有驱动表的概念，即时响应能力较差。

七．一般情况下最常见的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'  
  
我们取其中的select语句进行调优。在调整之前，原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

针对cardsuage200508表格的特性，我们在CDRTYPE字段上建立一个位图索引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电话卡';  
这样调整后，只需要几秒钟即可出来。

3. Use of single-column index merge  
复合索引有的时候比单列索引效率更高。根据where子句中的具体情况，有 时可以建立复合索引。例如：  
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语句执行需要3分27秒。

我们做了以下优化：  
在charge\_bill表格的accountnum,status,total,invoiceid列上建立一个复合索引。这样上述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');  
该语句需要执行大概72000次。整个过程执行大概需要100分钟左右。

将:b1以具体的值代替，这条SQL语句执行很快，大概0.1秒左右。

我们做了以下优化：  
在invoiceid,billingbegindate列上创建了一个索引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

这样一来，该过程的执行时间快的时候大概在10分钟左右，慢的时候(IO异常紧张的时)大概在30分钟左右。

4. Misuse of nested loop, sort merge, or hash join  
表格之间的连接方式和连接顺序都将极大的影响SQL语句的性能。这种问 题在平时最常见。ORACLE在处理5张或5张以上的表格的连接时候，很容 易出问题。一般情况下，谨记前面表格之间的连接原则，即可以处理此类问 题。  
  
例如：  
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个小时左右。

优化：  
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');

这样优化后，只需要40分钟左右即可。

八．案例  
1． 循环Update操作  
  
以下过程太慢了， 半个小时连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分钟不到就可产生中间表，然后再update只需几分钟即可。

2． 部分表格未做统计信息分析  
  
网通OA系统自从oracle服务器从pc服务器上迁到小型机上后，其CPU利用率经常冲到很高。而其中每一个进程在某个瞬间将占用40%左右的CPU。这些进程都是通过jdbc thin client 连过来的。

通过抓取其sql\_text，发现以下两条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;

其执行计划和统计信息如下：

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语句执行的时间也不长，就几秒钟，但是我们看到consistent gets很高有27万多，这个操作就是消耗CPU的祸首。从执行计划来看，其执行计划显然不可理，问题出在表格的连接顺序上面，应该是deal\_info表格做为驱动表先访问。

检查这些表格的统计分析，发现step\_def表格未做分析，对该表格做统计信息分析，并对deal\_info表做柱状图分析后：  
analyze table deal\_info compute statistics for all indexed columns;

其执行计划正是我们所想要的，同时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语句执行时间要2分钟左右。  
执行计划如下：  
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)

我们收集表格信息和结果集的信息:  
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

通过对上面这些信息进行分析，我们可以发现这个问题也可以归结为表格之间的连接顺序上面。通过将SO表做柱状图分析后，该SQL语句只需1秒钟即可出来。  
Analyze table so compute statistics for all indexed columns;

执行计划变成如下：  
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的使用  
\--停机保号用户数(除欠费)  
select 'XJ'||1||'180','停机保号用户数',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','停机保号用户数',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

调整：  
not in 改成not exists  
create index idx\_serviceorderid on cbq\_qf\_usage1(serviceorderid) nologging;

select 'XJ'||1||'180','停机保号用户数',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','停机保号用户数',1,'200509',a.groupid;

Elapsed: 00:00:01.36

九．其他  
1．SELECT子句中避免使用 ‘ \* ‘  
当你想在SELECT子句中列出所有的COLUMN时,使用动态 SQL列引用 ‘\*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’\*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.  
2．用TRUNCATE替代DELETE  
3．使用表的别名(Alias)  
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

4.索引的等级  
一般情况索引等级如下：  
a) 等式比较比范围比较要高。  
b) 唯一性索引比非唯一性索引要高。  
c) 一般情况下单列索引等级要比复合索引高，但如果where子句中包含所 有复合索引的字段，则复合索引等级高。  
例如：  
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.统计信息分析  
在现实当中，有关analyze分析有以下两种误区：

a) 只要对主要的或者关键的表格做分析即可。其实正确的应该是需要对所有涉及到的表格都做过分析。

b) 做一次分析后即可高枕无忧。事实上，一旦做过分析后，就应该定期更新这些统计信息，以保证统计信息的正确性。

6．Exists总比In快  
有许多人认为用Exists总比用In要快,这也是一个误区。有时用in反而比用Exists快。  
他们之间的区别如下：  
IN subquery，首先执行subquery，由subquery来驱动父查询。而Exists子查询则由父查询来驱动子查询。这就是两者之间的区别。  
所以如果子查询小的话，则可以采用in会快一些，如果子查询大的话，则采用exists会快一些。

7．>与>=  
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，  
30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。  
那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出  
为2的记录索引再进行比较，而A>=3时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.大对象操作  
  
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  
大的临时表空间

alter session sort\_area\_size=100000000;  
create index xxx on aa(ab) nologging parallel 2;

c)Large Delete  
分几次delete。

附录一  
Hint全集  
174. /\*+ALL\_ROWS\*/

表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.例如:  
SELECT /\*+ALL+\_ROWS\*/ EMP\_NO,EMP\_NAM,DAT\_IN FROM BSEMPMS WHERE EMP\_NO='CCBZZP';

175. /\*+FIRST\_ROWS\*/

表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.例如:  
SELECT /\*+FIRST\_ROWS\*/ EMP\_NO,EMP\_NAM,DAT\_IN FROM BSEMPMS WHERE EMP\_NO='CCBZZP';

176. /\*+CHOOSE\*/

表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;例如:  
SELECT /\*+CHOOSE\*/ EMP\_NO,EMP\_NAM,DAT\_IN FROM BSEMPMS WHERE EMP\_NO='CCBZZP';

177. /\*+ RULE\*/

表明对语句块选择基于规则的优化方法.例如:  
SELECT /\*+ RULE \*/ EMP\_NO,EMP\_NAM,DAT\_IN FROM BSEMPMS WHERE EMP\_NO='CCBZZP';

178. /\*+ FULL(TABLE)\*/

表明对表选择全局扫描的方法.例如:  
SELECT /\*+FULL(A)\*/ EMP\_NO,EMP\_NAM FROM BSEMPMS A WHERE EMP\_NO='CCBZZP';

179. /\*+ROWID(TABLE)\*/

提示明确表明对指定表根据ROWID进行访问.例如:  
SELECT /\*+ROWID(BSEMPMS)\*/ \* FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'  
AND EMP\_NO='CCBZZP';

180. /\*+CLUSTER(TABLE)\*/  
　  
　　提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.例如:  
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)\*/  
表明对表选择索引的扫描方法.例如:  
SELECT /\*+INDEX(BSEMPMS SEX\_INDEX) USE SEX\_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS \*/ FROM BSEMPMS WHERE SEX='M';

182. /\*+INDEX\_ASC(TABLE INDEX\_NAME)\*/

表明对表选择索引升序的扫描方法.例如:  
SELECT /\*+INDEX\_ASC(BSEMPMS PK\_BSEMPMS) \*/ FROM BSEMPMS WHERE DPT\_NO='CCBZZP';

183. /\*+INDEX\_COMBINE\*/

为指定表选择位图访问路经,如果INDEX\_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.例如:  
SELECT /\*+INDEX\_COMBINE(BSEMPMS SAL\_BMI HIREDATE\_BMI)\*/ \* FROM BSEMPMS  
WHERE SAL<5000000 AND HIREDATE

184. /\*+INDEX\_JOIN(TABLE INDEX\_NAME)\*/

提示明确命令优化器使用索引作为访问路径.例如:  
SELECT /\*+INDEX\_JOIN(BSEMPMS SAL\_HMI HIREDATE\_BMI)\*/ SAL,HIREDATE  
FROM BSEMPMS WHERE SAL<60000;

185. /\*+INDEX\_DESC(TABLE INDEX\_NAME)\*/

表明对表选择索引降序的扫描方法.例如:  
SELECT /\*+INDEX\_DESC(BSEMPMS PK\_BSEMPMS) \*/ FROM BSEMPMS WHERE DPT\_NO='CCBZZP';

186. /\*+INDEX\_FFS(TABLE INDEX\_NAME)\*/

对指定的表执行快速全索引扫描,而不是全表扫描的办法.例如:  
SELECT /\*+INDEX\_FFS(BSEMPMS IN\_EMPNAM)\*/ \* FROM BSEMPMS WHERE DPT\_NO='TEC305';

187. /\*+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';

188. /\*+USE\_CONCAT\*/

对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.例如:  
SELECT /\*+USE\_CONCAT\*/ \* FROM BSEMPMS WHERE DPT\_NO='TDC506' AND SEX='M';

189. /\*+NO\_EXPAND\*/

对于WHERE后面的OR 或者IN-LIST的查询语句,NO\_EXPAND将阻止其基于优化器对其进行扩展.例如:  
SELECT /\*+NO\_EXPAND\*/ \* FROM BSEMPMS WHERE DPT\_NO='TDC506' AND SEX='M';

190. /\*+NOWRITE\*/

禁止对查询块的查询重写操作.

191. /\*+REWRITE\*/

可以将视图作为参数.

192. /\*+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) Va WHERE A.DPT\_NO=V.DPT\_NO  
AND A.SAL>V.AVG\_SAL;

193. /\*+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;

194. /\*+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;

195. /\*+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;

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)\*/

强制与ORACLE所选择的位置不同的表进行查询执行.例如:  
SELECT /\*+DRIVING\_SITE(DEPT)\*/ \* FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT\_NO=DEPT.DPT\_NO;

199. /\*+LEADING(TABLE)\*/

将指定的表作为连接次序中的首表.

200. /\*+CACHE(TABLE)\*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端例如:  
SELECT /\*+FULL(BSEMPMS) CAHE(BSEMPMS) \*/ EMP\_NAM FROM BSEMPMS;

201. /\*+NOCACHE(TABLE)\*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端，例如:  
SELECT /\*+FULL(BSEMPMS) NOCAHE(BSEMPMS) \*/ EMP\_NAM FROM BSEMPMS;

202. /\*+APPEND\*/

直接插入到表的最后,可以提高速度.  
insert /\*+append\*/ into test1 select \* from test4 ;

203. /\*+NOAPPEND\*/

通过在插入语句生存期内停止并行模式来启动常规插入.  
insert /\*+noappend\*/ into test1 select \* from test4;

附录二  
STATSPACK包的使用指南  
1.oracle8.1.6开始引进statspack，statspack是诊断oracle性能的强有力的工具。  
2.安装前准备  
A.首先是系统参数的确认：  
job\_query\_processes：为了建立自动任务，执行数据收集，该参数要大于0  
time\_statistics：为了收集操作系统计时信息等，需要将其设置为TRUE

B.建议最好是单独的为perfstat用户（即安装statspack要建的用户）单独建立数据表空间和临时表空间，数据表空间至少要有100M的空闲空间，否则创建statspack对象会失败，如果打算长期使用statspack，可以考虑建稍大些的数据表空间。  
3.安装  
A.安装脚本  
安装的脚本所在目录是$ORACLE\_HOME/rdbms/admin，在oracle8.1.6版本安装脚本是statscre.sql，之后 8.1.7版本开始就是spcreate.sql，安装所需用户在9i之前的需要internal或者拥有sysdba权限的用户，9i需要的用户是 sys（9i已经不存在internal用户了）  
执行安装脚本如下：  
SQL> @$ORACLE\_HOME/rdbms/admin/spcreate  
  
B. 在安装过程中，需要填写perfstat用户的密码，并且选择perfstat用户的数据表空间和临时表空间，安装完成之后，察看相应的.lis文件检查安装是否正确无误，有问题可以通过spdrop.sql完成statspack的卸载，重新运行spcreate.sql完成statspack的安装。

4. 测试  
最简单的statspack报告生成，运行两次statspack.snap，然后运行spreport.sql生成一个基于两个时间点的报告。如果是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)  
  
建立简单的statspack报告过程如下：  
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的执行过程中会列出需要选择的快照，你需要填写该报告描述的开始和结束的快照序号，并填写报告的文件名，当然可以不填，使用默认的报告文件名，默认的会生成在目录$ORACLE\_HOME/rdbms/admin中  
这样就可以验证statspack已经正确的安装完成了  
  
自动收集statspack快照  
正常在真正的环境下，我们是需要连续的采样一段时间，这样生成的statspack才能更好的反映系统的现状，我们是可以通过spauto.sql来自动收集数据的。  
  
主要可能会设计到修改如下部分的内容  
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这个值，目前是一个小时自动收集一次数据，如果要改动为半个小时收集一次数据就修改为1/48,同理，进行或大或小的修改。  
  
执行后，可以在spauto.lis文件中看到当前自动收集数据的job号等信息。当想要生成statspack报告的时候，只要选择任何两个不跨越停机时间的快照序号就可以了。注意，statspack是不能跨越停机的。