In molti anni di lavoro come DBA (Database Administrator) ho scritto una grande quantita' di script SQL per raccogliere le piu' svariate informazioni dai sistemi che avevo in gestione. Il lavoro del DBA e' simile a quello del medico... perche' deve analizzare i dati per effettuare la diagnosi e definire la terapia. Spesso il DBA utilizza strumenti sofisticati che permettono una diagnosi veloce come da un elettrocardiogramma o una TAC. Ma per i casi piu' complessi o i pazienti piu' difficili non ci sono strumenti e bisogna raccogliere ed analizzare i dati creando un nuovo programma. Nella maggior parte dei casi sono programmi semplici come un comune saturimetro, ma quanto sono utili! Ecco a cosa servono gli script descritti in questa pagina: a raccogliere dal sistema i parametri piu' interessanti per analizzare lo stato di salute di un Database.
Poiche' esistono diversi tipi di database nel seguito sono riportati esempi relativi a PostgreSQL, MySQL, Oracle. Naturalmente nelle versioni piu' recenti e nelle configurazioni piu' significative! L'ordine e' per difficolta' sulle query presentate...
Lo script seguente estrae i dati sugli utenti connessi alla base dati e sulle attivita' in corso
su una base dati PostgreSQL.
La query e' eseguibile dalla versione PostgreSQL 9.2 poiche' le colonne della vista
pg_stat_activity sono cambiate.
La vista in 9.2 riporta l'ultima query eseguita
e l'indicazione se e' ancora attiva o meno. In precedenza veniva riportata solo
la query corrente o lo stato <IDLE>.
I nuovi contenuti della vista consentono un'analisi piu' completa delle attivita' svolte
sulla base dati. E' infatti possibile determinare la durata delle ultime query eseguite
da parte di ogni utente connesso alla base dati.
SELECT pid, datname, usename, client_addr, waiting, state,
query_start, state_change, query
FROM pg_stat_activity
ORDER BY datname, pid;
Altre query interessanti su PostgreSQL sono contenute nello script pg2html.10.sql (versione 10 o superiore) and pg2html.aur2.sql (Aurora PostgreSQL versione 2 o superiore).
Lo script seguente estrae i dati sugli sulle transazioni che stanno bloccando
altre transazioni con un lock.
La query e' eseguibile dalla versione MySQL 5.5 che ha introdotto una serie
di viste sulle attivita' dell'Engine InnoDB.
In precedenza tali informazioni potevano essere solo scaricate su un file di dump.
Con le nuove viste e' possibile incrociare i dati delle transazioni InnoDB con
le sessioni MySQL e determinare in modo semplice chi detiene i lock sulla base dati.
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;
Teoricamente non e' corretto un join con la processlist... ma e' sicuramente pratico! Altre query interessanti su MySQL sono contenute negli script my2html.57.sql, my2html.80.sql, my2html.103.sql (quest'ultimo per MariaDB).
La query seguente estrae i dati prestazionali sull'I/O ottimizzato degli statement SQL.
La query e' eseguibile dalla versione Oracle 11g ed e' significativa
su un sistema Exadata in cui l'ottimizzatore puo' demandare le operazioni
di I/O piu' pesanti alle celle (Cell Offload).
Oracle da sempre fornisce viste molto dettagliate sull'andamento delle attivita'
del sistema.
Con queste nuove colonne sulla V$SQL e' possibile determinare anche i risparmi sull'I/O
ottenuti dai sistemi Exadata che utilizzano celle dedicate per l'accesso ai dati.
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;
Altre query interessanti su Oracle sono contenute nello script ora2html.sql e nel suo plugin custom_11g.sql.
Chi si ferma e' perduto:
e' uscita la nuova versione di Oracle: 12c!
La query seguente estrae la storia dei PDB.
La query e' eseguibile dalla versione Oracle 12c ed e' significativa
quando si utlizza la funzionalita' multitenant realizzata con i PDB (Pluggable DataBase).
In tale architettura l'istanza del CDB (Container Database) mantiene al suo
interno zero, uno o piu' PDB utilizzando lo stesso set di processi, la stessa SGA (System Global Area)
e gli stessi Redo Log.
Dal punto di vista logico i PDB sono completamente separati ed utilizzano un differente
servizio listener, data dictionary distinti, utenti diversi, ... e naturalmente
datafile separati.
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;
Altre query interessanti su Oracle 12c sono contenute nei plug-in: custom_12c.sql, custom_19c.sql.
Altri script utili, realizzati in Bourne Shell, raccolgono i dettagli della configurazione di un sistema: ux2html.sh ed i suoi Plug-in.
Altri documenti di questo tipo su questa pagina
Titolo: DBA SQL Scripts
Livello: Hack
Data:
31 Gennaio 2013
Versione: 1.0.4 - 1 Aprile 2020
Autore: mail [AT] meo.bogliolo.name