SQL调优01–SQL优化介绍

Posted on by

Introduction to SQL Tuning

  1. SQL效率太低的原因:
    1. Stale or missing optimizer statistics:缺失优化统计信息或者信息太旧;
    2. Missing access structures:缺少索引,考虑索引的效率;
    3. Suboptimal execution plan selection:不是最好的执行计划;
    4. Poorly constructed SQL:SQL语句写的不好;
    5. 最重要的是表的数据量太大,归档历史数据.小的数据量可以解决一切问题;
    6. 解决办法:
      1. 尽量不要用子查询,可以通过关联查询解决;
      2. 不要再表列上面使用函数,导致索引无效;
      3. 如果发生隐式转换也不走索引,因为oracle内部总是转换表的列;
      4. 尽量使用UNION ALL而不用UNION;
      5. 排序,去重复,分组现在默认使用hash去除重复,对CPU消耗很大;
  2. 性能监控的解决方案;                                                                
  3. Oracle中监控和调优的工具;                                                      
  4. 调优的工具:
    1. Automatic Database Diagnostic Monitor (ADDM);
    2. SQL Tuning Advisor;
    3. SQL Tuning Sets;
    4. SQL Access Advisor;
    5. SQL Performance Analyzer;
    6. SQL Monitoring;
    7. SQL Plan Management:在11g中的工具,可以控制某个sql的执行计划;
  5. SQL调优的任务:
    1. 查找高负载的SQL语句;
    2. 收集统计信息;
    3. 收集系统统计信息;
    4. 重建已存在的索引;
    5. 维护执行计划;
    6. 创建新的索引;
  6. CPU和Wait Time的调优
    1. db_time=cpu_time+wait_time;
    2. 如果db_time增加,cpu_time和wait_time等比例增加,说明这是一个可扩展的系统,只需增加硬件即可;
    3. 如果db_time增加,cpu_time远大于wait_time的增加,说明SQL效率不高,需要SQL的优化;
    4. 如果db_time增加,cpu_time远小于wait_time的增加,说明内部有争用或者IO效率太低;
  7. 客户系统的常见问题:
    1. Bad connection management:可以使用连接池解决;
    2. Bad use of cursors and the shared pool:适当调大SGA和PGA,并指定动态管理;
    3. Excess of resources consuming SQL statements:sql要反复执行;
    4. Use of nonstandard initialization parameters:使用了隐含参数或者参数使用不当;
    5. Poor database disk configuration:IO问题;
    6. Redo log setup problems:至少使用三组在线日志组,每组设置足够大,保证20分钟切换一次;
    7. Excessive serialization:串行化扫描,添加索引,尽量使用单列索引,可控性比较强;
    8. Inappropriate full table scans:全表扫描,主要是加索引解决;
    9. Large number of space-management or parse-related generated SQL statements:如果使用本地管理表空间的的话一般不会出现递归SQL;
    10. Deployment and migration errors:部署时出错,这个是人为原因;
  8. 应用的设计:
    1. 简化设计;
    2. 数据模型:
      1. 主要还是要与业务逻辑相结合;
      2. 可以使用建模工具如Oracle Designer,但是最好是使用详细的文档;
      3. 考虑是OLTP系统还是DW系统;
    3. 表设计:
      1. 考虑使用默认值,约束,物化视图,分区表等特性;
      2. 分区列一定要在where条件上,而且最好不要更新;
      3. 不建议使用触发器;,触发器的目的是做check,而不是做DML;
      4. 不建议使用外键,可以使用程序保证数据的完整性;
    4. 索引设计:
      1. 索引的列一定要经常出现在WHERE后面;
      2. 在DW中建议使用外键,在OLTP中考虑到性能可以不用外键,加外键的话就一定要加索引;
    5. 视图:
      1. 可以使用视图,但是最好不要嵌套视图;
      2. 嵌套视图影响执行计划;
  9. Share Cursors
    1. 尽量使用存储过程和函数;
    2. cursor_sharing初始化参数尽量不要修改;                                             

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注