ORACLE SQL TUNING各种技巧及杂乱实例ITeye - 亚美娱乐

ORACLE SQL TUNING各种技巧及杂乱实例ITeye

2019年03月30日13时29分47秒 | 作者: 寻巧 | 标签: 索引,履行,方法 | 浏览: 1952

一.优化器方法
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 to_date(m_date,yyyymmdd);
这样一来,该进程的履行时刻快的时分大约在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
其履行计划和计算信息如下:
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;
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子句中防止运用 ‘ * ‘
当你想在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 SYSDATE;
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是不能跨过停机的。

参阅至:http://www.360doc.com/content/11/0407/15/1107705_107828361.shtml

如有过错,欢迎纠正

邮箱:czmcj@163.com

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表亚美娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章