SQL性能调优基础教材.docx
- 文档编号:14478796
- 上传时间:2023-06-23
- 格式:DOCX
- 页数:63
- 大小:1.02MB
SQL性能调优基础教材.docx
《SQL性能调优基础教材.docx》由会员分享,可在线阅读,更多相关《SQL性能调优基础教材.docx(63页珍藏版)》请在冰点文库上搜索。
SQL性能调优基础教材
SQL性能调优基础教材
一、数据库体系结构
1.Oracle数据库和实例
数据库:
物理操作系统文件或磁盘的集合。
实例:
一组Oracle后台进程/线程以及一个共享内存区,这些内存由同一个计算机上运行的线程/进程所共享。
2.文件
参数文件
跟踪文件
警告文件
临时文件
控制文件
重做日志文件
密码文件
3.内存结构和进程
SGA
PGA
PMON
SMON
RECO
CKPT
DBWn
LGWR
ARCn
4.Redo和undo
Undo和redo的作用及如何协作
Undo(撤销信息)是oracle在undo段中记录的信息,用于取消或回滚事务。
Undo在数据库内存储在一组特殊的段中,叫undo段。
对于每个insert,oracle会完成一个delete,对每个delete,oracle会执行一个insert。
对于每个update,oracle会执行一个“反update”,或者执行;另一个upate将修改前的行放回去。
Redo(重做日志)是oracle在在线(或归档)重做日志文件中记录的信息,万一出现失败时可以利用这些数据来“重放”(或重做)事务。
Undo和redo如何协作
如何测量undo和redo--实验
为什么要测量undo和redo?
在进行大规模数据操作时,有可能会造成数据库慢甚至挂起。
如果事先能对日志进行测量,能够预防此类事件的发生。
本会话测量方法:
selectname,value
fromv$mystat,v$statname
wherev$mystat.statistic#=v$statname.statistic#
and(v$statname.name='redosize'or
v$statname.name='undochangevectorsize');
对一张表进行insert、update、delete操作,生成undo、redo大小排序
Delete>update>insert
Commit做了什么
如何减少产生redo,使用Nologging吗—实验
二、数据库逻辑和物理存储结构
1.目标
能让大家对oracle数据存储逻辑结构和物理结构有一个认识,研究数据库存储最小单元block。
围绕着block做一些实验,解决大家在日常工作中遇到的困惑。
2.概念
数据库逻辑结构为数据块(DataBlock)、数据扩展(Extent)、和段(Segment);物理结构为数据文件。
Block是最精细的数据存储粒度,一个数据块相当于磁盘上一段连续的物理存储空间,oracle每次访问数据的单位是block。
Extent是为存储数据而分配的一组连续的block。
Segment则是由一个或多个Extent。
一张表可以看做是一个段,一个索引可以做作是一个段。
查看段的分类selectdistinctsegment_typefromuser_segments。
数据逻辑上存储表空间(Tablespace)中,而物理上则存储于属于表空间的数据文件(datafile)中。
3.如何查看表和索引的大小
查看表dba_segments、user_segments
4.Block结构
数据块头:
包含了此数据块的概要信息,如块地址(blockaddress)及数据块所属的段的类型。
表目录区:
如果一张表在此数据块中存储了数据行,那么这张表的信息将被记录在数据块的表目录区中。
行目录区:
此区域包含数据块中存储的数据行的信息。
可用空间区:
在插入新数据行,或在更新数据行需要空间时,将使用此区域。
行数据区:
包含表和索引的实际数据。
一个数据行可以跨多个数据库。
5.Dumpblock(在内存中的结构)--实验
SQL>createtabletest(namevarchar2(10));
SQL>insertintotestvalues('中国');
SQL>insertintotestvalues('美国');
SQL>commit;
SQL>setserveroutputon
SQL>execshow_space1('TEST','auto','table');
TotalBlocks............................8
TotalBytes.............................65536
UnusedBlocks...........................0
UnusedBytes............................0
LastUsedExtFileId....................6
LastUsedExtBlockId...................124225
LastUsedBlock.........................8
LastUsedExtBlockId+LastUsedBlock-1=段中扩展最后一个块使用的blockId
124225+8-1=124232;
SQL>altersystemdumpdatafile6block124232;
***2013-03-0608:
28:
58.181
Startdumpdatablockstsn:
7file#:
6minblk124232maxblk124232
buffertsn:
7rdba:
0x0181e548(6/124232)
scn:
0x095a.e56f14ceseq:
0x01flg:
0x06tail:
0x14ce0601
frmt:
0x02chkval:
0x8092type:
0x06=transdata
Hexdumpofblock:
st=0,typ_found=1
Dumpofmemoryfrom0x08D52A00to0x08D54A00
8D52A000000A2060181E548E56F14CE0601095A[....H.....o.Z...]
8D52A100000809200000001000180A2E56F14C6[..............o.]
8D52A200000095A003200020181E54100020002[Z.....2.A.......]
8D52A30000072BB0081FA0600261E1C00002002[.r........&....]
8D52A40E56F14CE000000000000000000000000[..o.............]
8D52A5000000000000000000000000000000000[................]
8D52A6000000000000201000016FFFF1F701F88[..............p.]
8D52A7000001F701F90000200001F8800000000[p...............]
8D52A8000000000000000000000000000000000[................]
Repeat501times
8D549E00000000000000000000000000401012C[............,...]
8D549F0FAB9C0C30401012CFAB9D0D614CE0601[....,...........]
Blockheaderdump:
0x0181e548
ObjectidonBlock?
Y
seg/obj:
0x180a2csc:
0x95a.e56f14c6itc:
2flg:
Etyp:
1-DATA
brn:
0bdba:
0x181e541ver:
0x01opc:
0
inc:
0exflg:
0
ItlXidUbaFlagLckScn/Fsc
0x010x0002.002.000072bb0x0081fa06.1e1c.26--U-2fsc0x0000.e56f14ce
0x020x0000.000.000000000x00000000.0000.00----0fsc0x0000.00000000
data_block_dump,dataheaderat0x8d52a64
===============
tsiz:
0x1f98
hsiz:
0x16
pbl:
0x08d52a64
bdba:
0x0181e548
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f70
tosp=0x1f70
0xe:
pti[0]nrow=2offs=0
0x12:
pri[0]offs=0x1f90
0x14:
pri[1]offs=0x1f88
block_row_dump:
tab0,row0,@0x1f90
tl:
8fb:
--H-FL--lb:
0x1cc:
1
col0:
[4]d6d0b9fa
tab0,row1,@0x1f88
tl:
8fb:
--H-FL--lb:
0x1cc:
1
col0:
[4]c3c0b9fa
end_of_block_dump
Enddumpdatablockstsn:
7file#:
6minblk124232maxblk124232
seg/obj:
0x180a2
selectpkg_number_trans.f_hex_to_dec('180a2')fromdual;
select*fromuser_objectswhereobject_id=98466;
解析字段内容
selectto_number('d6d0','xxxx')fromdual;--54992
selectchr(54992)fromdual;--中
selectto_number('b9fa','xxxx')fromdual;--47610
selectchr(47610)fromdual;--国
6.Oracle如何实现行锁--实验
通过数据库的物理结构实现行锁。
SQL>insertintotestvalues('韩国');
SQL>insertintotestvalues('朝鲜');
SQL>insertintotestvalues('越南');
SQL>commit;
SQL>altersystemdumpdatafile6block124232;
第一次dump:
ItlXidUbaFlagLckScn/Fsc
0x010x0007.002.0000485b0x00803738.1615.20--U-2fsc0x0000.e56fe278
0x020x0008.021.000071720x00801255.1683.1d--U-2fsc0x0000.e56fe26dfsc
block_row_dump:
tab0,row0,@0x1f90
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]d6d0b9fa
tab0,row1,@0x1f88
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]c3c0b9fa
tab0,row2,@0x1f80
tl:
8fb:
--H-FL--lb:
0x0cc:
1
col0:
[4]baabb9fa
tab0,row3,@0x1f78
tl:
8fb:
--H-FL--lb:
0x1cc:
1
col0:
[4]b3afcfca
tab0,row4,@0x1f70
tl:
8fb:
--H-FL--lb:
0x1cc:
1
col0:
[4]d4bdc4cf
end_of_block_dump
SQL>select*fromtestwherename='中国'forupdate;
SQL>altersystemdumpdatafile6block124232;
第二次dump:
ItlXidUbaFlagLckScn/Fsc
0x010x0004.02d.000048690x00800ff7.1290.1fC---0scn0x095a.e56fe16c
0x020x0008.021.000071720x00801255.1683.1c----1fsc0x0000.00000000
block_row_dump:
tab0,row0,@0x1f90
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]d6d0b9fa
tab0,row1,@0x1f88
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]c3c0b9fa
tab0,row2,@0x1f80
tl:
8fb:
--H-FL--lb:
0x0cc:
1
col0:
[4]baabb9fa
tab0,row3,@0x1f78
tl:
8fb:
--H-FL--lb:
0x1cc:
1
col0:
[4]b3afcfca
tab0,row4,@0x1f70
tl:
8fb:
--H-FL--lb:
0x1cc:
1
col0:
[4]d4bdc4cf
end_of_block_dump
SQL>select*fromtestwherename='美国'forupdate;
SQL>altersystemdumpdatafile6block124232;
ItlXidUbaFlagLckScn/Fsc
0x010x0007.002.0000485b0x00803738.1615.20C---0scn0x095a.e56fe278
0x020x0003.00d.000060b00x00809898.176b.0e----2fsc0x0000.00000000
block_row_dump:
tab0,row0,@0x1f90
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]d6d0b9fa
tab0,row1,@0x1f88
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]c3c0b9fa
tab0,row2,@0x1f80
tl:
8fb:
--H-FL--lb:
0x0cc:
1
col0:
[4]baabb9fa
tab0,row3,@0x1f78
tl:
8fb:
--H-FL--lb:
0x0cc:
1
col0:
[4]b3afcfca
tab0,row4,@0x1f70
tl:
8fb:
--H-FL--lb:
0x0cc:
1
col0:
[4]d4bdc4cf
end_of_block_dump
重新开一个session:
SQL>select*fromtestwherename='韩国'forupdate;
ItlXidUbaFlagLckScn/Fsc
0x010x0006.015.0000600e0x008037b9.18b7.1d----1fsc0x0000.00000000
0x020x0003.00d.000060b00x00809898.176b.0e----2fsc0x0000.00000000
block_row_dump:
tab0,row0,@0x1f90
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]d6d0b9fa
tab0,row1,@0x1f88
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]c3c0b9fa
tab0,row2,@0x1f80
tl:
8fb:
--H-FL--lb:
0x1cc:
1
col0:
[4]baabb9fa
tab0,row3,@0x1f78
tl:
8fb:
--H-FL--lb:
0x0cc:
1
col0:
[4]b3afcfca
tab0,row4,@0x1f70
tl:
8fb:
--H-FL--lb:
0x0cc:
1
col0:
[4]d4bdc4cf
end_of_block_dump
重新开一个session:
SQL>select*fromtestwherename='韩国'forupdate;
ItlXidUbaFlagLckScn/Fsc
0x010x0006.015.0000600e0x008037b9.18b7.1d----1fsc0x0000.00000000
0x020x0003.00d.000060b00x00809898.176b.0e----2fsc0x0000.00000000
0x030x0009.02c.00005fda0x00803853.1868.04----1fsc0x0000.00000000
block_row_dump:
tab0,row0,@0x1f78
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]d6d0b9fa
tab0,row1,@0x1f70
tl:
8fb:
--H-FL--lb:
0x2cc:
1
col0:
[4]c3c0b9fa
tab0,row2,@0x1f68
tl:
8fb:
--H-FL--lb:
0x1cc:
1
col0:
[4]baabb9fa
tab0,row3,@0x1f60
tl:
8fb:
--H-FL--lb:
0x3cc:
1
col0:
[4]b3afcfca
tab0,row4,@0x1f58
tl:
8fb:
--H-FL--lb:
0x0cc:
1
col0:
[4]d4bdc4cf
end_of_block_dump
7.Block参数PCTFREE、PCTUSED
PCTFREE:
为一个块保留的空间百分比,表示数据块在什么情况下可以被insert,默认是10,表示当数据块的可用空间低于10%后,就不可以被insert了,只能被用于update;即:
当使用一个block时,在达到pctfree之前,该block是一直可以被插入的,这个时候处在上升期。
PCTUSED:
是指当块里的数据低于多少百分比时,又可以重新被insert,一般默认是40,即40%,即:
当数据低于40%时,又可以写入新的数据,这个时候处在下降期。
注意:
如果表空间上启用了ASSM,在建立表的时候,只能指定PCTFREE,否则可用指定PCTFREE和PCTUSED。
8.行迁移和行链接
有两种情况会导致表中某行数据过大,一个数据块无法容纳。
第一种情况:
当一行数据被插入时一个数据块就无法容纳,在这种情况下oracle将这行数据存储在段内的一个数据块链中。
在插入数据量大的行时常会发生行链接(rowchaining)。
例如一个包含数据类型为long或longraw列的数据行,此时行链接不可避免。
第二种情况:
原本存储在一个数据块内的数据行,因为更新操作导致长度增长,而所在数据块的可用空间也不能容纳增长后的数据行。
在这种情况下,oracle将此行数据迁移到新的数据块中,oracle在被迁移数据行原本所在位置保存一个指向新数据块的指针。
被迁移数据行的rowid保持不变。
当数据行发生链接或迁移时,对其访问将会造成I/O性能降低,因为oracle为获取这些数据行的数据时,必须访问更多的数据块。
可以看到行迁移发生在update,有一部分数据放在当前块,有一部分数据放在链接的块中。
行链接在当前块只放一个地址,内容全部在链接的块中,且可能有多个链接块。
行迁移和行链接的危害?
会引起额外的I/O操作。
如何检测出行迁移?
SQL>@?
\RDBMS\ADMIN\utlchain.sql
SQL>analyzetable【table_name】listchainedrowsintochained_rows;
SQL>selectowner_name,table_name,head_rowidfromchained_rows;
如何避免和消除行迁移和行链接?
不要插入一行数据,而一行带有大量NULL的列,更合适的是,从一开始插入数据就要填满行。
使用增大blocksize的表空间。
增大pctfree。
重建表或索引。
9.解释表上参数含义
createtableTEST
(
IDNUMBER
)
tablespaceDFWMS
pctfree10
initrans1
maxtrans255
storage
(
initial64
minextents1
maxextentsunlimited
);
10.Rowid的理解--实验
rowid是伪列(pseudocolumn),伪劣的意思是实际上这一列本身在数据字典中并不存在,在查询结果输出时它被构造出来的。
rowid并不会真正存在于表的datablock中,但是他会存在于index当中,用来通过rowid来寻找表中的行数据。
rowid的组成:
数据对象号+相对文件号+数据块号+在数据块中的行号。
selectt.rowid,t.*fromtestt;--AAAMimAAFAAAAAMAAC
64进制A-Z(0-25)a-z(26-51)0-9(52-61)+/(62-63)
select12*64*64+34*64+38fromdual;--51366
s
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 性能 基础 教材