oracle 数据库连接数问题

AWR中Resource Limit Stats的信息是从这个表中查询到的：
DBA_HIST_RESOURCE_LIMIT
我们可以直接从这个表中查询出历史数据连接数有没有满

 

1、查看数据库允许的最大连接数

select value from v$parameter where name = 'processes'; 

 

2、查看当前已使用的连接数

select count(*) from v$process;

修改process参数设置。

参考64位操作系统Oracle11g参数设置标准：
4G物理内存设置为：500
8G以上设置为：1000
现在单板是24G的，设置成3000。
SQL>alter system set processes = 3000 scope = spfile;
SQL>shutdown immediate;
SQL>startup;

 

--查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b

 

 

看实时快照资源(session/process)和当前值和最大值

SQL> select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE  from v$resource_limit where resource_name='processes' or resource_name='sessions';

RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------------------- ------------------- --------------- ---------------
processes                             99             116        300
sessions                             105             123        335

看历史快照资源(session/processes)和当前值和最大值

SQL> select b.snap_id,b.begin_interval_time,b.end_interval_time,a.RESOURCE_NAME,a.CURRENT_UTILIZATION,a.MAX_UTILIZATION,a.LIMIT_VALUE 
    from DBA_HIST_RESOURCE_LIMIT a,dba_hist_snapshot b 
    where a.snap_id=b.snap_id and a.dbid=b.dbid and a.resource_name in('processes','sessions')
    and b.begin_interval_time>to_date('2014/07/27 08:30:00','yyyy/mm/dd hh24:mi:ss') and b.begin_interval_time<to_date('2014/07/27 10:30:00','yyyy/mm/dd hh24:mi:ss')
        ;

   SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME              RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
---------- ------------------------------ ------------------------------ -------------------- ------------------- --------------- ---------------
      2437 27-JUL-14 09.00.00.876 AM      27-JUL-14 10.00.21.961 AM      processes                            105             107        300
      2437 27-JUL-14 09.00.00.876 AM      27-JUL-14 10.00.21.961 AM      sessions                             112             115        335
      2438 27-JUL-14 10.00.21.961 AM      27-JUL-14 11.00.37.928 AM      processes                            104             107        300
      2438 27-JUL-14 10.00.21.961 AM      27-JUL-14 11.00.37.928 AM      sessions                             111             115        335

 

 

提炼巡检标准如下：

查询7天内的process和session最大使用情况，最多使用不超过80%

SQL> select max(a.resource_name),max(a.MAX_UTILIZATION),max(a.limit_value),max(a.MAX_UTILIZATION)/max(a.limit_value)*100 as usage 
    from DBA_HIST_RESOURCE_LIMIT a,dba_hist_snapshot b  
   where a.snap_id=b.snap_id and a.dbid=b.dbid and a.resource_name in('processes') and b.begin_interval_time>sysdate-7 ; 

MAX(A.RESOURCE_NAME)           MAX(A.MAX_UTILIZATION) MAX(A.LIMI      USAGE
------------------------------ ---------------------- ---------- ----------
processes                                         120        600         20

SQL> select max(a.resource_name),max(a.MAX_UTILIZATION),max(a.limit_value),max(a.MAX_UTILIZATION)/max(a.limit_value)*100 as usage 
    from DBA_HIST_RESOURCE_LIMIT a,dba_hist_snapshot b  
    where a.snap_id=b.snap_id and a.dbid=b.dbid and a.resource_name in('sessions') and b.begin_interval_time>sysdate-7 ;

MAX(A.RESOURCE_NAME)           MAX(A.MAX_UTILIZATION) MAX(A.LIMI      USAGE
------------------------------ ---------------------- ---------- ----------
sessions                                          132        665 19.8496241

 

查询7天内的session和process情况

SQL> select b.snap_id,b.begin_interval_time,b.end_interval_time,a.RESOURCE_NAME,a.CURRENT_UTILIZATION,a.MAX_UTILIZATION,a.LIMIT_VALUE 
    from DBA_HIST_RESOURCE_LIMIT a,dba_hist_snapshot b 
    where a.snap_id=b.snap_id and a.dbid=b.dbid and a.resource_name in('processes','sessions')
    and b.begin_interval_time>sysdate-7;

   SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME              RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
---------- ------------------------------ ------------------------------ -------------------- ------------------- --------------- ---------------
      2466 28-JUL-14 02.00.19.896 PM      28-JUL-14 03.00.40.552 PM      processes                            100             104        300
      2466 28-JUL-14 02.00.19.896 PM      28-JUL-14 03.00.40.552 PM      sessions                             107             111        335
      2461 28-JUL-14 09.23.09.000 AM      28-JUL-14 10.00.37.664 AM      processes                             80              89        300
      2461 28-JUL-14 09.23.09.000 AM      28-JUL-14 10.00.37.664 AM      sessions                              87             100        335
      2445 27-JUL-14 05.00.29.931 PM      27-JUL-14 06.00.52.871 PM      processes                            106             107        300
      2445 27-JUL-14 05.00.29.931 PM      27-JUL-14 06.00.52.871 PM      sessions                             113             115        335
      2456 28-JUL-14 04.00.44.299 AM      28-JUL-14 05.00.11.016 AM      processes                            106             111        300
      2456 28-JUL-14 04.00.44.299 AM      28-JUL-14 05.00.11.016 AM      sessions                             113             119        335
      2447 27-JUL-14 07.00.09.508 PM      27-JUL-14 08.00.41.034 PM      processes                            106             108        300
      2447 27-JUL-14 07.00.09.508 PM      27-JUL-14 08.00.41.034 PM      sessions                             113             115        335
      2449 27-JUL-14 09.00.01.081 PM      27-JUL-14 10.00.23.600 PM      processes                            106             108        300

 

 

 

针对rac节点，， 需要增加条件INSTANCE_NUMBER=1/2/3/4

 

SQL>select a.INSTANCE_NUMBER,b.snap_id,b.begin_interval_time,b.end_interval_time,a.RESOURCE_NAME,a.CURRENT_UTILIZATION,a.MAX_UTILIZATION,a.LIMIT_VALUE 
from DBA_HIST_RESOURCE_LIMIT a,dba_hist_snapshot b 
where a.snap_id=b.snap_id and a.dbid=b.dbid and a.resource_name in('processes','sessions')
and b.begin_interval_time>to_date('2016/02/01 10:00:00','yyyy/mm/dd hh24:mi:ss') and b.begin_interval_time<to_date('2016/02/01 21:00:00','yyyy/mm/dd hh24:mi:ss')
and RESOURCE_NAME='sessions' and a.INSTANCE_NUMBER=1 order by begin_interval_time;

 

SQL>select a.INSTANCE_NUMBER,b.snap_id,b.begin_interval_time,b.end_interval_time,a.RESOURCE_NAME,a.CURRENT_UTILIZATION,a.MAX_UTILIZATION,a.LIMIT_VALUE 
from DBA_HIST_RESOURCE_LIMIT a,dba_hist_snapshot b 
where a.snap_id=b.snap_id and a.dbid=b.dbid and a.resource_name in('processes','sessions')
and b.begin_interval_time>to_date('2016/02/01 10:00:00','yyyy/mm/dd hh24:mi:ss') and b.begin_interval_time<to_date('2016/02/01 21:00:00','yyyy/mm/dd hh24:mi:ss')
and RESOURCE_NAME='processes' and a.INSTANCE_NUMBER=1 order by begin_interval_time;