Blog

le 03/02/2007
Thomas Bordet
Archives blog-arkzoyd @fr

SQL #4 et SQL profile /+Anti-Pattern/

Qu’est-ce qu’un « SQL profile » ? L’exemple ci-dessous illustre cette question. Le profil SQL permet de stocker des corrections qui concerne les cardinalités estimées dans le plan d’exécution. Ces corrections permettent d’influencer le plan d’exécution en donnant des informations qui ne peuvent pas être déduite des statistiques. Contrairement aux « outlines », cette technique ne fige pas les plans et permet donc de conserver à tout moment l’ensemble des caractéristiques avancées du CBO.

Le schéma d’exemple
Pour démontrer cette fonctionnalité, nous allons créer 2 tables dont les données sont corrélées comme indiqué ci-dessous :

drop table demo#4 purge;
drop table demo#5 purge;

create table demo#4 (col1 number, col2 number, col3 number,
col4 number, col5 number, col6 number,
col7 varchar2(4000));

create table demo#5 (col1 number, col2 number, col3 number,
col4 number, col5 number, col6 number,
col7 varchar2(4000));

begin
for j in 1..1000 loop
for i in 1..10 loop
insert into demo#4 values (i, i, i, i, i, i,
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
insert into demo#5 values (i, i, i, i, i, i,
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
end loop;
end loop;
commit;
end;
/
create index demo#4_idx on demo#4(col1, col2, col3, col4, col5, col6);
create index demo#5_idx on demo#5(col1, col2, col3, col4, col5, col6);

exec dbms_stats.gather_table_stats(USER, ‘DEMO#4’, –
cascade=>true, method_opt=>’FOR ALL COLUMNS SIZE 254′);
exec dbms_stats.gather_table_stats(USER, ‘DEMO#5’, –
cascade=>true, method_opt=>’FOR ALL COLUMNS SIZE 254′);

Requête et erreur de cardinalités
En exécutant la requête ci-dessous et en visualisant le plan d’exécution, il apparaît clairement que la cardinalité des étapes est mal estimée. Cela est dû au fait que les colonnes sont corrélées et que les statistiques ne peuvent représenter ces corrélations (peut-être qu’en 11 ?).

set timing on
set autotrace traceonly
select count(d5.col7)
from demo#4 d4, demo#5 d5
where d4.col1=1 and d4.col2=1 and d4.col3=1 and d4.col4=1 and d4.col5=1 and d4.col6=1 and
d4.col1=d5.col1 and d4.col2=d5.col2 and d4.col3=d5.col3 and d4.col4=d5.col4 and
d4.col5=d5.col5 and d4.col6=d5.col6;

Plan d’exécution
———————————————————-
Plan hash value: 2104927668

———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 1037 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 1037 | | |
| 2 | MERGE JOIN CARTESIAN | | 1 | 1037 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEMO#4_IDX | 1 | 18 | 1 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 1019 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEMO#5 | 1 | 1019 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | DEMO#5_IDX | 1 | | 1 (0)| 00:00:01 |
———————————————————————————————

Utilisation du SQL*Tuning Advisor
Pour améliorer les informations dont dispose le CBO, nous allons créer un profile SQL. Pour cela, il faut en réalité utiliser le SQL*Tuning Advisor comme dans les Post Précédent.

exec dbms_sqltune.drop_tuning_task( task_name => ‘DEMO#4’ );
variable gg varchar2(100)
exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( –
sql_text => ‘select count(d5.col7) ‘|| –
‘from demo#4 d4, demo#5 d5 ‘|| –
‘where d4.col1=1 and d4.col2=1 and d4.col3=1 and d4.col4=1 and d4.col5=1 and ‘|| –
‘d4.col6=1 and d4.col1=d5.col1 and d4.col2=d5.col2 and d4.col3=d5.col3 and ‘|| –
‘d4.col4=d5.col4 and d4.col5=d5.col5 and d4.col6=d5.col6’,-
user_name => ‘SYSTEM’, –
scope => ‘COMPREHENSIVE’, –
time_limit => 30, –
task_name => ‘DEMO#4’, –
description => ‘Tuning d »un accès simple à la table DEMO#4’);

exec dbms_sqltune.execute_tuning_task( task_name => ‘DEMO#4’ );

set long 10000
set longchunksize 10000
set lines 100
select dbms_sqltune.report_tuning_task(‘DEMO#4’)
from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#4’)
——————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : DEMO#4
Tuning Task Owner : SYSTEM
Scope : COMPREHENSIVE
Time Limit(seconds) : 30
Completion Status : COMPLETED
Started at : 02/03/2007 19:29:06
Completed at : 02/03/2007 19:29:09
Number of SQL Profile Findings : 1
Number of SQL Restructure Findings: 1

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#4’)
——————————————————————————-

——————————————————————————-
Schema Name: SYSTEM
SQL ID : advwdy4cx4kvp
SQL Text : select count(d5.col7) from demo#4 d4, demo#5 d5 where d4.col1=1
and d4.col2=1 and d4.col3=1 and d4.col4=1 and d4.col5=1 and
d4.col6=1 and d4.col1=d5.col1 and d4.col2=d5.col2 and
d4.col3=d5.col3 and d4.col4=d5.col4 and d4.col5=d5.col5 and
d4.col6=d5.col6

——————————————————————————-

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘DEMO#4’)
——————-
————————————————————
FINDINGS SECTION (2 findings)
——————————————————————————-

1- SQL Profile Finding (see explain plans section below)
——————————————————–
Un meilleur plan d’exécution éventuel a été trouvé pour cette instruction.

Recommendation (estimated benefit: 91,26%)
——————————————
– Envisagez d’accepter le profil SQL recommandé.
execute dbms_sqltune.accept_sql_profile(task_name => ‘DEMO#4’, replace =>
TRUE);

[…]

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-
[…]

Accepter le « SQL profile »
Conformément à la recommandation de du SQL Tuning Advisor, nous allons implémenter le SQL*Profile à l’aide de la commande ci-dessous :
exec dbms_sqltune.accept_sql_profile(task_name => ‘DEMO#4’, replace =>TRUE);

On peut visualiser le profile et son contenu à l’aide des ordres qui suivent :
col SIGNATURE format 9999999999999999999999
set pages 1000
select * from dba_sql_profiles;

NAME CATEGORY SIGNATURE
—————————— —————————— ———————–
SQL_TEXT
—————————————————————————————————-
CREATED LAST_MOD
——– ——–
DESCRIPTION
—————————————————————————————————-
TYPE STATUS FOR
——— ——– —
SYS_SQLPROF_01442238490f4001 DEFAULT 17145405553731497926
select count(d5.col7) from demo#4 d4, demo#5 d5 where d4.col1=1 and d4.col2=1 and d4.col3=1 and d4.c
ol4=1 and d4.col5=1 and d4.col6=1 and d4.col1=d5.col1 and d4.col2=d5.col2 and d4.col3=d5.col3 and d4
.col4=d5.col4 and d4.col5=d5.col5 and d4.col6=d5.col6
03/02/07 03/02/07

MANUAL ENABLED NO

select * from sys.sqlprof$attr where SIGNATURE=17145405553731497926 order by attr#;

SIGNATURE CATEGORY ATTR#
———————– —————————— ———-
ATTR_VAL
——————————————————————————–
17145405553731497926 DEFAULT 1
OPT_ESTIMATE(@ »SEL$1″, TABLE, « D4″@ »SEL$1 », SCALE_ROWS=97734)

17145405553731497926 DEFAULT 2
OPT_ESTIMATE(@ »SEL$1″, INDEX_SCAN, « D4″@ »SEL$1 », DEMO#4_IDX, SCALE_ROWS=100000)

17145405553731497926 DEFAULT 3
OPT_ESTIMATE(@ »SEL$1″, INDEX_SCAN, « D5″@ »SEL$1 », DEMO#5_IDX, SCALE_ROWS=100000)

17145405553731497926 DEFAULT 4
OPT_ESTIMATE(@ »SEL$1″, TABLE, « D5″@ »SEL$1 », SCALE_ROWS=100240)

Enfin si on veux supprimer le SQL profile, il faudrait exécuter la commande qui suit :
exec dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_01442238490f4001’);

Impact du SQL profile
Pour visualiser l’impact du SQL Profile, il suffit de visualiser le plan d’exécution de la requête. On constate que les cardinalités des étapes du plan sont bien plus proche de la réalité.

set timing on
set autotrace on
select count(d5.col7)
from demo#4 d4, demo#5 d5
where d4.col1=1 and d4.col2=1 and d4.col3=1 and d4.col4=1 and d4.col5=1 and d4.col6=1 and
d4.col1=d5.col1 and d4.col2=d5.col2 and d4.col3=d5.col3 and d4.col4=d5.col4 and
d4.col5=d5.col5 and d4.col6=d5.col6;

COUNT(D5.COL7)
————–
1000000

Ecoulé : 00 :00 :00.75

Plan d’exécution
———————————————————-
Plan hash value: 2365803633

——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 1037 | 21 (86)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 1037 | | |
|* 2 | HASH JOIN | | 979K| 968M| 21 (86)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEMO#4_IDX | 977 | 17586 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEMO#5 | 1002 | 997K| 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEMO#5_IDX | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————–

Alors qu’est-ce qu’un SQL profile ?
Un SQL profile, c’est en fait un ensemble de HINTS qui sont stockés dans le dictionnaire de données constitués de OPT_ESTIMATE et associé à un ordre SQL. Ces HINTS permettent d’indiquer des modifications de cardinalités dans un plan d’exécution. Par exemple le HINTS qui suit : « OPT_ESTIMATE(@ »SEL$1 », TABLE, « D4″@ »SEL$1″, SCALE_ROWS=97734 » indique que dans le SELECT principal, la table dont l’alias est D4 a une correction de 97734 fois le nombre de lignes estimé à l’origine (Ce nombre est 1). Pour vous convaincre de ce point, exécutez simplement l’ordre qui suit et qui est construit au moyen des HINTS récupérés dans la vue sqlprofile$attr.

set timing on
set autotrace on
SELECT /*+OPT_ESTIMATE(@ »SEL$1″, TABLE, « D4″@ »SEL$1″, SCALE_ROWS=97734) OPT_ESTIMATE(@ »SEL$1 », INDEX_SCAN, « D4″@ »SEL$1″, DEMO#4_IDX, SCALE_ROWS=100000) OPT_ESTIMATE(@ »SEL$1 », INDEX_SCAN, « D5″@ »SEL$1″, DEMO#5_IDX, SCALE_ROWS=100000) OPT_ESTIMATE(@ »SEL$1 », TABLE, « D5″@ »SEL$1 », SCALE_ROWS=10
0240)*/ count(d5.col7)
from demo#4 d4, demo#5 d5
where d4.col1=1 and d4.col2=1 and d4.col3=1 and d4.col4=1 and d4.col5=1 and d4.col6=1 and
d4.col1=d5.col1 and d4.col2=d5.col2 and d4.col3=d5.col3 and d4.col4=d5.col4 and
d4.col5=d5.col5 and d4.col6=d5.col6;

COUNT(D5.COL7)
————–
1000000

Ecoulé : 00 :00 :00.75

Plan d’exécution

Pour en savoir encore plus…
Rien ne vaut des tests complémentaires… Je vous invite à positionner l’évènement 10053 pour visualiser l’impact des HINTS du SQL profile sur l’ordre ci-dessous :

alter session set events '10053 trace name context forever, level 1';
set timing on
set autotrace on
SELECT /*+OPT_ESTIMATE(@"SEL$1", TABLE, "D4"@"SEL$1", SCALE_ROWS=97734) OPT_ESTIMATE(@"SEL$1", TABLE, "D5"@"SEL$1", SCALE_ROWS=100240)*/ count(d5.col7)
from demo#4 d4, demo#5 d5
where d4.col1=1 and d4.col2=1 and d4.col3=1 and d4.col4=1 and d4.col5=1
and d4.col6=1 and d4.col1=d5.col1 and d4.col2=d5.col2 and d4.col3=d5.col3
and d4.col4=d5.col4 and d4.col5=d5.col5 and d4.col6=d5.col6;
alter session set events '10053 trace name context off';

Regardez le fichier de trace de la session qui est généré dans USER_DUMP_DEST. Avec un peu de patience, vous trouverez en effet que les nombres de lignes retournées par le select sur les tables DEMO#4 et DEMO#5 sont corrigés.

GarK!

Thomas Bordet

Voir tous ses articles

Blog

SQL #4 et SQL profile /*+Anti-Pattern*/

Articles récents

SQL #4 et SQL profile /+Anti-Pattern/