dbawiki

"SESSIONS parameter is the maximum number of sessions that can be created in the system. Every login requires a session.

It is calculated from PROCESSES parameter:

10g -> ((1.1 * PROCESSES) + 5)
11g -> ((1.5 * PROCESSES) + 22)
12.2-> ((1.5 * PROCESSES) + 22)

You may leave SESSIONS and let Database choose its value automatically."

TRANSACTIONS = (1.1 * SESSIONS)

 set lines 200
 set pages 999
 col killer      format a15
 col osuser      format a15
 col login_time  format a15
 select s.username
 ,      s.osuser
 ,      s.sid || ',' || s.serial# "Killer"
 ,      p.spid                    "OS PID"
 ,      s.status
 ,      to_char(s.logon_time, 'dd-mon-yy hh24:mi') login_time
 ,      s.last_call_et
 from   v$session s
 ,      v$process p
 where  1=1
 and    s.paddr    = p.addr
 and    s.username is not null
 order  by login_time;

select tablespace_name temp_name
,      tablespace_size/1024/1024 temp_size
,      allocated_space/1024/1024 temp_alloc
,      free_space/1024/1024 temp_free
from   dba_temp_free_space
/

select a.tablespace_name                                             tablespace
,      d.mb_total                                                    mb_total
,      sum (a.used_blocks * d.block_size) / 1024 / 1024              mb_used
,      d.mb_total - sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_free
from   v$sort_segment a
,      (
       select b.name
       ,      c.block_size
       ,      sum (c.bytes) / 1024 / 1024 mb_total
       from   v$tablespace b
       ,      v$tempfile   c
       where  b.ts# = c.ts#
       group  by b.name
       ,      c.block_size
       ) d
where  a.tablespace_name = d.name
group  by a.tablespace_name
,      d.mb_total
/

select b.tablespace
,      b.segfile#
,      b.segblk#
,      b.blocks
,      a.sid
,      a.serial#
,      a.username
,      a.osuser, a.status
from  v$session a
,     v$sort_usage b
where  a.saddr = b.session_addr
order  by b.tablespace
,      b.segfile#
,      b.segblk#
,      b.blocks
/

set lines 1000 pages 1000
col sid_serial for a10
col username   for a15
col osuser     for a10
col spid       for a10
col module     for a35
col program    for a25
col tablespace for a10

select s.sid || ',' || s.serial# sid_serial
,      s.username
,      s.osuser
,      p.spid
,      s.module
,      s.program
,      sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used
,      t.tablespace
,      count(*) sort_ops
from   v$sort_usage t
,      v$session s
,      dba_tablespaces tbs
,      v$process p
where  t.session_addr = s.saddr
and    s.paddr = p.addr and t.tablespace = tbs.tablespace_name
group  by s.sid
,      s.serial#
,      s.username
,      s.osuser
,      p.spid
,      s.module
,      s.program
,      tbs.block_size
,      t.tablespace
order  by sid_serial
/

or

SET PAGESIZE 50
SET LINESIZE 300

COLUMN tablespace FORMAT A20
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A50

SELECT b.tablespace,
       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
       a.inst_id as Instance,
       a.sid||','||a.serial# AS sid_serial,
       NVL(a.username, '(oracle)') AS username,
       a.program,
       a.status,
       a.sql_id
FROM   gv$session a,
       gv$sort_usage b,
       gv$parameter p
WHERE  p.name  = 'db_block_size'
AND    a.saddr = b.session_addr
AND    a.inst_id=b.inst_id
AND    a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks
/

select s.sid || ',' || s.serial# sid_serial
,      s.username
,      t.blocks * tbs.block_size / 1024 / 1024 mb_used
,      t.tablespace
,      t.sqladdr address
,      q.hash_value
,      q.sql_text
from   v$sort_usage t
,      v$session s
,      v$sqlarea q
,      dba_tablespaces tbs
where  t.session_addr = s.saddr
and    t.sqladdr      = q.address (+)
and    t.tablespace   = tbs.tablespace_name
order  by s.sid
/

 select sql_text
 from   v$sqlarea
 where  1=1
 and    (address, hash_value) in
        (
        select sql_address
        ,      sql_hash_value
        from   v$session
        where  1=1
        and    username like '&username'
        );

Lists all SQL statements in the library cache with CPU time > &1 &1 % of the total of all statements

define CPUTimePct = '&1'
@sqlplus_settings
@nls_settings
@header_title 155 DC 'All statements in the library cache with a total CPU time >= &CPUTimePct % of all statements together'

COLUMN cpu_time_total       FORMAT      9G999G990 HEADING "CPU time|total"
COLUMN sql_text             FORMAT            A60 HEADING "Sql text"
COLUMN pct                  FORMAT          990D9 HEADING "%"
COLUMN buffer_gets_per_exec FORMAT 99G999G999G999 HEADING "Buffer gets|per exec"
COLUMN wait_per_exec        FORMAT      999G990D9 HEADING "Wait time|per exec"
COLUMN cpu_per_exec         FORMAT      999G990D9 HEADING "CPU time|per exec"
COLUMN disk_reads_per_exec  FORMAT    999G999G999 HEADING "Disk reads|per exec"
COLUMN executions           FORMAT     99G999G999 HEADING "Executions"
COLUMN sql_id               FORMAT            A13 HEADING "Sql id"

SET FEEDBACK  ON
SET ARRAYSIZE 1
SET LONG      2000
SET RECSEP    WRAPPED
SET PAGESIZE  100

WITH total_cpu_time
  AS ( SELECT sum(cpu_time) total_cpu FROM v$sqlarea )
SELECT cpu_time/1000000                                                    cpu_time_total,
       (cpu_time*100)/total_cpu                                            pct,
       sql_text,
       executions,
       cpu_time/(decode(executions,0,1,executions)*1000000)                cpu_per_exec,
       (elapsed_time-cpu_time)/(decode(executions,0,1,executions)*1000000) wait_per_exec,
       buffer_gets/decode(executions,0,1,executions)                       buffer_gets_per_exec,
       disk_reads/decode(executions,0,1,executions)                        disk_reads_per_exec,
       sql_id
  FROM v$sqlarea,
       total_cpu_time
 WHERE cpu_time >= &CPUTimePct*total_cpu/100
 ORDER BY cpu_time desc;

undefine CPUTimePct

SET ARRAYSIZE 20
SET LONG      200

col username  for a12
col sid       for 99999
col sql_text  for a200
set lines 1000 pages 1000 long 100000
select a.username
,      to_char(a.logon_time,'DD-MON-YY HH24:MI:SS') dt
,      a.sid
,      a.serial#
,      b.sql_text
from   v$session a
,      v$sqlarea b
where  1=1
and    a.sql_address = b.address;

Run this if never run before (to create needed tables)

@?/rdbms/admin/catblock

then to show locking processes in a tree structure (non indented process is blocking indented sessions)

@?/rdbms/admin/utllockt

 select s1.username || '@' || s1.machine||
        ' ( SID=' || s1.sid || ' )  is blocking '||
        s2.username || '@' || s2.machine ||
        ' ( SID=' || s2.sid || ' ) '  blocking_status
 from   v$lock l1
 ,      v$session s1
 ,      v$lock l2
 ,      v$session s2
 where  1=1
 and    s1.sid     = l1.sid
 and    s2.sid     = l2.sid
 and    l1.block   = 1
 and    l2.request > 0
 and    l1.id1     = l2.id1
 and    l2.id2     = l2.id2;

select nvl(s.username,'Internal')         username
,      nvl(s.terminal,'None')             terminal
,      l.sid||','||s.serial#              killer
,      u1.name||'.'||substr(t1.name,1,20) tab
,      decode ( l.lmode
              ,1, 'No Lock'
              ,2, 'Row Share'
              ,3, 'Row Exclusive'
              ,4, 'Share'
              ,5, 'Share Row Exclusive'
              ,6, 'Exclusive'
                , null)                   lmode
,      decode ( l.request
              ,1, 'No Lock'
              ,2, 'Row Share'
              ,3, 'Row Exclusive'
              ,4, 'Share'
              ,5, 'Share Row Exclusive'
              ,6, 'Exclusive'
                , null)                   request
from  v$lock    l
,     v$session s
,     sys.user$ u1
,     sys.obj$  t1
where l.sid    = s.sid
and   t1.obj#  = decode(l.id2,0,l.id1,l.id2)
and   u1.user# = t1.owner#
and   s.type  != 'BACKGROUND'
and   S.sid   in ( select blocking_session from v$session where blocking_session is not null )
order by 1,2,5
/

from www.guyharrison.net

set wrap off
column sid format a8
column object_name format a20
column sql_text format a150
set echo on
WITH sessions AS
   (SELECT /*+materialize*/
           sid, blocking_session, row_wait_obj#, sql_id
      FROM v$session)
SELECT LPAD('  ', LEVEL ) || sid sid, object_name,
       substr(sql_text,1,240) sql_text
  FROM sessions s
  LEFT OUTER JOIN dba_objects
       ON (object_id = row_wait_obj#)
  LEFT OUTER JOIN v$sql
       USING (sql_id)
 WHERE sid IN (SELECT blocking_session FROM sessions)
    OR blocking_session IS NOT NULL
 CONNECT BY PRIOR sid = blocking_session
 START WITH blocking_session IS NULL
/

… and prepare the killer statement

set wrap off lines 2000 echo on
column sid for     a8
column username    for a12
column terminal    for a12
column object_name for a20
column sql_text    for a150
column killer      for a60
with sessions as
    (
    select /*+materialize*/ sid         sid
    ,      nvl(username,'Internal')     username
    ,      serial#                      serial#
    ,      nvl(terminal,'None')         terminal
    ,      blocking_session             blocking_session
    ,      row_wait_obj#                row_wait_obj#
    ,      sql_id                       sql_id
    ,      'alter system disconnect session **|| sid || ',' || serial# || ** immediate;' killer
    from   v$session
    )
select lpad('   ', level)||sid          sid
,      object_name                      object_name
,      substr(sql_text,1,240)           sql_text
,      decode(level,1,s.killer,null)    killer
from   sessions s
left   outer join dba_objects on    (object_id = row_wait_obj#)
left   outer join v$sql       using (sql_id)
where  sid in (select blocking_session from sessions)
or     blocking_session       is not null
connect by prior sid          = blocking_session
start  with blocking_session  is null
/

set lines 1000
col  object for a40
select ses.username
,      ddl.session_id
,      ses.serial#
,      owner || '.' || ddl.name object
,      ddl.type
,      ddl.mode_held
from   dba_ddl_locks ddl
,      v$session     ses
where  owner          like '%&userid%'
and    ddl.session_id = ses.sid;

select nvl(b.username,'SYS') username
,      session_id
,      lock_type
,      mode_held
,      mode_requested
,      lock_id1
,      lock_id2
from   sys.dba_lock_internal a
,      sys.v_$session        b
where  1=1
and    a.session_id = b.sid
and    b.username   like '%&username%'
and    b.sid        = &session_id
/

set linesize 150;
set head on;
col sid_serial form a13
col ora_user for a15;
col object_name for a35;
col object_type for a10;
col lock_mode for a15;
col last_ddl for a8;
col status for a10;
break on sid_serial;
SELECT l.session_id||','||v.serial# sid_serial,
       l.ORACLE_USERNAME ora_user,
       o.object_name,
       o.object_type,
       DECODE(l.locked_mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive',
          TO_CHAR(l.locked_mode)
       ) lock_mode,
       o.status,
       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
      and l.SESSION_ID=v.sid
order by 2,3;

 select 'alter system disconnect session ** || ddl.session_id || ',' || ses.serial# || ** immediate;'
 from   dba_ddl_locks ddl
 ,      v$session ses
 where  1=1
 and    ddl.session_id = ses.sid;
 and    owner          like upper('%&userid%')

set lines 100
set pages 100
col username  format a15
col message   format a40
col remaining format 9999
select username
,      to_char(start_time, 'dd-mon-yy hh24:mi') started
,      time_remaining remaining
,      message
from   v$session_longops
where  1=1
and    time_remaining != 0
order  by time_remaining desc

set pages 100 lines 1000
col p1 for 999999999999
col p2 for 999999999999
col p3 for 999999999999
select sid
,      event
,      p1
,      p2
,      p3
,      seconds_in_wait
from   v$session_wait
where  sid in
       (
       select sid
       from   v$session vs
       ,      v$process vp
       ,      dba_datapump_sessions dp
       where  vp.addr  = vs.paddr(+)
       and    vs.saddr = dp.saddr
       )

set pages 1000 lines 1000
col username for a15
select vss.sid
,      vs.username
,      vsn.name
,      vss.value
,      round((sysdate-vs.logon_time)*24) hours_connected
from   v$sesstat  vss
,      v$statname vsn
,      v$session  vs
where  1=1
and    vss.sid        = vs.sid
and    vss.statistic# = vsn.statistic#
and    vss.value      > 0
and    vsn.name       like '%parse count%'

set serveroutput on
set feedback off
column username format a20
column sql_text format a55 word_wrapped
begin
    dbms_output.enable(null);

    for x in
    (
    select username||'('||sid||','||serial#||') ospid = '|| process ||' program = ' || program username
    ,      to_char(LOGON_TIME,' Day HH24:MI') logon_time
    ,      to_char(sysdate,' Day HH24:MI')    current_time
    ,      sql_address
    ,      sql_hash_value
    from   v$session
    where  1=1
    and    status                 = 'ACTIVE'
    and    rawtohex(sql_address) != '00'
    and    username is not null
    ) loop
        for y in
        (
        select sql_text
        from   v$sqlarea
        where  1=1
        and    address = x.sql_address
        ) loop
            if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;
    end loop;
end;
/

These SQL statements cause expensive hard parse events in the shared pool

select count('x')                       num_sql
,      sum(decode(executions, 1, 1, 0)) one_use_sql
,      sum(sharable_mem)/1024/1024      meg_used
,      sum(decode ( executions
                  , 1, sharable_mem
                     , 0
                  )
          )/1024/1024                   mb_per
from   v$sqlarea
where  sharable_mem > 0;

set pages 999
select sess.username
,      sess.sid
,      sess.serial#
,      stat.value cursors
from   v$sesstat stat
,      v$statname sn
,      v$session sess
where  1=1
and    sess.username   is not null
and    sess.sid        = stat.sid
and    stat.statistic# = sn.statistic#
and    sn.name         = 'opened cursors current'
order  by value;

Use SID from query above.

select sid
,      sql_text
,      count('e') open_crsrs
,      user_name
from   v$open_cursor
where  1=1
and    sid = &SID
group  by sid
,      sql_text
,      user_name
order  by 3;

select max(a.value) highest_open_crsrs
,      p.value      max_open_crsrs
from   v$sesstat   a
,      v$statname  b
,      v$parameter p
where  1=1
and    a.statistic# = b.statistic#
and    b.name       = 'opened cursors current'
and    p.name       = 'open_cursors'
group  by p.value;