分享
 
 
 

关于SQL优化的一些知识

王朝mssql·作者佚名  2006-11-24
窄屏简体版  字體: |||超大  

关于SQL优化我提提几点总结,这里没有具体的实例,如果大家有兴趣,可以把他贴上来

1.除非写频率很低,否则不要用RAID5,但是对于cache能够减少RAID5的负荷

2.对于一个chained fetch ratio的,建议需要用一个高的pctfree来rebuild table

3.如果你有一个很高的磁盘排序率,那么建议修改sort_area_size,将其增大

4.buffer busy waits常常是由于很频繁的insert ,需要重建,或者没有充足的回滚段引起的

还应观注一下event这列其他值,这是我们调优的关键一列,下面对常出现的event做以简要的说明:

free buffer waits这个参数所标识是dbwr是否够用的问题,与IO很大相关的,

当v$session_wait中的free buffer wait的条目很小或没有的时侯,说明你的系统的dbwr进程决对够用,

不用调整;free buffer wait的条目很多,你的系统感觉起来一定很慢,这时说明你的dbwr已经不够用了,

它产生的wio已经成为你的数据库性能的瓶颈,这时的解决办法如下:

a.1增加写进程,同时要调整 db_block_lru_latches 参数

示例:修改或添加如下两个参数

db_writer_processes=4

db_block_lru_latches=8

a.2开异步IO,IBM这方面简单得多,hp则麻烦一些,可以与Hp工程师联系。

5.递归SQL

为了决定所有表和列在SQL中的名字是否正确,ORACLE必须通过数据字典检查有效性,这些信息通常在内存中(ROW CACHE)

找到了,但是如果在内存中找不到,那么就需要要进行递归SQL

6.在不改变源代码的前提下,可以通过两个参数调整来减少解析

cursor_sharing

session_cached_cursors

7.open cursor通常意义上仅是得到一个handle,但在某些情况下不是。

open cursor只是简单的定位首记录的指针

8.创建新表后,高水位为第5块

9.hash join

如果整个hash表能够填充到内存,将很好,否则就需要临时表来进行分配。

所以hash join常常对于两个不同大小的表进行连接,也可以在并行模式和反向连接中工作的很好

10.存储概要能够帮助从一个低版本移植到高版本,也可以将RULE移植到COST

11.OR常常可以利用union来实现索引的使用,当然ORACLE优化器有时也会自动进行转换

12.经常被> <等操作的列都要在可能的情况下使用索引

13.叶块包含了上一块和下一块的指针,所以允许对于索引过的列,使用order by和使用> <等操作进行利用

另外,对于索引扫描,即使没有任何记录,也会有3-4个IO,因为有纵向SCAN

对于大表,深度为4(1个都块,2级分支块和叶块),但是常常头块和分支块通常都会在内存,所以只会有1到2个物理读块

14.索引键值分离是很昂贵的,对于使用插入增量序列的值,可以避免索引分离

15.不能压缩分区索引和位图索引

16.唯一索引或者主键常常是很好的hash key,因为hash key不适合区域扫描

17.由于普通的B树索引,仍然消耗了很大磁盘空间,并且insert update delete也同样有很大的负载,所以考虑采用IOT,这样就没有表了,索引本身就是表,但是结构可是索引结构。主键将和所有列存储在叶节点,这样就造成了可能对索引结构的性能下降,所以可以通过设置including 来将某些不常用的大字段列放到益出段,对于想BOLB字段,可以采用disable storage in row使得分离不常查询的BOLB字段分离到别的段和表空间

18.query+current为逻辑读

autotrace没有每一步的row count,也不包括cpu or elapsed times

19.对于绑定变量与直方图的选择

条件 绑定 直方图

---------------------------- ------------------------------------------

搜索值不变不需要可能需要

range scan 不需要 需要

执行精确查找,

但有些值分布很少不需要需要

这个SQL执行很频繁,

期望快速执行需要 不需要

用户特定使用的

复杂的SQL可能不需要,因为最终需要

用户可能不喜欢

20.对于有null值的列要建立索引,那么最好使用default(where改成默认值),并设置not null

21.hash cluster能提高精确匹配,但不能在range scan中得到好处

22.你可以分离表中频繁被访问的和不频繁被访问的列到不同的段中或者表中,来提高性能,如果是IOT,可以使用INCLUDING

23. 范围搜索有时用PLSQL也是很好的,比如找到MAX和MIN值

24.优化位图索引,bitmap_merge_area_size 和 minimize records_per_block (该参数还可以优化pctfree)

minimize records_per_block能减少索引大小,但对全表扫描有副作用,他能造成块数的增长

25.reset 高水位

move table

exp + truncate table + imp

ctas

HWM 和 direct insert 有关系,direct insert 是从HWM 位置开始分配新的block 而不会使用HWM 以下的即使可以使用的block

26.如果pctfree很小,那么可以考虑增长initrans在ctas中,如果pctfree=0,那么可能一个事务提出请求时已经添满了块,那么象UPDATE等在多SESSION操作时就会失败。所以,一个表被频繁的表扫描,那么确保pctfree不要太高,特别是在没有UPDATE的情况下

27.多块读

在WINNT和2000中,多块读不能超过128K(32bit),所以最多是16个多块读在大多数UNIX文件系统中(不是RAW)是64K-1M

28.并行查询

满足下面条件:

有多个CPU

有剩余的CPU

数据分离在多个磁盘上

29.对于以响应时间为目标的,那么就要注意for update和order by操作,因为他们很难以first row方式优化

30.nested loop响应时间较好

32.hash_multiblock_io_count

该参数为影响的hash bucket数,这个值大,可以减少hash bucket数对于很大的表,可以通过减低hash_multiblock_io_count来增加bucket数来提高性能

33.尽管cluster是提供了优化的选项,但是在真实环境是很少用的,如果对于非常频繁的JOIN的可能有很大的提高性能,但通常是很伤害性能的

34.star hint可以将维度表进行笛卡儿积join,而事实表可以经过组合索引

35.不要期望使用多个位图索引来替换组合索引提高性能

36.对于自join的SQL语句使用PLSQL可能更有效

因为可以通过PLSQL的某些变量的处理方式,而不用多次访问同一个表

37.如果你有一个exists子查询,而且子查询没有能使用索引,那么你就可以使用semijoin来提高性能

38.一个in子查询,只是会执行一次,而exists子查询是对于父查询中的每行执行一次

如果IN子查询不能通过子查询中有索引来提高性能,那么exists能

如果exists不能通过父查询中的索引来提高性能,那么in可以

39.不要总是设置always_semi_join为true,那样的话,可能对于不需要semi join也会使用该连接

40.如果磁盘排序需要,每个排序进程都将分配自己的临时段在临时表空间中

当使用并行查询,如果有很高的CPU负载,那么将会有很大的风险

41.记住索引全扫描可能并不比全表扫描一定有效,因为他不能想全表扫描那样可以利用多块读,而且还可以采用并行

42.并行进程能够将SQL执行分散成多个任务

每个任务通过使用不同的CPU会有对应的从进程去并行处理

(一个进程是一个拥有自己内存的执行单元,一个线程是在进程内的不同线程共享内存的执行单元)

43.并行执行只是在全表处理或者分区及在分区表中执行本地索引时用到

如:全表扫描

rebuild index

update (全表或分区表)

insert的并行子查询

本地索引使用

批量插入,象SQLLDR

创建临时表

比如我们执行

select /*+ parallel(c1 ,2) */

con...

...

from customers c1

order by ...

process a process b

fetch rows fromfetch rows from

customers customers

||

||

process c process d

sort rows(a-k) sort rows(l-z)

combine rows

|

return result set

这里我们看到这个进程分散成4个进程,排序中各负责a-k和l-z,这样就可以并行处理

我们在分区表中,也可以用一个并行从进程对应一个分区表

如果你的并行度是3,那么你可能就比普通的执行速度提高3倍

注意在单CPU下,如果使用并行,那么就可能造成性能下降,而且也要设置合适的并行度

并行适合对于长时间运行的语句

在oltp可能并不适合使用并行处理,因为事务并发比较多,每个用户都要使用CPU,CPU的负载本来就比较高

所以并行处理还是比较适合olap中的批量导入,sqlldr,mis report和oltp中rebuild index

可以通过sar -u看cpu使用情况

44.并行度的设置

ORACLE通常设置两个并行处理进程集,如果语句有scan、group by 和order by,那么需要3个并行处理集,但是

由于scan并行进程已经用完,所以可以重用。

select /*+ parallel(s,2) */ customer_id,sum(sale_value)

from sales s

group by customer_id

order by 2

|-----scan------||--sort group by--| |--sort order by --这里被重用了|

parallel process 1 parallel process 3parallel process 1

sales

table

parallel process 2 parallel process 4parallel process 2

注意:并行从进程不会多于并行度的2倍

比如前面假设并行度为2,那么2*2=4个从进程

相关的参数

parallel_threads_per_cpu

这个是系统默认的并行度,如果建表的属性设置了并行度,那么在使用该表全表扫描时使用并行方式

如果没有指定,但是在SQL语句中加了并行提示,在没有写并行度的情况下使用系统有个默认并行度

使用v$pq_tqstat视图连接并行计划的细节

在一些复杂的并行SQL语句,确保所有重要的步骤在并行下执行很重要,这样不至于由于某一步变成串行,而使并行的优势丢失

45.如果有not in到自己的查询,那么使用antijoin可以改善性能,并能使用并行

46.union可以使用并行

minux和intersect不能使用并行

47.当位图索引被更新时,会产生全表行级锁,所以在oltp强烈建议不要使用

48.如果删除增加了系统的负载,那么可以采用逻辑删除的方式,比如设置一个删除列,更新该列

49.直接sqlldr

这种模型可以允许将数据块构件在内存中,并且直接插入数据到数据文件

而通常模型是要经过buffer cache in sga 和dbwr

并且直接创建新的块,就算free space存在也不使用

如果是分区表,那么能够插入数据到高水位以上的取

如果是非分区表,那么新的区必须生成,那么如果有2个并行度,就必须先新建2个新的分区。所以直接sqlldr可能影响全表扫描的性能

另外他也不支持外键参考,约束,触发器,复制,IOT,LOB等

当内存buffer cache产生冲突或者数据库写产生瓶径,那么对于传统的INSERT就可能造成等待,可以考虑直接LOAD

如果支持异步IO,那么直接模式可以同时插入到多个数据文件

插入可以并行的运行在分区或未分区的表,但是UPDATE和DELETE只能运行在分区表

50.提交频率要尽量的少

51.分区索引也可以在非分区表中

52.不要建立全局位图索引,也不要建立分区位图索引和cluster table

53.hash 分区的个数是2的乘方

54.snapshot存在快照日志,而只要数据库有任何变动,都会记录到快照日志中

有两种刷新方法

fast refresh 和 complete refreshes

如果源表有很频繁的更新,那么fast refresh 可能没有complete refreshes更有效

如果变动比较小,对快照日志的负载也较小,就可以使用fast refresh

批量load时,可以先drop snapshot,然后load,create snapshot

55.物化视图

基于事务刷新on commit

基于时间间隔on demand

56.触发器

如果能使用after触发器的尽量用after,因为before,触发器执行前他会先锁住触发器表的行

57.java提供更好的算术运算

plsql偏向于数据库敏感类型的,比如数据库的类型操作啊,如果是java,那么由于类型不匹配而增加了转换工作

58.sequence 中order 主要在ops和rac中使用

59.如果SQL语句中有系统视图,那么由于系统视图没有统计信息,所以优化程序不知道执行的驱动顺序,所以要使用象ordered这样的提示

60.natural key是由多个有意义的字段组成

artificial key通常由序列号生成,他不需要更新,减少了锁冲突,而且索引也较小,是比较好的选择

63.异步IO对于WIN总是有效,而UNIX常常使用RAW设备情况下使用异步IO

64.对于多表联接的优化

1).explain plan for

select ....;

set pagesize 0

set linesize 130

select * from table(dbms_xplan.display);

2).看看评估的结果,查看多表连接的方式

[1] [2] 下一页

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有