昨天帮客户优化一套9.2.0.4的数据库，业务高峰期，客户的数据库服务器CPU负载几乎100%，用户已经无法登录业务系统，SQL执行时间超长。客户的服务器48核CPU，内存32G。

经过现场分析， 发现3处对性能影响较大的地方。首先是内存设置不当，该部分对服务器压力造成较大影响。

SELECT
	COUNT (*) AS col_0_0_
FROM
	PRESSO.BIZ_PRESSCARD_LOGOUT presscardl0_,
	PRESSO.BIZ_REPORTER_INFO reporterin1_
WHERE
	presscardl0_.CARD_UUID = reporterin1_.CARD_UUID
AND 1 = 1
AND presscardl0_.APPLY_STATUS = 2
AND presscardl0_.NEXT_ORGID = '1'
AND reporterin1_.STATE = '7';

在问题时段，该SQL运行需要1分多的时间，而这两张表数据量均不大，以下是该SQL的执行计划和统计信息。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1 Bytes=69)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'BIZ_PRESSCARD_LOGOUT'
          (Cost=10 Card=1 Bytes=50)

   3    2       NESTED LOOPS (Cost=4 Card=1 Bytes=69)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'BIZ_REPORTER_INFO'
           (Cost=10 Card=1 Bytes=19)

   5    4           INDEX (RANGE SCAN) OF 'STATE_REPORTER_INFO_INDEX'
          (NON-UNIQUE) (Cost=4 Card=1)

   6    3         INDEX (RANGE SCAN) OF 'INDEX_LOGOUT_CARD_UUID' (NON-
          UNIQUE) (Cost=4 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     796225  consistent gets
       5245  physical reads
          0  redo size
        490  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)
          1  rows processed

再次运行这个SQL，发现执行时间和统计信息部分基本没变。

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     796225  consistent gets
       5087  physical reads
          0  redo size
        490  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)
          1  rows processed

为什么刚刚运行完的SQL，再次运行还需要这么多的物理读？这是个问题，经过分析发现，db_cache_size设置的不合理。

SQL > SHOW PARAMETER DB_CACHE_SIZE
NAME                                   TYPE        VALUE
------------------------------------   ----------- ------------------------------
db_cache_size                      	big integer  33792

可见，db_cache_size只有30多M，存不下需要处理的数据，下次再执行SQL，就需要物理读，这样就造成了大量的CPU等待，经查看，该数据库SGA_MAX_SIZE被其他DBA设置为16G，shared_pool_size被设置为1G，讲shared_pool_size调整为2G，db_cache_size调整为12G，性能有所提升，多次运行后，该SQL运行需要将近3秒，逻辑读下降一半，物理读为0.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=69)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'BIZ_PRESSCARD_LOGOUT'
          (Cost=2 Card=1 Bytes=50)

   3    2       NESTED LOOPS (Cost=4 Card=1 Bytes=69)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'BIZ_REPORTER_INFO'
           (Cost=2 Card=1 Bytes=19)

   5    4           INDEX (RANGE SCAN) OF 'STATE_REPORTER_INFO_INDEX'
          (NON-UNIQUE) (Cost=1 Card=1)

   6    3         INDEX (RANGE SCAN) OF 'INDEX_LOGOUT_CARD_UUID' (NON-
          UNIQUE) (Cost=1 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     497641  consistent gets
          0  physical reads
          0  redo size
        490  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)
          1  rows processed

查看表和索引的统计信息，发现统计信息都是一年前收集的，重新对表和所以进行分析后，SQL执行10毫秒即可查询出结果，逻辑读大大降低。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=67)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'BIZ_REPORTER_INFO' (Co
          st=3 Card=1 Bytes=17)

   3    2       NESTED LOOPS (Cost=5 Card=1 Bytes=67)
   4    3         TABLE ACCESS (FULL) OF 'BIZ_PRESSCARD_LOGOUT' (Cost=
          2 Card=1 Bytes=50)

   5    3         INDEX (RANGE SCAN) OF 'CARD_UUID_INDEX' (NON-UNIQUE)
           (Cost=2 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2862  consistent gets
          0  physical reads
         52  redo size
        490  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)
          1  rows processed

对业务用户所有表和索引进行分析，此时业务系统基本恢复正常，高峰期CPU使用基本在1%-3%直接，偶尔会到9%，但很快就会降下去。

客户反馈在批量审批100条信息的时候，之前需要5分钟才能审批完，现在虽然有所提升(3分钟)，但是还比较慢。经和开发人员交流，发现审批更新的表都存在触发器，将更新的信息同步到另一个用户的表里面，经查询，另一个用户基本没有索引。按照触发器的SQL，创建索引后，只需20秒即可审批完，如再需优化，就需要修改业务逻辑了，客户表示可以接受了。

下面摘录业务人员统计的优化对比信息。

本文固定链接: https://www.dbdream.com.cn/2014/08/%e4%b8%80%e6%ac%a19i%e6%95%b0%e6%8d%ae%e5%ba%93%e4%bc%98%e5%8c%96/ | 信春哥，系统稳，闭眼上线不回滚！