Oracle RDBMS SQL语句处理流程

《Oracle RDBMS SQL语句处理流程》由会员分享，可在线阅读，更多相关《Oracle RDBMS SQL语句处理流程（57页珍藏版）》请在金锄头文库上搜索。

1、Oracle RDBMSOracle RDBMSSQLSQL语句处理流程语句处理流程赵元杰中程在线中程在线( (北京北京) )科技有限公司科技有限公司2009.82009.8内容提要10g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤8/3/20242Oracle 数据库设计与性能性能有关术语|共享池概念回顾 ：Shared pool是SGA的一部分，它是用来存放由应用用户发出的SQL和PL/SQL语句 ；Shared Pool由3个部件组成 ：库缓存(library cache)-用来存放应用

2、用户刚发出的SQL及PL/SQL语句、存储过程、函数、包、触发器、同义词PL/SQL包及JAVA类库等对象信息 ；数据字典(Data Dictionary Cache)-表、列等信息存储在该区 ；用户全局区（User Global Area）-使用共享服务器选件时，用户全局区才有用，用户全局区用于存放用户会话的应用 8/3/20243Oracle 数据库设计与性能性能有关术语|Oracle共享池库高速缓存工作方式：用来缓存共享的SQL statements 和PL/SQL块，这些可以给所有的连接用户共享由LRU(最小使用最先淘汰算法)来管理不是FIFO(先进先出)算法管理！Oracle如何知道

3、你的SQL语句是否在里面先通过一个hash算法将Statement text运算成一个hash数值然后通过这个hash值在共享池中查找8/3/20244Oracle 数据库设计与性能性能有关术语|SQL语句分析SQL语法分析(Syntactical Analysis)语法正确性分析，如关键字拼写等语义分析(Semantic Analysis)当前用户访问表是否有权限等存入共享池( shared pool )当前SQL语句语法和语义正确后存储在SGA的共享词内，可供当前会话再次使用或其他用户使用8/3/20245Oracle 数据库设计与性能性能有关术语|SQL语句的HASH值SQL语句在语义分

4、析后，都产生一个叫SQL Hsah Values的值SQL Hsah Values是由Hash函数产生的，在数据库中相同的SQL语句具有相同的Hash值Oracle系统通过SQL Hash 值来判断某个语句是否出现过运行过程中，可从V$SQLTEXT查询到SQL语句及其Hash值：SQLSELECTa.username用户名用户名,a.sidSID号号,a.serial#序列号序列号,2b.id1ID1,c.sql_textSQL语句语句3FROMv$sessiona,v$lockb,v$sqltextc4WHEREa.lockwaitisnotnullANDa.lockwait=b.kadd

5、r5anda.sql_address=c.address6*anda.sql_hash_value=c.hash_value;用户名用户名SID号号序列号序列号ID1SQL语句语句-INMON1857968196685updatetst2setsal=98765wherename=zyj8/3/20246Oracle 数据库设计与性能性能有关术语|SQLSQL语句重新加载语句重新加载如果在如果在SHARED POOLSHARED POOL中找不到当前会话所发出的中找不到当前会话所发出的SQLSQL语句，则语句，则OracleOracle系统重新加载系统重新加载重新加载的重新加载的SQLSQL语

6、句要做下面的工作：语句要做下面的工作：语法正确性分析语法正确性分析如关键字拼写等如关键字拼写等8/3/20247Oracle 数据库设计与性能性能有关术语|V$libaraycacheV$libaraycache有关术语有关术语命名空间：命名空间：SQL AREA, TABLE/PROCEDURE, BODY, SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER TRIGGER 等等gets: (parse)gets: (parse)每当一条语句被分析一次时，该语句对应每当一条语句被分析一次时，该语句对应的名称空间的的名称空间的getsgets加加1 1gethi

7、tsgethits : : 分析时在对应的名称空间找到已经存在时加分析时在对应的名称空间找到已经存在时加1 1，其分析后的代码和执行计划在内存中找到了，不再执，其分析后的代码和执行计划在内存中找到了，不再执行硬分析，直接使用行硬分析，直接使用pins: (pins: (excutionexcution) )每当一条语句执行一次时，该语句对应每当一条语句执行一次时，该语句对应的名称空间的的的名称空间的的pinspins加加1 1reloads: (parse)reloads: (parse)因为找到的分析代码版本已经过期或作因为找到的分析代码版本已经过期或作废而被重新硬分析的次数废而被重新硬分析

8、的次数invalidations: invalidations: 因为数据词典发生变化，该语句被标记成因为数据词典发生变化，该语句被标记成失效，被迫重新做硬分析的次数失效，被迫重新做硬分析的次数8/3/20248Oracle 数据库设计与性能性能有关术语|绑定变量绑定变量就是将类似的绑定变量就是将类似的SQLSQL语句的变化部分采用变量语句的变化部分采用变量替代，从而替代，从而减少减少OracleOracle系统类似语句的重新分析系统类似语句的重新分析( (硬硬分析分析) )如果采用绑定变量，系统只做如果采用绑定变量，系统只做软分析工作软分析工作SQLSQL语句硬分析原因：语句硬分析原因：没有

9、共享的没有共享的SQL(SQL(没有使用绑定变量没有使用绑定变量), ), 共享共享SQLSQL重重新硬分析了（查询新硬分析了（查询V$sqlareaV$sqlarea的的parse_callsparse_calls和和excutionsexcutions字段，如果某个字段，如果某个SQLSQL对应的对应的parse_callsparse_calls接近接近excutionsexcutions数，说明该数，说明该SQLSQL经常被重新硬分析）经常被重新硬分析）注意：并不是绑定变量就是最好注意：并不是绑定变量就是最好8/3/20249Oracle 数据库设计与性能性能有关术语|共享光标(CURS

10、OR_SHARING)绑定变量原因：绑定变量原因：没有共享的没有共享的SQLSQL硬分析硬分析注意：并不是绑定变量就是最好注意：并不是绑定变量就是最好8/3/202410Oracle 数据库设计与性能性能有关术语|SQL语句执行计划costcost指指cbocbo中这一步所耗费的资源，这个值是中这一步所耗费的资源，这个值是相对值相对值cardcard是指计划中这一步所处理的行数是指计划中这一步所处理的行数bytesbytes指指cbocbo中这一步所处理所有记录的字节中这一步所处理所有记录的字节数，是估算出来的一组值。数，是估算出来的一组值。 8/3/202411Oracle 数据库设计与性能

11、内容提要10g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤8/3/202412Oracle 数据库设计与性能1 1 发出查询余款的发出查询余款的SQLSQL语句，如：语句，如： 先查询帐户余额：先查询帐户余额：先查询帐户余额：先查询帐户余额：SQLSQL语句通过语句通过语句通过语句通过SGASGA得到服务器进程；得到服务器进程；得到服务器进程；得到服务器进程；服务器进程检查共享池中有无该条语句，无该语服务器进程检查共享池中有无该条语句，无该语服务器进程检查共享池中有无该条语句，无该语服务器进程

12、检查共享池中有无该条语句，无该语句则将放置共享池中并准备运行；句则将放置共享池中并准备运行；句则将放置共享池中并准备运行；句则将放置共享池中并准备运行；执行执行执行执行SQLSQL语句，把存放有余款的数据块从数据文语句，把存放有余款的数据块从数据文语句，把存放有余款的数据块从数据文语句，把存放有余款的数据块从数据文件中读到件中读到件中读到件中读到SGASGA的数据高速缓冲区；的数据高速缓冲区；的数据高速缓冲区；的数据高速缓冲区；显示结果，比如余款为显示结果，比如余款为显示结果，比如余款为显示结果，比如余款为$325$325。 Select account_balance From bankta

13、ble Where account_number=111222333 And account_type=SAVINGS；一个银行业务处理流程8/3/202413Oracle 数据库设计与性能2 2 取款取款$25$25：SQLSQL语句为：语句为： 取款就是修改当前帐户的余额：取款就是修改当前帐户的余额：取款就是修改当前帐户的余额：取款就是修改当前帐户的余额：1.1.客户进程通过客户进程通过客户进程通过客户进程通过SGASGA把把把把SQLSQL语句传给服务器进程；语句传给服务器进程；语句传给服务器进程；语句传给服务器进程；2.2.服务器进程查找有无该条语句，有执行；服务器进程查找有无该条语句

14、，有执行；服务器进程查找有无该条语句，有执行；服务器进程查找有无该条语句，有执行；3.3.分析分析分析分析SQLSQL语句并存入共享池；语句并存入共享池；语句并存入共享池；语句并存入共享池；4.4.执行执行执行执行SQLSQL语句；语句；语句；语句；5.5.要处理的数据在数据高速缓冲区吗？是转要处理的数据在数据高速缓冲区吗？是转要处理的数据在数据高速缓冲区吗？是转要处理的数据在数据高速缓冲区吗？是转7 7；6.6.从数据文件中读数据块到数据高速缓冲区；从数据文件中读数据块到数据高速缓冲区；从数据文件中读数据块到数据高速缓冲区；从数据文件中读数据块到数据高速缓冲区；7.7.在回滚段中记录原来的数

15、值（在回滚段中记录原来的数值（在回滚段中记录原来的数值（在回滚段中记录原来的数值（$325$325）；）；）；）；8.8.在重做日志中生成该事务的一个拷贝；在重做日志中生成该事务的一个拷贝；在重做日志中生成该事务的一个拷贝；在重做日志中生成该事务的一个拷贝；9.9.将数据高速缓冲区中的余额改为将数据高速缓冲区中的余额改为将数据高速缓冲区中的余额改为将数据高速缓冲区中的余额改为$300$300；10.10.银行柜员机通过银行柜员机通过银行柜员机通过银行柜员机通过SGASGA发出工作完成信号（提交）发出工作完成信号（提交）发出工作完成信号（提交）发出工作完成信号（提交）: :11.11.在重做日志

16、中记录已完成事务；在重做日志中记录已完成事务；在重做日志中记录已完成事务；在重做日志中记录已完成事务；12.12.清除回滚段中的恢复信息（清除回滚段中的恢复信息（清除回滚段中的恢复信息（清除回滚段中的恢复信息（Undo InformationUndo Information）；）；）；）；13.13.顾客取钱完成。顾客取钱完成。顾客取钱完成。顾客取钱完成。Update Bank_table set account_balanct=300 Where account_number=111222333 And account_type=SAVINGS；一个银行业务处理流程8/3/202414Ora

17、cle 数据库设计与性能内容提要10g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤8/3/202415Oracle 数据库设计与性能SQL语句从发出到执行的主要流程：语句从发出到执行的主要流程：SQL 语句处理流程8/3/202416Oracle 数据库设计与性能SQL语句执行基本流程：语句执行基本流程：SQL 语句处理流程8/3/202417Oracle 数据库设计与性能多表连接的多表连接的SQL语句执行流程：语句执行流程：SQL 语句处理流程8/3/202418Oracle 数据库设计与性

18、能SQL执行过程-三项主要工作分析(Parsing)优化(Optimization)执行(Execution)8/3/202419Oracle 数据库设计与性能SQL执行过程-分析语法分析(Syntactical Analysis)语义分析(Semantic Analysis)存入共享池( shared pool )8/3/202420Oracle 数据库设计与性能SQL执行过程: 1.语法分析各表示符号查询 / 建议语法select ename, job, d.deptno, dname from emp e dept dwhere e.deptno = d.deptno and e.job

19、 = CLERK“ order deptnoSELECT FROM WHERE ORDER BY 8/3/202421Oracle 数据库设计与性能SQL执行过程: 2.语义分析解决引用关系 (锁-latches)检验权限SELECT ename, job, d.deptno, dname FROM emp e, dept dWHERE e.deptno = d.deptno AND e.job = CLERK ORDER BY deptnoSCOTT.EMP (table)ENAMEJOBDEPTNOSCOTT.DEPT (table)DEPTNODNAMESchema SCOTTData

20、Dictionary8/3/202422Oracle 数据库设计与性能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.deptno628938992HASHSystem Global AreaShared PoolShared SQL AreaUser 1User 2User 3Oracle Instance48/3/202423Oracle 数据库设计与

21、性能SQL执行过程: 3a.共享池工作如果共享池没找到就加载到共享池SELECT ename, job, d.deptno, dname FROM emp e, dept dWHERE e.deptno = d.deptno AND e.job = CLERK ORDER BY d.deptno628938992V$SQL (Data Dictionary view into SQL in Shared Pool)HASH_VALUE SQL_TEXT- - 619739417 SELECT COUNT(*) FROM USER_POLICIES V WHERE V.OB. 619739417

22、 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. .HASH5,68/3/202424Oracle 数据库设计与性能SQL执行过程: 优化Final Execution Plan / Row Source Generator 0 SELECT STATEMENT Optimizer=CHOOSE 1 0

23、MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF DEPT 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF EMPSELECT ename, job, d.deptno, dname Data Dictionary评估不同访问路径评估不同访问路径 (包括包括 latches)确定最佳路径，并保持在共享池中确定最佳路径，并保持在共享池中OptimizerPlan A : Cost = 5 Plan B : Cost = 3 Plan C : Cost = 10 8/3/202425Oracle 数据库

24、设计与性能SQL执行过程: 执行在私有 SQL 区分配光标绑定变量-Bind values运行光标INSERT/UPDATE/DELETE锁 / 修改SELECT识别活动数据集( active-set )从光标中返回数据关闭光标8/3/202426Oracle 数据库设计与性能SQL执行过程-要点SQL语句的执行要经过下面的步骤： 解析SQL-在共享池中找该语句；检查语法；执行和返回结果；解析SQL ：检查安全性；检查SQL语法；可能SQL语句重写。执行：创建执行计划；捆绑执行计划；执行计划执行；取出结果。显示结果-包括排序、转换和重格式化；转换结果集-对内置函数的结果进行转换。8/3/202

25、427Oracle 数据库设计与性能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参数，强制共享只有文字不同的语句解释计划。8/3/202428

26、Oracle 数据库设计与性能SQL执行过程-要点生成执行计划： 优化器职能是决定最有效方法为查询服务；查询速度和查询效率 ： 最大速度(first_rows)重点是最短时间返回结果； 最小的资源(all_rows)使用最少的机器资源和磁盘资源； 优化器模式由optimizer_mode参数决定： CBO-Oracle通过运行analyze分析的统计数据； RBO-Oracle使用数据字典中的索引的信息；Oracle 的optimizer_mode=choose；没有统计数据，则使用RBO，否则使用CBO；Oracle 10g 默认optimizer_mode = ALL_ROWS。8/3/2

27、02429Oracle 数据库设计与性能SQL执行过程-性能统计(1)TKPROFSELECT 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.0128/3/202430Oracle 数据库设计与性能SQL执行过程-性能统

28、计(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.218/3/202431Oracle 数据库设计与性能SQL执行过程-性能统计(3)SELECT username FROM big_user_table WHERE id = 100call count cpu elapsed- - - -Parse 1 0.08 0.07Execute 1 0.00

29、 0.00Fetch 2 0.00 0.00- - - -total 4 0.08 0.07(分析工作代价相当高.)8/3/202432Oracle 数据库设计与性能SQL执行过程-共享池快速分析最优保持执行计划SELECT username FROM big_user_table WHERE id = 100298300393V$SQL (Data Dictionary view into SQL in Shared Pool)HASH_VALUE SQL_TEXT- -2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj

30、.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.HASH8/3/202433Oracle 数据库设计与性能SQL执行过程-硬分析与软分析硬分析与软分析（硬分析与软分析（Hard-Parse vs. Soft-ParseHard-Parse vs. Soft-Parse）： Ha

31、rd Parse-Hard Parse-对对SQLSQL语句进行语句进行语法检查和语义分析语法检查和语义分析，并生，并生成执行计划和执行编码成执行计划和执行编码；Soft Parse-Soft Parse-对对SQLSQL语句进行语法检查和语义分析语句进行语法检查和语义分析8/3/202434Oracle 数据库设计与性能SQL执行过程-软分析更好-After added to Shared Pool-executed 4 times in a *new* session:SELECT username FROM big_user_table WHERE object_id = 100call

32、 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软分析代价较小软分析代价较小8/3/202435Oracle 数据库设计与性能SQL执行过程-软分析不足SELECT username FROM big_user_table WHERE id = 100;SELECT username FROM big_user_table WHERE object_id = 250;298300393V$SQL (Data Dictionary vie

33、w 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.404916576

34、0 select order#,columns,types from access$ where d_o.1737037929HASHHASH8/3/202436Oracle 数据库设计与性能在共享池中重用在共享池中重用在共享池中重用在共享池中重用SQLSQL语句：语句：语句：语句：当当当当SQLSQL语语语语句句句句被被被被传传传传递递递递给给给给OracleOracle处处处处理理理理时时时时，关关关关键键键键是是是是重重重重复复复复使使使使用用用用已已已已经经经经在在在在共共共共享享享享池中的语句，而不是让池中的语句，而不是让池中的语句，而不是让池中的语句，而不是让OracleOracl

35、e在接受语句时去准备新的语句；在接受语句时去准备新的语句；在接受语句时去准备新的语句；在接受语句时去准备新的语句；与共享池中的语句相一致的语句，就重用共享池中的语句；与共享池中的语句相一致的语句，就重用共享池中的语句；与共享池中的语句相一致的语句，就重用共享池中的语句；与共享池中的语句相一致的语句，就重用共享池中的语句；OracleOracleOracleOracle提提提提供供供供在在在在数数数数据据据据库库库库中中中中存存存存储储储储代代代代码码码码的的的的能能能能力力力力，当当当当应应应应用用用用系系系系统统统统开开开开始始始始运运运运行行行行时时时时，从从从从数数数数据据据据库库库库中

36、中中中读读读读取取取取代代代代码码码码（可可可可用用用用PL/SQLPL/SQLPL/SQLPL/SQL语语语语句句句句编编编编制制制制）并并并并传传传传递递递递到到到到共共共共享享享享池池池池中中中中去处理。从数据库中取出的代码是编译过的并驻留在共享池中；去处理。从数据库中取出的代码是编译过的并驻留在共享池中；去处理。从数据库中取出的代码是编译过的并驻留在共享池中；去处理。从数据库中取出的代码是编译过的并驻留在共享池中；利利利利用用用用数数数数据据据据库库库库中中中中存存存存储储储储的的的的程程程程序序序序代代代代码码码码设设设设计计计计应应应应用用用用系系系系统统统统，检检检检查查查查所所

37、所所有有有有的的的的事事事事务务务务处处处处理理理理以以以以及及及及主主主主要要要要的的的的通通通通用用用用的的的的过过过过程程程程，研研研研究究究究现现现现有有有有的的的的应应应应用用用用系系系系统统统统并并并并把把把把主主主主要要要要的的的的处处处处理理理理程程程程序序序序转转转转换换换换为为为为数数数数据据据据库库库库中中中中存存存存储储储储的的的的程程程程序序序序代代代代码码码码。在在在在OracleOracleOracleOracle中中中中存存存存储储储储代代代代码码码码可可可可以以以以通过过程、程序包、函数、触发器等来实现通过过程、程序包、函数、触发器等来实现通过过程、程序包、函

38、数、触发器等来实现通过过程、程序包、函数、触发器等来实现 。 SQL代码的重用8/3/202437Oracle 数据库设计与性能内容提要10g9iR2一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤8/3/202438Oracle 数据库设计与性能CURSOR_SHARING参数与SHRAED_POOL_SIZE有关什么是SHARED_POOLSHARED_POOL分为两个部分第一部分为库高速缓存第二部分为字典高速缓存PL/SQL程序分析后存放在库高速缓存共享池由共享池由SHARED_POOL_SIZE参数设置参数设置

39、 8/3/202439Oracle 数据库设计与性能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

40、= :EMP; select sysdate from dual; select to_char(sysdate, dd-mon-yy) from dual;8/3/202440Oracle 数据库设计与性能CURSOR_SHARING参数CURSOR_SHARING 可能的值:FORCEFORCEEXACT (default)EXACT (default)SIMILAR (SIMILAR (Oracle9i/10g/11gOracle9i/10g/11g) )CURSOR_SHARING 有三种修改:ALTER SYSTEMALTER SYSTEMALTER SESSIONALTER SES

41、SIONSPFILE(SPFILE(或或INITsid.oraINITsid.ora) )CURSOR_SHARING_EXACT提示8/3/202441Oracle 数据库设计与性能CURSOR_SHARING参数8i R28i R2开始引入开始引入cursor_sharingcursor_sharing参数参数8i8i设置设置cursor_sharing=FORCEcursor_sharing=FORCE和和EXACT(EXACT(默认默认) )9i9i增加增加SIMILARSIMILAR参数值参数值默认值是默认值是默认值是默认值是EXACT-EXACT-它只允许完全相同文本的语句共享一个

42、游标。这是它只允许完全相同文本的语句共享一个游标。这是它只允许完全相同文本的语句共享一个游标。这是它只允许完全相同文本的语句共享一个游标。这是早期版本的行为早期版本的行为早期版本的行为早期版本的行为；SIMILARSIMILAR参数值使相似语句共享同样的游标，而不危及执行计划的参数值使相似语句共享同样的游标，而不危及执行计划的参数值使相似语句共享同样的游标，而不危及执行计划的参数值使相似语句共享同样的游标，而不危及执行计划的安全。例如：只有最优共享语句共享游标；安全。例如：只有最优共享语句共享游标；安全。例如：只有最优共享语句共享游标；安全。例如：只有最优共享语句共享游标；FORCEFORCE

43、会强迫会强迫会强迫会强迫OracleOracle对相似语句共享游标，但存在非最优执行计划对相似语句共享游标，但存在非最优执行计划对相似语句共享游标，但存在非最优执行计划对相似语句共享游标，但存在非最优执行计划的风险，如，最优共享和非最优共享语句会共享同一个游标。的风险，如，最优共享和非最优共享语句会共享同一个游标。的风险，如，最优共享和非最优共享语句会共享同一个游标。的风险，如，最优共享和非最优共享语句会共享同一个游标。SQLSELECT*FROMMYTABLEWHERENAME=tomSQLSELECT*FROMMYTABLEWHERENAME=turner8/3/202442Oracle

44、数据库设计与性能CURSOR_SHARING_EXACT提示CURSOR_SHARING_EXACT提示被用于在语句级控制游标共享；这个标记类似于初始化参数cursor_sharing被设置为EXACT，并屏蔽原来的初始化参数它导致语句共享采用精确匹配构建的游标。下面例子（见下一页下面例子（见下一页下面例子（见下一页下面例子（见下一页) )：8/3/202443Oracle 数据库设计与性能CURSOR_SHARING_EXACT提示CURSOR_SHARING_EXACT例子下面例子使用提示：下面例子使用提示：ALTER SYSTEM SET ALTER SYSTEM SET cursor_

45、sharingcursor_sharing=SIMILAR SCOPE=BOTH;=SIMILAR SCOPE=BOTH; - as the client run two similar SQL statements- as the client run two similar SQL statementsSELECT latitude FROM SELECT latitude FROM uwclass.serversuwclass.servers WHERE WHERE srvr_idsrvr_id =1; =1;SELECT latitude FROM SELECT latitude FR

46、OM uwclass.serversuwclass.servers WHERE WHERE srvr_idsrvr_id =2; =2;SELECT latitude FROM SELECT latitude FROM uwclass.serversuwclass.servers WHERE WHERE srvr_idsrvr_id =3; =3; SELECT address, SELECT address, child_addresschild_address, , sql_textsql_text, , sql_idsql_idFROM FROM gv$sqlgv$sql WHERE W

47、HERE sql_fulltextsql_fulltext LIKE % LIKE %uwclassuwclass%;%;SELECT /*+ SELECT /*+ CURSOR_SHARING_EXACTCURSOR_SHARING_EXACT */ latitude FROM */ latitude FROM uwclass.serversuwclass.servers WHERE WHERE srvr_idsrvr_id = 3; = 3;SELECT address, SELECT address, child_addresschild_address, , sql_textsql_t

48、ext, , sql_idsql_idFROM FROM gv$sqlgv$sqlWHERE WHERE sql_fulltextsql_fulltext LIKE % LIKE %uwclassuwclass%;%; 8/3/202444Oracle 数据库设计与性能内容提要10g9iR2一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤8/3/202445Oracle 数据库设计与性能监视SQL Area 的SQL语句V$SYSSTAT 显示 Oracle CPU 所有会话情况 V$SESSTAT 显示每个会话对 O

49、racle CPU 使用情况 V$SQLAREA 显示目前运行的SQL语句基本情况8/3/202446Oracle 数据库设计与性能监视SQL Area 的SQL语句查询过分分析的语句查询过分分析的语句: :高的百分比表明高的百分比表明CPUCPU花在分析语句上而不是执行上花在分析语句上而不是执行上. .PL/SQLPL/SQL程序应该采用绑定变量；程序应该采用绑定变量；加大加大SHARED_POOLSHARED_POOL配置配置SELECT s1.value / s2.value * 100 SELECT s1.value / s2.value * 100 FROM FROM v$sysst

50、atv$sysstat s1, s1, v$sysstatv$sysstat s2 s2 WHERE s1.name = parse time WHERE s1.name = parse time cpucpu AND s2.name = AND s2.name = cpucpu used by this session; used by this session;8/3/202447Oracle 数据库设计与性能监视SQL Area 的SQL语句查询频繁重分析的语句查询频繁重分析的语句: :如果分析接近执行数如果分析接近执行数, ,则表明每个语句在执则表明每个语句在执行时都被分析一次行时都被

51、分析一次. .SELECT SELECT sql_textsql_text, , parse_callsparse_calls, , executions executions FROM FROM v$sqlareav$sqlarea ORDER BY ORDER BY parse_callsparse_calls descdesc; ;8/3/202448Oracle 数据库设计与性能监视SQL Area 的SQL语句下面查询结果应该比较低下面查询结果应该比较低. . 表示语句不用重分表示语句不用重分析就可执行析就可执行. .SELECT s1.value / s2.value SELECT

52、 s1.value / s2.value FROM FROM v$sysstatv$sysstat s1, s1, v$sysstatv$sysstat s2 s2 WHERE s1.name = parse count (hard) WHERE s1.name = parse count (hard) AND s1.name = execute count ; AND s1.name = execute count ;8/3/202449Oracle 数据库设计与性能监视SQL Area 的SQL语句本地频繁分析的语句:如果存在很高的类似的语句,可采用绑定变量.SELECT substr(s

53、ql_text,1,40), count(*) SELECT substr(sql_text,1,40), count(*) FROM FROM v$sqlareav$sqlarea GROUP BY substr(sql_text,1,40) GROUP BY substr(sql_text,1,40)HAVING count(*) 3HAVING count(*) 3 ORDER BY 2 ORDER BY 2 descdesc; ;8/3/202450Oracle 数据库设计与性能监视SQL Area 的SQL语句能从缓冲区得到哪些SQL 语句在使用CPU50000 是一个随意给定的点.

54、用 EXPLAIN 或跟踪可得到.SELECT SELECT buffer_getsbuffer_gets, executions, , executions, buffer_getsbuffer_gets/executions ratio, /executions ratio, sql_textsql_text, , address, address, hash_valuehash_value FROM FROM v$sqlareav$sqlarea WHERE WHERE buffer_getsbuffer_gets 50000 50000 AND executions 0 AND exe

55、cutions 0 ORDER BY 3 ORDER BY 3 descdesc ; ;8/3/202451Oracle 数据库设计与性能监视SQL Area 的SQL语句找出最消耗CPU的会话:注意：某时间点都会找出最差的语句(相对)SELECT SELECT ss.usernamess.username, , v.valuev.value, , ss.serialss.serial#,#, ss.logon_timess.logon_time, , ss.osuserss.osuser, , ss.machiness.machine, , ss.terminalss.terminal, ,

56、 v.sidv.sid FROM FROM v$statnamev$statname s, s, v$sesstatv$sesstat v, v, v$sessionv$session ssss WHERE WHERE s.names.name = CPU used by this session = CPU used by this session AND AND v.statisticv.statistic# = # = s.statistics.statistic# # AND AND ss.sidss.sid = = v.sidv.sid AND AND v.valuev.value

57、0 0 ORDER BY 2 ORDER BY 2 descdesc ; ;8/3/202452Oracle 数据库设计与性能内容提要10g9iR2一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤8/3/202453Oracle 数据库设计与性能SQL调整步骤SQL语句调整步骤： 定位频繁使用的SQL(可用statspack搜集数据)；调整SQL语句；添加合适的索引(位图或B*Tree索引)；改变优化器模式；使用语句提示；将调整语句持久化（更该代码、计划稳定性）。8/3/202454Oracle 数据库设计与性能SQL

58、调整步骤定位频繁使用的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语句执行缓冲区读的次数(默认1000

59、0)Stats$sql_summary 表的数据增加方法：当某个SQL语句超出阀值，则在该表添加一条SQL语句；DBA最好每一小时取样一次，这样才能使快照进行时获得位于库高速缓存的SQL语句；8/3/202455Oracle 数据库设计与性能附图：附图：SQL 语句处理流程语句处理流程SELECT ename FROM emp;Shared Pool Database BufferSGAServerProcessUserProcessDatafiles1. ParseShared Pool Database BufferSGAServerProcessUserProcessDatafiles2. ExecuteShared Pool Database BufferSGAServerProcessUserProcessDatafiles3. FetchShahLizaAtiqahAqilah参考资料Oracle :Oracle Database Performance Tuning Guide B14211-01Oracle Database Administrators Guide B14231-01Donald K.Burleson:Oracle High-Performance SQL Tuning 8/3/202457Oracle 数据库设计与性能