⑴ 2020-01-20 oracle中sql如何执行,什么是硬解析和软解析
1.语法检查:检查 SQL 拼写是否正确,如果不正确,Oracle 会报语法错误。
2.语义检查:检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候,列名写错了,系统就会提示错误。语法检查和语义侍槐察检查的作用是保证 SQL 语句没有错误。
3.权限检查:看用户是否具备访问该数据的权限。
4.共享池检查:共享池(Shared Pool)是一块内存池,最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划,来判断进行软解析,还是硬解析。那软解析和硬解析又该怎么理解呢?在共享池中,Oracle 首先对 SQL 语句进行 Hash 运算,然后根据 Hash 值在库缓存(Library Cache)中查找,如果存在 SQL 语句的执行计划,就直接拿来执行,直接进入“执行器”的环节,这就是软解析。如果没有找到 SQL 语句和执行计划,Oracle 就需要创建解析树进行解析明羡,生成执行计划,进入“优化器”这个步骤,这就是硬解析。
5.优化器:优化器中就是要进行硬解析,也就老茄是决定怎么做,比如创建解析树,生成执行计划。
6.执行器:当有了解析树和执行计划之后,就知道了 SQL 该怎么被执行,这样就可以在执行器中执行语句了。
共享池是 Oracle 中的术语,包括了库缓存,数据字典缓冲区等。我们上面已经讲到了库缓存区,它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义,比如表、视图、索引等对象。当对 SQL 语句进行解析的时候,如果需要相关的数据,会从数据字典缓冲区中提取。
如何避免硬解析,尽量使用软解析呢?在 Oracle 中,绑定变量是它的一大特色。绑定变量就是在 SQL 语句中使用变量,通过不同的变量取值来改变 SQL 的执行结果。
⑵ 关于ORACLE中SQL操作,请大家把下面语句的意思给我详细的解释下,谢谢
||是SQL连接符。
chr(13)char(10)是回车换行。
select table_name from user_tables;是查询user下的所有表。
drop table table_name;是删除表。
整句话的意思就是删除user下的所有表。
下一句同理,删除的是user下的所有视图。
⑶ 【基于ORACLE数据库的SQL语句优化分析】 数据库查询语句的优化
【摘要】随着数据库应用范围及规模的不断扩大,数据库的性能问题逐渐显现,优化数据库有助于维持系统的稳定性以及运行的高效性。本文主要依据笔者在实际工作中的精坦敏拍英,对SQL语句优化的目的、SQL语句优化技术及原则进行全面分析和阐述。
【关键词】ORACLE数据库;SQL语句;优化
1前言
随着现代化信息技术的迅猛发展,互联网应用的日益普及,数据库技术的影响力越来越大。作为信息系统管理的核心,数据库的主要操作就是查询,数据库的应用效率在很大程度上是由查询速度决定的,特别是对于规模较大的数据库而言,查询速度十分关键。查询速度在SQL语句中占有很大比重,所以,通过对查询语句进行优化有助于促进应用系统性能及效率的进一步提升。
2SQL语句优化分析
2.1SQL语句优化的目的
对于一个数据库而言,在确保设计无误的前提下,要想避免出现性能问题必须确保其拥有合理的SQL语句拿唤结构。最简单的数据库寻找数据路径是对SQL语句进行调整,ORACLE数据库性能提升的主要途径就是对SQL语句进行适当的调整。从本质上讲,SQL语句优化就是确保所使用的语句可以被优化器识别,对索引进行有效利用以便控制表扫描的I/O次数,有效防止出现表搜索。用高性能的SQL语句替代低性能的SQL语句,确定最佳的数据查找路径,尽可能使CPU时间与I/O时间保持平衡是进行优化的主要目的。在对SQL语句进行优化的过程中,以系统需求为依据确定最有可能实现性能提升的语句并进行优化。
2.2SQL语句优化技术及原则
当数据量积累到一定程度之后,对于数据库全表SQL语句进行一次扫描,若查询策略较好,一般只用几秒钟,但如果SQL语句性能较低,就需要用几分钟甚至更多时间。从这点不难看出,SQL语句性能对于查询速度具有极大的影响,所以,对于应用系统而言,不仅能满足功能的实现,还要保证SQL语句的质量。
(1)采取适宜的索引。为达到优化查询的目的,一项重要工作就是确定相适应的索引,并严格依照原则加以使用,与此同时,为有效控制I/O竞争,不可以在同一个磁盘中同时建立索引和用户表空间。
语句1:SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE CUS_NO NOT IN
(SELECT CUS_NO FROM SERVICE);
语句2: SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE NOT EXISTS
(SELECT * FROM SERVICE WHERE SERVICE.CUS_NO=CUSTOMER.CUS_NO);
上述两个语句可以达到一致的查询结果,对二者进行对比,当执行语句1时,由于ORACLE未利用CUSTOMER 表上CUS_NO索引,所以就会扫描整表,在执行语句2的过让羡程中,ORACLE所扫描的只是CUSTOMER 表子查询中的联合查询,并且使用了CUS_NO索引,因此,在执行效率方面明显优于前者。
(2)避免在SELECT子句中出现“*”。ORACLE在进行解析时,需要按照一定顺序对“*”进行转换,该项转换工作的进行需要对数据库的数据字典进行查询,势必需要花费较多的时间,这样就会导致较低的效率,所以,要避免在SELECT子句中出现“*”。
(3)如果必要可以利用COMMIT提交事务。ORACLE能够自动提交DDL语句,而诸如DML等类型的语句的提交则是通过手动方式或者回滚事务实现的。在编写应用程序的过程中,在操作诸如insert、delete以及update 等较为复杂的语境的时候,利用COMMIT提交事务可以讲会话中持有的锁加以释放,将存在于缓存中的未经修改的数据块进行清除,进而将系统资源予以释放,促进系统性能的进一步提升,因此,如果有必要,可以利用COMMIT对相关事务进行提交。
(4)联合查询连接顺序的确定。如果查询操作涉及到多个表,基础表应当是交叉表,所谓交叉表具体是指被其他表引用的表。连接执行效果在很大程度上受到FROM语句中表的顺序的影响,对于FROM中所包含的表,ORACLE解析器进行处理的顺序是由右至左,SQL语句中所选择的基础表会因优化器的不同而有所区别,在使用CBO的情况下,优化器会对SQL语句中各个表的物理大小以及索引状态进行检查,在此基础上确定一个花费最小的执行路径;在使用RBO的情况下,如果全部的连接条件均有索引与之相对应,那么,FROM子句中位置最后面的表就是基础表。
(5)IN用EXISTS取代。在对数个基础表查询过程中,一般需要进行表的连接。因为利用IN的子查询过程中,ORACLE的扫描对象是全表,因此,出于提高查询效率目的的考虑,应当将IN用EXISTS取代。
(6)在索引列中不使用计算。当通过对函数进行引用在WHERE子句中进行计算的时候,假如索引列只是函数的一部分,优化器就会针对全表进行扫描,而不会使用索引,所以,在索引列中不能使用函数。
3结语
综上所述,随着现代化信息技术的迅猛发展,互联网应用的日益普及,数据库技术的影响力越来越大。在信息量迅速激增的形势下,数据库优化调整成为当前所面临的一大关键性问题,特别是对规模较大的数据库而言,及时进行优化的意义更加倍重大。对于数据库的运行性能而言,最主要的影响因素主要体现在以下几点:数据库系统架构的设计是否合理,资源配置是否科学以及SQL语句编写效率等。笔者从事的是电信企业的运营分析工作,每天都要从数据库取各种数据,可以说是离不开数据库,所以在实践中,我觉得严格遵守SQL语句优化原则及方法,并在实践中及时总结经验教训,可以实现对系统响应时间的有效控制,促进运行效率的提升。
参考文献
[1] 许开宇,胡文骅. 如何提高ORACLE数据库应用程序的性能[J]. 计算机应用与软件. 2002(10)
[2] 郑耀,吴建岚. 基于Oracle数据库的语句优化策略[J]. 信息与电脑(理论版). 2011(07)
[3] 高攀,施蔚然. 基于Oracle数据库的SQL语句优化[J]. 电脑编程技巧与维护. 2010(22)
[4] 钟小权,叶猛. Oracle数据库的SQL语句优化[J]. 计算机与现代化. 2011(03)
作者简介:
王勇军,男,(1981.1-),吉林通化人,就职于中国联合网络通信有限公司长春市分公司,通信工程师,本科,研究方向:SQL使用
(作者单位:中国联合网络通信有限公司长春市分公司)
⑷ Sql语句解析过程
为了将用户写的SQL文本转化为Oracle认识的且可执行的语句 这个过程就叫做解析过程 解析分为硬解析和软解析 一条SQL语句在第一次被执行时必须进行硬解析
当客户端发出一条SQL语句(也可以是一个存储过程或者一个匿名PL/SQL块)进入shared pool时(注意 我们从前面已经知道 Oracle对这些SQL不叫做SQL语句 而是称为游标 因为Oracle在处理SQL时 需要很多相关的辅助信息 这些辅助信息与SQL语句一起组成了游标) Oracle首先将SQL文本转化为ASCII值 然后根据hash函数计算其对应的hash值(hash_value) 根据计算出的hash值到library cache中找到对应的bucket 然后比较bucket里是否存在该SQL语句
如果不存在 则需要按照我们前面所描述的 获得shared pool latch 然后在shared pool中的可用chunk链表(也就是bucket)上找到一个可用的chunk 之后释放shared pool latch 在获得了chunk以后 这块chunk就可以认为是进入了library cache 接下来 进行硬解析过程 硬解析包括以下几个步骤
对SQL语句进行文法检查 看是否有文法错误 比如没有写from select拼写错误等 如果存在文法错误 则退出解析过程
到数据字典里校验SQL语句涉及的对象和列是否都存在 如果不存在 则退出解析过程 这个过程会加载dictionary cache
将对象进行名称转换 比如将同名词翻译成实际的对象等 比如select * from t中 t是一个同名词 指向hr t 于是Oracle将t转换为hr t 如果转换失败 则退出解析过程
检查发出SQL语句的用户是否具有访问SQL语句里所引用的对象的权限 如果没有权限 则退出解析过程
通过优化器创建一个最优的执行计划 这个过程会根据数据字典里记录的对象的统计信息 来计算最优的执行计划 这一步牵涉大量数学运算 是最消耗CPU资源的
将该游标所产生的执行计划 SQL文本等装载进library cache的heap中
在硬解析的过程中 进程会一直持有library cache latch 直到硬解析结束为止 硬解析结束以后 会为SQL语句产生两个游标 一个是父游标 另一个是子游标 父游标里主要包含两种信息 SQL文本以及优化目标(optimizer goal) 父游标在第一次打开时被锁定 直到其他所有的session都关闭该游标后才被解锁 当父游标被锁定的时候是不能被交换出library cache的 只有在解锁以后才能被交换出library cache 父游标被交换出内存时 父游标对应的所有子游标也被交换出library cache 子游标包括游标所有的信息 比如具体的执行计划 绑定变量等 子游标随时可以被交换出library cache 当子游标被交换出library cache时 Oracle可以利用父游标的信息重新构建出一个子游标来 这个过程叫reload 可以使用下面的方式来确定reload的比率
select *sum(reloads)/sum(pins) Reload_Ratio from v$librarycache;
一个父游标可以对应多个子游标 子游标具体的个数可以从视图v$sqlarea的version_count字段体现出来 而每个具体的子游标则全都在视图v$sql里体现 当具体绑定变量的值与上次绑定变量的值有较大差异(比如上次执行的绑定变量值的长度是 位 而这次执行绑定变量的值的长度是 位)时或者当SQL语句完全相同 但是所引用的表属于不同的用户时 都会创建一个新的子游标
如果在bucket中找到了该SQL语句 则说明该SQL语句以前运行过 于是进行软解析 软解析是相对于硬解析而言的 如果解析过程中 可以从硬解析的步骤中去掉一个或多个的话 这样的解析就是软解析 软解析分为以下三种类型
第一种是某个session发出的SQL语句与library? cache里其他session发出的SQL语句一致 这时 该解析过程中可以去掉硬解析中的 和 但是仍然要进行硬解析过程中的 也就是表名和列名检查 名称转换和权限检查
* 第二种是某个session发出的SQL语句是该session之前发出的曾经执行过的SQL语句 这时 该解析过程中可以去掉硬解析中的 和 这四步 但是仍然要进行权限检查 因为可能通过grant改变了该session用户的权限
* 第三种是当设置了初始化参数session_cached_cursors时 当某个session第三次执行相同的SQL时 则会把该SQL语句的游标信息转移到该session的PGA里 这样 该session以后再执行相同的SQL语句时 会直接从PGA里取出执行计划 从而跳过硬解析的所有步骤 这种情况下 是最高效的解析方式 但是会消耗很大的内存
我们举一个例子来说明解析SQL语句的过程 在该测试中 绑定变量名称相同 但是变量类型不同时 所出现的解析情况 如下所示
首先 执行下面的命令 清空shared pool里所有的SQL语句
SQL> alter system flush shared_pool;
然后 定义一个数值型绑定变量 并为该绑定变数赋一个数值型的值以后 执行具体的查询语句
SQL> variable v_obj_id number;
SQL> exec :v_obj_id := ;
SQL> select object_id object_name from sharedpool_test
where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
AGGXMLIMP
接下来 定义一个字符型的绑定变量 变量名与前面相同 为该绑定变数赋一个字符型的值以后 执行相同的查询
SQL> variable v_obj_id varchar ( );
SQL> exec :v_obj_id := ;
SQL> select object_id object_name from sharedpool_test
where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
AGGXMLIMP
然后我们到视图v$sqlarea里找到该SQL的父游标的信息 并到视图v$sql里找该SQL的所有子游标的信息
SQL> select sql_text version_count from v$sqlarea where
sql_text like %sharedpool_test% ;
SQL_TEXT
VERSION_COUNT
select object_id object_name from sharedpool_test where
object_id=:v_obj_id
SQL> select sql_text child_address address from v$sql
where sql_text like %sharedpool_test% ;
SQL_TEXT
CHILD_ADDRESS ADDRESS
select object_id object_name from sharedpool_test where
object_id=:v_obj_id F
B D
select object_id object_name from sharedpool_test where
object_id=:v_obj_id FC
B D
从记录父游标的视图v$sqlarea的version_count列可以看到 该SQL语句有 个子游标 而从记录子游标的视图v$sql里可以看到 该SQL文本确实有两条记录 而且它们的SQL文本所处的地址(ADDRESS列)也是一样的 但是子地址(CHILD_ADDRESS)却不一样 这里的子地址实际就是子游标所对应的heap 的句柄
lishixin/Article/program/Oracle/201311/18653
⑸ SQL语句执行流程与顺序原理解析
SQL语句执行流程与顺序原理解析
Oracle语句执行流程
第一步:客户端把语句发给服务器端执行
当我们在客户端执行SQL语句时,客户端会把这条SQL语句发送给服务器端,让服务器端的进程来处理这语句。也就是说,Oracle 客户端是不会做任何的操作,他的主要任务就是把客户端产生的一些SQL语句发送给服务器端。服务器进程从用户进程把信息接收到后, 在PGA 中就要此进程分配所需内存,存储相关的信息,如:在会话内存存储相关的登录信息等。
虽然在客户端也有一个数据库进程,但是,这个进程的作用跟服务器上的进程作用是不相同的,服务器上的数据库进程才会对SQL 语句进行相关的处理。不过,有个问题需要说明,就是客户端的进程跟服务器的进程是一一对应的。也就是说,在客户端连接上服务器后,在客户端与服务器端都会形成一个进程,客户端上的我们叫做客户端进程,而服务器上的我们叫做服务器进程。
第二步:语句解析
当客户端把SQL语句传送到服务器后,服务器进程会对该语句进行解析。这个解析的工作是在服务器端所进行的,解析动作又可分为很多小动作。
1)查询高速缓存(library cache)
服务器进程在接到客户端传送过来的SQL语句时,不会直接去数据库查询。服务器进程把这个SQL语句的字符转化为ASCII等效数字码,接着这个ASCII码被传递给一个HASH函数,并返回一个hash值,然后服务器进程将到shared pool中的library cache(高速缓存)中去查找是否存在相同的hash值。如果存在,服务器进程将使用这条语句已高速缓存在SHARED POOL的library cache中的已分析过的版本来执行,省去后续的解析工作,这便是软解析。若调整缓存中不存在,则需要进行后面的步骤,这便是硬解析。硬解析通常是昂贵的操作,大约占整个SQL执行的70%左右的时间,硬解析会生成执行树,执行计划,等等。
所以,采用高速数据缓存的话,可以提高SQL 语句的查询效率。其原因有两方面:一方面是从内存中读取数据要比从硬盘中的数据文件中读取数据效率要高,另一方面也是因为避免语句解析而节省了时间。
不过这里要注意一点,这个数据缓存跟有些客户端软件的数据缓存是两码事。有些客户端软件为了提高查询效率,会在应用软件的客户端设置数据缓存。由于这些数据缓存的存在,可以提高客户端应用软件的查询效率。但是,若其他人在服务器进行了相关的修改,由于应用软件数据缓存的存在,导致修改的数据不能及时反映到客户端上。从这也可以看出,应用软件的数据缓存跟数据库服务器的高速数据缓存不是一码事。
2)语句合法性检查(data dict cache)
当在高速缓存中找不到对应的SQL语句时,则服务器进程就会开始检查这条语句的合法性。这里主要是对SQL语句的语法进行检查,看看其是否合乎语法规则。如果服务器进程认为这条SQL语句不符合语法规则的时候,就会把这个错误信息反馈给客户端。在这个语法检查的过程中,不会对SQL语句中所包含的表名、列名等等进行检查,只是检查语法。
3)语言含义检查(data dict cache)
若SQL 语句符合语法上的定义的话,则服务器进程接下去会对语句中涉及的表、索引、视图等对象进行解析,并对照数据字典检查这些对象的名称以及相关结构,看看这些字段、表、视图等是否在数据库中。如果表名与列名不准确的话,则数据库会就会反馈错误信息给客户端。
所以,有时候我们写select语句的时候,若语法与表名或者列名同时写错的话,则系统是先提示说语法错误,等到语法完全正确后再提示说列名或表名错误。
4)获得对象解析锁(control structer)
当语法、语义都正确后,系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性,防止我们在查询的过程中,其他用户对这个对象的结构发生改变。
5)数据访问权限的核对(data dict cache)
当语法、语义通过检查之后,客户端还不一定能够取得数据,服务器进程还会检查连接用户是否有这个数据访问的权限。若用户不具有数据访问权限的话,则客户端就不能够取得这些数据。要注意的是数据库服务器进程先检查语法与语义,然后才会检查访问权限。
6)确定最佳执行计划
当语法与语义都没有问题权限也匹配,服务器进程还是不会直接对数据库文件进行查询。服务器进程会根据一定的规则,对这条语句进行优化。在执行计划开发之前会有一步查询转换,如:视图合并、子查询解嵌套、谓语前推及物化视图重写查询等。为了确定采用哪个执行计划,Oracle还需要收集统计信息确定表的访问联结方法等,最终确定可能的最低成本的执行计划。
不过要注意,这个优化是有限的。一般在应用软件开发的过程中,需要对数据库的sql语句进行优化,这个优化的作用要大大地大于服务器进程的自我优化。
当服务器进程的优化器确定这条查询语句的最佳执行计划后, 就会将这条SQL语句与执行计划保存到数据高速缓存(library cache)。如此,等以后还有这个查询时,就会省略以上的语法、语义与权限检查的步骤,而直接执行SQL语句,提高SQL语句处理效率。
第三步:绑定变量赋值
如果SQL语句中使用了绑定变量,扫描绑定变量的声明,给绑定变量赋值,将变量值带入执行计划。若在解析的第一个步骤,SQL在高速缓冲中存在,则直接跳到该步骤。
第四步:语句执行
语句解析只是对SQL语句的语法进行解析,以确保服务器能够知道这条语句到底表达的是什么意思。等到语句解析完成之后,数据库服务器进程才会真正的执行这条SQL语句。
对于SELECT语句:
1)首先服务器进程要判断所需数据是否在db buffer存在,如果存在且可用,则直接获取该数据而不是从数据库文件中去查询数据,同时根据LRU 算法增加其访问计数;
2)若数据不在缓冲区中,则服务器进程将从数据库文件中查询相关数据,并把这些数据放入到数据缓冲区中(buffer cache)。
其中,若数据存在于db buffer,其可用性检查方式为:查看db buffer块的头部是否有事务,如果有事务,则从回滚段中读取数据;如果没有事务,则比较select的scn和db buffer块头部的scn,如果前者小于后者,仍然要从回滚段中读取数据;如果前者大于后者,说明这是一非脏缓存,可以直接读取这个db buffer块的中内容。
对于DML语句(insert、delete、update):
1)检查所需的数据库是否已经被读取到缓冲区缓存中。如果已经存在缓冲区缓存,则直接执行步骤3;
2)若所需的数据库并不在缓冲区缓存中,则服务器将数据块从数据文件读取到缓冲区缓存中;
3)对想要修改的表取得的数据行锁定(Row Exclusive Lock),之后对所需要修改的数据行取得独占锁;
4)将数据的Redo记录复制到redo log buffer;
5)产生数据修改的undo数据;
6)修改db buffer;
7)dbwr将修改写入数据文件;
其中,第2步,服务器将数据从数据文件读取到db buffer经经历以下步骤:
1)首先服务器进程将在表头部请求TM锁(保证此事务执行过程其他用户不能修改表的结构),如果成功加TM锁,再请求一些行级锁(TX锁),如果TM、TX锁都成功加锁,那么才开始从数据文件读数据。
2)在读数据之前,要先为读取的文件准备好buffer空间。服务器进程需要扫描LRU list寻找free db buffer,扫描的过程中,服务器进程会把发现的所有已经被修改过的db buffer注册到dirty list中。如果free db buffer及非脏数据块缓冲区不足时,会触发dbwr将dirty buffer中指向的缓冲块写入数据文件,并且清洗掉这些缓冲区来腾出空间缓冲新读入的数据。
3)找到了足够的空闲buffer,服务器进程将从数据文件中读入这些行所在的每一个数据块(db block)(DB BLOCK是ORACLE的最小操作单元,即使你想要的数据只是DB BLOCK中很多行中的一行或几行,ORACLE也会把这个DB BLOCK中的所有行都读入Oracle DB BUFFER中)放入db buffer的空闲的区域或者覆盖已被挤出LRU list的非脏数据块缓冲区,并且排列在LRU列表的头部,也就是在数据块放入db buffer之前也是要先申请db buffer中的锁存器,成功加锁后,才能读数据到db buffer。
若数据块已经存在于db buffer cache(有时也称db buffer或db cache),即使在db buffer中找到一个没有事务,而且SCN比自己小的非脏缓存数据块,服务器进程仍然要到表的头部对这条记录申请加锁,加锁成功才能进行后续动作,如果不成功,则要等待前面的进程解锁后才能进行动作(这个时候阻塞是tx锁阻塞)。
在记redo日志时,其具体步骤如下:
1)数据被读入到db buffer后,服务器进程将该语句所影响的并被读入db buffer中的这些行数据的rowid及要更新的原值和新值及scn等信息从PGA逐条的写入redo log buffer中。在写入redo log buffer之前也要事先请求redo log buffer的锁存器,成功加锁后才开始写入。
2)当写入达到redo log buffer大小的三分之一或写入量达到1M或超过三秒后或发生检查点时或者dbwr之前发生,都会触发lgwr进程把redo log buffer的数据写入磁盘上的redo file文件中(这个时候会产生log file sync等待事件)。
3)已经被写入redo file的redo log buffer所持有的锁存器会被释放,并可被后来的写入信息覆盖,redo log buffer是循环使用的。Redo file也是循环使用的,当一个redo file写满后,lgwr进程会自动切换到下一redo file(这个时候可能出现log file switch(check point complete)等待事件)。如果是归档模式,归档进程还要将前一个写满的redo file文件的内容写到归档日志文件中(这个时候可能出现log file switch(archiving needed)。
在为事务建立undo信息时,其具体步骤如下:
1)在完成本事务所有相关的redo log buffer之后,服务器进程开始改写这个db buffer的块头部事务列表并写入scn(一开始scn是写在redo log buffer中的,并未写在db buffer)。
2)然后包含这个块的头部事务列表及scn信息的数据副本放入回滚段中,将这时回滚段中的信息称为数据块的“前映像”,这个“前映像”用于以后的回滚、恢复和一致性读。(回滚段可以存储在专门的回滚表空间中,这个表空间由一个或多个物理文件组成,并专用于回滚表空间,回滚段也可在其它表空间中的数据文件中开辟)。
在修改信息写入数据文件时,其具体步骤如下:
1)改写db buffer块的数据内容,并在块的头部写入回滚段的地址。
2)将db buffer指针放入dirty list。如果一个行数据多次update而未commit,则在回滚段中将会有多个“前映像”,除了第一个“前映像”含有scn信息外,其他每个"前映像"的头部都有scn信息和"前前映像"回滚段地址。一个update只对应一个scn,然后服务器进程将在dirty list中建立一条指向此db buffer块的指针(方便dbwr进程可以找到dirty list的db buffer数据块并写入数据文件中)。接着服务器进程会从数据文件中继续读入第二个数据块,重复前一数据块的动作,数据块的读入、记日志、建立回滚段、修改数据块、放入dirty list。
3)当dirty queue的长度达到阀值(一般是25%),服务器进程将通知dbwr把脏数据写出,就是释放db buffer上的锁存器,腾出更多的free db buffer。前面一直都是在说明oracle一次读一个数据块,其实oracle可以一次读入多个数据块(db_file_multiblock_read_count来设置一次读入块的个数)
当执行commit时,具体步骤如下:
1)commit触发lgwr进程,但不强制dbwr立即释放所有相应db buffer块的锁。也就是说有可能虽然已经commit了,但在随后的一段时间内dbwr还在写这条sql语句所涉及的数据块。表头部的行锁并不在commit之后立即释放,而是要等dbwr进程完成之后才释放,这就可能会出现一个用户请求另一用户已经commit的资源不成功的现象。
2)从Commit和dbwr进程结束之间的时间很短,如果恰巧在commit之后,dbwr未结束之前断电,因为commit之后的数据已经属于数据文件的内容,但这部分文件没有完全写入到数据文件中。所以需要前滚。由于commit已经触发lgwr,这些所有未来得及写入数据文件的更改会在实例重启后,由smon进程根据重做日志文件来前滚,完成之前commit未完成的工作(即把更改写入数据文件)。
3)如果未commit就断电了,因为数据已经在db buffer更改了,没有commit,说明这部分数据不属于数据文件。由于dbwr之前触发lgwr也就是只要数据更改,(肯定要先有log)所有dbwr在数据文件上的修改都会被先一步记入重做日志文件,实例重启后,SMON进程再根据重做日志文件来回滚。
其实smon的前滚回滚是根据检查点来完成的,当一个全部检查点发生的时候,首先让LGWR进程将redologbuffer中的所有缓冲(包含未提交的重做信息)写入重做日志文件,然后让dbwr进程将dbbuffer已提交的缓冲写入数据文件(不强制写未提交的)。然后更新控制文件和数据文件头部的SCN,表明当前数据库是一致的,在相邻的两个检查点之间有很多事务,有提交和未提交的。
当执行rollback时,具体步骤如下:
服务器进程会根据数据文件块和db buffer中块的头部的事务列表和SCN以及回滚段地址找到回滚段中相应的修改前的副本,并且用这些原值来还原当前数据文件中已修改但未提交的改变。如果有多个”前映像“,服务器进程会在一个“前映像”的头部找到“前前映像”的回滚段地址,一直找到同一事务下的最早的一个“前映像”为止。一旦发出了commit,用户就不能rollback,这使得commit后dbwr进程还没有全部完成的后续动作得到了保障。
第五步:提取数据
当语句执行完成之后,查询到的数据还是在服务器进程中,还没有被传送到客户端的用户进程。所以,在服务器端的进程中,有一个专门负责数据提取的一段代码。他的作用就是把查询到的数据结果返回给用户端进程,从而完成整个查询动作。
从这整个查询处理过程中,我们在数据库开发或者应用软件开发过程中,需要注意以下几点:
一是要了解数据库缓存跟应用软件缓存是两码事情。数据库缓存只有在数据库服务器端才存在,在客户端是不存在的。只有如此,才能够保证数据库缓存中的内容跟数据库文件的内容一致。才能够根据相关的规则,防止数据脏读、错读的发生。而应用软件所涉及的数据缓存,由于跟数据库缓存不是一码事情,所以,应用软件的数据缓存虽然可以提高数据的查询效率,但是,却打破了数据一致性的要求,有时候会发生脏读、错读等情况的发生。所以,有时候,在应用软件上有专门一个功能,用来在必要的时候清除数据缓存。不过,这个数据缓存的清除,也只是清除本机上的数据缓存,或者说,只是清除这个应用程序的数据缓存,而不会清除数据库的数据缓存。
二是绝大部分SQL语句都是按照这个处理过程处理的。我们DBA或者基于Oracle数据库的开发人员了解这些语句的处理过程,对于我们进行涉及到SQL语句的开发与调试,是非常有帮助的。有时候,掌握这些处理原则,可以减少我们排错的时间。特别要注意,数据库是把数据查询权限的审查放在语法语义的后面进行检查的。所以,有时会若光用数据库的权限控制原则,可能还不能满足应用软件权限控制的需要。此时,就需要应用软件的前台设置,实现权限管理的要求。而且,有时应用数据库的权限管理,也有点显得繁琐,会增加服务器处理的工作量。因此,对于记录、字段等的查询权限控制,大部分程序涉及人员喜欢在应用程序中实现,而不是在数据库上实现。
Oracle SQL语句执行顺序
(8)SELECT (9) DISTINCT (11) <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
1)FROM:对FROM子句中的表执行笛卡尔积(交叉联接),生成虚拟表VT1。
2)ON:对VT1应用ON筛选器,只有那些使为真才被插入到TV2。
3)OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN),保留表中未找到匹配的行将作为外部行添加到VT2,生成TV3。如果FROM子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3,直到处理完所有的表位置。
4)WHERE:对TV3应用WHERE筛选器,只有使为true的行才插入TV4。
5)GROUP BY:按GROUP BY子句中的列列表对TV4中的行进行分组,生成TV5。
6)CUTE|ROLLUP:把超组插入VT5,生成VT6。
7)HAVING:对VT6应用HAVING筛选器,只有使为true的组插入到VT7。
8)SELECT:处理SELECT列表,产生VT8。
9)DISTINCT:将重复的行从VT8中删除,产品VT9。
10)ORDER BY:将VT9中的行按ORDER BY子句中的列列表顺序,生成一个游标(VC10),生成表TV11,并返回给调用者。
以上每个步骤都会产生一个虚拟表,该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句,将跳过相应的步骤。