Oracle数据库性能调优实践(二)——优化连接数及会话数

摘要：上周，办公系统出现打开页面缓慢，甚至登陆不了的问题，经过检查log日志，发现是Oracle会话数超过最大值500，于是修改了数据库的会话数，重启数据库服务后，办公系恢复正常。现将这个过程整理出来，供参考。详细内容请看下文。

1、分析问题

（1）、执行下面语句分析活动会话数变化趋势

SQL> select to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,count(*) cnt from dba_hist_active_sess_history ash where ash.instance_number=1 and ash.wait_class <> 'Idle' /* 非空闲回话 **/ and ash.sample_time between sysdate -1/2 and sysdate group by SAMPLE_TIME having count(*) >80 order by SAMPLE_TIME;

（2）、抽一个时间点分析等待事件及SQL执行情况

SQL> select to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,ash.sql_id,ash.event,count(*) CNT /* SQL的数量 **/,TRUNC(SUM(TIME_WAITED) / 1000000,2) SECONDS_IN_WAIT /* SQL的等待时间 **/,SUM(to_number(CAST(ash.sample_time AS DATE)-ash.sql_exec_start) *24*60*60 ) SECONDS_IN_EXECUTE /* SQL的执行时间 **/ from dba_hist_active_sess_history ash where ash.instance_number=1 and ash.wait_class <> 'Idle' and to_char(ash.sample_time,'YYYY-MM-DD HH24:MI:SS') = '2016-12-01 09:28:27' group by ash.sample_time,ash.sql_id,ash.event having count(*) > 5 order by SAMPLE_TIME,4;

（3）、根据SQL_ID分析SQL语句的执行过程

SQL> select to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,ash.event,ash.sql_id ,ash.blocking_inst_id,ash.blocking_session,ash.blocking_session_serial#,count(*) cnt from dba_hist_active_sess_history ash where ash.instance_number=1 and ash.wait_class <> 'idle' and ash.sql_id = '输入上面语句查出来的SQL_ID号' and to_char(ash.sample_time,'YYYY-MM-DD HH24:MI:SS') = '2022-04-06 11:24:20' group by sample_time,ash.event,ash.sql_id,ash.blocking_inst_id,ash.blocking_session,ash.blocking_session_serial# order by sample_time,7;

（4）、根据BLOCKING_INST_ID分析该会话的历史执行情况

SQL> select to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') sample_time,to_char(ash.sql_exec_start,'YYYY-MM-DD HH24:MI:SS') SQL_START_TIME,ash.instance_number inst_id,ash.session_id sid,ash.session_serial# serial ,ash.blocking_inst_id b_inst_id,ash.blocking_session b_sid,ash.blocking_session_serial# b_serial,ash.sql_id,ash.event,to_number(CAST(ash.sample_time AS DATE)-ash.sql_exec_start) *24*60*60 SECONDS_IN_EXECUTE,ash.xid from dba_hist_active_sess_history ash where ash.instance_number=1 and ash.session_id = **** and ash.session_serial# = ***** order by sample_time;

2、解决问题

（1）、查看最大连接数：

SQL> show parameter processes;

显示的界面如下：

这个查询界面不直观，需要进行调整，设置界面的相关参数如下：SQL> set pages 80; SQL> set lines 120; SQL> col event for a40;

重新执行SQL> show parameter processes;其显示界面如下：

（2）、修改最大连接数：

SQL> alter system set processes=1000 scope=spfile;

（3）、查看最大会话数：

SQL> show parameters sessions;

（4）、修改最大会话数：

SQL> alter system set sessions=1105 scope=spfile;

（5）、重启Oracle服务

SQL> shutdown immediate; --关闭数据库服务

SQL> startup; --启动数据库服务

3、注意事项

（1）、操作系统的参数区别：Linux和Windows系统，对sessions的处理是有区别的。Linux系统1会话对应1个进程处理，而Windows系统1会话对应1个线程处理。

（2）、Oracle的连接数process是没有上限的，理论上设置多少都是允许的。不过，实际上连接数上限的设置与服务器的配置有关，配置越强悍，连接数可以设置的上限越高，连接数设置的值会占用oracle内存的，上限设置过大的话，如果SGA中没有足够的内存分配，数据库便无法启动。下面语句可以查询连接数的设置需要占用的Oracle内存：SQL> select * from v$sgastat where pool='shared pool' and name='processes'。

（3）、连接数和会话数计算关系：会话数sessions是个派生值,由连接数processes的值决定,公式sessions=1.1*process + 5。

说明：修改了连接数和会话数参数，必须重启Oracle服务才会生效。