印刻数据库

外观

Oracle 连接管理最佳实践 ​

连接管理是Oracle数据库运维的核心组成部分，直接影响数据库的性能、稳定性和安全性。有效的连接管理可以优化资源使用、防止连接泄漏、提高响应速度，并确保数据库的安全运行。

连接管理概述 ​

核心目标 ​

  • 优化连接资源的使用效率
  • 防止连接泄漏和资源耗尽
  • 提高数据库的响应速度
  • 确保连接的安全性和合规性
  • 便于监控和管理数据库连接

关键组件 ​

  • 会话：用户与数据库的交互单元
  • 连接池：管理和复用数据库连接的机制
  • 监听器：处理客户端连接请求的服务
  • 资源配置文件：限制用户资源使用的策略

会话管理 ​

查看数据库会话 ​

在生产环境中，DBA需要定期查看会话状态，及时发现异常会话。以下是常用的查询语句：

sql
-- 查看所有会话，包含详细信息
SELECT sid, serial#, username, status, machine, program, logon_time, last_call_et/60 AS idle_minutes
FROM v$session
ORDER BY logon_time DESC;

-- 查看活跃会话，重点关注正在执行SQL的会话
SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.status, 
       t.sql_id, t.sql_text, s.last_call_et/60 AS active_minutes
FROM v$session s
JOIN v$sqlarea t ON s.sql_id = t.sql_id
WHERE s.status = 'ACTIVE' AND s.username IS NOT NULL;

-- 查看会话等待事件，分析性能瓶颈
SELECT s.sid, s.serial#, s.username, s.machine, w.event, w.wait_time, w.seconds_in_wait
FROM v$session s
JOIN v$session_wait w ON s.sid = w.sid
WHERE s.status = 'ACTIVE' AND s.username IS NOT NULL
ORDER BY w.seconds_in_wait DESC;

-- 按客户端机器分组，查看连接分布
SELECT machine, COUNT(*) AS connection_count
FROM v$session
WHERE username IS NOT NULL
GROUP BY machine
ORDER BY connection_count DESC;

终止会话 ​

在生产环境中，遇到长时间运行、阻塞其他会话或消耗过多资源的会话时，需要及时终止：

sql
-- 终止单个会话（软终止，允许会话完成当前事务）
ALTER SYSTEM KILL SESSION '123,45678';

-- 强制终止会话（立即终止，适用于阻塞会话）
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;

-- 使用OS进程ID终止会话（适用于无法通过ALTER SYSTEM终止的会话）
SELECT spid, sid, serial#, username, machine
FROM v$process p
JOIN v$session s ON p.addr = s.paddr
WHERE s.username = 'SCOTT';

-- 在Linux系统上终止进程
-- kill -9 <spid>

-- 在Windows系统上终止进程
-- taskkill /F /PID <spid>

会话终止注意事项 ​

  • 终止会话前，先确认会话是否正在执行关键业务操作
  • 优先使用软终止，避免数据不一致
  • 记录终止会话的原因和时间，便于后续分析
  • 对于频繁出现的问题会话，需要从根源解决，而不是频繁终止

连接池配置 ​

应用层连接池 ​

应用层连接池是最常用的连接管理方式，由应用服务器或中间件提供，如WebLogic、Tomcat、Spring Boot等。

配置建议 ​

参数建议值说明
初始连接数10-20根据系统负载设置，避免初始连接过多导致资源浪费
最大连接数CPU核心数的2-4倍根据数据库服务器资源和预期负载调整
连接超时时间30-60秒避免长时间等待无效连接
验证连接有效性启用确保从连接池获取的连接可用，建议使用SELECT 1 FROM DUAL
最小空闲连接数5-10保持一定数量的空闲连接，减少连接建立开销
空闲连接超时时间1800秒（30分钟）释放长时间空闲的连接，避免资源浪费

Spring Boot连接池示例配置 ​

yaml
spring:
  datasource:
    url: jdbc:oracle:thin:@//hostname:1521/service_name
    username: scott
    password: tiger
    driver-class-name: oracle.jdbc.OracleDriver
    hikari:
      minimum-idle: 10
      maximum-pool-size: 50
      connection-timeout: 30000
      validation-timeout: 5000
      idle-timeout: 1800000
      max-lifetime: 3600000
      connection-test-query: SELECT 1 FROM DUAL

Oracle数据库连接池（DRCP） ​

DRCP（Database Resident Connection Pooling）是Oracle提供的服务器端连接池，适用于大量短连接场景，如Web应用。

配置步骤 ​

  1. 启用DRCP
sql
-- 启动DRCP后台进程
EXECUTE dbms_connection_pool.start_pool;

-- 查看DRCP状态
SELECT connection_pool, status, minsize, maxsize, incrsize FROM dba_cpool_info;
  1. 优化DRCP配置
sql
-- 修改DRCP配置，根据实际负载调整参数
EXECUTE dbms_connection_pool.configure_pool(
  pool_name => 'SYS_DEFAULT_CONNECTION_POOL',
  minsize => 20,           -- 最小池大小
  maxsize => 200,          -- 最大池大小
  incrsize => 10,          -- 每次增加的连接数
  session_cached_cursors => 100, -- 会话缓存游标数
  inactivity_timeout => 600, -- 非活动连接超时（秒）
  max_think_time => 120,   -- 最大思考时间（秒）
  max_use_session => 2000,  -- 会话最大使用次数
  max_lifetime_session => 86400 -- 会话最大生命周期（秒）
);

-- 重启DRCP使配置生效
EXECUTE dbms_connection_pool.restart_pool;
  1. 使用DRCP连接

在连接字符串中添加SERVER=POOLED参数：

-- JDBC连接字符串
jdbc:oracle:thin:@//hostname:1521/service_name:POOLED

-- SQL*Plus连接示例
sqlplus scott/tiger@//hostname:1521/service_name:POOLED

DRCP适用场景 ​

  • 大量短连接的Web应用
  • 资源受限的数据库服务器
  • 需要集中管理连接的场景
  • 频繁建立和关闭连接的应用

连接限制与资源管理 ​

资源配置文件 ​

资源配置文件用于限制用户的资源使用，防止单个用户消耗过多资源：

sql
-- 创建资源配置文件，针对不同用户设置不同限制
CREATE PROFILE app_user_profile LIMIT
  SESSIONS_PER_USER 10         -- 每个用户最大会话数
  CPU_PER_SESSION UNLIMITED     -- 每个会话CPU使用限制
  CPU_PER_CALL 5000             -- 每个调用CPU使用限制（毫秒）
  CONNECT_TIME 120              -- 连接时间限制（分钟）
  IDLE_TIME 30                  -- 空闲时间限制（分钟）
  LOGICAL_READS_PER_SESSION UNLIMITED -- 每个会话逻辑读限制
  LOGICAL_READS_PER_CALL 2000   -- 每个调用逻辑读限制
  PRIVATE_SGA 20M               -- 每个会话私有SGA限制
  COMPOSITE_LIMIT UNLIMITED;    -- 复合资源限制

-- 分配资源配置文件给用户
ALTER USER scott PROFILE app_user_profile;

-- 查看用户资源配置
SELECT username, profile FROM dba_users WHERE username = 'SCOTT';

系统级连接限制 ​

系统级连接限制决定了数据库可以接受的最大连接数，需要根据服务器资源调整：

sql
-- 查看当前系统连接配置
SHOW PARAMETER processes;
SHOW PARAMETER sessions;
SHOW PARAMETER transactions;

-- 计算关系：sessions = processes * 1.1 + 5
--         transactions = sessions * 1.1

-- 修改系统连接限制（需要重启数据库）
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=555 SCOPE=SPFILE;
ALTER SYSTEM SET transactions=610 SCOPE=SPFILE;

-- 查看当前连接使用情况
SELECT 
  (SELECT COUNT(*) FROM v$session) AS current_connections,
  (SELECT value FROM v$parameter WHERE name = 'processes') AS max_processes,
  (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE') AS active_connections,
  (SELECT COUNT(*) FROM v$session WHERE status = 'INACTIVE') AS inactive_connections;

连接性能优化 ​

优化会话参数 ​

sql
-- 启用会话游标缓存，减少软解析
ALTER SYSTEM SET session_cached_cursors=100 SCOPE=BOTH;

-- 调整共享池大小，减少硬解析
ALTER SYSTEM SET open_cursors=100 SCOPE=BOTH;

-- 使用绑定变量，减少硬解析
ALTER SYSTEM SET cursor_sharing=EXACT SCOPE=BOTH;

-- 启用结果缓存，减少硬解析
ALTER SYSTEM SET result_cache_max_size=100M SCOPE=BOTH;

-- 启用自动PGA管理，减少硬解析
ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=BOTH;

-- 启用自动共享内存管理，减少软解析
ALTER SYSTEM SET shared_pool_size=2G SCOPE=BOTH;

优化网络配置 ​

sql
-- 启用TCP_NODELAY，禁用Nagle算法，减少延迟
ALTER SYSTEM SET tcp_nodelay=TRUE SCOPE=BOTH;

-- 启用自动统计，减少硬解析
ALTER SYSTEM SET statistics_level=TYPICAL SCOPE=BOTH;

减少硬解析 ​

sql
-- 使用绑定变量，减少硬解析
ALTER SYSTEM SET cursor_sharing=FORCE SCOPE=BOTH;

-- 启用自动优化，减少硬解析
ALTER SYSTEM SET automatic_sql_tuning=ON SCOPE=BOTH;

连接监控与故障排查 ​

监控连接状态 ​

sql
-- 查看当前连接数
SELECT COUNT(*) FROM v$session;

-- 查看连接状态分布
SELECT status, COUNT(*) FROM v$session GROUP BY status;

-- 查看连接来源分布
SELECT machine, COUNT(*) FROM v$session GROUP BY machine ORDER BY COUNT(*) DESC;

-- 查看用户连接分布
SELECT username, COUNT(*) FROM v$session GROUP BY username ORDER BY COUNT(*) DESC;

排查连接问题 ​

sql
-- 查看连接等待事件，分析瓶颈
SELECT event, COUNT(*) FROM v$session_wait GROUP BY event ORDER BY COUNT(*) DESC;

-- 查看长时间运行的SQL
SELECT s.sid, s.serial#, s.username, s.machine, s.program, 
       s.last_call_et/60 AS minutes_running, t.sql_text
FROM v$session s
JOIN v$sql t ON s.sql_id = t.sql_id
WHERE s.last_call_et > 3600
ORDER BY s.last_call_et DESC;

监控连接池 ​

sql
-- 监控连接池状态
SELECT 
  pool_name, 
  count(*) AS total_connections, 
  sum(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_connections,
  sum(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) AS inactive_connections
FROM 
  v$connection_pool
GROUP BY 
  pool_name;

监控连接性能 ​

sql
-- 监控连接性能
SELECT 
  s.sid, 
  s.serial#, 
  s.username, 
  s.machine, 
  s.program, 
  s.status, 
  s.last_call_et, 
  s.sql_id, 
  t.sql_text,
  EXTRACT(HOUR FROM (SYSDATE - s.logon_time)) * 60 + EXTRACT(MINUTE FROM (SYSDATE - s.logon_time)) AS minutes_connected
FROM 
  v$session s
JOIN 
  v$sqltext t ON s.sql_id = t.sql_id
WHERE 
  s.status = 'ACTIVE'
ORDER BY 
  s.last_call_et DESC;

Oracle 19c vs 21c 连接管理差异 ​

特性Oracle 19cOracle 21c
DRCP增强基础功能增强了DRCP的性能和可扩展性
连接池监控有限新增了更多的DRCP监控视图
会话管理基础功能增强了会话管理的安全性
连接限制支持新增了更细粒度的连接限制选项
连接加密支持增强了连接加密的性能
会话隔离支持新增了会话隔离功能
连接复用支持优化了连接复用机制
自动优化基础支持增强了自动优化功能

常见问题（FAQ） ​

如何处理数据库连接数耗尽的问题？ ​

处理数据库连接数耗尽的步骤：

  1. 查看当前连接数：SELECT COUNT(*) FROM v$session;
  2. 查看连接数限制：SHOW PARAMETER processes;
  3. 分析连接来源：SELECT machine, program, COUNT(*) FROM v$session GROUP BY machine, program;
  4. 终止无用连接：ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
  5. 调整连接池配置：减少应用层连接池的最大连接数
  6. 考虑增加系统连接数限制（需要重启数据库）

如何识别和处理连接泄漏？ ​

识别和处理连接泄漏的方法：

  1. 监控长时间空闲的会话：SELECT sid, serial#, username, machine, LAST_CALL_ET/60 AS idle_minutes FROM v$session WHERE status = 'INACTIVE' AND LAST_CALL_ET > 3600;
  2. 分析应用日志，查找未正确关闭连接的代码
  3. 在应用层实现连接泄漏检测机制
  4. 设置资源配置文件的IDLE_TIME限制，自动终止空闲会话
  5. 定期重启应用服务器，释放泄漏的连接

如何优化大量短连接的性能？ ​

优化大量短连接性能的方法：

  1. 使用连接池（应用层或DRCP）
  2. 启用DRCP（Database Resident Connection Pooling）
  3. 优化监听器配置，减少连接建立时间
  4. 使用共享服务器模式（适用于大量短连接场景）
  5. 调整会话参数，减少连接资源消耗

如何监控数据库连接的性能？ ​

监控数据库连接性能的方法：

  1. 监控连接建立时间：使用SQL*Plus的SET TIMING ON或应用层监控
  2. 监控会话等待事件：SELECT event, COUNT(*) FROM v$session_wait GROUP BY event;
  3. 监控会话的CPU和内存使用：SELECT sid, username, value FROM v$sesstat s, v$statname n WHERE s.statistic# = n.statistic# AND (n.name = 'CPU used by this session' OR n.name = 'session pga memory');
  4. 监控硬解析率：SELECT (1 - (sql_cursor_cache_hits / (sql_cursor_cache_hits + sql_cursor_cache_misses))) * 100 AS hard_parse_rate FROM v$sysstat;

如何处理会话死锁？ ​

处理会话死锁的步骤：

  1. 检测死锁：SELECT * FROM v$locked_object;
  2. 查看死锁详情：SELECT l.session_id, s.serial#, s.username, s.machine, o.object_name FROM v$locked_object l, v$session s, dba_objects o WHERE l.session_id = s.sid AND l.object_id = o.object_id;
  3. 终止导致死锁的会话：ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
  4. 分析死锁原因，优化应用程序逻辑，避免死锁再次发生

如何提高数据库连接的安全性？ ​

提高数据库连接安全性的方法：

  1. 使用强密码策略
  2. 启用SSL/TLS加密连接
  3. 限制连接来源IP地址
  4. 使用最小权限原则分配用户权限
  5. 启用连接审计
  6. 定期轮换数据库密码
  7. 使用数据库防火墙

如何配置DRCP以优化性能？ ​

配置DRCP优化性能的方法：

  1. 根据负载调整DRCP的minsize和maxsize参数
  2. 调整inactivity_timeout参数，释放长时间空闲的连接
  3. 设置合适的max_think_time，避免连接被长时间占用
  4. 启用session_cached_cursors，减少软解析
  5. 监控DRCP的性能指标，如等待时间、连接命中率等

如何处理监听器连接失败的问题？ ​

处理监听器连接失败的步骤：

  1. 检查监听器状态：lsnrctl status
  2. 检查监听器日志，查找错误信息
  3. 检查网络连通性：ping hostname和telnet hostname 1521
  4. 检查tnsnames.ora和listener.ora配置文件
  5. 重启监听器：lsnrctl stop和lsnrctl start
  6. 检查数据库服务状态：SELECT status FROM v$instance;

最佳实践 ​

  1. 使用连接池：无论是应用层连接池还是DRCP，都能有效提高连接管理效率
  2. 合理设置连接限制：根据系统资源和预期负载设置合适的连接数限制
  3. 定期监控连接状态：及时发现和处理异常连接
  4. 优化会话参数：根据业务需求调整会话的PGA、SGA等参数
  5. 设置资源配置文件：对不同用户设置不同的资源限制
  6. 启用连接加密：保护敏感数据在传输过程中的安全
  7. 定期清理无用连接：避免连接泄漏和资源耗尽
  8. 使用绑定变量：减少硬解析，提高连接复用率
  9. 监控连接性能：定期分析连接性能指标，及时优化
  10. 建立连接管理规范：制定连接管理的流程和规范，确保数据库的稳定性和安全性

总结 ​

连接管理是Oracle数据库运维的重要组成部分，涉及到会话管理、连接池配置、连接限制、性能优化等多个方面。通过有效的连接管理，可以提高数据库的性能和稳定性，减少资源消耗，确保数据安全。

在实际生产环境中，需要根据业务需求和系统资源情况，选择合适的连接管理策略，并定期监控和优化。Oracle 21c增强了连接管理的功能和性能，提供了更好的监控和管理能力，DBA可以考虑升级到新版本以获得更好的支持。

通过遵循最佳实践，DBA可以确保数据库连接的高效管理，为业务提供可靠的数据库服务。