Database

with data_file_size as (select trunc(sum(bytes)/1024/1024) as size_mb from dba_data_files),
     temp_file_size as (select trunc(sum(bytes)/1024/1024) as size_mb from dba_temp_files),
     redo_file_size as (select trunc(sum(l.bytes)/1024/1024) as size_mb from v$logfile lf join v$log l on lf.group# = l.group#)
select 
  db.dbid,
  db.name,
  i.version,
  db.log_mode,
  db.flashback_on,
  data_file_size.size_mb as datafile_mb,
  temp_file_size.size_mb as tempfile_mb,
  redo_file_size.size_mb as redo_mb
from v$database db,
v$instance i,
data_file_size,
temp_file_size,
redo_file_size;

Init Parameters

set linesize 150
set pagesize 1000
col name for a50
col value for a90 
select 
  name,
  value 
from v$parameter
where ISDEFAULT = 'FALSE'
order by name;

Hidden Parameters

set linesize 200
set pagesize 0
col "Parameter" for a50
col "Session Value" for a40
col "Instance Value" for a40
 
SELECT a.ksppinm "Parameter",
       b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value",
       decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
       decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' escape '/'
ORDER BY 1;

OPTIONS

Options Usage

SET LINESIZE 180;
SET PAGESIZE 1000;
SET FEEDBACK OFF;
SET COLSEP '|';
WHENEVER SQLERROR EXIT SQL.SQLCODE;

COL "Host Name" FORMAT A30;
COL "Option/Management Pack" FORMAT A60;
COL "Used" FORMAT A5;
with features as(
select a OPTIONS, b NAME  from
(
select 'Active Data Guard' a,  'Active Data Guard - Real-Time Query on Physical Standby' b from dual
union all 
select 'Advanced Compression', 'HeapCompression' from dual
union all
select 'Advanced Compression', 'Backup BZIP2 Compression' from dual
union all 
select 'Advanced Compression', 'Backup DEFAULT Compression' from dual
union all 
select 'Advanced Compression', 'Backup HIGH Compression' from dual
union all
select 'Advanced Compression', 'Backup LOW Compression' from dual
union all
select 'Advanced Compression', 'Backup MEDIUM Compression' from dual
union all
select 'Advanced Compression', 'Backup ZLIB, Compression' from dual
union all
select 'Advanced Compression', 'SecureFile Compression (user)' from dual
union all
select 'Advanced Compression', 'SecureFile Deduplication (user)' from dual
union all
select 'Advanced Compression',        'Data Guard' from dual
union all
select 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual
union all
select 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual
union all
select 'Advanced Security',	'ASO native encryption and checksumming' from dual
union all
select 'Advanced Security', 'Transparent Data Encryption' from dual
union all
select 'Advanced Security', 'Encrypted Tablespaces' from dual
union all
select 'Advanced Security', 'Backup Encryption' from dual
union all
select 'Advanced Security', 'SecureFile Encryption (user)' from dual
union all
select 'Change Management Pack',	'Change Management Pack (GC)' from dual
union all
select 'Data Masking Pack',	'Data Masking Pack (GC)' from dual
union all
select 'Data Mining',	'Data Mining' from dual
union all
select 'Diagnostic Pack',  	'Diagnostic Pack' from dual
union all
select 'Diagnostic Pack',  	'ADDM' from dual
union all
select 'Diagnostic Pack',  	'AWR Baseline' from dual
union all
select 'Diagnostic Pack',  	'AWR Baseline Template' from dual
union all
select 'Diagnostic Pack',  	'AWR Report' from dual
union all
select 'Diagnostic Pack',  	'Baseline Adaptive Thresholds' from dual
union all
select 'Diagnostic Pack',  	'Baseline Static Computations' from dual
union all
select 'Tuning  Pack',  	'Tuning Pack' from dual
union all
select 'Tuning  Pack',  	'Real-Time SQL Monitoring' from dual
union all
select 'Tuning  Pack',  	'SQL Tuning Advisor' from dual
union all
select 'Tuning  Pack',  	'SQL Access Advisor' from dual
union all
select 'Tuning  Pack',  	'SQL Profile' from dual
union all
select 'Tuning  Pack',  	'Automatic SQL Tuning Advisor' from dual
union all
select 'Database Vault',  	'Oracle Database Vault' from dual
union all
select 'WebLogic Server Management Pack Enterprise Edition',  	'EM AS Provisioning and Patch Automation (GC)' from dual
union all
select 'Configuration Management Pack for Oracle Database',  	'EM Config Management Pack (GC)' from dual
union all
select 'Provisioning and Patch Automation Pack for Database',  	'EM Database Provisioning and Patch Automation (GC)' from dual
union all
select 'Provisioning and Patch Automation Pack',  	'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual
union all
select 'Exadata',  	'Exadata' from dual
union all
select 'Label Security',  	'Label Security' from dual
union all
select 'OLAP',  	'OLAP - Analytic Workspaces' from dual
union all
select 'Partitioning',  	'Partitioning (user)' from dual
union all
select 'Real Application Clusters',  	'Real Application Clusters (RAC)' from dual
union all
select 'Real Application Testing',  	'Database Replay: Workload Capture' from dual
union all
select 'Real Application Testing',  	'Database Replay: Workload Replay' from dual
union all
select 'Real Application Testing',  	'SQL Performance Analyzer' from dual
union all
select 'Spatial'	,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual
union all
select 'Total Recall',	'Flashback Data Archive' from dual
)
)
select t.o "Option/Management Pack", 
       t.u "Used",
       d.DBID "DBID",
       d.name "DB Name",
       i.version "DB Version",
       i.host_name "Host Name",
       to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "ReportGen Time"
from
(select OPTIONS o, DECODE(sum(num),0,'NO','YES') u
from
(
select f.OPTIONS OPTIONS, case
                   when f_stat.name is null then 0
                   when ( ( f_stat.currently_used = 'TRUE' and
                            f_stat.detected_usages > 0 and
                            (sysdate - f_stat.last_usage_date) < 366 and
                            f_stat.total_samples > 0
                          )
                          or 
                          (f_stat.detected_usages > 0 and 
                          (sysdate - f_stat.last_usage_date) < 366 and
                          f_stat.total_samples > 0)
                        ) and 
                        ( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)')
                          or
                          (f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and
                           f_stat.feature_info is not null and trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0')
                        )
                        then 1
                   else 0
                  end num
  from features f,
       sys.dba_feature_usage_statistics f_stat
where f.name = f_stat.name(+)
) group by options) t,
  v$instance i,
  v$database d
order by 2 desc,1 
;

MEMORY

PROCESSES

Active Sessions

set linesize 280
set pagesize 1000
col sid for 999999
col serial# for 999999
col machine for a30
col program for a20
col username for a20
col event for a30
col wait_time_micro for 999999999999
col sql_text for a100
select
  substr(machine,1,30) as machine,
  substr(program,1,20) as program,
  sid,
  serial#,
  last_call_et,
  event,
  wait_time_micro,
  s.sql_id,
  sa.sql_text
  --substr(sa.sql_text,1,100) as sql_text
from v$session s
left outer join v$sqlarea sa on s.sql_id = sa.sql_id
where type = 'USER'
and status = 'ACTIVE'
order by machine, program, sid;

STORAGE

Tablespace Usage

set linesize 140
set pagesize 1000
col tablespace_name for a30
col size_mb for 999999
col maxsize_mb for 999999
col used_mb for 999999
col file# for 999999
col free_on_size_mb for 999999
col free_on_maxsize_mb for 999999
col pct_used_on_size for 999.99
col pct_used_on_maxsize for 999.99
with 
  tbsmax as (select tablespace_name, sum(decode(maxbytes,0,bytes,maxbytes)) as maxbytes from dba_data_files group by tablespace_name),
  tbssize as (select tablespace_name, sum(bytes) as bytes, count(*) as file# from dba_data_files group by tablespace_name),
  tbsused as (select tablespace_name, sum(bytes) as bytes from dba_segments group by tablespace_name)
select
  tbssize.tablespace_name,
  trunc(tbssize.bytes/1024/1024) as size_mb,
  trunc(tbsmax.maxbytes/1024/1024) as maxsize_mb,
  trunc(nvl(tbsused.bytes,0)/1024/1024) as used_mb,
  tbssize.file#,
  trunc((tbssize.bytes - nvl(tbsused.bytes,0))/1024/1024) as free_on_size_mb,
  trunc((tbsmax.maxbytes - nvl(tbsused.bytes,0))/1024/1024) as free_on_maxsize_mb,
  trunc(nvl(tbsused.bytes,0)/tbssize.bytes*100,2) as pct_used_on_size,
  trunc(nvl(tbsused.bytes,0)/tbsmax.maxbytes*100,2) as pct_used_on_maxsize
from tbssize
join tbsmax on tbssize.tablespace_name = tbsmax.tablespace_name
left outer join tbsused on tbssize.tablespace_name = tbsused.tablespace_name
order by tablespace_name;

HWM Of Datafiles

set linesize 100
col tablespace_name for a30
col file_id for 9999
col used_mb for 999999
col hwm_mb for 999999
col size_mb for 999999
col maxsize_mb for 999999
col free_below_hwm_mb 999999
with hwm as (select tablespace_name, file_id, max(block_id+blocks) as hwm_block, sum(blocks) as used_blocks from dba_extents group by tablespace_name, file_id)
select 
  hwm.tablespace_name,
  hwm.file_id, 
  trunc(hwm.used_blocks*8/1024) as used_mb,
  trunc(hwm.hwm_block*8/1024) as hwm_mb,
  trunc(f.bytes/1024/1024) as size_mb,
  trunc(f.maxbytes/1024/1024) as maxsize_mb,
  trunc((f.blocks - hwm.hwm_block)*8/1024) as free_below_hwm_mb
from hwm 
join dba_data_files f on f.file_id = hwm.file_id
order by 1,2;

Top Big Tables

set linesize 140
set pagesize 100
col owner for a30
col table_name for a30
col data_size_mb for 999999
col index_size_mb for 999999
col lob_size_mb for 999999
col total_size_mb for 999999
select * from (
with seg_table as (
  select s.owner, s.segment_name as table_name, sum(bytes) as bytes
  from dba_segments s
  where segment_type in ('CLUSTER','NESTED TABLE','TABLE','TABLE PARTITION','TABLE SUBPARTITION')
  and s.segment_name not like 'BIN$%'
  group by s.owner, s.segment_name
),
seg_index as (
  select i.table_owner as owner, i.table_name, sum(s.bytes) as bytes
  from dba_segments s
  join dba_indexes i on s.owner = i.owner and s.segment_name = i.index_name
  where s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
  and s.segment_name not like 'BIN$%'
  group by i.table_owner, i.table_name
),
seg_lob as (
  select l.owner, l.table_name, sum(s.bytes) as bytes
  from dba_segments s
  join dba_lobs l on s.owner = l.owner and s.segment_name = decode(s.segment_type,'LOBSEGMENT',l.segment_name,'LOB PARTITION',l.segment_name,'LOBINDEX',l.index_name)
  where s.segment_type in ('LOB PARTITION','LOBINDEX','LOBSEGMENT')
  and s.segment_name not like 'BIN$%'
  group by l.owner, l.table_name
)
select
  t.owner,
  t.table_name,
  trunc(t.bytes/1024/1024) as data_size_mb,
  trunc(nvl(i.bytes,0)/1024/1024) as index_size_mb,
  trunc(nvl(l.bytes,0)/1024/1024) as lob_size_mb,
  trunc((t.bytes + nvl(i.bytes,0) + nvl(l.bytes,0))/1024/1024) as total_size_mb
from seg_table t
left outer join seg_index i on t.owner = i.owner and t.table_name = i.table_name
left outer join seg_lob l on t.owner = l.owner and t.table_name = l.table_name
order by (t.bytes + nvl(i.bytes,0) + nvl(l.bytes,0)) desc, t.owner, t.table_name
) 
where rownum <= 50
;

ASM Usage by File Type

select
  b.NAME,
  a.TYPE,
  trunc(sum(SPACE)/1024/1024) as SPACE_MB
from V$ASM_FILE a 
join V$ASM_DISKGROUP b on a.GROUP_NUMBER = b.GROUP_NUMBER
group by b.NAME, a.TYPE
order by 1,2;

Usage by Schema

col owner for a20
col segment_type for a30
col size_mb for 9999999
select
  owner, 
  segment_type,
  trunc(sum(bytes)/1024/1024) as size_mb
from dba_segments
group by owner, segment_type
order by 1,2;

PERFORMANCE

Performance Counters

select name, value
from v$sysstat
where name in (
'execute count','parse count (hard)','parse count (total)',
'sorts (disk)','sorts (memory)','sorts (rows)',
'logons cumulative','logons current',
'user commits','user rollbacks',
'DB time',
'physical read total IO requests','physical write total IO requests','physical read total bytes','physical write total bytes',
'redo size','redo writes','redo wastage',
'bytes received via SQL*Net from client','bytes received via SQL*Net from dblink','bytes sent via SQL*Net to client','bytes sent via SQL*Net to dblink'
)
order by name;

select 
  wait_class#,
  wait_class,
  time_waited
from v$system_wait_class
order by 1;

SQL Stats

select 
  snap_id,
  sql_id, 
  plan_hash_value,
  parsing_schema_name,
  executions_delta, 
  trunc(elapsed_time_delta/1000) as elapsed_time_delta_ms,
  buffer_gets_delta,
  disk_reads_delta,
  trunc(executions_delta/3600) as exec_by_sec,
  trunc(elapsed_time_delta/1000/decode(executions_delta,0,1,executions_delta)) as elaps_ms_by_exec,
  trunc(buffer_gets_delta/decode(executions_delta,0,1,executions_delta)) as buffgets_by_exec,
  trunc(disk_reads_delta/decode(executions_delta,0,1,executions_delta)) as diskrd_by_exec
from dba_hist_sqlstat 
where snap_id between 24435 and 24459
and dbid = 454199401 
and instance_number = 1 
order by snap_id, sql_id;

Plan d'execution

define SQL_ID = "1d3b8kcywn9k3"
select * from table (dbms_xplan.display_awr('&SQL_ID'));
select * from table (dbms_xplan.display_cursor('&SQL_ID'));

Long Ops

set linesize 200
set pagesize 100
col message for a100
select 
  SID, 
  to_char(START_TIME,'YYYY-MM-DD HH24:MI:SS') as START_TIME, 
  ELAPSED_SECONDS, 
  TIME_REMAINING, 
  MESSAGE
from v$session_longops 
where START_TIME > (sysdate -1/24) 
order by START_TIME;

AWR Snapshot List

define BEGIN = "2015-12-31 15:00:00"
define END = "2015-12-31 17:00:00"
select 
  snap_id,
  instance_number,
  begin_interval_time,
  end_interval_time
from dba_hist_snapshot
where dbid = (select dbid from v$database)
and begin_interval_time >= to_timestamp('&BEGIN','YYYY-MM-DD HH24:MI:SS')
and end_interval_time <= (to_timestamp('&END','YYYY-MM-DD HH24:MI:SS') + 5/24/60)
order by snap_id, instance_number;

AWR List Sessions Waiting For Event

define BEGIN = "2015-12-31 15:00:00"
define END = "2015-12-31 16:00:00"
define EVENT = "enq: TX - row lock contention"
col inst for 99
col machine for a30
col program for a30
with snap_bounds as (
  select min(snap_id) as snap_min, max(snap_id) as snap_max 
  from dba_hist_snapshot
  where dbid = (select dbid from v$database)
  and begin_interval_time >= to_timestamp('&BEGIN','YYYY-MM-DD HH24:MI:SS')
  and end_interval_time <= (to_timestamp('&END','YYYY-MM-DD HH24:MI:SS') + 5/24/60)
)
select 
  instance_number as inst,
  machine,
  program,
  user_id,
  session_id,
  session_serial#,
  sql_id,
  sample_time,
  time_waited as time_waited_micro,
  blocking_session,
  blocking_session_serial#,
  blocking_inst_id
from DBA_HIST_ACTIVE_SESS_HISTORY
where snap_id between (select snap_min from snap_bounds) and (select snap_max from snap_bounds)
and dbid = (select dbid from v$database)
and event = '&EVENT'
and time_waited > 0
order by instance_number, machine, program, module, user_id, session_id, session_serial#, sql_id, sample_time
;

AWR SQL Text

define SQL_ID = "39pur5fhczqwa"
col sql_text for a100
select 
  sql_id,
  sql_text 
from DBA_HIST_SQLTEXT
where dbid = (select dbid from v$database)
and sql_id = '&SQL_ID';