SQL语句的执行过程 课件

SQL语句的执行过程以及数据库内存结构拓明科技Oracle数据库内部培训资料SQL语句的执行过程以及数据库内存结构拓明科技Oracle数1♦SQL语句的执行过程

♦数据库的内存结构拓明科技Oracle数据库内部培训资料♦SQL语句的执行过程

♦数据库的内存结构拓明科技Ora2DDL（数据定义语言）DML（数据操纵语言）拓明科技Oracle数据库内部培训资料SQL语句的类型DDL（数据定义语言）拓明科技Oracle数据库内部培训资料3修改Oracle数据字典所执行的语句。它们是创建表、增加用户、删除表中的列、创建触发器等的语句例如：CREATETABLE、CREATEUSER、CREATEPACKAGE、ALTERTABLE、CREATEPROCEDURE拓明科技Oracle数据库内部培训资料DDL（数据定义语言）修改Oracle数据字典所执行的语句。它们是创建表、增加用户4访问和修改数据库中数据所执行的语句。这些语句包括SELECT、INSERT、MERGE、UPDATE和DELETE命令拓明科技Oracle数据库内部培训资料DML（数据操纵语言）访问和修改数据库中数据所执行的语句。这些语句包括SELECT5Oracle执行一条语句从开始到结束经过4个步骤：分析对提交的语句进行语法和语义检查优化生成一个可在数据库中用来执行语句的最佳计划行资源生成为会话取得最佳计划和建立执行计划语句执行完成实际执行查询的行资源生成步骤的输出拓明科技Oracle数据库内部培训资料语句执行过程Oracle执行一条语句从开始到结束经过4个步骤：拓明科技O6语法分析查询是不是合法的语句语义分析如果是合法的语句，还需要检查是否具有访问权限、访问的列是否在所访问的表中、是否存在歧义共享池检查如果是DML语句还需要进行共享池检查，此语句是否已经被其他用户分析过且Oracle可重用已经执行的工作，如果是就进行软分析，如果不是就进行硬分析。DDL总是硬分析，从不重用拓明科技Oracle数据库内部培训资料分析语法分析拓明科技Oracle数据库内部培训资料分析7共享池概念是Oracle体系结构的一个关键成分。共享池是Oracle共享全局区（SGA）中的一块内存，其中库高速缓存区存放以前执行过的SQL语句、PL/SQL代码，数据字典高速缓存区存放字典信息硬分析软分析拓明科技Oracle数据库内部培训资料共享池检查共享池概念是Oracle体系结构的一个关键成分。共享池是Or8分析查询检查语法验证语义计算散列值找到一个匹配验证存在一个与我们的查询完全相同的查询（引用相同的对象）确认我们具有访问所有引用对象的必须的权限验证语句在相同的环境中执行如果是软分析就直接跳过下面的优化和行资源生成这一重要步骤拓明科技Oracle数据库内部培训资料硬分析和软分析分析查询拓明科技Oracle数据库内部培训资料硬分析和软分析9所有DML语句第一次提交给Oracle时，在它们的生存期中至少要优化一次。优化发生在硬分析中。语句义上和语法上完全相同，且执行环境也相同的语句的执行可以利用以前硬分析的工作。在此情形下，对它们进行软分析。硬分析包括查询优化步骤。这是一种费劲的、CPU密集型的处理过程，所花的时间可能比语句的实际执行时间还要长。查询优化工作是要找到可能是最好的执行计划拓明科技Oracle数据库内部培训资料优化和行资源生成所有DML语句第一次提交给Oracle时，在它们的生存期中至10基于规则的优化程序（RBO）这种优化程序基于一组规则建立查询计划。它不考虑对象尺寸、列的基数、数据的分布、排序区尺寸以及其它因素。查询优化只能利用已经编好的规则。基于成本的优化程序（CBO）这种优化程序根据收集的实际访问数据的统计信息对查询进行优化。在确定最合适的计划是，它将使用行数、数据集的尺寸以及许多其他信息。CBO将生成许多（或许数千）可能的查询计划（解决查询的预备途径），并给每个查询计划赋予一个数值成本。采用具有最低成本的查询计划拓明科技Oracle数据库内部培训资料优化程序模式基于规则的优化程序（RBO）拓明科技Oracle数据库内部培11优化之后就是行资源生成，行资源生成器是Oracle中的一个软件，它接受优化程序的输出，将它格式化为实际的执行计划。例如：在使用SQL*PLUS中的AUTOTRACE实用程序时，它打印查询计划拓明科技Oracle数据库内部培训资料行资源生成优化之后就是行资源生成，行资源生成器是Oracle中的一个软12语句执行的最后步骤是利用资源生成器的输出实际执行语句。它是DML执行中唯一的强制性的步骤。可以跳过分析、优化和行资源生成步骤。反复执行语句。因为这是目前为止最高效的方法，只要有可能，我们都希望建立符合“分析一次，执行许多次”规则的应用程序。拓明科技Oracle数据库内部培训资料执行语句执行的最后步骤是利用资源生成器的输出实际执行语句。它是D13OVERALLTOTALSFORALLNON-RECURSIVESTATEMENTScallcountcpuelapseddiskquerycurrentrows-----------------------------------------------------------------------Parse120.040.120200Execute120.020.0402682923Fetch48560.860.8107703072673-----------------------------------------------------------------------total48800.930.98079732972696count：提供OCI过程的执行次数CPU：提供执行CPU所花的时间单位是秒Elapsed：提供了执行时所花的时间。单位是秒。这个参数值等于用户响应时间Disk：提供缓存区从磁盘读取的次数Query：以一致性模式从缓存区获得数据的次数Current：以当前模式从缓存区获得数据的次数ROWs：返回调用或执行调用时，处理的数据行的数量。拓明科技Oracle数据库内部培训资料

OVERALLTOTALSFORALLNON-REC1410:34:55SQL>select*fromdept;Elapsed:00:00:00.10ExecutionPlan----------------------------------------------------------Planhashvalue:3383998547--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|3(0)|00:00:01||1|TABLEACCESSFULL|DEPT|4|120|3(0)|00:00:01|--------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------279recursivecalls0dbblockgets58consistentgets8physicalreads0redosize1630bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)4rowsprocessed拓明科技Oracle数据库内部培训资料

10:34:55SQL>select*fromde1510:35:00SQL>/Elapsed:00:00:00.02ExecutionPlan----------------------------------------------------------Planhashvalue:3383998547--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|3(0)|00:00:01||1|TABLEACCESSFULL|DEPT|4|120|3(0)|00:00:01|--------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------0recursivecalls0dbblockgets8consistentgets0physicalreads0redosize802bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)4rowsprocessed拓明科技Oracle数据库内部培训资料

10:35:00SQL>/拓明科技Oracle数据库内部1611:31:43SQL>l1*select*fromdeptorderby1Elapsed:00:00:00.03ExecutionPlan----------------------------------------------------------Planhashvalue:120787663---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|4(25)|00:00:01||1|SORTORDERBY||4|120|4(25)|00:00:01||2|TABLEACCESSFULL|DEPT|4|120|3(0)|00:00:01|---------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------0recursivecalls0dbblockgets7consistentgets0physicalreads0redosize794bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)4rowsprocessed拓明科技Oracle数据库内部培训资料

11:31:43SQL>l拓明科技Oracle数据库内部17altersystemflushshared_pool;altersystemflushbuffer_cache;Elapsed:00:00:00.20ExecutionPlan----------------------------------------------------------Planhashvalue:120787663---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|4(25)|00:00:01||1|SORTORDERBY||4|120|4(25)|00:00:01||2|TABLEACCESSFULL|DEPT|4|120|3(0)|00:00:01|---------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------1195recursivecalls0dbblockgets203consistentgets32physicalreads0redosize794bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient13sorts(memory)0sorts(disk)4rowsprocessed拓明科技Oracle数据库内部培训资料

altersystemflushshared_pool18数据库缓冲区高速缓存共享池重做日志缓冲区Java池（JavaPool）大池（LargePool）流池（StreamPool）拓明科技Oracle数据库内部培训资料数据库内存结构

数据库缓冲区高速缓存拓明科技Oracle数据库内部培训资料数19

拓明科技Oracle数据库内部培训资料

SGA共享池Java池流池库高速缓存数据字典缓存DBCACHESIZELogBuffer大池拓明科技Oracle数据库内部培训资料SGA共享池Jav20用于存储

最近执行的SQL语句

最近使用的数据定义包括的内存结构

库高速缓存

数据字典高速缓存拓明科技Oracle数据库内部培训资料共享池拓明科技Oracle数据库内部培训资料共享池21库高速缓存包括以下两个结构：共享SQL：共享SQL为针对数据库运行的SQL语句存储并共享执行计划和语法分析树。下次运行同一SQL语句时，这个语句就能利用共享SQL提供的语法分析信息来加快其执行速度。要确保SQL语句随时可以使用共享SQL区，文本、方案和绑定变量必须完全相同。共享PL/SQL：共享PL/SQL区存储并共享最近执行的PL/SQL语句。经过语法分析和编译的程序单元和过程（函数、程序包和触发器）都存储在这个区中拓明科技Oracle数据库内部培训资料库高速缓存库高速缓存包括以下两个结构：拓明科技Oracle数据库内部培22数据库中最近使用的定义的集合包括与数据库文件、表、索引、列、用户、权限和其它数据库对象相关的信息在语法分析阶段，服务器进程会在数据字典中查找用于解析对象名和验证访问的信息将数据字典信息高速缓存到内存中，可缩短查询和DML的响应时间拓明科技Oracle数据库内部培训资料数据字典高速缓存数据库中最近使用的定义的集合拓明科技Oracle数据库内部培23存储已从数据文件中检索到的数据块的副本能够大幅提高获取和更新数据时的性能通过LRU算法管理

处理查询时，Oracle服务器进程在数据库缓冲区高速缓存中查找任何所需的块。如果未在数据库缓冲区高速缓存中找到这个块，服务器进程就从数据文件读取这个块，并在数据库缓冲区高速缓存中放置一个副本。由于对同一个块的后续请求可以在内存中找到这个块，因此这些请求可能不需要进行物理读取。拓明科技Oracle数据库内部培训资料数据库缓冲区高速缓存存储已从数据文件中检索到的数据块的副本拓明科技Oracle数24Oracle服务器使用LRU（最近最少使用算法）算法来释放近期未被访的缓冲区，以便在数据库缓冲区高速缓存中为新块腾出空间拓明科技Oracle数据库内部培训资料

Oracle服务器使用LRU（最近最少使用算法）算法来释25记录对数据库数据块所做的全部更改主要用于恢复其中记录的更改称作重做条目重做条目包含用于重新构造或重做更改的信息重做日志缓冲区是一个循环缓冲区，它包含对数据文件块所做的各种更改。此信息存储在重做条目中。重做条目包含将数据恢复到使用INSERT、UPDATE、DELETE、CREATE、ALTER、或DROP操作进行更改前的状态所需要的信息。拓明科技Oracle数据库内部培训资料重做日志缓冲区记录对数据库数据块所做的全部更改拓明科技Oracle数据库内26Statistics----------------------------------------------------------25recursivecalls0dbblockgets2962024consistentgets45465physicalreads0redosize3972bytessentviaSQL*Nettoclient531bytesreceivedviaSQL*Netfromclient3SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)22rowsprocessed拓明科技Oracle数据库内部培训资料

Statistics拓明科技Oracle数据库内部培训资料27谢谢大家拓明科技Oracle数据库内部培训资料

谢谢大家拓明科技Oracle数据库内部培训资料28SQL语句的执行过程以及数据库内存结构拓明科技Oracle数据库内部培训资料SQL语句的执行过程以及数据库内存结构拓明科技Oracle数29♦SQL语句的执行过程

♦数据库的内存结构拓明科技Oracle数据库内部培训资料♦SQL语句的执行过程

♦数据库的内存结构拓明科技Ora30DDL（数据定义语言）DML（数据操纵语言）拓明科技Oracle数据库内部培训资料SQL语句的类型DDL（数据定义语言）拓明科技Oracle数据库内部培训资料31修改Oracle数据字典所执行的语句。它们是创建表、增加用户、删除表中的列、创建触发器等的语句例如：CREATETABLE、CREATEUSER、CREATEPACKAGE、ALTERTABLE、CREATEPROCEDURE拓明科技Oracle数据库内部培训资料DDL（数据定义语言）修改Oracle数据字典所执行的语句。它们是创建表、增加用户32访问和修改数据库中数据所执行的语句。这些语句包括SELECT、INSERT、MERGE、UPDATE和DELETE命令拓明科技Oracle数据库内部培训资料DML（数据操纵语言）访问和修改数据库中数据所执行的语句。这些语句包括SELECT33Oracle执行一条语句从开始到结束经过4个步骤：分析对提交的语句进行语法和语义检查优化生成一个可在数据库中用来执行语句的最佳计划行资源生成为会话取得最佳计划和建立执行计划语句执行完成实际执行查询的行资源生成步骤的输出拓明科技Oracle数据库内部培训资料语句执行过程Oracle执行一条语句从开始到结束经过4个步骤：拓明科技O34语法分析查询是不是合法的语句语义分析如果是合法的语句，还需要检查是否具有访问权限、访问的列是否在所访问的表中、是否存在歧义共享池检查如果是DML语句还需要进行共享池检查，此语句是否已经被其他用户分析过且Oracle可重用已经执行的工作，如果是就进行软分析，如果不是就进行硬分析。DDL总是硬分析，从不重用拓明科技Oracle数据库内部培训资料分析语法分析拓明科技Oracle数据库内部培训资料分析35共享池概念是Oracle体系结构的一个关键成分。共享池是Oracle共享全局区（SGA）中的一块内存，其中库高速缓存区存放以前执行过的SQL语句、PL/SQL代码，数据字典高速缓存区存放字典信息硬分析软分析拓明科技Oracle数据库内部培训资料共享池检查共享池概念是Oracle体系结构的一个关键成分。共享池是Or36分析查询检查语法验证语义计算散列值找到一个匹配验证存在一个与我们的查询完全相同的查询（引用相同的对象）确认我们具有访问所有引用对象的必须的权限验证语句在相同的环境中执行如果是软分析就直接跳过下面的优化和行资源生成这一重要步骤拓明科技Oracle数据库内部培训资料硬分析和软分析分析查询拓明科技Oracle数据库内部培训资料硬分析和软分析37所有DML语句第一次提交给Oracle时，在它们的生存期中至少要优化一次。优化发生在硬分析中。语句义上和语法上完全相同，且执行环境也相同的语句的执行可以利用以前硬分析的工作。在此情形下，对它们进行软分析。硬分析包括查询优化步骤。这是一种费劲的、CPU密集型的处理过程，所花的时间可能比语句的实际执行时间还要长。查询优化工作是要找到可能是最好的执行计划拓明科技Oracle数据库内部培训资料优化和行资源生成所有DML语句第一次提交给Oracle时，在它们的生存期中至38基于规则的优化程序（RBO）这种优化程序基于一组规则建立查询计划。它不考虑对象尺寸、列的基数、数据的分布、排序区尺寸以及其它因素。查询优化只能利用已经编好的规则。基于成本的优化程序（CBO）这种优化程序根据收集的实际访问数据的统计信息对查询进行优化。在确定最合适的计划是，它将使用行数、数据集的尺寸以及许多其他信息。CBO将生成许多（或许数千）可能的查询计划（解决查询的预备途径），并给每个查询计划赋予一个数值成本。采用具有最低成本的查询计划拓明科技Oracle数据库内部培训资料优化程序模式基于规则的优化程序（RBO）拓明科技Oracle数据库内部培39优化之后就是行资源生成，行资源生成器是Oracle中的一个软件，它接受优化程序的输出，将它格式化为实际的执行计划。例如：在使用SQL*PLUS中的AUTOTRACE实用程序时，它打印查询计划拓明科技Oracle数据库内部培训资料行资源生成优化之后就是行资源生成，行资源生成器是Oracle中的一个软40语句执行的最后步骤是利用资源生成器的输出实际执行语句。它是DML执行中唯一的强制性的步骤。可以跳过分析、优化和行资源生成步骤。反复执行语句。因为这是目前为止最高效的方法，只要有可能，我们都希望建立符合“分析一次，执行许多次”规则的应用程序。拓明科技Oracle数据库内部培训资料执行语句执行的最后步骤是利用资源生成器的输出实际执行语句。它是D41OVERALLTOTALSFORALLNON-RECURSIVESTATEMENTScallcountcpuelapseddiskquerycurrentrows-----------------------------------------------------------------------Parse120.040.120200Execute120.020.0402682923Fetch48560.860.8107703072673-----------------------------------------------------------------------total48800.930.98079732972696count：提供OCI过程的执行次数CPU：提供执行CPU所花的时间单位是秒Elapsed：提供了执行时所花的时间。单位是秒。这个参数值等于用户响应时间Disk：提供缓存区从磁盘读取的次数Query：以一致性模式从缓存区获得数据的次数Current：以当前模式从缓存区获得数据的次数ROWs：返回调用或执行调用时，处理的数据行的数量。拓明科技Oracle数据库内部培训资料

OVERALLTOTALSFORALLNON-REC4210:34:55SQL>select*fromdept;Elapsed:00:00:00.10ExecutionPlan----------------------------------------------------------Planhashvalue:3383998547--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|3(0)|00:00:01||1|TABLEACCESSFULL|DEPT|4|120|3(0)|00:00:01|--------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------279recursivecalls0dbblockgets58consistentgets8physicalreads0redosize1630bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)4rowsprocessed拓明科技Oracle数据库内部培训资料

10:34:55SQL>select*fromde4310:35:00SQL>/Elapsed:00:00:00.02ExecutionPlan----------------------------------------------------------Planhashvalue:3383998547--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|3(0)|00:00:01||1|TABLEACCESSFULL|DEPT|4|120|3(0)|00:00:01|--------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------0recursivecalls0dbblockgets8consistentgets0physicalreads0redosize802bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)4rowsprocessed拓明科技Oracle数据库内部培训资料

10:35:00SQL>/拓明科技Oracle数据库内部4411:31:43SQL>l1*select*fromdeptorderby1Elapsed:00:00:00.03ExecutionPlan----------------------------------------------------------Planhashvalue:120787663---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|4(25)|00:00:01||1|SORTORDERBY||4|120|4(25)|00:00:01||2|TABLEACCESSFULL|DEPT|4|120|3(0)|00:00:01|---------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------0recursivecalls0dbblockgets7consistentgets0physicalreads0redosize794bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)4rowsprocessed拓明科技Oracle数据库内部培训资料

11:31:43SQL>l拓明科技Oracle数据库内部45altersystemflushshared_pool;altersystemflushbuffer_cache;Elapsed:00:00:00.20ExecutionPlan----------------------------------------------------------Planhashvalue:120787663---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|4(25)|00:00:01||1|SORTORDERBY||4|120|4(25)|00:00:01||2|TABLEACCESSFULL|DEPT|4|120|3(0)|00:00:01|---------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------1195recursivecalls0dbblockgets203consistentgets32physicalreads0redosize794bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient13sorts(memory)0sorts(disk)4rowsprocessed拓明科技Oracle数据库内部培训资料

altersystemflushshared_pool46数据库缓冲区高速缓存共享池重做日志缓冲区Java池（JavaPool）大池（LargePool）流池（StreamPool）拓明科技Oracle数据库内部培训资料数据库内存结构

数据库缓冲区高速缓存拓明