OracleRDBMSSQL语句处理流程课件

1、2022-3-6OracleRDBMSSQL语句处理流程12022-3-6OracleRDBMSSQL语句处理流程210g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤2022-3-6OracleRDBMSSQL语句处理流程3|共享池概念回顾 ：Shared pool是SGA的一部分，它是用来存放由应用用户发出的SQL和PL/SQL语句 ；Shared Pool由3个部件组成 ：库缓存(library cache)-用来存放应用用户刚发出的SQL及PL/SQL语句、存储过程、函数、包、触发器、同

2、义词PL/SQL包及JAVA类库等对象信息 ；数据字典(Data Dictionary Cache)-表、列等信息存储在该区 ；用户全局区（User Global Area）-使用共享服务器选件时，用户全局区才有用，用户全局区用于存放用户会话的应用 2022-3-6OracleRDBMSSQL语句处理流程4|Oracle共享池库高速缓存工作方式： 用来缓存共享的SQL statements 和PL/SQL块，这些可以给所有的连接用户共享 由LRU(最小使用最先淘汰算法)来管理不是FIFO(先进先出)算法管理！ Oracle如何知道你的SQL语句是否在里面先通过一个hash算法将Statemen

3、t text运算成一个hash数值然后通过这个hash值在共享池中查找2022-3-6OracleRDBMSSQL语句处理流程5|SQL语句分析SQL语法分析(Syntactical Analysis)语法正确性分析，如关键字拼写等语义分析(Semantic Analysis)当前用户访问表是否有权限等存入共享池( shared pool )当前SQL语句语法和语义正确后存储在SGA的共享词内，可供当前会话再次使用或其他用户使用2022-3-6OracleRDBMSSQL语句处理流程6|SQL语句的HASH值 SQL语句在语义分析后，都产生一个叫SQL Hsah Values的值 SQL Hs

4、ah Values是由Hash函数产生的，在数据库中相同的SQL语句具有相同的Hash值 Oracle系统通过SQL Hash 值来判断某个语句是否出现过 运行过程中，可从V$SQLTEXT查询到SQL语句及其Hash值：SQL SELECername 用户名用户名, 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 an

5、d 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=zyj2022-3-6OracleRDBMSSQL语句处理流程72022-3-6OracleRDBMSSQL语句处理流程82022-3-6OracleRDBMSSQL语句处理流程92022-3-6OracleRDBMSSQL语句处理流程102022-3-6Orac

6、leRDBMSSQL语句处理流程112022-3-6OracleRDBMSSQL语句处理流程1210g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤2022-3-6OracleRDBMSSQL语句处理流程13 Select account_balance From banktable Where account_number=111222333 And account_type=SAVINGS；2022-3-6OracleRDBMSSQL语句处理流程14Update Bank_table set

7、 account_balanct=300 Where account_number=111222333 And account_type=SAVINGS；2022-3-6OracleRDBMSSQL语句处理流程1510g9iR2优化有关的术语一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤2022-3-6OracleRDBMSSQL语句处理流程16SQL语句从发出到执行的主要流程：语句从发出到执行的主要流程：2022-3-6OracleRDBMSSQL语句处理流程17SQL语句执行基本流程：语句执行基本流程：2022-

8、3-6OracleRDBMSSQL语句处理流程18多表连接的多表连接的SQL语句执行流程：语句执行流程：2022-3-6OracleRDBMSSQL语句处理流程19分析(Parsing)优化(Optimization)执行(Execution)2022-3-6OracleRDBMSSQL语句处理流程20语法分析(Syntactical Analysis)语义分析(Semantic Analysis)存入共享池( shared pool )2022-3-6OracleRDBMSSQL语句处理流程21各表示符号查询 / 建议语法select ename, job, d.deptno, dname

9、from emp e dept dwhere e.deptno = d.deptno and e.job = CLERK“ order deptnoSELECT FROM WHERE ORDER BY 2022-3-6OracleRDBMSSQL语句处理流程22解决引用关系 (锁-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)ENAMEJOBDEPTNOSCOT

10、T.DEPT (table)DEPTNODNAMESchema SCOTTData Dictionary2022-3-6OracleRDBMSSQL语句处理流程23转化为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 Ins

11、tance42022-3-6OracleRDBMSSQL语句处理流程24如果共享池没找到就加载到共享池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 WHER

12、E 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. .HASH5,62022-3-6OracleRDBMSSQL语句处理流程25Final Execution Plan / Row Source Generator 0 SELECT STATEMENT Optimizer=

13、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 EMPSELECT ename, job, d.deptno, dname Data Dictionary评估不同访问路径评估不同访问路径 (包括包括 latches)确定最佳路径，并保持在共享池中确定最佳路径，并保持在共享池中OptimizerPlan A : Cost = 5 Plan B : Cost = 3 Plan C : Cost = 10 2022-3-6O

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

15、6OracleRDBMSSQL语句处理流程28SQL语句的解析： 接收SQL到共享池；检查语法等；重新书写查询(Query Rewrite) ：如果创建了实体视图(enable query rewrite)；Alter session set query_rewrite_enabled=true；该SQL语句采用查询重写(见实体视图)。Oracle 的 cursor_sharing参数：Force -除变量外语句完全相同使用同一个光标；Exact(默认)-语句完全相同使用同一个光标；9i/10g/11g 增加SIMILAR参数，强制共享只有文字不同的语句解释计划。2022-3-6OracleR

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

17、DBMSSQL语句处理流程30TKPROFSELECT 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.0122022-3-6OracleRDBMSSQL语句处理流程31SELECT COUNT(*) FROM big_u

18、ser_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.212022-3-6OracleRDBMSSQL语句处理流程32SELECT 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.0

19、8 0.07(分析工作代价相当高.)2022-3-6OracleRDBMSSQL语句处理流程33最优保持执行计划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,st

20、atus,dataobj. 298300393 SELECT username FROM big_user_table WHERE object.4049165760 select order#,columns,types from access$ where d_o.HASH2022-3-6OracleRDBMSSQL语句处理流程342022-3-6OracleRDBMSSQL语句处理流程35-After added to Shared Pool-executed 4 times in a *new* session:SELECT username FROM big_user_table W

21、HERE 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.002022-3-6OracleRDBMSSQL语句处理流程36SELECT username FROM big_user_table WHERE id = 100;SELECT username FROM big_user_table WHERE object_id = 250;298300393V$SQL (Data Dictionary v

22、iew 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.4049165

23、760 select order#,columns,types from access$ where d_o.1737037929HASHHASH2022-3-6OracleRDBMSSQL语句处理流程372022-3-6OracleRDBMSSQL语句处理流程3810g9iR2一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤2022-3-6OracleRDBMSSQL语句处理流程39与SHRAED_POOL_SIZE有关什么是SHARED_POOLSHARED_POOL分为两个部分第一部分为库高速缓存第二部分为字典高

24、速缓存PL/SQL程序分析后存放在库高速缓存共享池由共享池由SHARED_POOL_SIZE参数设置参数设置 2022-3-6OracleRDBMSSQL语句处理流程40 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

25、; select address from emp where name = :EMP; select sysdate from dual; select to_char(sysdate, dd-mon-yy) from dual;2022-3-6OracleRDBMSSQL语句处理流程412022-3-6OracleRDBMSSQL语句处理流程42SQL SELECT * FROM MYTABLE WHERE NAME=tomSQL SELECT * FROM MYTABLE WHERE NAME=turner 2022-3-6OracleRDBMSSQL语句处理流程432022-3-6Or

26、acleRDBMSSQL语句处理流程44CURSOR_SHARING_EXACT例子下面例子使用提示：下面例子使用提示：2022-3-6OracleRDBMSSQL语句处理流程4510g9iR2一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤2022-3-6OracleRDBMSSQL语句处理流程46V$SYSSTAT 显示 Oracle CPU 所有会话情况 V$SESSTAT 显示每个会话对 Oracle CPU 使用情况 V$SQLAREA 显示目前运行的SQL语句基本情况2022-3-6OracleRDBMSS

27、QL语句处理流程472022-3-6OracleRDBMSSQL语句处理流程482022-3-6OracleRDBMSSQL语句处理流程492022-3-6OracleRDBMSSQL语句处理流程50本地频繁分析的语句:如果存在很高的类似的语句,可采用绑定变量.2022-3-6OracleRDBMSSQL语句处理流程51能从缓冲区得到哪些SQL 语句在使用CPU50000 是一个随意给定的点.用 EXPLAIN 或跟踪可得到.2022-3-6OracleRDBMSSQL语句处理流程52找出最消耗CPU的会话:注意：某时间点都会找出最差的语句(相对)2022-3-6OracleRDBMSSQL语句处理流程5310g9iR2一个银行业务处理流程理解SQL执行过程关于CURSOR_SHARING参数 监视SQL Area 的SQL语句调整SQL步骤2022-3-6OracleRDBMSSQL语句处理流程54SQL语句调整步骤： 定位频繁使用的SQL(可用statspack搜集数据)；调整SQL语句；添加合适的索引(位图或B*Tree索引)；改变优化