Profil de performance

Fiche express
Type	Méthode de diagnostic de performance SQL
Étapes	SQL_ID → PLAN_HASH_VALUE → SQL Profile
Vues clés	GV$SQL, DBA_HIST_SQLSTAT, DBA_HIST_SQL_PLAN
Voir aussi	Instance · Oracle · Redo log

Méthode pour identifier le profil de performance d'une requête SQL problématique et lui associer manuellement le meilleur plan d'exécution connu, via un SQL Profile.

Identification du profil

Identifier le SQL_ID de la requête

SELECT sql_id,
  (s.cpu_time/1000000) "CPU_Seconds",
  s.disk_reads "Disk_Reads",
  s.buffer_gets "Buffer_Gets",
  s.executions "Executions",
  (s.elapsed_time/1000000) "Elapsed_Seconds",
  substr(s.sql_text,1,500) "SQL"
FROM gv$session e JOIN gv$sql s USING (sql_id)
WHERE type != 'BACKGROUND'
  AND status = 'ACTIVE'
  AND sql_id IS NOT NULL;

Identifier le PLAN_HASH_VALUE pour ce SQL_ID

SELECT begin_interval_time, sql_id, plan_hash_value
FROM dba_hist_sqlstat JOIN dba_hist_snapshot s USING (snap_id)
WHERE sql_id = '&sqlid'
ORDER BY snap_id ASC;

SELECT plan_table_output FROM TABLE(dbms_xplan.display_awr('&sqlid'));

À l'issue de ces deux requêtes, on dispose du couple SQL_ID / PLAN_HASH_VALUE à utiliser pour la suite.

Forcer l'association d'un plan à une requête

Objectif : indiquer explicitement à Oracle d'utiliser un plan d'exécution donné (par exemple celui observé avant une régression de performance) pour un SQL_ID donné, via un SQL Profile construit à partir des hints du plan enregistré dans l'AWR.

• Script à créer une fois (exemple, technique classique de génération de SQL Profile depuis un

 snapshot AWR — (à vérifier) : la syntaxe de la sous-requête XML imbriquée peut
 varier selon la version d'Oracle, à tester avant tout usage en production) :

DECLARE
  ar_profile_hints sys.sqlprof_attr;
  cl_sql_text       CLOB;
BEGIN
  SELECT extractvalue(VALUE(d), '/hint') AS outline_hints
  BULK COLLECT INTO ar_profile_hints
  FROM xmltable('/*/outline_data/hint'
    PASSING (
      SELECT xmltype(other_xml) AS xmlval
      FROM dba_hist_sql_plan
      WHERE sql_id = '&&1'
        AND plan_hash_value = &&2
        AND other_xml IS NOT NULL
    )
  ) d;

  SELECT sql_text
  INTO cl_sql_text
  FROM dba_hist_sqltext
  WHERE sql_id = '&&1'
    AND rownum = 1;

  dbms_sqltune.import_sql_profile(
    sql_text    => cl_sql_text,
    profile     => ar_profile_hints,
    category    => '&&3',
    name        => 'PROFILE_&&1',
    -- force_match => true pour matcher même avec des littéraux différents
    -- (comportement proche de CURSOR_SHARING=SIMILAR)
    force_match => &&4
  );
END;
/

• Exécution en tant que sysdba, avec SQL_ID, PLAN_HASH_VALUE, catégorie et

 force_match en paramètres :
SQL> @create_profile_from_awr.sql 19v1d4mnc0tt0 619094667 DEFAULT false
SQL> @create_profile_from_awr.sql $SQL_ID $PLAN_HASH DEFAULT false

Voir aussi

• Instance — vues dynamiques et contexte de l'instance
• Oracle — panorama général
• Redo log — autre source de vues historiques (V$LOG_HISTORY) utile au diagnostic