Oracle RDBMS SQL语句处理流程.ppt

,Oracle RDBMSSQL语句处理流程,赵元杰中程在线(北京)科技有限公司2009.8,2023/2/22,Oracle 数据库设计与性能,2/57,内容提要,优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤,2023/2/22,Oracle 数据库设计与性能,3/57,性能有关术语,共享池概念回顾：Shared pool是SGA的一部分，它是用来存放由应用用户发出的SQL和PL/SQL语句；Shared Pool由3个部件组成：库缓存(library cache)-用来存放应用用户刚发出的SQL及PL/SQL语句、存储过程、函数、包、触发器、同义词PL/SQL包及JAVA类库等对象信息；数据字典(Data Dictionary Cache)-表、列等信息存储在该区；用户全局区（User Global Area）-使用共享服务器选件时，用户全局区才有用，用户全局区用于存放用户会话的应用,2023/2/22,Oracle 数据库设计与性能,4/57,性能有关术语,Oracle共享池库高速缓存工作方式：用来缓存共享的SQL statements 和PL/SQL块，这些可以给所有的连接用户共享由LRU(最小使用最先淘汰算法)来管理不是FIFO(先进先出)算法管理！Oracle如何知道你的SQL语句是否在里面先通过一个hash算法将Statement text运算成一个hash数值然后通过这个hash值在共享池中查找,2023/2/22,Oracle 数据库设计与性能,5/57,性能有关术语,SQL语句分析SQL语法分析(Syntactical Analysis)语法正确性分析，如关键字拼写等语义分析(Semantic Analysis)当前用户访问表是否有权限等存入共享池(shared pool)当前SQL语句语法和语义正确后存储在SGA的共享词内，可供当前会话再次使用或其他用户使用,2023/2/22,Oracle 数据库设计与性能,6/57,性能有关术语,SQL语句的HASH值SQL语句在语义分析后，都产生一个叫SQL Hsah Values的值SQL Hsah Values是由Hash函数产生的，在数据库中相同的SQL语句具有相同的Hash值Oracle系统通过SQL Hash 值来判断某个语句是否出现过运行过程中，可从V$SQLTEXT查询到SQL语句及其Hash值：,SQL SELECT a.username 用户名,a.sid SID号,a.serial#序列号,2 b.id1 ID1,c.sql_text SQL语句 3 FROM v$session a,v$lock b,v$sqltext c 4 WHERE a.lockwait is not null AND a.lockwait=b.kaddr 5 and a.sql_address=c.address 6*and a.sql_hash_value=c.hash_value;用户名 SID号 序列号 ID1 SQL语句-INMON 18 57968 196685 update tst2 set sal=98765 where name=zyj,2023/2/22,Oracle 数据库设计与性能,7/57,性能有关术语,SQL语句重新加载如果在SHARED POOL中找不到当前会话所发出的SQL语句，则Oracle系统重新加载重新加载的SQL语句要做下面的工作：语法正确性分析如关键字拼写等,2023/2/22,Oracle 数据库设计与性能,8/57,性能有关术语,V$libaraycache有关术语命名空间：SQL AREA,TABLE/PROCEDURE,BODY,TRIGGER 等gets:(parse)每当一条语句被分析一次时，该语句对应的名称空间的gets加1gethits:分析时在对应的名称空间找到已经存在时加1，其分析后的代码和执行计划在内存中找到了，不再执行硬分析，直接使用pins:(excution)每当一条语句执行一次时，该语句对应的名称空间的的pins加1reloads:(parse)因为找到的分析代码版本已经过期或作废而被重新硬分析的次数invalidations:因为数据词典发生变化，该语句被标记成失效，被迫重新做硬分析的次数,2023/2/22,Oracle 数据库设计与性能,9/57,性能有关术语,绑定变量绑定变量就是将类似的SQL语句的变化部分采用变量替代，从而减少Oracle系统类似语句的重新分析(硬分析)如果采用绑定变量，系统只做软分析工作SQL语句硬分析原因：没有共享的SQL(没有使用绑定变量),共享SQL重新硬分析了（查询V$sqlarea的parse_calls和excutions字段，如果某个SQL对应的parse_calls接近excutions数，说明该SQL经常被重新硬分析）注意：并不是绑定变量就是最好,2023/2/22,Oracle 数据库设计与性能,10/57,性能有关术语,共享光标(CURSOR_SHARING)绑定变量原因：没有共享的SQL硬分析注意：并不是绑定变量就是最好,2023/2/22,Oracle 数据库设计与性能,11/57,性能有关术语,SQL语句执行计划cost指cbo中这一步所耗费的资源，这个值是相对值card是指计划中这一步所处理的行数bytes指cbo中这一步所处理所有记录的字节数，是估算出来的一组值。,2023/2/22,Oracle 数据库设计与性能,12/57,内容提要,优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤,2023/2/22,Oracle 数据库设计与性能,13/57,1 发出查询余款的SQL语句，如：先查询帐户余额：SQL语句通过SGA得到服务器进程；服务器进程检查共享池中有无该条语句，无该语句则将放置共享池中并准备运行；执行SQL语句，把存放有余款的数据块从数据文件中读到SGA的数据高速缓冲区；显示结果，比如余款为$325。,Select account_balance From banktable Where account_number=111222333 And account_type=SAVINGS；,一个银行业务处理流程,2023/2/22,Oracle 数据库设计与性能,14/57,2 取款$25：SQL语句为：取款就是修改当前帐户的余额：1.客户进程通过SGA把SQL语句传给服务器进程；2.服务器进程查找有无该条语句，有执行；3.分析SQL语句并存入共享池；4.执行SQL语句；5.要处理的数据在数据高速缓冲区吗？是转7；6.从数据文件中读数据块到数据高速缓冲区；7.在回滚段中记录原来的数值（$325）；8.在重做日志中生成该事务的一个拷贝；9.将数据高速缓冲区中的余额改为$300；10.银行柜员机通过SGA发出工作完成信号（提交）:11.在重做日志中记录已完成事务；12.清除回滚段中的恢复信息（Undo Information）；13.顾客取钱完成。,Update Bank_table set account_balanct=300 Where account_number=111222333 And account_type=SAVINGS；,一个银行业务处理流程,2023/2/22,Oracle 数据库设计与性能,15/57,内容提要,优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤,2023/2/22,Oracle 数据库设计与性能,16/57,SQL语句从发出到执行的主要流程：,SQL 语句处理流程,2023/2/22,Oracle 数据库设计与性能,17/57,SQL语句执行基本流程：,SQL 语句处理流程,2023/2/22,Oracle 数据库设计与性能,18/57,多表连接的SQL语句执行流程：,SQL 语句处理流程,2023/2/22,Oracle 数据库设计与性能,19/57,SQL执行过程-三项主要工作,分析(Parsing)优化(Optimization)执行(Execution),2023/2/22,Oracle 数据库设计与性能,20/57,SQL执行过程-分析,语法分析(Syntactical Analysis)语义分析(Semantic Analysis)存入共享池(shared pool),2023/2/22,Oracle 数据库设计与性能,21/57,SQL执行过程:1.语法分析,各表示符号查询/建议语法,select ename,job,d.deptno,dname from emp e dept dwhere e.deptno=d.deptno and e.job=CLERK“order deptno,SELECT FROM WHERE ORDER BY,2023/2/22,Oracle 数据库设计与性能,22/57,SQL执行过程:2.语义分析,解决引用关系(锁-latches)检验权限,SELECT ename,job,d.deptno,dname FROM emp e,dept dWHERE e.deptno=d.deptno AND e.job=CLERK ORDER BY deptno,SCOTT.EMP(table)ENAMEJOBDEPTNO,SCOTT.DEPT(table)DEPTNODNAME,Schema SCOTT,Data Dictionary,2023/2/22,Oracle 数据库设计与性能,23/57,SQL执行过程:3a.进入共享池,转化为Hash SQL 并共享池中查到,SELECT ename,job,d.deptno,dname FROM emp e,dept dWHERE e.deptno=d.deptno AND e.job=CLERK ORDER BY d.deptno,628938992,HASH,System Global Area,Shared Pool,Shared SQL Area,User 1,User 2,User 3,Oracle Instance,4,2023/2/22,Oracle 数据库设计与性能,24/57,SQL执行过程:3a.共享池工作,如果共享池没找到就加载到共享池,SELECT ename,job,d.deptno,dname FROM emp e,dept dWHERE e.deptno=d.deptno AND e.job=CLERK ORDER BY d.deptno,628938992,V$SQL(Data Dictionary view into SQL in Shared Pool)HASH_VALUE SQL_TEXT-619739417 SELECT COUNT(*)FROM USER_POLICIES V WHERE V.OB.619739417 SELECT COUNT(*)FROM USER_POLICIES V WHERE V.OB.628938992 SELECT ename,job,e.deptno,dname FROM emp e,d.636388251 insert into ccol$(con#,obj#,intcol#,pos#,col#)val.,HASH,5,6,2023/2/22,Oracle 数据库设计与性能,25/57,SQL执行过程:优化,Final Execution Plan/Row Source Generator 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT(JOIN)3 2 TABLE ACCESS(FULL)OF DEPT 4 1 SORT(JOIN)5 4 TABLE ACCESS(FULL)OF EMP,SELECT ename,job,d.deptno,dname,Data Dictionary,评估不同访问路径(包括 latches)确定最佳路径，并保持在共享池中,Optimizer,Plan A:Cost=5,Plan B:Cost=3,Plan C:Cost=10,2023/2/22,Oracle 数据库设计与性能,26/57,SQL执行过程:执行,在私有 SQL 区分配光标绑定变量-Bind values运行光标INSERT/UPDATE/DELETE锁/修改SELECT识别活动数据集(active-set)从光标中返回数据关闭光标,2023/2/22,Oracle 数据库设计与性能,27/57,SQL执行过程-要点,SQL语句的执行要经过下面的步骤：解析SQL-在共享池中找该语句；检查语法；执行和返回结果；解析SQL：检查安全性；检查SQL语法；可能SQL语句重写。执行：创建执行计划；捆绑执行计划；执行计划执行；取出结果。显示结果-包括排序、转换和重格式化；转换结果集-对内置函数的结果进行转换。,2023/2/22,Oracle 数据库设计与性能,28/57,SQL执行过程-要点,SQL语句的解析：接收SQL到共享池；检查语法等；重新书写查询(Query Rewrite)：如果创建了实体视图(enable query rewrite)；Alter session set query_rewrite_enabled=true；该SQL语句采用查询重写(见实体视图)。Oracle 的 cursor_sharing参数：Force-除变量外语句完全相同使用同一个光标；Exact(默认)-语句完全相同使用同一个光标；9i/10g/11g 增加SIMILAR参数，强制共享只有文字不同的语句解释计划。,2023/2/22,Oracle 数据库设计与性能,29/57,SQL执行过程-要点,生成执行计划：优化器职能是决定最有效方法为查询服务；查询速度和查询效率：最大速度(first_rows)重点是最短时间返回结果；最小的资源(all_rows)使用最少的机器资源和磁盘资源；优化器模式由optimizer_mode参数决定：CBO-Oracle通过运行analyze分析的统计数据；RBO-Oracle使用数据字典中的索引的信息；Oracle 的optimizer_mode=choose；没有统计数据，则使用RBO，否则使用CBO；Oracle 10g 默认optimizer_mode=ALL_ROWS。,2023/2/22,Oracle 数据库设计与性能,30/57,SQL执行过程-性能统计(1),TKPROF,SELECT ename,job,d.deptno,dname FROM emp e,dept d WHERE e.deptno=d.deptno AND e.job=CLERK ORDER BY d.deptnocall count cpu elapsed-Parse 1 0.01 0.01Execute 1 0.00 0.00Fetch 2 0.00 0.00-total 4 0.01 0.01,2,2023/2/22,Oracle 数据库设计与性能,31/57,SQL执行过程-性能统计(2),SELECT COUNT(*)FROM big_user_table22739call count cpu elapsed-Parse 1 0.07 0.08Execute 1 0.00 0.00Fetch 2 0.95 1.12-total 4 1.02 1.21,2023/2/22,Oracle 数据库设计与性能,32/57,SQL执行过程-性能统计(3),SELECT username FROM big_user_table WHERE id=100call count cpu elapsed-Parse 1 0.08 0.07Execute 1 0.00 0.00Fetch 2 0.00 0.00-total 4 0.08 0.07,(分析工作代价相当高.),2023/2/22,Oracle 数据库设计与性能,33/57,SQL执行过程-共享池快速分析,最优保持执行计划,SELECT username FROM big_user_table WHERE id=100,298300393,V$SQL(Data Dictionary view into SQL in Shared Pool)HASH_VALUE SQL_TEXT-2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj.2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj.298300393 SELECT username FROM big_user_table WHERE object.4049165760 select order#,columns,types from access$where d_o.,HASH,2023/2/22,Oracle 数据库设计与性能,34/57,SQL执行过程-硬分析与软分析,硬分析与软分析（Hard-Parse vs.Soft-Parse）：Hard Parse-对SQL语句进行语法检查和语义分析，并生成执行计划和执行编码；Soft Parse-对SQL语句进行语法检查和语义分析,2023/2/22,Oracle 数据库设计与性能,35/57,SQL执行过程-软分析更好,-After added to Shared Pool-executed 4 times in a*new*session:SELECT username FROM big_user_table WHERE object_id=100call count cpu elapsed-Parse 4 0.00 0.00Execute 4 0.00 0.00Fetch 8 0.01 0.00-total 16 0.01 0.00,软分析代价较小,2023/2/22,Oracle 数据库设计与性能,36/57,SQL执行过程-软分析不足,SELECT username FROM big_user_table WHERE id=100;SELECT username FROM big_user_table WHERE object_id=250;,298300393,V$SQL(Data Dictionary view into SQL in Shared Pool)HASH_VALUE SQL_TEXT-2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj.2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj.1737037929 SELECT object_name FROM all_objects WHERE objec.298300393 SELECT object_name FROM all_objects WHERE object.4049165760 select order#,columns,types from access$where d_o.,1737037929,HASH,HASH,2023/2/22,Oracle 数据库设计与性能,37/57,在共享池中重用SQL语句：当SQL语句被传递给Oracle处理时，关键是重复使用已经在共享池中的语句，而不是让Oracle在接受语句时去准备新的语句；与共享池中的语句相一致的语句，就重用共享池中的语句；Oracle提供在数据库中存储代码的能力，当应用系统开始运行时，从数据库中读取代码（可用PL/SQL语句编制）并传递到共享池中去处理。从数据库中取出的代码是编译过的并驻留在共享池中；利用数据库中存储的程序代码设计应用系统，检查所有的事务处理以及主要的通用的过程，研究现有的应用系统并把主要的处理程序转换为数据库中存储的程序代码。在Oracle中存储代码可以通过过程、程序包、函数、触发器等来实现。,SQL代码的重用,2023/2/22,Oracle 数据库设计与性能,38/57,内容提要,一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤,2023/2/22,Oracle 数据库设计与性能,39/57,CURSOR_SHARING参数,与SHRAED_POOL_SIZE有关什么是SHARED_POOLSHARED_POOL分为两个部分第一部分为库高速缓存第二部分为字典高速缓存PL/SQL程序分析后存放在库高速缓存共享池由SHARED_POOL_SIZE参数设置,2023/2/22,Oracle 数据库设计与性能,40/57,CURSOR_SHARING参数-不同SQL,select name,address from app.employee;select name,address from app.employee;select name,address from employee;select address from emp where name=Emke,Larry;select address from emp where name=Drake,Rick;select address from emp where name=:EMP;select sysdate from dual;select to_char(sysdate,dd-mon-yy)from dual;,2023/2/22,Oracle 数据库设计与性能,41/57,CURSOR_SHARING参数,CURSOR_SHARING 可能的值:FORCEEXACT(default)SIMILAR(Oracle9i/10g/11g)CURSOR_SHARING 有三种修改:ALTER SYSTEMALTER SESSIONSPFILE(或INITsid.ora)CURSOR_SHARING_EXACT提示,2023/2/22,Oracle 数据库设计与性能,42/57,CURSOR_SHARING参数,8i R2开始引入cursor_sharing参数8i设置cursor_sharing=FORCE和EXACT(默认)9i增加SIMILAR参数值默认值是EXACT-它只允许完全相同文本的语句共享一个游标。这是早期版本的行为；SIMILAR参数值使相似语句共享同样的游标，而不危及执行计划的安全。例如：只有最优共享语句共享游标；FORCE会强迫Oracle对相似语句共享游标，但存在非最优执行计划的风险，如，最优共享和非最优共享语句会共享同一个游标。,SQL SELECT*FROM MYTABLE WHERE NAME=tomSQL SELECT*FROM MYTABLE WHERE NAME=turner,2023/2/22,Oracle 数据库设计与性能,43/57,CURSOR_SHARING_EXACT提示,CURSOR_SHARING_EXACT提示被用于在语句级控制游标共享；这个标记类似于初始化参数cursor_sharing被设置为EXACT，并屏蔽原来的初始化参数它导致语句共享采用精确匹配构建的游标。下面例子（见下一页)：,2023/2/22,Oracle 数据库设计与性能,44/57,CURSOR_SHARING_EXACT提示,CURSOR_SHARING_EXACT例子下面例子使用提示：,ALTER SYSTEM SET cursor_sharing=SIMILAR SCOPE=BOTH;-as the client run two similar SQL statementsSELECT latitude FROM uwclass.servers WHERE srvr_id=1;SELECT latitude FROM uwclass.servers WHERE srvr_id=2;SELECT latitude FROM uwclass.servers WHERE srvr_id=3;SELECT address,child_address,sql_text,sql_idFROM gv$sql WHERE sql_fulltext LIKE%uwclass%;SELECT/*+CURSOR_SHARING_EXACT*/latitude FROM uwclass.servers WHERE srvr_id=3;SELECT address,child_address,sql_text,sql_idFROM gv$sqlWHERE sql_fulltext LIKE%uwclass%;,2023/2/22,Oracle 数据库设计与性能,45/57,内容提要,一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤,2023/2/22,Oracle 数据库设计与性能,46/57,监视SQL Area 的SQL语句,V$SYSSTAT 显示 Oracle CPU 所有会话情况 V$SESSTAT 显示每个会话对 Oracle CPU 使用情况 V$SQLAREA 显示目前运行的SQL语句基本情况,2023/2/22,Oracle 数据库设计与性能,47/57,监视SQL Area 的SQL语句,查询过分分析的语句:高的百分比表明CPU花在分析语句上而不是执行上.PL/SQL程序应该采用绑定变量；加大SHARED_POOL配置,SELECT s1.value/s2.value*100 FROM v$sysstat s1,v$sysstat s2 WHERE s1.name=parse time cpu AND s2.name=cpu used by this session;,2023/2/22,Oracle 数据库设计与性能,48/57,监视SQL Area 的SQL语句,查询频繁重分析的语句:如果分析接近执行数,则表明每个语句在执行时都被分析一次.,SELECT sql_text,parse_calls,executions FROM v$sqlarea ORDER BY parse_calls desc;,2023/2/22,Oracle 数据库设计与性能,49/57,监视SQL Area 的SQL语句,下面查询结果应该比较低.表示语句不用重分析就可执行.,SELECT s1.value/s2.value FROM v$sysstat s1,v$sysstat s2 WHERE s1.name=parse count(hard)AND s1.name=execute count;,2023/2/22,Oracle 数据库设计与性能,50/57,监视SQL Area 的SQL语句,本地频繁分析的语句:如果存在很高的类似的语句,可采用绑定变量.,SELECT substr(sql_text,1,40),count(*)FROM v$sqlarea GROUP BY substr(sql_text,1,40)HAVING count(*)3 ORDER BY 2 desc;,2023/2/22,Oracle 数据库设计与性能,51/57,监视SQL Area 的SQL语句,能从缓冲区得到哪些SQL 语句在使用CPU50000 是一个随意给定的点.用 EXPLAIN 或跟踪可得到.,SELECT buffer_gets,executions,buffer_gets/executions ratio,sql_text,address,hash_value FROM v$sqlarea WHERE buffer_gets 50000 AND executions 0 ORDER BY 3 desc;,2023/2/22,Oracle 数据库设计与性能,52/57,监视SQL Area 的SQL语句,找出最消耗CPU的会话:注意：某时间点都会找出最差的语句(相对),SELECT ss.username,v.value,ss.serial#,ss.logon_time,ss.osuser,ss.machine,ss.terminal,v.sid FROM v$statname s,v$sesstat v,v$session ss WHERE s.name=CPU used by this session AND v.statistic#=s.statistic#AND ss.sid=v.sid AND v.value 0 ORDER BY 2 desc;,2023/2/22,Oracle 数据库设计与性能,53/57,内容提要,一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤,2023/2/22,Oracle 数据库设计与性能,54/57,SQL调整步骤,SQL语句调整步骤：定位频繁使用的SQL(可用statspack搜集数据)；调整SQL语句；添加合适的索引(位图或B*Tree索引)；改变优化器模式；使用语句提示；将调整语句持久化（更该代码、计划稳定性）。,2023/2/22,Oracle 数据库设计与性能,55/57,SQL调整步骤,定位频繁使用的SQL语句：方法1-使用STATSPACK捕获SQL语句：Stats$sql_summary存放使用频繁的SQL语句；阀值且数据库使用频繁，则该表存储很多数据；调整后不再需要该表的数据可删除掉。Stat$sql_summary表：所有数据是通过stat$sql_parameter表的阀值过滤后的SQL；Execution_th-SQL执行的次数(默认为100)；Disk_reads_th-SQL语句执行读磁盘次数(默认1000)；Parse_calls_th-SQL语句分析的次数(默认1000)；Buffer_gets_th-SQL语句执行缓冲区读的次数(默认10000)Stats$sql_summary 表的数据增加方法：当某个SQL语句超出阀值，则在该表添加一条SQL语句；DBA最好每一小时取样一次，这样才能使快照进行时获得位于库高速缓存的SQL语句；,Oracle 数据库设计与性能,附图：SQL 语句处理流程,SELECT ename FROM emp;,1.Parse,Shared Pool,Database Buffer,SGA,ServerProcess,UserProcess,Datafiles,2.Execute,3.Fetch,ShahLizaAtiqahAqilah,2023/2/22,Oracle 数据库设计与性能,57/57,参考资料,Oracle:Oracle Database Performance Tuning Guide B14211-01Oracle Database Administrators Guide B14231-01Donald K.Burleson:Oracle High-Performance SQL Tuning,