我们都知道在Oracle中每条SQL语句在执行之前都需要经过解析,这里面又分为软解析和硬解析。那么这两种解析有何不同之处呢?它们又分别是如何进行解析呢?Oracle内部解析的步骤又是如何进行的呢?下面我们就这些话题进行共同探讨。
在Oracle中存在两种类型的SQL语句,一类为DDL语句,他们是从来不会共享使用的,也就是每次执行都需要进行硬解析。还有一类就是DML语句,他们会根据情况选择要么进行硬解析,要么进行软解析。在Oracle 8i OCP教材的023中1-12有说明SQL语句的解析步骤,当一条SQL语句从客户端进程传递到服务器端进程后,需要执行如下步骤:
? 在共享池中搜索 SQL 语句的现有副本
? 验证 SQL 语句的语法是否准确
? 执行数据字典查找来验证表和列的定义
? 获取对象的分析锁以便在语句的分析过程中对象的定义不会改变
? 检查用户访问引用方案对象的权限
? 确定语句的最佳执行计划
? 将语句和执行计划载入共享的 SQL 区
这个先入为主的概念一直占据着我的脑海,我认为硬解析就是上面几个步骤。相对于硬解析,软解析的步骤就是上面第一步找到现有SQL语句的副本后,只需要验证用户是否有权限执行就是了,这样省略上面好几个步骤,相对硬解析来说性能开销就非常小了。即使是在论坛上和大家讨论时,我也一直坚持这个看法。直到前一天看了Tom的《Effective Oracle By Design》中关于语句处理的章节后,我才知道这个自己一直坚持的观点事实上是错误的。
事实上,在Oracle中SQL语句的解析步骤如下:
1、 语法检测。判断一条SQL语句的语法是否符合SQL的规范,比如执行:SQL> selet * from emp;我们就可以看出由于Select关键字少了一个“c”,这条语句就无法通过语法检验的步骤了。
2、 语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确?用户是否有权限访问或更改相应的表或列?比如如下语句:
SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
由于查询用户没有可供访问的emp对象,因此该SQL语句无法通过语义检查。
3、 检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本,那么该SQL语句将会被软解析,也就是可以重用已解析过的语句的执行计划和优化方案,可以忽略语句解析过程中最耗费资源的步骤,这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤:
(1)验证SQL语句是否完全一致。在这个步骤中,Oracle将会对传递进来的SQL语句使用HASH函数运算得出HASH值,再与共享池中现有语句的HASH值进行比较看是否一一对应。现有数据库中SQL语句的HASH值我们可以通过访问v$sql、v$sqlarea、v$sqltext等数据字典中的HASH_VALUE列查询得出。如果SQL语句的HASH值一致,那么ORACLE事实上还需要对SQL语句的语义进行再次检测,以决定是否一致。那么为什么Oracle需要再次对语句文本进行检测呢?不是SQL语句的HASH值已经对应上了?事实上就算是SQL语句的HASH值已经对应上了,并不能说明这两条SQL语句就已经可以共享了。我们首先参考如下一个例子:假如用户A有自己的一张表EMP,他要执行查询语句:select * from emp;用户B也有一张EMP表,同样要查询select * from emp;这样他们两条语句在文本上是一模一样的,他们的HASH值也会一样,但是由于涉及到查询的相关表不一样,他们事实上是无法共享的。假如这时候用户C又要查询同样一条语句,他查询的表为scott下的公有同义词,还有就是SCOTT也查询同样一张自己的表emp,情况会是如何呢?
SQL> connect a/a
Connected.
SQL> create table emp ( x int );
Table created.
SQL> select * from emp;
no rows selected
SQL> connect b/b
Connected.
SQL> create table emp ( x int );
Table created.
SQL> select * from emp;
no rows selected
SQL> conn scott/tiger
Connected.
SQL> select * from emp;
SQL> conn c/c
Connected.
SQL> select * from emp;
SQL> conn/as sysdba
Connected.
SQL> select address,hash_value, executions, sql_text
2 from v$sql
3 where upper(sql_text) like 'SELECT * FROM EMP%'
4 /
ADDRESS HASH_VALUE EXECUTIONS SQL_TEXT
-------- ---------- ---------- ------------------------
78B89E9C 3011704998 1 select * from emp
78B89E9C 3011704998 1 select * from emp
78B89E9C 3011704998 2 select * from emp
我们可以看到这四个查询的语句文本和HASH值都是一样的,但是由于查询的对象不同,只有后面两个语句是可以共享的,不同情况的语句还是需要硬解析的。因此在检查共享池共同SQL语句的时候,是需要根据具体情况而定的。
我们可以进一步查询v$sql_shared_cursor以得知SQL为何不能共享的原因:
SQL> select kglhdpar, address,
2 auth_check_mismatch, translation_mismatch
3 from v$sql_shared_cursor
4 where kglhdpar in
5 ( select address
6 from v$sql
7 where upper(sql_text) like 'SELECT * FROM EMP%' )
8 /
KGLHDPAR ADDRESS A T
-------- -------- - -
78B89E9C 786C9D78 N N
78B89E9C 786AC810 Y Y
78B89E9C 786A11A4 Y Y
TRANSLATION_MISMATCH表示SQL游标涉及到的数据对象是不同的;AUTH_CHECK_MISMATCH表示对同样一条SQL语句转换是不匹配的。
(2、)验证SQL语句执行环境是否相同。比如同样一条SQL语句,一个查询会话加了/*+ first_rows */的HINT,另外一个用户加/*+ all_rows */的HINT,他们就会产生不同的执行计划,尽管他们是查询同样的数据。我们下面就一个实例来说明SQL执行环境对解析的影响,我们通过将会话的workarea_size_policy变更来查看对同样一条SQL语句执行的影响:
SQL> alter system flush shared_pool;
System altered.
SQL> show parameter workarea_size_policy
NAME TYPE VALUE
------------------------------------ ----------- --------------
workarea_size_policy string AUTO
SQL> select count(*) from t;
COUNT(*)
----------
5736
SQL> alter session set workarea_size_policy=manual;
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
5736
SQL> select sql_text, child_number, hash_value, address
2 from v$sql
3 where upper(sql_text) = 'SELECT COUNT(*) FROM T'
4 /
SQL_TEXT CHILD_NUMBER HASH_VALUE ADDRESS
------------------------------ ------------ ---------- --------
select count(*) from t 0 2199322426 78717328
select count(*) from t 1 2199322426 78717328
可以看到由于不同会话workarea_size_policy设置得不同,即使是同样一条SQL语句还是无法共享的。通过进一步查询v$sql_shared_cursor我们可以发现两个会话的优化器环境是不同的:
SQL> select optimizer_mismatch
2 from v$sql_shared_cursor
3 where kglhdpar in
4 ( select address
5 from v$sql
6 where upper(sql_text) = 'SELECT COUNT(*) FROM T' );
O
-
N
Y
通过如上三个步骤检查以后,如果SQL语句是一致的,那么就会重用原有SQL语句的执行计划和优化方案,也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本,那么就需要进行硬解析了。
4、 Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话,那么CBO将会使用这些统计信息产生所有可能的执行计划(可能多达成千上万个)和相应的Cost,最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息,则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的,因此我们应该极力避免硬解析的产生。至此,解析的步骤已经全部完成,Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。
在Oracle中存在两种类型的SQL语句,一类为DDL语句,他们是从来不会共享使用的,也就是每次执行都需要进行硬解析。还有一类就是DML语句,他们会根据情况选择要么进行硬解析,要么进行软解析。在Oracle 8i OCP教材的023中1-12有说明SQL语句的解析步骤,当一条SQL语句从客户端进程传递到服务器端进程后,需要执行如下步骤:
? 在共享池中搜索 SQL 语句的现有副本
? 验证 SQL 语句的语法是否准确
? 执行数据字典查找来验证表和列的定义
? 获取对象的分析锁以便在语句的分析过程中对象的定义不会改变
? 检查用户访问引用方案对象的权限
? 确定语句的最佳执行计划
? 将语句和执行计划载入共享的 SQL 区
这个先入为主的概念一直占据着我的脑海,我认为硬解析就是上面几个步骤。相对于硬解析,软解析的步骤就是上面第一步找到现有SQL语句的副本后,只需要验证用户是否有权限执行就是了,这样省略上面好几个步骤,相对硬解析来说性能开销就非常小了。即使是在论坛上和大家讨论时,我也一直坚持这个看法。直到前一天看了Tom的《Effective Oracle By Design》中关于语句处理的章节后,我才知道这个自己一直坚持的观点事实上是错误的。
事实上,在Oracle中SQL语句的解析步骤如下:
1、 语法检测。判断一条SQL语句的语法是否符合SQL的规范,比如执行:SQL> selet * from emp;我们就可以看出由于Select关键字少了一个“c”,这条语句就无法通过语法检验的步骤了。
2、 语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确?用户是否有权限访问或更改相应的表或列?比如如下语句:
SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
由于查询用户没有可供访问的emp对象,因此该SQL语句无法通过语义检查。
3、 检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本,那么该SQL语句将会被软解析,也就是可以重用已解析过的语句的执行计划和优化方案,可以忽略语句解析过程中最耗费资源的步骤,这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤:
(1)验证SQL语句是否完全一致。在这个步骤中,Oracle将会对传递进来的SQL语句使用HASH函数运算得出HASH值,再与共享池中现有语句的HASH值进行比较看是否一一对应。现有数据库中SQL语句的HASH值我们可以通过访问v$sql、v$sqlarea、v$sqltext等数据字典中的HASH_VALUE列查询得出。如果SQL语句的HASH值一致,那么ORACLE事实上还需要对SQL语句的语义进行再次检测,以决定是否一致。那么为什么Oracle需要再次对语句文本进行检测呢?不是SQL语句的HASH值已经对应上了?事实上就算是SQL语句的HASH值已经对应上了,并不能说明这两条SQL语句就已经可以共享了。我们首先参考如下一个例子:假如用户A有自己的一张表EMP,他要执行查询语句:select * from emp;用户B也有一张EMP表,同样要查询select * from emp;这样他们两条语句在文本上是一模一样的,他们的HASH值也会一样,但是由于涉及到查询的相关表不一样,他们事实上是无法共享的。假如这时候用户C又要查询同样一条语句,他查询的表为scott下的公有同义词,还有就是SCOTT也查询同样一张自己的表emp,情况会是如何呢?
SQL> connect a/a
Connected.
SQL> create table emp ( x int );
Table created.
SQL> select * from emp;
no rows selected
SQL> connect b/b
Connected.
SQL> create table emp ( x int );
Table created.
SQL> select * from emp;
no rows selected
SQL> conn scott/tiger
Connected.
SQL> select * from emp;
SQL> conn c/c
Connected.
SQL> select * from emp;
SQL> conn/as sysdba
Connected.
SQL> select address,hash_value, executions, sql_text
2 from v$sql
3 where upper(sql_text) like 'SELECT * FROM EMP%'
4 /
ADDRESS HASH_VALUE EXECUTIONS SQL_TEXT
-------- ---------- ---------- ------------------------
78B89E9C 3011704998 1 select * from emp
78B89E9C 3011704998 1 select * from emp
78B89E9C 3011704998 2 select * from emp
我们可以看到这四个查询的语句文本和HASH值都是一样的,但是由于查询的对象不同,只有后面两个语句是可以共享的,不同情况的语句还是需要硬解析的。因此在检查共享池共同SQL语句的时候,是需要根据具体情况而定的。
我们可以进一步查询v$sql_shared_cursor以得知SQL为何不能共享的原因:
SQL> select kglhdpar, address,
2 auth_check_mismatch, translation_mismatch
3 from v$sql_shared_cursor
4 where kglhdpar in
5 ( select address
6 from v$sql
7 where upper(sql_text) like 'SELECT * FROM EMP%' )
8 /
KGLHDPAR ADDRESS A T
-------- -------- - -
78B89E9C 786C9D78 N N
78B89E9C 786AC810 Y Y
78B89E9C 786A11A4 Y Y
TRANSLATION_MISMATCH表示SQL游标涉及到的数据对象是不同的;AUTH_CHECK_MISMATCH表示对同样一条SQL语句转换是不匹配的。
(2、)验证SQL语句执行环境是否相同。比如同样一条SQL语句,一个查询会话加了/*+ first_rows */的HINT,另外一个用户加/*+ all_rows */的HINT,他们就会产生不同的执行计划,尽管他们是查询同样的数据。我们下面就一个实例来说明SQL执行环境对解析的影响,我们通过将会话的workarea_size_policy变更来查看对同样一条SQL语句执行的影响:
SQL> alter system flush shared_pool;
System altered.
SQL> show parameter workarea_size_policy
NAME TYPE VALUE
------------------------------------ ----------- --------------
workarea_size_policy string AUTO
SQL> select count(*) from t;
COUNT(*)
----------
5736
SQL> alter session set workarea_size_policy=manual;
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
5736
SQL> select sql_text, child_number, hash_value, address
2 from v$sql
3 where upper(sql_text) = 'SELECT COUNT(*) FROM T'
4 /
SQL_TEXT CHILD_NUMBER HASH_VALUE ADDRESS
------------------------------ ------------ ---------- --------
select count(*) from t 0 2199322426 78717328
select count(*) from t 1 2199322426 78717328
可以看到由于不同会话workarea_size_policy设置得不同,即使是同样一条SQL语句还是无法共享的。通过进一步查询v$sql_shared_cursor我们可以发现两个会话的优化器环境是不同的:
SQL> select optimizer_mismatch
2 from v$sql_shared_cursor
3 where kglhdpar in
4 ( select address
5 from v$sql
6 where upper(sql_text) = 'SELECT COUNT(*) FROM T' );
O
-
N
Y
通过如上三个步骤检查以后,如果SQL语句是一致的,那么就会重用原有SQL语句的执行计划和优化方案,也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本,那么就需要进行硬解析了。
4、 Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话,那么CBO将会使用这些统计信息产生所有可能的执行计划(可能多达成千上万个)和相应的Cost,最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息,则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的,因此我们应该极力避免硬解析的产生。至此,解析的步骤已经全部完成,Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。
发表评论
-
Oracle同义词,给你带来的几个惊喜
2009-03-03 19:59 1496在2008年,甲骨文在同SAP ... -
Oracle架构的基础知识(入门级)
2009-03-03 19:58 1487学习oracle,最好首先了解Oracle的框架。这样对Ora ... -
ORACLE中的各种数据类型详细的介绍
2009-03-03 19:56 664当你在数据库中创建数据表的时候,你需要定义表中所有字段的类型。 ... -
Oracle触发器详细介绍
2009-03-03 19:55 670触发器 是特定事件出 ... -
Oracle的初学者入门心得
2009-03-03 19:53 726Oracle的初学者入门心得 ... -
抽象数据类型,让Oracle数据库数据管理更加容易
2009-03-03 19:52 728姓名,其实包括两个部 ... -
深入解析Oracle数据库安全策略
2009-03-03 19:50 778Oracle是关系型数据库管 ... -
Oracle实例和Oracle数据库详解
2009-03-03 18:30 1806一、ORACLE实例 1、O ... -
oracle 常用命令(三)
2009-03-03 16:00 13380 推荐二.数据定义 (DDL) 部分 1.CREATE ( ... -
ORACLE常用命令(二)
2009-03-03 15:59 852二、用户如何有效地利用数据字典 ORACLE的数据字典是 ... -
Oracle常用命令(一)
2009-03-03 15:57 534Oracle常用命令 第一章:日志管理 1.forci ... -
Oracle数据库游标使用方法
2009-03-03 15:55 1151SQL是用于访问Oracle数据库的语言,PL/SQL扩展和加 ... -
Oracle常用维护语句
2009-03-03 15:52 6971 创建表空间 CREATE TA ... -
SQL Server与Oracle、DB2三种数据库比较
2009-03-03 15:51 1117开发数据库应用,选择 ... -
Oracle调优综述
2009-03-03 15:49 1492在过去的十年中, Oracle ... -
Oracle数据字典使用入门
2009-03-03 15:48 720每个Oracle数据库中包括一个被称为“元数据”的集合,或者说 ... -
Oracle数据库支持笔记--完全指南
2009-03-03 15:46 689要顺利地解决一个问题很不容易 ,当看了Metalink上不完整 ...
相关推荐
sql语句解析的时候,先执行语法检查。看语句是否符合规范。 2、语义检查 这个阶段,数据库会去判断SQL语句是否真正具有相应的含义,比如说sql语句涉及的表,或者字段是否存在等。 3、shared pool ...
在回答这个问题前,我们先来回顾一下:在ORACLE数据库架构下,SQL语句由用户进程产生,然后传到相对应的服务端进程,之后由服务器进程执行该SQL语句,如果是SELECT语句,服务器进程还需要将执行结果回传给用户进程。...
*Sql语句中大量使用函数时候会导致很多索引无法使用上,要针对具体问题分析 4.其它 避免使用Select *,因为系统需要去帮你将*转换为所有的列名,这个需要额外去查询数据字典。 Count(1)和Count(*)差别不大。 ...
执行计划是指示Oracle如何获取和过滤数据、产生最终的结果集,是影响...当一条语句提交到Oracle后,SQL引擎会分为三个步骤对其处理和执行:解析(Parse)、执行(Execute)和获取(Fetch),分别由SQL引擎的不同组件完成。
但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功...
通常情况下,SQL语句的执行过程如下: Step1. SQL代码的语法(语法的正确性)及语义检查(对象的存在性与权限)。 Step2. 将SQL代码的文本进行哈希得到哈希值。 Step3. 如果共享池中存在相同的哈希值,则对这个命令...
10.5.3 捕获排序SQL语句 515 10.5.4 确定典型问题SQL 515 10.5.5 选择解决办法 517 10.5.6 进一步的调整优化 518 10.5.7 小结 520 10.6 一次横跨两岸的问题诊断 520 10.6.1 第一封求助邮件 520 10.6.2...
注:SQL语句的解析有软解析soft parse与硬解析hard parse之说,以下是5个步骤: 1:语法是否合法(sql写法) 2:语义是否合法(权限,对象是否存在) 3:检查该sql是否在共享池中存在 -- 如果存在,直接跳过4和5,运行sql....
§1.4.1 SQL语句处理顺序 29 §1.4.2 COMMIT语句处理顺序 32 §1.5 共享池 33 §1.6 块缓存(数据高速缓冲区) 33 §1.7 数据库写入进程 34 §1.8 日志写进程 34 §1.9 数据库检查点 34 §1.10 归档处理 35 §1.11 ...
查看SQL语句的解析情况 52 10.3.3.3. 查看Oracle数据库的冲突情况 52 10.3.3.4. CPU的优化调整方法 52 10.3.4. 网络配置的优化 52 10.3.5. Oracle碎片整理 53 10.3.5.1. 碎片是如何产生的 53 10.3.5.2. 碎片对系统...
系统级的环境变量一般在/etc/profile 文件中定义 在 CAMS 系统 与数据库 相关的环境变量就定义在/etc/profile 文件中 如下所示 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/...
可能造成CPU使用率高的情况有:大量排序、大量SQL解析、全表扫描、Oracle Bug等。因此希望找到占用CPU较高的进程ID(UNIX或LINUX)或线程ID(Windows)来找到对应的SQL语句,以分析问题的原因。 三、处理步骤 ...
以纯文本导出时,能够自动生成建表的SQL语句和SQL*Loader导入所需的control文件 30.支持DESC表,以显示表的列定义支持列出表的分区和子分区 31.支持对误删除数据的恢复,即使被删除数据的表中有LOB列,即使被删除...
一、什么是执行计划(explain plan) 执行计划:一条查询语句在ORACLE中的执行过程或访问路径的描述。 二、如何查看执行计划 1: 在PL/SQL下按F5查看执行计划。第三方工具toad等。 很多人以为PL/SQL的执行计划只能...
从技术实现思路上与streams几无相同之处,倒是逻辑standby与streams的实现方式非常想像,都是通过分析 redo生成重做的sql语句在目标端执行,如果要说差异的话,逻辑standby只提供了整库级的复制,从功能上来看...
表名称:text//参考text表创建sql语句详单text.sql 表字段:mc,sl,jg,zk//参考数据库中text表 需要导入的列:1,2,3,4//参考test.xls文件,A列=1,B列=2,C列=3...(一定要和表字段对应) CLOB表字段:clob//text表中clob字段...
也论该不该在项目中使用存储过程代替SQL语句 如何使数据库中的表更有弹性,更易于扩展 存储过程——天使还是魔鬼 如何获取MSSQLServer,Oracel,Access中的数据字典信息 C#中利用GetOleDbSchemaTable获取数据库内表信息...
5.3 常用经典SQL语句 224 5.4 事务处理 226 5.4.1 SQL和存储过程级别的事务 227 5.4.2 ADO.NET级别的事务 229 5.4.3 ASP.NET页面级别的事务 230 5.4.4 企业级服务COM+事务 231 5.4.5 System.Transactions 事务处理...
5.3 常用经典SQL语句 224 5.4 事务处理 226 5.4.1 SQL和存储过程级别的事务 227 5.4.2 ADO.NET级别的事务 229 5.4.3 ASP.NET页面级别的事务 230 5.4.4 企业级服务COM+事务 231 5.4.5 System.Transactions 事务处理...
8.用一条SQL语句 查询出每门课都大于80分的学生姓名 9.所有部门之间的比赛组合 10.每个月份的发生额都比101科目多的科目 11.统计每年每月的信息 12.显示文章标题,发帖人、最后回复时间 13.删除除了id号不同,...