Oracle SQL Tuning_01 SQL优化方法论

前言

课程介绍

本课是基于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


OPI: 程序接口

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里同样有一块区域作为数据库缓冲区&&高速缓存。

文章目录
  1. 1. 前言
    1. 1.1. 课程介绍
    2. 1.2. Oracle Layers
    3. 1.3. High Level Vision DSI
  2. 2. SQL优化方法论
    1. 2.1. Where are we?
      1. 2.1.1. We need to tune a query(什么情况下需要优化SQL)
      2. 2.1.2. 什么情况会触发SQL变慢
      3. 2.1.3. 什么地方会出错
      4. 2.1.4. 为什么CBO会生成错误的执行计划
    2. 2.2. What are we going to do?
    3. 2.3. Identify the “bad” query Ways to finds a “bad” query
    4. 2.4. Top Down Approach
    5. 2.5. Determine What’s Wrong
      1. 2.5.1. Triage 分诊
      2. 2.5.2. Query Analysis 查询分析
    6. 2.6. Important Diagnostics and tools
    7. 2.7. Summary
  3. 3. User Response Time Analysis
    1. 3.1. Database Call Analysis
    2. 3.2. Response Time
    3. 3.3. Unaccounted-for Time
  4. 4. Cache和Buffer的区别