oracle :性能优化的一个案例

一．通过top命令看CPU的利用率：

#top

以下是TOP的结果：
last pid: 11225;  load averages:  7.95,  6.63,  6.25   17:19:35
273 processes: 259 sleeping, 3 running, 5 zombie, 3 stopped, 3 on cpu
CPU states: 10.0% idle, 75.0% user, 15.0% kernel,  0.0% iowait,  0.0% swap
Memory: 8192M real, 4839M free, 2147M swap in use, 12G swap free

   PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
 10929 oracle     1  59    0 1048M 1022M cpu/6    2:52 21.59% oracle
 11224 oracle     1  59    0 1047M 1018M run      0:03  4.22% oracle
  8800 oracle     1  59    0 1048M 1022M run      1:39  3.99% oracle
  4354 oracle     1  59    0 1049M 1023M cpu/4    0:28  3.46% oracle
  3537 oracle     1  59    0 1048M 1022M sleep    1:01  1.93% oracle
 29499 oracle     1  59    0 1048M 1022M sleep   30.0H  1.84% oracle
 11185 oracle     1  59    0 1047M 1020M sleep    0:01  0.74% oracle
 11225 wacos      1  44    0 2832K 1928K cpu/0    0:00  0.65% top
  9326 oracle     1  59    0 1047M 1020M sleep    0:58  0.50% oracle
   410 root      14  59    0 7048K 6896K run     76.3H  0.42% picld
 21363 oracle     1  59    0 1047M 1019M sleep  574:35  0.36% oracle
 10782 oracle    11  59    0 1052M 1024M sleep  749:05  0.28% oracle
 13415 oracle     1  59    0 1047M 1019M sleep    6:07  0.27% oracle
  5679 oracle    11  59    0 1052M 1026M sleep   79:23  0.19% oracle
  5477 oracle   258  59    0 1056M 1021M sleep   57:32  0.14% oracle

二．通过分析找出了消耗CPU最高进程对应的SQL语句：

SQL>set line 240

SQL>set verify off

SQL>column sid format 999

SQL>column pid format 999

SQL>column S_# format 999

SQL>column username format A9 heading "ORA User"

SQL>column program  format a29

SQL>column SQL      format a60

SQL>COLUMN OSname format a9 Heading "OS User"

SQL>SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,

S.osuser osname,P.serial# S_#,P.terminal,P.program  program,

P.background,S.status,RTRIM(a.sql_text)  SQL

FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+)  AND P.spid LIKE '%&1%';

Enter value for 1:10929

最终找到的SQL语句如下：

SELECT NVL(SUM(RURALCHARGE),0.00) AS Fee FROM LOCALUSAGE WHERE ServiceID=219987 and starttime >= to_date('2003/12/30 13:24:20','YYYY/MM/DD HH24:MI:SS');

 

三．查询localusage表上的分区索引：

通过以下查询得到localusage表上的分区索引有两个：

SQL> select INDEX_NAME from user_part_indexes where TABLE_NAME='LOCALUSAGE';

 

INDEX_NAME
------------------------------
I_LOCALUSAGE_SID
UI_LOCALUSAGE_ST_SEQ

 

通过执行计划分析出这条语句使用的是UI_LOCALUSAGE_ST_SEQ索引，没有使用I_LOCALUSAGE_SID索引，我统计了一下时间，用UI_LOCALUSAGE_ST_SEQ这个索引的效率很差的，返回时间用了2分钟36秒，而使用I_LOCALUSAGE_SID这个索引的话，返回时间为1秒多。而ORACLE缺省使用的是UI_LOCALUSAGE_ST_SEQ索引，因此占用了大量的CPU资源，导致CPU利用率下降。

 

以下是用autotrace的分析过程：

SQL>connect wacos/oss

SQL>set autotrace on

SQL> set timing on
SQL> SELECT NVL(SUM(RURALCHARGE),0.00) AS Fee FROM LOCALUSAGE WHERE ServiceID=219987 and starttime >= to_date('2003/12/30 13:24:20','YYYY/MM/DD HH24:MI:SS');

       FEE
----------
    107.25

 

Elapsed: 00:02:36.19   （返回时间2分36秒）

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=35)
   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (C
          ost=10 Card=10035 Bytes=351225)

 

   4    3         INDEX (RANGE SCAN) OF 'UI_LOCALUSAGE_ST_SEQ' (UNIQUE
          ) (Cost=2 Card=10035)

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   11000821  consistent gets
     349601  physical reads
          0  redo size
        292  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

用HINT强制ORACLE使用I_LOCALUSAGE_SID索引,然后查看执行计划:

SQL>connect wacos/oss

SQL>set autotrace on

SQL>set timing on

SQL> SELECT  /*+ INDEX(LOCALUSAGE I_LOCALUSAGE_SID)*/ NVL(SUM(RURALCHARGE),0.00) AS Fee FROM LOCALUSAGE WHERE ServiceID=219987 and
starttime >= to_date('2003/12/30 13:24:20','YYYY/MM/DD HH24:MI:SS');  

       FEE
----------
    107.25

Elapsed: 00:00:01.15    （返回时间1秒）

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=35)
   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (Cost=15 Card=10035 Bytes=351225)

   4    3         INDEX (RANGE SCAN) OF 'I_LOCALUSAGE_SID' (NON-UNIQUE) (Cost=14 Card=10035)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        307  consistent gets
        232  physical reads
          0  redo size
        292  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

建议研发人员调整该语句，使这条语句缺省使用I_LOCALUSAGE_SID索引，或在语句中使用HINT来强制使用I_LOCALUSAGE_SID索引。