dbawiki

select round(sum(pga_used_mem)/(1024*1024),2) pga_used_mb from v$process;

select round(p0.value*(2048576+p1.value+p2.value)/(1024*1024),0) you_need_pga_mb
from   v$parameter p0
,      v$parameter p1
,      v$parameter p2
where  p0.name = 'sessions'
and    p1.name = 'sort_area_size'
and    p2.name = 'hash_area_size';

If building a new database,..

"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)

or if updating an existing database…

select (&&processes*1.5)+22      sessions_11g_upwards
,      ((&processes*1.5)+22)*1.1 transactions_11g_upwards
from   dual;
undef processes

 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;

For example after killing a session with alter system disconnect session immediate, it takes a long time to die, use this script to see how long it will take to disappear by checking how many undo blocks still remain occupied.

select vs.sid
,      vs.username
,      vt.xidusn
,      vt.used_urec
,      vt.used_ublk
from   v$session     vs
,      v$transaction vt
where  vs.saddr = vt.ses_addr
/

or a little more detailed…

select s.username username,
   s.program program,
   decode(s.command,
     0,'No Command',
     1,'Create Table',
     2,'Insert',
     3,'Select',
     6,'Update',
     7,'Delete',
     9,'Create Index',
     15,'Alter Table',
     21,'Create View',
     23,'Validate Index',
     35,'Alter Database',
     39,'Create Tablespace',
     41,'Drop Tablespace',
     40,'Alter Tablespace',
     53,'Drop User',
     62,'Analyze Table',
     63,'Analyze Index',
     s.command||': Other') command
,      t.used_ublk
from
   v$session     s,
   v$process     p,
   v$transaction t,
   v$rollstat    r,
   v$rollname    n
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
and s.username='SYS'
order by 1
/

produces

USERNAME        PROGRAM                                            COMMAND                 USED_UBLK
--------------- -------------------------------------------------- ---------------------- ----------
SYS             [email protected] (TNS V1-V3)                  Update                       3751
SYS             [email protected] (TNS V1-V3)                  Select

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;

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

set lines 1000 pages 100
col username for a15
col status   for a10
select ses.username
,      ses.status
,      'alter system disconnect session ''' || ses.sid || ',' || ses.serial# || ''' immediate;' killer
from   v$session ses
where  1=1
and    lower (ses.username) like lower('%&username%')
order  by ses.status
/

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;