[转] Oracle sql 语句执行过程图文分析

[原文链接]  http://nvd11.blog.163.com/blog/static/200018312201301310585758/

[原文视频]  http://www.jiagulun.com/thread-2674-1-1.html

1. 数据库文件
Oracle
数据库文件大概可分为3种,分别是:
控制文件(control files):
存放数据库本身物理结构信息
数据文件(data files):
存放数据库数据啦~
日志文件(log files): 
包括重做日志文件和归档日志文件, 记录数据库数据的变化.
如下图:

2. 数据库实例

       用户和应用程序是无法直接访问数据库文件的数据的,
这时 Oracle 服务器会启动 (1或多个..RAC集群啦)个实例.,用户可以通过连接这个实例来访问数据库的数据。

实例有两个组成部分,分别是:
       系统全局内存区(SGA):服务器专门划分给 Oracle 实例使用的内存块。
      
Oracle进程:包括服务器进程和后台进程, 后面会解析。

2.1 系统全局内存区(SGA)

       SGA 可以分成 6 大块,分别是 Java pool,
shared pool, database buffer cache, large pool, streams pool, Redo log
buffer
       [注] 可用 v$sga 这个视图去查看 sga 各大块大小.

这篇文章介绍的是 sql 语句的执行流程,所以主要讲解其中的3个大块:
       共享缓冲区(shared
pool)
       数据库缓冲高速缓存(database buffer cache)
       重做日志缓冲区(redo log
buffer)

            
       如下图:

2.2
服务器进程

当用户(客户端)要连接 Oracle 数据库时,
Oracle就会创建 1 个 session(会话),并且在服务器上创建 1个专门处理这个 session 的进程,就是服务器进程啦。

如下图

[注意]  每当1个新用户创建1个新的连接到数据库时,Oracle 都会对应创建 1 条服务器进程的。

2.3
PGA(Program Global Area)

对应上面的 Server
Process, Oracle 会在服务器上对每一条 Server Process 分配一定大小的内存,就是PGA了,
注意有几个 session 就会有几个对应的 SGA 块, 所以服务器对内存需求很大的。

可以用
select
sum(pga_userd_mem) from v$process
语句来查看当前使用的总 PGA 大小。

如图:

3. 客户端与服务器的SQL语句传输

这时, 用户在客户端输入若干条SQL语句, 例如 1 个普通的存储过程,有读和写的动作。

这条语句通过什么来传输呢?答案就是session啦, 
那么服务器上用什么来接受这个sql 语句呢, 答案就是Server Process。

如图:

4. server process 会判断 sql 语句是否合法(语法,权限)。如果 sql 语法有错,或者对应的表或视图或 Procedure 没有权限,就会直接返回错误信息啦。

5. server process根据 sql 语句生成执行计划(execute plan).

     
Oracle 是无法直接执行 sql 语句的,必须先生成执行计划,然后 Oracle 就会根据执行计划去执行了。而生成执行计划要访问许多数据库对象,
是一个比较消耗服务器资源(CPU,IO,Memory)的动作。

而且因为同一条 sql 语句可能会有多个用户多次重复的执行,
那么是否每次都生成一次执行计划呢? 这时 SGA 里面的 Shared pool 就发挥作用了,它会缓存 sql 的执行计划。

所以 server process 会首先从 Shared pool 里面查找有无现成的执行计划, 如果有就直接采用。如果无,
就自己生成一个,然后看情况把这个执行计划放入shared pool。

6. server process 根据执行计划去取(写)数据.


       好了。当 server
process 得到执行计划后就可以去取数据了。Oracle 的数据放在哪里呢? 放在数据文件,这个大家都懂,但是 server
process 是不是就直接去访问数据文件呢?

因为计算机的时间消耗主要都在物理IO,所以要尽量避免物理读写,所以SGA 里面的 database buffer cache 起作用了。说明白点,
database buffer cache 就是用来缓冲 Data files 里的数据的。这样就可以避免了对数据文件的读写。

所以 server process 得到执行计划后,第一步是首先去 database buffer cache 去找有没有现成的数据;如果有最好,
如果无或者缓存中数据不全的话就只能去访问data files 啦。

从 data files 获得数据后,
也不是直接发给用户客户端,而是根据情况放入database buffer cache 里面, 以便当前或其他用户多次使用啦。

7. 逻辑读、物理读与缓存命中率.


          由上图得知,所谓逻辑读,就是从缓存(一般是内存)里读取数据。而物理读,也就是从磁盘(数据文件)里读取数据啦。

至于缓存命中率,就是取出数据的过程中  逻辑读次数/ (逻辑读次数+物理读次数) 这个比率。当然这个比率越接近1越好,
因为物理读相当费时间啦, 除非有服务器用SSD做硬盘。

当然命中率并不是数据库健康的唯一指标,因为当逻辑读十分巨大的时候, 即使物理读也很大,这个比率也很好看的, 所以有时要关心每秒物理读(tps)

可以在 linux 下使用 iostat 命令来查看当前磁盘的每秒物理读啦。

8. 在缓存中修改数据

server
process 拿出数据放入缓存中,接下来就对数据进行修改。因为修改数据很可能会产生大量缓冲数据,所以这个动作是在 Database
buffer cache 里完成的。 这个很容易理解。

9. 修改数据会产生重做日志


      
上面提到日志是用来记录数据库的数据变化的,所以对数据改动产生一定量的日志数据。那么这些日志是不是直接就写到日志文件中呢?写日志文件也是物理读,所以 SGA 就有个 Redo log buffer,就是日志缓存,专门实时存放产生的日志数据啦。

10. 最终Server Process把返回数据或信息通过session传回给用客户端


     
Server
Process 做完读取和修改数据的动作后,就会将结果返给用户了。

11. 将数据缓存和日志缓存写入磁盘


       
其实到上面那一步为止,  整个 sql 语句执行流程已经完成了。可能有人会问, Server
process 修改的数据和产生的日志还在 SGA 里面呢,它们不用被写入磁盘吗?

答案是肯定需要的,但是这些动作已经不是 sql 执行流程之内,而且这些动作也不是 server process 负责的, 他们分别由 DBWRLGWR
这个两个进程负责。

如图:

DBWR:  Database writer,后台进程之一,负责将 Database buffer
cache 里被修改的数据写入数据文件。
LGWR:   Log
writer,后台进程之一,负责将 Redo log buffer 里的日志数据写入到日志文件。

12.为什么Oracle要将server process 和后台进程分开?

      为什么写入数据文件和日志文件要交给后台进程去完成呢? 其实我们在流程可以发现, 服务器与用户打交道的就只有一个进程,就是Server
Process,  所以 server Process 的速度直接影响了用户的感受。无论后台进程多么繁忙,只要 server process 响应迅捷,
用户还是觉得数据库很快的。 相反,后台进程没事做,服务器 CPU 很空闲,但 server process 反应慢的话,
用户就觉得数据库慢了。

所以就要尽量精简 server process 的动作,  看看后台进程 DBWR 和 LGWR 进行的是什么动作,
磁盘写动作啊! 所以这些动作完全可以在sql流程执行完慢慢来嘛。

Server
Process 唯一进行的物理操作就是物理读,  这个是无办法避免的, 因为数据都在磁盘上嘛。除非有办法预测用户要提取的数据,提前拿出来。但也没有那么大的内存啊。

13. 顺便介绍其余3大系统进程 CKPT, SMON, PMON

都说 Oracle 有 SGA 6 大池,3 大数据库文件, 5 大系统进程。其中DBWR 和 LGWR上面已经介绍过了。剩下其余3个:

CKPT : Checkpoint 
检查点进程,负责更新控制文件和数据文件的头部信息,  控件文件在这篇blog开头就已经介绍过啦。至于数据文件的头部信息就是当前数据块的状态信息啦。
SMON :  system
monitor 系统监视器,负责监视维护 SGA 和后台进程啦,例如合并SGA里面的碎片。
PMON :  process monitor  进程监视器, 
这里主要指服务器进程啦。例如一个用户突然掉线了,但是该服务器进程还在服务器,Pmon会隔一段时间把该进程清理掉并且释放SGA 啦。

最后感谢老相老师用心做的教学视频, 真的很通俗易懂,
截个图。哈哈

时间: 08-28

[转] Oracle sql 语句执行过程图文分析的相关文章

oracle学习笔记 SQL语句执行过程剖析讲课

oracle学习笔记 SQL语句执行过程剖析讲课 这节课通过讲述一条SQL语句进入数据库 和其在数据库中的整个的执行过程 把数据库里面的体系结构串一下. 让大家再进一步了解oracle数据库里面的各个进程.存储结构以及内存结构的关联关系. 首先来讲整个体系中有客户端.实例和数据库 数据库里有三类文件 控制文件ctl.数据文件dbf.日志文件log 实例中SGA有六大池子 第一大内存区shared pool即共享池 第二大内存区buffer cache 第三块是redo log 我们主要讲上面的三

mysql之sql语句执行过程

1.SQL语句执行过程  1.客户端发送一条sql查询语句给服务器;     2.服务器通过权限检查之后,首先会检查查询缓存,如果命中了缓存,则立即返回 存储在缓存中的结果,否则进入下一步;     3.服务器端进行sql解析,预处理,再由查询优化器根据该sql所涉及的数据表的统计信息进行计算,生成对应的执行计划;     4.mySQL根据查询优化器生成的执行计划,调用存储引擎的API来执行查询;      5.将查询结果返回给客户端;    2.SQL语句执行过程的注意事项 1.在执行查询时

Oracle SQL语句执行完整过程:

SQL语句执行完整过程: 1. 用户进程提交一个sql 语句: update temp set a=a*2,给服务器进程. 2.服务器进程从用户进程把信息接收到后, 在PGA 中就要此进程分配所需内存,存储相关的信息,如在会话内存存储相关的登录信息等. 3.服务 器进程把这个sql  语句的字符转化为ASCII 等效数字码, 接着这个ASCII   码被传递给一个HASH 函数, 并返回一个hash 值,然后服务器进程将到shared pool  中的library cache 中去查找是否存在

SQL语句执行过程详解

一.SQL语句执行原理: 第一步:客户端把语句发给服务器端执行 当我们在客户端执行select语句时, 客户端会把这条SQL语句发送给服务器端,让服务器端的进程来处理这语句.也就是说,Oracle客户端是不会做任何的操作,它的主要任务就是把客户端产生的一些SQL语句发送给服务器端.虽然在客户端也有一个数据库进程,但是,这个进程的作用跟服务器上的进程作用不同.服务器上的数据库进程才会对SQL语句进行相关的处理.不过,有个问题需要说明,就是客户端的进程跟服务器的进程是一 一对应的.也就是说,在客户端

跟踪oracle中sql语句执行过程及相关知识拓展

<pre name="code" class="sql">select * from v$sqlarea; select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00'; 这个方法查询结果每条记录显示一条查询语句,且只能查询sql_text小于1000字符的,多余的会被截断. 改进一下: select * from v$sqlarea where first_load_tim

SQL语句执行效率及分析(note)

1.关于SQL查询效率,100w数据,查询只要1秒,与您分享: 机器情况p4: 2.4内存: 1 Gos: windows 2003数据库: ms sql server 2000目的: 查询性能测试,比较两种查询的性能 SQL查询效率 step by step -- setp 1.-- 建表create table t_userinfo(userid int identity(1,1) primary key nonclustered,nick varchar(50) not null defa

oracle sql语句跟踪及性能分析工具实现

在网上找了一大圈,没找着合适的工具来跟踪oracle一段时间的sql. 我们的场景是打算自动化跑遍所有场景(rft)+fiddler跟踪请求+后端跟踪sql,根据结果去分析慢的请求和sql,本来awr报告也能实现,但是每次都用awr比较麻烦,想的是能够简单点,直接定位到执行慢的sql或者耗cpu的sql. 这个工具抓取的sql不是实时的,是某段时间的sql,原理也很简单,比awr的sql要简单的多,试了一把,跟awr给出的数据是一致的. 最终效果:准备操作时开启跟踪,结束操作后关闭跟踪,然后工具

(转)SQL语句执行效率及分析(note)

1.关于SQL查询效率,100w数据,查询只要1秒,与您分享: 机器情况p4: 2.4内存: 1 Gos: windows 2003数据库: ms sql server 2000目的: 查询性能测试,比较两种查询的性能 SQL查询效率 step by step -- setp 1.-- 建表create table t_userinfo(userid int identity(1,1) primary key nonclustered,nick varchar(50) not null defa

Oracle执行SQL语句的过程

转载至:http://blog.csdn.net/aqszhuaihuai/article/details/7024551 当我们提交一条sql语句时,Oracle会做哪些操作呢? Oracle会为每个用户进程分配一个服务器进程:service process(实际情况应该区分专用服务器和共享服务器),当service process接收到用户进程提交的sql语句时,服务器进程会对sql语句进行语法和词法分析. 名词解释: 语法分析:语句本身正确性. 词法分析:对照数据字典中检查表,索引,视图和