首页 > 代码库 > 使用SwingBench测试连接数以及修改连接数和pga

使用SwingBench测试连接数以及修改连接数和pga

2024-07-06 09:21:05   229人阅读

 使用SwingBench工具对oracle环境进行压力测试，100个用户，就提示连接数不够：

 

后台日志报错：

?
1
2
3
4
5
Errors in file /oracle/admin/dbcc/bdump/dbcc_smon_3317.trc:
ORA-00018: maximum number of sessions exceeded
Thu May 29 09:14:50 2014
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)

查看跟踪文件：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
[oracle@sdk31 DBCC]$ more /oracle/admin/dbcc/bdump/dbcc_smon_3317.trc
/oracle/admin/dbcc/bdump/dbcc_smon_3317.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name:    Linux
Node name:      sdk31
Release:        2.6.32-279.el6.i686
Version:        #1 SMP Fri Jun 22 10:59:55 UTC 2012
Machine:        i686
Instance name: dbcc
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 3317, image: oracle@sdk31 (SMON)
 
*** SERVICE NAME:() 2014-05-28 18:42:11.605
*** SESSION ID:(164.1) 2014-05-28 18:42:11.605
Dead transaction 0x0002.02b.0000328e recovered by SMON
Dead transaction 0x0003.00b.000032be recovered by SMON
Dead transaction 0x0006.00f.000032d6 recovered by SMON
Dead transaction 0x0007.009.0000325a recovered by SMON
Dead transaction 0x000e.010.0000312c recovered by SMON
Dead transaction 0x000f.025.000030cf recovered by SMON
Dead transaction 0x0012.013.00002b0c recovered by SMON
Dead transaction 0x0018.00c.0000249e recovered by SMON
Dead transaction 0x0019.028.0000249a recovered by SMON
Dead transaction 0x001b.024.00002493 recovered by SMON
Dead transaction 0x0021.01c.000024d3 recovered by SMON
Dead transaction 0x002f.016.00000fb5 recovered by SMON
Dead transaction 0x0032.002.00000fb1 recovered by SMON
Dead transaction 0x0037.028.00000ff4 recovered by SMON
Dead transaction 0x0038.005.00000fb2 recovered by SMON
Dead transaction 0x0039.010.00000fb2 recovered by SMON
Dead transaction 0x003a.02f.00000fb2 recovered by SMON
Dead transaction 0x003c.02a.00000eac recovered by SMON
*** 2014-05-29 09:13:51.286
SMON: following errors trapped and ignored:
ORA-00018: maximum number of sessions exceeded

查看默认的连接数和进程数的大小：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> show parameter process;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150
SQL> show parameter session;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     170
shared_server_sessions               integer

修改大小 sessions=(processes+10)*1.1 大概的数量关系

实际上修改了processes的大小后 sessions会自动调整

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
SQL> alter system set processes=500 scope=both;
alter system set processes=500 scope=both
                 *
第 1 行出现错误:
ORA-02095: 无法修改指定的初始化参数
 
 
SQL> alter system set processes=500 scope=spfile;
 
系统已更改。
 
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
 
SQL> startup
ORACLE 例程已经启动。
 
Total System Global Area  285212672 bytes
Fixed Size                  1267044 bytes
Variable Size              88083100 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
数据库装载完毕。
数据库已经打开。
 
SQL> show parameter process;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     500
 
SQL> show parameter session;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     555
shared_server_sessions               integer

调整系统sga和pga的大小

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
调整系统pga  sga的大小：
 
SQL> show parameter sga;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 272M
sga_target                           big integer 272M
SQL> show parameter pga;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 90M
 
 
SQL> alter system set sga_max_size=2G scope=SPFILE;
 
系统已更改。
 
SQL> alter system set pga_aggregate_target=800M scope=SPFILE;
 
系统已更改。
 
SQL> alter system set sga_target=2G scope=SPFILE;
 
系统已更改。
 
SQL> show parameter pga;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 90M
 
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
 
SQL> startup
ORACLE 例程已经启动。
 
Total System Global Area 2147483648 bytes
Fixed Size                  1268536 bytes
Variable Size             486540488 bytes
Database Buffers         1644167168 bytes
Redo Buffers               15507456 bytes
数据库装载完毕。
数据库已经打开。
 
SQL> show parameter pga;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 800M
 
SQL> show parameter sga;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 2G
sga_target                           big integer 2G

再次使用100个用户连接

?
1
2
3
4
5
6
7
SQL> select username,count(*) from v$session group by username;
 
USERNAME                         COUNT(*)
------------------------------ ----------
                                       16
SOE                                   100
SYS                                     1

　　

（这里只是通过测试环境，演示如何调整session和pga的大小来满足系统连接数，具体的参数需要根据实际业务场景和命中率、系统内存和CPU进行实际调整）


联系
我们