仓酷云

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 940|回复: 8
打印 上一主题 下一主题

[学习教程] MSSQL编程:数据库功能剖析及调剂一例

[复制链接]
跳转到指定楼层
楼主
发表于 2015-1-16 22:34:45 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
修复过程包含最多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:14 | 只看该作者
XML字段类型更好的解决了XML数据的操作。XQuery确实不错,但是个人对其没好感。(CSDN的开发者应该是相当的熟了!)
深爱那片海 该用户已被删除
板凳
发表于 2015-1-25 15:42:53 | 只看该作者
外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键,都是通过程序来控制父子数据的完整性。
莫相离 该用户已被删除
地板
发表于 2015-2-2 23:04:04 | 只看该作者
但是随着数据量的增大,这种成本差距会逐渐减小,趋于相等。(500万数量级只相差10%左右)
再现理想 该用户已被删除
5#
发表于 2015-2-8 19:27:04 | 只看该作者
这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据,是否会出现大规模的碎片?
海妖 该用户已被删除
6#
发表于 2015-2-25 23:15:22 | 只看该作者
如果我们从集合论(关系代数)的角度来看,一张数据库的表就是一组数据元的关系,而每个SQL语句会改变一种或数种关系,从而产生出新的数据元的关系(即产生新的表)。
愤怒的大鸟 该用户已被删除
7#
发表于 2015-3-8 10:18:02 | 只看该作者
发几份SQL课件,以飨阅者
若相依 该用户已被删除
8#
发表于 2015-3-15 22:06:12 | 只看该作者
以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多,但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了,往往会有很多用户不会用了
老尸 该用户已被删除
9#
发表于 2015-3-22 05:39:52 | 只看该作者
可以动态传入参数,省却了动态SQL的拼写。
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|仓酷云 鄂ICP备14007578号-2

GMT+8, 2024-12-23 04:05

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表