作者：闫书清
出版社：电子工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
高性能SQL调优精要与案例解析试读：
前言
在当今信息时代，各行业的数据均呈爆炸式增长，这不仅为我们认识和掌握规律，进而推动整个社会发展提供了更多依据、途径和动力，同时对我们提出了更高的目标和要求。因为，面对如此海量数据，我们除了要科学地管理好它，更重要的是，要充分地利用好它，否则，这些将成为我们沉重的负担和累赘。那么，如何才能从这些海量数据中挖掘出蕴藏的巨大价值呢？
以 Oracle 为首的关系型数据库，历经几十年的高速发展，凭借其理论和技术上的优势，目前已为广大客户所垂青和依赖，并充斥于各行各业的每个角落。同时，关系型数据库也已成为海量数据的主要容器和工具，以至于，以 Oracle 为首的关系型数据库几乎成了数据的代名词。诚然，当今的数据库领域是个开放、多元的时代，各种数据产品和概念可谓百花齐放，但是，由于目前除关系型数据库外的其他产品都有其特定的场景和限制，因而在短时期内，关系型数据库在数据领域的重要地位依然难以被撼动和替代。
说到海量数据，大家自然会想到数据库优化，进而又必然会想到SQL调优，当然，除了SQL调优，数据库优化还涉及其他方面的诸多内容。实际上，针对海量数据和数据库优化，SQL调优占据着非常大的比重，通常也会带来非常明显的效果。现实情况中，一条存在性能问题的SQL语句，也许会拖垮整个系统；同时，一条SQL语句的完美优化，又可能使整个系统的问题瞬间灰飞烟灭。鉴于SQL调优的重要性，本书将对其进行详细论述和深入解析。
目前，SQL调优方面的优秀书籍在国内市场并不多见，希望本书能填补这个空白和缺憾。本书开篇从基础入手，详细论述SQL调优所需具备的理论知识，以及对它们的深入、正确理解。本书后半部分精选了10个实际案例作为样本，并结合前面的基础理论，对这些案例进行了详细、深入的剖析，以说明应用基础理论解决实际案例的思路、方法和步骤等。本书既注重基础理论，又强调实践应用，通篇理论和实践浑然一体，相信会让各位读者有焕然一新、豁然开朗的感觉，同时，衷心希望各位读者能通过本书多多受益。
本书虽然基于 Oracle 关系型数据库创作与编写，但就理论和实践方面，对其他关系型数据库的SQL调优，依然具有很好的借鉴意义。另外，作者在本书适当位置为读者留有疑问，且很多语句或命令中带有省略号“...”，初衷在于启发读者主动学习、研究和解决问题的动力和方法，同时为读者学习、思考和研究留有更大空间，从而达到授人以渔而非仅授人以鱼的效果。
作者结合多年来的学习、研究和实践经验，旨在努力打造一部SQL调优方面的经典之作，更希望其能成为读者学习航程上的一座灯塔。但限于时间等诸多因素，疏漏之处再所难免，欢迎各位读者批评指正。
最后，谨对多年来一直默默陪伴和支持我的家人及所有的老师、朋友，表示深深的感激和敬意！
特别值得提出的是，本书的出版得到了电子工业出版社安娜等各位老师的大力支持和帮助，没有各位老师的耐心指导和辛勤付出，本书不会在这么短时间内如此完美地展现在读者面前，在此，表示衷心的感谢和深深的敬意。基础篇
对于任何一门技能的学习而言，理论和实践缺一不可。学习理论是奠定基础，进行实践是验证理论，理解理论，进而升华掌握理论，积累实战经验的过程。只学习理论而不进行实践，是很难真正理解和掌握理论的，结果也只能是纸上谈兵。只有在长期实践中才能真正理解和掌握理论。只进行实践而不学习理论，实际也不算真正的实践，只能算蛮干和乱撞，结果很可能是没有任何根基的空中楼阁，能力和水平也始终只能在较低层次徘徊。只有在理论指导下的实践，才能使能力和水平不断得以进步和提升。同理，对SQL调优来说，理论和实践同等重要，这在本书前言部分也曾提及，因此，在论述和解析实际案例前，先进行基础理论学习非常必要。本书基础篇包括第1至10章，内容涉及数据库基础架构、数据对象、执行计划、分析及解决方法等内容，“工欲善其事，必先利其器”，说明了学习过程中打好基础的必要性。下面，我们就开始SQL调优学习之旅的前半程，进入SQL调优的筑基阶段。第1章 整体架构
所谓整体架构，这里指的是保证 Oracle 数据库系统正常工作和运行所必需的组件及其实现。从大的方面讲，这主要包括实例和数据库。在此基础上细分，实例由相关内存区域和服务进程组成，数据库包括各种相关文件，而这些相关内存区域、服务进程及相关文件又可再进一步细分。下面就按照这个顺序和逻辑，逐步讲解Oracle数据库系统的整体架构。1.1 实例
实例（Intance），即数据库实例，由服务器上的一组内存结构和进程组成，用以支撑和完成数据库的正常运行和操作，实例可以独立于数据库存在，主要包括下列组件。
1）内存（Memory）：服务器操作系统为实例分配的内存区域，用于数据库内存方面的活动和操作，主要分为两部分：SGA 和 PGA。SGA 为实例范围内共享的，其内部分为共享池（Shared Pool）、数据缓冲（Buffer Cache）、Redo缓冲等。其中，共享池又可分为库缓冲（Library Cache）和字典缓冲（Data Dictionary Cache）等。PGA为各会话私有的。我们可以通过查询系统视图V$MEMORY_DYNAMIC_COMPONENTS来获取当前实例内存各组件的相关信息。
2）后台进程（Background Process）：实例创建和维护的一组后台进程（或线程），用以完成数据库内部的各种统一管理和监控任务。进程的数量和名字随版本和具体配置的不同而有所不同，但这些进程是共享的，不会属于某个或某些会话，例如，Pmon、Smon、Dbw、Lgwr和Ckpt等。可以通过查询系统视图V$PROCESS来获取后台进程的相关信息。
3）服务进程（Server Process）：实例为数据库会话创建或分配的，用以完成会话任务的服务器端服务进程。实例为会话分配服务进程的方式，分为如下两种情况。
· 当实例设置为专用服务器模式，用户和数据库之间建立会话时，实例就会为该会话创建一个服务进程，用以完成该会话的任务。
· 当实例设置为共享服务器模式时，实例会在数据库服务器上启动一组服务进程，这组进程并不专属于某个或某些会话，而是在会话要执行任务时，实例调度进程（Dispatcher）将会话任务放到共享内存中的相关任务队列中，并择机将该任务分配给这组进程中的某个服务进程，该服务进程完成该会话任务后，会将该会话任务的结果放到共享内存的相关结果队列中，实例调度进程再择机将任务结果返回给相关会话。因此，该模式下，实例的所有会话共享一组服务进程，该组进程的多少及相关行为由实例的相关参数控制，并且，一旦该组服务进程不足以服务实例中的所有会话时，实例会根据相关参数设置动态创建和分配另外的服务进程，此后，当实例中的服务进程资源过剩时，这些服务进程也可以再被释放掉。
可以通过查询系统视图V$PROCESS来获取服务进程的相关信息。1.2 数据库
数据库（Database），由服务器上的一组磁盘文件组成，用于存储数据库相关的各种管理信息和用户数据，以保证数据库正常运转和用户数据的不丢失，数据库及其文件可以独立于实例而存在，其主要包括下列文件。
1）参数文件（Parameter File）：一个非常重要的文件，实例（数据库）启动时要使用它。该文件存储着实例及数据库的配置等重要信息。当该文件不存在时，Oracle无法创建实例，因此，也不能正常启动和使用数据库。高版本的Oracle中，该文件分为initsid.ora和spfilesid.ora两种，前者可以用文本编辑器编辑，以修改和保存数据库参数，而后者不可以，但后者支持数据库参数的命令修改和某些参数的在线动态修改。默认情况下，数据库会首选使用spfilesid.ora启动，仅当该参数文件不存在时，才会使用 initsid.ora 启动，两者默认位置为 ORACLE_HOME/dbs/目录，也可以在文件initsid.ora中指定文件spfilesid.ora的位置和名字，initsid.ora和spfilesid.ora可以互相创建。可以通过查看数据库参数spfile来确定spfile参数文件的位置和名字。
由spfile创建initsid.ora的命令为
说明
① 参数文件pfile和spfile在相互创建过程中，可以指定pfile和spfile的位置和名字，也可以不指定。
② 启动实例时，可以指定参数文件pfile和spfile的路径和名字。
2）控制文件（Control File）：一个非常重要的文件，损坏和丢失后会导致数据库不能正常打开和使用，如果没有备份，创建起来将非常麻烦。庆幸的是，控制文件提供了多路复制功能，即相同控制文件可以存在多个副本，这些副本可以分别存在于多个不同设备上，这样既防止了文件的损坏和丢失，同时也提高了访问控制文件的并发和性能。该文件存储着数据库物理结构等重要信息，有时也会存储其他一些信息。可以通过查询系统视图V$CONTROLFILE来获取控制文件的相关信息。
3）数据文件（Data File）：实际存储用户数据的文件，其内部格式随版本不同而不同。因为用户的数据都存储在这些文件里，因此，这些文件是数据库的核心，非常重要。可以通过查询系统视图DBA_DATA_FILES来获取数据文件的相关信息。
4）回滚文件（Undo File）：存储用于构造数据块前影像的数据的文件。当用户修改数据库中的数据时，系统会为该修改操作生成反操作向量，并把这些反操作向量存储于Undo表空间中（Undo文件构成了Undo表空间），当需要被修改数据块的前影像（修改前的样子）时，那么，就需要把Undo表空间中存储的这些反操作向量应用于修改后的数据块上，以构造这些数据块的前影像，Undo文件和数据文件同样重要。可以通过查询系统视图DBA_DATA_FILES获取回滚文件的相关信息。
5）临时文件（Temp File）：存储数据库中临时数据的文件。临时数据主要来自两个方面：一是当PGA中的内存不能满足会话某些操作的需求时，会话将把一些临时或中间数据暂时写到临时表空间（临时文件构成了临时表空间）；二是用户在数据库中创建了临时表，其中的数据也会存储在临时表空间中。可以通过查询系统视图DBA_TEMP_FILES来获取临时文件的相关信息。
6）重做日志文件（Redo Log File）：存储数据库修改操作相关信息的文件，通过该文件可以保证数据库及其数据的安全和可恢复。可以通过查询系统视图V$LOGFILE来获取重做日志文件的相关信息。
7）归档日志文件（Archive Log File）：存储数据库历史，修改操作相关信息的文件。Oracle的Redo文件是循环使用的，为了使Redo文件中的数据不被覆盖，需要预先将Redo文件复制到其他位置，那么，这些被复制到其他位置的Redo文件，就是归档日志文件。归档日志文件可以用来对数据库进行时间点恢复，同时，要想对数据库进行热备份，也必须将数据库置于归档模式，这样，数据库就会在Redo文件被覆盖前，自动在指定位置产生归档日志文件。可以通过查询系统视图来获取归档日志文件的相关信息。
8）警告日志文件（Alert Log File）：存储数据库运行状况日志信息的文件，其中，会记录一些有关数据库的运行日志、操作、报错及其他运行状况相关信息。具体位置因版本不同而不同，Oracle 11g版本中，默认位置为$ORACLE_BASE/diag/rdbms/dbname/sid/trace/alert.log，可以通过查询系统视图V$DIAG_INFO获取警告日志文件的相关信息。
9）跟踪文件（Trace File）：记录数据库内部行为的文件，Trace文件可以是系统自动生成的，也可以是用户手动生成的。Oracle 11g版本中，可以通过查询系统视图V$DIAG_INFO来获取Trace文件的相关信息。1.3 整体架构图解
通过前面的介绍，相信大家对Oracle架构有了一个大致了解。为了帮助大家对Oracle整体架构、工作机制及原理等有更具体、深刻的理解，笔者结合自身经验及相关文档，绘制了Oracle整体架构示意图，如图1-1所示。图1-1 Oracle整体架构示意图
启示
① 图1-1中，系统全局区（见本书2.1节）的共享池（见本书2.1.1节）和程序全局区（见本书2.2节）中都包含会话区（见本书2.2.1节），且会话区四周边框为虚线，以此说明会话区可能存在于两个内存区内（并非同时）。专用服务器模式下，会话区存在于程序全局区内；共享服务器模式下，会话区存在于系统全局区的共享池内（有时也会在大池[Large Pool]内）。具体见本书2.2.1节中的内容，也可以参考官方或其他相关文档。
② 大家请思考，数据库实例包括系统全局区（SGA）和后台服务进程，是否也包括程序全局区（PGA）和会话服务进程？为什么？第2章 内存架构
所谓内存架构，这里是指Oracle实例内存管理和使用相关的逻辑设计及实现等方面内容。更具体一点，就是讲Oracle实例是如何分配、管理和使用相关内存空间的。本书1.1节中我们已经讲过，Oracle实例包括各种内存区域，而这些内存区域的设计和实现又各不相同，这里我们主要讲解SGA和PGA相关设计和实现等方面的内容，这也是Oracle内存层面的核心原理和机制。如果对Oracle实例内存区域其他相关内容感兴趣，请参考官方或其他相关文档。2.1 系统全局区
系统全局区（SGA，System Global Area），是数据库实例启动时创建的一个共享内存区域，其大小和相关配置通过参数文件（spfile或pfile）中的相关参数来控制。这里需要强调一点，系统全局区是实例范围内的共享区域。系统全局区主要由共享池、数据缓冲和重做日志缓冲等诸多区域组成。SGA 相关参数配置不同，系统全局区组成也会有所差异，下面仅就上述三个区域进行讲解，其他详细信息请参考官方或其他相关文档。2.1.1 共享池
共享池（Shared Pool）是数据库实例最重要、最复杂的共享内存区域，里面存储着数据库实例最重要的结构和信息。可以通过查询系统视图 V$SGA_DYMATIC_COMPONENTS 和V$SGAINFO来获取共享池的相关信息。
此外，还可以通过show parameter命令显示共享池的配置信息。2.1.2 数据缓冲
数据缓冲（Data Buffer），作为用户数据的缓存区，在系统共享内存中暂存数据库的数据块，通过这个措施，可以改善数据库的读写性能。可以通过查询系统视图 V$SGA、V$SGA_DYNAMIC_COMPONENTS和V$SGAINFO获取数据缓冲的相关信息。
此外，还可以通过show parameter命令显示Data buffer配置信息。
启示
大家知道，数据缓冲由很多个共享内存区内的Buffer Block组成，每个Buffer Block对应一个数据块（见本书3.1节），那么，Oracle系统为了很好地管理这些Buffer Block，会为每个Buffer Block分配一个Buffer Block Header（简称BH），大家思考一下，这些BH存储在什么地方？如何进行组织和管理？2.1.3 重做日志缓冲
重做日志缓冲（Redo Log Buffer），作为重做日志数据的缓存区，在系统共享内存中暂存数据库重做日志数据，这样，可以改善重做日志数据的读写性能。可以通过查询系统视图V$SGA、V$SGA_DYNAMIC_COMPONENTS和V$SGAINFO获取重做日志缓冲的相关信息。
此外，还可以通过show parameter命令显示重做日志缓冲的配置信息。2.1.4 系统全局区相关信息
可以通过查询系统视图V$SGA、V$SGA_DYNAMIC_COMPONENTS和V$SGAINFO获取系统全局区的相关信息。
此外，还可以通过show parameter命令显示系统全局区的配置信息。2.2 程序全局区
程序全局区（PGA，Program or Process Global Area），是服务进程存储数据及控制信息进而完成相关任务的内存区域。相对于前面讲的其他区域来讲，该区域是个私有内存区域。程序全局区内部又分为会话区和SQL工作区，会话区存储会话相关的各种信息，SQL工作区用于各种内存操作和计算，具体将在下面各节进行讲解。2.2.1 会话区
1）会话区（UGA，User Global Area），是为会话分配的内存区域，用于存储各种会话变量，例如，会话登录信息以及会话需要的其他各种信息。实际上，会话区存储会话的各种状态信息。会话区内分为各种区域，其中包括私有SQL区（Private SQL Area），而私有SQL区又包括持久区（Persistent Area）和运行区（Run-time Area），前者用于存储会话绑定变量值等信息，后者用于跟踪会话操作的执行状态等信息，具体信息请参考官方或其他相关文档。
2）针对Oracle实例的不同服务器模式设置，会话区分为如下两种情况。
· 专用服务器模式：当实例运行在专用服务器模式时（见本书1.1节），会话区存储在程序全局区中。
· 共享服务器模式：当实例运行在共享服务器模式时（见本书1.1节），实例中不同会话共享一组服务进程，会话区不能被存储在程序全局区中，而是被移到系统全局区的共享池或大池（Large Pool）中。
启示
为什么专用服务器模式下，会话区位于程序全局区中，而共享服务器模式下，就要将其移到系统全局区的共享池或大池中，具体原因是什么？2.2.2 SQL工作区
SQL工作区（SQL Work Areas），是为服务进程进行各种内存操作分配的PGA私有内存。例如，排序区（Sort Area）用于数据排序，哈希区（Hash Area）用于为左行源构建哈希表，而位图合并区（Bitmap Merge Area）用于合并来自多个位图索引的数据等。
启示
大家思考一下，为什么共享服务器模式下，未将SQL工作区移到系统全局区的共享池或大池中，具体原因是什么？2.2.3 程序全局区的相关信息
通过查询系统视图V$PGASTAT，可以获取程序全局区的相关信息。
此外，还可以通过show parameter命令来查看程序全局区的配置信息。第3章 存储架构
所谓存储架构，这里是指 Oracle 数据库磁盘空间组织、管理和使用相关的逻辑设计及实现等方面内容。更具体一点，就是讲 Oracle 数据库是如何组织、分配、管理和使用磁盘文件内的存储空间的。本书1.2节中我们已经讲过，Oracle数据库包括各种文件，而这些文件的设计和实现肯定各不相同，这里我们主要讲解数据文件相关的设计和实现等内容，这也是Oracle存储层面的核心原理和机制。如果对 Oracle 数据库文件的其他相关内容感兴趣，请参考官方或其他相关文档。3.1 块
1）块概念：块（Block）是Oracle数据库读写的最小单位，块大小（Block Size）是操作系统（OS）层面块大小的整数倍。Oracle块大小的默认值通过参数“db_block_size”设置，可以是2KB、4KB、8KB、16KB和32KB，Oracle 9i版本以后支持数据库中同时存在多种不同大小的块。
2）块自由空间（Free Space）：块内的自由空间与行迁移（Row Migrating）及行链接（Row Chaining）相关。
3）行迁移：当数据行长度（Data Row Length）因update操作而增长时，块内的自由空间不足以容纳修改后的数据行（Data Row），那么该数据行将迁移到另外一个能容纳它的块里存储，原来的块里留下一个指向新存储位置的指针。行迁移组织结构具体如图3-1所示。图3-1 行迁移（Row Migrating）示意图
说明
① 图3-1中，数据行Row3原来在块Block1中，后因update数据行Row3导致长度变大，块 Block1 中自由空间太小，不足以容纳修改后的数据行 Row3，因此数据行 Row3 迁移到块Block2中，在Block1中留下Row3的新地址。当需要访问数据行Row3时，只能先读取块Block1，获取Row3的新地址，然后，再去访问块Block2，获取真实的数据行Row3，这样，在访问数据行Row3时，因为发生了行迁移而导致多读取了一个块。
② 可以通过增大块中自由空间来尽量避免和减少行迁移。
4）行链接：当单个块（Single Block）的全部空间不足以容纳单个数据行（Single Data Row）时，就需要多个块链接起来，共同存储单个数据行。因此，如果单个数据行足够大，那么行链接是不可避免的。行链接组织结构具体如图3-2所示。图3-2 行连接（Row Chaining）示意图
说明
① 图3-2中，因为块Block3的全部空间也不足以容纳单个数据行Row1，因此，在将块Block3填满后，将数据行Row1未能存储在块Block3中的剩余部分，存储到块Block4中，块Block3中保留未能存储在块Block3中数据行Row1部分的地址。
② 虽然在块大小和行大小固定的前提下，行链接不可避免，但我们可以调整块大小或行大小来尽量避免和减少行链接。
5）行迁移和行链接的影响：数据库中存在过多的行迁移和行链接，都会因为消耗过多I/O资源而影响性能。
6）块信息获取：虽然数据库中没有可以直接获取块相关信息的视图，但可以间接通过系统视图DBA_EXTENTS获取，该视图中有区间（见本书3.2节）的相关信息，这就间接获取了块的相关信息。因为一个区间内，块都是连续的，可以用如下语句获取块的相关信息。3.2 区间
1）区间（Extent）概念：也可译作扩展，区间是 Oracle 有关存储空间的一个逻辑单位，由多个地址连续的块（见本书3.1节）组成，也是Oracle存储空间分配的最小单位，当某个数据库对象需要存储空间时，Oracle至少要为其分配一个区间。
2）区间分配：区间在段（Segment）被创建或段空间扩展时被分配，具体细节与 Oracle版本相关。
3）区间分配管理方式：随着版本的不同，区间的分配和管理方式也不同。Oracle 9i及更高版本中，一般采用本地管理（Local Management）存储空间的方式，分配方式有统一（Uniform）和自动管理（Auto）两种方式，前者每个区间的大小固定且统一，后者由 Oracle 系统自动调整和确定。此外，还有一种分配方式，就是用户（User）分配方式，因为使用时比较烦琐，因此很少被用。
4）区间释放：一般来说，当段被清除（drop）时，区间所占用的存储空间会被释放，以供系统中的其他对象使用，否则，即使数据被删除（delete），区间空间也不会被释放，哪怕是普通的截断（truncate）。虽然不同版本情况会不同，但区间占用的存储空间也不会被完全释放。
5）区间信息获取：可以通过查询系统视图DBA_EXTENTS来获取区间的相关信息。
启示
思考段的高水位线（HWM，High Water Mark）降低和区间释放之间的区别。3.3 段
1）段（Segment）概念：实际存储数据库中数据对象数据的逻辑结构和单元，段由一个或多个区间（见本书3.2节）组成。
2）分区和段：非分区表和非分区索引分别对应一个段，分区表和分区索引的每个分区或子分区分别对应一个段。
3）段分配：段是存储数据库对象数据的实体，是存放数据的真正逻辑结构和单元。创建一个非分区表和非分区索引，或分区表的分区和分区索引的分区时，或者向表或索引中加载数据时，系统就会为它们分配段，用来实际存储这些数据库对象的数据，相关细节和具体Oracle版本相关。
4）其他段：除了表和索引对应的段外，还有其他类型的段，例如，临时段（Temporary Segments）、回滚段（Rollback Segments）等。虽然都叫作段，但其内部机制各不相同。临时段在临时表空间（见本书3.4节）中创建和分配，用来存储临时或中间数据。回滚段在回滚表空间（见本书3.4节）中创建和分配，用来存储重构块前影像所需的反操作向量数据。
5）段信息获取：可以通过查询系统视图DBA_SEGMENTS获取段的相关信息。3.4 表空间
1）表空间（Tablespace）概念：Oracle数据库中最大的存储空间相关的逻辑概念和容器，存储系统和用户数据的段（见本书3.3节）都在表空间中被分配。表空间是共享资源而不是私有资源，不同用户或段的数据可以存储在同一个表空间中，当然，也可以存储在不同的表空间中。
2）表空间组成：表空间由一个或多个数据文件（见本书 1.2 节）组成。可以通过查询系统视图DBA_DATA_FILES和DBA_TEMP_FILES来获取组成数据表空间和临时表空间的数据文件的相关信息。
3）表空间分类：按照存储数据的类型，表空间分为数据表空间（Data Tablespace）、临时表空间（Temporary Tablespace）和回滚表空间（Undo Tablespace）。顾名思义，数据表空间用来存储系统和用户的数据，临时表空间用来存储系统和用户的临时数据，回滚表空间用来存储重构块前影像所需的数据。
4）表空间信息获取：可以通过查询系统视图DBA_TABLESPACES来获取表空间的相关信息。
5）Oracle数据库存储层面逻辑组织结构具体如图3-3所示。
试读结束[说明：试读内容隐藏了图片]