系统存在严重的性能问题，IO十分高，DB CACHE的命中率不足50&。经分析HASH VALUE为1285325803的SQL存在严重的性能问题，通过SPREPSQL分析：
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 1,055,004 36,379.4 32.81
Disk Reads: 1,000,975 34,516.4 46.02
Rows processed: 159 5.5
CPU Time(s/ms): 113 3,889.3
Elapsed Time(s/ms): 318 10,976.9
Sorts: 29 1.0
Parse Calls: 29 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 49
Executions: 29
SQL Text
~~~~~~~~
select b.req_name, b.cli_pid
, b.req_id, b.svr_name, b.timeout, b.svr_module,
b.svr_inst, b.status, to_char(b.req_tm, 'YYYY MM DD HH24 MI SS'),
nvl(a.default_timeout,0)
from service_list a, service_req_list b
where
a.app_id(+) = b.app_id and
a.svr_name(+) = b.svr_name and
b.status in ('Q','A') and
b.app_id = :v1 order by b.req_tm,b.req_id
Known Optimizer Plan(s) for this Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id
First First Plan
Snap Id Snap Time Hash Value Cost
-------- --------------- ------------ ----------
167 24 Sep 08 16:57 74934330 40682
Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 74934330 ----- | | | 40682 |
|SORT ORDER BY | | 1M| 190M| 40682 |
| HASH JOIN OUTER | | 1M| 190M| 8355 |
| TABLE ACCESS FULL |SERVICE_REQ_LIST | 1M| 147M| 5641 |
| TABLE ACCESS BY INDEX ROWID |SERVICE_LIST | 15 | 345 | 2 |
| INDEX RANGE SCAN |PINX_SERVICE_LIST$_A | 1 | | 1 |
--------------------------------------------------------------------------------
对SERVICE_REQ_LIST的全表扫描是引起性能问题的关键，该表有超过100万条记录。在该表上存在下列过滤条件：
b.status in ('Q','A') and
b.app_id = :v1
经检查，b.app_id的值域只有一个值，无选择性。而b.status只有很少的值：
SQL> select status,count(*) from acsdba.service_req_list group by status;
S COUNT(*)
- ----------
A 2
C 2304679
E 367
Q 1
幸运的是该SQL查找的STATUS IN('A','Q')的记录只有几条。因此STATUS上的创建索引可以有效减少全表扫描。经检查在STATUS字段上已经创建了索引，但是该索引并没有起作用，经检查，发现是由于没有分析柱状图引起。通过下列脚本对该表进行重新分析：
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'acsdba', tabname=>'service_req_list', estimate_percent=>20, -
method_o> pt=>'for all indexed columns size skewonly',cascade=>true, degree=>2);
分析后检查执行计划：
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 10932168 ----- | | | 25 |
|SORT ORDER BY | | 1 | 102 | 25 |
| NESTED LOOPS OUTER | | 1 | 102 | 5 |
| INLIST ITERATOR | | | | |
| TABLE ACCESS BY INDEX ROWID |SERVICE_REQ_LIST | 1 | 79 | 4 |
| INDEX RANGE SCAN |RINX_SERVICE_REQ$_LI | 1 | | 3 |
| TABLE ACCESS BY INDEX ROWID |SERVICE_LIST | 1 | 23 | 1 |
| INDEX UNIQUE SCAN |UINX_SERVICE_LIST$_A | 1 | | |
--------------------------------------------------------------------------------
发现全表扫描已经消除，通过9月25日上午的验证，系统的整体性能有了较大提升
阅读(1687) | 评论(2) | 转发(0) |
