首页 > 代码库 > 使用SwingBench测试连接数以及修改连接数和pga
使用SwingBench测试连接数以及修改连接数和pga
使用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进行实际调整)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。