Configuration Support Manager /*+ Oracle is aware */

Inclus dans le support classique (i.e. premium), Oracle Configuration Support Manager est un patch que vous installez sur vos logiciels Oracle. Il permet de mettre à jour directement Metalink avec les informations relatives à vos configurations.

• La création de SR est plus rapide
• Les analyses sont plus pertinentes
• Des recommandations sont effectuées automatiquement

Pour l'installer, cliquez sur le bouton "Configuration Support Manager" -> "Download Collector" -> "Download" comme ci-dessous.


Cette solution ressemble de plus en plus aux approches des fournisseurs de matériels de stockage. Pour une explication plus complète cliquez ici.

GarK!

Oracle Never Dies /*+Lifetime Support*/

Pour plusieurs produits dont Oracle 9.2, le support est désormais vraiment éternel... J'imagine qu'il y aura bientôt une ou deux publications pour indiquer les modifications de la politique déjà annoncée et qui devrait faire très plaisir à tous (A moi au moins). Surveillez les URL ci-dessous :

• http://www.oracle.com/support/policies.html
• http://www.oracle.com/support/premier/lifetime-support-policy.html

En substance, vous pourrez toujours ouvrir des SR sur Oracle9i dans 20 ans... et peut-être même (ça reste à confirmer), si vous n'avez pas payé le supplément liés à l'"Extended Support".

Quoiqu'il en soit, je vous conseille malgré tout, comme SAP dans sa note 925871, de migrer vos bases de données 9i en 10g avant le 31 juillet 2007. Sinon, attention aux délais de résolutions et aux bugs non référencés.

GarK!

Surveiller l'utilisation des index et SQL #8

Dans ce Post, vous allez activer l'utilisation des index. Pour cela, vous allez créer une table DEMO#8. Activer la surveillance des index. Utiliser l'index surveillé et visualiser la surveillance.

Création du schéma
create table DEMO#8 (col1 number primary key, col2 number, col3 varchar2(4000)) tablespace users;

begin
for i in 1..100000 loop
insert into DEMO#8 values (i, i,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
/

exec dbms_stats.gather_table_stats(USER, 'DEMO#8', cascade=>true,-
method_opt=>'FOR ALL COLUMNS SIZE AUTO');

select index_name from user_indexes where table_name='DEMO#8';

INDEX_NAME
-------------
SYS_C005443

Utiliser le monitoring de l'index
Dans cette section, vous allez activer la surveillance de l'index et l'utiliser. A toutes les étapes de la manipulation, vous pouvez effectivement savoir si l'index a été utilisé.

alter index SYS_C005443 monitoring usage;

col INDEX_NAME format A13
col TABLE_NAME format A10
select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------- ---------- --- --- ------------------- -------------------
SYS_C005443 DEMO#8 YES NO 02/04/2007 23:28:48

SQL> set autotrace traceonly explain
SQL> select /*+ INDEX(DEMO#8)*/ * from demo#8 where col1=2;

Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 101176399

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1010 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO#8 | 1 | 1010 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005443 | 1 | |
-------------------------------------------------------------------

select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------- ---------- --- --- ------------------- -------------------
SYS_C005443 DEMO#8 YES YES 02/04/2007 23:28:48

alter index SYS_C005443 nomonitoring usage;

select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------- ---------- --- --- ------------------- -------------------
SYS_C005443 DEMO#8 NO YES 02/04/2007 23:28:48 02/04/2007 23:29:48

Supprimez la table
drop table demo#8 purge;

GarK!

Segment Advisor et SQL #7 /*+Premiers Pas*/

Quand réorganiser les tables et les index ? C'est à cette question que répond le "Segment Advisor". Dans l'exemple ci-dessous, nous allons illustrer comment il fonctionne.

Création d'un schéma
Dans le schéma ci-dessous, la table DEMO#7 est volontairement remplie puis vidée pour examiner les conseils du Segment Advisor. Le script ci-dessous permet de créer les objets du schéma.

create table DEMO#7 (col1 number primary key, col2 number, col3 varchar2(4000)) tablespace users;

begin
for i in 1..10000 loop
for j in 1..10 loop
insert into DEMO#7 values ((j-1)*10000+i, j,
'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;
/

delete from demo#7 where col2>1;

commit;

exec dbms_stats.gather_table_stats(USER, 'DEMO#7', cascade=>true,-
method_opt=>'FOR ALL COLUMNS SIZE AUTO');

Utilisation du Segment Advisor
Le segment advisor est utilisé à partir du package DBMS_ADVISOR. les vues DBA_ADVISOR_OBJECTS et DBA_ADVISOR_FINDINGS permettent d'afficher les conseils. Dans le cas qui suit, l'Advisor indique que la table peut être réduire au moyen d'un SHRINK et que l'espace gagné serait de 101 Mo environ.

VARIABLE ID NUMBER;
BEGIN
DECLARE
TASK_ID NUMBER;
NAME VARCHAR2(100) ;
DESCR VARCHAR2(500) ;
OBJID NUMBER;
BEGIN
NAME := 'DEMO#7';
DESCR := 'SEGMENT ADVISOR ON A TABLE DEMO#7';
DBMS_ADVISOR.CREATE_TASK('Segment Advisor', :ID, NAME, DESCR, NULL);
DBMS_ADVISOR.CREATE_OBJECT(NAME, 'TABLE', USER,'DEMO#7', NULL, NULL, OBJID);
DBMS_ADVISOR.SET_TASK_PARAMETER(NAME, 'RECOMMEND_ALL', 'TRUE');
DBMS_ADVISOR.EXECUTE_TASK(NAME);
END;
END;
/


set lines 120
col task_name format A10
col segname format A10
col type format A10
select af.task_name, ao.attr2 segname, ao.type, af.message
from dba_advisor_findings af, dba_advisor_objects ao
where ao.task_id = af.task_id
and ao.object_id = af.object_id
and ao.attr2='DEMO#7';

TASK_NAME SEGNAME TYPE
---------- ---------- ----------
MESSAGE
---------------------------------------
DEMO#7 DEMO#7 TABLE
Activez le déplacement de lignes de la table SYSTEM.DEMO#7 et effectuez une
réduction ; ceci devrait permettre d'économiser 101809859 octets.

Taille du segment et SHRINK
Dans le script qui suit, nous allons vérifier la taille du SEGMENT DEMO#7 puis effectuer le SHRINK et valider que l'espace promis est gagné. Pour pouvoir effectuer le shrink, il faut que le tablespace dans lequel il est effectué soit Locally Managed. Il faut également que la table ait "ROW MOVEMENT ENABLED".

select BYTES/1024/1024 SIZE_MB
from dba_segments
where owner=user and SEGMENT_NAME='DEMO#7';

SIZE_MB
----------
120

alter table demo#7 enable row movement;
alter table demo#7 shrink space;

select BYTES/1024/1024 SIZE_MB
from dba_segments
where owner=user and SEGMENT_NAME='DEMO#7';

SIZE_MB
----------
11,4375

Pour terminer, supprimer la tâche associée au Segment Advisor et la table DEMO#7 :

exec DBMS_ADVISOR.DELETE_TASK('DEMO#7')
drop table DEMO#7 purge;

GarK!

Exemple d'ordre SQL #5 et #6 /*+Anti-Pattern*/

Dans l'exemple qui suit, nous allons illustrer deux possibilités offertes par le SQL Tuning Advisor. Il donne ci-dessous des conseils qui concernent la manière dont l'ordre SQL est écrit.

Schema d'exemple
Dans un premier temps, nous allons créer une table demo#6 avec le script ci-dessous :

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

begin
for j in 1..100000 loop
insert into demo#6 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;
commit;
end;
/

exec dbms_stats.gather_table_stats(USER, 'DEMO#6', -
cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 254');

SQL*Tuning Advisor #1
Nous allons lancer le SQL Tuning Advisor avec la requête "select * from demo#6 where col1=1 union select * from demo#6 where col1=2". Le Tuning Advisor est capable de donner des conseils à propos de la syntaxe SQL comme vous pouvez le constater.

exec dbms_sqltune.drop_tuning_task( task_name => 'DEMO#6' );
variable gg varchar2(100)
exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
sql_text => 'select * from demo#6 where col1=1 union select * from demo#6 where col1=2',-
user_name => 'SYSTEM', -
scope => 'COMPREHENSIVE', -
time_limit => 30, -
task_name => 'DEMO#6', -
description => 'Tuning d''un accès simple à la table DEMO#6');

exec dbms_sqltune.execute_tuning_task( task_name => 'DEMO#6' );

set long 10000
set longchunksize 10000
set lines 100
select dbms_sqltune.report_tuning_task('DEMO#6')
from dual;


DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#6')
--------------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------
Tuning Task Name : DEMO#6
Tuning Task Owner : SYSTEM
Scope : COMPREHENSIVE
Time Limit(seconds) : 30
Completion Status : COMPLETED
Started at : 02/04/2007 17:56:53
Completed at : 02/04/2007 17:56:55
Number of SQL Restructure Findings: 1


DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#6')
--------------------------------------------------------------------------
--------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID : 4am69yrt6d6y3
SQL Text : select * from demo#6 where col1=1 union select * from demo#6
where col1=2

--------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
--------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#6')
--------------------------------------------------------------------------
----------------------------------------------------------------
Une opération "UNION" coûteuse a été trouvée à l'ID de ligne 1 du plan
d'exécution.

Recommendation
--------------
- Si les doubles sont autorisés ou que l'unicité est garantie, envisagez
l'utilisation de "UNION ALL" à la place de "UNION".

Rationale
---------

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#6')
--------------------------------------------------------------------------
"UNION" est une opération coûteuse et bloquante car elle exige
l'élimination des lignes en double. "UNION ALL" est un choix moins
coûteux, à condition que les doubles soient autorisés ou que l'unicité
soit garantie.

--------------------------------------------------------------------------
EXPLAIN PLANS SECTION
--------------------------------------------------------------------------
[...]


SQL*Tuning Advisor #2
Nous allons lancer le SQL Tuning Advisor avec la requête "select * from demo#6 where col2=1". Le Tuning Advisor est capable de donner des conseils à propos de l'indexation comme vous le voyez ci-dessous.

exec dbms_sqltune.drop_tuning_task( task_name => 'DEMO#6b' );
variable gg varchar2(100)
exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
sql_text => 'select * from demo#6 where col2=1',-
user_name => 'SYSTEM', -
scope => 'COMPREHENSIVE', -
time_limit => 30, -
task_name => 'DEMO#6b', -
description => 'Tuning d''un accès simple à la table DEMO#6b');

exec dbms_sqltune.execute_tuning_task( task_name => 'DEMO#6b' );

set long 10000
set longchunksize 10000
set lines 100
select dbms_sqltune.report_tuning_task('DEMO#6b')
from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#6B')
------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
------------------------------------------------------------------------------
Tuning Task Name : DEMO#6b
Tuning Task Owner : SYSTEM
Scope : COMPREHENSIVE
Time Limit(seconds) : 30
Completion Status : COMPLETED
Started at : 02/04/2007 18:12:42
Completed at : 02/04/2007 18:12:42
Number of Index Findings : 1


DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#6B')
------------------------------------------------------------------------------
------------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID : 5mjr2d9h58tux
SQL Text : select * from demo#6 where col2=1

------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#6B')
------------------------------------------------------------------------------
Le plan d'exécution de cette instruction peut Ûtre amélioré en créant un ou
plusieurs index.

Recommendation (estimated benefit: 100%)
----------------------------------------
- Envisagez d'exécuter Access Advisor pour améliorer la conception du
schéma physique ou de créer l'index recommandé.
create index SYSTEM.IDX$$_002A0001 on SYSTEM.DEMO#6('COL2');

Rationale
---------

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#6B')
------------------------------------------------------------------------------
La création des index recommandés améliore de faþon considérable le plan
d'exécution de cette instruction. Il pourrait cependant être préférable
d'exécuter "Access Advisor" en utilisant une charge globale SQL
représentative contrairement à une seule instruction. Ceci permettra
d'obtenir des recommandations d'index complètes prenant en compte le coût
de maintenance des index et de la consommation d'espace supplémentaire.

------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
------------------------------------------------------------------------------
[...]

Avec ce qui précède, voici deux exemples de plus des recommandations du SQL Tuning Advisor (et presque complètement en français...)

GarK!

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=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;

COUNT(D5.COL7)
--------------
1000000

Ecoulé : 00 :00 :00.75

Plan d'exécution

--------------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------------

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!

Events 10046 et 10053

Deux types de traces sont très utiles si vous utilisez Oracle 10g et que vous voulez "tuner" des requêtes SQL...

• L'évènement 10046 est équivalente à SQL_TRACE=TRUE. Elle permet d'ajouter plus d'informations comme le contenu des variables de type BIND (level 4), les informations de type wait events (level 8) et les 2 (level 12). Le fichier situé dans le répertoire correspondant au paramètre user_dump_dest peut alors être utilisé avec TKPROF
  
• L'évènement 10053 permet de tracer le calcul du CBO et d'expliquer ainsi pourquoi un plan est choisi plutôt qu'un autre.

Pour positionner ces évènements il y a plusieurs possibillités; pour la session en cours, vous pouvez simplement utiliser :

SQL> alter session set events '10046 trace name context forever, level 4';
SQL> alter session set events '10053 trace name context forever, level 1';
Pour arrêter les traces utilisez :
SQL> alter session set events '10046 trace name context off';
SQL> alter session set events '10053 trace name context off';

Si vous cherchez à tracer des sessions différentes, utilisez les packages DBMS_SUPPORT ou DBMS_MONITOR (Event 10046) comme ci-dessous :
SQL> exec DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> exec DBMS_SUPPORT.stop_trace;
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id =>1234, serial_num=>1234);

Vous pouvez également utiliser le package suivant :
SQL> exec dbms_system.set_ev(sid, serial#, 10046, 4)
SQL> exec dbms_system.set_ev(sid, serial#, 10046, 0)

GarK!

Exemple d'ordre SQL #3 /*+Anti-Pattern*/

Dans l'exemple qui suit, nous allons illustrer que le SQL Tuning Advisor ne descend pas dans le détail de comment les statistiques sont capturées. Cet exemple a été réalisé avec Oracle 10.2.0.3 sur Windows.

Dans un premier temps, nous allons créer une table demo#3 avec le script ci-dessous :

• La table demo#3 a des statistiques a jour puisque prise après le remplissage de la table. En revanche la stratégie par défaut ne prend pas d'histogramme sur les colonnes à moins qu'elles aient été préalablement utilisées dans une clause WHERE.
  
• Les données de la colonne COL1 sont indexées. Elles sont distribuées de manière non homogène puisque la valeur 1 représente en fréquence la moitié des valeurs de la colonne.

SQL> create table demo#3 (id number primary key,
col1 number,
col2 varchar2(4000));

create index demo#3_col1_idx on demo#3(col1);

begin
for i in 1..50000 loop
insert into demo#3 values (i,1,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');

insert into demo#3 values (50000+i,1+i,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
/
exec dbms_stats.gather_table_stats(USER, 'DEMO#3' -
, cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

SQL*Tuning Advisor
Nous allons utiliser le SQL Tuning Advisor pour voir si la requête "select count(col2) from demo#3 where col1=1" peut être améliorée.

SQL> variable gg varchar2(100)
SQL> exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
    sql_text    => 'select count(col2) from demo#3 where col1=1', -
    user_name   => 'SYSTEM', -
    scope       => 'COMPREHENSIVE', -
    time_limit  => 30, -
    task_name   => 'DEMO#3', -
    description => 'Tuning d''un accès simple à la table DEMO#3');
SQL> exec dbms_sqltune.execute_tuning_task( task_name => 'DEMO#3' );

Pour affichez le résultat du SQL Tuning Advisor, tapez :

SQL > set long 10000
SQL > set longchunksize 10000
SQL > set lines 100
SQL > select dbms_sqltune.report_tuning_task('DEMO#3')
   from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#3')
----------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------
Tuning Task Name   : DEMO#3
Tuning Task Owner  : SYSTEM
Scope              : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status  : COMPLETED
Started at         : 02/03/2007 16:18:25
Completed at       : 02/03/2007 16:18:27

----------------------------------------------------------
Schema Name: SYSTEM
SQL ID     : 2s4vfqtcpcw5v
SQL Text   : select count(col2) from demo#3 where col1=1

----------------------------------------------------------
There are no recommendations to improve the statement.

----------------------------------------------------------
SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('DEMO#3');

Et pourtant...
Il n'y a pas de recommandation et pourtant, si on exécute la requête, on s'aperçoit qu'elle passe par l'index alors qu'il serait plus efficace d'effectuer un FULL TABLE SCAN. J'aime bien l'idée selon laquelle un index n'est pas toujours utile et même contre-productif.

SQL> set autotrace on
SQL> select count(col2) from demo#3 where col1=1

COUNT(COL2)
-----------
50000

EcoulÚ : 00 :00 :31.18

Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2470349637

------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1005 |
| 1 | SORT AGGREGATE | | 1 | 1005 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEMO#3 | 29 | 29145 |
|* 3 | INDEX RANGE SCAN | DEMO#3_COL1_IDX | 29 | |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("COL1"=1)

Statistiques
----------------------------------------------------------
1 recursive calls
0 db block gets
21745 consistent gets
5723 physical reads
524724 redo size
422 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Améliorer les plans grâce à la stratégie par défaut de prise de statistiques

La stratégie par défaut de prise des statistiques utilise le monitoring des tables pour améliorer automatiquement la prise de statistiques. C'est ce qui se passe si on reprend les statistiques après avoir exécuté la requête comme ceci :

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYSTEM', -
tabname => 'DEMO#2', -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
cascade => TRUE);

On s'aperçoit que le plan a changé et que l'index n'est plus utilisé :

SQL> set autotrace on
SQL> select count(col2)
from demo#2
where col1=1;

COUNT(COL2)
-----------
 50000


Plan d'exécution
----------------------------------------------------------
Plan hash value: 559471190

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  1005 |  3149   (1)| 00:00:38 |
|   1 |  SORT AGGREGATE    |        |     1 |  1005 |            |          |
|*  2 |   TABLE ACCESS FULL| DEMO#2 | 49606 |    47M|  3149   (1)| 00:00:38 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("COL1"=1)


Statistiques
----------------------------------------------------------
     1  recursive calls
     0  db block gets
 14297  consistent gets
  7422  physical reads
     0  redo size
   422  bytes sent via SQL*Net to client
   396  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     1  rows processed

Pourquoi ?
La raison de ce changement est que que la distribution des données dans la colonne COL1 est désormais enregistrée et visible grâce la la vue DBA_HISTOGRAMS comme le montre la requête ci-dessus :

SQL> select ENDPOINT_NUMBER, ENDPOINT_VALUE
from dba_histograms
where OWNER=USER and TABLE_NAME='DEMO#3' and COLUMN_NAME='COL1';

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              1
              1          49999

GarK!

Exemple d'ordre SQL #2 /*+Anti-Pattern*/

Les statistiques sont probablement une des clés les plus importantes du bon fonctionnement d'Oracle 10g. En effet l'optimiseur dit "CBO : Cost Based Optimizer" calcule les plans d'exécution des requêtes SQL en fonction de la manière dont les données sont distribuées. Dans l'exemple qui suit, vous allez voir les effets des statistiques. Cette exemple a été réalisé avec Oracle 10.2.0.3 sur Windows.

SQL> create table demo#2 (id number primary key,
col1 number,
col2 varchar2(4000));

create index demo#2_col1_idx on demo#2(col1);

exec dbms_stats.gather_table_stats(USER, 'DEMO#2' -
, cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

begin
for i in 1..50000 loop
insert into demo#2 values (i,1,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');

insert into demo#2 values (50000+i,1+i,
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'||
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
/

Quelques remarques à propos du script qui précède :

• La table demo#2 a des statistiques éronnées puisqu'elle ont été prise alors que la table est vide
• Les données de la colonne COL1 sont indexées. Elles sont distribuées de manière non homogène puisque la valeur 1 représente en fréquence la moitié des valeurs de la colonne.

SQL*Tuning Advisor
Comme dans le Post précédent nous allons utiliser le SQL Tuning advisor pour cette fois voir si l'instance ne peut pas gérer dans les meilleurs condition l'ordre "SELECT count(COL2) FROM demo#2 where col1=1". Comme dans le Post précédent, exécutez le script correspondant :

SQL> variable gg varchar2(100)
SQL> exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
      sql_text    => 'select count(col2) from demo#2 where col1=1', -
      user_name   => 'SYSTEM', -
      scope       => 'COMPREHENSIVE', -
      time_limit  => 30, -
      task_name   => 'DEMO#2', -
      description => 'Tuning d''un accès simple à la table DEMO#2');
SQL> exec dbms_sqltune.execute_tuning_task( task_name => 'DEMO#2' );

Pour affichez le résultat du SQL Tuning Advisor, tapez :

SQL > set long 10000
SQL > set longchunksize 10000
SQL > set lines 100
SQL > select dbms_sqltune.report_tuning_task('DEMO#2')
     from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#2')
----------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
Tuning Task Name                  : DEMO#2
Tuning Task Owner                 : SYSTEM
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 30
Completion Status                 : COMPLETED
Started at                        : 02/03/2007 15:14:45
Completed at                      : 02/03/2007 15:15:01
Number of Statistic Findings      : 1

----------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID     : 0f0qzt2ydjvqj
SQL Text   : select count(col2) from demo#2 where col1=1

----------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
----------------------------------------------------------------------------

1- Statistics Finding
---------------------
Les statistiques de l'optimiseur pour la table "SYSTEM"."DEMO#2" sont
obsolÞtes.

Recommendation
--------------
- Envisagez de collecter des statistiques d'optimiseur pour cette table et
 ses index.
 execute dbms_stats.gather_table_stats(ownname => 'SYSTEM', tabname =>
         'DEMO#2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
         method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Rationale
---------
 L'optimiseur exige des statistiques à jour de la table et de ses index
 afin de sélectionner un bon plan d'exécution.

----------------------------------------------------------------------------
EXPLAIN PLANS SECTION
----------------------------------------------------------------------------
[...]

SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('DEMO#2');

Le SQL Tuning Advisor conseille effectivement d'exécutez la prise de statistiques.

Prise de statistiques
Si vous prenez les statistiques comme recommandé, et que vous regardez le plan d'exécution de l'ordre SQL, vous constatez que vous passez par l'index.

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYSTEM', -
      tabname => 'DEMO#2', -
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
      method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
      cascade => TRUE);

SQL> set autotrace on
SQL>  select count(col2)
  from demo#2
  where col1=1;

COUNT(COL2)
-----------
   50000


Plan d'exécution
----------------------------------------------------------
Plan hash value: 559471190

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  1005 |  3149   (1)| 00:00:38 |
|   1 |  SORT AGGREGATE    |        |     1 |  1005 |            |          |
|*  2 |   TABLE ACCESS FULL| DEMO#2 | 49606 |    47M|  3149   (1)| 00:00:38 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("COL1"=1)


Statistiques
----------------------------------------------------------
       1  recursive calls
       0  db block gets
   14297  consistent gets
    7422  physical reads
       0  redo size
     422  bytes sent via SQL*Net to client
     396  bytes received via SQL*Net from client
       2  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       1  rows processed

Remarque à propos des index
Vous remarquerez que dans ce cas le passage par l'index est moins performant. Exécutez plusieurs fois la requête ci-dessous pour vous en persuadez :

SQL> set timing on
SQL> set autotrace off
SQL> select /*+FULL(demo#2)*/ count(col2)
    from demo#2
    where col1=1;

COUNT(COL2)
-----------
      50000

Ecoulé : 00 :00 :00.06

SQL> select /*+INDEX(demo#2)*/ count(col2)
    from demo#2
    where col1=1;

COUNT(COL2)
-----------
      50000

Ecoulé : 00 :00 :00.10

Voilà... Dans un prochain "Post" vous verrez que le SQL Tuning Advisor ne descend pas dans le détail de comment passer les statistiques.

GarK!

10.2 Tuning Session

Dans ce qui suit, l'utilisation d'AWR, d'ADDM et du SQL*Tuning advisor permet de découvrir et d'expliquer le problème associé à l'application du "Post" précédent. A travers cet exemple, il est effectué une présentation pratique et rapide de plusieurs outils des Packs diagnostic et tuning inclus dans Oracle 10g. Ce qui suit a été réalisés avec Oracle 10.2.0.3 sur Windows.

Paramétre AWR
Le paramètre d'instance statistics_level fixe le niveau d'information conservé dans AWR.

SQL> show parameter statistics_level

NAME TYPE VALUE
-------------------------- ----------- -------
statistics_level string TYPICAL

Création du schéma de l'application
Pour les besoin de la démonstration, créez et la table DEMO#1, remplissez là et prenez les statistiques comme décrit dans le "Post" précédent.

Clichés AWR
Le principe de fonctionnement des clichés AWR est très semblable à celui du Statspack. Le package DBMS_WORKLOAD_REPOSITORY permet de les manipuler. Une première différence réside dans le fait que par défaut, la base de données 10.2 effectue un cliché AWR toutes les heures et conserve un historique de 7 jours. Pour notre exemple, nous allons déclencher 2 clichés. Un premier avant de lancer la charge SQL et un second juste après. Pour déclencher le cliché, tapez la commande suivante :

SQL> exec dbms_workload_repository.create_snapshot();

SQL> col END_INTERVAL_TIME format A25
SQL> select SNAP_ID, END_INTERVAL_TIME, SNAP_LEVEL
from DBA_HIST_SNAPSHOT
where STARTUP_TIME > systimestamp - 1/24
order by snap_id;

SNAP_ID END_INTERVAL_TIME SNAP_LEVEL
---------- ------------------------- ----------
5 03/02/07 09:55:06,093 1
6 03/02/07 10:10:46,000 1

Démarrez la charge applicative
L'objectif de ADDM est de découvrir les problèmes des instances et du SQL généré par l'application. Il faut que que ce problème soit significatif pour être signalé. Dans le Post précédent, il est présenté un ordre qui met 0.14 seconde et qui pourrait être exécuté plus rapidement... si seulement... Cet ordre ne sera présenté comme un problème par ADDM que si son impact est important. La charge que vous devez exécuter doit donc utiliser de nombreuse fois cet ordre pour être significative. Dans une seconde fenêtre SQL*Plus lancez donc le script ci-dessous :

SQL> set timing on
SQL> declare
gg varchar2(4000);
begin
for i in 1..10000 loop
select lib into gg
from demo#1
where id=1;
end loop;
end;
/

Procédure PL/SQL terminée avec succès.

Ecoulé :00:02:15.07

Second cliché AWR
Afin de lancer ADDM, il faut créer un second cliché AWR. Comme pour le premier, effectuez les opérations ci-dessous :

SQL> exec dbms_workload_repository.create_snapshot();

SQL> col END_INTERVAL_TIME format A25
SQL> select SNAP_ID, END_INTERVAL_TIME, SNAP_LEVEL
from DBA_HIST_SNAPSHOT
where STARTUP_TIME > systimestamp - 1/24
order by snap_id;

SNAP_ID END_INTERVAL_TIME SNAP_LEVEL
---------- ------------------------- ----------
5 03/02/07 09:55:06,093 1
6 03/02/07 10:10:46,000 1
7 03/02/07 10:40:57,375 1

Exécutez et visualisez les préconisations ADDM
Une fois le second cliché effectué, il faut lancez ADDM pour connaître les préconisations de la base de données 10.2 en ce qui concerne la charge constatée. Par défaut, il existe une tâche ADDM qui s'exécute après chaque cliché AWR. Une fois le second cliché effectué, vous aurez donc automatiquement le rapport ADDM correspondant. Pour visualiser le derniez rapport ADDM, exécutez ce qui suit :

SQL> set long 1000000
SQL> set pagesize 50000
SQL> column get_clob format a80
SQL> select dbms_advisor.get_task_report(
task_name, 'TEXT', 'ALL')
as ADDM_report
from dba_advisor_tasks
where task_id=(
select max(t.task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name='ADDM'
and l.status= 'COMPLETED');

ADDM_REPORT
------------------------------------------------------------------------
DETAILED ADDM REPORT FOR TASK 'ADDM:1139312629_1_7' WITH ID 13
--------------------------------------------------------------

Analysis Period: 03-F╔VR.-2007 from 10:10:46 to 10:40:57
Database ID/Instance: 1139312629/1
Database/Instance Names: ORCL/orcl
Host Name: AOFR12992
Database Version: 10.2.0.3.0
Snapshot Range: from 6 to 7
Database Time: 1630 seconds
Average Database Load: ,9 active sessions

[...]


FINDING 3: 91% impact (1485 seconds)
------------------------------------
Des instructions SQL particulières responsables d'une attente d'E/S
utilisateur importante ont été trouvées.

RECOMMENDATION 1: SQL Tuning, 100% benefit (1650 seconds)
ACTION: Exécutez SQL Tuning Advisor pour l'instruction SQL dont SQL_ID
est "34fxbmz5516x3".
RELEVANT OBJECT: SQL statement with SQL_ID 34fxbmz5516x3 and
PLAN_HASH 3966067846
SELECT LIB FROM DEMO#1 WHERE ID=1
RATIONALE: L'instruction SQL o¨ SQL_ID = "34fxbmz5516x3" a été exécutée
853 fois, avec un temps ÚcoulÚ moyen de 1.9 secondes.
RATIONALE: Le temps moyen consommé pour les événements d'attente d'E/S
utilisateur par exécution a été de 1.7 secondes.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: La classe d'attente "E/S utilisateur" a consommÚ une part
importante du temps base de donnÚes. (91% impact [1490
seconds])
[...]

Exécutez le SQL*Tuning Advisor
Maintenant que vous avez détecté un ordre SQL qui pose problème, vous pouvez lancer le SQL Tuning Advisor pour le solutionner. Pour exécutez le SQL Tuning Advisor, on crée une tâche correspondante et on l'exécute comme ci-dessous :

SQL> variable gg varchar2(100)
SQL> exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
         sql_text    => 'select lib from demo#1 where id=1', -
         user_name   => 'SYSTEM', -
         scope       => 'COMPREHENSIVE', -
         time_limit  => 30, -
         task_name   => 'DEMO#1', -
         description => 'Tuning d''un accès simple à la table DEMO#1');
SQL> exec dbms_sqltune.execute_tuning_task( task_name => 'DEMO#1' );

Pour affichez le résultat du SQL Tuning Advisor, tapez :

SQL > set long 10000
SQL > set longchunksize 10000
SQL > set lines 100
SQL > select dbms_sqltune.report_tuning_task('DEMO#1') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#1')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : DEMO#1
Tuning Task Owner                 : SYSTEM
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 30
Completion Status                 : COMPLETED
Started at                        : 02/03/2007 11:02:54
Completed at                      : 02/03/2007 11:02:59
Number of Index Findings          : 1
Number of SQL Restructure Findings: 1

-------------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID     : 6kjb7f5600zxj
SQL Text   : select lib from demo#1 where id=1

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
------------------------------------------------------------------------------
[...]
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Le prédicat TO_NUMBER("DEMO#1"."ID")=1 utilisé à l'ID de ligne 1 du plan
  d'exécution contient une conversion de type de données implicite sur la
  colonne indexée "ID". Cette conversion de type de données implicite empéche
  l'optimiseur de sélectionner les index sur la table "SYSTEM"."DEMO#1".

  Recommendation
  --------------
  - Réécrivez le prédicat sous une forme équivalente pour bénéficier des index.

  Rationale
  ---------
    L'optimiseur ne peut pas utiliser d'index si le prédicat est une condition
    d'inégalité ou qu'il existe une expression ou une conversion de type de
    données implicite sur la colonne indexée.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
[...]

Conclusion
Remplacez l'ordre :
SQL> select lib into gg from demo#1 where id=1;
par
SQL> select lib into gg from demo#1 where id='1';

Le résultat est le suivant
SQL> set timing on
SQL> declare
   gg varchar2(4000);
   begin
   for i in 1..10000 loop
   select lib into gg
   from demo#1
   where id='1';
   end loop;
   end;
   /

Procédure PL/SQL terminée avec succès.

Ecoulé :00:00:00.53

Soit une amélioration de plus de 2 heures en temps "elapse". Impressionnant ?

Avant de terminer...
Supprimez la tâche associée au SQL Tuning advisor en lançant la commande ci-dessous :
SQL> exec dbms_sqltune. drop_tuning_task('DEMO#1')

Dans les prochains "Post", j'illustrerai d'autres problèmes ainsi que la manière dont les Diagnostic et Tuning Packs les résolvent.

GarK!