Oracle SQL Tuning_SQL优化方法论_02

本文章参考郭一军老师的视频《Oracle SQL Tuning（DSI系列Ⅲ）》

We need to tune a query      什么情况下需要优化SQL？

1. A single query is taking too much time           Wait Time + Service Time = Response Time

    单个SQL查询执行时间比较长                        等待时间 + 服务时间   = 响应时间

2. Many querues taking too long, but one is representative          bind peeking

    许多查询花费的时间比较长，但其中一种是具有代表性          绑定变量窥视

 

What triggered the problem?    什么情况下会触发SQL慢？

1. Upgrade...change in behavior or bug    数据库升级（优化器升级）或者 BUG

2. Statistics gathered                              统计信息的收集

3. Data changed                                     数据发生改变

4. Application changed                           应用程序发生改变

 

What can go wrong with a Query?       什么地方会出错呢？

1. Index used / on used...or needs to be created   

   是否使用了索引，是否使用索引数据库变慢，或没使用索引数据库慢，及是否需要创建索引

2. Wrong join order used                                      表连接顺序错误

3. Wrong join type used                                        表连接类型错误（Nested Loop / Hash Join / Sort Merge Join）

4. Predicates not pushed, views not merged         谓词未推进，视图未合并

5. Transformation improperly costed                     SQL转换不恰当，导致成本比较高

6. Other problems...                                             其他问题

 

Why does the CBO make bad plans?   为什么CBO生成错误执行计划

1. CBO estimated rows very different from actual             CBO估算的行与实际行有很大出入

2. CBO heuristics are imperfect                                       CBO算法的缺陷

3. Statistics are imperfect                                                统计信息不完善

4. Other reasons...

 

What are we going to do?     我们将如何做？

1. Identify the “bad” query                           识别有问题的查询

2. Verify the query is “bad”                          验证查询是否有问题

3. Determine what’s wrong with query         确定什么导致有问题的查询

4. Solve the query problem                         解决问题

ID->Verify->Determine->Solve                    识别->验证->确定->解决

 

Identify the “bad” query Ways to find a “bad” query     定位缓慢SQL

1. Client reports a query is taking too long                               用户反应查询时间过长

    Sometimes appears hung                                                   似乎hung

2. OS stats (TOP) > Session data shows top query                 OS(TOP)>会话数据显示查询次数最多

3. AWR, AWR Comparison Report, ASH Report                      DB(AWR报告, ASH报告)

      Top Timed Events related to CPU and I/O                          与CPU和I/O相关的等待

      SQL is responsible for most DB Time or activity                 SQL负责大多数数据库时间或活动

4. TKProf / TRCA shows certain SQL taking too much time      TKProf / TRCA显示某些SQL需要时间过长

5. ADDM found SQL as a main problem                                   ADDM发现SQL是主要问题

6. SQL Perf.Analyzer shows a query regressed                        SQL Perf.Analyzer显示SQL查询递归信息

Top Down Approach 自上而下方法论

 

Determine What’s Wrong    确定问题

1. Two Approaches                                                            两种方法

       Triage                                                                         分诊

       Query Analysis                                                           查询分析

2. Triage Approach                                                             分诊方法

       Find a good plan                                                          找到一个好的执行计划

       Fix it (Quick)                                                                修复（快速）

3. Query Analysis Approach                                                查询分析方法

       Find out why it’s a bad plan                                          找出为什么这是一个有问题的计划

       Address the cause                                                       找到原因

       Fix the query; maybe fix many other queries like it        修正查询；解决其他查询

 

Triage Approach  分诊方法

1. Objectives:                                                                       目标

      Quick solution to a problem                                              快速解决问题

      Not a thorough analysis                                                   不需要彻底的分析

      Some customers want this, others a thorough analysis     有些客户想要这个，其他的则需要彻底的分析

      Inferior to the “Query Analysis” Approach                          不如“查询分析”方法

2. Approach Overview:                                                           方法概述

      Find a good plan                                                                找到一个好的计划

             Compare with historical data (AWRSQRPT.SQL)         与历史数据比较

             Or, leverage the CBO to get it (SQLTXPLORE)           利用CBO进行获取

      Make a bad plan look like the good one                               使一个有问题计划变成一个好计划

3. finding a Good Plan                                                               寻找一个好的计划

       Use SQL Tuning Advisor (if you can)                                  使用SQL Tuning Advisor（如果可以）

       Get a good test case...                                                       获得一个好的测试用例...

       Leverage the CBO by tweaking settings to change the plan (SESSION only)

       通过调整设置来更改计划以利用CBO（仅适用于SESSION）

                FIRST_ROWS_N, ALL_ROWS

               OPTIMIZER_FEATURES_ENABLE

               OPTIMIZER_INDEX_COST_ADJ

               OPTIMIZER_INDEX_CACHING

4. Try Dynamic Sampling at high levels                                             尝试高水平的动态采样

5. SQLT XPLORE does this and more automatically                         SQLT XPLORE可以自动执行此操作

6. Induce the CBO to Produce the Good Plan                                   促使CBO制定良好计划

    Capture a Stored Outline (once you get the good plan)                捕获存储的大纲（一旦制定了好的计划） 就是Hit和 Profile

    Accept a profile from STA                                                            接受STA的个人资料

    Use a profile from SQLT generated from good plan                     使用通过良好计划生成的SQLT配置文件profile

    11g: SQL Plan Management                                                      SQL计划管理

             Cause the good plan through stats change/parameters      通过统计信息更改/参数导致良好的计划

             Evolve the good plan to be accepted                                  制定好的计划被接受

     11g: Use Panding Statistics in a session to cause the good plan   在会话中使用Panding Statistics来制定良好的计划

 

Query Analysis Approach         查询分析方法

1. Objectives:                                                                                目标

        Address the root cause of the problem                                   解决问题的根本原因

        Spend time to figure this out                                                  花时间弄清楚这个

        Possibly solve many queries affected, instead of just one       可能解决许多受影响的查询，而不仅仅是一个

        Discover if it’s a bug and obtain a patch for it                          发现是否是BUG并为其获取补丁

2. Summary of the Query Analysis Approach                                查询分析方法摘要

        Gather Data for the Query                                                   收集查询数据

              SQLT XECUTE or TKPROF / XTRACT output (execution plan, object stats, runtime stats, DDL)

              Sanity check cost, system and object stats, parameter settings

        Understand the SQL and its Purpose                                 了解SQL及其用途

         Reconcile:

                 User’s performance expectation for given query             用户对给定查询的效果期望

                 Actual execution performance of given query                给定查询的实际执行性能

                 Amount of work extimated by the CBO                          CBO估算的工作量

                 Realistic time to perform minimum work required to do the job, roughly  实际完成该工作所需的最低限度工作的时间

         Draw the “ERD” behind the SQL (use join predicates)            在SQL后面绘制“ ERD”（使用连接谓词）

         Validate expected number of rows, work needed, etc             验证预期的行数，所需的工作等

         Understand how the CBO chose the plan                             了解CBO如何选择计划

         Review the plan to find potential trouble spots               审查计划以查找潜在的问题点

                 Look for earliest plan steps where actual and extimated rows differ   寻找实际行和估算行不同的最早计划步骤

                 Look for plan steps where actual and estimated time differ (may use cost)   查找实际时间和预计时间不同的计划步骤

                 Look for plan steps where cost and logical reads differ significantly    寻找成本和逻辑读取有明显差异的计划步骤

           For each “trouble spot”, consider possibilities:             对于每个“故障点”，请考虑可能性：

                  Are unsuitable statistics causing estimation problems?     不合适的统计信息会导致估算问题吗？

                  Is predicate dependence causing cardinality estimation problems?      谓词依赖性是否会导致基数估算问题？

                  Does the CBO estimate seem unreasonable (due to a bug)?         CBO估算似乎不合理（由于错误）？

                 Can an index be created or changed to maximize use of access predicate, index filter predicate, or avoid a table access?                 是否可以创建或更改索引以最大程度地使用访问谓词，索引过滤谓词或避免全表访问？

                   Is the SQL needlessly complex and confusing the CBO or preventing an index from being used?       

                   SQL是否不必要地复杂且混淆了CBO或阻止了使用索引？

          Consider corrective actions    考虑采取纠正措施

                  1. Collect better stats   收集更好的统计数据

                         Object stats at a higher sampling size (& try add/drop histograms) 

                         抽样数量较高的对象统计信息（请尝试添加/删除直方图）

                        System stats    系统统计

                       11g: Use extended stats   使用扩展统计信息

                  2. Lock stats or use dynamic sampling (detection of a better plan) 锁定统计信息或使用动态抽样（发现更好的计划）

                  3. Rewrite query (simplify, use PL/SQL pipelined functions)    重写查询（简化，使用PL/SQL流水线函数）

                  4. Change application (reduce/avoid database calls if possible) 更改应用程序（如果可能，减少/避免数据库调用）

                  5. Create or improve indexes (add columns, change order, drop)   创建或改善索引（添加列，更改顺序，删除）

                  6. Use other features like IOTs, composite indexes with all columns required by query, parallelism, materialized views, partitioning...       使用其他功能，具有查询所需的所有列的组合索引，并行性，物化视图，分区...

                  7. Incorporate or remove hints    合并或删除提示

                  8.Use stored Outlines or SQL Profiles     使用存储的提纲或SQL配置文件

 

Important Diagnostics and Tools   诊断工具

1. SQLT XECUTE / XTRACT / COMPARE / XPLORE

       Execution plan       执行计划

       Object information including statistics gathered and metadata     对象信息，包括收集的统计信息和元数据

       Environment information    环境信息

       Comparison and test case capabilities    比较和测试用例功能

2. DBMS_XPLAN

3. 10046 and TKProf

4. TRCANLZR

        Analyze 10046 deeper and more complete than TKProf          比TKProf更深入，更完整地分析10046

5. 10053 trace and Comparison Tool

6. Test Case Builder

7. Misc. 11g EM Packs