3从案例中体验逻辑结构如何影响SQL优化.pdf

《3从案例中体验逻辑结构如何影响SQL优化.pdf》由会员分享，可在线阅读，更多相关《3从案例中体验逻辑结构如何影响SQL优化.pdf（37页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 基于案例学SQL优化第3周 从案例中体验逻辑结从案例中体验逻辑结构如何影响构如何影响SQL优化优化 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 法律声明【声明】本视频和幻灯片为炼数成金网络课程的教学资料，所有资料只能在课程内使用，丌得在课程以外范围散播，违者将可能被追究法律和经济责任。课程详情访问炼数成金培训网站 http:/ 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 当前课程进度 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 逻辑结构知识讲述 基

2、于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 share pool Library cache Data directory cache data buffer log buffer SGA PMON SMON LCKn RECO LGWR DBWR instance 1 4 2 3 数据文件data file 日志文件 参数文件 控制文件 ARCH 存储介质 ARCH ARCH database Server User User User default keep 逻辑结构从物理结构的逻辑结构从物理结

3、构的Data file说起说起 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 4.区(Extent)不能跨越在多个数据文件上，一个数据文件可以包含多个区。3.一个段(Segment)可以分布在多个数据文件(Data file)中，一个数据文件也可以存在多个段。1.数据库(Database)可以含多表空间(Tablespace)2.一个表空间可以有多个数据文件(data file)，可以有多个段(Segment)，一个段只存在一个表空间内。逻辑体系结构不Data file 5.一个段可以划分为多个区间。6.任何一个区间都是由一系列连续的块(Block)组成的，一个区间含多

4、个数据库块。包含更多的block物理结构 VS 逻辑结构基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 逻辑体系结构关系图 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 逻辑体系结构最小单位BLOCK的研究 数据块头(header)中包含了此数据块的概要信息，例如块地址(block address)及此数据块所属的段(segment)的类型(比如到底是表还是索引)。表目录存放的是某行数据插入到数据库块中，该行数据所在的表的信息将被存储在这个区域。行目录就是存放你插入的行的地址。可用空间区即块中的空余空间，大小由pctfree参数设置，如果是10，表

5、示该块将空余10%左右空间。此外如果是表或索引块，该区域还会存储事务条目。行数据区域更简单，即存储具体的行或索引的信息，这部分占用数据块绝大部分空间。一次IO一定是以block为单位块头可以识别基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 体系细节不sql优化 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 体系细节不sql优化 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 试验结论：各种开销致试验结论：各种开销致每每行最小长度大致行最小长度大致11字节，一字节，一个个8K块块的行理论上的行理论上最多存储不超过最多存储不超过

6、(8096/11)行行。不BLOCK相关 Block最多能装多少行？最多能装多少行？文件号块号基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不BLOCK相关 Block行迁移的成因与优化 试验结论：行迁移确实会导致产生更多的逻辑读，会影响sql语句的性能！基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不BLOCK相关 Block行链接的成因与优化 试验结论：行链接一般来说无法避免，要通过增大数据块的方式消除。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不SEGMENT相关 segment 及EXTENTS的体会 试验结论

7、：建表即产生表段，建索引即产生索引段，最小单位是BLOCK，空间申请是以EXTENTS为单位的。索引和表分别是两个段由若干连续的block组成基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不SEGMENT相关 试验结论：随着记录的增加，表和索引变大，可观察EXTENTS和BLOCKS的个数也在增多。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不SEGMENT相关 segment之高水平位相关排查优化案例 试验结论：delete无法降低高水平位，表扫描依然需要大量逻辑读，并且表的大小依然不变，在完成move等降低高水平位的操作后，逻辑读大大减少，

8、表的大小也大幅度减少。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不SEGMENT相关 试验结论：通过统计信息分析NUM_ROWS和BLOCKS的关系，可以推测出是否存在高水平位问题。这可以用来做系统的性能监控。另外请记住，大量delete后的空间是可以重用的。segment之高水平位相关情况监控探讨 delete会有大量的高水位的空块基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不TABLESPACE相关 表空间的类别、体会及脚本 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不TABLESPACE相关 基于案例学SQ

9、L优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不TABLESPACE相关 表空间之临时表空间组调优 试验结论：临时表空间组可以把各个SESSION分配不同临时表空间，有利于分散负载，缓解IO竞争。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不ROWID相关 oracle行定位与rowid描述 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不ROWID相关 试验结论：1.OOOOOOFFFBBBBBBRRR，O是对象ID,F是文件ID,B是块ID,R是行ID。（注：Oracle8以上)2.rowid为一行的物理地址，当一行插入数据库块后

10、，rowid就唯一，除非行的物理移动，否则不变。3.rowid不真正存在表数据块中，但是会存在索引中，方便根据索引中的rowid找到表数据。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 相关优化案例分析 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 BLOCK相关案例 block 尺寸设置减少逻辑读的优化案例 试验结论：BLOCK越大，存储的行就越多，访问产生的逻辑读就越小。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 BLOCK相关案例 block 尺

11、寸与并发争用关系之分析案例 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 SEGMENT相关案例 segment之分区表的化整为零优化案例 试验结论：分区表由多个段组成的，而普通表由单个段组成的。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 SEGMENT相关案例 试验结论：因为分区表可以叧扫描特定的段，化整为零，所以性能比普通表扫描更好！基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 表空间相关案例 表空间查询慢与回收站关系之经典案例 试验结论：回收站有1万个对象后，表空间查询语句执行速度从原来的0.1秒变成5秒，慢了50

12、倍！基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 表空间相关案例 案例分析：XX系统的表空间监控语句经常反馈查询很慢，经过分析，实际情况是和当时监控的数据库的回收站数量过多有关系，因为表空间语句需要扫描RECYCLEBIN$这个对象，而且是全表扫描，不仅如此。还是NL的连接方式，该表返回多少条，被驱动表就要被访问多少次。因此这个RECYCLEBIN$尽可能要少记录，才可以性能更快。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 表空间相关案例 表空间频繁扩展对插入速度效率的案例 试验结论：如果表空间自动扩展非常频繁，会显著影响插入的速度。exten

13、ts分配空间的最小单位基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 ROWID相关案例 利用rowid提升sql性能的常见优化案例 试验结论：返回少量记录，全表扫描效率索引读效率rowid访问效率 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 相关优化案例分析 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 炼数成金逆向收费式网络课程 Dataguru（炼数成金）是与业数据分析网站，提供教育，媒体，内容，社区，出版，数据分析业务等服务。我们的课程采用新兴的互联网教育形式，独创地发展了逆向收费式网络培训课程模式。既继承传统教育重学习氛围，重竞争压力的特点，同时又发挥互联网的威力打破时空限制，把天南地北志同道合的朋友组织在一起交流学习，使到原先孤立的学习个体组合成有组织的探索力量。并丏把原先动辄成千上万的学习成本，直线下降至百元范围，造福大众。我们的目标是：低成本传播高价值知识，构架中国第一的网上知识流转阵地。关于逆向收费式网络的详情，请看我们的培训网站 http:/ DATAGURU专业数据分析网站 FAQ时间 37