Aggiungiamo l'opzione STATSPACK:
In questa paginetta vediamo lo Statspack:
una funzionalita' particolare di Oracle che consente di ottenere informazioni
molto utili sulle prestazioni della base dati.
Lo Statspack e' disponibile da molto tempo ma lo utilizzeremo su
RDS for Oracle che e' il servizio in Cloud di Amazon
che offre un database Oracle gestito.
Questo documento presenta diversi elementi: Introduzione, Configurazione, Utilizzo, Schedulazione, ...
Oracle Statspack consente la raccolta di statistiche prestazionali del database dalla versione 8.1.6 [NdA versione del secolo scorso... era il 1999]. L'uso dello statspack non e' cosi' conosciuto perche' la versione Oracle 10R2 ha introdotto l'AWR (Automatic Workload Repository) che viene utilizzato in forma grafica dall'EM (Enterprise Manager) risultando molto semplice da utilizzare e da comprendere. Tuttavia l'AWR e' una Oracle Option quindi richiede un'acquisto ed e' utilizabile solo con l'Enterprise Edition. Percio' vi sono diversi casi in cui l'utilizzo dello Statspack risulta molto utile. Anche su RDS perche', senza utilizzare la possibilita' del BYOL, generalmente per Oracle vengono utilizzate Standard Edition.
Il funzionamento dello Statspack e' semplice: vengono raccolte periodicamente snap (fotografie) dei contatori delle strutture di sistema della base dati e quindi gli snap vengono confrontati tra loro fornendo indicazioni quali il numero di letture su disco, gli statement piu' utilizzati, i tempi di attesa, ... tutte informazioni utilissime al DBA ed agli sviluppatori tecnicamente piu' preparati per effettuare il tuning della base dati e l'ottimizzazione degli statement SQL.
Anche se introdotto parecchio tempo fa lo Statspack puo' essere utilmente impiegato anche sulle piu' recenti configurazioni di Oracle su Amazon RDS affiancandosi ai sistemi di Monitoring.
La configurazione dello statspack su RDS for Oracle e' molto differente rispetto a quella di un'installazione on-premises... ma e' comunque semplice.
Creiamo un nuovo Option Group:
Aggiungiamo l'opzione STATSPACK:
Assegnamo l'Option Group al nostro database ed attendiamo che venga attivato.
Non e' necessario alcun riavvio e la sincronizzazione avviene in pochi minuti:
L'ultimo passaggio della configurazione prevede l'abilitazione dell'utente PERFSTAT ed il grant per la schedulazione delle attivita':
[NdA ovviamente non utilizzate xxx come password]
Gia' fatto! I passi di configurazione su AWS sono terminati e possiamo passare all'utilizzo.
Effettuata la configurazione e' possibile utilizzare immediatamente lo Statspack. La prima cosa da fare e' raccogliere gli snap:
Tipicamente gli snapshot vanno presi all'inizio ad al termine delle attivita'
che si vogliono controllare. Se una fase batch o un bechmark sono particolarmente
lunghi e' anche possibile raccogliere snap intermedi.
L'elenco degli snap raccolti si ottiene con questa query:
I report Statspack vengono eseguiti confrontando tra loro due snapshot
ed ottenendo i dati statistici sulle attivita' occorse nel frattempo.
Su RDS il comando per lanciare il report e':
Il risultato e' accessibile tra i file di log della base dati e puo' essere facilmente scaricato dalla console:
Il formato e' quello testuale classico dello script spreport di Statspack:
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2815999920 DEMO01 1 01-Apr-23 06:35 19.0.0.0.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
ip-172-13-13-269 Linux x86 64-bit 4 2 1 15.5
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 1 23-Oct-24 06:53:47 89 1.6
End Snap: 2 23-Oct-24 08:00:00 122 1.7
Elapsed: 66.22 (mins) Av Act Sess: 0.0
DB time: 3.18 (mins) DB CPU: 2.76 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 9,344M Std Block Size: 8K
Shared Pool: 2,240M Log Buffer: 23,192K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.1 0.0 0.00 0.00
DB CPU(s): 0.0 0.0 0.00 0.00
Redo size: 27,189.8 7,216.1
Logical reads: 619.1 164.3
Block changes: 111.3 29.5
Physical reads: 0.5 0.1
Physical writes: 3.5 0.9
User calls: 261.6 69.4
Parses: 120.7 32.0
Hard parses: 0.4 0.1
W/A MB processed: 0.6 0.2
Logons: 0.4 0.1
Executes: 158.9 42.2
Rollbacks: 0.1 0.0
Transactions: 3.8
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 100.00 Optimal W/A Exec %: 100.00
Library Hit %: 99.35 Soft Parse %: 99.70
Execute to Parse %: 24.07 Latch Hit %: 99.94
Parse CPU to Parse Elapsd %: 52.79 % Non-Parse CPU: 93.27
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.10 86.19
% SQL with executions>1: 68.28 76.26
% Memory for SQL w/exec>1: 78.15 87.19
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 99 56.0
log file sync 17,454 27 2 15.5
log file parallel write 22,908 25 1 14.4
control file sequential read 17,639 10 1 5.9
control file parallel write 3,850 4 1 2.2
-------------------------------------------------------------
Host CPU (CPUs: 4 Cores: 2 Sockets: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.00 0.05 0.76 0.56 97.71 0.06
...
Le prime sezioni del report riportano dati generali sull'istanza ed e' relativamente semplice ottenere indicazioni su eventuali bottleneck e criticita'. Le sezioni successive entrano in dettaglio sugli statement SQL piu' pesanti ordinandoli su diverse metriche... ma entrare nel dettaglio dei report di Statspack richiede piu' spazio di quello disponibile in questa paginetta.
Su web sono disponibili innumerevoli documenti che spiegano come analizzare il report di Statspack... tra cui uno dei primi White Paper ufficiali.
Per poter eseguire un'analisi sulle attivita' svolte sulla base dati in precedenza alla segnalazione del problema e' necessario raccogliere in modo periodico gli snapshot. Spesso vengono utilizzati strumenti di schedulazione presenti sui sistemi ospite come il crontab. Ma con RDS non e' possibile perche' non si ha accesso alla VM ospite. Va quindi utilizzato uno strumento interno alla base dati.
Nell'esempio che segue le statistiche vengono raccolte ogni due ore in orario lavorativo. E' una configurazione minimale ma utile anche per mostrare la flessibilita' del package dbms_scheduler:
begin
dbms_scheduler.create_job(job_name => 'statspack_schedule',
job_type => 'PLSQL_BLOCK',
job_action => 'perfstat.statspack.snap(i_snap_level=>5);',
start_date => systimestamp,
repeat_interval => 'freq=hourly; byhour=8,10,12,14,16,18; byminute=0; bysecond=0;',
enabled => true,
auto_drop => false,
comments => 'Office hours statspack schedule');
end;
Altrettanto importante e' ripulire le statistiche raccolte. Con la schedulazione che segue viene mantenuto l'ultimo mese:
begin
dbms_scheduler.create_job(job_name => 'statspack_purge',
job_type => 'PLSQL_BLOCK',
job_action => 'perfstat.statspack.purge(I_NUM_DAYS => 32, I_EXTENDED_PURGE => TRUE);',
start_date => systimestamp,
repeat_interval => 'FREQ=DAILY; byhour=1; byminute=0; bysecond=0;',
enabled => true,
auto_drop => false,
comments => 'Purge statspack records');
end;
La distanza in tempo tra gli snap e' molto importante. Quale esempio in AWR il default e' uno snapshot ogni ora con una ritenzione di 8 giorni, sicuramente e' una scelta ragionevole. Per disporre di una granularita' maggiore si possono campionare i dati ogni 15 minuti, generalmente non e' opportuna una frequenza maggiore se necessario possono essere eseguiti snap manuali.
Disporre di una baseline e' molto importante. A volte per analizzare un problema e' piu' utile disporre degli snapshot eseguiti a sistema funzionante che di quelli eseguiti durante la problematica.
Il livello di dettaglio delle statistiche raccolte da uno snap e' configurabile utilizzando il parametro i_snap_level:
I livelli utilizzabili sono riassunti nella seguente tabella:
| Livello | Descrizione | Da versione | Note |
| 0 | General performance statistics | 8i | |
| 5 | SQL Statements | 8i | Default |
| 6 | SQL Plans | 9i | |
| 7 | Segment statistics | 9.2 | |
| 10 | Parent and Child latches | 8i |
I livelli superiori raccolgono le statistiche di tutti i livelli inferiori.
L'elenco completo dei parametri che e' possibile utilizzare e' il seguente:
Parameter Name Range Default Note ------------------ ------------ ------- ----------------------------------- i_snap_level 0,5,6,7,10 5 Snapshot Level i_ucomment Text Blank Comment to be stored with Snapshot i_executions_th Integer >=0 100 SQL Threshold: number of times the statement was executed i_disk_reads_th Integer >=0 1000 SQL Threshold: number of disk reads the statement made i_parse_calls_th Integer >=0 1000 SQL Threshold: number of parse calls the statement made i_buffer_gets_th Integer >=0 10000 SQL Threshold: number of buffer gets the statement made i_sharable_mem_th Integer >=0 1048576 SQL Threshold: amount of sharable memory i_version_count_th Integer >=0 20 SQL Threshold: number of versions of a SQL statement i_seg_phy_reads_th Integer >=0 1000 Segment statistic Threshold: number of physical reads on a segment. i_seg_log_reads_th Integer >=0 10000 Segment statistic Threshold: number of logical reads on a segment. i_seg_buff_busy_th Integer >=0 100 Segment statistic Threshold: number of buffer busy waits for a segment. i_seg_rowlock_w_th Integer >=0 100 Segment statistic Threshold: number of row lock waits for a segment. i_seg_itl_waits_th Integer >=0 100 Segment statistic Threshold: number of ITL waits for a segment. i_seg_cr_bks_sd_th Integer >=0 1000 Segment statistic Threshold: number of Consistent Reads blocks served by the instance for the segment. i_seg_cu_bks_sd_th Integer >=0 1000 Segment statistic Threshold: number of Current blocks served by the instance for the segment. i_session_id sid 0 Session Id of the Oracle Session to capture session granular statistics for i_modify_parameter True,False False Save the parameters specified for future snapshots?
Confrontare snap presi a grande distanza di tempo generalmente non fornisce alcuna indicazione utile. Non e' possibile eseguire un report su snap tra cui e' avvenuto un restart dell'istanza del database.
Se gli oggetti o gli utenti che costituiscono lo statspack sono gia' presenti sull'istanza Oracle RDS allora la procedura di configurazione descritta puo' presentare degli errori. Inoltre con alcune versioni puo' presentarsi il bug 28523746 che non fa caclolare correttamente i tempi di CPU. La documentazione ufficiale Amazon riportata tutte le indicazioni ed i work-araound da applicare.
Per conoscere le versioni di Oracle e dei servizi su Amazon RDS... seguite i link!
E' disponibile un'ampia documentazione su Statspack sul sito di supporto di Oracle. Tra i principali documenti: FAQ- Statspack Complete Reference (Doc ID 94224.1), Installing and Configuring StatsPack Package (Doc ID 149113.1), AWR Reporting - Licensing Requirements Clarification (Doc ID 1490798.1), Statistics Package (STATSPACK) Guide (Doc ID 394937.1). Per accedere al sito di supporto Oracle e' necessario di disporre di un'utenza autorizzata.
Titolo: Statspack su RDS for Oracle
Livello: Avanzato
Data:
14 Febbraio 2024 ❤️
Versione: 1.0.1 - 31 Ottobre 2024 🎃
Autori: mail [AT] meo.bogliolo.name