【转载案例分享】 如何在无法修改应用的时候进行数据库优化
这是云和恩墨的技术专家罗海雄，在微信直播的一个讲座，分享给大家。
一个汽车财务经销商，财务系统出现严重性能问题。这是系统CPU压力示意图：
红色是CPU使用量，绿色是CPU空闲率，黑色是I/O等待。
可以看到在图的中间部分，也就是月底的时候，CPU使用量已经达到80-90%，而CPU idle已经接近0了。中间似乎还有一段CPU空闲100%，其实是由于数据库压力太大，主机自动重启了。这种情况已经严重影响他们公司财务的月结工作。观察AWR，发现大量SQL执行时间很长，如下图：
从表里可以看到，第一个SQL平均一次执行需要2700秒，后面还有几个SQL单次执行在2000秒以上，显然有很大问题。
SELECT DISTINCT si.FId FID
FROM T_IM_SaleIssueEntry SIE
LEFT OUTER JOIN T_IM_SaleIssueBill SI ON si.FId = sie.FParentId
LEFT OUTER JOIN T_BOT_RELATIONENTRY RE ON sie.FID = re.FDestEntryId
WHERE ((si.FBaseStatus NOT IN (:1, :2))
AND ((si.FTransactionTypeID IN (:3, :4))
AND (re.FSrcEntryId IN (:5, :6))))
T_IM_SaleIssueEntry是外连接的驱动表，但是上面没有直接的条件。T_IM_SaleIssueBill 和 T_BOT_RELATIONENTRY都是被驱动表，上面有两个条件:si.FTransactionTypeID IN (:3, :4)和 re.FSrcEntryId IN (:5, :6)
这种情况下，由于被驱动表已经有了确定值，逻辑上来说，外连接和内连接是等价的，但是Oracle 10g 的优化器没有为这种情况做优化，导致在优化前，走了大表的全表扫描。
理想的执行计划应该是 T_BOT_RELATIONENTRY RE => T_IM_SaleIssueEntry=> T_IM_SaleIssueBill ，这一类SQL不少。我们最早的建议就是修改部分写法存在问题的SQL, 从源头入手，尝试解决。
比如说，把LEFT OUTER JOIN 改成普通JOIN, 逻辑上是一样的，就能走刚才的好的执行计划。
SELECT DISTINCT si.FId FID
FROM T_IM_SaleIssueEntry SIE
JOIN T_IM_SaleIssueBill SI ON si.FId = sie.FParentId
JOIN T_BOT_RELATIONENTRY RE ON sie.FID = re.FDestEntryId
WHERE ((si.FBaseStatus NOT IN (:1, :2))
AND ((si.FTransactionTypeID IN (:3, :4))
AND (re.FSrcEntryId IN (:5, :6))))
但是，应用开发厂商表示，由于这个版本比较老，他们已经没有专门的人员对代码进行维护，无法修改SQL。
系统刚上线，一切很美好，一两年后，由于数据量的积累，用户数增多，功能点使用增多等原因，会使负荷逐渐增加，从而出现性能问题。根据我们的经验，在这些性能问题里面，SQL的问题可能占了80%.
但是，由于种种原因，可能就是没法修改SQL。
比如说：
-- 使用封装好的商业套件
-- 外包开发，开发商已经离场
-- 自行开发，但开放部门不愿意配合进行代码修改
总的来说，可以从硬件和软件两个方面去解决：硬件方面，可以通过增加或者升级CPU，增加内存，做一定的改善；
通过升级成RAC或者增加RAC节点, 也可以增加系统的CPU的处理能力；
存储方面，升级更好地存储，针对一些I/O要求比较高的系统，也一种优化的手段。
在我们的经验当中，如果能够升级到固态硬盘或者PCIE Flash卡，往往的效果要好于升级CPU和内存。
这是一个客户，通过把存储介质升级到PCIE Flash卡，极大的改善了I/O性能问题，是整个系统的性能得到了极大的提升。
【一波广告，不好意思去掉啊，毕竟听了人家的课】
云和恩墨公司的zData产品，通过高速的PCIE Flash卡，结合超大带宽，超小延迟的高速IB网络, 也可以非常有效地提高I/O的性能，总体性能是传统存储的10倍以上。这样，在不修改任何SQL的情况下，就轻松解决了I/O的瓶颈，提高的系统的性能。
当然，对于不差钱的单位/企业，使用Oracle的Exadata也是一种方法很简便的、不修改应用就能明显提高系统性能的方法。
动硬件往往涉及到预算。不增加开销的情况下，也可以通过数据库的层面做一些优化。
经过研究发现，LEFT OUTER JOIN不能等价转换成普通JOIN是Oracle 10g的行为模式。 在Oracle 11g中，优化器做了升级，能够识别并内部进行这种转换。而恰巧，用户用的是Oracle 11g的数据库，只是由于应用开发方的要求，把优化器模式设为了10.2.0.1 。最终，通过和应用开发方，使用方的多次沟通和测试，最终把优化器模式设为了11.2.0, 从而解决了最大的问题。
当然，LEFT OUTER JOIN只是其中一个问题，后来，在这个客户的数据库上，我们还有针对性的建立了100多个索引。最终，在没有修改任何SQL的情况下，彻底解决了用户的系统性能问题。
通常来说，数据库层面的优化包括：
参数调整： 内存参数，优化器参数等；
表结构调整：索引, 并行度,分区；
SQL执行计划调整：SQL Profile, SPM，SQL Patch；
其它： Cache 表、统计信息、物化视图+查询重写、数据归档等等。
索引的问题看起来很简单，但往往是最有效的方法之一。大家讨论的比较多，我就不深入了。
分区以及数据归档也是一个常用的手段。实际上，数据都是有生命周期的。很多用户的数据库里面，存了很多已经不需要的数据，通过清理、归档这些数据，往往也能获得比较高的优化效果。
还有一些情况，SQL写的并没有问题，但由于种种原因，数据库经常走错执行计划；这时候，通过改写SQL, 增加Hint是一种常见解决方式。在无法修改SQL的情况下，也可以通过一些手段对SQL执行计划进行固定。
这些手段主要包括有：
SQL Profile(Oracle 10g以后)
SQL Plan Baseline Management(Oracle 11g以后)
SQL Patch
Outline
SQL profile在Oracle 10g引入
通过为特定的SQL文本指定优化器的一些信息，从而引导优化器生成更为合理的SQL执行计划。达到不修改SQL文本就可以改变并指定执行计划的目的
SQL-Profile 主要通过dbms_sqltune包进行控制；
SQL Plan Management在Oracle 11g引入，通过为特定的SQL指定已知SQL执行计划，强制优化器选择已经指定的SQL执行计划，从而达到不修改SQL文本即可修改执行计划的目的，可以指定多个可用执行计划供优化器选择，可以和SQL Tuning Advisor一起用，可以自动收集运行库中SQL 作为已知执行计划，也可以手工设置，SQL Plan Management主要通过DBMS_SPM包进行控制；
sys.dbms_sqldiag_internal.i_create_patch
SQL Patch是一种强行给SQL加Hint的方法，主要通过sys.dbms_sqldiag_internal.i_create_patch 进行。
完毕。
