Oracle SQL Tuning SQL优化方法论2
本文章参考郭一军老师的视频《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