具体原因请查看： 再见，BLOGBUS

一、sessions和processes参数的关系

sessions不能直接设定，由下面的公式计算得出

sessions = processes * 1.1 + 5

search@WEBDB>show parameter processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     1000

search@WEBDB>show parameter sessions
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sessions                             integer     1105

二、查询当前session和实例启动后最大session

select '1. current session', count(*) from v$session
union
select '2. max session in license table', SESSIONS_HIGHWATER from v$license
union
select '3. current session in license table', SESSIONS_CURRENT from v$license;

系统真实的总session数查询

select count(*) from x$ksuse where bitand(ksspaflg,1)!=0;

三、查询可能是无法释放的session

select logon_time,
       last_call_et "time inactive",
       nvl(s.username, 'ORACLE PROCESS') username,
       s.machine,
       s.program,
       s.sid session_id,
       s.status,
       sql_text,
       ss.value "CPU used",
       trunc(buffer_gets / (executions + 1)) "BUFF-EXEC",
       trunc(buffer_gets / (rows_processed + 1)) "BUFF-ROWS",
       first_load_time,
       executions,
       parse_calls,
       disk_reads,
       buffer_gets,
       rows_processed
  from v$session s, v$sesstat ss, v$statname sn, v$sqlarea sa
 where s.sid = ss.sid
   and ss.statistic# = sn.statistic#
   and sn.name = 'CPU used by this session'
   and s.sql_address = sa.address
   and s.sql_hash_value = sa.hash_value
   and last_call_et > 1000
 order by machine, status, program, last_call_et asc;

V$SESSION.LAST_CALL_ET的含义

If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.

If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.

参考文章：

Ora -00018 Error  

原文地址： 查询初始化参数的方法（二）

关键内容：

1、一般在查询初始化参数的时候都习惯性的使用SHOW PARAMETER，也就是查询V$PARAMETER视图，但是有些时候查询这个视图得到的结果并不准确。

2、V$PARAMETER视图反映的是初始化参数在当前会话中生效的值，而V$SYSTEM_PARAMETER反映的才是实例级上的初始化参数。

search@WEBDB>select * from v$osstat;
STAT_NAME                                                             VALUE  OSSTAT_ID
---------------------------------------------------------------- ---------- ----------
NUM_CPUS                                                                 16          0
IDLE_TIME                                                        8512032009          1
BUSY_TIME                                                        4154889188          2
USER_TIME                                                        3049052081          3
SYS_TIME                                                         1105837107          4
IOWAIT_TIME                                                       197701635          5
AVG_IDLE_TIME                                                     531753548          7
AVG_BUSY_TIME                                                     259432143          8
AVG_USER_TIME                                                     190317536          9
AVG_SYS_TIME                                                       68866446         10
AVG_IOWAIT_TIME                                                    12108487         11
OS_CPU_WAIT_TIME                                                 4360049800         13
RSRC_MGR_CPU_WAIT_TIME                                                    0         14
LOAD                                                                .015625         15
NUM_CPU_CORES                                                            16         16
PHYSICAL_MEMORY_BYTES                                            3.4360E+10       1008

16 rows selected.

oracle官方网站上对V$OSSTAT的说明

V$OSSTAT

V$OSSTAT displays system utilization statistics from the operating system. One row is returned for each system statistic.

Latch: cache buffer chains等待时间的含义：

“buffer cache中block的header是被放置到hash chains上，而hash chains又是放在hash bucket中，多个hash bucket被一个cache buffers chains latch保护。当多个session并发访问同一个数据块上的数据，每个session都要首先获得cache buffers chains latch，这样将造成cache buffers chains latch的争用。”

一、查询各等待时间的含义：

search@WEBDB>r
  1  select name, parameter1, parameter2, parameter3
  2  from v$event_name
  3* where name like 'latch%'
NAME                                               PARAMETER1 PARAMETER2 PARAMETER3
-------------------------------------------------- ---------- ---------- ----------
latch: cache buffers chains                        address    number     tries
latch: redo writing                                address    number     tries
latch: redo copy                                   address    number     tries
latch: Undo Hint Latch                             address    number     tries
latch: In memory undo latch                        address    number     tries
latch: MQL Tracking Latch                          address    number     tries
latch: row cache objects                           address    number     tries
latch: shared pool                                 address    number     tries
latch: library cache                               address    number     tries
latch: library cache lock                          address    number     tries
latch: library cache pin                           address    number     tries
latch activity                                     address    number     process#
latch free                                         address    number     tries
latch: session allocation                          address    number     tries
latch: messages                                    address    number     tries
latch: enqueue hash chains                         address    number     tries
latch: ges resource hash list                      address    number     tries
latch: gcs resource hash                           address    number     tries
latch: cache buffers lru chain                     address    number     tries
latch: checkpoint queue latch                      address    number     tries
latch: cache buffer handles                        address    number     tries
latch: object queue header operation               address    number     tries
latch: object queue header heap                    address    number     tries
latch: redo allocation                             address    number     tries
latch: KCL gc element parent latch                 address    number     tries
latch: undo global data                            address    number     tries
latch: Change Notification Hash table latch        address    number     tries
latch: virtual circuit queues                      address    number     tries
latch: parallel query alloc buffer                 address    number     tries

二、查询等待相关的sql语句

select sql_text, sql_fulltext, t2.sid, t2.event, t2.wait_class, t2.p1, t2.p2, t2.p3, t2.state
  from v$sqlstats t1 inner join v$session t2 on t1.SQL_ID = t2.sql_id
                inner join v$session_wait t3 on t2.sid = t3.sid
where t3.wait_class <> 'Idle'

三、查询latch相关的统计数据

select latch#, name, gets, misses, sleeps from v$latch where latch#=98 order by sleeps;
select latch#,name,gets,misses,sleeps from v$latch where name like 'cache buffer%';

参考资料：

模拟试验--latch free之cache buffers chains

某数据库经历了严重的cache buffers chains

Wait Event Enhancements in Oracle 10g