Oracle utilizza un ottimizzatore SQL molto sofisticato e potente. Questa breve paginetta riporta alcune indicazioni per sfruttarlo al meglio.
Avevo gia' scritto alcuni documenti sulle novita' dell'ottimizzatore Oracle [NdE novita' della versione 7.3!] e sui principali strumenti di verifica delle prestazioni. Anche se i concetti di base erano sempre gli stessi, sono anche passati quasi 20 anni quindi ho ritenuto opportuno scrivere questo aggiornamento allineato alla versione 11g R2!
L'SQL e' un linguaggio di quarta generazione,
ovvero consente di dire cosa si vuole
ma non come trovarlo.
Gli statement SQL vengono parsificati
(che e' una semplice verifica sintattica e sull'accesso ai dati)
e quindi vengono analizzati dall'optimizer. L'optimizer ha il compito
di decidere quale sia i modo migliore per estrarre i dati richiesti.
L'optimizer Oracle e' cost based, ovvero utilizza statistiche sofisticate sulle
tabelle e sugli indici, ed elaborare un plan (piano di esecuzione) ottimizzato
per l'SQL utilizzato.
Tutto questo vale in modo praticamente identico da oltre 20 anni,
dalla versione 7 di Oracle all'attuale 12c.
Ma in realta' di cose in vent'anni ne sono cambiate parecchie.
Per raccontarle tutte servirebbe un'enciclopedia...
quindi raccontero' solo il poco che mi interessa e che conosco!
Nel bagaglio di esperienze di un DBA Oracle questi tool sono sempre presenti: EXPLAIN PLAN, TKPROF, HINT, ANALYZE!
L'explain plan lo diamo per scontato:
SYS@demo > explain plan for select 1 from dual;
Explained.
SYS@demo > SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
SYSTEM = optimizer_features_enable='11.2.0.3'
SESSION = alter session set optimizer_features_enable='11.2.0.3';
SQL = select /*+ optimizer_features_enable('9.2.0') */
Sul TKPROF non ci sono novita' di rilievo...
Gli HINT sono sempre di piu'. Ovviamente sono una feature non standard di Oracle... ma anche altri RDBMS li stanno imitando.
L'Enterprise Manager fornisce un fracco di informazioni con un'interfaccia grafica semplice. Occorre solo fare attenzione al licensing! [NdA eventualmente impostare il parametro CONTROL_MANAGEMENT_PACK_ACCESS]
I possibili interventi sull'ottimizzatore sono innumerevoli:
select optimizer_feature_enable, count(*) from v$system_fix_control group by optimizer_feature_enable OPTIMIZER_FEATURE_ENABLE COUNT(*) ------------------------- ---------- 8.0.0 85 8.1.6 1 8.1.7 2 9.2.0 8 9.2.0.8 3 10.1.0 3 10.1.0.3 1 10.1.0.4 1 10.1.0.5 2 10.2.0.1 9 10.2.0.2 12 10.2.0.3 11 10.2.0.4 73 10.2.0.5 111 11.1.0.6 41 11.1.0.7 19 11.2.0.1 68 11.2.0.2 107 11.2.0.3 82 11.2.0.4 141 65
Per tracciare il comportamento di uno statement SQL sono possibili diverse alternative a seconda delle versioni come ben descritto in questa paginetta di oracle-base (EVENT 10046) e nella nota Oracle How to Obtain Tracing of Optimizer Computations [ID 225598.1] (EVENT 10053).
Da ultimo, ma non per importanza, e' necessario ricordare l'ANALYZE: quanti problemi di tuning ha risolto il semplice lancio delle statistiche!
SQLT (SQLTXPLAIN) e' a mio avviso lo strumento migliore per analizzare gli statement SQL. E' un tool sviluppato individualmente e poi adottato ufficialmente da Oracle. L'installazione e la configurazione sono banali e descritte in modo completo nella documentazione ufficiale [NdA DocId 215187.1].
L'analisi che SQLT effettua e' molto completa e consente di disporre di tutti gli elementi per ottimizzare una query. SQLT puo' essere lanciato con script diversi, con un grado decrescente di funzionalita', a seconda che lo statement SQL sia gia' stato eseguito o meno:
START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] START sqltxecute.sql script [sqltxplain_password] START sqltxplain.sql script sqltxplain_passwordDa notare l'utility coe_xfr_profile.sql che genera ed importa l'SQL Profile scelto tra quelli analizzati. E' anche possibile utilizzare alcuni trucchi come modificare il force_match o analizzare uno statement con HINT e scambiare i profili... potentissimo!
Un'ultima nota. L'utilizzo di SQLT Profile non richiede le Option DIAGNOSTICS e TUNING pack ma, se assenti, opera in modalita' molto, molto ridotta. coe_xfr_profile richiede il TUNING pack.
Il tuning di uno statement SQL puo' richiedere molto tempo e richiede una notevole esperienza... Ma seguendo il seguente esempio e' possibile automatizzare le attivita' di tuning utilizzando un tuning task:
spool analisi_tuning
set lines 180
set long 1000000
SET LONGCHUNKSIZE 1000000
variable report_out clob;
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'xxxyyyzzz');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => stmt_task );
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( stmt_task ) into :report_out FROM dual;
end;
/
print report_out;
spool off
In pratica basta disporre dell'SQL Id dello statement per far provare ad Oracle.
Lo script effettua il tuning dello statement e restituisce un report con le diverse alternative di plan.
Le indicazioni possono essere di diverso tipo:
raccogliere statistiche, creare indici,
applicare un profilo per far utilizzare un execution plan differente alla query, ...
e debbono essere interpretate con attenzione.
Se si ritiene valida una delle indicazioni emerse basta accettarla come indicato
nel report. Ad esempio:
begin DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_6969', task_owner => 'SYSTEM', replace => TRUE); end; /Da questo momento e' attivo un nuovo SQL Profile che guida l'ottimizzatore per lo statement analizzato. La verifica dei profile presenti si effettua con:
select * from dba_sql_profiles;
Per disabilitare un profilo si utilizza:
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/
Per controllare quali statement utilizzano un profile:
set lines 132 column sql_text format a130 select sql_id, child_number, plan_hash_value, sql_profile, EXECUTIONS, ELAPSED_TIME, BUFFER_GETS, sql_text from v$sql where sql_profile is not null;
Un'ultima nota. L'utilizzo degli SQL Profile richiede le Option DIAGNOSTICS e TUNING pack.
alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true sid='*';
spool plan_table
SELECT PLAN_TABLE_OUTPUT
FROM V$SQL s, DBA_SQL_PLAN_BASELINES b,
TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')
) t
WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND s.
alter session set current_schema=them;
L'utilizzo di una baseline SPM richiede l'Edition Enterprise ma non e' necessaria nessuna Option.
DBMS_XPLAN
DECLARE
cl_sql_text CLOB;
BEGIN
SELECT sql_text INTO cl_sql_text from gv$sqlarea where sql_text like 'SELECT name FROM sql_prof_sample_1 t1 JOIN sql_prof_sample_2 t2 ON t1.type_id = t2.type_id WHERE t2.type_name%';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => cl_sql_text, profile => sqlprof_attr('INDEX(@"SEL$1" "T1"@"SEL$1" SQL_PROF_SAMPLE_1_I)'), name => 'SQL_PROF_SAMPLE', force_match => TRUE);
end;
/
Le informazioni di dettaglio sugli statement SQL si possono raccogliere da viste diverse.
V$SQL ha una riga con le statistiche di ogni esecuzione
mentre V$SQLAREA riporta le statistiche in modo aggregato per ogni statement SQL.
La vista V$SQLSTATS e' stata introdotta nella 10g R2, e' piu' efficiente della
V$SQLAREA ed ha una ritenzione maggiore.
Le tre viste 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 [NdA detti anche 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
richiedendo un numero di lock inferiore: in pratica risulta tipicamente molto piu' veloce.
Nella vista V$SQLSTATS e' disponibile l'intero statement SQL
ma sono disponibili nettamente meno colonne (eg. PARSING_SCHEMA_NAME).
Con Oracle RAC tipicamente si utilizzano le viste GV$ che anologhe alle viste V$
ma riportano dati globali per l'intero cluster.
Altre viste utili sono V$SESSION, V$SQL_PLAN, V$SQLTEXT, ...
Pur mantenendo la stessa logica sono significative le differenze tra le versioni di Oracle.
Titolo: SQL tuning in Oracle 11gR2
Livello: Avanzato
Data: 1 Aprile 2014
Versione: 1.0.2 -
1 Aprile 2017
Autore:
mail [AT] meo.bogliolo.name