首页 > 数据库 > Oracle >

Oracle DB通过SQL优化管理性能

2013-10-26

Oracle DB通过SQL优化管理性能• 将SQL 优化指导用于:– 确定使用资源最多的 SQL 语句– 优化使用资源最多的 SQL 语句• 使用SQL 访问指导优化工作量SQL 优化SQL 优化进程&bull

Oracle DB通过SQL优化管理性能
• 将SQL 优化指导用于:
– 确定使用资源最多的 SQL 语句
– 优化使用资源最多的 SQL 语句
• 使用SQL 访问指导优化工作量
SQL 优化
SQL 优化进程
• 确定没有很好地优化的SQL 语句。
• 优化各条语句。
• 优化整个应用程序。

一般情况下,效果最明显的优化工作是SQL 优化。没有很好地优化的SQL 会不必要地使用过多资源。这种低效率会降低可伸缩性、使用更多的OS 和数据库资源并增加响应时间。要对没有很好地优化的SQL 语句进行优化,必须先确定这些语句,然后再进行优化。可以分别对各条SQL 语句进行优化,但优化一条语句的解决方法经常会影响多条其它语句的性能。
顾名思义,使用资源最多的SQL 语句是需要优化的语句。这些语句的用时最长,使用的CPU 资源最多,或者物理或逻辑读取次数最多。
优化各条语句的方法是:检查优化程序统计信息,检查最高效的访问路径的解释计划,测试替代SQL 构造,以及测试可能的新索引、实体化视图和分区。
使用优化后的SQL 语句测试整个应用程序的性能。整体性能是否有所提高?
方法很可靠,但有些拖沓。优化单条语句并不难。测试各条语句优化对应用程序的整体影响可能非常困难。
在Oracle Database 11 g 中,可以使用一组SQL 指导来确定和优化语句(单独优化或集体优化)。
SQL 指导

Oracle Database 11 g 提供了一组SQL 指导:SQL 访问指导、SQL 优化指导、SQL 性能分析器和SQL 修复指导。AWR 可确定最近的高负荷SQL 语句并记录有关的统计信息。
SQL 优化指导可分析一个或多个SQL 语句(一次分析一个)。它会检查统计信息、SQL 概要文件、索引、实体化视图以及调整后的SQL 。SQL 优化指导可随时通过手动方式运行;但主要是在每个维护窗口期间针对最近的高负荷SQL 语句运行。单击“Automatic SQL Tuning Results(自动SQL 优化结果)”可查看和实施建议。可以对此自动作业进行配置,自动为高负荷语句实施建议的SQL 概要文件。
SQL 访问指导会考察应用到一组SQL 语句的更改,确定性能是否有净提高。这组语句可能是一组假想的SQL 语句,以往的语句或手动创建的语句。
SQL 性能分析器可用于预测和防止任何影响SQL 执行计划结构的数据库环境更改所带来的潜在性能问题。
SQL 修复指导是在某个SQL 语句因严重错误而失败时从支持工作台运行的。严重错误还会产生意外事件。修复指导会尝试查找并推荐一个SQL 补丁程序。如果未找到任何补丁程序,可以接着在支持工作台中打包意外事件,并将意外事件程序包作为服务请求(SR) 提交给Oracle 技术支持部门。
自动SQL 优化结果

默认情况下,自动SQL 优化任务在每天晚上运行。自动SQL 优化结果链接会显示结果概要页。如果单击“View Report(查看报表)”,则可查看接受检查的每条SQL 语句。
单击“Configure(配置)”按钮会显示一个页面;在此页面中,可以更改自动优化任务的默认设置,并启用SQL 概要文件自动实施功能。
实施自动优化建议

如果单击“Automatic Tuning Results Summary (自动优化结果概要)”页上的“View Report (查看报表)”按钮,将显示“Automatic SQL Tuning Result Details (自动SQL 优化结果详细资料)”页。可以实施所有建议,或细化以查看或实施单个建议。

在“Recommendations(建议)”页上,可以单击右侧的眼镜图标查看实施SQL 概要文件会对解释计划产生的影响。
SQL 优化指导:概览
全面SQL优化
检测过时或缺少的统计信息
优化SQL 计划(SQL 概要文件)
添加缺少的索引
调整SQL 结构

SQL 优化指导是优化过程中使用的主要驱动程序。它可以执行多种类型的分析:
• 统计信息分析:检查每个查询对象是否缺少统计信息或统计信息是否过时,然后提出建议以收集相关的统计信息。
• SQL 概要分析:优化程序会验证它自身的估计值并收集辅助信息以消除估计错误。它使用辅助信息构建SQL 概要文件,并提出创建SQL 概要文件的建议。创建SQL 概要
文件后,查询优化程序便可以通过此文件生成合理优化的计划。
• 访问路径分析:考察新索引是否会显著改善对查询中的每个表的访问性能。如果合适,则会建议创建此类对象。
• SQL 结构分析:确定使用了错误计划的 SQL 语句,并提出对这些语句进行结构调整的相关建议。建议的更改可能涉及语法方面的更改,也可能涉及语义方面的更改。
SQL 优化指导会分别考察指导任务中包括的每个SQL 语句。创建一个新索引可能会对查询有帮助,但也可能增加DML 的响应时间。因此,应使用SQL 访问指导检查建议的索引或其它对象对工作量(一组SQL 语句)的影响,以确定性能是否有净提高。
使用SQL 优化指导
• 使用SQL 优化指导可分析SQL 语句,并获得性能建议。
• SQL 优化指导分析的来源:
– 顶级活动:分析当前处于活动状态的顶级 SQL 语句
– SQL 优化集:分析用户提供的一组 SQL 语句
– 以往的SQL (AWR):分析 AWR 快照收集的 SQL 语句中的语句

SQL 优化指导会在每晚以自动SQL 优化任务的形式自动运行。有时,可能需要对某个SQL 语句立即进行优化操作。你可以随时使用SQL 优化指导分析SQL 语句,并获得性能建议。通常,运行此指导类似于使用ADDM 查找性能问题的操作。
此外,可以运行SQL 优化指导来分析哪些SQL 语句占用的CPU 时间、I/O 和内存最多。
即使在一项任务中提交了多条语句进行分析,对每条语句的分析仍是分别进行的。要获得考虑了一组SQL 语句的整体性能的优化建议,使用SQL 访问指导。
SQL 优化指导选项

在“Schedule SQL Tuning Advisor(调度SQL 优化指导)”页上,可以选择要包括的SQL 语句,以及更改优化任务的自动默认设置。可以设置SQL 语句的来源;如果有
ADVISOR系统权限,可以提交任务。随后,Oracle Enterprise Manager 会为SQL 优化指导创建一项优化任务。
通过SQL 语句选项,可以从以下来源中选择一条或多条SQL 语句:最近的顶级活动、AWR 中存储的以往的SQL 语句或者已创建的SQL 优化集。
选择优化任务的适当范围很重要。如果选择“Limited(有限制)”选项,SQL 优化指导会根据统计信息检查结果、访问路径分析结果和SQL 结构分析结果来生成建议。“Limited(有限制)”选项不会产生SQL 概要文件建议。如果选择“Comprehensive(综合)”选项,SQL 优化指导不仅会生成“Limited(有限制)”选项生成的所有建议,在SQL 概要分析模式下还会调用优化程序来构建SQL 概要文件。使用“Comprehensive(综合)”选项时,还可以指定优化任务的时间限制,该时间限制的默认值是30 分钟。选择“Run SQL Tuning Advisor(运行 SQL 优化指导)”之后,请使用“SQL Tuning Options(SQL 优化选项)”页配置优化任务。
SQL 优化指导建议
任务的SQL 优化结果会在任务完成后即刻显示,也可以在以后通过“Advisor Central(指导中心)”页访问这些优化结果。此时还会显示建议的概要。可以复查和实施单个建议。选择语句并单击“View(查看)”。
使用SQL 优化指导:示例
可以通过执行以下步骤来调用SQL 优化指导:
1. 在“Database(数据库)”主页中,单击“Related Links(相关链接)”区域中的“Advisor Central(指导中心)”。
2. 单击“SQL Advisors (SQL 指导)”。此时将出现“SQL Tuning Advisor Links(SQL 优化指导链接)”页。
可以对以下任一来源运行该指导:
- Active SQL (活动SQL):分析当前活动的顶级 SQL 语句
- SQL Tuning Sets(SQL 优化集):分析用户提供的一组 SQL 语句
- Historical SQL (AWR) (以往的SQL (AWR)):分析AWR 快照捕获的SQL 语句
3. 选择“Active SQL (活动SQL )”。选择要分析的五分钟间隔,方法是将灰色框拖到目标时间段上。请选择要在选定期间内分析的一条或多条语句。

4. 单击“Run SQL Tuning Advisor(运行 SQL 优化指导)”。此时会出现“SQL Tuning Options(SQL 优化选项)”页,显示此时间间隔内的SQL 语句。提供任务的名称和
描述,选择“Comprehensive(综合)”作为范围,选择“Immediately(立即)”作为启动时间。单击“OK(确定)”。

5. 重新导航到“Advisor Central(指导中心)”页。指导任务的状态列于“Results (结果)”区域中此标题的下面。请一直等待,直至任务状态变为已完成。通过单击浏览
器中的“Refresh(刷新)”检查状态。选择任务并单击“View Result (查看结果)”。此时会显示“SQL Tuning Result (SQL 优化结果)”页。
6. 选择SQL 语句并单击“View Recommendations (查看建议)”。
重复的SQL


重复的SQL 语句是指只是所用的文字值或格式有所不同的语句。每个不同的语句在库高速缓存中都会有一个单独的游标。如果将文字替换为绑定变量,并统一格式,则重复的语句可以使用相同的游标。
通过在“Additional Monitoring Links(其他监视链接)”区域中单击“Performance(性能)”选项卡页上的“Duplicate SQL(重复的SQL )”,可以确定重复的SQL 语句。已确定为重复的SQL (不考虑格式或文字差异)会列在一起。这将有助于确定应用程序中的哪个SQL 可以合并,从而降低对库高速缓存的需求,提高语句的执行速度。
SQL 访问指导:概览

评估 SQL 的全部工作量, 并建议索引, 分区和实体化视图, 它们将改善 SQL 工作量的总体性能。

SQL 访问指导能够为给定工作量建议一组适当的实体化视图、实体化视图日志、分区和索引。了解和使用这些结构在优化SQL 时至关重要,因为这些结构在数据检索时能够极大地改善性能。
SQL 访问指导建议使用位图索引、基于函数的索引和B 树索引。位图索引可减少许多类型的即席查询的响应时间,并且相对于其它索引技术而言降低了存储要求。B 树索引在数据仓库中最为常用,用于为唯一关键字或接近唯一的关键字编制索引。
SQL 访问指导的另一个组件还提供了优化实体化视图的建议,使这些视图可以快速刷新并利用常规查询重写。
典型的SQL 访问指导会话

启动SQL 访问指导会话时,可以选择“Use Default Options(使用默认选项)”,以一组建议的预定义指导选项开始。此外,还可以选择“Inherit Options from a Task or Template(从任务或模板继承选项)”来启动任务,并使该任务继承一组由模板或任务定义的选项值。这些模板包括多个针对通用环境、OLTP 和数据仓库数据库设计的通用模板。你可以保存先前任务中的定制模板并在需要时重新使用。
单击“Continue (继续)”启动SQL 访问指导向导。
工作量来源

使用SQL 访问指导向导的“Workload Source(工作量源)”页提供已定义的工作量,以便访问指导可以生成建议。支持的工作量源包括:
• Current and Recent SQL Activity(当前和最近的SQL 活动):使用高速缓存中的当前SQL 作为工作量
• Use an existing SQL Tuning Set(使用现有的SQL 优化集):可以将先前创建的 SQL 优化集指定为工作量源
• Create a Hypothetical Workload from the Following Schemas and Tables (从下列方案和表创建假想工作量):提供方案以便访问指导可以搜索维表并生成工作量
通过应用“Filter Options (过滤器选项)”部分中的可用过滤器,可以进一步缩小工作量的范围。使用这些选项,可以缩小出现在工作量中的SQL 语句的范围。指导将过滤器应用于工作量后,可以对重点部分进行优化。



可用的过滤器选项包括:
• 消耗资源最多的SQL 语句
• 用户、模块标识符或操作
• 表
建议选项

使用“Recommendations Options (建议选项)”页可以选择是否限制指导基于单个访问方法提出建议。从“Access Structures to Recommend(要推荐的访问结构)”部分选择“Indexes(索引)”、“Materialized Views (实体化视图)”、“Partitioning (分区)”或者上述各项的任意组合。可以选择“Evaluation Only(仅评估)”,从而只评估现有的访问结构。在此模式下,指导不生成新的建议,而是就现有结构的使用提出意见。此项在跟踪当前索引、实体化视图的有效性和MV 日志使用率随时间变化方面非常有用。
可以使用“Advisor Mode (指导模式)”部分,以两种模式之一运行指导。这些模式会影响建议的质量和处理所需的时间。在“Comprehensive(综合)”模式中,指导将搜索候选的大型池,以便得到最高质量的建议。在“Limited(有限制)”模式中,指导执行速度很快,但是会限制候选建议。


可以选择“Advanced Options(高级选项)”来显示或隐藏选项,这些选项可用于设置空间限制、优化选项和默认存储位置。使用“Workload Categorization(工作量类别)”部分可以设置“Workload Volatility(工作量不稳定性)”和“Workload Scope(工作量范围)”选项。生成建议时,可以选择关注只读操作,或者考察被引用对象的不稳定性。你还可以选择“Partial Workload (部分工作量)”,其中不包括删除未使用的访问结构的建议;或者选择“Complete Workload(全部工作量)”,其中包括删除未使用的访问结构的建议。
使用“Space Restrictions(空间限制)”部分可指定硬性空间限制,强制指导仅使用不超过指定限制的总空间要求生成建议。
使用“Tuning Options(优化选项)”部分可指定相关选项对指导生成的建议进行定制。
使用“Prioritize Tuning of SQL Statements by(确定优化SQL 语句优先级的依据)”下拉列表,可以按“Optimizer Cost(优化程序开销)”、“Buffer Gets (缓冲区获取数)”、“CPU Time(CPU 时间)”、“Disk Reads(磁盘读取数)”、“Elapsed Time(用时)”和“Execution Count (执行计数)”划分优先级。


使用“Default Storage Locations(默认存储位置)”部分可以覆盖为方案和表空间位置定义的默认值。默认情况下,索引放置在所引用表的方案和表空间中。实体化视图放置在相应用户的方案和表空间中,该用户执行的某一个查询为实体化视图建议提供了信息。
定义了这些参数以后,可以调度和复查优化任务。

复查建议

使用“Advisor Central(指导中心)”页可以列出所有已完成的SQL 访问指导任务。选择要查看建议的任务,然后单击“View Result (查看结果)”按钮。使用“Results for Task(任务结果)”的“Summary (概要)”页,可以访问指导查找结果的概览。该页中给出了图表和统计信息,提供了建议可能对整体工作量性能和查询执行时间产生的改进。使用该页可以显示语句计数和建议操作计数。
要查看指导任务结果的其它方面,可单击该页上其它三个选项卡之一:“Recommendations(建议)”、“SQL Statements(SQL 语句)”或“Details (详细资料)”。
“Recommendations(建议)”页中提供了一个图表和一个表,其中按建议对整个工作量总开销的改进百分比显示顶级建议。顶级建议对总体性能的改进幅度最大。
单击“Show SQL(显示SQL )”按钮,可以查看为所选建议生成的SQL 脚本。在表中单击相应的建议标识符,可以查看实施建议所需执行的操作列表。在“Actions(操作)”
页上,可以查看实施操作需要执行的所有相应SQL 语句。对于不希望实施的建议,请使对应的复选框处于未选中状态。然后,单击“Schedule Implementation(安排实施)”按钮来实施保留的操作。此步骤以调度程序作业的形式执行。
SQL 性能分析程序:概览
• 目标用户:DBA、QA、应用程序开发人员
• 帮助预测系统更改对SQL 工作量响应时间的影响
• 构建不同版本的SQL 工作量性能数据(即SQL 执行计划和执行统计信息)
• 以串行方式执行SQL(不考虑并发)
• 分析性能差异
• 针对单个SQL 提供细粒度性能分析
• 与SQL 优化指导相集成以优化回归

Oracle Database 11 g 包括SQL 性能分析程序,该工具可以准确地评估更改对构成工作量的SQL 语句的影响。SQL 性能分析程序有助于预测潜在的更改对SQL 查询工作量的性能影响。此功能可为DBA 提供有关SQL 语句性能的详细信息,如执行前后的统计信息,以及性能有所提高或降低的语句。这样您便可以(例如)在测试环境中进行更改,以确定数据库的升级是否会改进工作量性能。
SQL 性能分析程序:使用情形

SQL 性能分析程序可用于预测和防止任何影响SQL 执行计划结构的数据库环境更改所带来的潜在性能问题。这些更改可包括(但不限于)以下任何更改:
• 数据库升级
• 实施优化建议
• 方案更改
• 收集统计信息
• 更改数据库参数
• 更改操作系统和硬件
可以使用SQL 性能分析程序分析最复杂的环境更改所导致的SQL 性能变化。随着应用程序在开发生命周期中的推进,数据库应用程序开发人员可以测试对方案、数据库对象和重写应用程序的更改,以减轻任何潜在的性能影响。
SQL 性能分析程序还可以比较SQL 性能统计信息。
可以通过Oracle Enterprise Manager 或使用DBMS_SQLPA程序包访问SQL 性能分析程序。
使用SQL 性能分析程序
1. 捕获生产环境的SQL 工作量。
2. 将SQL 工作量传输至测试系统。
3. 构建“更改前”性能数据。
4. 进行更改。
5. 构建“更改后”性能数据。
6. 比较步骤3 和步骤5 的结果。
7. 优化回归的SQL。

1. 收集SQL:在此阶段中,将收集代表生产系统中的 SQL 工作量的SQL 语句集。
2. 传输:必须将得到的工作量结果传输到测试系统。将 STS 从生产系统中导出,然后再导入测试系统。
3. 计算“之前版本”性能:在进行任何更改之前,先执行 SQL 语句,收集评估未来更改对工作量性能可能产生的影响所需的基线信息。
4. 进行更改:获得了之前版本数据后,即可实施计划的更改,然后开始查看对性能的影响。
5. 计算“之后版本”性能:在数据库环境中进行了更改之后,执行此步骤。SQL 工作量中的每个语句都在虚拟执行模式下运行(仅收集统计信息),收集与步骤3 所捕获的
相同的信息。
6. 比较和分析SQL 性能:在获得了两个版本的 SQL 工作量性能数据后,可以比较之后与之前版本的数据,进行性能分析。
7. 优化回归的SQL:在此阶段中,已经准确地确认了哪些SQL 语句在进行数据库更改时可能导致性能问题。你可以使用任何一种数据库工具来优化系统。在实施了任何优
化操作后,应重复该过程来创建新的之后版本数据,然后分析性能差异以确保新的性能是可接受的。
小结
• 将SQL 优化指导用于:
– 确定使用资源最多的 SQL 语句
– 优化使用资源最多的 SQL 语句
• 使用SQL 访问指导优化工作量
相关文章
最新文章
热点推荐