Oracle 11g DBA
Performance Tuning
Distribuito tramite il sito www.manualioracle.it
Copyright © 2012 Assi Loris
Qualsiasi abuso sarà perseguito e punito secondo i termini di legge.
Version: 6.2
Alcuni termini usati sono trademarks registrati dei rispettivi proprietari.
11g__Performance_Tuning
Sommario
Introduzione al Manuale ............................................................................................................................................... 4
Cap. 1 – INTRODUZIONE al PERFORMANCE TUNING .................................................................................... 5
1.1 – Primi Concetti riguardo al Tuning Oracle .................................................................................................... 5
1.2 – Review dell’Architettura Oracle.................................................................. Error! Bookmark not defined.
1.3 – Strutture di memoria Oracle ........................................................................ Error! Bookmark not defined.
Cap. 2 – FONTI della TUNING INFORMATION (1^ parte)....................................... Error! Bookmark not defined.
2.1 – L’Automatic Diagnostic Repository (ADR)................................................................................................. 6
2.2 – Alert e Trace Files ....................................................................................... Error! Bookmark not defined.
2.3 – Performance Tuning Views ......................................................................... Error! Bookmark not defined.
Cap. 3 – FONTI della TUNING INFORMATION (2^ parte)....................................... Error! Bookmark not defined.
3.1 – Collecting Performance Statistics (AWR and ASH) .................................................................................... 7
3.2 – Come usare l’AWR ..................................................................................... Error! Bookmark not defined.
3.3 – Gli AWR baseline template ......................................................................... Error! Bookmark not defined.
3.4 – Settare le metric Thresholds per le AWR baseline ...................................... Error! Bookmark not defined.
3.5 – Diagnosing Performance Statistics: ADDM ................................................ Error! Bookmark not defined.
3.6 – Diagnosing Performance Statistics: Server-generated alerts ....................... Error! Bookmark not defined.
3.7 – Come usare l’ ADDM ................................................................................. Error! Bookmark not defined.
3.8 – Le directives dell’ADDM ............................................................................ Error! Bookmark not defined.
3.9 – Automated Maintenance Tasks ................................................................... Error! Bookmark not defined.
Cap. 4 – SQL Tuning ........................................................................................................ Error! Bookmark not defined.
4.1 – Il TKPROF : Introduzione ............................................................................................................................ 8
4.2 – L’ Explain Plan : Introduzione .................................................................... Error! Bookmark not defined.
4.3 – L’ Explain Plan : Come interpretarlo .......................................................... Error! Bookmark not defined.
4.4 – L’ Optimizer ................................................................................................ Error! Bookmark not defined.
4.5 – Il parametro CURSOR_SHARING ............................................................. Error! Bookmark not defined.
Cap. 5 – Tuning della SHARED Pool .............................................................................. Error! Bookmark not defined.
5.1 – Hard Parse e Soft Parse ............................................................................... Error! Bookmark not defined.
5.2 – La Shared Pool ............................................................................................ Error! Bookmark not defined.
5.3 – Misurare le Performance della Shared Pool ................................................ Error! Bookmark not defined.
5.4 – Migliorare le Performance della Shared Pool.............................................. Error! Bookmark not defined.
5.5 – Query Result Cache ..................................................................................... Error! Bookmark not defined.
5.6 – OCI Client Query Cache ............................................................................. Error! Bookmark not defined.
5.7 – PL/SQL Result Cache ................................................................................. Error! Bookmark not defined.
Cap. 6 – Tuning della Database Buffer Cache ................................................................ Error! Bookmark not defined.
6.1 – La DB Buffer Cache .................................................................................... Error! Bookmark not defined.
6.2 – Misurare le Performance della DB Buffer Cache ........................................ Error! Bookmark not defined.
6.3 – Migliorare le Performance della DB Buffer Cache ..................................... Error! Bookmark not defined.
6.4 – Setup dell’Automatic Memory Management (AMM) ................................. Error! Bookmark not defined.
Cap. 7 – Tuning del Redo Log Buffer .............................................................................. Error! Bookmark not defined.
7.1 – Il Redo Log Buffer ...................................................................................... Error! Bookmark not defined.
7.2 – Misurare le Performance del Redo Log Buffer ........................................... Error! Bookmark not defined.
7.3 – Migliorare le Performance del Redo Log Buffer ......................................... Error! Bookmark not defined.
Cap. 8 – Tuning dell’ I/O Fisico ....................................................................................... Error! Bookmark not defined.
8.1 – I/O Operations ............................................................................................. Error! Bookmark not defined.
8.2 – Introduzione alle I/O calibrations ................................................................ Error! Bookmark not defined.
8.3 – Performance Tuning dell’I/O sul Datafile ................................................... Error! Bookmark not defined.
8.4 – Tuning del DBW0 ....................................................................................... Error! Bookmark not defined.
8.5 – Tuning del Segment Block I/O : Automatic Segment-Space Management (ASSM) Error! Bookmark not
defined.
8.6 – Tuning del Segment Block I/O : altre considerazioni ................................. Error! Bookmark not defined.
8.7 – Tuning dei Redo Log, LGWR I/O, Archiving e ARC0 ............................... Error! Bookmark not defined.
8.8 – Tuning del Sort I/O ..................................................................................... Error! Bookmark not defined.
8.9 – Tuning del Rollback Segment I/O ............................................................... Error! Bookmark not defined.
Cap. 9 – Tuning Contention ............................................................................................. Error! Bookmark not defined.
9.1 – Latch Contention ......................................................................................... Error! Bookmark not defined.
9.2 – DML e DDL Lock Contention .................................................................... Error! Bookmark not defined.
9.3 – Waits relativi alla gestione dei Cursori (mutex, pin S wait on X, ecc…) .... Error! Bookmark not defined.
9.4 – Free List Contention .................................................................................... Error! Bookmark not defined.
2
www.manualioracle.it
11g__Performance_Tuning
9.5 – Parametro DDL_LOCK_TIMEOUT........................................................... Error! Bookmark not defined.
Cap. 10 – Raccolta Statistiche .......................................................................................... Error! Bookmark not defined.
10.1 – Statistiche .................................................................................................. Error! Bookmark not defined.
10.2 – DBMS_STATS ......................................................................................... Error! Bookmark not defined.
10.3 – Raccolta automatica delle Optimizer Statistics ......................................... Error! Bookmark not defined.
10.4 – Raccolta delle Data Dictionary statistics ................................................... Error! Bookmark not defined.
10.5 – Raccolta delle System statistics ................................................................. Error! Bookmark not defined.
Cap. 11 – Space Management .......................................................................................... Error! Bookmark not defined.
11.1 – Segment Shrink ......................................................................................... Error! Bookmark not defined.
11.2 – Segment Advisor ....................................................................................... Error! Bookmark not defined.
11.3 – Undo Advisor and Redo Logfile Size Advisor .......................................... Error! Bookmark not defined.
Cap. 12 – SQL Plans Baseline .......................................................................................... Error! Bookmark not defined.
12.1 – Architettura dell’SQL Plan Baseline ......................................................... Error! Bookmark not defined.
12.2 – Settare un SQL Plan Baseline.................................................................... Error! Bookmark not defined.
12.3 – I SQL Plan Attributes ................................................................................ Error! Bookmark not defined.
12.4 – Evoluzione dell’SQL Baseline .................................................................. Error! Bookmark not defined.
12.5 – Esempi ....................................................................................................... Error! Bookmark not defined.
12.6 – Quando usare un SQL Plan Baseline ......................................................... Error! Bookmark not defined.
12.7 – Gestione dello Spazio nell’SQL Management Base (SMB) ...................... Error! Bookmark not defined.
Cap. 13 – Real Application Testing (RAT): Database Replay (DBR) ........................... Error! Bookmark not defined.
13.1 – Overview del Workload Capture and Replay ............................................ Error! Bookmark not defined.
13.2 – Workload Capture dal Sistema Source ...................................................... Error! Bookmark not defined.
13.3 – Workload PreProcessing, Replay and Report ............................................ Error! Bookmark not defined.
13.4 – Viste di dizionario relative alla Database Replay ...................................... Error! Bookmark not defined.
Cap. 14 – SQL Performance Analyzer (SPA) ................................................................ Error! Bookmark not defined.
14.1 – Overview dell’SQL Performance Analyzer .............................................. Error! Bookmark not defined.
14.2 – Uso dell’SQL Performance Analyzer ........................................................ Error! Bookmark not defined.
Cap. 15 – Miscellaneous .................................................................................................... Error! Bookmark not defined.
15.1 – Introduzione al Partizionamento................................................................ Error! Bookmark not defined.
15.2 – Index monitoring e Table monitoring ........................................................ Error! Bookmark not defined.
15.3 – Oracle Database Resource Manager .......................................................... Error! Bookmark not defined.
15.4 – Introduzione agli Advisor .......................................................................... Error! Bookmark not defined.
15.5 – Il CONTROL_MANAGEMENT_PACK_ACCESS Initialization Parameter ........ Error! Bookmark not
defined.
www.manualioracle.it
3
11g__Performance_Tuning
Introduzione al Manuale
Contenuto
Il presente manuale tratta il Performance & Tuning in Oracle 11.2.
Audience
Il presente manuale è rivolto a chiunque voglia avere una conoscenza specifica del tema di Performance & Tuning
(dunque le tecniche per velocizzare un db, un codice SQL e un workload in Oracle), avendo già una conoscenza base
dell’architettura di Oracle 11g.
Particolarità
Il presente manuale è in italiano ma molti termini tecnici in esso contenuti sono in lingua inglese.
Abbiamo fatto tale scelta perché spesso a lavoro tali termini vengono detti in inglese e perché riteniamo più utile che
i lettori del manuale conoscano tali termini nella lingua usata nella documentazione ufficiale Oracle.
Molti argomenti sono schematizzati al fine di fornire una comprensione ed una memorizzazione superiore.
A volte quando nel manuale si parla di una feature particolare, citiamo anche la versione Oracle in cui tale feature è
stata creata.
Alcune abbreviazioni usate:
!!! : Attenzione
CBO : Cost-Based Optimizer
FTS : Full Table Scan
HW : hardware
HWM : High Water Mark
OS o O.S.: Sistema Operativo
SW : software
TAF : Table Access Full
db : database
init parameter : initialization parameter (contenuto nell’init file o nell’spfile)
loccare : mettere un lock
stats : statistiche
tunare : fare il tuning
Principali Versioni
-
02.10.2012 : version 1.0
03.10.2012 : version 1.1 varie modifiche e rilascio al pubblico
15.02.2015 : version 3.0 varie modifiche
24.11.2015 : version 5.2 varie modifiche
09.03.2017 : version 6.0 varie modifiche
Disclaimer
Non si fornisce alcuna garanzia relativamente al fatto che il presente documento sia privo di errori. Non si assume
nessuna responsabilità sugli eventuali errori o danni derivanti dall’uso delle informazioni qui contenute.
4
www.manualioracle.it
11g__Performance_Tuning
Cap. 1 – INTRODUZIONE al PERFORMANCE TUNING
1.1 – Primi Concetti riguardo al Tuning Oracle
Per riuscire a fare il tuning di un db bisogna capire come Oracle processa gli SQL statement e come interagisce con
il Sistema Operativo e con l’Hardware del server.
Quando bisogna ottimizzare un sistema, bisognerebbe seguire questo ordine di priorità:
Step 1: Tuning del Data Design (ossia fare un corretto disegno del db)
Step 2: Tuning dell’Application (db operations e access path: ad es. vanno evitati grossi Full Table Scan)
Step 3: Tuning della Memoria (shared pool, db buffer cache, redo log buffer, pga, …)
Step 4: Tuning dell’I/O e delle Strutture fisiche
Step 5: Tuning della contesa
Step 6: Tuning del Sistema Operativo
Gli step che vanno fra il 2 e il 6 saranno ampiamente discussi in questo manuale. Mentre riguardo al Data Design
(step 1) possiamo dire che se un db è disegnato nel modo opportuno:
- potrà contenere tante informazioni pur non avendo un grosso Hardware (si parla di db con dati “normalizzati”)
- potrà venire aggiornato in modo semplice e veloce
- potrà restituire i dati efficientemente (anche aiutandosi con tabelle “denormalizzate”)
Dunque per poter fare un buon disegno del db bisogna utilizzare adeguatamente i concetti di normalizzazione e
denomalizzazione.
Nell’ultimo capitolo faremo dei cenni su tali concetti.
Ogni Server Oracle è limitato dalla disponibilità di 3 risorse chiave:
-
CPU:
Il tuning della Oracle memory e della I/O activity darà pochi benefit se il processore del server è già sovraccarico
(overburdened).
..............
..............
..............
www.manualioracle.it
5
11g__Performance_Tuning
Cap. 2 – FONTI della TUNING INFORMATION (1^ parte)
2.1 – L’Automatic Diagnostic Repository (ADR)
Fault Diagnosability Infrastructure
La Fault Diagnosability Infrastructure in Oracle 11g trova proattivamente i problemi e permette una diagnosi e una
risoluzione veloce degli errori.
Quando un errore critico viene rilevato sul db, l’errore dà un numero e tutti i dati relativi a questo errore vengono
scritti nei trace files.
Questi trace files sono contenuti nell’Automatic Diagnostic Repository (ADR), che è un repository file-based posto
sul server.
Locations dei File della Fault Diagnosability Infrastructure
La root directory dell’ ADR è conosciuta come ADR base ed è la base per tutte le ADR homes.
Una ADR base può contenere varie ADR homes e ognuna di esse corrisponde ad una istanza diversa.
Ecco lo schema di una struttura ADR in un server Oracle:
..............
..............
..............
6
www.manualioracle.it
11g__Performance_Tuning
Cap. 3 – FONTI della TUNING INFORMATION (2^ parte)
3.1 – Collecting Performance Statistics (AWR and ASH)
Oracle 10g ha migliorato l’Automatic Statistics Collection feature per collezionare varie informazioni riguardanti
l’O.S. e il db introducendo un nuovo set di programmi chiamato Automatic Workload Repository (AWR).
L’AWR fa uno snapshot del db negli intervalli specificati e li contiene nella SYSAUX tablespace.
Nelle precedenti versioni Oracle al posto dell’AWR esisteva lo statspack (era più semplice e non automatico).
L’AWR è l’elemento centrale della Common Manageability Infrastructure (CMI).
Ecco i componenti dell’architettura CMI:
- l’AWR e l’ASH
- Automated Tasks
- Server Alerts
- Advisory Framework
L’AWR potrebbe avere informazioni non aggiornate visto che è popolata in set intervals.
Per diagnosi correnti, si può usare l’Active Sessions History (ASH).
Usare l’Automatic Workload Repository (AWR)
Di default, l’AWR raccoglie statistiche ogni ora e le conserva per una settimana.
..............
..............
..............
www.manualioracle.it
7
11g__Performance_Tuning
Cap. 4 – SQL Tuning
Un tuning appropriato dell’applicazione dipende molto dal tipo di sistema in cui si è: gli OnLine Transactional
Processing systems (OLTP) hanno differenti SQL e design considerations rispetto ai data warehouse (DWH).
Senza preoccuparti del tipo di sistema, la prima cosa da fare è misurare le performance degli SQL in esecuzione e
capire le tuning options disponibili.
Il metodo più usato per misurare le performance degli SQL statement è Explain Plan tramite il DBMS_XPLAN,
l’AUTOTRACE, il TKPROF utility o l’AWR SQL report.
4.1 – Il TKPROF : Introduzione
Il Trace Kernel Profile, o TKPROF, è usato per formattare gli user trace files che sono generati dalle sessioni messe
in trace.
Gli user trace file contengono, in formato raw, tutti gli SQL che l’utente ha fatto mentre il trace era attivo,
informazioni sul tempo necessario ad eseguire ogni statement e la quantità di risorse che gli statements hanno
consumato.
Visto che il contenuto di un file raw è difficilmente interpretabile, il TKPROF converte il trace file in un formato
facilmente interpretabile.
Vediamo un esempio in Unix:
$ tkprof ora_12558.trc trace.txt
Questo es. formatta uno user trace file chiamato ora_12558.trc e mette la sua versione formattata nel file chiamato
trace.txt.
Il TKPROF ha molti altri argomenti che possono essere usati a seconda delle informazioni che si desidera avere.
Queste opzioni vengono specificate nella linea di comando subito dopo i 2 parametri già visti.
Sintassi:
..............
..............
..............
8
www.manualioracle.it
