Oracle RDBMS SQL语句处理流程

1、2021-10-24oracle 数据库设计与性能2/5710g9ir2优化有关的术语一个银行业务处理流程理解sql执行过程关于cursor_sharing参数 监视sql area 的sql语句调整sql步骤2021-10-24oracle 数据库设计与性能3/57|共享池概念回顾 ：shared pool是sga的一部分，它是用来存放由应用用户发出的sql和pl/sql语句 ；shared pool由3个部件组成 ：库缓存(library cache)-用来存放应用用户刚发出的sql及pl/sql语句、存储过程、函数、包、触发器、同义词pl/sql包及java类库等对象信息 ；数据字典(d

2、ata dictionary cache)-表、列等信息存储在该区 ；用户全局区（user global area）-使用共享服务器选件时，用户全局区才有用，用户全局区用于存放用户会话的应用 2021-10-24oracle 数据库设计与性能4/57|oracle共享池库高速缓存工作方式： 用来缓存共享的sql statements 和pl/sql块，这些可以给所有的连接用户共享 由lru(最小使用最先淘汰算法)来管理不是fifo(先进先出)算法管理！ oracle如何知道你的sql语句是否在里面先通过一个hash算法将statement text运算成一个hash数值然后通过这个hash值在

3、共享池中查找2021-10-24oracle 数据库设计与性能5/57|sql语句分析sql语法分析(syntactical analysis)语法正确性分析，如关键字拼写等语义分析(semantic analysis)当前用户访问表是否有权限等存入共享池( shared pool )当前sql语句语法和语义正确后存储在sga的共享词内，可供当前会话再次使用或其他用户使用2021-10-24oracle 数据库设计与性能6/57|sql语句的hash值 sql语句在语义分析后，都产生一个叫sql hsah values的值 sql hsah values是由hash函数产生的，在数据库中相同的

4、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.add

5、ress 6* and a.sql_hash_value = c.hash_value ; 用户名用户名 sid号号 序列号序列号 id1 sql语句语句- - - - -inmon 18 57968 196685 update tst2 set sal=98765 where name=zyj2021-10-24oracle 数据库设计与性能7/572021-10-24oracle 数据库设计与性能8/572021-10-24oracle 数据库设计与性能9/572021-10-24oracle 数据库设计与性能10/572021-10-24oracle 数据库设计与性能11/572021-

6、10-24oracle 数据库设计与性能12/5710g9ir2优化有关的术语一个银行业务处理流程理解sql执行过程关于cursor_sharing参数 监视sql area 的sql语句调整sql步骤2021-10-24oracle 数据库设计与性能13/57 select account_balance from banktable where account_number=111222333 and account_type=savings；2021-10-24oracle 数据库设计与性能14/57update bank_table set account_balanct=300 wh

7、ere account_number=111222333 and account_type=savings；2021-10-24oracle 数据库设计与性能15/5710g9ir2优化有关的术语一个银行业务处理流程理解sql执行过程关于cursor_sharing参数 监视sql area 的sql语句调整sql步骤2021-10-24oracle 数据库设计与性能16/57sql语句从发出到执行的主要流程：语句从发出到执行的主要流程：2021-10-24oracle 数据库设计与性能17/57sql语句执行基本流程：语句执行基本流程：2021-10-24oracle 数据库设计与性能18/

8、57多表连接的多表连接的sql语句执行流程：语句执行流程：2021-10-24oracle 数据库设计与性能19/57分析(parsing)优化(optimization)执行(execution)2021-10-24oracle 数据库设计与性能20/57语法分析(syntactical analysis)语义分析(semantic analysis)存入共享池( shared pool )2021-10-24oracle 数据库设计与性能21/57各表示符号查询 / 建议语法select ename, job, d.deptno, dname from emp e dept dwhere

9、e.deptno = d.deptno and e.job = clerk“ order deptnoselect from where order by 2021-10-24oracle 数据库设计与性能22/57解决引用关系 (锁-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)deptnodna

10、meschema scottdata dictionary2021-10-24oracle 数据库设计与性能23/57转化为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 instance42021-10-24oracle

11、数据库设计与性能24/57如果共享池没找到就加载到共享池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 selec

12、t 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,62021-10-24oracle 数据库设计与性能25/57final execution plan / row source generator 0 select statement optimizer=choose 1 0 merge join 2

13、 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 2021-10-24oracle 数据库设计与性能26/57在私

14、有 sql 区分配光标绑定变量-bind values运行光标insert/update/delete锁 / 修改select识别活动数据集( active-set )从光标中返回数据关闭光标2021-10-24oracle 数据库设计与性能27/57sql语句的执行要经过下面的步骤： 解析sql-在共享池中找该语句；检查语法；执行和返回结果；解析sql ：检查安全性；检查sql语法；可能sql语句重写。执行：创建执行计划；捆绑执行计划；执行计划执行；取出结果。显示结果-包括排序、转换和重格式化；转换结果集-对内置函数的结果进行转换。2021-10-24oracle 数据库设计与性能28/57

15、sql语句的解析： 接收sql到共享池；检查语法等；重新书写查询(query rewrite) ：如果创建了实体视图(enable query rewrite)；alter session set query_rewrite_enabled=true；该sql语句采用查询重写(见实体视图)。oracle 的 cursor_sharing参数：force -除变量外语句完全相同使用同一个光标；exact(默认)-语句完全相同使用同一个光标；9i/10g/11g 增加similar参数，强制共享只有文字不同的语句解释计划。2021-10-24oracle 数据库设计与性能29/57生成执行计划：

16、优化器职能是决定最有效方法为查询服务；查询速度和查询效率 ： 最大速度(first_rows)重点是最短时间返回结果； 最小的资源(all_rows)使用最少的机器资源和磁盘资源； 优化器模式由optimizer_mode参数决定： cbo-oracle通过运行analyze分析的统计数据； rbo-oracle使用数据字典中的索引的信息；oracle 的optimizer_mode=choose；没有统计数据，则使用rbo，否则使用cbo；oracle 10g 默认optimizer_mode = all_rows。2021-10-24oracle 数据库设计与性能30/57tkprofse

17、lect 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.0122021-10-24oracle 数据库设计与性能31/57select count(*) from big_user_table22739call coun

18、t cpu elapsed- - - -parse 1 0.07 0.08execute 1 0.00 0.00fetch 2 0.95 1.12- - - -total 4 1.02 1.212021-10-24oracle 数据库设计与性能32/57select 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(分析工作代价相当高.)2021-

19、10-24oracle 数据库设计与性能33/57最优保持执行计划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.2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj. 298300393

20、 select username from big_user_table where object.4049165760 select order#,columns,types from access$ where d_o.hash2021-10-24oracle 数据库设计与性能34/572021-10-24oracle 数据库设计与性能35/57-after added to shared pool-executed 4 times in a *new* session:select username from big_user_table where object_id = 100cal

21、l count cpu elapsed - - - -parse 4 0.00 0.00execute 4 0.00 0.00fetch 8 0.01 0.00- - - -total 16 0.01 0.002021-10-24oracle 数据库设计与性能36/57select username from big_user_table where id = 100;select username from big_user_table where object_id = 250;298300393v$sql (data dictionary view into sql in shared

22、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#,colum

23、ns,types from access$ where d_o.1737037929hashhash2021-10-24oracle 数据库设计与性能37/572021-10-24oracle 数据库设计与性能38/5710g9ir2一个银行业务处理流程理解sql执行过程关于cursor_sharing参数 监视sql area 的sql语句调整sql步骤2021-10-24oracle 数据库设计与性能39/57与shraed_pool_size有关什么是shared_poolshared_pool分为两个部分第一部分为库高速缓存第二部分为字典高速缓存pl/sql程序分析后存放在库高速缓存共

24、享池由共享池由shared_pool_size参数设置参数设置 2021-10-24oracle 数据库设计与性能40/57 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 e

25、mp where name = :emp; select sysdate from dual; select to_char(sysdate, dd-mon-yy) from dual;2021-10-24oracle 数据库设计与性能41/572021-10-24oracle 数据库设计与性能42/57sql select * from mytable where name=tomsql select * from mytable where name=turner 2021-10-24oracle 数据库设计与性能43/572021-10-24oracle 数据库设计与性能44/57cur

26、sor_sharing_exact例子下面例子使用提示：下面例子使用提示：2021-10-24oracle 数据库设计与性能45/5710g9ir2一个银行业务处理流程理解sql执行过程关于cursor_sharing参数 监视sql area 的sql语句调整sql步骤2021-10-24oracle 数据库设计与性能46/57v$sysstat 显示 oracle cpu 所有会话情况 v$sesstat 显示每个会话对 oracle cpu 使用情况 v$sqlarea 显示目前运行的sql语句基本情况2021-10-24oracle 数据库设计与性能47/572021-10-24ora

27、cle 数据库设计与性能48/572021-10-24oracle 数据库设计与性能49/572021-10-24oracle 数据库设计与性能50/57本地频繁分析的语句:如果存在很高的类似的语句,可采用绑定变量.2021-10-24oracle 数据库设计与性能51/57能从缓冲区得到哪些sql 语句在使用cpu50000 是一个随意给定的点.用 explain 或跟踪可得到.2021-10-24oracle 数据库设计与性能52/57找出最消耗cpu的会话:注意：某时间点都会找出最差的语句(相对)2021-10-24oracle 数据库设计与性能53/5710g9ir2一个银行业务处理流程理解sql执行过程关于cursor_sharing参数 监视sql area 的sql语句调整sql步骤2021-10-24oracle 数据库设计与性能54/57sql语句调整步骤： 定位频繁使用的sql(可用statspack搜集数据)；调整sql语句；添加合适的索引(位图或b*tree索引)；改变