Ricerca statement SQL in Oracle

Questa paginetta riporta alcune utili query Oracle per cercare... altre query!
Cercheremo gli statement attivi sul sistema, quelli piu' pesanti, gli execution plan, quelli eseguiti la scorsa settimana, ... insomma un po' di tutto.

Molte delle indicazioni valgono per tutte le versioni di Oracle ma nel tempo sono cambiate molte cose: per motivi pratici in questa paginetta faremo riferimento all'ultima versione di Oracle disponibile come Enterprise Edition on premises, ovvero la 26ai, e per il relativo capitolo, considereremo disponibili le due Oracle Option: Diagnostics Pack e Tuning Pack.

Viste di sistema: v$session v$sql v$sqlarea v$sqlstats ...

Sul database Oracle le informazioni di dettaglio sugli statement SQL si possono raccogliere da molteplici viste di sistema.

Il data dictionary di Oracle e' molto ricco, le dynamic performance views ne sono un esempio.
La V$SESSION riporta i dati di tutte le sessioni connesse al database; la vista V$PROCESS riporta i dati di tutti i processi presenti. Sebbene ricche di informazioni queste due viste non bastano per conoscere il testo degli statement SQL attivi, infatti la colomma V$SESSION.COMMAND contiene solo la codifica del tipo di statement SQL. Per conoscere il testo dell'SQL e' necessario accedere alla vista V$SQL che viene aggiornata al termine di ogni esecuzione e, per le query di lunga durata, riceve un campionamento ogni 5 secondi. V$SQL ha una riga con le statistiche di ogni esecuzione mentre V$SQLAREA riporta le statistiche in modo aggregato per ogni statement SQL con lo stesso SQL_ID. La vista V$SQLSTATS e' stata introdotta nella 10g R2, e' piu' efficiente della V$SQLAREA ed ha una ritenzione maggiore.

Le V$SQL, V$SQLAREA e V$SQLSTATS hanno molte colonne colonne in comune (eg. SQL_ID, EXECUTIONS, CPU_TIME, BUFFER_GETS, PLAN_HASH_VALUE, ...) ma vi sono alcune piccole ma importanti differenze.
Uno statement SQL puo' avere uno o piu' child cursor o version. La vista V$SQL ha una riga per ogni child cursor mentre la vista V$SQLAREA riporta i totali. Quando V$SQLAREA ha VERSION_COUNT>1 vi sono piu' child cursor in V$SQL con CHILD_NUMBER differenti. Analizzando la V$SQL_SHARED_CURSOR e' possibile determinare perche' vengono creati child differenti.
Anche la vista V$SQLSTATS riporta dati totali ma e' implementata in modo differente e richiede un numero di lock inferiore: in pratica risulta piu' veloce. Nella vista V$SQLSTATS e' disponibile l'intero statement SQL ma sono disponibili nettamente meno colonne (eg. PARSING_SCHEMA_NAME). Gli esempi riportati nel seguito possono essere modificati utilizzando viste diverse a secondo del tipo di ricerca sia piu' opportuna.
Altre viste utili accessibili tramite join sono V$SQL_PLAN, V$SQLTEXT, ...

Con Oracle RAC si utilizzano le viste GV$ analoghe alle viste V$ ma che riportano dati globali per l'intero cluster.

... ma abbiamo parlato troppo: facciamo un poco di pratica!

Sessioni attive

select s.sid||','||s.serial# sid_serial,
       s.schemaname username,  
       s.osuser os_user,
       p.spid process,
       s.type type,
       s.status 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',23,'Validate index',24,'Create procedure',25,'Alter procedure',
   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', 
   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',79,'Alter Role',
   85,'Truncate table',86,'Truncate Cluster', 
   88,'Alter View',91,'Create Function',92,'Alter Function',93,'Drop Function', 
   94,'Create Package',95,'Alter Package',96,'Drop Package', 
   97,'Create PKG Body',98,'Alter PKG Body',99,'Drop PKG Body',
   0,'No command',
   'Other') Command,
       substr(s.program,1,30) program,
       substr(module,1,30) module,
       to_char(logon_time, 'YYYY-MM-DD HH24:MI:SS') as logon,
       s.client_identifier
  from v$process p, v$session s
 where s.paddr = p.addr
 order by s.type desc, s.status, s.sid;

In questa query, come in quelle successive, e' possibile utilizzare le viste GV$ per ottenere i dati sull'intero RAC avendo cura di inserire le opportune ulteriori condizioni di join (in questo caso and s.inst_id = p.inst_id).

Statement SQL

select s.sid||','||s.serial# sid_serial,
       s.username,
       q.executions exec,
       q.parse_calls parse,
       q.disk_reads read,
       q.buffer_gets get,  
       q.sql_id,  
       q.sql_text
  from v$session s, v$sql q
 where s.sql_address=q.address
   and s.type <> 'BACKGROUND'
   and s.status = 'ACTIVE'
   and s.username <> 'SYS'
 order by s.sid;

Escludendo l'utente SYS evitiamo di riportare processi di sistema attivi o comandi eseguiti come amministratori.

Naturalmente, in questa query come in tutte le altre query di esempio, e' possibile utilizzare ulteriori condizioni per raffinare la ricerca. Per rendere attiva una sessione basta lanciare il comando call DBMS_SESSION.sleep(60) [NdA disponibile della versione 18].

La colonna SQL_ID e' molto importante perche' in Oracle identifica in modo univoco uno statement, due query avranno lo stesso SQL_ID se sono identiche in ogni parte (attenzione contano anche gli spazi ed i commenti):

Query pesanti (CPU)

select * from 
(select cpu_time, elapsed_time, executions, round(elapsed_time/executions/1000000,3) avg_duration, 
        parsing_schema_name, sql_id, substr(sql_text,1,80) sql_text
   from v$sqlarea a
  where parsing_schema_name NOT IN ('SYS', 'SYSTEM', 'RDSADMIN')
    and executions>0
  order by 1 desc)
where  rownum <=10;

Oltre a SYS escludiamo l'utente SYSTEM ed RDSADMIN (utente utilizzato su Amazon RDS).

Query pesanti (I/O)

select * from 
(select buffer_gets, disk_reads, executions, round(elapsed_time/executions/1000000,3) avg_duration,
        parsing_schema_name, sql_id, substr(sql_text,1,80) sql_text
   from v$sqlarea a
  where parsing_schema_name NOT IN ('SYS', 'SYSTEM')
    and executions>0
  order by 1 desc)
where  rownum <=10;

Ordinando per la seconda colonna otterremo le query che effettuano piu' letture fisiche.

Execution Plan

select plan_table_output from table(dbms_xplan.display_cursor('sql_id',null,'basic'));

Vi sono molteplici possibilita' di ricavare i piani di esecuzione dagli statement SQL usando il package dbms_xplan.

Literal vs bind variables

E' possibile individuare gli statement che non utilizzano le variabili di bind con questa query:

select parsing_schema_name, force_matching_signature, count(*), substr(max(sql_text),1,80)
  from v$sqlarea
 where parsing_schema_name not in ('SYS')
   and force_matching_signature<>0
 group by parsing_schema_name, force_matching_signature
 having count(*)>100
 order by 3 desc;

In generale l'utilizzo di literal non e' una buona pratica di programmazione con Oracle. Vi sono alcuni casi in cui puo' essere vantaggioso (eg. su colonne con distribuzione di dati non uniforme), ma in realta' vi e' modo di trattare anche questi casi [NdA adaptive cursor sharing: colonne IS_BIND_SENSITIVE IS_BIND_AWARE in V$SQL, BIND_AWARE hint, V$SQL_CS_HISTOGRAM, V$SQL_CS_STATISTICS, V$SQL_CS_SELECTIVITY, ...].
E' possibile forzare l'utilizzo della condivisione dei cursori anche se le applicazioni usano literal impostando il parametro cursor_sharing. Non e' consigliato modificare il parametro a livello di sistema, e' piu' opportuno farlo a livello di sessione all'avvio dell'applicazione o con un trigger di logon:

create or replace trigger test.after_logon_trg
after logon on test.schema
begin
  execute immediate 'alter session set cursor_sharing=force';
end;
/

Diagnostic&Tuning Pack

Le Oracle Option Diagnostics Pack e Tuning Pack sono due opzioni distinte tra loro che forniscono al DBA strumenti per l'analisi delle prestazioni ed il tuning del database. L'Automatic Workload Repository (AWR) e l'Automatic Database Diagnostic Monitor (ADDM) fanno parte del Diagnostic Pack; l'SQL Tuning Advisor e le funzionalita' automatiche di tuning e di riorganizzazione del DB fanno invece parte del Tuning Pack [NdA le viste DBA_ADDM_%, DBA_ADVISOR_%, DBA_HIST_%, V_$ACTIVE_SESSION_HISTORY richiedono il Diagnostics Pack, mentre le viste V_$SQL_MONITOR, V_$SQL_PLAN_MONITOR ed il package DBMS_SQLTUNE richiedono il Tuning Pack, il package DBMS_ADVISOR richiede entrambe le option].

Per eseguire le query che seguono e' percio' necessario aver acquistato le Option indicate ed averne abilitato l'utilizzo impostando il parametro CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING.

Query storiche pesanti (I/O)

set pages 9999
set lines 180
column exec format 999,999,999
column cpu format 999,999,999,999
column elapsed format 9,999,999,999,999
column buffer_get format 999,999,999,999
column disk_reads format 999,999,999,999
column rows_proc format 999,999,999,999

select * from (
select s.sql_id, sum(s.EXECUTIONS_DELTA) as exec, sum(s.CPU_TIME_DELTA) as cpu, sum(s.ELAPSED_TIME_DELTA) as elapsed,
       sum(s.BUFFER_GETS_DELTA) as buffer_get, sum(s.DISK_READS_DELTA) as disk_reads, sum(s.ROWS_PROCESSED_DELTA) as rows_proc,
       max(plan_hash_value) as plan_h
  from dba_hist_sqlstat s
  join dba_hist_snapshot n on (s.snap_id=n.snap_id and s.dbid=n.dbid and s.instance_number=n.instance_number)
 where trunc(end_interval_time) between date '2024-04-01' and date '2024-04-08'
   and extract(hour from end_interval_time) between 8 and 18
 group by s.sql_id
 order by 5 desc)
where rownum<=10;

La vista DBA_HIST_SQLSTAT contiene le informazioni storiche sulle statistiche SQL che vengono catturate dall'AWR ad intervalli da V$SQL. Nella vista sono presenti sia i contatori dall'avvio della base dati che i delta dal BEGIN_INTERVAL_TIME all'END_INTERVAL_TIME di DBA_HIST_SNAPSHOT. Altre viste utili sono DBA_HIST_OPTIMIZER_ENV, DBA_HIST_SQLTEXT e DBA_HIST_SQL_PLAN.

E' importante sottolineare che solo gli statement piu' pesanti e di maggior durat vengono catturati dall'AWR. Tuttavia e' possibile evidenziare uno statement SQL in modo che le statistiche vengano raccolte nelle viste di storico con:
  exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(:sqlid);

Sessioni pesanti

select * from (
select username, sql_opname, module, machine,
       sum(delta_read_io_bytes)/(1024) io_r_k, sum(delta_write_io_bytes)/(1024) io_w_k,
       sum(delta_read_io_requests) rq_r_k, sum(delta_write_io_requests) rq_w_k
  from v$active_session_history, dba_users
 where v$active_session_history.user_id = dba_users.user_id
   and username not in ('SYS')
 group by username, sql_opname, module, machine
 order by 5 desc)
 where rownum < 21;

Tuning SQL

DECLARE
  my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => 'MySQLID',
    scope => 'COMPREHENSIVE',
    time_limit => 600,
    task_name => 'MY01_tuning_task',
    description => 'Tune query using sqlid');
END;
/

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'MY01_tuning_task');
END;
/

spool tuning_task_10_MySQLID.txt
set lines 200
set pages 9999
set long 1000000
SET LONGCHUNKSIZE 1000000
set heading off
variable report_out clob;
declare
stmt_task VARCHAR2(80);
begin
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'MY01_tuning_task' ) into :report_out FROM dual;
end;
/
print report_out;
spool off

I suggerimenti forniti dal report possono rivolgersi ad aspetti diversi: creare un indice, raccogliere una statistica, applicare un profile, ... e naturalmente vanno applicati con buonsenso ma possono essere molto utili.

Bonus Queries

Le query che seguono possono essere utili in casi particolari...

-- Bind variables values
select s.sql_id, 
       bc.position, 
       bc.value_string, 
       s.last_load_time, 
       bc.last_captured
  from v$sql s
  left join v$sql_bind_capture bc 
         on bc.sql_id = s.sql_id 
        and bc.child_number = s.child_number
  where sql_id='xyz'
 order by s.sql_id, bc.position;

-- Estracts SQL statement samples for statements not using bind variables
set lines 400 pages 99 trimspool on tab off
col parsing_schema_name for a30
col sql_text for a180
spool cmd
select 'select sql_text from v$sql where force_matching_signature='||force_matching_signature||
       ' and parsing_schema_name='''||parsing_schema_name||''' and rownum <= 3 -- num_stmt='||cnt||';' stmt
  from (select count(*) cnt, parsing_schema_name, force_matching_signature
          from v$sql
         where parsing_schema_name not in ('SYS')
           and force_matching_signature<>0
         group by parsing_schema_name, force_matching_signature
         order by 1  desc)
 where cnt > 10;
spool off
@cmd.sql

Varie ed eventuali...

Sicuramente utile e' l'elenco delle colonne delle viste di sistema tra cui: V$SQL, V$SESSION, V$SQLAREA, V$SQLSTATS, DBA_HIST_SQLSTAT, ...

Ricco di script e' l'ottimo sito Oracle-Base.

E' molto importante conoscere l'evoluzione delle versioni Oracle.

Infine il link ad una paginetta sul tuning SQL, vecchia perche' relativa alla version 11gR2, ma ancora utile.

Titolo: Ricerca statement SQL in Oracle
Livello: Avanzato (3/5)
Data: 1 Aprile 2024
Versione: 1.0.1 - 31 Ottobre 2024 🎃
Autore: mail [AT] meo.bogliolo.name