Blog

le 16/09/2012
Thomas Bordet
Archives blog-arkzoyd @fr

1 SQL et 2 Plans d'Exécution ? Cherchez la "REASON"

Il y a de nombreuses raisons pour lesquelles votre base de données Oracle peut donner des plans différents pour le même ordre SQL. Il suffit de regarder la définition de V$SQL_SHARED_CURSOR pour s’en persuader.

Ces changements peuvent évidemment être dus à des statistiques différentes sur les objets mais pas seulement… Les paramètres peuvent être différents de même que les objets/schémas. Il arrive que les valeurs ou les types des variables bind impactent le calcul des plans. Il se peut que les contextes d’exécution comme ceux de la VPD ou le nombre de process parallèles disponibles soient différents. Autrement dit, il y a quelques bonnes raisons que détaille depuis la version 11.2.0.2, la colonne REASON de la vue V$SQL_SHARED_CURSOR pour identifier la cause de ce changement. Avant cette version, vous devrez identifier, à l’aide de la colonne adéquate de la-dite vue, la cause de cette différence.

Mais revenons, à ce qui peut être un problème, ou pas : un changement de plan… Une idée assez immédiate dans ce type de situation, lorsque l’évènement ne passe pas inaperçu, consiste à fixer le plan. Seulement voilà, cette idée n’est pas nécessairement une bonne idée :

D’abord parce que fixer un plan est rarement une bonne idée ; sinon pourquoi y aurait-il un hint pour ignorer les hints ? cf IGNORE_OPTIM_EMBEDDED_HINTS
Ensuite parce que le plan que vous voulez fixer n’est peut-être simplement pas utilisable.

C’est un exemple de ce second cas qu’illustre cet article ; cas pour lequel, les structures sont les mêmes, les algorithmes et paramètres de l’optimiseurs sont les mêmes mais pour lequel pourtant, vous ne pouvez pas utiliser un index…
Pour notre exemple, voici une table T1 et un index :

drop table t1 purge;
create table t1 (id varchar2(10), text varchar2(1000));
begin
  for i in 1..26 loop
    insert into t1 
       values (chr(64+i), 'Test '||chr(64+i));
  end loop;
end;
/
create index t1_idx on t1(id);
exec dbms_stats.gather_table_stats(user,'T1')

Comme vous pouvez le voir dans la section ci-dessous, l’ordre SQL utilise l’index T1_IDX :

sqlplus / as sysdba
alter session set nls_comp=linguistic;
alter session set nls_sort=binary;
alter system flush shared_pool;

select count(*)
  from t1
 where id<'F';

col sqlid new_value sqlid
select prev_sql_id sqlid
  from v$session 
 where sid=sys_context('userenv','sid');

prompt &&sqlid

set lines 80
set pages 1000
set tab off
select * 
  from table(dbms_xplan.display_cursor(
                  sql_id=>'&&sqlid',
                  format=>'last basic +predicate'));

PLAN_TABLE_OUTPUT
----------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(*)   from t1  where id<'F'

Plan hash value: 383751810

------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|   1 |  SORT AGGREGATE   |        |
|*  2 |   INDEX RANGE SCAN| T1_IDX |
------------------------------------

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

   2 - access("ID"<'F')

Si le paramètre nls_sort est positionné à FRENCH, la fonction NLSSORT est implicitement appliquée à la colonne ID. Autrement dit, l’index ne peut pas être utilisé :

alter session set nls_sort=french;

select count(*)
  from t1
 where id<'F';

set lines 80
set pages 1000
set tab off
select * 
  from table(dbms_xplan.display_cursor(
                  format=>'last basic +predicate'));

PLAN_TABLE_OUTPUT
----------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(*)   from t1  where id<'F'

Plan hash value: 3724264953

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|*  2 |   TABLE ACCESS FULL| T1   |
-----------------------------------

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

   2 - filter(NLSSORT("ID",'nls_sort=''FRENCH''')<HEXTORAW('2D000100') )

Dans la mesure où les 2 plans sont dans la shared_pool, vous pouvez interroger la colonne REASON de la table V$SQL_SHARED_CURSOR pour confirmer que le problème est lié aux paramétres NLS :

set lines 80
col reason format a80 wor wra
set long 1000
set longchunksize 1000
select sql_id, child_number, reason
  from v$sql_shared_cursor 
 where sql_id='&&sqlid';

SQL_ID        CHILD_NUMBER
------------- ------------
REASON
--------------------------------------------------------------------------------
3rzwn44nagq8f            0
<ChildNode><ChildNumber>0</ChildNumber><ID>45</ID><reason>NLS Settings(0)</reaso
n><size>2x4</size><SessionLengthSemantics>0</SessionLengthSemantics><CursorLengt
hSemantics>0</CursorLengthSemantics></ChildNode>

3rzwn44nagq8f            1

Si vous cherchez à forcer le plan, vous constaterez que l’index peut ne pas être utilisé :

select /*+index(t1)*/ count(*)
  from t1
 where id<'F';

set lines 80
set pages 1000
set tab off
select * 
  from table(dbms_xplan.display_cursor(
                  format=>'last basic +predicate'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+index(t1)*/ count(*)   from t1  where id<'F'

Plan hash value: 3724264953

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|*  2 |   TABLE ACCESS FULL| T1   |
-----------------------------------

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

   2 - filter(NLSSORT("ID",'nls_sort=''FRENCH''')<HEXTORAW('2D000100') )

Vous pourrez vérifier qu’en imposant une contrainte NOT NULL, l’index peut désormais être utilisé :

alter table t1 modify (id not null);

select /*+index(t1)*/ count(*)
  from t1
 where id<'F';

set lines 80
set pages 1000
set tab off
select * 
  from table(dbms_xplan.display_cursor(
                  format=>'last basic +predicate'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
--------------------
----
select /*+index(t1)*/ count(*)   from t1  where id<'F'

Plan hash value: 3656771984

-----------------------------------
| Id  | Operation        | Name   |
-----------------------------------
|   0 | SELECT STATEMENT |        |
|   1 |  SORT AGGREGATE  |        |
|*  2 |   INDEX FULL SCAN| T1_IDX |
-----------------------------------

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

   2 - filter(NLSSORT("ID",'nls_sort=''FRENCH''')<HEXTORAW('2D000100') )

Autrement dit, fixer un plan, n’est pas toujours possible, quoiqu’on en dise…

Thomas Bordet

Voir tous ses articles

Blog

1 SQL et 2 Plans d'Exécution ? Cherchez la "REASON"

Articles récents