OracleRDBMSSQL语句处理流程

OracleRDBMS

SQL语句处理流程赵元杰中程在线(北京)科技2021.89/27/20231内容提要10g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数

监视SQLArea的SQL语句调整SQL步骤9/27/20232性能有关术语共享池概念回忆：Sharedpool是SGA的一局部，它是用来存放由应用用户发出的SQL和PL/SQL语句；SharedPool由3个部件组成：库缓存(librarycache)-用来存放应用用户刚发出的SQL及PL/SQL语句、存储过程、函数、包、触发器、同义词PL/SQL包及JAVA类库等对象信息；数据字典(DataDictionaryCache)-表、列等信息存储在该区；用户全局区〔UserGlobalArea〕-使用共享效劳器选件时，用户全局区才有用，用户全局区用于存放用户会话的应用9/27/20233性能有关术语Oracle共享池库高速缓存工作方式：用来缓存共享的SQLstatements和PL/SQL块，这些可以给所有的连接用户共享由LRU(最小使用最先淘汰算法)来管理不是FIFO(先进先出)算法管理！！Oracle如何知道你的SQL语句是否在里面先通过一个hash算法将Statementtext运算成一个hash数值然后通过这个hash值在共享池中查找9/27/20234性能有关术语SQL语句分析SQL语法分析(SyntacticalAnalysis)语法正确性分析，如关键字拼写等语义分析(SemanticAnalysis)当前用户访问表是否有权限等存入共享池(sharedpool)当前SQL语句语法和语义正确后存储在SGA的共享词内，可供当前会话再次使用或其他用户使用9/27/20235性能有关术语SQL语句的HASH值SQL语句在语义分析后，都产生一个叫SQLHsahValues的值SQLHsahValues是由Hash函数产生的，在数据库中相同的SQL语句具有相同的Hash值Oracle系统通过SQLHash值来判断某个语句是否出现过运行过程中，可从V$SQLTEXT查询到SQL语句及其Hash值：SQL>SELECTa.username"用户名",a.sid"SID号",a.serial#"序列号",2b.id1"ID1",c.sql_text"SQL语句"3FROMv$sessiona,v$lockb,v$sqltextc4WHEREa.lockwaitisnotnullANDa.lockwait=b.kaddr5anda.sql_address=c.address6*anda.sql_hash_value=c.hash_value;

用户名SID号序列号ID1SQL语句-------------------------------------------------------------------------------------INMON1857968196685updatetst2setsal=98765wherename='zyj'9/27/20236性能有关术语SQL语句重新加载如果在SHAREDPOOL中找不到当前会话所发出的SQL语句，那么Oracle系统重新加载重新加载的SQL语句要做下面的工作：语法正确性分析如关键字拼写等9/27/20237性能有关术语V$libaraycache有关术语命名空间：SQLAREA,TABLE/PROCEDURE,BODY,TRIGGER等gets:(parse)每当一条语句被分析一次时，该语句对应的名称空间的gets加1gethits:分析时在对应的名称空间找到已经存在时加1，其分析后的代码和执行方案在内存中找到了，不再执行硬分析，直接使用pins:(excution)每当一条语句执行一次时，该语句对应的名称空间的的pins加1reloads:(parse)因为找到的分析代码版本已经过期或作废而被重新硬分析的次数invalidations:因为数据词典发生变化，该语句被标记成失效，被迫重新做硬分析的次数9/27/20238性能有关术语绑定变量绑定变量就是将类似的SQL语句的变化局部采用变量替代，从而减少Oracle系统类似语句的重新分析(硬分析)如果采用绑定变量，系统只做软分析工作SQL语句硬分析原因：没有共享的SQL(没有使用绑定变量),共享SQL重新硬分析了〔查询V$sqlarea的parse_calls和excutions字段，如果某个SQL对应的parse_calls接近excutions数，说明该SQL经常被重新硬分析〕注意：并不是绑定变量就是最好9/27/20239性能有关术语共享光标(CURSOR_SHARING)绑定变量原因：没有共享的SQL硬分析注意：并不是绑定变量就是最好9/27/202310性能有关术语SQL语句执行方案cost指cbo中这一步所消耗的资源，这个值是相对值card是指方案中这一步所处理的行数bytes指cbo中这一步所处理所有记录的字节数，是估算出来的一组值。9/27/202311内容提要10g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数

监视SQLArea的SQL语句调整SQL步骤9/27/2023121．发出查询余款的SQL语句，如：先查询帐户余额：SQL语句通过SGA得到效劳器进程；效劳器进程检查共享池中有无该条语句，无该语句那么将放置共享池中并准备运行；执行SQL语句，把存放有余款的数据块从数据文件中读到SGA的数据高速缓冲区；显示结果，比方余款为$325。Selectaccount_balanceFrombanktableWhereaccount_number='111222333'Andaccount_type='SAVINGS'；一个银行业务处理流程9/27/2023132．取款$25：SQL语句为：取款就是修改当前帐户的余额：1.客户进程通过SGA把SQL语句传给效劳器进程；2.效劳器进程查找有无该条语句，有执行；3.分析SQL语句并存入共享池；4.执行SQL语句；5.要处理的数据在数据高速缓冲区吗？是转7；6.从数据文件中读数据块到数据高速缓冲区；7.在回滚段中记录原来的数值〔$325〕；8.在重做日志中生成该事务的一个拷贝；9.将数据高速缓冲区中的余额改为$300；10.银行柜员机通过SGA发出工作完成信号〔提交〕:11.在重做日志中记录已完成事务；12.去除回滚段中的恢复信息〔UndoInformation〕；13.顾客取钱完成。UpdateBank_tablesetaccount_balanct=300Whereaccount_number='111222333'Andaccount_type='SAVINGS'；一个银行业务处理流程9/27/202314内容提要10g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数

监视SQLArea的SQL语句调整SQL步骤9/27/202315SQL语句从发出到执行的主要流程： SQL语句处理流程9/27/202316SQL语句执行根本流程： SQL语句处理流程9/27/202317多表连接的SQL语句执行流程： SQL语句处理流程9/27/202318SQL执行过程-三项主要工作分析(Parsing)优化(Optimization)执行(Execution)9/27/202319SQL执行过程-分析语法分析(SyntacticalAnalysis)语义分析(SemanticAnalysis)存入共享池(sharedpool)9/27/202320SQL执行过程:1.语法分析各表示符号查询/建议语法selectename,job,d.deptno,dnamefromempedeptdwheree.deptno=d.deptnoande.job="CLERK“orderdeptnoSELECT<listofexpressions>FROM<listofdatasourceobjects>WHERE<condition>ORDERBY<listofexpressions>9/27/202321SQL执行过程:2.语义分析解决引用关系(锁-latches)检验权限SELECTename,job,d.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDe.job=‘CLERK’ORDERBYdeptnoSCOTT.EMP(table)ENAMEJOBDEPTNO…SCOTT.DEPT(table)DEPTNODNAME…SchemaSCOTTDataDictionary9/27/202322SQL执行过程:3a.进入共享池转化为HashSQL并共享池中查到SELECTename,job,d.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDe.job=‘CLERK’ORDERBYd.deptno628938992HASHSystemGlobalAreaSharedPoolSharedSQLAreaUser1User2User3OracleInstance49/27/202323SQL执行过程:3a.共享池工作如果共享池没找到就加载到共享池SELECTename,job,d.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDe.job=‘CLERK’ORDERBYd.deptno628938992V$SQL(DataDictionaryviewintoSQLinSharedPool)HASH_VALUESQL_TEXT---------------------------------------------------------------619739417SELECTCOUNT(*)FROMUSER_POLICIESVWHEREV.OB...619739417SELECTCOUNT(*)FROMUSER_POLICIESVWHEREV.OB...628938992SELECTename,job,e.deptno,dnameFROMempe,d...636388251insertintoccol$(con#,obj#,intcol#,pos#,col#)val......HASH5,69/27/202324SQL执行过程:优化FinalExecutionPlan/RowSourceGenerator

0SELECTSTATEMENTOptimizer=CHOOSE10MERGEJOIN21SORT(JOIN)32TABLEACCESS(FULL)OF'DEPT'41SORT(JOIN)54TABLEACCESS(FULL)OF'EMP'SELECTename,job,d.deptno,dname…DataDictionary评估不同访问路径(包括latches)确定最正确路径，并保持在共享池中OptimizerPlanA:Cost=5

PlanB:Cost=3

PlanC:Cost=10

9/27/202325SQL执行过程:执行在私有SQL区分配光标[绑定变量-Bindvalues]运行光标INSERT/UPDATE/DELETE锁/修改SELECT识别活动数据集(active-set)从光标中返回数据关闭光标9/27/202326SQL执行过程-要点SQL语句的执行要经过下面的步骤：解析SQL-在共享池中找该语句；检查语法；执行和返回结果；解析SQL：检查平安性；检查SQL语法；可能SQL语句重写。执行：创立执行方案；捆绑执行方案；执行方案执行；取出结果。显示结果-包括排序、转换和重格式化；转换结果集-对内置函数的结果进行转换。9/27/202327SQL执行过程-要点SQL语句的解析：接收SQL到共享池；检查语法等；重新书写查询(QueryRewrite)：如果创立了实体视图(enablequeryrewrite)；Altersessionsetquery_rewrite_enabled=true；该SQL语句采用查询重写(见实体视图)。Oracle的cursor_sharing参数：Force--除变量外语句完全相同使用同一个光标；Exact(默认)--语句完全相同使用同一个光标；9i/10g/11g增加SIMILAR参数，强制共享只有文字不同的语句解释方案。9/27/202328SQL执行过程-要点生成执行方案：优化器职能是决定最有效方法为查询效劳；查询速度和查询效率：最大速度(first_rows)重点是最短时间返回结果；最小的资源(all_rows)使用最少的机器资源和磁盘资源；优化器模式由optimizer_mode参数决定：CBO-Oracle通过运行analyze分析的统计数据；RBO-Oracle使用数据字典中的索引的信息；Oracle的optimizer_mode=choose；没有统计数据，那么使用RBO，否那么使用CBO；Oracle10g默认optimizer_mode=ALL_ROWS。9/27/202329SQL执行过程-性能统计(1)TKPROFSELECTename,job,d.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDe.job='CLERK'ORDERBYd.deptnocallcountcpuelapsed-------------------------------Parse10.010.01Execute10.000.00Fetch20.000.00-------------------------------total40.010.0129/27/202330SQL执行过程-性能统计(2)SELECTCOUNT(*)FROMbig_user_table

22739callcountcpuelapsed-------------------------------Parse10.070.08Execute10.000.00Fetch20.951.12-------------------------------total41.021.219/27/202331SQL执行过程-性能统计(3)SELECTusernameFROMbig_user_tableWHEREid=100callcountcpuelapsed-------------------------------Parse10.080.07Execute10.000.00Fetch20.000.00-------------------------------total40.080.07(分析工作代价相当高.)9/27/202332SQL执行过程-共享池快速分析最优保持执行方案SELECTusernameFROMbig_user_tableWHEREid=100298300393V$SQL(DataDictionaryviewintoSQLinSharedPool)HASH_VALUESQL_TEXT---------------------------------------------------------------2591785020selectobj#,type#,ctime,mtime,stime,status,dataobj...2591785020selectobj#,type#,ctime,mtime,stime,status,dataobj...298300393SELECTusernameFROMbig_user_tableWHEREobject...4049165760selectorder#,columns,typesfromaccess$whered_o......HASH9/27/202333SQL执行过程-硬分析与软分析硬分析与软分析〔Hard-Parsevs.Soft-Parse〕：HardParse-对SQL语句进行语法检查和语义分析，并生成执行方案和执行编码；SoftParse-对SQL语句进行语法检查和语义分析9/27/202334SQL执行过程-软分析更好--AfteraddedtoSharedPool--executed4timesina*new*session:SELECTusernameFROMbig_user_tableWHEREobject_id=100callcountcpuelapsed-------------------------------Parse40.000.00Execute40.000.00Fetch80.010.00-------------------------------total160.010.00软分析代价较小9/27/202335SQL执行过程-软分析缺乏SELECTusernameFROMbig_user_tableWHEREid=100;SELECTusernameFROMbig_user_tableWHEREobject_id=250;298300393V$SQL(DataDictionaryviewintoSQLinSharedPool)HASH_VALUESQL_TEXT---------------------------------------------------------------2591785020selectobj#,type#,ctime,mtime,stime,status,dataobj...2591785020selectobj#,type#,ctime,mtime,stime,status,dataobj...1737037929SELECTobject_nameFROMall_objectsWHEREobjec...298300393SELECTobject_nameFROMall_objectsWHEREobject...4049165760selectorder#,columns,typesfromaccess$whered_o......1737037929HASHHASH9/27/202336在共享池中重用SQL语句：当SQL语句被传递给Oracle处理时，关键是重复使用已经在共享池中的语句，而不是让Oracle在接受语句时去准备新的语句；与共享池中的语句相一致的语句，就重用共享池中的语句；Oracle提供在数据库中存储代码的能力，当应用系统开始运行时，从数据库中读取代码〔可用PL/SQL语句编制〕并传递到共享池中去处理。从数据库中取出的代码是编译过的并驻留在共享池中；利用数据库中存储的程序代码设计应用系统，检查所有的事务处理以及主要的通用的过程，研究现有的应用系统并把主要的处理程序转换为数据库中存储的程序代码。在Oracle中存储代码可以通过过程、程序包、函数、触发器等来实现。

SQL代码的重用9/27/202337内容提要10g9iR2一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数

监视SQLArea的SQL语句调整SQL步骤9/27/202338CURSOR_SHARING参数与SHRAED_POOL_SIZE有关什么是SHARED_POOLSHARED_POOL分为两个局部第一局部为库高速缓存第二局部为字典高速缓存PL/SQL程序分析后存放在库高速缓存共享池由SHARED_POOL_SIZE参数设置9/27/202339CURSOR_SHARING参数-不同SQLselectname,addressfromapp.employee;selectname,addressfromapp.employee;selectname,addressfromemployee;selectaddressfromempwherename=‘Emke,Larry’;selectaddressfromempwherename=‘Drake,Rick’;selectaddressfromempwherename=:EMP;selectsysdatefromdual;selectto_char(sysdate,‘dd-mon-yy’)fromdual;9/27/202340CURSOR_SHARING参数CURSOR_SHARING可能的值:FORCEEXACT(default)SIMILAR(Oracle9i/10g/11g)CURSOR_SHARING有三种修改:ALTERSYSTEMALTERSESSIONSPFILE(或INITsid.ora)CURSOR_SHARING_EXACT提示9/27/202341CURSOR_SHARING参数8iR2开始引入cursor_sharing参数8i设置cursor_sharing=FORCE和EXACT(默认)9i增加SIMILAR参数值默认值是EXACT-它只允许完全相同文本的语句共享一个游标。这是早期版本的行为；SIMILAR参数值使相似语句共享同样的游标，而不危及执行方案的平安。例如：只有最优共享语句共享游标；FORCE会强迫Oracle对相似语句共享游标，但存在非最优执行方案的风险，如，最优共享和非最优共享语句会共享同一个游标。SQL>SELECT*FROMMYTABLEWHERENAME='tom'SQL>SELECT*FROMMYTABLEWHERENAME='turner'9/27/202342CURSOR_SHARING_EXACT提示CURSOR_SHARING_EXACT提示被用于在语句级控制游标共享；这个标记类似于初始化参数cursor_sharing被设置为EXACT，并屏蔽原来的初始化参数它导致语句共享采用精确匹配构建的游标。下面例子〔见下一页)：9/27/202343CURSOR_SHARING_EXACT提示CURSOR_SHARING_EXACT例子下面例子使用提示：ALTERSYSTEMSETcursor_sharing='SIMILAR'SCOPE=BOTH;…--astheclientruntwosimilarSQLstatementsSELECTlatitudeFROMuwclass.serversWHEREsrvr_id=1;SELECTlatitudeFROMuwclass.serversWHEREsrvr_id=2;SELECTlatitudeFROMuwclass.serversWHEREsrvr_id=3;…SELECTaddress,child_address,sql_text,sql_idFROMgv$sqlWHEREsql_fulltextLIKE'%uwclass%';SELECT/*+CURSOR_SHARING_EXACT*/latitudeFROMuwclass.serversWHEREsrvr_id=3;SELECTaddress,child_address,sql_text,sql_idFROMgv$sqlWHEREsql_fulltextLIKE'%uwclass%';……9/27/202344内容提要10g9iR2一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数

监视SQLArea的SQL语句调整SQL步骤9/27/202345监视SQLArea的SQL语句V$SYSSTAT–显示OracleCPU所有会话情况V$SESSTAT–显示每个会话对OracleCPU使用情况V$SQLAREA–显示目前运行的SQL语句根本情况9/27/202346监视SQLArea的SQL语句查询过分分析的语句:高的百分比说明CPU花在分析语句上而不是执行上.PL/SQL程序应该采用绑定变量；加大SHARED_POOL配置SELECTs1.value/s2.value*100FROMv$sysstats1,v$sysstats2WHERE=‘parsetimecpu’AND=‘cpuusedbythissession’;9/27/202347监视SQLArea的SQL语句查询频繁重分析的语句:如果分析接近执行数,那么说明每个语句在执行时都被分析一次.SELECTsql_text,parse_calls,executionsFROMv$sqlareaORDERBYparse_callsdesc;9/27/202348监视SQLArea的SQL语句下面查询结果应该比较低.表示语句不用重分析就可执行.SELECTs1.value/s2.valueFROMv$sysstats1,v$sysstats2WHERE=‘parsecount(hard)’AND=‘executecount’;9/27/202349监视SQLArea的SQL语句本地频繁分析的语句:如果存在很高的类似的语句,可采用绑定变量.SELECTsubstr(sql_text,1,40),count(*)FROMv$sqlareaGROUPBYsubstr(sql_text,1,40)HAVINGcount(*)>3ORDERBY2desc;9/27/202350监视SQLArea的SQL语句能从缓冲区得到哪些SQL语句在使用CPU50000是一个随意给定的点.用EXPLAIN或跟踪可得到.SELECTbuffer_gets,executions,buffer_gets/executionsratio,sql_text,address,hash_valueFROMv$sqlareaWHEREbuffer_gets>50000ANDexecutions>0ORDERBY3desc;9/27/202351监视SQLArea的SQL语句找出最消耗CPU的会话:注意：某时间点都会找出最差的语句(相对)SELECTss.us