Oracle共享池缓冲多进程共享及加快缓存效果（oracle 共享池缓冲）

Oracle共享池缓冲：多进程共享及加快缓存效果

Oracle数据库作为企业级应用中的主流数据库，应用场景越来越复杂，访问量也越来越大，数据缓存管理成为数据库性能优化的重点。其中Oracle共享池缓冲（Shared Pool Buffer）是核心部件之一，它存储在内存中的SQL语句、存储过程、视图数据等结构，是对于后续的重复执行请求能够得到优化的重要手段。

然而，随着业务的扩展，多进程访问数据库的情况越来越常见，如果Oracle共享池缓冲不能很好地支持多进程场景，就会导致SQL语句缓存失效，导致性能降低。本文将介绍如何通过多进程共享、扩展Shared Pool Buffer和加快缓存效果的方法来解决这些问题。

一、多进程共享的实现

默认情况下，Oracle的Shared Pool Buffer是基于每个会话独立缓存的，即每个连接需要一个独立的缓存。但是，这种模式无法满足多进程访问的需求，因此可以采用以下两种方式实现多进程共享：

1. Oracle Connection Pool（连接池）

连接池是以连接复用来优化性能的一种技术，通过将开销大的连接复用于多个请求，减少了创建和关闭连接的开销。在Oracle数据库中，可以使用基于线程的连接池技术，将多个进程共同使用一个连接池，从而实现多进程共享。

以下是基于Java语言实现多进程共享Shared Pool Buffer的代码示例：

“`java

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import oracle.jdbc.pool.OracleConnectionPoolDataSource;

import oracle.jdbc.pool.OracleDataSource;

public class DataSourceTest {

public static void mn(String[] args) throws Exception {

// Oracle连接池

OracleConnectionPoolDataSource ds = new OracleConnectionPoolDataSource();

ds.setUser(“scott”);

ds.setPassword(“tiger”);

ds.setURL(“jdbc:oracle:thin:@localhost:1521:orcl”);

// 获取数据库连接

Connection conn = ds.getConnection();

// 创建Statement对象

Statement stmt = conn.createStatement();

// 执行SELECT语句

ResultSet rs = stmt.executeQuery(“SELECT * FROM emp”);

// 输出结果集

while (rs.next()) {

System.out.println(rs.getInt(“empno”) + “,” + rs.getString(“ename”));

}

// 关闭资源

rs.close();

stmt.close();

conn.close();

}

}

2. Database Resident Connection Pool（DRCP数据库驻留连接池）

与连接池不同的是，DRCP是Oracle提供的一种强大的可扩展性和可用性高的连接池技术。DRCP的主要优势在于，它可以在一个共享的数据库实例中维护多个连接池，这些连接池可以被多个进程或线程之间共享。

在Oracle11g中，使用DRCP实现Shared Pool Buffer的多进程共享非常方便。只需要在jdbc url中加入 " (SERVER=POOLED)"，就可以开启DRCP并共享Shared Pool Buffer。以下是Java语言实现的示例代码：

```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import oracle.jdbc.pool.OracleDataSource;

public class DataSourceTest {

    public static void mn(String[] args) throws Exception {

        // Oracle DRCP连接池
        OracleDataSource ds = new OracleDataSource();
        ds.setUser("scott");
        ds.setPassword("tiger");
        ds.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl))(SERVER=POOLED))");

        // 获取数据库连接
        Connection conn = ds.getConnection();
        // 创建Statement对象
        Statement stmt = conn.createStatement();
        // 执行SELECT语句
        ResultSet rs = stmt.executeQuery("SELECT * FROM emp");
        // 输出结果集
        while (rs.next()) {
            System.out.println(rs.getInt("empno") + "," + rs.getString("ename"));
        }
        // 关闭资源
        rs.close();
        stmt.close();
        conn.close();
    }
}

二、扩展Shared Pool Buffer

Oracle共享池缓冲是物理内存区域，可在Oracle的实例模式（Instance Mode）下通过SPOOLCP命令动态增加Memory Area并扩展Shared Pool Buffer的大小。可以通过以下步骤实现Shared Pool Buffer的扩展：

1. 查看Shared Pool Buffer的当前大小

“`sql

SELECT NAME, BYTES FROM V$SGASTAT WHERE NAME = ‘shared pool’;

2. 使用SPOOLCP命令动态增加Memory Area，并扩展Shared Pool Buffer的大小

```sql
ALTER SYSTEM SET SGA_TARGET=1500M SCOPE=SPFILE;
ALTER SYSTEM SET SGA_MAX_SIZE=1800M SCOPE=SPFILE;
ALTER SYSTEM SET SGA_SIZE=1800M SCOPE=BOTH;

其中，SGA_TARGET表示要分配给Oracle实例的整个内存大小，SGA_MAX_SIZE表示SGA_TARGET的最大值，SGA_SIZE表示当前实例中的SGA大小，SCOPE参数指示更改系统级别还是当前进程级别。最终实例的SGA大小将扩大到1800M，其中Shared Pool Buffer的分配比例取决于“Shared Pool Size”参数的值。

三、加快缓存效果

高效的缓存会直接影响到应用的性能，如果能够加快Shared Pool Buffer的缓存效果，就可以有效提升数据库的性能。以下是几种可以加快Shared Pool Buffer缓存效果的方式：

1. 减少文本SQL语句长度

文本SQL语句长而复杂，会占用更多的Shared Pool Buffer空间，从而降低命中率。因此，建议将SQL语句放在存储管道（PL/SQL块）中，在数据库中引用存储PIPELINE名称。通过这种方式可以减少SQL语句长度，进而提高命中率。

以下是示例代码：

“`sql

CREATE OR REPLACE PACKAGE my_pipes AS

TYPE t_emp IS RECORD

(empno NUMBER(4),

ename VARCHAR2(10));

TYPE t_emp_tab IS TABLE OF t_emp;

FUNCTION get_emp_list RETURN t_emp_tab PIPELINED;

END my_pipes;

/

CREATE OR REPLACE PACKAGE BODY my_pipes AS

FUNCTION get_emp_list RETURN t_emp_tab PIPELINED IS

BEGIN

FOR c IN (SELECT empno, ename FROM emp) LOOP

PIPE ROW (c);

END LOOP;

RETURN;

END get_emp_list;

END my_pipes;

2. 排除不必要的调用

在编写存储管道或Java存储过程时，请告诉数据库需要使用哪些SQL语句。否则，数据库服务器将尝试解释并缓存每个SQL语句，这会导致Shared Pool Buffer被占满并降低命中率。

可以通过以下方法排除不必要的SQL调用：

a.在存储管道或Java存储过程中，除了必要的SQL语句之外，不要执行其他SQL语句；

b.将SQL与动态SQL分离，这样共享池中的语句对所有进程都是可用的；

c.使用已编译的PL/SQL语句，而不是使用动态SQL或轻微的变化；

d.禁用Java中的AutoCommit选项（默认为开启）并使用显式事务。

3. 注重内存和CPU的使用

在共享池缓冲中，考虑到内存和CPU的使用效率，应该始终遵循以下原则：

1.尽量少使用解析器

2.尽量不使用锁

3.最小化并行处理

结论

Oracle共享池