Oracle utilizza un ottimizzatore SQL molto sofisticato e potente. Questa breve paginetta riporta le 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 65http://www.oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof.php
10053 [NdA DocId 225598.1]
Da ultimo, ma non per importanza, e' necessario ricordare l'ANALYZE: quanti problemi di tuning ha risolto!
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. 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;
Un'ultima nota. 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;
/
Titolo: SQL tuning in Oracle 11gR2
Livello: Avanzato
Data: 1 Aprile 2014
Versione: 1.0.1 -
1 Aprile 2014
Autore:
mail [AT] meo.bogliolo.name