Oracle优化sql语句

识别高负载sql语句

高负载sql语句可能会不成比例地消耗系统资源。这些sql语句经常对数据库性能有重大影响，因此必须优化它们的性能和资源消耗。即使数据库层面已经被优化了，但低效的sql也会严重降低性能。

识别高负载sql语句是一个重要的sql优化活动，该活动必须作为一个常态化的日常行为。而ADDM会自动执行这个标记活动。此外，还能使用Oracle Enterprise Manager来识别需要进一步研究的高负载sql语句。识别出来高负载sql语句之后，就可以使用SQL Tuning Advisor和SQL Access Advisor来优化它们。

使用ADDM findings识别

默认情况下，ADDM会每小时主动运行一次。它分析关键的统计数据来识别性能问题，包括识别高负载sql语句，而这些统计数据是通过Automatic Workload Repository（AWR）收集的过去一小时（的数据库活动数据）。当系统发现性能问题时，它会把它们作为ADDM的findings显示在ADDM 页面。

ADDM会为每个ADDM findings提供recommenddations。当高负载sql语句被识别出来后，ADDM同样会给出recommenddations（推荐解决方案），例如在sql语句中运行SQL Tuning Advisor。

使用Top SQL识别

ADDM会自动识别那些引起系统层面性能下降的高负载sql语句，所以一般情况下，不需要手动识别高负载sql语句。然而在一些情况下，需要更频繁的监视sql语句。这时，在EM中的Top Activity page（顶级活动页面）的Top SQL部分可以帮助我们识别出来高负载SQL语句，更新间隔是5分钟。

从主页面（刚登录系统时的页面，即主目录），点击【性能】

在【性能】下方的，【其他监视链接】，点击【顶级活动】

顶级活动页面出现，如上上上图所示

页面上部的折线图显示的是最近1小时内的顶级活动（该区域命名为A，方便引用）。页面下方左侧显示的是顶级sql活动（该区域命名为B），以5分钟为时间间隔。

要B区域显示不同的5分钟间隔的sql活动，拖放A区域的阴影框至相应的时间即可。

要想查看超过当前1小时范围的SQL语句，A区域右上方选择【历史】，在历史界面中，可以看到AWR保留期内的所有顶级sql语句。

通过Wait Class查看sql语句

顶级活动页面的Top SQL部分出现的SQL语句是被按照wait class进行分类的，A区域中的图例就是相应的wait class。要看某一个wait class 的SQL语句，点击相应的图例即可。

查看sql语句的细节

顶级sql里显示的是顶级活动中选中的5分钟间隔内的sql语句，并以资源消耗的降序显示。

包括以下四个方面内容：查看sql 统计数据、查看会话活动、查看Plan Control、查看调优历史。

优化sql语句

一个sql语句的运行结果是（表达）数据，这些数据是你希望从数据库获取的。例如，一个SQL语句是获取部门的员工姓名。当Oracle数据库执行sql语句时，query optimizer（即查询优化器，也被称为optimizer）首先测算出获取结果的最有效率的方式。

optimizer（优化器）会测算读取表中的全部数据是否是更有效率的方法，被称为a full table scan（全表扫描），或者使用index。它会比较所有方法的成本，并选择一个成本最低的方法。这种用于物理执行一个SQL语句的访问方法被称为一个（execution plan）执行计划

，优化器负责生成执行计划。执行计划的测算判定是任何sql语句处理过程中的一个重要步骤，会对执行时间有重大影响。

查询优化器还可以帮助你优化sql语句。通过使用SQL Tuning Advisor （SQL优化顾问）和SQL Access Advisor（SQL访问顾问），可以以顾问模式运行查询优化器，测试一个或者若干sql语句，然后判断如何去改善效率。SQL 优化顾问和SQL访问顾问可以做出各种推荐方案，例如创建SQL profiles，重构SQL 语句，创建索引或者物化视图，刷新优化器统计数据。此外，Oracle Enterprise Manager能让你更容易接收和实施这些推荐方案。

sql访问顾问主要负责做模式调整的推荐方案，例如增加或者删除索引、物化视图。sql优化顾问做其他类型的推荐方案，例如创建sql profiles和重构sql语句。如果创建一个索引能够有重要的性能改善，那么sql优化顾问也会推荐它

使用sql优化顾问优化sql

当优化多个语句时，SQL优化顾问不能识别出SQL语句之间的相互依赖关系。但，SQL优化顾问依然提供了简便方法，来获取多条SQL语句的优化建议。

Oracle数据库可以自动生成SQL优化报告。

使用SQL优化顾问手动优化SQL

在主目录的底部，相关链接的下方区域，有个【指导中心】（Advisor Central），点击

弹出指导中心界面，点击【SQL指导】

弹出【SQL指导】的界面，点击【SQL优化指导】

点击【SQL优化指导】，弹出如下界面。

上图中

• 名称

在【名称】栏中，为SQL 优化task输入一个名称。如果未指定，SQL优化指导会使用系统生成的名称。

• SQL源

要为一个或多个高负载SQL语句运行SQL优化task，在SQL优化指导Data Source Links，点击Top Activity。The Top Activity page出现。

要为来自Automatic Workload Repository（AWR）的历史SQL语句运行SQL优化task，在SQL优化指导Data Source Links下，点击Historical SQL（AWR）。

要为一个SQL优化set运行SQL优化task，点击SQL Tuning Sets

The SQL Tuning Sets page出现

以上三种任选一个，作为sql源，此时页面中都会出现Schedule SQL Tuning Advisor。

点击【Schedule SQL Tuning Advisor】，界面会变化，出现如下图所示红框标注的内容。

如上图所示，分别再【范围】、【调度】中选择合适的信息。然后点击【提交】，开始出现优化指导运行界面，一段时间后，会出现如下界面，提供相应的优化建议案。

查看Automatic SQL Tuning Results

主页面，即下图中的主目录，点击最下方的【指导中心】

管理SQL Tuning Sets

一个SQL tuning set是一个数据库对象，该对象包含一个或多个SQL语句以及它们的执行统计信息和上下文。可以只用这个set作为advisors（即指导）的输入，例如SQL Tuning Advisor、SQL Access Advisor、SQL Performance Analyzer。可以从不同的SQL sources加载SQL statements（语句）到一个SQL tuning set，例如AWR、the cursor cache（游标缓存）、识别的高负载SQL statements。

创建一个SQL Tuning Set

删除一个SQL Tuning Set

操作参考上图

传输一个SQL Tuning Set

操作参考上上图

导出、导入按钮，可以将一个数据库服务器的SQL Tuning Set导出后，再导入到另一个数据库服务器。

管理SQL Profiles

一个SQL Profile是一系列的辅助信息，它们是在一个sql语句自动优化时被创建的。A SQL profile对于一个SQL语句的意义，类似于统计信息对于一个表的意义。

当以一个limited scope的方式运行一个SQL Tuning Advisor时，optimizer（优化器）会做一些评估，评估包括cardinality、selectivity、cost，如果评估结果是不好的execution plans。那么要解决评估结果差的问题，可以考虑以comprehensive scope的方式运行一个SQL Tuning Advisor任务，该任务使用sampling和partial execution技术来收集额外的信息放入一个SQL profile。

管理SQL Execution Plans

SQL plan management是一个预防机制，它会记录和评估一段时间内SQL statements的execution plans。数据库会创建由一系列高效plans组成的SQL plan baselines。如果SQL语句重复执行时，optimizer（优化器）生成来一个不同于基线的新plan，那么数据库会比较这个plan和baseline，并选择最优的那个。

优化数据访问路径

物化视图、索引