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

  • YouTube Social  Icon

The queries used in the program

Kernel monitoring (DBMS 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>

No system sessions

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

Including system session

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>

The number of active sessions in Oracle database

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

<Count session>

The number of sessions in Oracle database

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

<Count blocking session>

The query returns the number of sessions waiting for release of resource to continue

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>

The number of processes in Oracle

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

<Count open cursor>

The number of cursors opened by the session in Oracle database

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>

The number of corruption data blocks in the DB file

select count(*) counts from V$DATABASE_BLOCK_CORRUPTION

<Count invalid object>

The number of objects (procedures, functions, packages, ...) in the status "Invalid"

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

<Parse count>

The value of the parameter parse count total of the system statistics (number of dissections of executed queries database)

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

The value of the parameter parse count hard from system statistics (the number of complete dissections of executed queries database)

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

<Parse time>

The value of the 2 parameters of the parse time cpu and parse elapsed time of system statistics (time spent on the analysis of the queries made of the database)

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

<Switching logs>

In the "Archivelog" (the average switching time of the last log 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)

 

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

The size of the archive log (archivelog) generated for the current day

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>

The amount of data written and read in the DB files (in megabytes)

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>

Information about the last backup operation using 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>

The amount of free space in disk groups 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>

The amount of free space in the area of 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>

Based on the data from 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

Excluding the data from dba_free_space (easy request)

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>

The number of error ORA-01555 for the current day

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

The starting values are in DB parameter

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

Relevant

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>

Cycle for 15 seconds the query is executed (if the option is not worth it check 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

Cycle for 15 seconds the query is executed (if the option is a check 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>

The query plan by clicking on a row in the "list query"

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

Recommendations (if you switch to the tab 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