Create Oracle SQL Profile For Tuning
L’ottimizzazione SQL automatica risolve questo problema con la sua capacità di profiling SQL. L’ottimizzazione della sintonizzazione automatica crea un profilo dell’istruzione SQL denominato profilo SQL, costituito da statistiche ausiliarie specifiche per tale istruzione.
Durante la creazione di profili SQL, l’ottimizzazione della sintonizzazione automatica utilizza anche le informazioni sulla cronologia di esecuzione dell’istruzione SQL per impostare in modo appropriato le impostazioni dei parametri dell’ottimizzatore, ad esempio la modifica dell’impostazione del parametro di inizializzazione OPTIMIZER_MODE da ALL_ROWS a FIRST_ROWS per tale istruzione SQL.
L’output di questo tipo di analisi è una raccomandazione per accettare il profilo SQL. Un profilo SQL, una volta accettato, viene memorizzato in modo persistente nel dizionario dei dati. Notare che il profilo SQL è specifico per una determinata query.
Se accettato, l’ottimizzatore in modalità normale utilizza le informazioni nel profilo SQL insieme alle normali statistiche del database durante la generazione di un piano di esecuzione. La disponibilità delle informazioni aggiuntive rende possibile produrre piani ben sintonizzati per la corrispondente istruzione SQL senza richiedere alcuna modifica al codice dell’applicazione.
È importante notare che il profilo SQL non congela il piano di esecuzione di un’istruzione SQL, come avviene per le strutture memorizzate. Man mano che le tabelle crescono o gli indici vengono creati o eliminati, il piano di esecuzione può cambiare con lo stesso profilo SQL.
Le informazioni in esso memorizzate continuano ad essere rilevanti anche se la distribuzione dei dati o il percorso di accesso dell’istruzione corrispondente cambia. Tuttavia, per un lungo periodo di tempo, il suo contenuto può diventare obsoleto e dovrebbe essere rigenerato. Questa operazione può essere eseguita eseguendo nuovamente l’ottimizzazione SQL automatica sulla stessa istruzione per rigenerare il profilo SQL.
Ecco il set di istruzioni SQL che puoi utilizzare per tracciare il tempo di esecuzione della query SQL in esecuzione ACTIVE che desideri ottimizzare.
— Ottieni SQL_ID dall’SQL della sessione attiva
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = ‘ACTIVE’
AND a.spid = ‘11696’
ORDER BY a.spid, c.piece
— Traccia il tempo di esecuzione della query SQL utilizzando l’ID SQL
SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
FROM v$sql s WHERE s.sql_id=’4n01r8z5hgfru’
—Aggiungi il suggerimento / * + gather_table_statistics * / nell’istruzione SQL ed esegui
SELECT /* + gather_table_statistics */ sysdate … (SQL Statement)
Nota: L’esecuzione dell’istruzione SQL WITH hint / * + gather_table_statistics * / fornirà successivamente l’esecuzione del piano di dettaglio TIME IN nella query NEXT di seguito.
— Ottieni il piano di spiegazione dei dettagli utilizzando l’ID SQL
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(‘dtdqt19kfv6yx’))
— Ottieni la cronologia dell’esecuzione del piano Explain utilizzando l’ID SQL nel report AWR
SELECT plan_table_output FROM TABLE(dbms_xplan.display_awr(‘dtdqt19kfv6yx’))
— Usa Longops per controllare il tempo di esecuzione della stima
SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,
last_update_time, time_remaining “REMAIN SEC”, round(time_remaining/60,2) “REMAIN MINS”,
elapsed_seconds “ELAPSED SEC”, round(elapsed_seconds/60,2) “ELAPSED MINS”,
round((time_remaining+elapsed_seconds)/60,2)”TOTAL MINS”, message TIME
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> ‘0’
Creazione di un profilo SQL utilizzando DBMS_SQLTUNE
Fornisce consigli che, se implementati, vengono associati alla query e vengono utilizzati dall’ottimizzatore in fase di analisi.
Quanto segue creerà un profilo SQL:
SQL> DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => ‘FULL QUERY TEXT’,
profile => sqlprof_attr(‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’),
name => ‘PROFILE NAME’, force_match => TRUE/FALSE);
‘FULL QUERY TEXT’ – Il valore può essere ottenuto dalla colonna SQL_FULLTEXT nella tabella GV $ SQLAREA o DBA_HIST_SQLTEXT.
SQL> SELECT SQL_FULLTEXT FROM GV$SQLAREA WHERE sql_id = ‘4n01r8z5hgfru’
SQL> SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE sql_id = ‘4n01r8z5hgfru’
‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’– The hint specification can be obtained from the table DBA_HIST_SQL_PLAN. For example:
SQL> SELECT extractvalue(VALUE(d), ‘/hint’) AS outline_hints
FROM xmltable(‘/*/outline_data/hint’
passing (SELECT xmltype(other_xml) AS xmlval
FROM dba_hist_sql_plan WHERE sql_id = ‘4n01r8z5hgfru’ AND plan_hash_value = ‘82930460’
AND other_xml IS NOT NULL)) d;
Oppure puoi ottenerlo dalla funzione DBMS_XPLAN.DISPLAY_AWR come di seguito.
SQL> SELECT plan_table_output FROM TABLE
(dbms_xplan.display_awr(‘SQL_ID’,’PLAN_HASH’,NULL,’OUTLINE’))
Puoi anche generare il file di traccia 10053 e cercare la specifica del suggerimento tra BEGIN_OUTLINE_DATA e END_OUTLINE_DATA. Scarica SQLTXPLAIN.sql da Oracle Metalink ed eseguilo per ottenere il file di traccia 10053.
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.3’)
OPT_PARAM(‘_b_tree_bitmap_plans’ ‘false’)
OPT_PARAM(‘_fast_full_scan_enabled’ ‘false’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$335DD26A”)
MERGE(@”SEL$3″)
OUTLINE_LEAF(@”SEL$7286615E”)
MERGE(@”SEL$5″)
OUTLINE_LEAF(@”SEL$1″)
……
END_OUTLINE_DATA
*/
FORCE_MATCH è in realtà il motivo principale per utilizzare i profili SQL, quando impostato su TRUE ignorerà i valori letterali con query esatte e implementerà il profilo su di essi (proprio come ciò che cursor_sharing = force fa all’intero DB). Per esempio: –
Quando force match è impostato su TRUE, a.segment1 = 1234 diventerà a.segment1 =: b1
Per creare un profilo SQL, un utente deve avere quanto segue: ruolo ADVISOR, creare qualsiasi privilegio del profilo sql, alterare qualsiasi privilegio del profilo sql, eliminare qualsiasi privilegio del profilo sql ed eseguire il privilegio su DBMS_SQLTUNE.
SQL> GRANT EXECUTE ON SYS.DBMS_SQLTUNE TO <user>;
SQL> GRANT ADVISOR TO <user>;
SQL> GRANT CREATE ANY SQL PROFILE TO <user>;
SQL> GRANT ALTER ANY SQL PROFILE TO <user>;
SQL> GRANT DROP ANY SQL PROFILE TO <user>;
Elenco di esempi per creare il profilo Oracle SQL:
SQL> DBMS_SQLTUNE.IMPORT_SQL_PROFILE
(
sql_text => ‘select * from emp’,
profile => sqlprof_attr(‘ALL_ROWS’,’IGNORE_OPTIM_EMBEDDED_HINTS’, …… ),
category => ‘DEFAULT’,
name => ‘change_emp’,
force_match => TRUE
);
DECLARE
cl_sql_text CLOB;
BEGIN
— SELECT sql_fulltext INTO cl_sql_text from gv$sqlarea where sql_id=’4n01r8z5hgfru’;
SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = ‘4n01r8z5hgfru’;
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => cl_sql_text,
profile => sqlprof_attr(‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’),
name => ‘PROFILE NAME’,
force_match => TRUE);
END;
/
DECLARE
cl_sql_text CLOB;
hint_spec sys.sqlprof_attr;
BEGIN
–SELECT sql_fulltext INTO cl_sql_text FROM gv$sqlarea WHERE sql_id=’gtwyx63711jp1′;
SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = ‘gtwyx63711jp1’;
SELECT
extractvalue(VALUE(d), ‘/hint’) AS outline_hints
BULK COLLECT
INTO
hint_spec
FROM
xmltable(‘/*/outline_data/hint’
passing (
SELECT
xmltype(other_xml) AS xmlval
FROM
dba_hist_sql_plan
WHERE
sql_id = ‘gtwyx63711jp1’
AND plan_hash_value = ‘82930460’
AND other_xml IS NOT NULL
)
) d;
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => cl_sql_text,
profile => hint_spec,
name => ‘PROFILE NAME’,
force_match => TRUE);
END;
/
Nota: è possibile utilizzare la tabella v $ sql_plan se non sono disponibili suggerimenti di struttura in dba_hist_sql_plan.
SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
SQL> SELECT sql_attr.attr_val outline_hints
FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr
WHERE sql_profiles.signature = sql_attr.signature
AND sql_profiles.name = ‘PROFILE NAME’
ORDER BY sql_attr.attr# ASC;
È possibile eliminare un profilo SQL con la procedura DROP_SQL_PROFILE. Per esempio:
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘PROFILE NAME’);
END;
/
In questo esempio, my_sql_profile è il nome del profilo SQL che desideri eliminare. È inoltre possibile specificare se ignorare gli errori sollevati se il nome non esiste. Per questo esempio, viene accettato il valore predefinito di FALSE.