MSSQL网页设计数据库功能反省引导计划 － Part II

优化的SQL查询算法，有效地提高查询速度数据|数据库|功能
存储功能评价

在存储功能评价的时分，我们利用磁盘功能指数（DPI,DiskPerformanceIndex），下表列出了DPI中的各项指数，这个评分体系其实不意味着对磁盘的利用和分派的全方位评价，而只是代表一个晴雨表，反应以后磁盘的利用和分派上是不是存在必要改善大概注重的中央。



MPI指数

分类

所需品级

最高分

调剂表和索引

是

30

表的行毗连成绩

无

30

分别关头的Oracle文件

是

30

回滚段的均衡



30

一时段的均衡



30

利用最多的前10个SQL的磁盘利用率

<5%

60

是不是已调剂利用磁盘最多的前25个SQL

是

40

MPI指数

总分

250



1．调剂表和索引

因为表和索引的数据块一般是被同时读取的，以是应当只管将表和其相干联的索引安排在分歧的磁盘上，以便削减文件的I/O抵触。



反省办法：

selecti.index_name,t.table_name,t.tablespace_name
fromuser_tablest,user_indexesi
wheret.table_name=i.table_name
andt.tablespace_name=i.tablespace_name;



前往了局是创立在不异表空间中的表和相干联的索引。倡议创立新的表空间用于专门寄存索引，并将以后的索引rebuild到新创立的表空间中。

alterindexidx_namerebuildtablespacets_name;

评价原则：

品级

分数

表和索引放在统一磁盘上

0

存储利用了磁盘阵列，没有进一步伐整

20

存储利用了磁盘阵列，关于RAID范例已作过调剂

30

表和索引已计划在分歧磁盘上

30



2．表的行链接成绩

当更新一张表，而数据块中又没有充足的残剩空间来包容所作的修正时，就会产生“行链接”征象，该纪录被链接到别的一个有充足空间的数据块中，也就是一笔记录超过了多个数据块，如许在读取该纪录的时分就会损耗更多的I/O，当数据库中有大批的“行链接”征象存在时，数据库的全体功能就会下落。



反省办法：

sqlplus/nolog

connectapp_user/password

SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql

SQL>analyzetable<table_name>listchainedrows;

SQL>selectcount(*)chained_rows,table_name
fromchained_rows
groupbytable_name;



假如没有前往任何行，则暗示没有“行链接”征象。不然将依照已剖析过的表显现每张表中有几纪录呈现了“行链接”征象。

“行链接”征象的发生跟PCTFREE参数的设置不妥有干系。PCTFREE值默许为10%，假如体系中存在大批行链接，暗示这个参数指定的块保存空间太小，不敷以包容块中一切纪录的更新操纵。此时应当增年夜响应表的PCTFREE值。



评价原则：

品级

分数

存外行链接征象

0

不存外行链接征象

30



3．分别关头的Oracle文件

不管是出于平安性的思索仍是功能的思索，都倡议将关头的Oracle文件散布在可用的自力磁盘上。

起首在毛病呈现以后，用来被恢复的数据文件和用来恢复的把持文件，重作日记文件，回档日记文件应当分别寄存。假如有大概，将以下各个关头文件散布在分歧的磁盘上。

体系表空间（SYSTEM），一时表空间（TEMP），回滚表空间（UNDO），联机重作日记文件（REDO）和回档日记文件（ARCH），常常会见的用户表空间，常常会见的用户索引表空间，操纵体系盘，$ORACL_EBASE中的关头Oracle软件文件。

最少联机重作日记文件（REDO）和回档日记文件（ARCH）应当跟别的文件寄存在分歧的磁盘上，而且因为日记文件的年夜部分工夫为只写属性，以是必要思索RAID5在写方面的弱势，只管不要将日记文件寄存在RAID5的阵列组上。



反省办法：

selectfile_name,tablespace_name,bytes
fromdba_data_files
unionall
selectfile_name,tablespace_name,bytes
fromdba_temp_files
unionall
selectnamefile_name,NULL,NULL
fromv$controlfile
unionall
selectmemberfile_name,to_char(a.group#)tablespace_name,b.bytesbytes
fromv$logfilea,v$logb
wherea.group#=b.group#
unionall(selectvaluefile_name,NULL,NULL
fromv$parameter
wherenamelikelog_archive_dest_%
andvalueisnotnull
minus
selectvaluefile_name,NULL,NULL
fromv$parameter
wherenamelikelog_archive_dest_state%);



前往数据库中一切关头文件存储的地位，由DBA和SA考查前往的了局，确认已关于关头文件的存储地位作过切合实践情形的调剂。



评价原则：

品级

分数

没有调剂，全体在单个磁盘上

0

没有调剂，全体在RAID上

20

已调剂

30



4．回滚段的均衡

在Oracle9i和Oracle9i之前假如没有利用回滚段主动办理，那末关于回滚段的功能仍旧是必要监控而且调剂的。



反省是不是利用了回滚段主动办理：

selectname,valuefromv$parameterwherenamelike%undo_%;



假如前往了局中undo_management的值是AUTO，则暗示利用了回滚段主动办理，同时undo_tablespace值显现了主动办理利用的回滚表空间，undo_retention值显现了在回滚表空间中保存回滚数据的时限，以秒为单元。

注重：假如undo_management的值是AUTO可是undo_tablespace没有设置响应的值，那末就会利用SYSTEM表空间中的SYSTEM回滚段，这个是相对应当制止的征象。



假如没有利用回滚段主动办理，那末必要监控用户利用回滚段的频度，准绳上以为不该该有凌驾1个用户同时利用1个回滚段。



反省办法：

selecta.name,
b.extents,
b.rssize,
b.xacts,
b.waits,
b.gets,
optsize,
status
fromv$rollnamea,v$rollstatb
wherea.usn=b.usn;



反省输入了局，关于一切回滚段而言，假如xacts（举动事件）和waits（段优等待）常常超越1，那末就标明必要增添回滚段数量，以免大概呈现的争用。

增添回滚段的办法：

createrollbacksegmentrs_nametablespaceRBSstorage(initial1Mnext2M);
alterrollbacksegmentrs_nameonline;



假如利用了回滚段主动办理，那末能够从v$undostat,v$rollstat,dba_undo_extents等视图中查询以后回滚段的利用和分派情形。



评价原则：

品级

分数

有回滚段守候征象

0

无回滚段守候征象

30

利用了回滚段主动办理

30



5．一时段的均衡

现在始化参数中界说的SORT_AREA_SIZE巨细没法满意排序请求的空间，就会利用一时表空间中的一时段举行排序，磁盘排序比内存排序要慢100-10000倍，以是只管削减磁盘排序是功能调剂事情的一个主要部分。

大概引发排序的操纵有createindex,distinct,orderby,groupby等。



反省办法：

selectname,valuefromv$sysstatwherenamelike%sorts%;



前往了局中的sorts(memory)暗示内存排序，而sorts(disk)则暗示磁盘排序，假如存在大批的磁盘排序，则标明我们必要增添SORT_AREA_SIZE大概HASH_AREA_SIZE等排序区的巨细，大概必要反省今朝体系中损耗大批磁盘的SQL是不是已经由调剂（反省前25位损耗磁盘的SQL在前面部分将提到）。



反省利用磁盘排序的会话信息，能够定位实行了大批磁盘排序的会话。

反省办法：

selectb.name,a.sid,a.value
fromv$sesstata,v$statnameb
wherea.STATISTIC#=b.STATISTIC#
andb.name=sorts(disk)
anda.value>0
orderbya.valuedesc;

假如有大概我们应当将一时表空间中的多个一时数据文件散布在分歧的磁盘上，以削减排序时大概会发生的磁盘抵触。

在Oracle9i中，我们能够设置PGA_AGGREGATE_SIZE初始化参数来指定一切会话将利用的PGA巨细，同时也必需设置WORKAREA_SIZE_POLICY参数为AUTO。别的具体信息见内存功能评价中“4。内存中的排序”部分。



评价原则：

品级

分数

关于存在的磁盘排序没有评价

0

已就存在的磁盘排序举行过调剂

30



6．最华侈磁盘读操纵的前10个语句占一切语句的比例

一般一个没有优化体系中，10个最经常使用的SQL语句的会见量会占到全部体系中磁盘读操纵的50%以上。这些SQL是最必要举行优化的部分，也是优化事情中优先级很高的部分。一般我们的优化方针是将这些SQL的磁盘读操纵百分比下降到5-19%。



反省办法：

selectsum(pct_bufgets)
from(selectrank()over(orderbydisk_readsdesc)asrank_bufgets,
to_char(100*ratio_to_report(disk_reads)over(),999.99)pct_bufgets
fromv$sqlarea)
whererank_bufgets<11;



评价原则：

品级

分数

<5%

60

5-19%

50

20-25%

30

>25%

0



7．调剂前25个最华侈磁盘读操纵的语句

在没有调剂的情形下，尽年夜多半体系中，会见量占前25位的语句的磁盘读操纵将占用全部体系一切磁盘读操纵的75%，对这部分语句举行调剂是相当主要的。这部分剧本用于取得会见量占前25位的SQL语句。输入了局中的Exec暗示该SQL被实行的次数。



反省办法：

setserveroutputonsize1000000
declare
executionnumber;
top25number;
text1varchar2(4000);
xnumber;
len1number;
cursorc1is
selectexecutions,disk_reads,substr(sql_text,1,4000)
fromv$sqlarea
orderbydisk_readsdesc;
begin
dbms_output.put_line(Exec||||Reads||||Text);
dbms_output.put_line(-----||||--------||||
-------------);
openc1;
foriin1..25loop
fetchc1
intoexecution,top25,text1;
dbms_output.put_line(rpad(to_char(execution),5)||||
rpad(to_char(top25),8)||||
substr(text1,1,66));
len1:=length(text1);
x:=66;
whilelen1>x-1loop
dbms_output.put_line(-||substr(text1,x,66));
x:=x+66;
endloop;
endloop;
end;
/



评价原则：

本部分没有详细的评价原则，必要开辟职员大概DBA往确认在这25个SQL中属于使用体系的语句是不是都已作过调优。



8．别的存储相干的调剂

1)字典办理表空间中的Extent总数不凌驾4096

反省办法：

selecta.tablespace_name,sum(a.extents)
fromdba_segmentsa,dba_tablespacesb
wherea.tablespace_name=b.tablespace_name
andb.extent_management=DICTIONARY
groupbya.tablespace_name
orderbysum(a.extents);



反省输入了局，假如显现某个表空间中的extents总数凌驾了4096，那末必要扩展这个表空间的extent巨细，过量的extent关于DMT的空间办理有负面影响。



2)当地办理表空间中单个Segement的Extent数不凌驾1024

反省办法：

selectsegment_name,segment_type,extents,bytes,b.tablespace_name
fromdba_segmentsa,dba_tablespacesb
wherea.tablespace_name=b.tablespace_name
andb.extent_management=LOCAL
anda.extents>1024;



反省输入了局，前往的纪录都是单个段的区间年夜于1024的工具，关于这些工具，应当创立一个独自的具有更年夜extent巨细的表空间，然后将这些工具move到新的表空间中往。



3)反省字典办理表空间的PCTINCREASE值是不是是0

为了表空间中的一切extent巨细不异，倡议表空间中的一切段都不要设置自力的storage参数。关于表空间的pctincrease参数，倡议设置为0，同时应当设置minextents参数，包管初始分派充足的空间给新创立的工具。

关于LMT表空间，storage参数中的pctincrease和next参数均有效，倡议设置得当的uniform参数办理表空间的extent分派。



4)思索利用分区来制止磁盘争用

分区表在办理的便利性和功能的进步上都有较强的有用性，乃至能够以为分区是进步与年夜型表有关的功能的最好办法。经由过程会见一个表大概索引的较小片断，而不是会见全部表或索引，分区能够很好地进步效力。假如一个表大概索引的分区位于分歧的磁盘上，就更能够年夜年夜增添数据吞吐量，取得很好的数据库功能。

关于分区的利用，临时不在本文的叙说局限内，请参阅别的的分区文档。



5)关于分区表的非分区键索引是不是是全局分区索引

因为分区表的数据量一般对照伟大，以是假如在分区表的非分区键上创立索引，那末倡议创立为全局分区索引，如许可以更好地进步功能。注重：假如截断了一个分区的数据大概删除一个分区，那末必需rebuild这个分区表中的全体全局索引，不然这些全局索引将处于invalid形态，招致利用到这些索引的SQL语句失利。
提供用于管理、检查、优化数据库操作的管理工具。

对于微软系列的东西除了一遍遍尝试还真没有太好的办法

再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。

换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的

很多书籍啊，不过个人认为看书太慢，还不如自己学。多做实际的东西，就会遇到很多问题，网上搜下解决问题。不断重复这个过程，在配合sql的F1功能。

发几份SQL课件，以飨阅者