Anteprima del Manuale

Oracle 11g DBA
Performance Tuning Applicativo
e Tuning dell’SQL
Distribuito tramite il sito www.manualioracle.it
Copyright © 2015 Assi Loris
Qualsiasi abuso sarà perseguito e punito secondo i termini di legge.
Version: 4.2
Alcuni termini usati sono trademarks registrati dei rispettivi proprietari.
11g__Performance_Tuning_Applicativo
Sommario
Introduzione al Manuale ........................................................................................................................................ 4
Cap. 1 - Oracle db ................................................................................................................................................ 5
1.1 – Review dell’Architettura Oracle ...................................................................................................................5
1.2 – Approccio DTAP nei database .................................................................... Error! Bookmark not defined.
Cap. 2 - Introduzione al Performance Tuning ..................................................... Error! Bookmark not defined.
2.1 – Primi Concetti riguardo al Tuning Oracle .....................................................................................................6
2.2 – DWH, OLAP, OLTP e concetti di Normalizzazione .................................. Error! Bookmark not defined.
2.3 – Ulteriori dettagli sui Data Warehouse (DWH) ............................................ Error! Bookmark not defined.
2.4 – Performance Tuning Views ......................................................................... Error! Bookmark not defined.
2.5 – Il flusso di Esecuzione di un SQL ............................................................... Error! Bookmark not defined.
2.6 – Hard Parse e Soft Parse ............................................................................... Error! Bookmark not defined.
Cap. 3 - Il Query Optimizer ................................................................................... Error! Bookmark not defined.
3.1 – Optimizer e Optimizer Operations ..............................................................................................................7
3.2 – Componenti dell’Ottimizzatore ................................................................... Error! Bookmark not defined.
3.3 – Optimizer Goal ............................................................................................ Error! Bookmark not defined.
Cap. 4 - SQL Tuning : l’Explain Plan ................................................................... Error! Bookmark not defined.
4.1 – L’ Explain Plan : Introduzione ......................................................................................................................8
4.2 – Il parametro CURSOR_SHARING ............................................................. Error! Bookmark not defined.
4.3 – Adaptive Cursor Sharing (ACS) .................................................................. Error! Bookmark not defined.
4.4 – Come interpretare un piano di accesso di queries su una tabella non partizionata (senza WHERE
condition) ............................................................................................................. Error! Bookmark not defined.
4.5 – Piani di accesso di queries su una tabella non partizionata (con WHERE condition) Error! Bookmark not
defined.
4.6 – Altri piani di accesso su una tabella non partizionata.................................. Error! Bookmark not defined.
4.7 – Piani di accesso di queries su una tabella partizionata (non “composite partitioned”) ..... Error! Bookmark
not defined.
4.8 – Piani di accesso di queries su una tabella sotto-partizionata ....................... Error! Bookmark not defined.
4.9 – Piani di accesso su Query con Join ............................................................. Error! Bookmark not defined.
Cap. 5 - SQL Tuning : altre considerazioni .......................................................... Error! Bookmark not defined.
5.1 – Il package DBMS_XPLAN ......................................................................... Error! Bookmark not defined.
5.2 – L’Autotrace ................................................................................................. Error! Bookmark not defined.
5.3 – Gli Hints ...................................................................................................... Error! Bookmark not defined.
5.4 – Esempi di uso degli Hints ............................................................................ Error! Bookmark not defined.
Cap. 6 - Raccolta Statistiche .................................................................................. Error! Bookmark not defined.
6.1 – Statistiche .................................................................................................... Error! Bookmark not defined.
6.2 – DBMS_STATS ........................................................................................... Error! Bookmark not defined.
6.3 – Vedere le statistiche di Tabelle e Indici ...................................................... Error! Bookmark not defined.
6.4 – Altre features sulla Raccolta delle Statistiche ............................................. Error! Bookmark not defined.
6.5 – Pending Statistics ........................................................................................ Error! Bookmark not defined.
6.6 – Multicolumn Statistics................................................................................. Error! Bookmark not defined.
Cap. 7 - Space Management................................................................................... Error! Bookmark not defined.
7.1 – Segment Shrink ........................................................................................... Error! Bookmark not defined.
7.2 – Segment Advisor ......................................................................................... Error! Bookmark not defined.
Cap. 8 - Application Design Performance ............................................................ Error! Bookmark not defined.
8.1 – Plan Stability ............................................................................................... Error! Bookmark not defined.
8.2 – Materialized Views ..................................................................................... Error! Bookmark not defined.
8.3 – Gestione e Tuning delle Materialized view ................................................. Error! Bookmark not defined.
Cap. 9 - Indici e Clusters ........................................................................................ Error! Bookmark not defined.
9.1 – Gli Indici ..................................................................................................... Error! Bookmark not defined.
9.2 – Indici Bitmap ............................................................................................... Error! Bookmark not defined.
9.3 – Le Index Organized Tables (IOTs).............................................................. Error! Bookmark not defined.
9.4 – Index Clusters e Hash Clusters .................................................................. Error! Bookmark not defined.
9.5 – L’index monitoring ..................................................................................... Error! Bookmark not defined.
9.6 – Create Invisible Indexes .............................................................................. Error! Bookmark not defined.
Cap. 10 SQL Plans Baseline .............................................................................. Error! Bookmark not defined.
10.1 – Architettura dell’SQL Plan Baseline ......................................................... Error! Bookmark not defined.
10.2 – Settare un SQL Plan Baseline ................................................................... Error! Bookmark not defined.
10.3 – I SQL Plan Attributes ................................................................................ Error! Bookmark not defined.
2
www.manualioracle.it
11g__Performance_Tuning_Applicativo
10.4 – Evoluzione dell’SQL Baseline .................................................................. Error! Bookmark not defined.
10.5 – Esempi ....................................................................................................... Error! Bookmark not defined.
10.6 – Quando usare un SQL Plan Baseline......................................................... Error! Bookmark not defined.
Cap. 11 Gli Advisor e l’Automatic SQL Tuning ............................................. Error! Bookmark not defined.
11.1 – Gli Advisor ................................................................................................ Error! Bookmark not defined.
11.2 – L’SQL Tuning Advisor (STA) .................................................................. Error! Bookmark not defined.
11.3 – Settare e Modificare l’Automatic SQL Tuning ......................................... Error! Bookmark not defined.
11.4 – Interpretare i Report generati dall’Automatic SQL Tuning ...................... Error! Bookmark not defined.
11.5 – L’SQL Access Advisor ............................................................................. Error! Bookmark not defined.
11.6 – SQL Access Advisor Analysis Session tramite PL/SQL ........................... Error! Bookmark not defined.
Cap. 12 Oracle Partitioning .............................................................................. Error! Bookmark not defined.
12.1 – Il Partizionamento ..................................................................................... Error! Bookmark not defined.
12.2 – Cenni su Partition Pruning e sul SottoPartizionamento ............................. Error! Bookmark not defined.
12.3 – System Views riguardanti il Partizionamento ........................................... Error! Bookmark not defined.
12.4 – Tipi di Partizionamento : Interval e System .............................................. Error! Bookmark not defined.
12.5 – Tipi di Partizionamento : Reference e Virtual column-based ................... Error! Bookmark not defined.
Cap. 13 Application Tracing Tools ................................................................... Error! Bookmark not defined.
13.1 – L’ End to End Application Tracing ........................................................... Error! Bookmark not defined.
13.2 – Trcsess Utility ........................................................................................... Error! Bookmark not defined.
13.3 – Event Tracing e User Trace File ................................................................ Error! Bookmark not defined.
13.4 – Il TKPROF : Introduzione ......................................................................... Error! Bookmark not defined.
13.5 – Il TKPROF : il Sort e ulteriori approfondimenti ....................................... Error! Bookmark not defined.
Cap. 14 AWR, ASH e ADDM ........................................................................... Error! Bookmark not defined.
14.1 – Collecting Performance Statistics (AWR and ASH) ................................. Error! Bookmark not defined.
14.2 – Come usare l’AWR ................................................................................... Error! Bookmark not defined.
14.3 – Gli AWR baseline template ....................................................................... Error! Bookmark not defined.
14.4 – Settare le metric Thresholds per le AWR baseline .................................... Error! Bookmark not defined.
14.5 – Diagnosing Performance Statistics: ADDM.............................................. Error! Bookmark not defined.
14.6 – Diagnosing Performance Statistics: Server-generated alerts ..................... Error! Bookmark not defined.
14.7 – Come usare l’ ADDM ............................................................................... Error! Bookmark not defined.
14.8 – Le directives dell’ADDM .......................................................................... Error! Bookmark not defined.
Cap. 15 Comandi SQL Avanzati....................................................................... Error! Bookmark not defined.
15.1 – SQL for Aggregation nei Data Warehouses .............................................. Error! Bookmark not defined.
15.2 – L’ISO/ANSI standard SQL: il CASE ........................................................ Error! Bookmark not defined.
15.3 – L’ISO/ANSI standard SQL: le Inner Join ................................................. Error! Bookmark not defined.
15.4 – L’ISO/ANSI standard SQL: Outer Join e Self Join ................................... Error! Bookmark not defined.
15.5 – Partitioned Outer Join................................................................................ Error! Bookmark not defined.
15.6 – Regular Expressions .................................................................................. Error! Bookmark not defined.
15.7 – La MODEL clause .................................................................................... Error! Bookmark not defined.
15.8 – Altri SQL Statement Performanti .............................................................. Error! Bookmark not defined.
15.9 – Il MERGE SQL statement ......................................................................... Error! Bookmark not defined.
15.10 – La WIDTH_BUCKET function .............................................................. Error! Bookmark not defined.
Cap. 16 Miscellaneous........................................................................................ Error! Bookmark not defined.
16.1 – DML e DDL Lock Contention .................................................................. Error! Bookmark not defined.
16.2 – Table Lock Mechanism ............................................................................. Error! Bookmark not defined.
16.3 – Parallelismo in Oracle 11.2 : Concetti ....................................................... Error! Bookmark not defined.
16.4 – Parallelismo in Oracle 11.2 : Schemi Riassuntivi ..................................... Error! Bookmark not defined.
www.manualioracle.it
3
11g__Performance_Tuning_Applicativo
Introduzione al Manuale
Contenuto
Il presente manuale tratta gli argomenti applicativi e di sviluppo (SQL e PL/SQL) del Performance & Tuning in
Oracle 11.2.
Audience
Il presente manuale è rivolto a chiunque voglia avere una conoscenza specifica del tema di Performance & Tuning
applicativo (dunque le tecniche per velocizzare 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
DOP : Degree Of Parallelism
FTS : Full Table Scan
HWM : High Water Mark
OS o O.S. : Sistema Operativo
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
- 03.04.2015 :
- 07.04.2015 :
- 09.12.2015 :
- 02.12.2016 :
- 08.03.2017 :
version 1.0
version 1.1 varie modifiche e rilascio al pubblico
version 2.1 varie modifiche
version 3.2 varie modifiche
version 4.2 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_Applicativo
Cap. 1 - Oracle db
1.1 – Review dell’Architettura Oracle
Oracle Server è costituito da 2 principali componenti:
-
L’istanza è costituita dalle strutture di memoria (SGA e PGA) e dai processi di background che gestiscono
il DB con compiti distinti e in modo asincrono.
Ogni DB ha almeno una istanza associata
-
Il database è usato per contenere e restituire dati. Ha 2 tipi di struttura:
o logica: è rappresentata dai componenti che si possono vedere nel database (come tabelle, indici,
ecc..)
o fisica: rappresenta il metodo di immagazzinamento che Oracle usa internamente (i file fisici)
Ecco una rappresentazione semplificata della struttura logica e di quella fisica di un database Oracle :
…………
…………
…………
www.manualioracle.it
5
11g__Performance_Tuning_Applicativo
Cap. 2 - Introduzione al Performance Tuning
2.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
In questo manuale discuteremo soprattutto dei vari aspetti legati allo step 2.
Riguardo gli step 3-6 suggeriamo di fare riferimento al manuale ad hoc (11g_Performance_Tuning), mentre riguardo
al Data Design (step 1) lo tratteremo in pochi paragrafi e possiamo già 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.
Nel prossimo paragrafo 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).
Oracle è un prodotto molto “CPU-aware” (conscio delle CPU): alcuni parametri della configurazione Oracle
cambiano dinamicamente quando si aggiungono o tolgono CPU dal server.
-
Disk (I/O):
…………
…………
…………
6
www.manualioracle.it
11g__Performance_Tuning_Applicativo
Cap. 3 - Il Query Optimizer
3.1 – Optimizer e Optimizer Operations
Il Query Optimizer (chiamato anche optimizer o ottimizzatore) è un software interno al db Oracle il cui scopo è
quello di determinare il modo più efficiente di eseguire una query.
Quando lanci una query, l’ottimizzatore sceglierà come farla eseguire velocemente: lo fa trovando il piano d’accesso
con il costo minore.
Il costo è un numero (senza una vera unità di misura) che indica quante risorse l’ottimizzatore si aspetta di utilizzare
per eseguire un SQL statement seguendo un certo piano di accesso.
In questo paragrafo dunque vediamo come l’ Optimizer sceglie uno specifico piano d’accesso per eseguire un SQL.
Optimizer Operations
Un SQL può essere eseguito accedendo ai dati in molti modi, come Table Access Full, index scans, ecc…
Quando lanci un SQL statement, l’ottimizzatore fa i seguenti steps:
1. Genera un set di potenziali plans per eseguire il SQL statement
2. Stima il costo di ogni plan basandosi sulle statistiche presenti nel dizionario dati.
In particolare, fra le statistiche l’ottimizzatore valuterà vari indicatori (numero di righe, distribuzione dei
dati, come sono fatti gli indici, ecc..) degli oggetti referenziati.
3. Compara i plan e sceglie quello con costo più basso.
Es.: supponiamo che per eseguire la query q1 l’ottimizzatore valuti 2 plan alternativi:
…………
…………
…………
www.manualioracle.it
7
11g__Performance_Tuning_Applicativo
Cap. 4 - SQL Tuning : l’Explain Plan
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 – L’ Explain Plan : Introduzione
L’explain plan è un modo per vedere gli execution plan (piani di esecuzione) scelti dall’optimizer per SELECT,
UPDATE, INSERT e DELETE statements.
Un execution plan è la sequenza di operazioni che il db fa per fare girare uno statement.
Guardando l’execution plan puoi cercare di migliorare le performance di un SQL statement.
Explain Plan nel TKPROF
Anche tramite il TKPROF puoi vedere degli explain plan di SQL statements.
Es. di report TKPROF che mostra un explain plan:
…………
…………
…………
8
www.manualioracle.it