sql语句优化.sql

oracle sql语句优化 hawk 收集

送上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, 
                                                     pio_status in out nocopy varchar2) is 
              l_dat date := sysdate; 
            begin 
              if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then 
                pio_status := 'OK'; 
              else 
                pio_status := 'Invalid tape loaded'; 
              end if; 
            exception 
              when others then 
                pio_status := 'Error in am_perf_chek'; 
            end; 
 

2.         用配置文件调用例程

       替换上面的例程, 执行call_profiler.sql脚本(脚本代码参见下面),传入pi_seq=2
            SQL> @d:\am\call_profiler.sql 
            Profiler started 
            Invalid tape loaded 
            PL/SQL procedure successfully completed. 
            Profiler stopped 
            Profiler flushed 
            runid:8 
 

3.         评估执行时间:
执行eavluate_profiler_results.sql脚本,得到时间统计