Contacts     ->      ICQ:    699 512 265,         E-Mail: CerebroSQL@gmail.com

  • YouTube Social  Icon

Список SQL запросов используемых в программе для формирования интерфейса, сбора информации о работе баз данных

 

<1> ... <2> ... <3

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Ядро мониторинга (СУБД Oracle)

[ASH (Active Session History)]

select sel."even",
       sel."wait",
       sel."ma",
       sel.cnt,
       sel.sample_id,
       sel.last_day,
       (select to_char(to_date(beg_secs,'SSSSS'),'hh')from dual) as hh,
       (select to_char(to_date(beg_secs,'SSSSS'),'mm')from dual) as mi,
       (select to_char(to_date(beg_secs,'SSSSS'),'ss')from dual) as ss,
       sel.first_day,
       sel.session_type      
  from (select event# as "even",
               nvl(wait_class,'CPU') as "wait",
               max(to_char(sample_time,'SSSSS')) "ma",
               sum(decode(type,'ash',1,'hist',10)) cnt,
               max(sample_id) sample_id,
               max(to_char(sample_time,'J')) last_day,
               trunc(to_char(sample_time,'SSSSS')/bucket)*bucket beg_secs,
               (trunc(to_char(sample_time,'SSSSS')/bucket)+1)*bucket end_secs,
               min(to_char(sample_time,'J')) first_day,
               session_type
         from (select 15 bucket,
                      sample_time,
                      sample_id,
                      translate( decode(session_state,'ON CPU',  decode(session_type,'BACKGROUND','BCPU','CPU') ,event),' $/',' ____') event#,
                      wait_class,
                      'ash' type,
                      session_type
                 from v$active_session_history
                where sample_time >= sysdate-15/86400
              union all
               select 15 bucket,
                      sample_time,
                      sample_id,
                      translate( decode(session_state,'ON CPU', decode(session_type,'BACKGROUND','BCPU','CPU'),event) ,' $/','____') event#,
                      wait_class,
                      'hist' type,
                      session_type
                from  dba_hist_active_sess_history
               where sample_time >= sysdate-15/86400
        and sample_time < (select min(sample_time) from v$active_session_history) )
group by (trunc(to_char(sample_time,'SSSSS')/bucket)+1)*bucket,
          trunc(to_char(sample_time,'SSSSS')/bucket)*bucket,
          event#,
          session_type,
          wait_class
order by last_day, end_secs) sel

[REAL-TIME]

Без системных сессий

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Timestamp", sid, serial#, username, schemaname, osuser, machine, program, sql_id, logon_time, event, wait_class, state, command
from v$session where service_name <> 'SYS$BACKGROUND' and status = 'ACTIVE' and sid <> (SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL)
and event not in ('jobq slave wait','PL/SQL lock timer','SQL*Net message from client','SQL*Net message from dblink','PX Deq Credit: send blkd')
and sql_id is not null

Включая системные сессии

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Timestamp", sid, serial#, username, schemaname, osuser, machine, program, sql_id, logon_time, event, wait_class, state, command
from v$session where  status = 'ACTIVE' and sid <> (SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL)
and event not in ('jobq slave wait','PL/SQL lock timer','SQL*Net message from client','SQL*Net message from dblink','PX Deq Credit: send blkd')
and sql_id is not null

[Active session]

Количество активных сессий в базе данных Oracle

SELECT count(s.SID) "sess" FROM v$session s  where s.status='ACTIVE'

[Count session]

Количество сессий в базе данных Oracle

SELECT count(SID) "countsess"  FROM v$session

[Count blocking session]

Запрос возвращает количество сессий ожидающий освобождение ресурса для продолжения работы

SELECT count(blocking_sid) "CountBlock"
           FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked
           FROM ( SELECT l.id1, l.id2,
                         MAX(DECODE(l.block, 1, i.instance_name||'-'||l.sid,
                         2, i.instance_name||'-'||l.sid, 0 )) blocking_sid,
                         SUM(DECODE(l.request, 0, 0, 1 )) num_blocked
                   FROM gv$lock l, gv$instance i
                   WHERE ( l.block!= 0 OR l.request > 0 ) AND
                           l.inst_id = i.inst_id
                           GROUP BY l.id1, l.id2)
           GROUP BY blocking_sid
           ORDER BY num_blocked DESC)
           WHERE num_blocked != 0

[Count process]

Количество процессов в СУБД Oracle

select count (*) "countproc" from v$process

[Count open cursor]

Количество курсоров открытых сессиями в СУБД Oracle

select sum(a.value) 
  from v$sesstat a,
       v$statname b
 where a.statistic# = b.statistic#
   and b.name = 'opened cursors current'

[Count corruption block]

Количество испорченных блоков с данными в файла БД

select count(*) counts from V$DATABASE_BLOCK_CORRUPTION

[Count invalid object]

Количество объектов (процедуры, функции, пакеты, ...) в статусе "Invalid

select count (NVL (d.OWNER,0)) AS INV from dba_objects d where status='INVALID'

[Parse count]

Значение параметра parse count total из системной статистики (количество разборов запросов выполненное БД)

select 'parse count total',value
from v$sysstat
where name  like ('parse count (to%')

Значение параметра parse count hard из системной статистики (количество полных разборов запросов выполненное БД)

select   'parse count hard',value
from v$sysstat
where name like  ( 'parse count (ha%')

[Parse time]

Значение 2 параметров parse time cpu и parse time elapsed из системной статистики (время потраченное на разборов запросов выполненное БД)

select name, value from v$sysstat where name like 'parse time%'

[Switching logs]

В режиме "Archivelog" (среднее время переключения последних 51 журнала)

select nvl(round(avg(diff)),0) "diff"
  from
  (select to_char(d,'mmddhh24miss') d, (d - lag(d, 1, null) over (order by d))*(24*60*60) DIFF
     from (select FIRST_TIME d
             from v$archived_log
           order by recid  desc)
    WHERE rownum <52 order by D)

 

В режиме "Noarchivelog"
select nvl(round(avg(diff)),0) "diff"
  from
   (select to_char(d,'mmddhh24miss') d, (d - lag(d, 1, null) over (order by d))*(24*60*60) DIFF
      from (select FIRST_TIME d
              from v$log
             where FIRST_TIME is not null)
    order by D)

[Generat REDO size]

Размер архивных журналов (archivelog) сформированных за текущие сутки

SELECT ROUND(SUM(blocks * block_size)/1024/1024/1024,2) size_gb
   FROM v$archived_log
WHERE TRUNC(first_time) >= TRUNC(SYSDATE)
GROUP BY TRUNC(first_time)
ORDER BY TRUNC(first_time)

[Read/Write data datafile]

Объем данных записанных и прочитанных в файлы БД (в мегабайтах)

select round(sum((ts.PHYRDS *fd.block_size))/1024/1024) "read",
       round(sum((ts.PHYWRTS *fd.block_size))/1024/1024) "write" from
(
 (select PHYRDS, PHYWRTS, File# from V$FILESTAT)
union all
 (select PHYRDS, PHYWRTS, File# from V$TEMPSTAT) )ts,
 (select File#, block_size from V$DATAFILE) fd
 where fd.file#=ts.file#

[RMAN backup info]

Информация о последней операции резервного копирования с использованием RMAN

select * from (
select max(session_stamp),
       status,
       round((sysdate - start_time)*24*60) "DTRMIN",
       to_char(start_time,'dd.mm.yyyy hh24:mi') "DTR", nvl(input_type,'no') "Type"
  from v$rman_backup_job_details
 where (status='COMPLETED' or status ='RUNNING' or status ='COMPLETED WITH WARNINGS')
group by status,input_type,session_stamp,start_time,status
order by session_stamp desc )
where rownum=1

[Free space ASM storage]

Объем свободного места в дисковых группах ASM (Automatic Storage Management)

select round(free_mb/1024) "SizeFreeGB",
       round(free_mb/total_mb*100,2)"SizeFreePerc",
       name "ASMName",
       round(free_mb/total_mb*100) "SizeFreePercRound"
  from v$asm_diskgroup order by 4

[Free space FRA]

Размер свободного места в области FRA (Flash Recovery Area)

select name,
       round(space_limit/1024/1024/1024) "lim",
       round(space_used/1024/1024/1024) "used",
       round(((space_limit-space_used)/space_limit)*100) "free"
  from v$recovery_file_dest where name is not null

[Size database]

С учетом данных из dba_free_space

select s.tablespace_name, nvl(round(s.max/1024),0) "max", s.counts, nvl(round(s.megs_alloc/1024),0) "megs_alloc", nvl(round(s.megs_used/1024),0) "megs_used", nvl(round(s.megs_free/1024),0) "megs_free",
       nvl(round(1-((s.max-s.megs_used)/s.max),4)*100,0) "used", nvl(100-round((s.max-s.megs_used)/s.max*100),0) "used1"
from
(SELECT a.tablespace_name,
        a.counts,
        ROUND (maxbytes /  1048576) MAX,
        ROUND (a.bytes_alloc / 1048576) megs_alloc,
        ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1048576) megs_used,
        ROUND (NVL (b.bytes_free, 0) / 1048576) megs_free
   FROM (  SELECT f.tablespace_name,
                  count(f.file_id) as counts,
                  SUM (f.bytes) bytes_alloc,
                  SUM ( DECODE (f.autoextensible,'YES', f.maxbytes,'NO', f.bytes))maxbytes
             FROM dba_data_files f
         GROUP BY tablespace_name) a,
       (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free
            FROM dba_free_space f
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
  SELECT h.tablespace_name,
         count(f.file_id) as counts,
         ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) /  1048576) MAX,
         ROUND (SUM (h.bytes_free + h.bytes_used) /  1048576) megs_alloc,
         ROUND (SUM (NVL (p.bytes_used, 0)) /  1048576) megs_used,
         ROUND (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))/ 1048576) megs_free
    FROM sys.v_$TEMP_SPACE_HEADER h,
         sys.v_$Temp_extent_pool p,
         dba_temp_files f
   WHERE     p.file_id(+) = h.file_id
         AND p.tablespace_name(+) = h.tablespace_name
         AND f.file_id = h.file_id
         AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name ORDER BY 1  ) s
order by 7   desc

Без учета данных из dba_free_space (легкий запрос)

select a."name",
       a."count",
       nvl(round(a."all"/1024),0) "all",
       nvl(round(a."alloc"/1024),0) "alloc",
       nvl(round(a."used"/1024),0) "used",
       nvl(100-round(((a."all"-a."alloc"+(a."alloc"-a."used"))/a."all")*100,2),0) "%", nvl(100-round(((a."all"-a."alloc"+(a."alloc"-a."used"))/a."all")*100),0) "%1"
 from (
select tablespace_name "name",
       count(file_name) "count",
       round(sum(DECODE(autoextensible,'YES',maxbytes,'NO',bytes))/1048576) "all",
       round(sum(bytes)/1048576) "alloc",
       round(sum(user_bytes)/1048576) "used"
  from DBA_DATA_FILES
 group by tablespace_name
union all
  SELECT h.tablespace_name "name",
         count(f.FILE_NAME ) "count",
         ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) /  1048576) "all",
         ROUND (SUM (h.bytes_free + h.bytes_used) /  1048576) "alloc",
         ROUND (SUM (NVL (p.bytes_used, 0)) /  1048576) "used"
    FROM sys.v_$TEMP_SPACE_HEADER h,
         sys.v_$Temp_extent_pool p,
         dba_temp_files f
   WHERE     p.file_id(+) = h.file_id
         AND p.tablespace_name(+) = h.tablespace_name
         AND f.file_id = h.file_id
         AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name) a
 order by 6 desc

[Reclaimable space]

select round(sum(reclaimable_space)/1024/1024,2) "FreeMB"
  from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
order by reclaimable_space desc

[Count ORA-01555 day]

Количество ошибок ORA-01555 за текущие сутки

SELECT count(u.ssolderrcnt) "count"
  FROM v$undostat u
 WHERE  u.end_time>sysdate-1
   and u.SSOLDERRCNT>0

[Destination status]

select dest_name, status, destination, error
  from V$ARCHIVE_DEST_STATUS where status<>'INACTIVE'

[Status standby (recovering)]

select ad.status, ad.target, ad.schedule, ad.destination, ad.LOG_SEQUENCE, ads.ARCHIVED_SEQ#, ad.fail_sequence, ad.error
  from V$ARCHIVE_DEST ad, V$ARCHIVE_DEST_STATUS ads
 where ad.target='STANDBY'
   and ad.DEST_NAME=ads.DEST_NAME

[Count job running]

select count(*) "count" from DBA_JOBS_RUNNING

[Count scheduler job running]

select count(*) "count" from DBA_SCHEDULER_RUNNING_JOBS

[Count scheduler job failed]

select job_name||' ('|| max(log_date)||')' "name",
       count(*) "count"
  from DBA_SCHEDULER_JOB_LOG
 where log_date>sysdate-1
   and status ='FAILED'
group by  job_name

[Count outstanding database]

select count(*) "Count" from dba_outstanding_alerts

[Total PGA used]

select round(sum(b.value/1024/1024)) usepga
  from v$session a,
       v$sesstat b,
       v$statname c
 where a.sid = b.sid
   and b.statistic# = c.statistic#
   and c.name = 'session pga memory'

[SGA max size]

SELECT round(SUM(value)/1024/1024) "startsga" FROM V$SGA

[SGA (Size pool and cache)]

Стартовые значения прописанные в параметрах БД

select name, ' = '|| nvl(value,0)||' байт' "param"
  from v$parameter where name like '%pool%'

Актуальные значение

select s.name "name", sum(s.summ) "sum" from (select case
  when pool is null then name
   when pool is not null then pool
 end as name, round(sum(bytes)/1024/1024,1) as summ
from v$sgastat group by pool, name) s group by s.name
union all
select name, round(bytes/1024/1024) as sum  from v$sgainfo where name in ('Free SGA Memory Available','Streams Pool Size','Maximum SGA Size')

[Hit ratio: Dictionary cache]

SELECT round((1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100,2) "rel"
  FROM v$rowcache

[Hit ratio: Library cache]

SELECT round((1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100,2) "pin"
  FROM v$librarycache

[Hit ratio: Block Buffer cache]

SELECT round((1 - (phys.value / (db.value + cons.value))) * 100,2) "read"
  FROM   v$sysstat phys, v$sysstat db, v$sysstat cons
 WHERE  phys.name  = 'physical reads'
   AND    db.name    = 'db block gets'
   AND    cons.name  = 'consistent gets'

[Hit ratio: Latch]

SELECT round((1 - (Sum(misses) / Sum(gets))) * 100,2) "Latch"
  FROM v$latch

[Disk sort ratio]

SELECT round((disk.value/mem.value) * 100,2) "DISK"
  FROM v$sysstat disk,
       v$sysstat mem
 WHERE disk.name = 'sorts (disk)'
   AND mem.name  = 'sorts (memory)'

[Rollback Segment waits]

SELECT round((Sum(waits) / Sum(gets)) * 100,2)  FROM   v$rollstat

[Dispatcher workload]

SELECT round(NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0),2) "disp"
  FROM   v$dispatcher

[TOP Wait event]

Session

select RANK,
       WAIT_EVENT,
       lpad(TO_CHAR(PCTTOT, '990D99'), 6) || '% waits with avg.du =' ||
       TO_CHAR(AVERAGE_WAIT_MS, '9999990D99') || ' ms' as EVENT_VALUES
  from (select RANK() OVER(order by sum(time_waited) desc) as RANK,
               event as WAIT_EVENT,
               round(RATIO_TO_REPORT(sum(time_waited)) OVER() * 100, 2) AS PCTTOT,
               round(avg(average_wait) * 10, 2) as AVERAGE_WAIT_MS
          from (select se.SID,
                       se.INST_ID,
                       se.EVENT,
                       se.TIME_WAITED,
                       se.AVERAGE_WAIT
                  from gv$session_event se
                 where se.WAIT_CLASS not in ('Idle')
                union
                select ss.SID,
                       ss.INST_ID,
                       sn.NAME    as EVENT,
                       ss.VALUE   as TIME_WAITED,
                       0          as AVERAGE_WAIT
                  from gv$sesstat ss, v$statname sn
                 where ss."STATISTIC#" = sn."STATISTIC#"
                   and sn.NAME in ('CPU used when call started'))
         where (sid, inst_id) in
               (select sid, inst_id
                  from gv$session
                 where gv$session.SERVICE_NAME not in ('SYS$BACKGROUND'))
         group by event
         order by PCTTOT desc) we
 where RANK <= 50

Database

select RANK,
       WAIT_EVENT,
       lpad(TO_CHAR(PCTTOT, '990D99'), 6) || '%' as EVENT_VALUES
  from (select RANK() OVER(order by sum(time_waited) desc) as RANK,
               event as WAIT_EVENT,
               round(RATIO_TO_REPORT(sum(time_waited)) OVER() * 100, 2) AS PCTTOT
          from (select ss.INST_ID,
                       sn.NAME    as EVENT,
                       ss.VALUE   as TIME_WAITED
                  from gv$sysstat ss, v$statname sn
                 where ss."STATISTIC#" = sn."STATISTIC#")
         group by event
         order by PCTTOT desc) we
 where RANK <= 50

[Oracle database version]

select banner from v$version where banner like '%Oracle%'

Existence

[Size_database_full_mb and Size_database_full_perc]

select s.tablespace_name, s.max, s.counts, s.megs_alloc, s.megs_used, s.max-s.megs_used "fremb",
round(((s.max-s.megs_used)/s.max)*100) as "free"
from
(SELECT a.tablespace_name,
       a.counts,
       ROUND (maxbytes / 1048576) MAX,
       ROUND (a.bytes_alloc / 1024 / 1024) megs_alloc,
       ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024) megs_used,
       ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) megs_free
  FROM (  SELECT f.tablespace_name,
                 count(f.file_id) as counts,
                 SUM (f.bytes) bytes_alloc,
                 SUM ( DECODE (f.autoextensible,'YES', f.maxbytes,'NO', f.bytes))maxbytes
            FROM dba_data_files f
        GROUP BY tablespace_name) a,
      (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free
            FROM dba_free_space f
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
  SELECT h.tablespace_name,
         count(f.file_id) as counts,
         ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) / 1048576) MAX,
         ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
         ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576) megs_used,
         ROUND (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))/ 1048576)megs_free
    FROM sys.v_$TEMP_SPACE_HEADER h,
         sys.v_$Temp_extent_pool p,
         dba_temp_files f
   WHERE     p.file_id(+) = h.file_id
         AND p.tablespace_name(+) = h.tablespace_name
         AND f.file_id = h.file_id
         AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name ORDER BY 1  ) s
order by 7   desc

[Size_database_lite_mb and Size_database_lite_perc]

select a."tablespace_name",a."count",a."all",a."alloc",a."used", a."all"-a."used" "fremb",
round(((a."all"-a."used")/a."all")*100) "free"
 from (select tablespace_name "tablespace_name",
       count(file_name) "count",
       round(sum(DECODE(autoextensible,'YES',maxbytes,'NO',bytes))/1024/1024) "all",
       round(sum(bytes)/1024/1024) "alloc",
       round(sum(user_bytes)/1024/1024) "used"
  from DBA_DATA_FILES group by tablespace_name
union all
select tf.tablespace_name "tablespace_name",
       count(tf.file_name) "count",
       round(sum(DECODE(tf.autoextensible,'YES',tf.maxbytes,'NO',tf.bytes))/1024/1024) "all",
       round(sum(tf.bytes)/1024/1024) "alloc",
       round(sum(tf.user_bytes)/1024/1024) "used"
  from DBA_TEMP_FILES tf, DBA_TEMP_FREE_SPACE fs
  where fs.tablespace_name=tf.tablespace_name
 group by tf.tablespace_name,fs.free_space ) a order by 7 desc

[Size_database_asm_mb]

select 'ASM: '||name "name", total_mb "Total",
 free_mb "freemb" from V$ASM_DISKGROUP

[resize_tablespace]

select Name,
       MAXSIZE_MB,
       SIZE_MB_MIN,
       SIZE_MB_MAX,
       MIN_USEDSIZE_MB,
       MAX_USEDSIZE_MB,
       DAYS,
       (MAXSIZE_MB-MAX_USEDSIZE_MB) "Free",
       Round((SIZE_MB_MAX-SIZE_MB_MIN)/nvl(DAYS,1)) "DAY_RES",
       Round((MAXSIZE_MB-MAX_USEDSIZE_MB)/Round((SIZE_MB_MAX-SIZE_MB_MIN)/nvl(DAYS,1))) "DAYS_RES_MAX"
  from
(SELECT NAME,
       Min(ROUND((TABLESPACE_SIZE*8*1024)/1024/1024)) SIZE_MB_MIN,
       Max(ROUND((TABLESPACE_SIZE*8*1024)/1024/1024)) SIZE_MB_MAX,
       Max(ROUND((TABLESPACE_MAXSIZE*8*1024)/1024/1024)) MAXSIZE_MB,
       min(ROUND((TABLESPACE_USEDSIZE*8*1024)/1024/1024)) MIN_USEDSIZE_MB,
       Max(ROUND((TABLESPACE_USEDSIZE*8*1024)/1024/1024)) MAX_USEDSIZE_MB,
       trunc(Max(END_INTERVAL_TIME))- trunc(min(BEGIN_INTERVAL_TIME)) DAYS
  FROM DBA_HIST_TBSPC_SPACE_USAGE A
       JOIN V$TABLESPACE B ON (A.TABLESPACE_ID = B.TS#)
       JOIN DBA_HIST_SNAPSHOT C ON (A.SNAP_ID = C.SNAP_ID)
group by name) s
where Round((SIZE_MB_MAX-SIZE_MB_MIN)/nvl(DAYS,1))>0
order by "DAYS_RES_MAX"

[Wait_database_ash]

select sel."even",sel."wait", sel."ma", sel.cnt, sel.sample_id, sel.last_day,
(select to_char(to_date(beg_secs,'SSSSS'),'hh')from dual) as hh,
(select to_char(to_date(beg_secs,'SSSSS'),'mm')from dual) as mi,
(select to_char(to_date(beg_secs,'SSSSS'),'ss')from dual) as ss, sel.first_day, sel.session_type
from (select event# as "even", nvl(wait_class,'CPU') as "wait" ,max(to_char(sample_time,'SSSSS')) as "ma"
 ,sum(decode(type,'ash',1,'hist',10)) cnt ,max(sample_id) sample_id , max(to_char(sample_time,'J')) last_day
, trunc(to_char(sample_time,'SSSSS')/bucket)*bucket     beg_secs ,(trunc(to_char(sample_time,'SSSSS')/bucket)+1)*bucket  end_secs
 , min(to_char(sample_time,'J')) first_day, session_type from ( select 15 bucket, sample_time,
sample_id, translate( decode(session_state,'ON CPU',  decode(session_type,'BACKGROUND','BCPU','CPU') ,event),' $/',' ____')
event#, wait_class, 'ash' type, session_type from v$active_session_history where sample_time >= sysdate-15/86400
  union all
select 15 bucket, sample_time, sample_id, translate( decode(session_state,'ON CPU', decode(session_type,'BACKGROUND','BCPU','CPU')
,event) ,' $/','____') event#, wait_class, 'hist' type, session_type from  dba_hist_active_sess_history
where
sample_time >= sysdate-15/86400 )
group by (trunc(to_char(sample_time,'SSSSS')/bucket)+1)*bucket , trunc(to_char(sample_time,'SSSSS')/bucket)*bucket,
event#, session_type,wait_class order by last_day,end_secs) sel

[Wait_database_se]

Циклом в течении 15 секунд выполняется запрос (если в настройках не стоит чек Settings - Monitoring-Show backgraund)

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Timestamp", sid, serial#, username, schemaname, osuser, machine, program, sql_id, logon_time, event, wait_class, state, command
from v$session where service_name <> 'SYS$BACKGROUND' and status = 'ACTIVE' and sid <> (SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL)
and event not in ('jobq slave wait','PL/SQL lock timer','SQL*Net message from client','SQL*Net message from dblink','PX Deq Credit: send blkd')
and sql_id is not null

Циклом в течении 15 секунд выполняется запрос (если в настройках стоит чек Settings - Monitoring-Show backgraund)

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Timestamp", sid, serial#, username, schemaname, osuser, machine, program, sql_id, logon_time, event, wait_class, state, command
from v$session where  status = 'ACTIVE' and sid <> (SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL)
and event not in ('jobq slave wait','PL/SQL lock timer','SQL*Net message from client','SQL*Net message from dblink','PX Deq Credit: send blkd')
and sql_id is not null

[Rman_backup_date]

select status, DTR, round((sysdate - to_date(DTR,'dd.mm.yyyy hh24:mi:ss'))*24*60) "Minut",
case
         when "Type"='DB FULL' then 'Full'
         when "Type"='ARCHIVELOG' then 'Arch'
         when "Type"='DB INCR' then 'Incr'
         else "Type" end "Type"
from (
select max(session_stamp),
       status,
       to_char(start_time,'dd.mm.yyyy hh24:mi') "DTR", nvl(input_type,'no') "Type"
  from v$rman_backup_job_details
 where (status='COMPLETED' or status ='RUNNING' or status ='COMPLETED WITH WARNINGS')
group by status,input_type,session_stamp,start_time,status
order by session_stamp desc )
where rownum=1

[Standby_status]

select ad.status, ad.target, ad.schedule, ad.destination, ad.LOG_SEQUENCE, ads.ARCHIVED_SEQ#, ad.fail_sequence, ad.error
  from V$ARCHIVE_DEST ad, V$ARCHIVE_DEST_STATUS ads
 where ad.target='STANDBY'
   and ad.DEST_NAME=ads.DEST_NAME

[Block_buffer_cache]

SELECT round((1 - (phys.value / (db.value + cons.value))) * 100,2) "read"
  FROM   v$sysstat phys, v$sysstat db, v$sysstat cons
 WHERE  phys.name  = 'physical reads'
   AND    db.name    = 'db block gets'
   AND    cons.name  = 'consistent gets'

[Dictionary_cache]

SELECT round((1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100,2) "Dict"
  FROM   v$rowcache

[Latch]

SELECT round((1 - (Sum(misses) / Sum(gets))) * 100,2) "Latch" FROM   v$latch

[Labrary_cache]

SELECT round((1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100,2) "pin" FROM   v$librarycache

[session_process]

select 'Active' "Name", count(s.SID) "Value" from v$session s where s.STATUS='ACTIVE'
union all
select 'Count' "Name", count(s.SID) "Value" from v$session s
union all
select 'Process' "Name", count(*) "Value" from V$PROCESS

 General window - menu Action

[Transaction count]

Radio: For the year

SELECT TO_CHAR(FIRST_TIME,'YYYY') INTERVAL,
       round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
  FROM V$LOG_HISTORY where to_char(first_time,'yyyy')='
<The current year>'
GROUP BY TO_CHAR(FIRST_TIME,'YYYY')
ORDER BY 1

Radio: Monthly

SELECT TO_CHAR(FIRST_TIME,'MM') INTERVAL,
       round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
  FROM V$LOG_HISTORY where to_char(first_time,'yyyy')='
<The current year>'
GROUP BY TO_CHAR(FIRST_TIME,'MM')
ORDER BY 1

Radio: For days

SELECT TO_CHAR(FIRST_TIME,'MM-dd') INTERVAL,
       round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
   FROM V$LOG_HISTORY where to_char(first_time,'yyyy')='
<The current year>'
GROUP BY TO_CHAR(FIRST_TIME,'MM-dd')
ORDER BY 1

[Transaction dead]

select ktuxeusn,
       ktuxeslt,
       ktuxesqn,
       ktuxesta,
       ktuxesiz
from x$ktuxe
where ktuxesta <> 'INACTIVE' and ktuxecfl like 'ÞAD%' order by ktuxesiz asc

ktuxeusn – Undo Segment Number
ktuxeslt – Slot number
ktuxesqn – Sequence
ktuxesta – State
ktuxesiz – Undo Blocks Remaining
ktuxecfl – Flag

[Transaction rollback progress]

select ses.username,
       ses.sid,
       substr(ses.program, 1, 19) command,
       tra.used_ublk
  from v$session ses, v$transaction tra
 where ses.saddr = tra.ses_addr

[Transaction SMON rollback progress]

select usn,
       state,
       undoblockstotal "Total",
       undoblocksdone "Done",
       undoblockstotal-undoblocksdone "ToDo",
       decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated"
  from v$fast_start_transactions

[TOP SQL]

Top type: buffer

SELECT * FROM
   (SELECT sql_fulltext sql,
           sql_id,
           buffer_gets, executions, buffer_gets/(nvl2(executions,1,1)) "Gets/Exec",
           hash_value,address
      FROM V$SQLAREA
     WHERE buffer_gets > 10000
    ORDER BY buffer_gets DESC)
WHERE rownum <=
<Num row>

Top type: Physical Reads

SELECT * FROM
   (SELECT sql_fulltext sql,
           sql_id,
           disk_reads, executions, disk_reads/(nvl2(executions,1,1)) "Reads/Exec",
           hash_value,address
      FROM V$SQLAREA
     WHERE disk_reads > 1000
   ORDER BY disk_reads DESC)
WHERE rownum <=
<Num row>

Top type: Executions

SELECT * FROM
   (SELECT sql_fulltext sql,
           sql_id,
           executions, rows_processed, rows_processed/(nvl2(executions,1,1)) "Rows/Exec",
           hash_value,address
      FROM V$SQLAREA
     WHERE executions > 100
    ORDER BY executions DESC)
WHERE rownum <=
 
<Num row>

Top type: Parse Calls

SELECT * FROM
   (SELECT sql_fulltext sql,
           sql_id,
           parse_calls, executions, hash_value,address
      FROM V$SQLAREA
     WHERE parse_calls > 1000
    ORDER BY parse_calls DESC)
WHERE rownum <=
 
<Num row>

Top type: Sharable Memory

SELECT * FROM
   (SELECT sql_fulltext sql,
           sql_id,
           sharable_mem, executions, hash_value,address
      FROM V$SQLAREA
     WHERE sharable_mem > 1048576
    ORDER BY sharable_mem DESC)
WHERE rownum <=
 
<Num row>

Top type: Version Count

SELECT * FROM
   (SELECT sql_fulltext sql,
           sql_id,
           version_count, executions, hash_value,address
      FROM V$SQLAREA
     WHERE version_count > 20
    ORDER BY version_count DESC)
WHERE rownum <=
 
<Num row>

План запроса при клике по строке в списке "List query"

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID')

Рекомендации (при переключении на вкладку Recommendation)

DECLARE
my_task_name VARCHAR2 (30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
  sql_id      => '
SQL_ID'
, task_name   => 'The auto-generated name');
END;

begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK ('The auto-generated name'); end;
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('The auto-generated name') "SQL" FROM dual

[Memory]

Pool size

select s.name "name", sum(s.summ) "sum" from (select case
  when pool is null then name
   when pool is not null then pool
 end as name, round(sum(bytes)/1024/1024,1) as summ
from v$sgastat group by pool, name) s group by s.name
union all
select name, round(bytes/1024/1024) as sum  from v$sgainfo where name in ('Free SGA Memory Available','Streams Pool Size','Maximum SGA Size')

Page: Parameter

select name, display_value from v$parameter where
  Upper(name) like Upper('%inmemo%') or
  Upper(name) like Upper('%sga%') or
  Upper(name) like Upper('%pga%') or
  Upper(name) like Upper('%memor%') or
  Upper(name) like Upper('%pool%')
 order by 1

Page: SGAINFO

select name, round(bytes/1024/1024,2) "MB", resizeable from V$SGAINFO

Page: SGASTAT

select pool, name, bytes from V$SGASTAT

Page: PGASTAT

select name,
       case
         when Upper(substr(unit, 0,4))= Upper('byte') then
              round(value/1024/1024,2)||' MB' else to_char(value)
       end "value", substr(unit, 0,4) "Type"
  from V$PGASTAT

Page: PGA advice

SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb,
                estd_pga_cache_hit_percentage cache_hit_perc
FROM   v$pga_target_advice

Page: Cache advice

SELECT size_for_estimate,
       estd_physical_read_factor,
       estd_physical_reads
FROM   v$db_cache_advice
WHERE  name= 'DEFAULT'
AND    block_size    = (SELECT value
                        FROM   v$parameter
                        WHERE  name = 'db_block_size')
AND    advice_status = 'ON'

Page: Memory advice

SELECT memory_size, memory_size_factor, estd_db_time, estd_db_time_factor
  FROM v$memory_target_advice
ORDER BY memory_size

[Alert viewer]

Radio: Full data

select indx, component_id, host_address,originating_timestamp,message_text
from  v$diag_alert_ext
where component_id not like '%tnslsnr%'
order by originating_timestamp

Radio: Ora only

select indx,
       component_id,
       host_address,
       originating_timestamp,
       message_text
  from  v$diag_alert_ext where component_id not like '%tnslsnr%'
   and message_text like '%ORA-%'
order by originating_timestamp

Radio: Select the last

select * from
(select indx,component_id,host_address,originating_timestamp,message_text
   from  v$diag_alert_ext where component_id not like '%tnslsnr%'
order by originating_timestamp desc) s where rownum < <Row count>
order by originating_timestamp

Radio: Date between

select indx,
       component_id,
       host_address,
       originating_timestamp,
       message_text
  from v$diag_alert_ext
where component_id not like '%tnslsnr%'
and  originating_timestamp  between to_date('Date start','dd.mm.yyyy') and to_date('Date end','dd.mm.yyyy')
order by originating_timestamp

[AWR report]

select * from table(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML((select dbid from v$database),1,<Start ID>,<End ID>)

[Registry]

select dr.COMP_NAME, dr.VERSION, dr.STATUS, dr.SCHEMA, dr.PROCEDURE from DBA_REGISTRY dr

 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Redo generation top by object

[TOP Generat object]

select *
  from (SELECT to_char(min(begin_interval_time), 'DD-Mon-YY HH24:MI') ||' - ' ||
               to_char(max(begin_interval_time), 'DD-Mon-YY HH24:MI') as WHEN,
               dhso.object_name,
               dhso.object_type,
               dhso.tablespace_name,
               sum(db_block_changes_delta) as db_block_changes,
               to_char(round((RATIO_TO_REPORT(sum(db_block_changes_delta)) OVER()) * 100, 2), '99.00')||' %' as REDO_PERCENT
          FROM dba_hist_seg_stat     dhss,
               dba_hist_seg_stat_obj dhso,
               dba_hist_snapshot     dhs
         WHERE dhs.snap_id = dhss.snap_id
           AND dhs.instance_number = dhss.instance_number
           AND dhss.obj# = dhso.obj#(+)
           AND dhss.dataobj# = dhso.dataobj#(+)
           AND begin_interval_time BETWEEN
               to_date('<DateTime start>','dd.mm.yyyy hh24:mi:ss') AND
               to_date('<DateTime end>','dd.mm.yyyy hh24:mi:ss')
         GROUP BY dhso.object_name,dhso.object_type,dhso.tablespace_name
         ORDER BY db_block_changes desc)

 

[Size REDO archived]

select s."DateFirst",
         round(sum(s."Bytes")/1024/1024/1024, 2)||' GB' "GB",
         (select dest_name||'='||destination from V$ARCHIVE_DEST where dest_id=s.dest_id)
  from (select TRUNC(first_time) "DateFirst", blocks*block_size "Bytes", dest_id
            from V$ARCHIVED_LOG) s
group by s."DateFirst", dest_id
order by 1 desc, 3

FileIO

[Detail file IO]

SELECT a.tablespace_name,
       a.file_name,
       a.blocks,
       round((a.phywrts*(<Block size>/1024))/1024) phywrts,
       round((a.phyrds*(<Block size>/1024))/1024) phyrds
FROM
(SELECT t.name tablespace_name,
       d.name file_name,
       d.blocks,
       f.phywrts,
       f.phyrds
  FROM v$filestat f,
       v$datafile d,
       V$TABLESPACE t
 WHERE f.file#=d.file#
   AND d.blocks >0
   AND t.ts#=d.ts#
UNION ALL
SELECT t.name tablespace_name,
       d.name file_name,
       d.blocks,
       f.phywrts,
       f.phyrds
  FROM v$tempstat f,
       v$tempfile d,
       V$TABLESPACE t
 WHERE f.file#=d.file#
   AND d.blocks >0
   AND t.ts#=d.ts# ) a
  order by 4 desc

 

[Session IO]

SELECT
       s.sid||':'||
       s.serial# SS,
       NVL(DECODE(type,'BACKGROUND','SYS ('||b.name||')',
                  s.username),substr(p.program,instr(p.program,'('))) oracle_user,
       s.status status,
       s.machine machine,
       nvl(s.osuser,'('||b.name||')')  os_user, 
       round(((i.block_gets+i.consistent_gets)*8192)/1024/1024,3) logical_reads,
       round((i.physical_reads*8192)/1024/1024,3) physical_reads,
       s.program  client_program,
       t.ksusestv*10 cpu_usage,
       s.logon_time logon_time,
       s.sql_hash_value
  FROM v$session s,
       v$process p,
       v$sess_io i,
       x$ksusesta t,
       v$bgprocess b
 WHERE p.addr=s.paddr
   AND i.sid=s.sid
   AND t.indx=s.sid
   AND t.ksusestn=12
   AND p.addr=b.paddr(+)
order by i.physical_reads desc

RMAN Operation

[List backup]

SELECT end_time,
       input_type||' ('||substr(status,1,1)||')' "Type",
       status,
       session_key,
       session_recid,
       session_stamp,
       command_id,
       start_time,
       time_taken_display,
       input_type,
       output_device_type,
       input_bytes_display,
       INPUT_BYTES_PER_SEC_DISPLAY,
       output_bytes_display,
       output_bytes_per_sec_display
 FROM (SELECT end_time,
              status,
              session_key,
              session_recid,
              session_stamp,
              command_id,
              start_time,
              time_taken_display,
              input_type,
              output_device_type,
              input_bytes_display,
              INPUT_BYTES_PER_SEC_DISPLAY,
              output_bytes_display,
              output_bytes_per_sec_display
         FROM v$rman_backup_job_details  
        WHERE start_time> sysdate - <count day>
ORDER BY start_time DESC)
 where rownum<200

[Backup details]

select a.STATUS,
       to_char(open_time,'dd.mm.yyyy hh24:mi') timebegin,
       round(BYTES/1024/1024,2)|| '' Mb'' mbbegin ,
       a.type,
       filename
  from v$backup_async_io a
 where  not  a.STATUS in ('UNKNOWN')
   and open_time >= to_date('
<Start backup>','dd.mm.yyyy hh24:mi:ss')
   and close_time <= to_date('
<End time backup>','dd.mm.yyyy hh24:mi:ss')+10/86400
order by 2 desc

[Backup log]

select output from V$RMAN_OUTPUT where session_stamp=<Session STAMP>

[RMAN Configuration]

select name, value from V$RMAN_CONFIGURATION order by name

 

Session manager

[Oracle session: Session list]

select sid, serial#,status,schemaname,event, sql_id, machine,program, logon_time,
osuser, wait_class, seconds_in_wait, state
from v$session

[List parallel session Oracle]

SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) )"Username",
       s.sql_id,
       DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,TO_CHAR( px.server_set) "SlaveSet",
       s.program,
       TO_CHAR(s.SID) "SID",
       TO_CHAR(px.inst_id) "Slave INST",
       DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) AS STATE,
       CASE  sw.state
          WHEN 'WAITING'
          THEN SUBSTR(sw.event,1,30)
          ELSE NULL END AS wait_event ,
       DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) "QC SID",
       TO_CHAR(px.qcinst_id) "QC INST",
       DECODE(px.server_set,'',s.last_call_et,'') "Elapsed (s)"
  FROM gv$px_session px,
       gv$session s,
       gv$px_process pp,
       gv$session_wait sw
 WHERE px.SID=s.SID (+)
   AND px.serial#=s.serial#(+)
   AND px.inst_id = s.inst_id(+)
   AND px.SID = pp.SID (+)
   AND px.serial#=pp.serial#(+)
   AND sw.SID = s.SID
   AND sw.inst_id = s.inst_id
ORDER BY username, DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), px.QCSID,
     DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID

[Oracle session: Statistics parallel execute]

SELECT substr(NAME, 21,length(name)) "name",
       VALUE,
       ROUND((RATIO_TO_REPORT(VALUE) OVER ())*100, 2)|| '%' PERC
  FROM V$SYSSTAT
 WHERE NAME LIKE 'Parallel%'
ORDER BY NAME DESC

[Oracle session: Tree of locks in Oracle]

 with
 LOCKS as (select /*+ MATERIALIZE*/   * from gv$lock)
,S     as (select /*+ MATERIALIZE*/ s.* from gv$session s)
,BLOCKERS as
 (select distinct L1.inst_id, L1.sid
    from LOCKS L1, LOCKS L2
   where L1.block > 0
     and L1.ID1 = L2.ID1
     and L1.ID2 = L2.ID2
     and L2.REQUEST > 0)
,WAITERS as (select inst_id, sid from S where blocking_session is not null or blocking_instance is not null)
select
 LPAD(' ', (LEVEL - 1) * 2) || 'INST#' || s.inst_id || ' SID#' || sid as BLOCKING_TREE,
 s.program,
 substr(s.USERNAME || ' ' || s.CLIENT_IDENTIFIER,1,40) as USERNAME,
 EVENT,
 last_call_et,
 seconds_in_wait as SECS_IN_WAIT,
 blocking_session_status as BLOCK_SESSTAT,
 pdml_enabled,
 s.sql_id,
 s.osuser,
 p.spid,
 s.machine as CLNT_HOST,
 s.process as CLNT_PID,
 s.port    as CLNT_PORT,
 substr(trim(NVL(sa1.sql_text,sa2.sql_text)), 1, 100) SQL_TEXT,
 decode(sign(nvl(s.ROW_WAIT_OBJ#, -1)), -1, 'NONE', DBMS_ROWID.ROWID_CREATE(1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#)) req_rowid,
 p1text || ' ' || decode(p1text, 'name|mode', chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535)||' '||bitand(p1, 65535), p1text) as
 p1text,
 p1,
 p1raw,
 p2text || ' ' || decode(p2text, 'object #', o.object_name || ' ' || o.owner || '.' || o.object_name, p2text) as
 p2text,
 p2
 from s
  left join gv$sqlarea sa1 on s.sql_id = sa1.sql_id and s.inst_id =  sa1.inst_id
  left join gv$sqlarea sa2 on s.prev_sql_id = sa2.sql_id and s.inst_id =  sa2.inst_id
  left join dba_objects o  on s.p2 = o.object_id
  left join gv$process p on s.paddr = p.addr and s.inst_id = p.inst_id
connect by NOCYCLE prior sid = blocking_session and prior s.inst_id = blocking_instance
 start with (s.inst_id, s.sid)
            in (select inst_id, sid from BLOCKERS minus select inst_id, sid from WAITERS)

[Oracle session: open cursor]

select oc.sql_text,

           oc.sql_id

  from V$OPEN_CURSOR oc, v$SESSION s

where oc.saddr=s.saddr and s.sid='<session sid>'

[Oracle session: current SQL text]

select sql_fulltext

  from v$sql where sql_id='<sql_id>'

[Oracle session: statistics]

select st.name,

           se.value

  from v$sesstat se, v$statname st

where se.statistic#=st.statistic# and se.value<>0 and sid=<session sid>

order by 2 desc

[Oracle session: lock]

select blocking_session,
       event,
       dba_objects.owner||'.'||dba_objects.object_name req_object,
       sql_text
  from v$session, dba_objects, v$sql
 where v$session.ROW_WAIT_OBJ# = dba_objects.object_id
   and v$session.sql_id = v$sql.sql_id
   and blocking_session is not  null
   and sid=
<session sid>

[Oracle session: longops]

SELECT s.OPNAME,
       s.TARGET,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
and    s.SID =
<Session sid>

[Oracle session: plan current SQL]

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID'))

[Oracle session: rollback segment]

SELECT rs.segment_name,
       round(r.rssize/1024/1024)||' MB' "size", r.status
  FROM v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
 WHERE s.saddr = t.ses_addr
   AND t.xidusn = r.usn
   AND rs.segment_id = t.xidusn
   and s.sid='
<session sid>'
   and s.serial#='
<session serial>'
ORDER BY t.used_ublk DESC

[Oracle session: session wait]

select s.EVENT,
       s.SECONDS_IN_WAIT,
       s.P1,
       s.P1TEXT,
       s.P2,
       s.P2TEXT,
       s.P3
  from V$SESSION_WAIT s  where sid=
<Session sid>
 order by SECONDS_IN_WAIT desc

[Oracle session: kill select session]

alter system kill session '<session sid>,<session serial>' immediate

[Oracle session: enable tracing session]

begin sys.dbms_system.set_ev(<session sid>,<session serial>,10046,12,''); end;

[Oracle session: disable tracing session]

begin sys.dbms_system.set_ev(<session sid>,<session serial>,10046,0,''); end;