DBA SQL Scripts

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!

PostgreSQL

The following script extract data about users connected to the database and their activity on PostgreSQL.
The query works on PostgreSQL 9.2 version only because pg_stat_activity columns were changed to report more informations.

SELECT pid, datname, usename, client_addr, waiting, state,
       query_start, state_change, query
 FROM  pg_stat_activity
 ORDER BY datname, pid;

Other useful queries for PostgreSQL can be found in the following scripts: pg2html.10.sql (for version 10 or more) and pg2html.aur2.sql (for Aurora PostgreSQL version 2 or newer).

MySQL

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.

Oracle

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.

Oracle 12c

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.

Note

Other useful scripts are ux2html.sh and its Plug-ins.


Title: DBA SQL Scripts
Level: Hack (5/5)
Date: 31 January 2013
Version: 1.0.4 - 1st April 2020
Author: mail [AT] meo.bogliolo.name