• 具体原因请查看: 再见,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.

    ColumnDatatypeDescription
    STAT_NAME VARCHAR2(64) Name of the statistic (see Table 7-1)
    VALUE NUMBER Instantaneous statistic value
    OSSTAT_ID NUMBER Statistic ID

    Table 7-1 V$OSSTAT Statistics

    Statistic NameDescription

    NUM_CPUS

    Number of CPUs or processors available

    IDLE_TIME

    Number of hundredths of a second that a processor has been idle, totalled over all processors

    BUSY_TIME

    Number of hundredths of a second that a processor has been busy executing user or kernel code, totalled over all processors

    USER_TIME

    Number of hundredths of a second that a processor has been busy executing user code, totalled over all processors

    SYS_TIME

    Number of hundredths of a second that a processor has been busy executing kernel code, totalled over all processors

    IOWAIT_TIME

    Number of hundredths of a second that a processor has been waiting for I/O to complete, totalled over all processors

    NICE_TIME

    Number of hundredths of a second that a processor has been busy executing low-priority user code, totalled over all processors

    AVG_IDLE_TIME

    Number of hundredths of a second that a processor has been idle, averaged over all processors

    AVG_BUSY_TIME

    Number of hundredths of a second that a processor has been busy executing user or kernel code, averaged over all processors

    AVG_USER_TIME

    Number of hundredths of a second that a processor has been busy executing user code, averaged over all processors

    AVG_SYS_TIME

    Number of hundredths of a second that a processor has been busy executing kernel code, averaged over all processors

    AVG_IOWAIT_TIME

    Number of hundredths of a second that a processor has been waiting for I/O to complete, averaged over all processors

    AVG_NICE_TIME

    Number of hundredths of a second that a processor has been busy executing low-priority user code, averaged over all processors

    OS_CPU_WAIT_TIME

    Total number of hundredths of a second that processes have been in a ready state, waiting to be selected by the operating system scheduler to run

    RSRC_MGR_CPU_WAIT_TIME

    Total number of hundredths of a second that Oracle processes have been in a ready state, waiting for CPU to be available for their consumer group in the currently active resource plan

    VM_PAGE_IN_BYTES

    Total number of bytes of data that have been paged in due to virtual memory paging

    VM_PAGE_OUT_BYTES

    Total number of bytes of data that have been paged out due to virtual memory paging

    PHYSICAL_MEMORY_BYTES

    Total number of bytes of physical memory

    LOAD

    Current number of processes that are either running or in the ready state, waiting to be selected by the operating-system scheduler to run. On many platforms, this statistic reflects the average load over the past minute.

    NUM_CPU_CORES

    Number of CPU cores available (includes subcores of multicore CPUs as well as single-core CPUs)

    NUM_CPU_SOCKETS

    Number of CPU sockets available (represents an absolute count of CPU chips on the system, regardless of multithreading or multi-core architectures)


    Note:

    The availability of all statistics except for NUM_CPUS and RSRC_MGR_CPU_WAIT_TIME is subject to the operating system platform on which the Oracle Database is running.

     

  • 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