查看: 1293|回复: 0

[Oracle数据库] 基于成本的优化--CBO

发表于 2018-3-8 08:00:02
  1. 选择CBO的优化方式
  2. 默认条件下,CBO将SQL语句的吞吐量作为优化目标
  3. 三种不同的优化方式
  4. ALL_ROWS :该优化方式是Oracle的默认模式,优化目标是实现查询的最大吞吐量
  5. FIRST_ROWS_n:该优化方式使用CBO的成本优化输出查询的前n行数据,目标是以满足快速相应的查询需求,
  6. FIRST_ROWS :该方式是FIRST_ROWS_n优化方式的老版本,作用是使用CBO的成本优化尽快输出查询的前几行数据,满足最小相应时间的需求
  7. 查询当前数据库的CBO优化方式
  8. show parameter optimizer_mode
  9. 在实例级设置优化方式
  10. alter system set optimizer_mode = FIRST_ROWS_10 scope=spfile
  11. 在会话级设置优化方式
  12. alter session set optimizer_mode=ALL_ROWS
  13. 会话级上设置优化方式必须使用hint提示
  14. select /*+first_rows_10*/ ename,sal,mgr
  15. from scott.emp
  16. 优化器工作过程
  17. 步骤
  18. 1.SQL转换
  19. 在CBO优化中,一个SQL语句往往被转换成另一种表达形式,这个转换的基础是CBO认为转换后的查询会更有效
  20. 2.确定访问路径
  21. 一个SQL查询中对数据的访问的路径要根据访问这些数据消耗的资源来判断,在多个查询路径中选择计算成本最小的一个。
  22. 3.确定联结方式
  23. 在SQL语句中涉及多个表时,CBO会根据统计数据以及表的键的信息来选择连接方式,在多个连接方法中选择计算成本最低的一个作为最佳连接方法
  24. 4.确定联结次序
  25. CBO会对不同的连接次序中进行计算以选择最好的执行计划。
  26. 自动统计数据
  27. 查看GATHER_STATS_JOB状态
  28. select job_name,state,owner
  29. from dba_scheduler_jobs;
  30. 通过数据字典DBA_TABLES查询用户SCOTT拥有表的统计分析情况
  31. select last_analyzed,table_name,owner,num_rows,sample_size
  32. from dba_tables
  33. where owner='SCOTT'
  34. 手动统计数据库数据
  35. DBMS_STATS
  36. 存储过程
  37. GATHER_DATABASE_STATS 为全库中的表统计数据
  38. GATHER_SCHEMA_STATS 为某个模式统计数据
  39. GATHER_TABLE_STATS 为某个特定的表统计数据
  40. GATHER_INDEX_STATS 为某个索引表统计数据
  41. 上述统计数据保存在 DBA_TAB_STATISTICS 和 DBA_TAB_COL_STATISTICS
  42. 为模式SCOTT的所有表统计数据
  43. execute DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT');
  44. 验证模式SCOTT的数据统计成功
  45. select last_analyzed,table_name,owner,num_rows,sample_size
  46. from dba_tables
  47. where owner='SCOTT'
  48. 为模式SCOTT用户的表EMP统计数据
  49. execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP');
  50. 为DEPT的索引统计数据
  51. execute DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_DEPT')
  52. 手工收集数据库级别的统计数据-----需要对初始化参数JOB_QUEUE_PROCESSES设置一个非0值
  53. execute DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent=>null)
  54. 查询表的统计数据 DBA_TAB_STATISTICS
  55. 查询表的列的统计数据 DBA_TAB_COL_STATISTICS
  56. 统计OS数据
  57. DBMS_STATS.GATHER_SYSTEM_STATS SYS.AUX_STAST$
  58. 无负载方式下收集10分钟的系统统计数据
  59. execute DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD',10)
  60. 收集系统统计数据
  61. execute DBMS_STATS.GATHER_SYSTEM_STATS('start')
  62. execute DBMS_STATS.GATHER_SYSTEM_STATS('stop')
  63. 每三分钟执行一次
  64. 查询统计的系统数据
  65. select * from SYS.AUX_STAST$;
  66. 手工统计字典数据---具备SYSDBA权限
  67. 收集固定字典表的统计数据
  68. execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
  69. 收集数据字典表的统计数据
  70. execute DBMS_STATS.GATHER_DIRECTORY_STATS;
  71. /
  72. 使用过程GATHER_SCHEMA_STATS统计数据字典数据
  73. execute DBMS_STATS.GATHER_SCHEMA_STATS('sys')
  74. 主动优化SQL语句
  75. SQL语句优化工具
  76. 1.使用EXPLAN FOR 指令
  77. utlxplan.sql
  78. 执行脚本---生成PLAN_TABLE表
  79. @?/rdbms/admin/utlxplan.sql
  80. 通过EXPLAIN PLAN FOR 指令分析SQL语句的执行计划
  81. explain plan for select count(*) from scott.emp;
  82. 查看表 PLAN_TABLE 中SQL语句执行计划信息
  83. col if for 999
  84. col operation for a20
  85. col options for a20
  86. col object_name for a20
  87. select id,operation,options,object_name,position
  88. from PLAN_TABLE
  89. OPERATION :为TABLEACCESS说明该步骤的行为是访问表
  90. OPTIONS :为FULL,说明全表扫描访问表
  91. OBJECT_NAME :说明行为的对象为表EMP
  92. 使用AUTOTRACE指令------SQL_TRACE=TRUE
  93. 设置参数 SQL_TRACE 启动SQL语句追踪
  94. alter system set SQL_TRACE = TRUE;
  95. /* 选项 结果
  96. SET AUTOTRACE ON 查询输出,解释计划,统计信息
  97. SET AUTOTRACE OFF 关闭 AUTOTRACE
  98. SET AUTOTRACE ON EXPLAIN 查询输出,解释计划,没有统计信息
  99. SET AUTOTRACE ON EXPLAIN STAT 查询输出,解释计划,统计信息
  100. SET AUTOTRACE ON STAT 查询输出,解释计划,统计信息
  101. SET AUTOTRACE TRACE 解释计划,统计信息,生成结果但不显示
  102. SET AUTOTRACE TRACE EXPLAIN 只有解释计划,不生成结果
  103. SET AUTOTRACE TRACE STAT 只有统计,生成结果但不显示*/
  104. 使用AUTOTRACE追踪SQL语句执行计划
  105. set autotrace traceonly
  106. select count(*) from scott.emp
复制代码

aotot.png

  1. recursive calls 递归调用的次数
  2. db block gets 读数据块的数量
  3. consistent gets 总的逻辑I/O
  4. physical reads 物理I/O
  5. redo size 重做数量
  6. bytes sent via SQL*Net to client SQL*Net通信
  7. bytes received via SQL*Net from client
  8. SQL*Net roundtrips to/from client
  9. sorts (memory) 内存排序统计
  10. sorts (disk) 磁盘排序统计
  11. rows processed 被检索的行数
  12. 关闭AUTOTRACE
  13. set autotrace OFF
  14. 启动 SQL Trace的前提
  15. 1.statistics_level: TYPICAL / ALL
  16. BASE
  17. 2.timed_statistics: TRUE -----BASE
  18. False -----TYPICAL / ALL
  19. 3.user_dump_dest: 该参数存储SQL语句的追踪文件。
  20. (max_dump_file_size)
  21. 启动SQL Trace追踪
  22. 实例级启动SQL Trace追踪
  23. alter system set SQL_TRACE=TRUE
  24. 会话级启动SQL Trace追踪
  25. alter session set SQL_TRACE=TRUE
  26. /
  27. begin
  28. sys.dbms_session.set_sql_trace(TRUE);
  29. end;
  30. 使用 TKPPOF 解释 SQL Trace文件
  31. 执行sql查询
  32. 使用TKPPOF工具格式化SQL追踪文件
  33. TKPPOF xxxxxxxx.trc xxxx.txt sys=no
  34. 格式化参数的含义
  35. count :不同执行阶段所读取的数据块数量
  36. cpu :不同执行阶段锁消耗的CPU时间,单位是秒
  37. elapsed :执行用掉的时间
  38. disk :物理磁盘数据读操作数目
  39. query :一致的缓冲区读取数量
  40. current :数据库块读取的数量
  41. call :该参数说明SQL语句的不同执行阶段
复制代码


  1. 消除子查询优化SQL语句
  2. 对查询用户scott的emp表进行嵌套子查询
  3. select *
  4. from scott.emp e1
  5. where e1.sal>
  6. (select avg(sal)
  7. from scott.emp e2
  8. where e2.deptno=e1.deptno)
  9. 开启AUTOTRACE功能
  10. set autotrace traceonly
  11. 跟踪SQL语句的执行
  12. select *
  13. from scott.emp e1
  14. where e1.sal>
  15. (select avg(sal)
  16. from scott.emp e2
  17. where e2.deptno=e1.deptno)
复制代码

tracesql.png

  1. 跟踪改写的SQL语句
  2. 使用联机视图改写子查询
  3. select * from scott.emp e1,(select e2.deptno deptno ,avg(e2.sal) avg_sal
  4. from scott.emp e2 group by deptno ) dept_avg_sal
  5. where e1.deptno = dept_avg_sal.deptno
  6. and e1.sal > dept_avg_sal.avg_sal
复制代码

viewtrace.png

  1. 被动优化SQL语句
  2. 使用分区表
  3. 使用表和索引压缩
  4. 创建压缩表
  5. create table compress_emp
  6. compress
  7. tablespace users
  8. as select * from scott.emp
  9. 查询是否成功创建压缩表 compress_emp
  10. select table_name,tablespace_name,compression
  11. from user_tables
  12. where table_name like 'COMPRESS%';
  13. 创建压缩索引
  14. create index compress_emp_ename_idx
  15. on compress_emp(ename)
  16. compress;
  17. 保持CBO的稳定性
  18. 1.创建存储大纲的前提
  19. 初始化参数
  20. QUERY_REWRITE_ENABLED = TRUE
  21. STAR_TRANSFORMATION_ENABLED = TRUE
  22. 验证系统师傅具备创建存储大纲的前提
  23. show paameter QUERY_REWRITE_ENABLED;
  24. show paramter STAR_TRANSFORMATION_ENABLED;
  25. show parameter optimizer_features_enable;
  26. 2.创建存储大纲
  27. 创建数据库级的存储大纲
  28. alter system set create_stored_outlines = TRUE
  29. 创建会话级的存储大纲
  30. alter session set create_stored_outlines = TRUE
  31. 为特定SQL语句创建存储大纲
  32. create outline emp_outline
  33. on
  34. select *
  35. from scott.emp
  36. tablespace oltbs;
  37. 查询EMP_OUTLINE创建信息
  38. select ol_name,sql_text,creator,timestamp
  39. from ol$
  40. where ol_name like 'EMP%'
  41. 查询Oracle自动生产的存储大纲的名字
  42. set lines 120
  43. select ol_name,sql_text
  44. from ol$
  45. 3.删除存储大纲
  46. 删除存储大纲-----sysdba
  47. drop outline emp_outline
  48. 4.启用存储大纲
  49. 修改参数 USE_STORED_OUTLINES 为TRUE
  50. alter system set USE_STORED_OUTLINES= TRUE
复制代码




回复

使用道具 举报