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?

5 réflexions sur “V$SQL_BIND_CAPTURE, SQL Tuning Set (STS) et SQL Performance Analyzer (SPA)”

  1. Merci pour la réponse.
    Pour information j’ai également posté sur le forum d’Oracle mais ce que je souhaite faire n’est actuellement pas possible.

    Encore Merci. 🙂

  2. Fabien,

    Ce n’est pas une jointure ; c’est une requête imbriquée. Mettez-la dans la clause SELECT plutôt que dans la clause WHERE (sans le >100) et vous verrez…

    Les valeurs de B1 (i.e. la référence à l’autre colonne) se déduit visuellement du filtre de l’étape 4 dans le prédicat :
    * 4- FILTER (« DE ». »DEPT_NO »=:B1)
    et de la requête imbriquée elle-même

    Je ne connais pas de moyen simple autre que visuel de « programmatiquement » récupérer l’information; dans le format par exemple. Je suis loin de tout connaitre. Regardez ce qui est écrit à ce propos dans la 10053. Et posez la question sur les forums oracle.

    Cela dit, désolé, je ne vois pas à quoi cette information peut servir: – Soit vous avez un problème (fonctionnel, performance, etc) et vous avez tout pour reproduire puisque la variable n’est pas utilisée ailleurs qu’en interne de la requête
    – soit vous n’avez pas de problème et dans ce cas, pourquoi s’intéresser à cette question.

    Pourquoi faire diable ?

    Grégory

  3. Je comprend votre raisonnement. Je comprend également que la requête peut être écrite différemement en évitant la sous-requête,…

    mais je ne suis pas à l’origine de la requête et ma problématique est de savoir si dans un plan d’exécution, il est possible de
    récupérer de façon simple la jointure qui est faite entre la table de la requête principale et celle de la sous-requête. Je ne
    souhaite pas récupérer les valeurs mais bien les champs de jointure.

    Dans le cas présent, je souhaite récupérer dans l’explain plan le champ de jointure entre dep_dept_emp et dep_departments
    soit DEP_DEPARTMENTS.DEPT_NO à la place du :B1 ou bien savoir comment retrouver à coup sûr ce champ dans l’exlain.

    Cordialement

  4. Bonjour,

    Je ne peux pas m’empêcher de commencer par répondre à ce commentaire par 2 remarques, pas méchantes, mais qui me font sourire :
    – d’abord, c’est probable que l’article ne réponde pas à votre question car il n’a pas été écrit dans ce but. Si vous avez une question et que vous cherchez une réponse, utilisez plutôt les forums Oracle.
    – ensuite quand vous signez « Anonyme », ça me donne toujours l’impression que c’est un de mes « vieilles canailles » d’amis qui essaie de me piéger. Ça ne coute rien à personne, surtout quand on demande un coup de main de s’appeler par son prénom. En outre, il n’y a aucune honte à se tromper, vous trouverez des dizaines de coquilles dans ces articles…

    Plus sérieusement et concernant la question :
    – Oracle variabilise effectivement d.dept_no pour résoudre son plan mais il ne s’agit pas ICI d’une bind variable qui est utilisée pour faire le lien entre un programme (en C, Java, PL/SQL,…) et le SQL et qui, en général, facilite le travail du moteur

    – Il existe plusieurs moyens de retrouver les valeurs de binds utilisées dans un plan ; si ces variables sont utilisées pour calculer le plan, le paramètre peeked_bind dans le « format » du dbms_xplan.display_xxx; sinon le level 4 des traces 10046 ou v$sql_bind_capture/dba_hist_sqlbind. Il y a effectivement un paramètre (statistic_level=all) qui peut faciliter la capture de ces valeurs avec les limites décrites dans l’article ci-dessus et ce paramètre peut être utilisé pour l’ordre sql uniquement avec un hint. Dans votre cas, ça ne marchera pas puisque ce n’est pas une variable bind

    – Votre plan n’inclut pas non plus de jointure. En fait votre requête s’exécute de la manière suivante :
    (a) la table DEP_DEPARTMENTS est lue en entier
    (b) pour chaque ligne de la table, une 3eme colonne nommée « (#keys=0) COUNT(*) » est construite par exécution de la requête (SELECT COUNT(*) FROM « IITER_TEST ». »DEP_DEPT_EMP » « DE » WHERE
    « DE ». »DEPT_NO »=:B1) ou B1 représente DEP_DEPARTMENTS.DEPTNO ; ce sont les étapes 3 et 4 du plan et elles utilisent une lecture de l’index comme s’il s’agissait d’une table (index fast full scan)
    (c) l’étape 1 filtre ensuite les lignes pour ne ramener que celles qui vérifient la condition du nombre d’employé > 100 dans le département

    – Si vous voulez qu’Oracle fasse une jointure il faut écrire votre ordre différemment. Maintenant, attention ce que j’écris ci-dessous est sémantiquement différent de l’ordre que vous présentez et il est tout à fait possible que le résultat soit différent (il faudrait le tester). En outre, vous ne donnez rien à propos du schéma des contraintes, des types, etc donc on peut probablement imaginer plein de cas très tordus :

    with dept_morethan100 as
    (select de.dept_no
    from iiter_test.dep_dept_emp de
    group by de.dept_no
    having count(*) > 100)
    select d.dept_no, d.dept_name
    from IITER_TEST.DEP_DEPARTMENTS d,
    dept_morethan100 de
    where de.dept_no=d.dept_no;

    Par ailleurs, je ne dis pas non plus qu’un plan d’exécution ou une syntaxe est meilleure qu’une autre. Ca dépend.

    – A mon tour de poser une question. Mais quel pourrait bien être votre intérêt de connaitre les valeurs de B1, quand bien même il s’agirait d’une bind variable ?

    Bon amusement,

    Grégory

  5. Bonjour

    Votre article est très intéressant mais je ne trouve pas l’information quant à ma problématique:

    J’ai une requête contenant une sous requête dans la clause where et je souhaite récupérer toutes les informations à partir du plan d’exécution. Sauf qu’Oracle transforme les jointures de la sous-requête en bind_value.

    Exemple:
    la requête
    explain plan for
    select d.dept_no, d.dept_name
    from IITER_TEST.DEP_DEPARTMENTS d
    where (select count(*) from iiter_test.dep_dept_emp de
    where D.DEPT_NO = de.dept_no) > 100;

    Le plan:
    select * from table (dbms_xplan.display(‘plan_table’, null, ‘all’));

    Plan hash value: 3224470819

    ——————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————
    | 0 | SELECT STATEMENT | | 9 | 153 | 73 (9)| 00:00:01 |
    |* 1 | FILTER | | | | | |
    | 2 | TABLE ACCESS FULL | DEP_DEPARTMENTS | 9 | 153 | 2 (0)| 00:00:01 |
    | 3 | SORT AGGREGATE | | 1 | 5 | | |
    |* 4 | INDEX FAST FULL SCAN| DEPT_EMP_PK | 36845 | 179K| 71 (9)| 00:00:01 |
    ——————————————————————————————

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$1
    2 – SEL$1 / D@SEL$1
    3 – SEL$2
    4 – SEL$2 / DE@SEL$2

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

    1 – filter( (SELECT COUNT(*) FROM « IITER_TEST ». »DEP_DEPT_EMP » « DE » WHERE
    « DE ». »DEPT_NO »=:B1)>100)
    4 – filter(« DE ». »DEPT_NO »=:B1)

    Column Projection Information (identified by operation id):
    ———————————————————–

    1 – « D ». »DEPT_NO »[VARCHAR2,4], « D ». »DEPT_NAME »[VARCHAR2,40]
    2 – « D ». »DEPT_NO »[VARCHAR2,4], « D ». »DEPT_NAME »[VARCHAR2,40]
    3 – (#keys=0) COUNT(*)[22]

    Est il possible d’avoir dans l’explain plan les champs de jointure remplacés par les bind value. Est ce qu’il existe un Hint? Pourquoi génère t’il ces bind?

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *