Oracle SQL Tuning_SQL优化方法论_01

前言

课程介绍

本课是基于Oracle DSI404e学习Oracle SQL Tuning。DSI是Data Server Internals的缩写，是Oracle公司内部用来培训Oracle售后工程师使用的教材。

DSI课程系统包括：

• DSI303 Advanced Backup, Restore and recovery Techniques
• DSI401 Dumps Crashes and Corruptions
• DSI402 Space and Transaction Management
• DSI402e Data types and block structures
• DSI403e Recovery Architecture Components
• DSI404e Query Optimizer
• DSI405 Performance TUning
• DSI408 Real Application clusters Internals

Oracle Layers

 

High Level Vision DSI

 

SQL优化方法论

Where are we?

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

• 一个简单的查询占用了太多的时间
• 使用绑定变量(bind peeking)的许多查询，访问有严重数据倾斜的列时使用了错误的执行计划，导致查询缓慢。
• 相对于原执行时间突然变慢的查询
• 用户对查询占用的时间不满意

什么情况会触发SQL变慢

• Oracle数据库版本升级，优化器版本升级或BUG
• 收集的统计信息与表现有状况差异大
• 数据改变：
  • 数据大量增多或减少后没有收集统计信息；
  • 改变数据后列有严重数据倾斜，SQL查询中该列的值用了绑定变量，导致错误(不好)的执行计划
• 应用程序升级改变

BIND PEEKING：当SQL第一次执行的时候，优化器会根据绑定变量来确定执行计划（如果存在柱状图）。BIND PEEKING只有当该SQL第一次执行的时候，进行HARD PARSE的时候才进行，第二次调用该SQL，就不会再次进行BIND PEEKING。这种情况下，如果某个列的倾斜性很厉害，使用BIND PEEKING代入不同的参数，只能走第一次执行时的执行计划，执行计划就像掷色子一样，要靠运气了。这种情况，不应该使用绑定变量，而应该改为直接值。

什么地方会出错

• 索引是否使用，索引是否创建，走索引是否快
• 错误的表连接顺序
• 错误的表连接类型
• 谓词没有推进，视图没有合并(predicates not pushed,views not merged)
• 转换成本高
• 其他问题

Single-table computed cardinality influences join orders to be costed(permutations)

为什么CBO会生成错误的执行计划

• CBO估算的行数和实际相差很大
• CBO算法自身的缺陷
• 统计信息的缺陷
• 其他原因

What are we going to do?

1. 识别找到有问题的SQL查询
2. 验证这个SQL查询是有问题的
3. 确定这个SQL对应的问题
4. 解决这个SQL查询问题

Identify the “bad” query Ways to finds a “bad” query

Top Down Approach

Oracle-OSW性能监控工具
Oracle-LTOM数据库性能故障诊断工具

Determine What’s Wrong

确定问题是什么有两个方法：

Triage 分诊

• 通过经验找一个好的执行计划，或者建一个索引等
• 快速的找到问解决问题的方案

Query Analysis 查询分析

• 深入分析为什么执行计划有问题
• 找到根源
• 修复查询解决问题

Important Diagnostics and tools

Summary

User Response Time Analysis

Database Call Analysis

Response Time

Unaccounted-for Time

Cache和Buffer的区别

Cache和Buffer是两个不同的概念，简单的说，Cache是加速“读”，而 buffer是缓冲“写”，前者解决读的问题，保存从磁盘上读出的数据，后者是解决写的问题，保存即将要写入到磁盘上的数据。在很多情况下，这两个名词并没有严格区分，常常把读写混合类型称为buffer cache，在Oracle Instance里同样有一块区域作为数据库缓冲区&&高速缓存。