V$SQL_BIND_CAPTURE, SQL Tuning Set (STS) et SQL Performance Analyzer (SPA)

Oracle Database offre, à partir de 10g, une vue pour collecter simplement les valeurs des variables bind de vos ordres SQL; Cette vue nommée V$SQL_BIND_CAPTURE permet, sans trop d'effort, de connaître le contexte d'utilisation de votre requête et donc de mieux appréhender certains problèmes en vous aidant à constituer des jeux de tests plus facilement. Oracle 11g propose, en plus de la capacité précédente, une function pipeline dbms_sqltune.extract_binds pour visualiser le contenu des colonnes BIND_DATA que vous retrouvez dans le type SYS.SQLSET ou la vue v$sqlarea. Dans cet article, je vous propose d'illustrer ces fonctionnalités mais surtout de montrer certaines des limites que j'ai rencontré récemment avec les SQL Tuning Set (STS) et SQL Performance Analyzer (SPA)

Note:
Les tests ci-dessous ont été réalisés avec une base de données 11.2.0.1 sous Linux x86 32bits. Il est possible que le comportement soit différent avec d'autres versions.

Un exemple simple

Commençons par illustrer la fonctionnalité et l'intérêt de V$SQL_BIND_CAPTURE avec un exemple simple et où tout fonctionne. Pour commencer, nous allons créer une table t:

drop table t purge;

create table t(id number,
col2 varchar2(1000));

create index tidx on t(id);

insert into t
(select rownum, rpad('X',1000,'Y')
from dual connect by level <= 1000);   insert into t  (select 1001, rpad('X',1000,'Y')  from dual connect by level <= 1000);  commit;  exec dbms_stats.gather_table_stats(user, 'T', -  method_opt => 'for all columns size 254');

Une fois la table créée, exécutez une requête avec des variables bind:

alter system flush shared_pool;
alter session set statistics_level=typical;

var a number
var b number
exec :a := 1
exec :b := 2

select /* arkzoyd_1 */ count(col2)
from t
where id=:a and 2=:b;

COUNT(COL2)
-----------
 1

Recherchons maintenant les requêtes et les valeurs de bind dans les différentes tables:

col sql_id format a13 new_value sql_id
col sql_text format a80
set lines 100
select s.sql_id, s.sql_text
from v$sql s
where sql_text like '%arkzoyd\_1%' escape '\';

SQL_ID       SQL_TEXT
------------- -----------------------------------------------------------------
aw83x5fn3khq5 select /* arkzoyd_1 */ count(col2)   from t  where id=:a and 2=:b

col value_string format a10
col was_capture format a3
col name format a6
col datatype_string format a15
set heading on
select c.was_captured, c.name, c.datatype_string, c.VALUE_STRING
 from v$sql_bind_capture c
where c.sql_id='&&sql_id';

WAS NAME   DATATYPE_STRING VALUE_STRI
--- ------ --------------- ----------
YES :A    NUMBER    1
YES :B    NUMBER    2

Les données de bind peeking stockées avec le plan d'exécution et disponibles dans la colonne other_xml de v$sql_plan ne concernent pas toutes les valeurs de bind mais simplement celles qui influencent le plan; vous demandiez-vous pourquoi on avait collecté les histogrammes sur toutes les colonnes de notre table ? Voilà comment afficher les valeurs des variables bind utilisées dans le cadre du bind peeking:

set pages 1000
set lines 100

select *
from table(
dbms_xplan.display_cursor(
        sql_id=>'&&sql_id',
        format=>'basic peeked_binds'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* arkzoyd_1 */ count(col2)   from t  where id=:a and 2=:b

Plan hash value: 2029148709

----------------------------------------------
| Id  | Operation        | Name |
----------------------------------------------
|   0 | SELECT STATEMENT       |      |
|   1 |  SORT AGGREGATE        |      |
|   2 |   FILTER        |      |
|   3 |    TABLE ACCESS BY INDEX ROWID| T    |
|   4 |     INDEX RANGE SCAN       | TIDX |
----------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 1

Un exemple moins propisce

C'est écrit dans la documentation de V$SQL_BIND_CAPTURE, mais encore faut-il la lire avec attention! Seules les valeurs de bind incluses dans les clauses WHERE et HAVING sont capturées.

Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement.

Dans ce second exemple, la valeur de bind n'est donc pas collectée par V$SQL_BIND_CAPTURE:

alter system flush shared_pool;
alter session set statistics_level=typical;

var a number
exec :a := 1001

select /* arkzoyd_2 */ count(col2)
 from t
where id in (select :a from dual);

col sql_id format a13 new_value sql_id
col sql_text format a80
set lines 100
select s.sql_id, s.sql_text
 from v$sql s
where sql_text like '%arkzoyd\_2%' escape '\';

SQL_ID       SQL_TEXT
------------- --------------------------------------------------------------------------------
40r85sn1b20na select /* arkzoyd_2 */ count(col2)   from t  where id in (select :a from dual)

col value_string format a10
col was_capture format a3
col name format a6
col datatype_string format a15
set heading on
select c.was_captured, c.name, c.datatype_string, c.VALUE_STRING
 from v$sql_bind_capture c
where c.sql_id='&&sql_id';

WAS NAME   DATATYPE_STRING VALUE_STRI
--- ------ --------------- ----------
NO  :A    NUMBER

Et pourtant, la valeur est bien impliquée dans le process de bind peeking et elle est capturée avec le plan d'exécution comme vous pouvez vous en rendre compte ci-dessous:

select *
from table(
dbms_xplan.display_cursor(
        sql_id=>'&&sql_id',
        format=>'basic peeked_binds'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* arkzoyd_2 */ count(col2)   from t  where id in (select :a
from dual)

Plan hash value: 3518428625

--------------------------------------------------
| Id  | Operation        | Name  |
--------------------------------------------------
|   0 | SELECT STATEMENT       |   |
|   1 |  SORT AGGREGATE        |   |
|   2 |   NESTED LOOPS        |   |
|   3 |    VIEW         | VW_NSO_1 |
|   4 |     FAST DUAL        |   |
|   5 |    TABLE ACCESS BY INDEX ROWID| T  |
|   6 |     INDEX RANGE SCAN       | TIDX  |
--------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 1001

Un autre exemple

Prenons ce troisième exemple qui utilise non pas une mais de très nombreuses variables bind. Ce cas est inspiré de l'utilisation de cursor_sharing=force avec de nombreuses clauses in (..., [...], ...). Dans l'exemple qui suit, je créé 200 variables pour un unique appel SQL. Pour cela, je génère l'ordre SQL en PL/SQL:

cat >demo_gen.sql <<= 200;  select 'exec :x'||rownum||       ' := 100000+to_number('||       rownum||')' cmd  from dual  connect by level <= 200;  declare   vline varchar2(1000); begin    dbms_output.put_line('select /* arkzoyd'||'_3 */ '||                       'count(*) from t where id in (');    for i in 0..19 loop      vline:='';      for j in 1..10 loop         if (i*10+j)!=200 then            vline:=vline || ':x'||to_char(i*10+j)||',';         else            vline:=vline || ':x'||to_char(i*10+j)||')';         end if;      end loop;      dbms_output.put_line(vline);  end loop;  dbms_output.put_line('/'); end; / spool off set echo on set feedback on set heading on EOF

Le fichier demo_gen.sql permet de génèrer demo.sql; c'est ce second fichier qui contient nos 200 variables bind; connectez-vous à SQL*Plus et exécutez successivement les 2 scripts :

@demo_gen
alter session set statistics_level=typical;
@demo

Et là (si on y réfléchit, ce n'est pas très surprenant!), seules 100 variables bind sont capturées:

col sql_id format a13 new_value sql_id
col sql_text format a80
set lines 100
select s.sql_id, s.sql_text
from v$sql s
where sql_text like '%arkzoyd\_3%' escape '\';

col name format a10
col datatype_string format a8
col value_string format a10
select c.was_captured, c.name, c.datatype_string, c.value_string
 from v$sql s, v$sql_bind_capture c
where sql_text like '%arkzoyd\_3%' escape '\'
  and s.sql_id=c.sql_id;

WAS NAME       DATATYPE VALUE_STRI
--- ---------- -------- ----------
YES :X1        NUMBER 100001
[...]
YES :X100      NUMBER 100100
NO  :X101      NUMBER
[...]
NO  :X200      NUMBER

En 11g, il est possible de collecter ces informations grâce à la procédure dbms_sqltune.extract_binds et la colonne v$sqlarea.bind_data :

var binddata varchar2(2000);
begin
select bind_data into :binddata
from v$sqlarea
where sql_id='&&sql_id';
end;
/

set serveroutput on
begin
for i in (select position, datatype_string, VALUE_ANYDATA
         from table(dbms_sqltune.extract_binds(:binddata)))
loop
if (i.value_anydata is not null) then
   dbms_output.put_line(rpad(to_char(i.position)||'('
                                     ||i.datatype_string||')'
                        ,15)
                        ||' : '
                        ||to_char(i.value_anydata.accessnumber));
else
   dbms_output.put_line(rpad(to_char(i.position),15)
                        ||' : NULL');
end if;
end loop;
end;
/

1(NUMBER) : 100001
[...]
100(NUMBER) : 100100
101  : NULL
[...]
200  : NULL

SQL Tuning Set

Créez un SQL Tuning Set à partir de la requête précédente comme ci-dessous:

connect / as sysdba

prompt &&sql_id
begin
dbms_sqltune.drop_sqlset(
          sqlset_name=>'ArKZoYD');
end;
/

begin
dbms_sqltune.create_sqlset(
          sqlset_name=>'ArKZoYD',
          description=>'ArKZoYD SQL Tuning Set');
end;
/

DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_cursor_cache (
                   'sql_id = ''&&sql_id''', -- basic_filter
                   NULL,                    -- object_filter
                   NULL,                    -- ranking_measure1
                   NULL,                    -- ranking_measure2
                   NULL,                    -- ranking_measure3
                   NULL,                    -- result_percentage
                   10)                      -- result_limit
          ) p;

DBMS_SQLTUNE.load_sqlset (
          sqlset_name => 'ArKZoYD',
          populate_cursor => l_cursor);
END;
/

Vous ne serez pas étonné de découvrir que toutes les valeurs des variables bind ne sont pas inclues dans le SQL Tuning Set:

var binddata varchar2(2000);
begin
select bind_data into :binddata
from table(dbms_sqltune.select_sqlset('ArKZoYD'));
end;
/

set serveroutput on
begin
for i in (select position, datatype_string, VALUE_ANYDATA
         from table(dbms_sqltune.extract_binds(:binddata)))
loop
if (i.value_anydata is not null) then
   dbms_output.put_line(rpad(to_char(i.position)||'('
                                     ||i.datatype_string||')'
                        ,15)
                        ||' : '
                        ||to_char(i.value_anydata.accessnumber));
else
   dbms_output.put_line(rpad(to_char(i.position),15)
                        ||' : NULL');
end if;
end loop;
end;
/

1(NUMBER) : 100001
[...]
100(NUMBER) : 100100
101  : NULL
[...]
200  : NULL

SQL Performance Analyzer

Maintenant, qu'on pressent bien qu'il va y avoir potentiellement un problème dans l'exécution de SQL Performance Analyzer, rien ne vaut de le vérifier en lançant l'outil avec l'event 10046 activé au niveau 12 pour vérifier quelles sont les valeurs des variables bind utilisées:

var tname varchar2(100);
exec :tname:=dbms_sqlpa.create_analysis_task( -
           sqlset_name => 'ArKZoYD', -
           task_name   => 'SQLPArKZoYD');

alter system set events
 '10046 trace name context forever, level 12';

exec dbms_sqlpa.execute_analysis_task( -
           task_name => 'SQLPArKZoYD');

alter system set events
 '10046 trace name context off';

var x clob
exec :x:=dbms_sqlpa.report_analysis_task(   -
           task_name => 'SQLPArKZoYD', -
           type=>'HTML',               -
           level => 'ALL',             -
           section => 'ALL');

set long 20000
set longchunksize 20000
set lines 20000
print x

Le résultat est un fichier HTML qui donne les informations d'exécution mais avec des valeurs différentes des valeurs initiales d'exécution de la requête; On peut le visualiser du fait des conversions implicites :

Le fichier de trace montre que les valeurs d'exécution sont des valeurs NULL comme l'extrait suivant l'illustre:

 Bind#99
 oacdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
 oacflg=11 fl2=0000 frm=00 csi=00 siz=0 off=2376
 kxsbbbfp=01401198  bln=22  avl=03  flg=01
 value=100100
Bind#100
 oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
 oacflg=21 fl2=10000 frm=01 csi=178 siz=0 off=2400
 kxsbbbfp=014011b0  bln=32  avl=00  flg=01

Conclusion

Cet article illustre bien l'intérêt de comprendre comment fonctionne un outil et quelles sont ses limites. Parmi les nombreuses conclusions possibles, vous retiendrez une difficulté de plus à travailler avec le paramètre cursor_sharing positionné à similar ou force. C'est le prix à payer lorsque les développeurs n'ont que faire des performances d'une application et ne les intègrent pas dans leur développement. Qu'est-ce que vous en pensez?