仓酷云

标题: MSSQL编程：数据库功能剖析及调剂一例 [打印本页]

作者: 仓酷云 时间: 2015-1-16 22:34
标题: MSSQL编程：数据库功能剖析及调剂一例
修复过程包含最多4个阶段，在下面描述。在你开始前，你应该cd到数据库目录和检查表文件的权限，确保他们可被运行mysqld的Unix用户读取(和你，因为你需要存取你正在检查的文件)。如果它拒绝你修改文件，他们也必须是可被你写入的。数据|数据库|功能妨碍征象
2004年6月8日上午10:00,内蒙古巴盟网通用户反应在OSS体系界面“话单查询”里查询单个用户五天的话单出格慢，查询很长工夫无了局。

比方：在OSS体系界面“综合查询”内点击“免费”-〉“话单查询”，键进“用户号码，肇端工夫：2004-01-0100：00：00，停止工夫：2004-06-0123：00：00”，点击查询后，IE进度条迟缓，很长工夫不前往了局。
妨碍剖析经由剖析，此征象和数据库的功能有关，次要是数据库初始化参数调剂分歧理酿成的功能低下。详细剖析步骤以下：
1．起首查询话单表的索引是不是生效，由于生效的索引会带来差的SQL查询效力。

SQL>selectINDEX_NAME,statusfromUSER_IND_PARTITIONSwherestatus!=USABLE;

norowsselected.

了局申明没有生效的话单表索引。

2．用top命令看到可用物理内存很低，只剩下100M，有大批的SWAP区内存正在利用，ORACLE单个会话占用的内存良多，经检察ORACLE初始化参数shared_pool_size的值设置的太高，应从头调剂。

top的了局:
lastpid:4565;loadaverages:0.15,0.20,0.20
10:09:56

170processes:169sleeping,1oncpu

CPUstates:84.9%idle,1.6%user,1.1%kernel,12.4%iowait,0.0%swap

Memory:4096Mreal,100Mfree,1343Mswapinuse,6851Mswapfree

PIDUSERNAMETHRPRINICESIZERESSTATETIMECPUCOMMAND

10459oracle15901978M1953Msleep0:530.79%oracle

2258oracle11001976M1951Msleep116:570.65%oracle

25639oracle15801975M1949Msleep1:560.27%oracle

1948oracle15801976M1948Msleep3:340.18%oracle

4002wacos64749616K2344Ksleep27:260.18%cdr_backup

2271oracle15901975M1947Msleep15:130.16%oracle

1958oracle14801976M1949Msleep2:260.13%oracle

1928oracle15801976M1951Msleep4:280.12%oracle

1926oracle15801976M1949Msleep2:060.12%oracle

1956oracle15801976M1949Msleep2:230.11%oracle

1952oracle15901976M1949Msleep2:190.10%oracle

403root102104896K4608Ksleep16:320.09%picld

1954oracle14801976M1949Msleep2:040.08%oracle

2189oracle15801976M1949Msleep15:510.08%oracle

3．为了进一步剖析ORACLE的功能，用ORACLE自带的诊断工具statspack做功能快照剖析，统计时段为1小时，工夫从下战书17：00-18：00之间。这段工夫营业对照忙碌，选择在此时段内对全部体系举行功能剖析，可以失掉加倍正确的信息。

安装statspack功能剖析工具:

SQL>connectinternal

SQL>altersystemsettimed_statistics=true;(搜集操纵体系的计时信息)

SQL>@?/rdbms/admin/spcreate.sql

SQL>executestatspack.snap(17:00的时分运转一次)

SQL>executestatspack.snap(18:00的时分运转一次)

SQL>@?/rdbms/admin/spreport(发生功能剖析呈报)

截取呈报的部份内容以下:

STATSPACKreportfor

DBNameDBIdInstanceInstNumReleaseOPSHost

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

ORCL1000277484ORCL18.1.7.3.0NObm_db1

SnapIdSnapTimeSessions

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

BeginSnap:108-Jun-0417:00:15116

EndSnap:208-Jun-0418:00:40116

Elapsed:60.42(mins)

CacheSizes

~~~~~~~~~~~

db_block_buffers:180000log_buffer:

8192000

db_block_size:8192shared_pool_size:

314572800

LoadProfile

~~~~~~~~~~~~PerSecondPerTransaction

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

Redosize:11,005.012,280.39

Logicalreads:65,704.2113,614.83

Blockchanges:67.9614.08

Physicalreads:1,392.89288.63

Physicalwrites:11.612.40

Usercalls:172.6335.77

Parses:29.116.03

Hardparses:0.010.00

Sorts:7.811.62

Logons:0.140.03

Executes:101.4421.02

Transactions:4.83

%BlockschangedperRead:0.10RecursiveCall%:41.29

Rollbackpertransaction%:0.28RowsperSort:25.55

InstanceEfficiencyPercentages(Target100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

BufferNowait%:100.00RedoNoWait%:100.00

BufferHit%:97.88In-memorySort%:100.00

LibraryHit%:99.98SoftParse%:99.96

ExecutetoParse%:71.30LatchHit%:99.99

ParseCPUtoParseElapsd%:62.24%Non-ParseCPU:99.99

SharedPoolStatisticsBeginEnd

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

MemoryUsage%:24.1524.44

%SQLwithexecutions>1:75.0476.95

%MemoryforSQLw/exec>1:75.4979.90

Top5WaitEvents

~~~~~~~~~~~~~~~~~Wait%Total

EventWaitsTime(cs)WtTime

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

dbfilesequentialread5,030,075389,07186.37

logfilesync17,47021,1874.70

logfileparallelwrite17,64018,6114.13

dbfileparallelwrite1,85314,9303.31

dbfilescatteredread3,1492,297.51

对呈报剖析后发明有一些分歧理的初始化参数必要调剂，倡议以下调剂：

1．呈报中发明全表扫描的语句出格多，因而倡议程序中只管制止利用全表扫描，

削减IO守候，从而加速语句的实行速率。

相似以下语句必要优化：

SQL>selectcount(*)astotalcountfromLOCALUSAGEwherese

rviceid=:"SYS_B_0"andstarttime>=to_date(:"SYS_B_1",:"SYS_B_2")

andstarttime<=to_date(:"SYS_B_3",:"SYS_B_4")and(LOCALROAMI

NGCHARGE>:"SYS_B_5"orLocalCharge>:"SYS_B_6"orUrbanCharge

>:"SYS_B_7"orruralcharge>:"SYS_B_8")；

2．调剂db_file_multiblock_read_count=16

这个参数指定一个完整一连扫描的一次I/O操纵过程当中读取的块的最年夜数目。它的增添对IO是有改良的，出格是在做fulltablescan的时分，能够削减IO的次数。

3．调剂db_block_lru_latches=2

这个参数指定LRU闩锁集数目的下限。LRU锁的数目是在Oracle数据库外部用来办理数据库缓冲的，它严峻依附于服务器上CPU的数目,这个值一般设置为服务器上cpu_count的一半，增年夜这个值有益于进步磁盘的I/O功能。

4．调剂session_cached_cursors=200

这个参数指定要高速缓存的会话游标的数目，对统一SQL语句举行屡次语法剖析后，它的会话游标将被移到该会话的游标高速缓存中。增年夜这个值能够延长语法剖析的工夫，由于游标被高速缓存，无需被从头翻开。

5．调剂log_buffer=1048576

参数log_buffer指定在LGWR将重做日记缓冲区里的内容写进重做日记文件之前，用于缓存这些条目标内存量。这个参数以字节为单元，同时受cpu_count的影响,log_buffer假如被设置得太高（比方，年夜于1MB），这会引发功能成绩，由于年夜容量的了局会使得写进同步举行（比方，日记同步守候事务十分高）。

6.调剂db_block_buffers=200000shared_pool_size=262144000

依照杭州的计划，Oracle终极运转起来占用近1/2的物理内存。个中最次要的两个参数为：

db_block_buffers：它的设置准绳是，终极数据块缓存占有1/3的内存。

Shared_pool_size：它的设置准绳是，基础把持在200-500M摆布。

7．从呈报中发明体系守候最严峻的五个事务为:dbfilesequentialread，logfilesync，logfileparallelwrite，dbfileparallelwrite和dbfilescatteredread.

(1)关于dbfilesequentialread守候事务，一样平常成绩呈现在读索引上，倡议将wacos表空间和wacos索引表空间分隔存储在分歧的物理卷下，以进步磁盘的I/O功能。

(2)关于dbfilescatteredread守候事务，倡议程序中只管制止利用全表扫描的语句，大概能够增年夜db_file_multiblock_read_count的值，进步全表扫描一次读取数据块的速率，削减磁盘I/O。

(3)关于dbfileparallelwrite守候事务，申明DBWR历程正守候把缓冲区的内容并行写进数据文件中往,守候将一向延续到一切的I/O全体完成。倡议增年夜初始化参数中的db_writer_processes的值，能够增年夜到4。
(4)关于logfilesync守候事务，申明任什么时候候一个事物提交时，它将关照LGWR将LOG_BUFFER写进日记文件，假如此部分占用工夫较长，应削减COMMIT的次数，倡议将重做日记放到较快的磁盘长进行存储。
(5)关于logfileparallelwrite守候事务，和下面一样倡议将重做日记放到较快的磁盘长进行存储。

妨碍处置
调剂initORCL.ora里分歧理的参数，详细调剂为：

process=200

log_buffer=1048576

session_cached_cursors=200

db_block_lru_latches=2

shared_pool_size=262144000

db_block_buffers=200000

sort_area_size=6553600

sort_area_retained_size=6553600

db_file_multiblock_read_count=16

处置了局
调剂完重启DB后，发明查询统统一般，很快就前往了却果。

总结数据库里初始化参数设置分歧理,内存充裕太少,招致数据库运转利用大批的swap空间，数据库功能很差，招致经由过程OSS界面查询话单很慢。这时候必要经由过程调剂数据库初始化参数办理该成绩。从功能方面思索，数据库服务器最好能充裕300－500M以上的内存。
使为了数据安全，我们搭建了主从。但实时主从备份只能防止硬件问题，比如主库的硬盘损坏。但对于误操作，则无能为力。比如在主库误删一张表，或者一个update语句没有指定where条件，导致全表被更新。

作者: 飘灵儿 时间: 2015-1-19 17:26
XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

作者: 深爱那片海 时间: 2015-1-25 15:42
外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

作者: 莫相离 时间: 2015-2-2 23:04
但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

作者: 再现理想 时间: 2015-2-8 19:27
这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？

作者: 海妖 时间: 2015-2-25 23:15
如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

作者: 愤怒的大鸟 时间: 2015-3-8 10:18
发几份SQL课件，以飨阅者

作者: 若相依 时间: 2015-3-15 22:06
以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了

作者: 老尸 时间: 2015-3-22 05:39
可以动态传入参数，省却了动态SQL的拼写。

欢迎光临仓酷云 (http://ckuyun.com/)