SQL4DBA - SQL per DBA Oracle

Il DBA (DataBase Administrator) utilizza statement SQL particolari per estrarre le informazioni piu' interessanti sui DB che amministra.
Questa pagina raccoglie e descrive gli script SQL piu' utili ed interessanti per la gestione di un database Oracle.

Il documento e' volutamente breve e pratico con esempi funzionanti di statement SQL.
Informazioni di dettaglio si possono trovare nella relativa documentazione.

Introduzione

Oracle fornisce da sempre (e' stato il primo RDBMS commerciale) un SQL ricco di funzionalita'. In particolare sono centinaia le viste di sistema raccolte in un Data Dictionary molto completo. In generale i nomi delle viste sono: USER_ (per gli oggetti dell'utente), ALL_ (per tutti gli oggetti disponibili all'utente), DBA_ (per tutti gli oggetti, ma riservata ai DBA). Ad esempio la vista ALL_TABLES riporta tutte le tabelle accessibili all'utente che la utilizza.
Sempre sotto forma di vista sono disponibili informazioni sullo stato del sistema e dei processi attivi. Ad esempio la vista $VSESSION contiene le informazioni su tutte le sessioni connesse alla base dati.

Gli statement SQL a disposizione di un DBA Oracle sono quindi moltissimi... in questa pagina faremo una scelta su quelli piu' utili ed interessanti.

Sessioni

Una prima visione sull'utilizzo di una base dati e' quella delle connessioni presenti. Oracle fornisce informazioni sia sulle connessioni dei processi di sistema (background) che di quelli utente. Ecco come ottenere l'elenco delle sessioni presenti sulla base dati:

column os_user format a16 column username format a12 column sid format 9999 column command format a12 column program format a23 select s.sid, s.schemaname username, s.osuser os_user, p.spid process, substr(s.type,1,1) type, s.status, decode(s.command, 1, 'Create table',2,'Insert', 3, 'Select', 4,'Create cluster',5,'Alter cluster',6,'Update',7,'Delete', 8,'Drop',9,'Create index',10,'Drop index',11,'Alter index', 12,'Drop table',15,'Alter table', 16, 'Drop Seq.', 17,'Grant',18,'Revoke', 19,'Create synonym',20,'Drop synonym',21,'Create view', 22,'Drop view',26,'Lock table',27,'No operation',28,'Rename', 29,'Comment',30,'Audit',31,'Noaudit',32,'Create ext. database', 33,'Drop ext. database',34,'Create database',35,'Alter database', 36,'Create rollback segment',37,'Alter rollback segment', 38,'Drop rollback segment',39,'Create tablespace', 40,'Alter tablespace',41,'Drop tablespace',42,'Alter session', 43,'Alter user',44,'Commit',45,'Rollback',46,'Savepoint', 23,'Validate index',24,'Create procedure',25,'Alter procedure', 47,'PL/SQL Exec',48,'Set Transaction', 60,'Alter trigger',62,'Analyze Table', 63,'Analyze index',71,'Create Snapshot Log', 72,'Alter Snapshot Log',73,'Drop Snapshot Log', 74,'Create Snapshot',75,'Alter Snapshot', 76,'Drop Snapshot',85,'Truncate table', 183,'Alter Operator', 0,'No command', 'Other') Command, substr(s.program,1,23) program from v$process p, v$session s where s.paddr = p.addr order by s.sid;

La selezione e' molto semplice ed utilizza un join tra la vista delle sessioni e quella dei processi per ottenere informazioni anche sui processi di sistema utilizzati.
Il lunghissimo decode() consente di estrarre il comando utilizzato e naturalmente e' sempre piu' ampio per ogni versione di Oracle.

Lock

Gli oggetti sulla base dati vengono protetti dall'utilizzo concorrente in Oracle mediante la gestione dei lock. In qualche caso possono verificarsi blocchi dovuti a sessioni che non rilasciano i lock e non permettono la modifica dei dati ad altre sessioni. Ecco come individuare i lock sospensivi e come risolverli:

SELECT a.object_id, b.object_name, sid||','||serial# session_id, username, command, schemaname, osuser, machine, terminal FROM v$locked_object a, dba_objects b, v$session WHERE a.object_id = b.object_id and a.session_id = sid; ALTER system kill session 'sid,serial#';

Nota: prima di ammazzare qualcuno... e' importante essere certi di quello che si sta facendo!

Utilizzo di spazio

Oracle utilizza in modo molto sofisticato lo spazio sullo storage preallocando gli oggetti ed ottimizzandone la memorizzazione. Tutti gli oggetti vengono memorizzati su contenitori logici chiamati TABLESPACE costituiti a loro volta dagli spazi fisici assegnati ai DATAFILE. Nelle diverse versioni le modalita' si sono evolute adeguandosi alle accresciute capacita' degli storage ed alle esigenze di database sempre piu' grandi.
Per ottenere i dettagli delle allocazioni di spazio si possono interrogare la vista DBA_EXTENTS che riporta le allocazioni di ogni singolo oggetto e la vista DBA_FREE_SPACE che riporta gli spazi liberi.
Ecco un esempio:

set lines 132 create view v_tab_occ as select tablespace_name,sum(bytes) bytes, max(extent_id)+1 max_extent from sys.dba_extents group by tablespace_name; create view v_tab_free as select tablespace_name,max(bytes) bytes from sys.dba_free_space group by tablespace_name; select a.tablespace_name tablespace, to_char(sum(a.bytes),'999,999,999,999,999') total, to_char(nvl(b.bytes,0),'999,999,999,999,999') occuped, nvl(substr(to_char(trunc(b.bytes/sum(a.bytes)*100)),1,3),'0')||'%' pct, to_char(nvl(c.bytes,0),'999,999,999,999,999') max_free, to_char(nvl(b.max_extent,0),'999,999') max_extent from sys.dba_data_files a, v_tab_occ b, v_tab_free c where a.tablespace_name = b.tablespace_name (+) and a.tablespace_name = c.tablespace_name (+) group by a.tablespace_name,b.bytes,c.bytes,b.max_extent order by a.tablespace_name;

Si potrebbe scrivere la query in modo piu' elegante... ma cosi' funziona su tutte le versioni di Oracle diffuse sulla terra (dalla 7 alla 12) [NdE La versione 7 e' del 1992, la versione 12 e' stata rilasciata a luglio 2013].

Performance

L'ottimizzazione delle performance e' il leitmotiv di ogni DBA. I punti di vista sono moltissimi: individuare gli statement SQL piu' pesanti, ottimizzare i piani di esecuzione degli statement, determinare eventuali bottleneck, effettuare il tuning sui parametri di configurazione, ...
L'ottimizzatore di Oracle e' cost-based [NdE per essere precisi... il cost-based e' stato introdotto nella versione Oracle 7.0 (1992), dalla 8.0 (1997) e' il metodo di default e nella 10.0 (2003) e' stato eliminato il RULE BASED] e' quindi fondamentale il calcolo delle statistiche sulle dimensioni delle tabelle e la selettivita' degli indici. Quando le statistiche sono corrette l'ottimizzatore Oracle ottiene Plan molto efficaci.
Ecco qualche query di esempio:

select s.sid, s.username, p.username os_user, p.spid process, s.program, substr(s.status,1,6) status, q.executions exec, q.parse_calls parse, q.disk_reads read, q.buffer_gets get from v$process p, v$session s, v$sql q where p.addr=s.paddr and s.sql_address=q.address and s.type <> 'BACKGROUND' and s.status='ACTIVE' order by q.buffer_gets desc; select 'A) Hit ratio buffer cache (>90%): '|| to_char(round(1-( sum(decode(name,'physical reads',1,0)*value) /(sum(decode(name,'db block gets',1,0)*value) +sum(decode(name,'consistent gets',1,0)*value)) ), 3)*100) || '%' statistic from v$sysstat where name in ('db block gets', 'consistent gets', 'physical reads'); exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT'); EXPLAIN PLAN for select * from emp;

La prima query individua gli statement SQL attivi e li ordina per numero di accessi a disco. Il numero di GET, usato nell'ordinamento della query, e' spesso il fattore determinante in una query; ma vi sono altre colonne interessanti nella vista V$SQL da analizzare.
La seconda query riporta l'Hit Ratio della Buffer Cache. Il valore indicato (90%) e' solo indicativo. In molti casi (eg. OLTP) la percentuale ottimale di utilizzo della cache e' il 100%!
Nelle versioni piu' recenti la raccolta delle statistiche necessarie all'ottimizzatore avviene in automatico (DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC)... ma se serve analizzare uno schema l'esempio successivo indica come!
Da ultimo ecco come ottenere nella PLAN_TABLE il plan di Oracle di una select [NdE ovviamente nell'esempio di query banale sara' un FULL TABLE SCAN].

La versione 10g di Oracle introduce diverse funzialita' di aiuto nell'ottimizzazione delle performances. L'AWR (Automatic Workload Repository) colleziona automaticamente le statistiche di accesso [NdE differenti da quelle dell'ottimizzatore gia' presenti dalla versione 7.0], l'ADDM (Automatic Database Diagnostic Monitor) determina eventuali problemi di performance ed avvisa il DBA.
Cambia quindi il modo di analizzare le prestazioni su un database Oracle e si possono utilizzare nuove tabelle/viste in SQL:

SELECT h.event Wait_Event, to_char( SUM(h.wait_time + h.time_waited), '999,999,999,999') Total_Wait_Time FROM v$active_session_history h, v$event_name e WHERE h.sample_time BETWEEN sysdate - 1 AND sysdate AND h.event_id = e.event_id AND e.wait_class <> 'Idle' GROUP BY h.event HAVING SUM(h.wait_time + h.time_waited) > 0.1 ORDER BY 2 DESC; select * from ( SELECT 'On '||o.TYPE||' '||o.attr1||'.'||o.attr2||' '||o.attr4 Object_found, ' '||f.message Action_found FROM DBA_ADVISOR_RECOMMENDATIONS r, DBA_ADVISOR_FINDINGS f, DBA_ADVISOR_OBJECTS o WHERE r.TASK_ID = f.TASK_ID AND r.TASK_NAME = f.TASK_NAME AND r.FINDING_ID = f.FINDING_ID AND f.TASK_ID = o.TASK_ID AND f.TASK_NAME = o.TASK_NAME AND f.OBJECT_ID = o.OBJECT_ID and o.type = 'SQL' order by o.attr1, o.attr2) where rownum <41;

La prima statistica si basa determina gli eventi di maggior importanza per le prestazioni del sistema analizzando le attese di tutti gli statement SQL.
La seconda... visualizza direttamente i consigli dell'ADDM! Naturalmente sono consigli da utilizzare buonsenso.

RAC

Nell'architettura RAC piu' nodi accedono allo stesso storage in una configurazione cluster Active-Active garantendo agli utenti finali il massimo del livello di servizio.

Oltre alle normali viste V$ disponibili sui singoli nodi sono presenti una serie di viste GV$ che riportano l'andamento del sistema RAC al completo:

column avg_ms format 999.99 SELECT event, SUM(total_waits) total_waits, ROUND(SUM(time_waited_micro) / 1000000, 2) time_waited_secs, ROUND(SUM(time_waited_micro)/1000 / SUM(total_waits), 2) avg_ms FROM gv$system_event WHERE wait_class <> 'Idle' AND( event LIKE 'gc%block%way' OR event LIKE 'gc%multi%' OR event like 'gc%grant%' OR event = 'db file sequential read') GROUP BY event HAVING SUM(total_waits) > 0 ORDER BY event; SELECT wait_class time_category ,ROUND ( (time_secs), 2) time_secs, ROUND ( (time_secs) * 100 / SUM (time_secs) OVER (), 2) pctFROM (SELECT wait_class wait_class, sum(time_waited_micro) / 1000000 time_secs FROM gv$system_event WHERE wait_class <> 'Idle' AND time_waited > 0 GROUP BY wait_class UNION SELECT 'CPU', ROUND ((SUM(VALUE) / 1000000), 2) time_secs FROM gv$sys_time_model WHERE stat_name IN ('background cpu time', 'DB CPU'))ORDER BY time_secs DESC; WITH sys_time AS ( SELECT inst_id, SUM(CASE stat_name WHEN 'DB time' THEN VALUE END) db_time, SUM(CASE WHEN stat_name IN ('DB CPU', 'background cpu time') THEN VALUE END) cpu_time FROM gv$sys_time_model GROUP BY inst_id ) SELECT instance_name, ROUND(db_time/1000000,2) db_time_secs, ROUND(db_time*100/SUM(db_time) over(),2) db_time_pct, ROUND(cpu_time/1000000,2) cpu_time_secs, ROUND(cpu_time*100/SUM(cpu_time) over(),2) cpu_time_pct FROM sys_time JOIN gv$instance USING (inst_id);

Le query riportate sono tre e riguardano aspetti diversi.
La prima query controlla i tempi di risposta della Global Cache che deve rispondere in tempi brevissimi alle richieste dei nodi (eg. 1/10 rispetto ad un disco).
La seconda query valuta l'overhead introdotto dal cluster. Deve essere inferiore al 10% in una situazione normale.
L'ultima query valuta il bilanciamento del carico tra i diversi nodi presenti.

L'analisi dei singoli nodi e la visione d'insieme ottenuta con queste query permettono di valutare ed eventualmente di effetture un tuning specifico per il RAC.

Data Guard

La configurazione di Oracle Data Guard permette di creare un sito di DR (Disaster Recovery) con RPO (Recovery Point Objective) e RTO (Recovery Time Objective) ottimali.

Naturalmente e' necessario controllare l'aggiornamento del sito secondario rispetto al sito primario. Ecco come:

column arc_first format a20 column arc_last format a20 column scn_next format 999999999999 select applied arch_applied, count(*),min(to_char(FIRST_TIME,'YYYY-MM-DD HH24:MI')) arc_first, max(to_char(FIRST_TIME,'YYYY-MM-DD HH24:MI')) arc_last, max(NEXT_CHANGE#) scn_next from v$archived_log group by applied order by applied desc;

La query utilizza la vista v$archived_log che e' disponibile anche in stato di MOUNT/RECOVER come si trova normalmente l'istanza secondaria.

EXADATA, ODA, ...

Dopo l'acquisizione di Sun Oracle ha sviluppato una serie di sistemi ingegnerizzati per ottenere le massime prestazioni sul database (Eg. Exadata, ODA).

La configurazione di tali sistemi e' piu' semplice rispetto alla preparazione di un ambiente completo acquisendo da fornitori diversi i diversi componenti (server, storage, switch, connessioni di rete, ...) ma la gestione della base dati da parte di un DBA risulta sempre necessaria!

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 <= 20;

La query e' eseguibile dalla versione Oracle 11g ed e' significativa su un sistema Exadata (il modello di punta dell'offerta Oracle) in cui l'ottimizzatore puo' demandare le operazioni di I/O piu' pesanti alle celle (Cell Offload). Con le nuove colonne disponibili sulla V$SQL e' possibile determinare i risparmi ottenuti dai sistemi Exadata sull'I/O mediante l'accesso a celle dedicate per il reperimento dei dati.

Oracle 12c (HEAT MAP)

L'evoluzione non si ferma... nella versione 12c sono disponibili nuove viste per l'amministratore!
Particolarmente interessante e' la Heat Map che consente di determinare i blocchi o gli oggetti piu' acceduti (o meno) ed impostare politiche automatiche di storicizzazione dei dati (ILM: Information Lifecycle Management).
Ecco una query di esempio (richiede l'impostazione del parametro HEAT_MAP=on):

select * from v$heat_map_segment order by (full_scan+lookup_scan) desc fetch first 50 rows only;

Ulteriori informazioni

Un'introduzione ad Oracle si trova in Oracle RDBMS. L'evoluzione delle funzionalita' dell'RDBMS Oracle nel tempo e' stata notevole... La storia delle versioni Oracle negli ultimi 20 anni viene descritta in C'era una volta... Maggiori dettagli tecnici sulle diverse versioni di Oracle e le date di rilascio di ogni versione sono riportate in questo documento.
Il sito Oracle ufficiale contiene tutta la documentazione ufficiale.

DBA SQL Scripts

SQL per DBA Oracle