同事打电话跟我说，数据库CPU过高、swap交换频繁，要我马上看看，这里记录下整个过程以供大家参考，也让大家提点意见
$topas
Topas Monitor for host:???fjlt_wb_db01????????EVENTS/QUEUES??? FILE/TTY
Mon Feb 13 10:10:09 2012??Interval:? 2???????? Cswitch?? 13932?Readch? 2718.1K
???????????????????????????????????????????????Syscall? 344.8K? Writech?162.7K
CPU? User%? Kern%?Wait%? Idle%??? ?????????????Reads????? 1231?Rawin???????? 0
ALL?? 89.1?? 10.9???0.0??? 0.0? ???????????????Writes????? 861?Ttyout????? 725
???????????? ???????????????????????????????????Forks???????? 6?Igets???????? 0
Network? KBPS?? I-Pack?O-Pack?? KB-In? KB-Out?Execs???????? 6? Namei??????414
Total??? 97.6K??? 24.9K??51.5K?? 30.8K?? 66.9K Runqueue?? 43.0?Dirblk??????? 0
???????????????????????????????????????????????Waitqueue?? 0.0
Disk??? Busy%???? KBPS????TPS KB-Read KB-Writ??????????????????MEMORY
Total??? 13.0???? 22.6K 2715.0??? 21.7K?932.5? PAGING?????????? Real,MB?? 31744
???????????????????????????????????????????????Faults???? 4337? % Comp????85
FileSystem???????KBPS???? TPS KB-Read KB-Writ? Steals???????0? % Noncomp?? 4
Total???????????638.7?? 398.5? 637.4???1.3??? PgspIn??????? 0?% Client??? 4
???????????????????????????????????????????????PgspOut?????? 0
Name??????????? PID ?CPU%?PgSp Owner?????????? PageIn ???????0?PAGING SPACE
oracle????66847032? 17.3? 10.4 oracle?????????PageOut?????? 1?Size,MB?? 32768
oracle????33751546?? 4.3? 10.7 oracle????????? Sios????????? 1?% Used???? 39
oracle????37093606?? 2.6? 10.6 oracle????????????????????????? ?% Free????61
oracle????51577090?? 2.4? 10.4 oracle????????? NFS (calls/sec)
oracle????60752382?? 2.4? 10.2 oracle????????? SerV2???????? 0?WPAR Activ??? 0
oracle?????2425184?? 2.3? 10.5 oracle????????? CliV2???????? 0?WPAR Total??? 0
oracle????38535516?? 2.1? 10.6 oracle????????? SerV3???????? 0?Press: "h"-help
oracle????65404954?? 2.0? 10.5 oracle????????? CliV3???????? 0???????? "q"-quit
oracle????40239486?? 2.0? 10.3 oracle
oracle????65208590?? 2.0? 11.6 oracle
oracle????60555628?? 1.9? 10.5 oracle
oracle????23658656?? 1.9? 10.3 oracle
oracle????47841658?? 1.9? 10.5 oracle
oracle????52363552?? 1.8? 10.3 oracle
oracle?????2359684?? 1.8? 10.5 oracle
oracle????31916154?? 1.7? 10.6 oracle
oracle????46530580?? 1.7? 10.9 oracle
oracle??????721382?? 1.6? 11.7 oracle
oracle?????2884060?? 1.6? 10.5 oracle
oracle?????5636414?? 1.6? 10.5 oracle
?发现CPU使用率为100%，swap分区交换频繁，其中进程号为66847032的oracle用户进程的CPU使用率为17.3%。
kthr??? memory????????????? page????????????? faults??????? cpu??
----- ----------- ------------------------ ------------ -----------
?r? b?? avm?? fre? re? pi? po? fr?? sr? cy? in?? sy? cs us sy id wa
?2? 0 7506815 658832?? 0?? 0?? 0?? 0??? 0?? 0 6913 154614 20053 31 12 58? 0
?5? 0 7506576 659071?? 0?? 0?? 0?? 0??? 0?? 0 5763 193844 14268 53 11 36? 0
?5? 0 7506767 658880?? 0?? 0?? 0?? 0??? 0?? 0 8699 157452 18035 43 11 45? 0
?2? 0 7506557 659089?? 0?? 0?? 0?? 0??? 0?? 0 6392 135539 20697 27? 9 64? 0
?2? 0 7506286 659359?? 0?? 0?? 0?? 0??? 0?? 0 6518 126193 19194 21 10 69? 0
pi、po均为0，说明swap分区并未频繁交换。问题定位为CPU使用率过高
根据当前时间做ash报告。发现CPU的等待事件占了活动时间的67.62%
另外看到sqlid为dczhdxppd0fmm的sql占用了41.4的活动时间
等了半天没有反应，估计是sga过大，导致查询较慢，先放着让他跑跑。
使用PL\SQL执行如下sql，大致看下目前系统中的等待事件
select * from v$session_wait where wait_class#<>6;
常规的gc cr multi block request等待事件，没有看到比较异常的等待。老办法，制作ash看看
根据当前时间做ash报告。
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
?? DB Id??? DB Name????? Inst Num Instance
----------- ------------ -------- ------------
?4193910433 WBDB??????????????? 1 wbdb1
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified:? html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
?? DB Id???? Inst Num DB Name????? Instance???? Host
------------ -------- ------------ ------------ ------------
? 4193910433??????? 2 WBDB???????? wbdb2??????? fjlt_wb_db02
* 4193910433??????? 1 WBDB???????? wbdb1??????? fjlt_wb_db01
Defaults to current database
Using database id: 4193910433
Defaults to current instance
Using instance number: 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available:? 05-Feb-12 23:00:09?? [? 10995 mins in the past]
Latest ASH sample available:? 13-Feb-12 14:14:45?? [????? 0 mins in the past]
Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
--??? Valid input formats:
--????? To specify absolute begin time:
--??????? [MM/DD[/YY]] HH24:MI[:SS]
--??????? Examples: 02/23/03 14:30:15
--????????????????? 02/23 14:30:15
--????????????????? 14:30:15
--????????????????? 14:30
--????? To specify relative begin time: (start with '-' sign)
--??????? -[HH24:]MI
--??????? Examples: -1:15? (SYSDATE - 1 Hr 15 Mins)
--????????????????? -25??? (SYSDATE - 25 Mins)
Defaults to -15 mins
Enter value for begin_time: 02/13/12 10:00:00
Report begin time specified: 02/13/12 10:00:00
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration:02/13/12 10:30:00
以下输出省略
Enter value for report_name: wbdb01_ash_20120213_1000_1030
以下输出省略
打开生成好的ash报告
?????????????????????? 
从上图可以看到CPU资源的竞争才是真正的问题。
?????? 
根据上图 sqlid 为 dczhdxppd0fmm 的 sql 占用了41.4 的活动时间
完整sql如下
select a.shbxhm, nvl(sum(a.ylbx_jfjs), 0) fromt_wb_shbxmxsbb a, t_wb_sbbqk b where a.pz_xh = b.pz_xh and a.swglm = :1 and to_char(a.sfssq_qsrq,'yyyy-mm-dd') = :2 and to_char(a.sfssq_zzrq, 'yyyy-mm-dd') = :3 and b.zt in('2', '4', '5', '6') group by a.shbxhm
查看该sql的执行计划????????????
这里需要加上相应的用户
SQL> explain plan for select a.shbxhm,nvl(sum(a.ylbx_jfjs), 0) from ETAX.t_wb_shbxmxsbb a, ETAX.t_wb_sbbqk b wherea.pz_xh = b.pz_xh and a.swglm = :1 and to_char(a.sfssq_qsrq, 'yyyy-mm-dd') = :2and to_char(a.sfssq_zzrq, 'yyyy-mm-dd') = :3 and b.zt in ('2', '4', '5', '6')group by a.shbxhm;
Explained.
?
SQL> select * from table(DBMS_XPLAN.display)
?
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 2119216066
?
------------------------------------------------------------
| Id? |Operation???????????????????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
------------------------------------------------------------
|?? 0 | SELECTSTATEMENT????????????? |??????????????? |???? 1 |???86 | 35939?? (1)| 00:07:12 |
|?? 1 |? HASH GROUP BY??????????????? |??????????????? |???? 1 |???86 | 35939?? (1)| 00:07:12 |
|?? 2 |?? NESTED LOOPS??????????????? |???????????? ???|????1 |??? 86 | 35938?? (1)| 00:07:12 |
|*? 3 |??? TABLE ACCESS FULL????????? | T_WB_SHBXMXSBB |???? 1 |???67 | 35936?? (1)| 00:07:12 |
|*? 4 |??? TABLE ACCESS BY INDEX ROWID|T_WB_SBBQK???? |???? 1 |???19 |???? 2?? (0)| 00:00:01 |
|*? 5 |???? INDEX UNIQUE SCAN???????? | PK_T_WB_SBBQK? |???? 1|?????? |???? 1??(0)| 00:00:01 |
?
PLAN_TABLE_OUTPUT
---------------------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 3 -filter("A"."SWGLM"=TO_NUMBER(:1) AND
?????????????TO_CHAR(INTERNAL_FUNCTION("A"."SFSSQ_QSRQ"),'yyyy-mm-dd')=:2AND
?????????????TO_CHAR(INTERNAL_FUNCTION("A"."SFSSQ_ZZRQ"),'yyyy-mm-dd')=:3)
?? 4 -filter("B"."ZT"='2' OR "B"."ZT"='4' OR"B"."ZT"='5' OR "B"."ZT"='6')
?? 5 -access("A"."PZ_XH"="B"."PZ_XH")
?
21 rows selected.
可以看到T_WB_SHBXMXSBB并没有走索引，
查看T_WB_SHBXMXSBB的数据量
SQL>select count(*) from ETAX.t_wb_shbxmxsbb;
? COUNT(*)
----------
?? 9495977
执行
select * from dba_ind_columns where upper(table_name)='T_WB_SHBXMXSBB';
查看表的索引情况如下
发现并未在sfssq_qsrq和sfssq_zzrq这两列上创建索引.
解决方案，在sfssq_qsrq和sfssq_zzrq这两列上创建普通索引或者函数索引，如果网报系统中to_char(a.sfssq_qsrq,'yyyy-mm-dd') = :2的用法较多，那么则应创建函数索引，反之创建普通索引即可
创建普通索引：
SQL> set timing on
SQL> create index ETAX.fbi_2 on ETAX.T_WB_SHBXMXSBB(sfssq_zzrq);
Index created.
Elapsed: 00:00:31.54
SQL> explain plan for select a.shbxhm, nvl(sum(a.ylbx_jfjs), 0) from etax.t_wb_shbxmxsbb a, etax.t_wb_sbbqk b where a.pz_xh = b.pz_xh and a.swglm = :1 and a.sfssq_qsrq >= to_date('2','yyyy-mm-dd') and a.sfssq_qsrq < to_date('3','yyyy-mm-dd') and
? 2? a.sfssq_zzrq >= to_date('4','yyyy-mm-dd') and a.sfssq_zzrq < to_date('5','yyyy-mm-dd')? and b.zt in ('2', '6', '7', '8') group by a.shbxhm
? 3? ;
Explained.
Elapsed: 00:00:00.03
SQL> set linesize 180
SQL> select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 3466029591
------------------------------------------------------------------
| Id? | Operation?????????????????????????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????????????????? |??????????????? |???? 1 |??? 86 |?? 308?? (2)| 00:00:04 |
|?? 1 |? HASH GROUP BY????????????????????? |??????????????? |???? 1 |??? 86 |?? 308?? (2)| 00:00:04 |
|*? 2 |?? FILTER??????????????????????????? |??????????????? |?????? |?????? |??????????? |????????? |
|?? 3 |??? NESTED LOOPS???????????????????? |??????????????? |???? 1 |??? 86 |?? 307?? (2)| 00:00:04 |
|*? 4 |???? TABLE ACCESS BY INDEX ROWID???? | T_WB_SHBXMXSBB |???? 1 |??? 67 |?? 305?? (2)| 00:00:04 |
|?? 5 |????? BITMAP CONVERSION TO ROWIDS??? |??????????????? |?????? |?????? |??????????? |????????? |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
|?? 6 |?????? BITMAP AND??????????????????? |??????????????? |?????? |?????? |??????????? |????????? |
|?? 7 |??????? BITMAP CONVERSION FROM ROWIDS|??????????????? |?????? |?????? |??????????? |????????? |
|?? 8 |???????? SORT ORDER BY?????????????? |??????????????? |?????? |?????? |??????????? |????????? |
|*? 9 |????????? INDEX RANGE SCAN?????????? | FBI??????????? | 43201 |?????? |?? 117?? (0)| 00:00:02 |
|? 10 |??????? BITMAP CONVERSION FROM ROWIDS|??????????????? |?????? |?????? |??????????? |????????? |
|? 11 |???????? SORT ORDER BY?????????????? |??????????????? |?????? |?????? |??????????? |????????? |
|* 12 |????????? INDEX RANGE SCAN?????????? | FBI_2????????? | 43201 |?????? |?? 117?? (0)| 00:00:02 |
|* 13 |???? TABLE ACCESS BY INDEX ROWID???? | T_WB_SBBQK???? |???? 1 |??? 19 |???? 2?? (0)| 00:00:01 |
|* 14 |????? INDEX UNIQUE SCAN????????????? | PK_T_WB_SBBQK? |???? 1 |?????? |???? 1?? (0)| 00:00:01 |
------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter(TO_DATE('4','yyyy-mm-dd')<TO_DATE('5','yyyy-mm-dd') AND
????????????? TO_DATE('2','yyyy-mm-dd')<TO_DATE('3','yyyy-mm-dd'))
?? 4 - filter("A"."SWGLM"=TO_NUMBER(:1))
?? 9 - access("A"."SFSSQ_QSRQ">=TO_DATE('2','yyyy-mm-dd') AND
????????????? "A"."SFSSQ_QSRQ"<TO_DATE('3','yyyy-mm-dd'))
? 12 - access("A"."SFSSQ_ZZRQ">=TO_DATE('4','yyyy-mm-dd') AND
????????????? "A"."SFSSQ_ZZRQ"<TO_DATE('5','yyyy-mm-dd'))
? 13 - filter("B"."ZT"='2' OR "B"."ZT"='6' OR "B"."ZT"='7' OR "B"."ZT"='8')
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
? 14 - access("A"."PZ_XH"="B"."PZ_XH")
34 rows selected.
Elapsed: 00:00:00.01
走索引了，时间有所缩短
SQL> drop index etax.fbi ;urge;
Index dropped.
Elapsed: 00:00:00.11
SQL> drop index ETAX.fbi_2;
Index dropped.
Elapsed: 00:00:00.04
并更改该sql写法如下
a.sfssq_qsrq>= to_date('2012-01-01','yyyy-mm-dd') and a.sfssq_qsrq < to_date('2012-01-02','yyyy-mm-dd')
创建函数索引：
SQL> create index etax.fbi on ETAX.T_WB_SHBXMXSBB ( to_char(sfssq_qsrq, 'yyyy-mm-dd'));
Index created.
Elapsed: 00:00:34.65
SQL> create index ETAX.fbi_2 on ETAX.T_WB_SHBXMXSBB ( to_char(sfssq_zzrq, 'yyyy-mm-dd'));
Index created.
Elapsed: 00:00:36.66
SQL> explain plan for select a.shbxhm, nvl(sum(a.ylbx_jfjs), 0) from ETAX.t_wb_shbxmxsbb a, ETAX.t_wb_sbbqk b where a.pz_xh = b.pz_xh and a.swglm = :1 and to_char(a.sfssq_qsrq, 'yyyy-mm-dd') = :2 and to_char(a.sfssq_zzrq, 'yyyy-mm-dd') = :3 and b.zt in ('2', '4', '5', '6') group by a.shbxhm;
Explained.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 4204756945
-----------------------------------------------------------------
| Id? | Operation????????????????????????? | Name?????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-----------------------------------------------------------------
|?? 0 | SELECT STATEMENT?????????????????? |??????????????? |???? 1 |??? 86 |? 1134?? (1)| 00:00:14 |
|?? 1 |? HASH GROUP BY???????????????????? |??????????????? |???? 1 |??? 86 |? 1134?? (1)| 00:00:14 |
|?? 2 |?? NESTED LOOPS???????????????????? |??????????????? |???? 1 |??? 86 |? 1133?? (1)| 00:00:14 |
|*? 3 |??? TABLE ACCESS BY INDEX ROWID???? | T_WB_SHBXMXSBB |???? 1 |??? 67 |? 1131?? (1)| 00:00:14 |
|?? 4 |???? BITMAP CONVERSION TO ROWIDS??? |??????????????? |?????? |?????? |??????????? |????????? |
|?? 5 |????? BITMAP AND??????????????????? |??????????????? |?????? |?????? |??????????? |????????? |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
|?? 6 |?????? BITMAP CONVERSION FROM ROWIDS|??????????????? |?????? |?????? |??????????? |????????? |
|*? 7 |??????? INDEX RANGE SCAN??????????? | FBI_2????????? | 38405 |?????? |?? 456?? (1)| 00:00:06 |
|?? 8 |?????? BITMAP CONVERSION FROM ROWIDS|??????????????? |?????? |?????? |??????????? |????????? |
|*? 9 |??????? INDEX RANGE SCAN??????????? | FBI??????????? | 38405 |?????? |?? 463?? (1)| 00:00:06 |
|* 10 |??? TABLE ACCESS BY INDEX ROWID???? | T_WB_SBBQK???? |???? 1 |??? 19 |???? 2?? (0)| 00:00:01 |
|* 11 |???? INDEX UNIQUE SCAN????????????? | PK_T_WB_SBBQK? |???? 1 |?????? |???? 1?? (0)| 00:00:01 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
?? 3 - filter("A"."SWGLM"=TO_NUMBER(:1))
?? 7 - access(TO_CHAR(INTERNAL_FUNCTION("SFSSQ_ZZRQ"),'yyyy-mm-dd')=:3)
?? 9 - access(TO_CHAR(INTERNAL_FUNCTION("SFSSQ_QSRQ"),'yyyy-mm-dd')=:2)
? 10 - filter("B"."ZT"='2' OR "B"."ZT"='4' OR "B"."ZT"='5' OR "B"."ZT"='6')
? 11 - access("A"."PZ_XH"="B"."PZ_XH")
27 rows selected.
Elapsed: 00:00:00.01
走索引了，时间有所缩短
SQL> drop index etax.fbi ;urge;
Index dropped.
Elapsed: 00:00:00.11
SQL> drop index ETAX.fbi_2;
Index dropped.
Elapsed: 00:00:00.04
可以看到，创建函数索引和创建普通索引在本质操作本质上对系统并没有太大影响。创建什么样的索引，关键取决于应用系统。
本文原创，转载请注明出处、作者
如有错误，欢迎指正