oracle rac一个节点执行语句很慢_浅析ORACLE数据库物理体系结构及其对应优化策略...

 

张志国

合肥科技研发中心

Oracle数据库服务器有两个主要的组成部分：数据库和实例(instance)。Oracle数据库用于存储和检索信息，是数据的集合。Oracle实例是指数据库服务器的内存及相关处理程序。

正因为如此，我们一般才会说关闭和启动实例，加载卸载数据库，就是这个道理。从实例和数据库的概念上来看，我们能知道，实例是暂时的，它不过是一组逻辑划分的内存结构和进程结构，会随着数据库的关闭而消失，而数据库其实就是一堆物理文件(控制文件，数据文件，日志文件等等)，它是永久存在的(除非磁盘损坏)。数据库和实例通常是一对一的，这种结构我们称为单实例体系结构。当然还有一些复杂的分布式的结构，一个数据库可以对多个实例，像Oracle的RAC(有兴趣的童鞋可以了解下)。

一、Oracle数据库的物理体系结构

如上图，我们可知： ① Oracle由实例instance和数据库database组成。 ② 实例是由一个共享内存区SGA (G=Global)和一系列后台进程组成。SGA划分为共享池、数据缓冲区和日志缓冲区三类。后台进程包括DBWR、LGWR、CKRT、ARCH等。 ③ 数据库由数据文件、参数文件、日志文件、控制文件、归档日志等组成，其中归档日志最终会被转移到存储介质中，用于备份和恢复数据使用。 ④ PGA也是一块开辟出来的内存区，和SGA区别在于，PGA是私有的。作用主要包括：保存用户的连接信息，如会话和绑定变量，保存用户权限等信息，承担一部分(排序尺寸太大的话，超出的部分放在临时表空间进行)排序功能。

二、由物理体系结构探查SQL执行步骤

一条查询语句：SELECT T.OBJECT_ID FROM DBA_OBJECTS T WHERE T.OBJECT_ID = 12;Oracle会执行如下步骤：

(1)这个时候先完成PGA的3步骤中的头2步：保存会话信息，用户权限信息，然后将sql语句hash出一条hash值。

(2)带着hash值，sql进入了第二个区域：共享池，在这里首先查找是否有同样的hash值。如果没有，那么就比较辛苦：首先查询语法是否正确，然后查询语义(表和字段有没有写错)是否正确，是否有权限等，确认完这些，将hash值存储下来。接下来进行硬解析，经过优化器分析以后Oracle会选择一个低成本的执行计划。

(3)带着执行计划的sql接下来进入第三个区域：数据缓存区，去获得需要的数据，如果查到该数据，则返回给sql带回到PGA。如果没有，就要大费周章地去磁盘(增大IO降低速度)里面查找。查找的方式就是按照执行计划来。读出来的数据会被放回数据缓存区和PGA。

但是，执行同样一条查询语句，第二次的时间明显变少了。第一次执行耗时：2.547S，第二次执行耗时：0.141S。前后两次执行同一条语句，第二次比第一次少很多时间。原因是第二次少做了一些事情：

(1)因为会话没有断开，所以不需要再去数据库读取权限和用户信息，少了很多物理读。

(2)因为是第二次执行同一条语句，所以SGA的共享池里已经保存了相对应的hash值，比照一样之后，不需要做语法语义的验证，也不需要做硬解析获得优化的执行计划，只要把已经解析好的执行计划拿来用就好。

(3)数据被取到SGA的数据缓存区里面，也不需要去硬盘读。

三、由物理体系得到优化方案

由以上的访问步骤可以知道，只要理解好数据库的物理结构，就可以得到一些优化思路：采用绑定变量的方式，可以避免多次硬解析。

优化前：

create table t (x int);

create or replace procedure proc1

as

begin

for i in 1..100000

loop

        execute immediate

        ‘insert into t values(‘||i||’)’;

commit;

end loop;

end;

/

以上为存储过程的定义

 exec proc1; 执行耗时42.87s；

优化后：采用绑定变量方式，减少解析时间

drop table t purge;

create table t (x int);

create or replace procedure proc2

as

begin

for i in 1..100000

loop

        execute immediate

        'insert into t values( :x )' using i;

commit;

end loop;

end;

/

以上为存储过程的定义

 exec proc2; 执行耗时8.41s

四、小 结

因为Oracle有缓存的功能，如果有缓存SQL相应的连接信息、权限信息、执行计划、数据等，不用去磁盘上读取，可以减少物理读。所以执行相同的SQL，第二次比第一次快。另外，采用绑定变量的方式，可以避免多次硬解析的操作，节约执行时间，也可以达到优化的效果。

文章来源：公众号“I生活T精彩”

你“在看”我吗