Qu’est-ce que vous préfèrez de la 11g jusqu’à présent ? Database Replay (testé dans un précédent post) qui permet de reproduire la charge de la production sur un environnement de test de manière très simple ? Snapshot Standby (également testée dans un précédent post) qui permet d’utiliser une Standby pour des tests tout en assurant la protecti0n de la base de donnees de production ? Tous les améliorations en terme de performance (algorithme NL, statistiques sur des colonnes virtuelles, statistiques multi-colonnes, bind peeking 2.0…) ? Tuning Pack Real Time SQL Monitoring ? Une des 389 nouvelles fonctionnalités que je n’ai pas encore explorée ? Ça pourrait bien être « Automatic SQL Plan Management » ! Pourquoi ?
Avec 11g Enterprise Edition, SQL Plan Management « remplace » (complète ?) avantageusement les « Outlines » pour gérer les plans des ordres SQL. Vous trouverez ci-dessous un exemple d’utilisation de cette nouvelle fonctionnalité.
Il faut noter que la gestion automatique d’Oracle SQL Plan Management fait parti du Tuning Pack mais commençons par un exemple d’utilisation avec Oracle 11g Enterprise Edition, sans pack. La documentation Oracle explique très bien cette fonctionnalité et je ne la paraphraserai pas ; vous ne devriez pas en avoir besoin pour lire ce qui suit (quoique !) :
- Oracle 11g Performance Guide – 15.Using SQL Plan Management
- Oracle 11g PL/SQL Packages and Types Reference : DBMS_SPM
- Oracle 11g Reference :
Etape 1. Configuration et schéma exemple
Pour commencer, créez une table que vous allez remplir avec 10000 lignes.
create table gark
(id number not null);
begin
for i in 1..10000 loop
insert into gark(id)
values (i);
end loop;
commit;
end;
/
exec dbms_stats.gather_table_stats(user, 'GARK')
Etape 2. Requête et Plan
Avec SQL*Plus (ça ne marche pas avec SQL*Developer parce des ordres SQL sont générés entre les différentes étapes d’exécution d’un script), exécutez la requête suivante et visualisez son plan a l’aide du script ci-dessous :
set serveroutput off
select a.id,
b.id
from gark a,
gark b
where a.id=500
and b.id=a.id;
select * from table(dbms_xplan.display_cursor);
Le plan ressemble à celui ci :
Execution Plan
----------------------------------------------------------
Plan hash value:
2625395012
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 24 | 23 (5)|
|* 1 | HASH JOIN | | 4 | 24 | 23 (5)|
|* 2 | TABLE ACCESS FULL| GARK | 2 | 6 | 11 (0)|
|* 3 | TABLE ACCESS FULL| GARK | 2 | 6 | 11 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="A"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)
Etape 3. Enregistrer le plan dans les baselines de SQL Plan Management
Pour réaliser cette opération manuellement, vous devez relever le SQL_ID de la requête. Notez la PLAN_HASH_VALUE qui s’est affichée dans votre plan, puis recherchez le SQL_ID correspondant :
accept plan_hash_value prompt "Enter value for plan_hash_value: "
select distinct sql_id, plan_hash_value
from v$sql_plan
where plan_hash_value=&plan_hash_value;
SQL_ID PLAN_HASH_VALUE
------------- ---------------
4pznd20f4x8tf 2625395012
La requête suivante affiche les baselines enregistrées dans la base de données. Cette liste est normalement vide sauf si vous avez utiliser cette fonctionnalité ou positionne le paramètre optimizer_capture_sql_plan_baselines à true
col SQL_HANDLE format a24
col PLAN_NAME format a29
col PARSING_SCHEMA_NAME format a8
select SQL_HANDLE,
PLAN_NAME,
PARSING_SCHEMA_NAME,
ENABLED,
ACCEPTED,
SQL_TEXT
from dba_sql_plan_baselines;
no row selected.
Vous pouvez ensuite enregistrez votre plan dans les baselines. Le script qui suit :
- Enregistre le plan dans SQL Plan Management
- Affiche le plan enregistré
accept sql_id prompt "Enter value for sql_id: "
4pznd20f4x8tf
declare
gg binary_integer;
begin
gg:=dbms_spm.load_plans_from_cursor_cache(
sql_id=>'&sql_id');
end;
/
PL/SQL procedure successfully completed.
select SQL_HANDLE,
PLAN_NAME,
PARSING_SCHEMA_NAME,
ENABLED,
ACCEPTED,
SQL_TEXT
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME PARSING_ ENA ACC
------------------------ ----------------------------- -------- --- ---
SQL_TEXT
--------------------------------------------------------------------------------
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b SCOTT YES YES
select a.id,
b.id
from gark a,
gark b
where a.id=500
and b.id=a.id
Etape 4. Tester que la « baseline » empêche l’évolution du plan
Pour vérifier le fonctionnement, nous allons :
- Créer un index qui pourrait améliorer le plan
- Vérifiez le contenu du baseline après la création de l’index
- Vider la shared pool et s’assure que le plan n’est plus dans la SGA
- Exécutez la requête et afficher son plan
1- Créer un index
create index gark_idx
on gark(id);
Index created.
2- Si après avec créé l’index, vous regardez ce qui est stocké dans le baseline. C’est le plan d’avant la création de l’index :
accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'&sql_handle'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL handle: SYS_SQL_9ff3489361e958aa
SQL text: select a.id, b.id from gark a, gark b where a.id=
and b.id=a.id
---------------------------------------------------------------------------
---------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_61e958aa45bb399b
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
---------------------------------------------------------------------------
Plan hash value: 2625395012
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 24 | 23 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 24 | 23 (5)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| GARK | 2 | 6 | 11 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| GARK | 2 | 6 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="A"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)
ERROR: User has no SELECT privileges on objects of the SQL plan baseline
3- Ensuite vous videz la shared pool (Pour être sur…) et vous vérifiez que le SQL_ID n’y est plus :
alter system flush shared_pool;
System altered.
accept sql_id prompt "Enter value for sql_id: "
4pznd20f4x8tf
select sql_id,
plan_hash_value
from v$sql
where sql_id='&sql_id';
no rows selected
4- Enfin vous exécutez la requête encore et visualisez son plan ne change pas malgré l’index :
set serveroutput off
select a.id,
b.id
from gark a,
gark b
where a.id=500
and b.id=a.id;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 06uwqz3vkq97v, child number 1
-------------------------------------
select a.id, b.id from gark a, gark b where a.id=500
and b.id=a.id
Plan hash value: 2625395012
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| |
|* 1 | HASH JOIN | | 4 | 24 | 23 (5)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| GARK | 2 | 6 | 11 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| GARK | 2 | 6 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="A"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_61e958aa45bb399b used for this statement
Il est intéressant de noter qu’un autre plan est ajouté automatiquenent dans le baselines pour lequel ACCEPTED=’NO’ et ORIGIN=’AUTO-CAPTURE’. Ce plan n’est pas utilisé par la requête :
select SQL_HANDLE,
PLAN_NAME,
PARSING_SCHEMA_NAME,
ENABLED,
ACCEPTED,
SQL_TEXT
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME PARSING_ ENA ACC
------------------------ ----------------------------- -------- --- ---
SQL_TEXT
-----------------------------------------------------------------------
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b SCOTT YES YES
select a.id,
b.id
from gark a,
gark b
where a.id=500
and b.id=a.id
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa SCOTT YES NO
select a.id,
b.id
from gark a,
gark b
where a.id=500
and b.id=a.id
accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'&sql_handle'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_9ff3489361e958aa
SQL text: select a.id, b.id from gark a, gark b where a.id=500 and
b.id=a.id
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_61e958aa45bb399b
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 2625395012
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 15 (7)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 6 | 15 (7)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| GARK | 1 | 3 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| GARK | 1 | 3 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="A"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_61e958aafcd784aa
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 886284999
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (34)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 3 (34)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | GARK_IDX | 1 | 3 | 1 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 3 | 2 (50)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | GARK_IDX | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"=500)
4 - access("B"."ID"=500)
Etape 5. Faire évoluer le plan
La fonction evolve_sql_plan_baseline permet de valider les nouveaux plans dans la baseline et ainsi de faire évoluer automatiquement les plans stockés si ceux-ci sont plus performants :
accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
var v_out clob
exec :v_out:=dbms_spm.evolve_sql_plan_baseline(-
sql_handle=>'&sql_handle')
print v_out
select SQL_HANDLE,
PLAN_NAME,
PARSING_SCHEMA_NAME,
ENABLED,
ACCEPTED
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME PARSING_ ENA ACC
------------------------ ----------------------------- -------- --- ---
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b SCOTT YES YES
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa SCOTT YES YES
Vous constatez que les 2 plans sont désormais acceptés et si vous exécutez de nouveau la requête, le nouveau plan (avec l’index) est utilisé :
select a.id,
b.id
from gark a,
gark b
where a.id=500
and b.id=a.id;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------
SQL_ID 4pznd20f4x8tf, child number 0
-------------------------------------
select a.id, b.id from gark a, gark b where a.id=500 and
b.id=a.id
Plan hash value: 886284999
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | GARK_IDX | 1 | 3 | 1 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | GARK_IDX | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"=500)
4 - access("B"."ID"=500)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_61e958aafcd784aa used for this statement
Etape 6. Revenir en arrière
Il est bien sur possible d’enlever le plan des baselines pour fixer un plan (cf point 7). Toutefois l’attribut FIXED des baselines permet de forcer les plan qui doivent être utilisés parmi ceux acceptés. Vous pouvez donc en garder tous les plans dans les baselines pour revenir simplement en arrière :
accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
accept plan_name prompt "Enter value for plan_name: "
SYS_SQL_PLAN_61e958aa45bb399b
declare
v_out binary_integer;
begin
v_out := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle =>'&sql_handle',
plan_name=>'&plan_name',
attribute_name=>'fixed',
attribute_value=>'YES');
end;
/
select SQL_HANDLE,
PLAN_NAME,
FIXED,
ACCEPTED
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME FIX ACC
------------------------ ----------------------------- --- ---
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b YES YES
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa NO YES
select a.id,
b.id
from gark a,
gark b
where a.id=500
and b.id=a.id;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4pznd20f4x8tf, child number 2
-------------------------------------
select a.id, b.id from gark a, gark b where a.id=500 and
b.id=a.id
Plan hash value: 2625395012
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
|* 1 | HASH JOIN | | 1 | 6 | 15 (7)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| GARK | 1 | 3 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| GARK | 1 | 3 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"="A"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)
Pour revenir à l’état précédent, il suffit de passer l’attribut FIXED à NO :
accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
accept plan_name prompt "Enter value for plan_name: "
SYS_SQL_PLAN_61e958aa45bb399b
declare
v_out binary_integer;
begin
v_out := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle =>'&sql_handle',
plan_name=>'&plan_name',
attribute_name=>'fixed',
attribute_value=>'NO');
end;
/
select SQL_HANDLE,
PLAN_NAME,
FIXED,
ACCEPTED
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME FIX ACC
------------------------ ----------------------------- --- ---
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b NO YES
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa NO YES
Etape 7. Supprimer les plans de la baseline
Pour supprimer les plans enregistrés dans les baselines, il suffit d’utiliser la fonction drop_sql_plan_baseline du package dbms_spm comme ci-dessous :
col schema format a5
col SQL_HANDLE format a24
select SQL_HANDLE,
PLAN_NAME,
PARSING_SCHEMA_NAME SCHEMA,
ENABLED,
ACCEPTED
from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SCHEM ENA ACC
------------------------ ----------------------------- ----- --- ---
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aa45bb399b SCOTT YES YES
SYS_SQL_9ff3489361e958aa SYS_SQL_PLAN_61e958aafcd784aa SCOTT YES YES
accept sql_handle prompt "Enter value for sql_handle: "
SYS_SQL_9ff3489361e958aa
accept plan_name prompt "Enter value for plan_name: "
SYS_SQL_PLAN_61e958aa45bb399b
DECLARE
gg binary_integer;
BEGIN
gg:=DBMS_SPM.DROP_SQL_PLAN_BASELINE(
SQL_HANDLE => '&sql_handle',
PLAN_NAME => '&plan_name');
END;
/
accept plan_name prompt "Enter value for plan_name: "
SYS_SQL_PLAN_61e958aafcd784aa
DECLARE
gg binary_integer;
BEGIN
gg:=DBMS_SPM.DROP_SQL_PLAN_BASELINE(
SQL_HANDLE => '&sql_handle',
PLAN_NAME => '&plan_name');
END;
/
col schema format a5
col SQL_HANDLE format a24
select SQL_HANDLE,
PLAN_NAME,
PARSING_SCHEMA_NAME SCHEMA,
ENABLED,
ACCEPTED
from dba_sql_plan_baselines;
no rows selected
Etape 8. SQL Plan Management et Tuning Pack 11g
Le paramètres optimizer_capture_sql_plan_baselines permet de capturer automatiquement les plans utilisés tandis que le SQL Tuning Advisor programmé toutes les nuits par défaut en 11g fait évoluer les plans tout en considérant bien plus que les statistiques. : les vrais performances ? 11g avec les Packs Diagnostic et Tuning pourraient bien nous faire entrer dans une nouvelle ère ; reste à valider à quel point les advisors évoluent eux aussi !
alter system set optimizer_capture_sql_plan_baselines=true
Etape 9. Supprimer le données exemples :
Pour supprimer les structures créées précédemment, utilisez le script ci-dessous :
drop index gark_idx;
drop table gark;
Etape 10. Conclure
Utilisé en mode manuel, SQL Plan Management simplifie grandement la manière de figer un plan par rapport aux Outlines. Enfin, j’espère bien l’utiliser en mode automatique sur un vrai environnement d’ici peu, histoire de vraiment réaliser, autrement qu’en théorie, les gains auxquels on peut s’attendre
Alors, qu’est-ce que vous en pensez ?
