###### Postgresql

# voir http://www.postgresql.org/docs/current/static/monitoring-stats.html
# et http://www.varlena.com/GeneralBits/107.php
# et http://pgsnap.projects.postgresql.org/pagila_snap_20080624/bases.html (pgsnap par dalibo.com)

postgresql.pg_stat_activity = select * from pg_stat_activity
postgresql.pg_locks = select pg_stat_activity.datname, pg_class.relname, pg_locks.transactionid, pg_locks.locktype, pg_locks.mode, pg_locks.granted, \
pg_stat_activity.usename, pg_stat_activity.query, pg_stat_activity.query_start, age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.pid \
from pg_stat_activity, pg_locks \
left outer join pg_class on (pg_locks.relation = pg_class.oid) \
where pg_locks.pid = pg_stat_activity.pid \
order by query_start

postgresql.pg_tablespace = \
SELECT spcname, pg_get_userbyid(spcowner) AS owner, \
pg_size_pretty(pg_tablespace_size(spcname)) AS size, spcacl \
FROM pg_tablespace ORDER BY spcname
postgresql.pg_database = \
SELECT datname, pg_get_userbyid(datdba) AS dba, pg_catalog.pg_encoding_to_char(encoding) AS encoding, \
datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, spcname as tablespace, \
pg_size_pretty(pg_database_size(datname)) AS size, datacl \
FROM pg_database, pg_tablespace WHERE dattablespace = pg_tablespace.oid ORDER BY datname
# en commentaire, car pb_buffercache nécessite l'installation d'une contrib disponible avec postgresql,
# mais size dans pg_database et cachehitratio dans pg_stat_database sont plus intéressants
#postgresql.databaseCache = \
#SELECT pg_get_userbyid(datdba) AS dba, datname, pg_database_size(reldatabase) AS size, count(*) AS buffers \
#FROM pg_buffercache, pg_database, pg_tablespace WHERE reldatabase=pg_database.oid GROUP BY 1, 2, 3 ORDER BY 1, 2, 3
postgresql.pg_stat_database = select *, \
round((blks_hit::float/(blks_read+blks_hit+1)*100)::numeric, 2) as cachehitratio from pg_stat_database
postgresql.pg_stat_user_tables = select * from pg_stat_user_tables
postgresql.pg_stat_user_indexes = select * from pg_stat_user_indexes
postgresql.pg_statio_user_tables = select * from pg_statio_user_tables
postgresql.pg_statio_user_indexes = select * from pg_statio_user_indexes
postgresql.pg_statio_user_sequences = select * from pg_statio_user_sequences
postgresql.pg_settings = select * from pg_settings

###### MariaDB (comme mysql)

mariadb.processlist = show full processlist
mariadb.databases = show databases
mariadb.variables = show variables
mariadb.global_status = show global status
mariadb.innodb_status = show engine innodb status
# (en général le log slow queries se fait en fichier et non dans cette table)
# mariadb.slow_log = SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 100

mariadb.unusedIndexes = SELECT object_name, index_name \
  FROM performance_schema.table_io_waits_summary_by_index_usage \
 WHERE index_name IS NOT NULL \
   AND index_name != 'PRIMARY' \
   AND count_star = 0 \
   AND object_schema = database() \
 ORDER BY object_name

# if needed: UPDATE performance_schema.setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long';
mariadb.longRunning = SELECT left(digest_text, 64) \
     , COUNT(*) as cnt \
     , ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms \
     , ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms \
     , ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms \
     , ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms \
     , ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms \
     , ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms \
     , MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS first_seen \
     , MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS last_seen \
  FROM performance_schema.events_statements_history_long \
  JOIN information_schema.global_status AS isgs \
 WHERE isgs.variable_name = 'UPTIME' and current_schema = database() \
 GROUP BY LEFT(digest_text,64) \
 ORDER BY tot_exec_ms DESC \
 LIMIT 5000

# following reports inspired by mysql perf analyzer (Apache license)
# https://github.com/yahoo/mysql_perf_analyzer/blob/master/myperf/src/main/resources/sql.xml
mariadb.tableStats = select table_name, table_type, engine, \
    case when table_rows > 1000000 then concat(round(table_rows/1000000,6), 'M') else table_rows end table_rows, \
    concat(round(data_length/1048576),'MB') data, concat(round(index_length/1048576), 'MB') idx, \
    concat(round((data_length+index_length)/1048576),'MB') total, \
    concat(round(data_free/1048576),'MB') free, avg_row_length \
from information_schema.tables \
where table_schema = database()

mariadb.eventsWaits = select e.event_name, e.count_star, e.sum_timer_wait/1000000000 WAIT_MS, e.sum_lock_time/1000000000 LOCK_MS \
from performance_schema.events_statements_summary_by_thread_by_event_name e \
where e.count_star > 0 \
order by e.event_name

mariadb.tableIoWaits = select object_name NAME, \
     count_star, avg_timer_wait/1000000000 AVG_WAIT_MS, \
     count_read, avg_timer_read/1000000000 AVG_READ_MS, \
     count_write, avg_timer_write/1000000000 AVG_WRITE_MS, \
     count_fetch, avg_timer_fetch/1000000000 AVG_FETCH_MS, \
     count_insert, avg_timer_insert/1000000000 AVG_INSERT_MS, \
     count_update, avg_timer_update/1000000000 AVG_UPDATE_MS, \
     count_delete, avg_timer_delete/1000000000 AVG_DELETE_MS \
from performance_schema.table_io_waits_summary_by_table \
where count_star > 0 and object_schema = database() \
order by 1 limit 5000

mariadb.indexIoWaits = select CONCAT(object_name,'.', index_name) NAME, \
     count_star, avg_timer_wait/1000000000 AVG_WAIT_MS, \
     count_read, avg_timer_read/1000000000 AVG_READ_MS, \
     count_write, avg_timer_write/1000000000 AVG_WRITE_MS, \
     count_fetch, avg_timer_fetch/1000000000 AVG_FETCH_MS, \
     count_insert, avg_timer_insert/1000000000 AVG_INSERT_MS, \
     count_update, avg_timer_update/1000000000 AVG_UPDATE_MS, \
     count_delete, avg_timer_delete/1000000000 AVG_DELETE_MS \
from performance_schema.table_io_waits_summary_by_index_usage \
where index_name is not null and count_star > 0 and object_schema = database() \
order by 1 limit 5000

mariadb.tableLockWaits = select object_name NAME, \
     count_star, avg_timer_wait/1000000000 AVG_WAIT_MS, \
     count_read, avg_timer_read/1000000000 AVG_READ_MS, \
     count_write, avg_timer_write/1000000000 AVG_WRITE_MS, \
     count_read_normal, avg_timer_read_normal/1000000000 AVG_R_NORMAL_MS, \
     count_read_with_shared_locks, avg_timer_read_with_shared_locks/1000000000 AVG_R_SHARED_LOCKS_MS, \
     count_read_high_priority, avg_timer_read_high_priority/1000000000 AVG_R_HIGH_PRIORITY_MS, \
     count_read_no_insert, avg_timer_read_no_insert/1000000000 AVG_R_NO_INSERT_MS, \
     count_read_external, avg_timer_read_external/1000000000 AVG_R_EXTERNAL_MS, \
     count_write_allow_write, avg_timer_write_allow_write/1000000000 AVG_W_ALLOW_W_MS, \
     count_write_concurrent_insert, avg_timer_write_concurrent_insert/1000000000 AVG_W_CONC_INSERT_MS, \
     count_write_low_priority, avg_timer_write_low_priority/1000000000 AVG_W_LOW_PRIORITY_MS, \
     count_write_normal, avg_timer_write_normal/1000000000 AVG_W_NORMAL_MS, \
     count_write_external, avg_timer_write_external/1000000000 AVG_W_EXTERNAL_MS \
from performance_schema.table_lock_waits_summary_by_table \
where count_star > 0 and object_schema = database() \
order by 1 limit 5000

mariadb.tablesWithoutPk = select table_name \
      from information_schema.columns \
      where table_schema = database() \
      group by table_name \
having sum(if(column_key in ('PRI', 'UNI'), 1, 0))=0

# Requêtes SQL sur 7 jours (avec sum_errors, sum_created_tmp_tables, sum_select_full_join ...)
mariadb.perfDigests = select digest_text, count_star, avg_timer_wait/1000000000 AVG_TIMER_WAIT_MS, \
  sum_lock_time/1000000000 SUM_LOCK_TIME_MS, sum_errors, sum_warnings, sum_rows_affected, sum_rows_sent, sum_rows_examined, \
  sum_created_tmp_disk_tables, sum_created_tmp_tables, sum_select_full_join, sum_select_full_range_join, \
  sum_select_range, sum_select_range_check, sum_select_scan, sum_sort_merge_passes, sum_sort_range, sum_sort_rows, sum_sort_scan, \
  sum_no_index_used, sum_no_good_index_used \
from performance_schema.events_statements_summary_by_digest \
where timestampadd(minute, 24*60, last_seen) > localtimestamp and schema_name = database() \
order by sum_timer_wait desc \
limit 5000

mariadb.memory = select event_name, count_alloc, count_free, sum_number_of_bytes_alloc, sum_number_of_bytes_free, \
  low_count_used, current_count_used, high_count_used, low_number_of_bytes_used,current_number_of_bytes_used, \
  high_number_of_bytes_used \
  from performance_schema.memory_summary_global_by_event_name \
where count_alloc > 0

###### Mysql 5+ ("show engine innodb status" au lieu de "show innodb status")

mysql.processlist = show full processlist
mysql.databases = show databases
mysql.variables = show variables
mysql.global_status = show global status
mysql.innodb_status = show engine innodb status
# (en général le log slow queries se fait en fichier et non dans cette table)
# mysql.slow_log = SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 100

mysql.unusedIndexes = SELECT object_name, index_name \
  FROM performance_schema.table_io_waits_summary_by_index_usage \
 WHERE index_name IS NOT NULL \
   AND index_name != 'PRIMARY' \
   AND count_star = 0 \
   AND object_schema = database() \
 ORDER BY object_name

# if needed: UPDATE performance_schema.setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long';
mysql.longRunning = SELECT left(digest_text, 64) \
     , COUNT(*) as cnt \
     , ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms \
     , ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms \
     , ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms \
     , ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms \
     , ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms \
     , ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms \
     , MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS first_seen \
     , MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS last_seen \
  FROM performance_schema.events_statements_history_long \
  JOIN information_schema.global_status AS isgs \
 WHERE isgs.variable_name = 'UPTIME' and current_schema = database() \
 GROUP BY LEFT(digest_text,64) \
 ORDER BY tot_exec_ms DESC \
 LIMIT 5000

# following reports inspired by mysql perf analyzer (Apache license)
# https://github.com/yahoo/mysql_perf_analyzer/blob/master/myperf/src/main/resources/sql.xml
mysql.tableStats = select table_name, table_type, engine, \
    case when table_rows > 1000000 then concat(round(table_rows/1000000,6), 'M') else table_rows end table_rows, \
    concat(round(data_length/1048576),'MB') data, concat(round(index_length/1048576), 'MB') idx, \
    concat(round((data_length+index_length)/1048576),'MB') total, \
    concat(round(data_free/1048576),'MB') free, avg_row_length \
from information_schema.tables \
where table_schema = database()

mysql.eventsWaits = select e.event_name, e.count_star, e.sum_timer_wait/1000000000 WAIT_MS, e.sum_lock_time/1000000000 LOCK_MS \
from performance_schema.events_statements_summary_by_thread_by_event_name e \
where e.count_star > 0 \
order by e.event_name

mysql.tableIoWaits = select object_name NAME, \
     count_star, avg_timer_wait/1000000000 AVG_WAIT_MS, \
     count_read, avg_timer_read/1000000000 AVG_READ_MS, \
     count_write, avg_timer_write/1000000000 AVG_WRITE_MS, \
     count_fetch, avg_timer_fetch/1000000000 AVG_FETCH_MS, \
     count_insert, avg_timer_insert/1000000000 AVG_INSERT_MS, \
     count_update, avg_timer_update/1000000000 AVG_UPDATE_MS, \
     count_delete, avg_timer_delete/1000000000 AVG_DELETE_MS \
from performance_schema.table_io_waits_summary_by_table \
where count_star > 0 and object_schema = database() \
order by 1 limit 5000

mysql.indexIoWaits = select CONCAT(object_name,'.', index_name) NAME, \
     count_star, avg_timer_wait/1000000000 AVG_WAIT_MS, \
     count_read, avg_timer_read/1000000000 AVG_READ_MS, \
     count_write, avg_timer_write/1000000000 AVG_WRITE_MS, \
     count_fetch, avg_timer_fetch/1000000000 AVG_FETCH_MS, \
     count_insert, avg_timer_insert/1000000000 AVG_INSERT_MS, \
     count_update, avg_timer_update/1000000000 AVG_UPDATE_MS, \
     count_delete, avg_timer_delete/1000000000 AVG_DELETE_MS \
from performance_schema.table_io_waits_summary_by_index_usage \
where index_name is not null and count_star > 0 and object_schema = database() \
order by 1 limit 5000

mysql.tableLockWaits = select object_name NAME, \
     count_star, avg_timer_wait/1000000000 AVG_WAIT_MS, \
     count_read, avg_timer_read/1000000000 AVG_READ_MS, \
     count_write, avg_timer_write/1000000000 AVG_WRITE_MS, \
     count_read_normal, avg_timer_read_normal/1000000000 AVG_R_NORMAL_MS, \
     count_read_with_shared_locks, avg_timer_read_with_shared_locks/1000000000 AVG_R_SHARED_LOCKS_MS, \
     count_read_high_priority, avg_timer_read_high_priority/1000000000 AVG_R_HIGH_PRIORITY_MS, \
     count_read_no_insert, avg_timer_read_no_insert/1000000000 AVG_R_NO_INSERT_MS, \
     count_read_external, avg_timer_read_external/1000000000 AVG_R_EXTERNAL_MS, \
     count_write_allow_write, avg_timer_write_allow_write/1000000000 AVG_W_ALLOW_W_MS, \
     count_write_concurrent_insert, avg_timer_write_concurrent_insert/1000000000 AVG_W_CONC_INSERT_MS, \
     count_write_low_priority, avg_timer_write_low_priority/1000000000 AVG_W_LOW_PRIORITY_MS, \
     count_write_normal, avg_timer_write_normal/1000000000 AVG_W_NORMAL_MS, \
     count_write_external, avg_timer_write_external/1000000000 AVG_W_EXTERNAL_MS \
from performance_schema.table_lock_waits_summary_by_table \
where count_star > 0 and object_schema = database() \
order by 1 limit 5000

mysql.tablesWithoutPk = select table_name \
      from information_schema.columns \
      where table_schema = database() \
      group by table_name \
having sum(if(column_key in ('PRI', 'UNI'), 1, 0))=0

# Requêtes SQL sur 7 jours (avec sum_errors, sum_created_tmp_tables, sum_select_full_join ...)
mysql.perfDigests = select digest_text, count_star, avg_timer_wait/1000000000 AVG_TIMER_WAIT_MS, \
  sum_lock_time/1000000000 SUM_LOCK_TIME_MS, sum_errors, sum_warnings, sum_rows_affected, sum_rows_sent, sum_rows_examined, \
  sum_created_tmp_disk_tables, sum_created_tmp_tables, sum_select_full_join, sum_select_full_range_join, \
  sum_select_range, sum_select_range_check, sum_select_scan, sum_sort_merge_passes, sum_sort_range, sum_sort_rows, sum_sort_scan, \
  sum_no_index_used, sum_no_good_index_used \
from performance_schema.events_statements_summary_by_digest \
where timestampadd(minute, 24*60, last_seen) > localtimestamp and schema_name = database() \
order by sum_timer_wait desc \
limit 5000

# mysql 5.7
mysql.memory = select event_name, count_alloc, count_free, sum_number_of_bytes_alloc, sum_number_of_bytes_free, \
  low_count_used, current_count_used, high_count_used, low_number_of_bytes_used,current_number_of_bytes_used, \
  high_number_of_bytes_used \
  from performance_schema.memory_summary_global_by_event_name \
where count_alloc > 0

###### Mysql 4- ("show innodb statuts" au lieu de "show engine innodb status")

mysql4.processlist = show full processlist
mysql4.databases = show databases
mysql4.variables = show variables
mysql4.global_status = show global status
mysql4.innodb_status = show innodb status

###### Oracle

oracle.instance = select current_timestamp, v$instance.* from v$instance

oracle.database = select * from v$database

oracle.nlsParameters = select d.parameter, d.value "Base", i.value "Instance", s.value "Session" \
  from nls_database_parameters d, nls_instance_parameters i, nls_session_parameters s \
  where d.parameter=i.parameter(+) and d.parameter=s.parameter(+)

# les requêtes oracle les plus longues
oracle.sqlTimes = select round(100 * a.pct, 2) pct, \
       round(a.elapsed_time/1000000, 2) elapsed_time, \
       round(a.elapsed_time/a.executions/1000) ms_by_exec, \
       round(a.cpu_time/1000000, 2) cpu_time, \
       a.buffer_gets total_cost, \
       round(a.buffer_gets/a.executions) elem_cost, \
       a.executions exec, \
       a.rows_processed nb_rows, \
       s.sql_text \
from (select * \
      from (select elapsed_time, \
                   ratio_to_report(elapsed_time) over () pct, \
                   cpu_time, \
                   buffer_gets, \
                   executions, \
                   rows_processed, \
                   address, \
                   hash_value \
            from  v$sql \
            order by elapsed_time desc) \
      where rownum < 26) a, \
     v$sqlarea s \
where a.address = s.address \
  and a.hash_value = s.hash_value \
  and a.executions <> 0 \
order by pct desc, cpu_time desc

oracle.sessions = select sesion.sid, \
    username, \
    osuser, \
    machine, \
    sesion.module, \
    status, \
    optimizer_mode, \
    sql_text \
  from v$sqlarea sqlarea, v$session sesion \
 where sesion.sql_hash_value = sqlarea.hash_value(+) \
   and sesion.sql_address    = sqlarea.address(+) \
   and sesion.username is not null \
 order by username, sql_text

oracle.locks = select \
  username, \
  osuser, \
  machine, \
  s.module, \
  l.sid, \
  decode(l.type, \
     'MR', 'Media Recovery', \
     'RT', 'Redo Thread', \
     'UN', 'User Name', \
     'TX', 'Transaction', \
     'TM', 'DML', \
     'UL', 'PL/SQL User Lock', \
     'DX', 'Distributed Xaction', \
     'CF', 'Control File', \
     'IS', 'Instance State', \
     'FS', 'File Set', \
     'IR', 'Instance Recovery', \
     'ST', 'Disk Space Transaction', \
     'TS', 'Temp Segment', \
     'IV', 'Library Cache Invalidation', \
     'LS', 'Log Start or Switch', \
     'RW', 'Row Wait', \
     'SQ', 'Sequence Number', \
     'TE', 'Extend Table', \
     'TT', 'Temp Table', l.type) type, \
  decode(lmode, \
     0, 'None', \
     1, 'Null', \
     2, 'Row-S (SS)', \
     3, 'Row-X (SX)', \
     4, 'Share', \
     5, 'S/Row-X (SSX)', \
     6, 'Exclusive', lmode) lmode, \
  decode(request, \
     0, 'None', \
     1, 'Null', \
     2, 'Row-S (SS)', \
     3, 'Row-X (SX)', \
     4, 'Share', \
     5, 'S/Row-X (SSX)', \
     6, 'Exclusive', request) request, \
  decode(block, \
     0, 'Not Blocking', \
     1, 'Blocking', \
     2, 'Global', block) block, \
  owner, \
  object_name, \
  a.sql_text \
from v$locked_object lo, all_objects ao, v$lock l, v$session s, v$sqlarea a \
where lo.object_id = ao.object_id \
  and l.sid = lo.session_id \
  and s.sid = l.sid \
  and a.address = s.sql_address(+) \
  and a.hash_value = s.sql_hash_value(+) \
order by username

oracle.foreignKeysWithoutIndexes = \
  select user_cons_columns.table_name || '.' || user_constraints.constraint_name as "Foreign key" \
  from user_cons_columns, user_constraints \
  where user_constraints.constraint_type = 'R' and \
    user_constraints.constraint_name = user_cons_columns.constraint_name and \
    user_cons_columns.table_name || '.' || user_cons_columns.column_name not in (select table_name || '.' || column_name from user_ind_columns)

oracle.invalidObjects = select object_name from user_objects where status = 'INVALID'

oracle.disabledConstraints = select table_name || '.' || constraint_name as "Constraint" from user_constraints where status = 'DISABLED'

oracle.tableStats = select * from USER_TAB_STATISTICS

# Increase the Shared Pool Size if Library Cache < 99%
# Increase the Shared Pool Size if Row Cache < 85%
# Increase DB_BLOCK_BUFFER if cache hit ratio < 90%
oracle.ratios = select round(100*(1-sum(reloads)/sum(pins)),2) || '% Library Cache Ratio' Ratio from v$librarycache \
union \
select round(100*(1-sum(getmisses)/sum(gets)),2) || '% Row Cache Ratio' from v$rowcache \
union \
select round(100*(1-(phy.value / (cur.value + con.value))),2) || '% Cache Hit Ratio' \
from v$sysstat cur, v$sysstat con, v$sysstat phy \
where cur.name = 'db block gets' and \
      con.name = 'consistent gets' and \
      phy.name = 'physical reads'

oracle.parameters = select * from v$parameter

oracle.rollbackSegmentStatistics = Select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets", \
       rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits", \
       rs.Shrinks "# Shrinks", rs.Extends "# Extends" \
from   v$RollName rn, v$RollStat rs \
where  rn.usn = rs.usn

oracle.statistics = select name statistic, value system, \
           (select sum(value) from v$sesstat ses where ses.statistic# = sys.statistic#) sessions \
from v$sysstat sys

oracle.events = select event, time_waited system, \
           (select sum(time_waited) from v$session_event ses where ses.event = sys.event) sessions \
from v$system_event sys \
where event != 'Null event' and \
  event != 'rdbms ipc message' and \
  event != 'pipe get' and \
  event != 'virtual circuit status' and \
  event != 'lock manager wait for remote message' and \
  event not like '% timer' and \
  event not like 'SQL*Net message from %'

# les io reads / writes par datafile oracle
oracle.datafileIo = select   df.NAME filename, \
         ts.name tablespace_name, \
         PHYRDS physical_reads, \
         round((PHYRDS / pd.PHYS_READS)*100,2) pct_reads, \
         PHYWRTS physical_writes, \
         round(PHYWRTS * 100 / pd.PHYS_WRTS,2) pct_writes, \
         fs.PHYBLKRD + fs.PHYBLKWRT total_block_io \
from    (select sum(PHYRDS) phys_reads, \
                sum(PHYWRTS) phys_wrts \
         from v$filestat) pd, \
         v$datafile df, \
         v$filestat fs, \
         v$tablespace ts \
where    df.FILE# = fs.FILE# and df.ts# = ts.ts# \
order by fs.PHYBLKRD + fs.PHYBLKWRT desc

# les parametres extents par tablespace oracle
oracle.tablespaceExtents = select   TABLESPACE_NAME, \
         INITIAL_EXTENT, \
         NEXT_EXTENT, \
         MIN_EXTENTS, \
         MAX_EXTENTS, \
         PCT_INCREASE, \
         MIN_EXTLEN, \
         STATUS, \
         CONTENTS, \
         LOGGING, \
         EXTENT_MANAGEMENT, \
         ALLOCATION_TYPE \
from     user_tablespaces \
order by TABLESPACE_NAME

# l'espace utilise / libre par tablespace oracle
oracle.tablespaceFreespace = select   ddf.TABLESPACE_NAME, \
         ddf.BYTES tablespace_size, \
         ddf.BYTES-nvl(DFS.BYTES,0) used, \
         round(((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES)*100,2) pct_used, \
         nvl(dfs.BYTES,0) free, \
         round((1-((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES))*100,2) pct_free \
from    (select t.name TABLESPACE_NAME, \
                sum(BYTES) bytes \
         from   v$datafile d, v$tablespace t \
         where t.ts# = d.ts# \
         group  by t.name) ddf, \
        (select TABLESPACE_NAME, \
                sum(BYTES) bytes \
         from   user_free_space \
         group  by TABLESPACE_NAME) dfs \
where    ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME(+) \
order by ((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES) desc

###### DB2

# MON_CURRENT_SQL returns metrics for all activities that were submitted on all members of the database and have not yet been completed, including a point-in-time view of currently executing SQL statements.
db2.mon_current_sql = select * from MON_CURRENT_SQL
# MON_DB_SUMMARY returns metrics aggregated over all service classes.
db2.mon_db_summary = select * from MON_DB_SUMMARY
# MON_LOCKWAITS returns information about agents working on behalf of applications that are waiting to obtain locks in the currently connected database.
db2.mon_lockwaits = select * from MON_LOCKWAITS
# MON_SERVICE_SUBCLASS_SUMMARY returns metrics for all service subclasses, showing work executed per service class.
db2.mon_service_subclass_summary = select * from MON_SERVICE_SUBCLASS_SUMMARY
# MON_CURRENT_UOW returns metrics for all units of work.
db2.mon_current_uow = select * from MON_CURRENT_UOW
# MON_WORKLOAD_SUMMARY returns metrics for all workloads, showing incoming work per workload.
db2.mon_workload_summary = select * from MON_WORKLOAD_SUMMARY

# The 6 monitor (mon) views above are in the SYSIBMADM schema.
# They wont give a historical summary like the snapshot but they will give a good picture of how the database is travelling at a certain point in time.

# info and metrics for connections (accumulation of all metrics for requests that were submitted by each connection)
db2.mon_get_connection = select * from table(MON_GET_CONNECTION(CAST(NULL AS BIGINT), -1)) as connection

# snapshot_dyn_sql (current queries, requires system-wide privileges)
db2.current_queries = select * from table(SNAPSHOT_DYN_SQL(CAST(NULL AS VARCHAR(8)), -1)) as dynsql

###### HSQLDB

hsqldb.system_sessions = select * from information_schema.SYSTEM_SESSIONS
hsqldb.system_cacheinfo = select * from information_schema.SYSTEM_CACHEINFO
hsqldb.system_properties = select * from information_schema.SYSTEM_PROPERTIES
hsqldb.system_schemas = select * from information_schema.SYSTEM_SCHEMAS

###### H2 (utilisé notamment en tests unitaires)

h2.memory = select 'Database: ' || database() as Value union select 'Memory: ' || memory_used() || 'Kb / ' || (memory_used() + memory_free()) || 'Kb' as Value union select 'Date and time:' || current_timestamp() as Value
h2.sessions = select * from information_schema.sessions
h2.locks = select * from information_schema.locks
h2.settings = select * from information_schema.settings

###### MS SQLSERVER
sqlserver.version = select @@version
sqlserver.connections = select GETDATE(), @@connections

###### Sybase
sybase.sp_who = sp_who
sybase.connections = select p.hostname,p.program_name,p.ipaddr,l.name,db_name(p.dbid) base ,count(1) nb_connections from master..sysprocesses p,master..syslogins l \
where \
p.suid = l.suid \
group by p.hostname,p.program_name,db_name(p.dbid),p.ipaddr,l.name \
order by nb_connections desc ,name
sybase.sp_lock = sp_lock
sybase.lock = select  p.spid,l.name,  count(1) locks,  p.blocked, p.status, p.cmd, ob.name table_name \
from master..sysprocesses p, master..syslogins l, master..syslocks lo, sysobjects ob \
where \
p.suid = l.suid \
and lo.spid = p.spid \
and lo.id = ob.id \
group by l.name, p.spid, p.blocked, p.status, p.cmd, ob.name \
order by spid
sybase.running_stored_procedure = select s1.spid,s3.name login ,s2.name proc_name from master..sysprocesses s1,sysobjects s2,master..syslogins s3 where s1.id = s2.id and s3.suid = s1.suid
sybase.used_temporary_tables = select distinct s2.name ,m.spid,s3.name login from tempdb..sysobjects s2,master..syslocks m,master..syslogins s3,master..sysprocesses s1 where s2.id=m.id and m.spid=s1.spid and s3.suid = s1.suid
sybase.used_tables = select distinct s2.name, m.spid,s3.name login  from sysobjects s2,master..syslocks m,master..syslogins s3,master..sysprocesses s1 where s2.id=m.id and m.spid=s1.spid and s3.suid = s1.suid
sybase.sp_version = sp_version

###### Informix
# http://www.informix.com.ua/articles/sysmast/sysmast.htm
informix.version = select first 1 dbinfo('version', 'full') from sysmaster:systables
informix.sessions = select sdb.name database, sses.username, sses.hostname, sses.feprogram, sl.owner sid \
from  sysmaster:syslocks sl, sysmaster:sysdatabases sdb, outer sysmaster:syssessions sses \
where sl.tabname = 'sysdatabases' and  sl.rowidlk = sdb.rowid  and sl.owner = sses.sid \
order by 1
informix.resources_by_user = select username, sprf.sid, lockreqs, bufreads, bufwrites \
from  sysmaster:syssesprof sprf, sysmaster:syssessions sses \
where sprf.sid = sses.sid \
order by bufreads desc
informix.current_queries = select username, sqx_sessionid, sqx_conbno, sqx_sqlstatement \
from sysmaster:syssqexplain, sysmaster:sysscblst \
where sqx_sessionid = sid
informix.config = select * from sysmaster:sysconfig

###### SQLite
sqlite.version = select sqlite_version()
sqlite.database_list = PRAGMA database_list