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<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<=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;

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是不能跨越停机的。





阅读全文(125次) / 评论 / 丢小纸条 / 文件夹: Oracle
收藏: QQ书签 del.icio.us / 订阅: Google 抓虾

  
Exp&Imp
suetrain @ 2005-11-14 18:06


Import and Export Utilities
Transportable Tablespaces
Export introduction
1. move schema, data, table
2. copy data or table to different user
3. create logical backup (simple and need not to invest on hardware)
4. re-organize data (exp->drop->imp)
轉出資料的權限
1. create session
2. exp_full_database (when not table owner)
create user louis IDENTIFIED  BY "louis";
       --drop user louis;
grant create session to louis;
      --revoke create session  from louis;
grant exp_full_database to louis;
      --revoke exp_full_database from louis;
Export 語法
exp parfile = exp_table.txt
        userid=test/[e-mail]
        file=m:\oracle\R105.dmp
        log=m:\ORAcle\R105.log
        tables=(test1.r_ base_t)
        grants=y
        indexes=y
        rows=y
        constraints=y
參數說明
userid :            must be the first parameter on the command line
Buffer :             the number of bytes used to fetch data rows
                       (if buffer = 0, fetch only 1 row at a time)
compress :       default : Y
                       Y : all tables will import into one initial extent.
consistent :      default :N,
                       Y : could cause rollback segments to grow very large
                       if many changes are happening to the database.
constraints:      default : Y
direct :             default : N,
                       Y : export will read data directly, without using the public buffer cache
feedback :        default value = 0
file :                 default : Expdat.dmp
filesize:            maximum dump file size
full                   default : N
                       exp_full_database privilege needed
grants              default : Y
inctype             complete, cumulative, or incremental export
indexes            default : Y
log                   export log filename
owner               one or more usernames whose objects will be exported
query                export data from a SQL where statement
rows                 N: structure-only export
tables               export table list
triggers             default : Y
Export demo
1.      exp_multi_tables
userid=test/[e-mail]
file=m:\oracle\multi_table.dmp
log=m:\ORAcle\multi_table.log
tables=(table1.......tablen )
grants=y
indexes=y
rows=y
constraints=y
2.      exp owner
userid=test/[e-mail]
owner=(test2)
file=m:\oracle\owner.dmp
log=m:\ORAcle\owner.log
grants=y
indexes=y
rows=y
constraints=y
3.      exp query
userid=test1/[e-mail]
file=m:\oracle\r105_Query.dmp
log=m:\ORAcle\r105_Query.log
tables=(table)
grants=y
indexes=y
rows=y
constraints=y
query="where MODEL_NAME LIKE 'U54%'"
4.      exp table
userid=louis/[e-mail]
file=m:\oracle\r1051.dmp
log=m:\ORAcle\r1051.log
tables=(table )
grants=y
indexes=y
rows=y
constraints=y
5.      exp tablestructure
userid=test2/[e-mail]
file=m:\oracle\R105.dmp
log=m:\ORAcle\R105.log
tables=(table )
grants=y
indexes=y
constraints=y
rows= N
6.      exp tablespace
userid="system/[e-mail] as sysdba"
file=m:\oracle\csfis.dmp
log=m:\ORAcle\csfis.log
tablespaces = (csfis)
transport_tablespace = Y
constraints=N
Import introduction
import 語法
imp parfile = imp_table_sap_dn.txt
參數說明
full :               with imp_full_database privilege
                      imp dump file exported by other user
fromuser         only table owners in fromuser list will be imported
touser            change table owner to touser
tables             import tables list
ignore :           if table already exist
                      N : table structure will not be created, no data will be inserted
                      Y : table structure will not be created, data not violating any constraints will be inserted
重新組織資料
1.   建立執行匯出所需的參數檔案
compress = Y (建立可容納所有資料列的單一大型初始範圍)
direct = Y (加快資料匯出的速度)
2.   執行匯出
3.   卸除資料表 (if foreign keys, can save definition first)
4.   建立匯入參數檔案
5.   執行匯入
Transportable Tablespaces
execute sys.dbms_tts.transport_set_check ('csfis',true);
select * from sys.transport_set_violations;
exp file = csfis.dmp
transport_tablespace = Y
tablespaces = (csfis)
constraints = N
select file_name from DBA_DATA_FILES
where tablespace_name in (‘csfis’)
imp memo
1.imp multitable by user
userid=test2/[e-mail]
file=m:\oracle\multi_table.dmp
log=m:\oracle\multi_table.log
fromuser=test1
touser=test2
grants=y
indexes=y
rows=y
constraints=y

评论

2 楼 celavi 2012-11-29  

非常好的文章，谢谢分享！

1 楼 hotjava 2008-06-04  

这篇文章对我这种初手实在太好了,不介意我转一下吧.