3从案例中体验逻辑结构如何影响SQL优化.pdf
基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 基于案例学SQL优化第3周 从案例中体验逻辑结从案例中体验逻辑结构如何影响构如何影响SQL优化优化 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 法律声明【声明】本视频和幻灯片为炼数成金网络课程的教学资料，所有资料只能在课程内使用，丌得在课程以外范围散播，违者将可能被追究法律和经济责任。课程详情访问炼数成金培训网站 http:/ 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 当前课程进度 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 逻辑结构知识讲述 基于案例学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 逻辑结构从物理结构的逻辑结构从物理结构的Data file说起说起 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 4.区(Extent)不能跨越在多个数据文件上，一个数据文件可以包含多个区。3.一个段(Segment)可以分布在多个数据文件(Data file)中，一个数据文件也可以存在多个段。1.数据库(Database)可以含多表空间(Tablespace)2.一个表空间可以有多个数据文件(data file)，可以有多个段(Segment)，一个段只存在一个表空间内。逻辑体系结构不Data file 5.一个段可以划分为多个区间。6.任何一个区间都是由一系列连续的块(Block)组成的，一个区间含多个数据库块。包含更多的block物理结构 VS 逻辑结构基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 逻辑体系结构关系图 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 逻辑体系结构最小单位BLOCK的研究 数据块头(header)中包含了此数据块的概要信息，例如块地址(block address)及此数据块所属的段(segment)的类型(比如到底是表还是索引)。表目录存放的是某行数据插入到数据库块中，该行数据所在的表的信息将被存储在这个区域。行目录就是存放你插入的行的地址。可用空间区即块中的空余空间，大小由pctfree参数设置，如果是10，表示该块将空余10%左右空间。此外如果是表或索引块，该区域还会存储事务条目。行数据区域更简单，即存储具体的行或索引的信息，这部分占用数据块绝大部分空间。一次IO一定是以block为单位块头可以识别基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 体系细节不sql优化 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 体系细节不sql优化 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 试验结论：各种开销致试验结论：各种开销致每每行最小长度大致行最小长度大致11字节，一字节，一个个8K块块的行理论上的行理论上最多存储不超过最多存储不超过(8096/11)行行。不BLOCK相关 Block最多能装多少行？最多能装多少行？文件号块号基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不BLOCK相关 Block行迁移的成因与优化 试验结论：行迁移确实会导致产生更多的逻辑读，会影响sql语句的性能！基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不BLOCK相关 Block行链接的成因与优化 试验结论：行链接一般来说无法避免，要通过增大数据块的方式消除。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不SEGMENT相关 segment 及EXTENTS的体会 试验结论：建表即产生表段，建索引即产生索引段，最小单位是BLOCK，空间申请是以EXTENTS为单位的。索引和表分别是两个段由若干连续的block组成基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不SEGMENT相关 试验结论：随着记录的增加，表和索引变大，可观察EXTENTS和BLOCKS的个数也在增多。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不SEGMENT相关 segment之高水平位相关排查优化案例 试验结论：delete无法降低高水平位，表扫描依然需要大量逻辑读，并且表的大小依然不变，在完成move等降低高水平位的操作后，逻辑读大大减少，表的大小也大幅度减少。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不SEGMENT相关 试验结论：通过统计信息分析NUM_ROWS和BLOCKS的关系，可以推测出是否存在高水平位问题。这可以用来做系统的性能监控。另外请记住，大量delete后的空间是可以重用的。segment之高水平位相关情况监控探讨 delete会有大量的高水位的空块基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不TABLESPACE相关 表空间的类别、体会及脚本 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 不TABLESPACE相关 基于案例学SQL优化 讲师 梁敬彬 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为一行的物理地址，当一行插入数据库块后，rowid就唯一，除非行的物理移动，否则不变。3.rowid不真正存在表数据块中，但是会存在索引中，方便根据索引中的rowid找到表数据。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 相关优化案例分析 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 BLOCK相关案例 block 尺寸设置减少逻辑读的优化案例 试验结论：BLOCK越大，存储的行就越多，访问产生的逻辑读就越小。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 BLOCK相关案例 block 尺寸与并发争用关系之分析案例 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 SEGMENT相关案例 segment之分区表的化整为零优化案例 试验结论：分区表由多个段组成的，而普通表由单个段组成的。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 SEGMENT相关案例 试验结论：因为分区表可以叧扫描特定的段，化整为零，所以性能比普通表扫描更好！基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 表空间相关案例 表空间查询慢与回收站关系之经典案例 试验结论：回收站有1万个对象后，表空间查询语句执行速度从原来的0.1秒变成5秒，慢了50倍！基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 表空间相关案例 案例分析：XX系统的表空间监控语句经常反馈查询很慢，经过分析，实际情况是和当时监控的数据库的回收站数量过多有关系，因为表空间语句需要扫描RECYCLEBIN$这个对象，而且是全表扫描，不仅如此。还是NL的连接方式，该表返回多少条，被驱动表就要被访问多少次。因此这个RECYCLEBIN$尽可能要少记录，才可以性能更快。基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 表空间相关案例 表空间频繁扩展对插入速度效率的案例 试验结论：如果表空间自动扩展非常频繁，会显著影响插入的速度。extents分配空间的最小单位基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 ROWID相关案例 利用rowid提升sql性能的常见优化案例 试验结论：返回少量记录，全表扫描效率索引读效率rowid访问效率 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 相关优化案例分析 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 基于案例学SQL优化 讲师 梁敬彬 DATAGURU与业数据分析社区 炼数成金逆向收费式网络课程 Dataguru（炼数成金）是与业数据分析网站，提供教育，媒体，内容，社区，出版，数据分析业务等服务。我们的课程采用新兴的互联网教育形式，独创地发展了逆向收费式网络培训课程模式。既继承传统教育重学习氛围，重竞争压力的特点，同时又发挥互联网的威力打破时空限制，把天南地北志同道合的朋友组织在一起交流学习，使到原先孤立的学习个体组合成有组织的探索力量。并丏把原先动辄成千上万的学习成本，直线下降至百元范围，造福大众。我们的目标是：低成本传播高价值知识，构架中国第一的网上知识流转阵地。关于逆向收费式网络的详情，请看我们的培训网站 http:/ DATAGURU专业数据分析网站 FAQ时间 37
