Oracle对 SQL语句的数据库处理过程

1. Oracle SGA介绍

内存结构=SGA（系统全局区）+PGA（程序全局区）

PGA内部包含：用户Session信息、排序信息、Hasharea、堆栈。这些信息被后台进程所控制。

SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。我们重点就是设置SGA，理论上SGA可占OS系统物理内存的1/3—1/2。原则上SGA+PGA+OS使用内存<总物理RAM。

SGA系统全局区，主要包括，数据缓冲区（存储由磁盘数据文件读入的数据），重做日志缓冲区（存储数据库修改信息），共享池（sql缓存，pl/sql语法分析等），JAVA池，大池（用于数据库备份恢复管理器RMAN），流池等。

SQL> select * from v$sga;

 

NAME                      VALUE

-------------------- ----------

Fixed Size              2218112

Variable Size        2415921024

Database Buffers     1114007142

RedoBuffers           70868992

其中，Fixed Size：字典信息、控制信息、状态信息。

Variable Size：共享池（sharedpool）、Java池(Java Pool)、大池(Large Pool)、Stream pool；

Database Buffers：为数据缓冲区，OLTP系统要求这块设置较大。

Redo Buffer：重做日志缓冲区，适当提高缓冲区，减少文件组切换，可以提高效率。重点介绍的是共享池（shared pool）。

查询共享池大小：

SQL> showparameter shared_pool_size;

SQL> SELECT *FROM V$SGAINFO WHERE NAME='shared_pool_size';

共享池，由库高速缓存（librarycache）和数据字典高速缓存（data dictionary cache）组成。库高速缓存是用来共享SQL和PL/SQL代码的，SQL语句文本，解析代码值及其执行计划。数据字典高速缓存是当Oracle执行SQL时，会将相关的数据文件、表、索引、列、用户、其他的数据对象的定义和权限信息存放到数据字典高速缓存中。在此之后，如果需要相同的相关数据，Oracle会从数据字典高速缓存中提取。

共享池的大小是有限制的，所以ORACLE在共享池不够用的时候，采用基于LRU为核心的算法进行替换(V$SQL，V$SQLAREA中的PARSE_CALLS字段可以基本看出SQL被调用的次数，但是不完全依赖于它)，在内存中获取我们称为命中，命中率高才能提高利用率，系统的整体性能才能得到保证。

2. 系统连接数，进程数查看

Show parameter session----session

Alter system set session =××× scope=spfile；

Show parameter process ---process

Alter system set process=××× scope=spfile；

Session=process×1.1 +5

重启oracle服务器生效 shutdown immediately startup

 

查看当前数据库并发连接数

select count(*) from v$session where status='ACTIVE';

查看有哪些用户正在使用数据：

Selectosuser,username,cpu_time/executions/1000000,sql_fulltext,machine from v$sessiona, v$sqlarea b where a.SQL_ADDRESS=b.ADDRESS order by cpu_time/execution desc;

 

3. 连接进入DB sever 后的流程

SQL由PMON进程传入数据库服务器，首先有一个相关的进程号和session号，即先写了V$PROCESS和V$SESSION，至此就有了该SQL的SQL_ID。进入数据库后SQL的流程走向如下图所示。

   

为了获取用户连接到数据库中的信息，需要先从V$SESSION视图确定用户的SID号，然后用V$SESSION 和V$SQL查看相关信息。

通过得到的HASH_VALUE或者ADDREDSS或者SQL_ID都可以通过以下视图得到对应执行SQL的全部内容（当SQL较长的时候，V$SQLAREA只保存前面一部分，全部内容在视图中）：

SELECT * FROM V$SQLTEXT_WITH_NEWLINES;

通过以下语句得到SQL的执行计划：

SELECT * FROM V$SQL_PLAN;

通过以下语句得到对共享池设置的建议值，ORACLE根据实际运行情况，推荐的值：

SELECT * FROM V$SHARED_POOL_ADVANCE;

通过以下语句得到SQL绑定变量信息：

SELECT * FROM V$SQL_BIND_CAPTURE;

通过以下语句得到SQL占用共享池内存：

SELECT * FROM V$SQL_SHARED_MEMORY;

通过以下语句得到SQL消耗调用的统计信息：

SELECT * FROM V$SQLSTATS;

由此可知，在一条SQL语句进入服务器端后，都会在这些存在SGA库缓存中的动态性能试图对应的表做相应的记录。而SQL的流程如下所述。

SQL从终端通过1521 TCP服务端口以字符串方式传送至ORACLE后，ORACLE通过HASH算法对其SQL转换，并在共享池中查找是否存在同样HASH值的SQL（即：SQL即使是参数或者大小写不同，也会导致找不到一样的HASH值），如果找到了，直接执行已经编译完的SQL，并修改使用率；若没有，则首先通过硬解析工具对其进行各项语法分析和性能指标分析等等，然后开始征用共享池（此为共享资源），并注册到共享池中，标志调用次数为1，然后再执行，再将结果返回给用户。当在大量征用共享资源时候，并且在硬解析过程中，高并发将导致阻塞。

硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的门闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和CPU的数量来解决。

之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。

绑定变量的实质就是用于替代SQL语句中的常量的替代变量。绑定变量能够使得每次提交的SQL语句都完全一样。绑定变量只是起到占位的作用，同名的绑定变量并不意味着在它们是同样的，在传递时要考虑的是传递的值与绑定变量出现顺序的对位，而不是绑定变量的名称。

绑定变量为ORACLE系统提供了很多优点，减少资源占用，降低资源争用，加速了SQL解析的过程，但同时也带来了某些缺点，比如对于数据分布严重不均衡 的列可能产生不正确的执行计划，很难得到绑定变量实际传入的值。在执行计划不正确的时候，我们要判断到底是因为不均衡的列的窥视带来的问题，还是统计信息的问题，还是BUG或者其他问题，这时候就要看看到底绑定变量传入的值是什么。

ORACLE提供了V$SQL_BIND_CAPTURE视图来查看这个绑定变量的值，但这个值15分钟才更新一次。ORACLE的隐含参数_cursor_bind_capture_interval，是控制绑定变量抓取频率的参数，默认值是900，单位是秒，也就是15分钟。可通过下列语句修改抓取频率。

SQL> altersystem set "_cursor_bind_capture_interval"=×× scope=both;

但频繁的抓取对系统性能会有些影响，如果实在需要，可以根据系统压力情况去逐步的缩小这个值，达到满意的范围。

Oracle在应用中还会经常用到的一种方法是SQL的预编译。预编译可以充分利用软解析，存储过程默认就会按照预编译方式去执行。

预编译接口PreparedStatement是java.sql中的一个接口，它是Statement的一个自接口。Statement执行SQL语句时，需要将完整的SQL发送给数据库，数据库经过编译后再执行。预编译的语句和Statement对象不同，在创建PreparedStatement对象时指定了SQL语句，该SQL语句立刻发送给数据库进行编译。当编译语句执行时，直接运行编译后的SQL语句，而不像其他SQL语句先经过编译在执行。预编译的SQL语句的处理性能稍微高于普通的传递变量的方法。