sql turning 的学习笔记

sql turning 的学习笔记
一
1 描述优化器的使用
2 执行计划稳定性
3 如何实现化器
4 描述hints的使用
5 使用跟踪SQL
6 收集表和索引的统计信息
7 使用柱状图
8 多个数据库拷贝统计信息
二 SQL调优的目的：让优化器选择什么样的优化器 sql选择什么样的路径
rule 依赖是数据字典 和cbo 统计信息
三 设置优化器的类型:
在实例级别 choose|rule|first_row|first_rows_n|all_rows
在会话级别 choose|rule|first_row|first_rows_n|all_rows
在语句级别  使用hints
四 设置稳定的执行计划:
1创建存储outline（大纲）
自己创建一个存储大纲稳定执行计划
1 容许用户创建outline(sys)
2 execute dbms_outln_edit.create_edit_tables;(scott)
3 alter session set create stored outlines cuug;
4 alter session set use_stored_outlines=cuug;
5 create or replace outline emp3 empno for category cuug on
select * from emp3 wher empno=7788;
6 执行select * from emp3 wher empno=7788;走全表扫描
7 在empno 上添加索引还是走之前的执行计划 全表扫描
8 如果让优化器用上面的索引必须使用hints 给oracle一个建议不一定走索引
概述调优工具
1 statspack
2 explan plan 只产生执行计划不执行语句
创建一个表
plan_table @?/rdbms/admin/utlxplan
explain  plan for
3 sql trace and tkprof
alter session set sql_trace= true
run app
alter session set sql_trace= false
tkprof 1  2
alter session set sql_trace=false|true
execute dbms_session.set_sql_trace true|false
execute dbms_system.set_sql_trace_in_session
(session_id,serial_id,true|false)
4 sqlplus autotrace features
@?/rdbms/sqlplus/admin/plustrce.sql
grant plustrace to scott;
set autotrace off|on|traceonly
5 oracle sql analyze
五:管理统计信息
1使用 分析命令去收集或者删除统计
2 你可以使用dbms_stats package
 1 gather_table_stats
 2 gather_index_stats
 3 gather_schema_stats
 4 gather_database_stats
 5 gather_stale_stats
表的统计信息
  表的行数
  块和空快的行数
  自由空间有效值
  放在数据字典
  
表的统计
analyze table emp3 compute statistics for columns empno;
查询表的统计信息:
表dba_tab_col_statistics 知道列值的分布情况让oracle按照什么样的路径找到这个行
柱状图:
影响oracle对优化器的选择 保存在数据字典dba_histograms,dba_tab_histograms中
分析列值所占的比例:
例如对一个表的id进行分析 生成五个柱状图
1-20 放在第一个 20-30 放在第二个 一次类推
假如在id上建立index 执行select ....... where id=66 在柱状图上占的比例少所以走index
假如执行select .......where id=20 在柱状图上占占用的比例多(40%) 建议oracle 走full scan
收集表的柱状图可以用
execute dbms_stats.gather_table_stats（'HR','EMPLOYEE',METHOD_OPT=>'FOR COLUMNS SIZE 10 SALARY '）;
在两个数据库中copy 统计信息
从生产库copy到测试库做个比较
step 1 copy 统计信息
create the table to hold the statistics:
dbms_stats.create_stat_table('train'// schoma name
,'stats' //statistics table name
,'users')//tablespace
step 2 copy 到表中
dbms_stats.export_table_stats('train','courses',null,'stats','crs990601',true);
step 3 到处stats table and then import it into the second database.
step 4 copy the statistics into the data dictionary:
dbms_stats.import_table_stats('train','courses',null,'stats','crs990601',true);