EBS DB最大连接数调整

noel0217

最近部门在做JDeveloper开发，经常使EBS DB连接数达到最大，导致EBS出现无法访问的情况。所以尝试着提高了processes和sessions，EBS安装完之后，数据库init参数processes默认为200,sessions默认为400.这里我把DB的processes和sessions提高到了300和600。
1.查询数据库当前进程的连接数：
select count(*) from v$process;
2.查看数据库当前会话的连接数：
select count(*) from v$session;
3.查看数据库的并发连接数：
select count(*) from v$session where status='ACTIVE';
4.查看当前数据库建立的会话情况：
select sid,serial#,username,program,machine,status from v$session;
5.查询数据库允许的最大连接数(processes和sessions)：
SQL> show parameter processes
NAME                                  TYPE         VALUE
processes                            integer     200
....
SQL> show parameter sessions
NAME                                  TYPE         VALUE
sessions                             integer     400
....
6.修改数据库允许的最大连接数：
修改数据库初始化文件中的processes和sessions值，文件位置：
/d01/oracle/<SID>/db/tech_st/11.1.0/dbs/init<SID>.ora
ORACLE的连接数(sessions)与其参数文件中的进程数(processes)有关，它们的关系如下：
processes             = 300    # Max. no. of users * 2
sessions              = 600    # 2 * processes
 

• --当前的连接数  
  
• select count(*) from v$process;  
  
• --设置的最大连接数（默认值为200）  
  
• select value from v$parameter where name = 'processes';  
  
• ------------------------------------------------  
  
• --查看当前的session使用情况  
  
• SELECT  Count(*) FROM v$session;  
  
• 或  
  
• SELECT   username, machine, program, status, COUNT (machine) AS  
  
• FROM v$session  
  
• GROUP BY username, machine, program, status  
  
• ORDER BY machine;  
  
• --查看系统最大session数  
  
• select value from v$parameter where name = 'sessions';  
  

　　 
Oracle EBS DB Init Parameters
Oracle EBS里查看数据库参数
　　路径:System Administration > Oracle Applications Manager > Initialization Parameters
或者
路径:System Administration > Oracle Applications Manager > System Configuration Overview > Database > Init.ora Parameters


可以查看DB的数据库parameter(init.ora或者pfile parameter)

关于Oracle Parameter可以参见我之前写得帖子：Oracle Parameters
如果是使用的init<sid>.ora的话，这个DB参数文件默认放在$RDBMS_ORACLE_HOME/dbs/init<sid>.ora
你可以在这个文件中修改DB参数，注意，修改完之后，需要重启数据库才能生效。
EBS的DB init.ora参数文件的设置
另外这里有两篇不错的metalink notes可以帮助你来设置EBS的init.ora参数文件，给出了一些设置的建议
Note: 216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
Note: 396009.1 - Database Initialization Parameters for Oracle Applications Release 12
Index for Note 396009.1
This document describes the database initialization parameter settings required for Oracle E-Business Suite Release 12.
 

• Section 1: Common Database Initialization Parameters For All Releases
  
• Section 2: Release-Specific Database Initialization Parameters For Oracle 10g Release 2
  
• Section 3: Release-Specific Database Initialization Parameters For Oracle 11g Release 1
  
• Section 4: Release-Specific Database Initialization Parameters For Oracle 11g Release 2
  
• Section 5: Using System Managed Undo (SMU)
  
• Section 6: Temporary Tablespace Setup
  
• Section 7: Database Initialization Parameter Sizing
  

Key Points
    * The document consists of a common section, which provides a common set of database initialization parameters used for all releases of the Oracle Database, followed by several release-specific sections, which list parameters and settings required for a particular release of the Oracle Database.
    * Put together, the parameters from the common section and appropriate release-specific section formulate a complete set of database initialization parameters.
    * Parameters may appear on a removal list either because they are obsolete, or because the default value is required and no other value may be set.
    * In the various parameter lists, check for comments giving any platform-specific exceptions. Such comments will apply only to the exact platform mentioned: for example, a reference to HP-UX (PA-RISC) will not apply to HP-UX (Itanium IA-64).
    * The "X" notation used in the release-specific section denotes all patchset releases within that major version. For example, "10.2.0.X" refers to all releases of 10.2.0, such as 10.2.0.2 and 10.2.0.3.
    * Oracle E-Business Suite Release 12 requires Oracle Database 10g Release 2 (10.2.0.2) Enterprise Edition as a minimum release level and edition. No earlier releases, or other editions of any release, may be used.



 
Database Initialization Parameter Sizing
关于DB Parameter Sizing，Oracle根据用户数提供了一个参考的设置列表

Footnote 1
    The parameter sga_target should be used for Oracle 10g or 11g based environments such as Release 12. This replaces the parameter db_cache_size, which was used in Oracle 9i based environments. Also, it is not necessary to set the parameter undo_retention for 10g or 11g-based systems, since undo retention is set automatically as part of automatic undo tuning.

    Enabling the 11g Automatic Memory Management (AMM) feature is supported in EBS, and has been found to be useful in scenarios where memory is limited, as it will dynamically adjust the SGA and PGA pool sizes. AMM is enabled by using the memory_target and memory_max_target initialization parameters. MEMORY_TARGET specifies the system-wide sharable memory for Oracle to use when dynamically controlling the SGA and PGA as workloads change. The memory_max_target parameter specifies the maximum size that memory_target may take. AMM has proven useful for small to mid-range systems as it simplifies both the configuration and management. However, many customers with large production systems have experienced better performance with manually sized pools (or large minimum values for the pools). On Linux, Hugepages has resulted in improved performance; however, this configuration is not compatible with AMM. For large mission-critical applications systems, it is advisable to set sga_target with a minimum fixed value for shared_pool_size and pga_aggregate_target.

Footnote 2
    The total memory required refers to the amount of memory required for the database instance and associated memory, including the SGA and the PGA. You should ensure that your system has sufficient available memory in order to support the values provided above. The values provided above should be adjusted based on available memory so as to prevent paging and swapping.
 
转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7728738