Working as a DBA (Database Administrator) for many years I wrote a large amount of SQL scripts. Here are the most recent and intresting snippets. The DBAs' job is similar to the Doctors' one... they both must analyze the data to make the diagnosis and choose the right treatment. Often the DBA uses sophisticated tools that allow quick diagnosis like an electrocardiogram or a CT scan. But for some complex or stange cases there isn't any tool and you need to collect and analyze data by creating a new program. In the majority of cases they are simple programs such as an oximeter, but they are very useful! The scripts in this page are same examples: they collect most intresting parameters from a system in order to analyze the health of a Database.
Because there are different kinds of databases here are different samples for PostgreSQL, MySQL, Oracle. Of course in most recent versions and with the most complex configurations!
The following script extract data about users connected to the database and their activity
on PostgreSQL.
The query works on PostgreSQL 10+ versions because
the pg_stat_activity columns were changed to report more informations.
select pid,
datname,
usename,
client_addr,
backend_type,
state,
wait_event is not null as waiting, wait_event, wait_event_type,
query
from pg_stat_activity
order by state, pid;
Other useful queries for PostgreSQL can be found in the following scripts: pg2html.sql (for version 10 or newer) and pg2html.aur2.sql (for Aurora PostgreSQL version 2 or newer).
The following script extract data about locks and blocking transations
on MySQL.
The query works from MySQL 5.5 version because
new system views were introduced.
SELECT p.id, p.user, p.state,
r.trx_id trx_id, r.trx_mysql_thread_id thread, r.trx_query query,
b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query
FROM information_schema.processlist p, information_schema.innodb_lock_waits w,
information_schema.innodb_trx b, information_schema.innodb_trx r
WHERE b.trx_id = w.blocking_trx_id
AND r.trx_id = w.requesting_trx_id
AND p.id=r.trx_mysql_thread_id;
Other useful queries for MySQL can be found in the following scripts: my2html.57.sql, my2html.80.sql, and my2html.103.sql for MariaDB 10.3.
The following script extract data about I/O Performances and Optimizations
on Oracle.
The query can be executed on any Oracle 11g database but gives specific information
on Exadata Hardware that uses I/O Cell Offloading.
SELECT * FROM (
SELECT sql_id, child_number child, plan_hash_value plan, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions)
/decode(px_servers_executions,0,1,px_servers_executions/decode(nvl(executions,0),0,1,executions)) avg_time,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_par,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') offload,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)
/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) IO_saved,
sql_text sql1
FROM v$sql s
WHERE IO_CELL_OFFLOAD_ELIGIBLE_BYTES <> 0
ORDER BY 5 desc)
WHERE rownum < 21;
Other useful queries for Oracle can be found in the following script ora2html.sql and its plugin custom_11g.sql.
Breaking news: Oracle 12c is out!
The following script extract data PDB History.
The query is useful when using the new Multitenant Oracle 12c Option.
column db_name format a20 column pdb_name format a20 column cloned_from format a20 set lines 132 SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME cloned_from FROM CDB_PDB_HISTORY WHERE CON_ID>2 ORDER BY OP_TIMESTAMP;
Other useful queries for Oracle 12c can be found in the following plugins: custom_12c.sql, custom_18c.sql.
Other useful scripts are ux2html.sh and its Plug-ins.
Title: DBA SQL Scripts
Level: Hack
Date:
31 January 2013
Version: 1.0.5 - 1st April 2020
Author: mail [AT] meo.bogliolo.name