net.bull.javamelody.resource.databaseInformations.properties Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of javamelody-core Show documentation
Show all versions of javamelody-core Show documentation
Monitoring of JavaEE applications
###### 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() as Database, memory_used() || 'Kb / ' || (memory_used() + memory_free()) || 'Kb' as Memory, current_timestamp() as DateAndTime
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