sql.rar_SQL语句_hawk_sql优化

送上sql语句优化方法案例－针对Oracle数据库 在sql语句优化过程中，我们经常会用到hint,现总结一下在sql优化过程中常见oracle hint的用法： 1. /*+all_rows*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: select /*+all+_rows*/ emp_no,emp_nam,dat_in from bsempms where emp_no=''scott''; 2. /*+first_rows*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化. 例如: select /*+first_rows*/ emp_no,emp_nam,dat_in from bsempms where emp_no=''scott''; 3. /*+choose*/ 表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量; 表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法; 例如: select /*+choose*/ emp_no,emp_nam,dat_in from bsempms where emp_no=''scott''; 4. /*+rule*/ 表明对语句块选择基于规则的优化方法. 例如: select /*+ rule */ emp_no,emp_nam,dat_in from bsempms where emp_no=''scott''; 5. /*+full(table)*/ 表明对表选择全局扫描的方法. 例如: select /*+full(a)*/ emp_no,emp_nam from bsempms a where emp_no=''scott''; 6. /*+rowid(table)*/ 提示明确表明对指定表根据rowid进行访问. 例如: select /*+rowid(bsempms)*/ * from bsempms where rowid>=''aaaaaaaaaaaaaa'' and emp_no=''scott''; 7. /*+cluster(table)*/ 提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效. 例如: select /*+cluster */ bsempms.emp_no,dpt_no from bsempms,bsdptms where dpt_no=''tec304'' and bsempms.dpt_no=bsdptms.dpt_no; 8. /*+index(table index_name)*/ 表明对表选择索引的扫描方法. 例如: select /*+index(bsempms sex_index) use sex_index because there are fewmale bsempms */ from bsempms where sex=''m''; 9. /*+index_asc(table index_name)*/ 表明对表选择索引升序的扫描方法. 例如: select /*+index_asc(bsempms pk_bsempms) */ from bsempms where dpt_no=''scott''; 10. /*+index_combine*/ 为指定表选择位图访问路经,如果index_combine中没有提供作为参数的索引,将选择出位图索引的布尔组合方式. 例如: select /*+index_combine(bsempms sal_bmi hiredate_bmi)*/ * from bsempms where sal<5000000 and hiredate<sysdate; 11. /*+index_join(table index_name)*/ 提示明确命令优化器使用索引作为访问路径. 例如: select /*+index_join(bsempms sal_hmi hiredate_bmi)*/ sal,hiredate from bsempms where sal<60000; 12. /*+index_desc(table index_name)*/ 表明对表选择索引降序的扫描方法. 例如: select /*+index_desc(bsempms pk_bsempms) */ from bsempms where dpt_no=''scott''; 13. /*+index_ffs(table index_name)*/ 对指定的表执行快速全索引扫描,而不是全表扫描的办法. 例如: select /*+index_ffs(bsempms in_empnam)*/ * from bsempms where dpt_no=''tec305''; 14. /*+add_equal table index_nam1,index_nam2,...*/ 提示明确进行执行规划的选择,将几个单列索引的扫描合起来. 例如: select /*+index_ffs(bsempms in_dptno,in_empno,in_sex)*/ * from bsempms where emp_no=''scott'' and dpt_no=''tdc306''; 15. /*+use_concat*/ 对查询中的where后面的or条件进行转换为union all的组合查询. 例如: select /*+use_concat*/ * from bsempms where dpt_no=''tdc506'' and sex=''m''; 16. /*+no_expand*/ 对于where后面的or 或者in-list的查询语句,no_expand将阻止其基于优化器对其进行扩展. 例如: select /*+no_expand*/ * from bsempms where dpt_no=''tdc506'' and sex=''m''; 17. /*+nowrite*/ 禁止对查询块的查询重写操作. 18. /*+rewrite*/ 可以将视图作为参数. 19. /*+merge(table)*/ 能够对视图的各个查询进行相应的合并. 例如: select /*+merge(v) */ a.emp_no,a.emp_nam,b.dpt_no from bsempms a (selet dpt_no ,avg(sal) as avg_sal from bsempms b group by dpt_no) v where a.dpt_no=v.dpt_no and a.sal>v.avg_sal; 20. /*+no_merge(table)*/ 对于有可合并的视图不再合并. 例如: select /*+no_merge(v) */ a.emp_no,a.emp_nam,b.dpt_no from bsempms a (select dpt_no,avg(sal) as avg_sal from bsempms b group by dpt_no) v where a.dpt_no=v.dpt_no and a.sal>v.avg_sal; 21. /*+ordered*/ 根据表出现在from中的顺序,ordered使oracle依此顺序对其连接. 例如: select /*+ordered*/ a.col1,b.col2,c.col3 from table1 a,table2 b,table3 c where a.col1=b.col1 and b.col1=c.col1; 22. /*+use_nl(table)*/ 将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表. 例如: select /*+ordered use_nl(bsempms)*/ bsdptms.dpt_no,bsempms.emp_no,bsempms.emp_nam from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no; 23. /*+use_merge(table)*/ 将指定的表与其他行源通过合并排序连接方式连接起来. 例如: select /*+use_merge(bsempms,bsdptms)*/ * from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no; 24. /*+use_hash(table)*/ 将指定的表与其他行源通过哈希连接方式连接起来. 例如: select /*+use_hash(bsempms,bsdptms)*/ * from bsempms,bsdptms where bsempms.dpt_no=bsdptms.dpt_no; 25. /*+driving_site(table)*/ 强制与oracle所选择的位置不同的表进行查询执行. 例如: select /*+driving_site(dept)*/ * from bsempms,dept@bsdptms where bsempms.dpt_no=dept.dpt_no; 26. /*+leading(table)*/ 将指定的表作为连接次序中的首表. 27. /*+cache(table)*/ 当进行全表扫描时,cache提示能够将表的检索块放置在缓冲区缓存中最近最少列表lru的最近使用端 例如: select /*+full(bsempms) cahe(bsempms) */ emp_nam from bsempms; 28. /*+nocache(table)*/ 当进行全表扫描时,cache提示能够将表的检索块放置在缓冲区缓存中最近最少列表lru的最近使用端 例如: select /*+full(bsempms) nocahe(bsempms) */ emp_nam from bsempms; 29. /*+append*/ 直接插入到表的最后,可以提高速度. insert /*+append*/ into test1 select * from test4 ; 30. /*+noappend*/ 通过在插入语句生存期内停止并行模式来启动常规插入. insert /*+noappend*/ into test1 select * from test4 SQL Tunning 內部考試以及答案 Oracle Sql Tuning内部考卷 1、为什么SQL需要不断tuning？ 1). SQL 作用的表等對象中的紀錄筆数在不斷的表.針對紀錄筆數的不同,SQL的執行計畫若不便,效率會有很大的變化,所以要tuning SQL. 2). SQL在從一個版本的DB移植到另一個版本的DB时,根據DB支持的執行計畫的特點,要tuning SQL. 2、请列举出你知道的查看SQL执行计划的方法。 1) Oracle Enterprise Manager 2) Statspack 3) Explain plan 4) SQL Trace and TKPROF 5) Autotrace 3、Oracle Outlines的用途？ 保持指定的SQL的執行計畫不會受DB環境變化的影響. 4、Oracle optimzer_mode参数的值有哪些？ 说出各值代表的意义？ Oracle optimzer_mode参数的值有: RULE,FIRST_ROWS,FIRST_ROWS_N, ALL_ROWS, CHOOSE. Rule: 忽略CBO和統計數据并且完全基于基本數据字典信息生成執行計划﹒總是使用INDEX﹐總是從驅動表開始鏈接TABLE; First_rows: 最快的速度返回紀錄﹐但是會造成總体查詢速度的下降或者是消耗更多的資源﹒更傾向于使用INDEX﹐适用于在線系統; First_rows_n: 指示选择一个查询执行计划,这个计划会缩短生成最初n行查询结果的時間; All_rows:總体查詢時間最短﹐但是收到第一條紀錄要花費更長的時間﹒通常使用全表掃描﹐适用于批量查詢; Choose: ORACLE默認的优化器模式﹐如果統計資料不存在將使用RULE﹐如果存在則根据參數設定來确定哪种CBO的优化模式. 5、请写出在SQLPLUS中设定SQL trace环境的步骤。 1) sql_trace =true --用戶對全局的設置 2) alter session set sql_trace=true; --用戶對當前session的設置 3) exec dbms_system.set_sql_trace_in_session(9,437,true) --用於對其他用戶sessions的設置. 6、查询数据时， 一定需要使用索引吗？ 为什么？ 不一定. 原因為: 比如一個帶有索引的表,只有很少筆紀錄,針對該表的查詢,如果使用索引, DBMS在執行查詢時,要做兩步:先查index,然後根據index 去查表;而如果不使用索引,DBMS只要掃描全表,一步就可以了,執行效率也就高了. 7、当cursor_sharing = exact时， 写SQL时需要注意些什么？ 注意SQL語句的大小写要統一規範,空格多少個也要統一規範; 注意儘量不用hardcode,而改用變量. 8、Union all 和union 有什么差别？ Union all不會對合併後的紀錄排序,不會去除重複的紀錄; Union會排序和去除重複的紀錄. 9、建立SQL时，为什么对存在索引的列，不建议使用 <> ? <>是永遠不會用到索引的﹐對它的處理只會產生全表掃描 10、 设定sql_trace = true 后，生成trc格式的文件存放在哪里？ 跟踪文件位于user_dump_dest目录中,比如: /opt/oracle/admin/hsjf/udump/*.trc */ --plsql 性能优化 DBMS_Profiler:PL/SQL 性能调整 XML:namespace prefix = o ns = "urn:schemas-microsoft-com:Office:office" /> DBMS_PROFILER 包举例 下面是我提供的怎样使用配置的简单例子,运行配置文件来测试下面例程的性能. 例程用到的自定义脚本紧随其后. 1. 创建过程. create or replace procedure am_perf_chk (pi_seq in number,