En 2018 j’avais publié un article qui expliquait comment visualiser sous forme de “heatmap” le nombre de fichiers redo log générés par heure sur une quinzaine de jours. J’avais alors utilisé HighChart et Plotly pour afficher les données.
Depuis 2018 de l’eau a coulé sous les ponts et D3 JS a enfin sorti une surcouche, sobrement appelée plot, qui permet de gérer directement les graphiques.
Depuis des années, ora2pg permet la migration de données d’Oracle vers PostGreSQL. Une des ombres au tableau de cet outil est le traitement des LOBs (Large OBjects) et plus particulièrement de BLOBs (Binary Large OBjects), qui même optimisé reste particulièrement lent.
J’ai récemment testé l’aventure sur une table contenant 8 millions de lignes pour 27 Go de BLOB-segments. Avec ora2pg l’extraction a pris, en utilisant le parallélisme que perl me permet sur un serveur de 4 vCPU et 32 Go de mémoire, près de 5 jours.
(suite…)
Depuis quelques version d’Oracle, la gestion de la mémoire a été simplifiée, comprenez que le nombre de paramètres minimal liés à sa gestion a été considérablement réduit. En clair, il suffirait de positionner le volume global de mémoire à allouer à l’instance par le truchement du paramètre memory_target et par le biais de réallocations dynamiques oracle serait capable de se débrouiller. On constate bien vite que c’est faux notamment pour le tuning sga.
Comme d’habitue lors de ce genre de cas, si on laisse l’initiative à Oracle, au bout d’un moment la mémoire finit par fragmenter et malgré une taille globale suffisante on rencontre de vilaines erreurs ORA-4031. Et plus généralement ce mode d’allocation (via memory_target donc) n’est pas compatible avec l’utilisation de huge pages. Il est donc recommandé de tuner les différentes sous-sections de mémoire, la pga au travers de pga_aggregate_target et la sga au travers de sga_target. Ces deux paramètres seuls ne suffisent cependant encore pas et il faut positionner des valeurs pour les divers pools et caches de la SGA qui seront considérées comme des minimums.
La question qui se pose régulièrement lors du réglage des instances Oracle est comment est gérée la mémoire sous Linux et plus généralement, de combien de mémoire puis-je disposer pour mon instance ( mes instances ? ). En effet, la littérature est assez peu fournie sur la métrologie de la mémoire sous linux et on se retrouve souvent avec des données incompréhensibles. C’est notamment le cas de la commande free, qui peut très bien afficher un espace libre de 0, en permanence, sans que cela ne porte à conséquences ni que cela ne reflète la réalité.
Ce que l’on souhaite faire c’est afficher simplement le nombre de redo log files générés en mode graphiquesous forme de heat-map. Pour ce faire on utilise highcharts qui a le bon goût d’être gratuit pour un usage récréatif et non commercial. Et parce qu’on aime s’amuser on testera aussi avec plotly qui est gratuit tout court.
Pour ce faire, On choisit de séparer le code HTML, du javascript et des données de manière ce que chaque partie de code puisse être réutilisés au maximum.
La requête suivante permets de trouver l’historique des modifications du paramétrage Oracle qui a pu être effectué au niveau de l’instance dans et stocké dans AWR. La rétention des informations est donc équivalente à celle de rétention des snapshots. Si vous utilisez un AWR Warehouse, potentiellement, vous conservez cet historique depuis son initialisation.
select eit
, param
, last_val
, cur_val
, is_def
from ( select end_interval_time eit
, parameter_name param
, lag ( value, 1 ) over ( partition by dbid, instance_number, parameter_hash order by snap_id) last_val
, value cur_val
, isdefault is_def
from dba_hist_parameter natural join dba_hist_snapshot
where dbid = :dbid )
where last_val != cur_val
and last_val is not null
/
La requête nécessite que la variable DBID soit positionnée afin pouvoir requêter aussi bien la base locale que le reository AWR Warehouse. En local on positionnera la bind variable de la manière suivante :
var dbid number exec select dbid into :dbid from v$database /
Avoir l’historique des modifications du paramétrage Oracle permet, surtout lorsqu’on travaille à plusieurs sur une même base de données, d’apporter des éléments de réponse à la question : “Pourquoi mon appli marche-t-elle moins bien depuis 12 jour ?”. Donc savoir accéder à ces informations est une aide précieuse surtout lors d’interventions occasionnelles.
Les performances d’Oracle reposent sur l’analyse des requêtes SQL au travers du Cost Based Optimizer (CBO) qui s’appuie sur les statistiques sur les divers objets de la base. Toute le problématique des statistiques Oracle est d’avoir une représentation la plus fidèle possible des données en base en un minimum d’informations. La solution proposée par Oracle pour le faire est d’utiliser les histogrammes.
Au niveau des tables et sans s’occuper des problématiques de partitionnement les statistiques sont visibles dans des vues diverses qui vont donner des informations au niveau de la table (par exemple DBA_TABLES, ALL_TAB_STATISTICS, USER_TABLES …) puis pour les colonnes des tables (USER_TAB_COLUMNS, DBA_TAB_COL_STATISTICS …) et enfin pour certaines colonnes un histogramme est créé et visible dans des vues telles que DBA_COL_HISTOGRAMS. Reste à juger de leur pertinence afin de décider si oui ou non on en a besoin pour telle ou telle colonne.
create table t0 ( c1 number ); insert into t0 select abs(ceil(dbms_random.normal*3)) from sys.dual connect by rownum <= 1E5 ; exec dbms_stats.gather_table_stats(user, 'T0', method_opt=>'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)
On effectue un Explain plan pour toutes les requête dont le prédicat est C1=x où x varie de -2 à 15 que l’on compare au nombre effectif d’occurrence de chaque valeur ( count(*) group by C1 ).
En bleu l’histogramme, en rouge le nombre effectif d’occurrences des valeurs données en abscisse … On a ici un histogramme qui représente parfaitement le contenu de la colonne C1, et qui contredit la documentation officielle du fait qu’il ne décroît pas progressivement au delà des valeurs minimales et maximales.
L’histogramme a été calculé sur un échantillon de 100 %. Le CBO le considère comme “juste” en l’occurrence à raison, mais dans deux semaines ? Au bout de deux semaines aussi, et c’est nettement moins agréable.
Si on laisse Oracle choisir l’échantillon, ça donne quoi ?
exec dbms_stats.gather_table_stats(user, 'T0')
ATTENTION: L’échelle est logarithmique. Mais là on a bien ce que décrit la documentation. Et l’histogramme garde sa pertinence.
En résumé: Les Frequency histograms c’est génial mais ça prend de la place donc si le nombre de valeur distinctes d’une colonne est trop important il faut trouver une autre solution pour rendre compte de la répartition non uniforme des données et ne pas trop encombrer le dictionnaire des données; c’est là qu’intervient le second type d’histogramme appelé height balanced histogram.
Oracle a décidé que le nombre maximal de valeurs distinctes qu’il allait stocker pour une colonne était de 254, au delà ou si on demande de calculer l’histogramme pour moins de valeurs qu’il n’en existe pour la colonne (non documenté) l’histogramme devient height balanced.
create table t1 ( c1 number );
insert /*+ APPEND */ into t1
select ceil(dbms_random.normal*35)
from sys.dual
connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)
create table t2 ( c1 number );
insert /*+ APPEND */ into t2
select (150-abs(c))*sign(c+0.1)
from (select ceil(dbms_random.normal*35) c
from sys.dual
connect by rownum <=1E5 ) ;
On obtient les répartitions de données suivantes :
Si on calcule les statisitques sur la table en ne calculant pas l’histogramme on obtient la distribution de cardinalités suivantes (en vert):
Comme on pouvait s’y attendre, Oracle considère que chaque valeur est représentée autant que les autres. A noter tout de même: avant la valeur minimale et au delà de la valeur maximale détectée, le CBO estime une cardinalité progrssivement décroissante de la valeur moyenne calculée jusuq’à une valeur de 1. La raison en est que les statistiques n’ont pas vocation à être calculées en permanence et que donc, la table vivant, de nouvelles valeurs peuvent apparaitre.
0n obtient la distribution de cardinalités suivantes (en bleu):
On constate :
On constate :
On constate :
Les histogrammes sont assez représentatifs des données (ouf !) mais peut-être que ça vient de la forme de la courbe, d’où l’idée d’essayer avec une courbe ‘inversée’.
On constate :
les données de la colonne sont ordonnées et le résultat découpé en autant de buckets que vous ou Oracle en avez décidé. A partir de là à chaque point de rupture entre deux buckets, la valeur est enregistrée.A chaque bucket correspond donc le nombre de lignes valuées de la table divisé par le nombre du buckets. A une valeur de la colonne qui est relevée correspond une cardinalité estimée au nombre de fois où elle a été relevée multiplié par le nombre de lignes que contient un bucket
Oui mais c’est très coûteux: en effet, si on fait du dynamic sampling on obtient ça:
Seulement, du coup, le temps de parsing est très fortement allongé et les lectures disques augmentées
Le calcul des histogrammes parait un bon compromis, cependant il est des cas où il s’avère inutile. Le graphe ci-après représente l’histogramme et les valeurs calculées sur une table de 10 millions de lignes sur une colonne présentant près de 1600 valeurs distinctes.
On constate :
Jusqu’à maintenant les histogrammes étaient calculés sur des colonnes de type numérique. Si on calcule un histogramme sur une colonne de type varchar2 on peut rencontrer un soucis dans le cas où un grand nombre de valeurs distinctes ont leur 32 premiers caractères en commun.
create table t6 ( c1 varchar2(64) ); insert /*+ APPEND */ into t6 select 'Foo Bar Baz Foo Bar Baz Foo Bar Baz '||to_char(ceil(dbms_random.normal*35)) from sys.dual connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)
On constate :
Les histogrammes représentent un moyen intéressant d’avoir une estimation de la cardinalité réelle d’une occurrence de valeur pour une colonne donnée. Cependant, si les valeurs sont trop nombreuses ou pour certains types de données trop centrées autour d’une même valeur les statistiques calculées seront au mieux inutiles et au pire complètement erronées.
Cet article est paru précédemment sur le site de mon ancien employeur.
Lors d’un précédent article j’ai montré que l’instruction Explain Plan donnait un résultat discuttable dès lors que la requête étudiée impliquait des bind variables. Une question reste en suspens: Pour quelle raison les cardinalités calculées par Explain plan sont-elles fausses ?
Je vois circuler sur le net une explication farfelue selon laquelle explain plan ne déclencherait pas de hard parse et qu’en conséquence il n’irait pas rechercher la valeur des bind variables, Explication plus ou moins mélangée avec une demi vérité selon laquelle explain plan considérerait toutes les bind variables comme étant de type varchar2 (vrais) et que de ce fait il serait incapable d’aller chercher la valeur correspondante dans la table (faux).
Avant toute chose, il est important de se référer à Tom Kyte qui en la matière est quasiment indiscuttable (après tout, tout le monde peut se tromper):
explain plan always does a hard parse, it evaluated the query plan “in the here and now, as of this moment in time
Il est donc plus que probable que l’explication selon laquelle explain plan ne fait pas de hard parse est fausse. Pour s’en convaincre, je ferai des traces grace à l’évènement 10053 qui ne produit une trace que lors de hard parses.
Les tests effectués dans ce billet le sont en 11gR2.
Avant toutes chose, on se crée une petite table de test, indexée afin de l’optimiseur puisse choisir ou non de prendre l’index et avec des histogrammes pour que les opérations de bind peeking aient un sens.
drop table t1; create table t1 ( c1 number, c2 number ); insert /*+ APPEND */ into t1 select abs(ceil(dbms_random.normal*3)), rownum from sys.dual connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)
La commande suivante liste les cardinalités (pêchées dans l’histogramme sur la colonne) associées à chaque valeurs de la colonne C1 de la table de test T1.
select TABLE_NAME
, COLUMN_NAME
, ENDPOINT_NUMBER - nvl(lag(ENDPOINT_NUMBER, 1) over (partition by TABLE_NAME, COLUMN_NAME order by ENDPOINT_NUMBER) , 0 ) card
, ENDPOINT_VALUE
from dba_tab_histograms
where table_name='T1'
and owner=user ;
TABLE_NAME C_N CARD ENDPOINT_VALUE
------------------------------ --- ---------- --------------
T1 C1 13088 0
T1 C1 24444 1
T1 C1 21250 2
T1 C1 16146 3
T1 C1 11145 4
T1 C1 6867 5
T1 C1 3752 6
T1 C1 1948 7
T1 C1 865 8
T1 C1 316 9
T1 C1 121 10
T1 C1 42 11
T1 C1 12 12
T1 C1 3 13
T1 C1 1 14
On a donc une colonne dont les valeurs ne sont pas uniformément réparties et si l’optimiseur fait son travail correctement, pour un contrainte de type C1=13 il devrait choisir un accès par index et pour une cuntrainte de type C1=1 il devrait choisir un parcours complet de la table.
La mémoire est vide de tout plan pour cette table nouvellement créée, on s’attend à ce que, par le biais de l’histogramme, l’optimiseur s’apperçoive que la valeur 13 est peu représentée pour la colonne C1 et choisisse un accès par index.
variable s number
exec :s := 13
alter session set tracefile_identifier = 'T1_EXE_NUM' ;
alter session set events='10053 trace name context forever' ;
select * from t1 where c1 = :s ;
C1 C2
---------- ----------
13 61103
13 79854
13 95305
alter session set events='10053 trace name context off' ;
alter session set tracefile_identifier='' ;
Trois lignes de remontées sur 100 000, on espère qu’un index a été utilisé …
select * from table ( dbms_xplan.display_cursor() ) ;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID 6v6vzbxcpb91s, child number 0
-------------------------------------
select * from t1 where c1 = :s
Plan hash value: 1198593617
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 24 | 2| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_C1 | 3 | | 1| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=:S)
Formidable, le plan choisi est le bon, ou du moins celui que l’on attend, le curseur est en mémoire, on continue.
On joue cette fois-ci un explain plan, le curseur n’a pas quitté la mémoire, la variable ‘s’ n’a pas changé de valeur on pourrait penser :
alter session set tracefile_identifier = 'T2_XPL_NUM' ; alter session set events='10053 trace name context forever' ; explain plan for select * from t1 where c1 = :s ; Explained. alter session set events='10053 trace name context off' ; alter session set tracefile_identifier='' ;
On le sait, on l’a vu lors de mon précédent article sur le sujet, ni le plan en mémoire, ni le même déduit à partir de la valeur de la variable ne sera produit :
select * from table ( dbms_xplan.display() ) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 838529891
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7143 | 57144 | 35| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 7143 | 57144 | 35| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=TO_NUMBER(:S))
13 rows selected.
Au passage on vérifie que des hard parses ont étés faits pour chacun de nos tests :
ho ls -rt $BDUMP/TTEST*T*trc TTEST_ora_20250650_T1_EXE_NUM.trc TTEST_ora_20250650_T2_XPL_NUM.trc
Comme prévu une analyse complète de la requête a été faite dans les deux cas et le plan généré est différent du plan initial.
On constate aussi un traitement différent :
Que dit Tom Kyte quand il évoque le sujet ?
The last bit about explain plan […] is the fact that explain plan doesn’t see your bind datatype. It presumes all binds are varchar2’s regardless of how the developer is binding.
On pourrait penser du fait que l’explain plan considère les bind variables systématiquement comme un type varchar2, la conversion influe sur le plan d’exécution et sur les cardinalités, et on se fourvoierait :
alter system flush shared_pool ;
variable s2 varchar2(3)
exec :s2 := '13'
alter session set tracefile_identifier = 'T3_EXE_VC2' ;
alter session set events='10053 trace name context forever' ;
select * from t1 where c1 = :s2 ;
C1 C2
---------- ----------
13 61103
13 79854
13 95305
alter session set events='10053 trace name context off' ;
alter session set tracefile_identifier='' ;
Si l’explication selon laquelle un type varchar2 égare le CBO est vraie, le plan utilisé devrait utiliser un parcours complet de la table, il prendrait un index dans le cas contraire.
select * from table ( dbms_xplan.display_cursor() ) ;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID bmawjntgyr2p7, child number 0
-------------------------------------
select * from t1 where c1 = :s2
Plan hash value: 1198593617
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 24 | 2| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_C1 | 3 | | 1| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=TO_NUMBER(:S2))
Lors de l’exécution avec une bind variable de type varchar2, les opérations de bind peeking ont bien été faites et le bon plan choisi, la supposition que l’absence de bind peeking dans l’explain plan provient du type de la bind variable est fausse. Alors pourquoi ?
On s’intéresse ici aux contenus des fichiers de trace générés et surtout à la section où la Bind Variable est lue.
******************************************* Peeked values of the binds in SQL statement ******************************************* ----- Bind Info (kkscoacd) ----- Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0 kxsbbbfp=110950ff8 bln=22 avl=02 flg=05 value=13 Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "SYS"."T1" "T1" WHERE "T1"."C1"=:B1
******************************************* Peeked values of the binds in SQL statement ******************************************* ----- Bind Info (kkscoacd) ----- Bind#0 oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1010000 frm=01 csi=873 siz=32 off=0 kxsbbbfp=11097d100 bln=32 avl=00 flg=05 Final query after transformations:******* UNPARSED QUERY IS ******* SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "SYS"."T1" "T1" WHERE "T1"."C1"=TO_NUMBER(:B1)
Dans les deux cas, l’analyseur de requête constate qu’il a une bind variable, mais dans le second il ne sait pas lui associer une valeur. On peut en déduire que l’instruction explain plan ne donne pas la valeur de la variable à l’analyseur de requête.
Si explain plan ne produit pas le plan d’exécution qui sera celui de la requête lors de la concommitance de bind variables et d’histogrammes c’est parce que la valeur de la variable n’est pas transmise au CBO et de fait celui-ci doit se rabattre sur un calcul qui suppose qu’au travers de cette bind variable n’importe quelle valeur peut être passée. De là il calcule une cardinalité moyenne qui l’oriente à bon ou mauvais escient sur un plan qui ne représente pas toujours la réalité.
Dans ces conditions peut-on compter sur explain plan pour faire du tuning ? Je dirais oui, le but d’explain plan est de répondre à la question qu’on lui pose, en l’occurrence et dans tout ce billet, “quel plan prendrait l’optimiseur si on le contraignait avec une valeur variable sur la colonne C1”, la réponse qu’il donne, à savoir “un parcours complet de la table” est vraie pour des valeurs de la variable de 0 à 7 (8 valeurs) et fausse si la variable est comprise entre 8 et 14 (7 valeurs) où le parcours par index est moins coûteux.
Si on souhaite connaitre le plan que l’optimiseur utiliserait pour une valeur précise, il faut la lui donner et ne pas passer par une variable.
On est loin de l’ésotérique absence de hard parse ou de la tout aussi surprenante perte d’information lors d’une conversion de type et quelque part ça rassure non ?
Lors d’une discussion sur un forum j’ai lancé l’affirmation suivante qui a laissé un membre dubitatif :
Vous utilisez ici des bind variables. Lorsque vous utilisez ce type de variable dans un explain plan, Oracle ignore les éventuels histogrammes sur les colonnes alors que lors d’une exécution réelle il les prends en compte.
Je reprends ici les éclaircissements donnés à ce sujet sur ledit forum.
Je me suis créé une table de test de 100 000 lignes contenant une colonne qui suit une loi normale de la manière suivante :
set autot off drop table t1; create table t1 ( c1 number ); insert /*+ APPEND */ into t1 select abs(ceil(dbms_random.normal*3)) from sys.dual connect by rownum 'FOR COLUMNS C1 SIZE 254', estimate_percent=>100)
J’ai donc la distribution suivante pour les valeurs de ma table
select c1, count(*)
from t1
group by c1
order by c1 ;
C1 COUNT(*)
---------- ----------
0 12917
1 24741
2 21291
3 16074
4 11194
5 6775
6 3840
7 1778
8 868
9 346
10 113
11 51
12 10
13 2
Premier test avex une valeur fortement représentée :
variable s number
exec :s := 1 ;
explain plan for
select /* tst explain 1 */ * from t1
where c1=:s ;
@?/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4291296153
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7143 | 21429 | 14 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_T1 | 7143 | 21429 | 14 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=TO_NUMBER(:S))
13 rows selected.
Le plan d’exécution m’indique que je vais remonter 7143 lignes. Je sais que c’est faux, la distribution des données me donne 24741 et l’histogramme ayant été calculé complètement, il donne les mêmes inforamtions à l’optimiseur.
Je fais un nouveau test avec une valeur très faiblement représentée
exec :s := 13 ;
explain plan for
select /* tst explain 13 */ * from t1
where c1=:s ;
@?/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4291296153
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7143 | 21429 | 14 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_T1 | 7143 | 21429 | 14 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=TO_NUMBER(:S))
13 rows selected.
Là encore le plan m’annonce que je vais remonter 7143 lignes, hors je sais que j’en remonterai deux
Je refais mon test pour la valeur 13 qui doit remonter deux lignes
select /* tst exec 13 */ * from t1
where c1=:s ;
C1
----------
13
13
Formidable, elle remonte bien deux lignes, l’explain plan m’avait donc bien donné une information erronée. Que dit le plan choisi par le CBO ?
select * from table( dbms_xplan.display_cursor() ) ;
------------------------------------------------------------------------------------
SQL_ID 31j5tck2g6gd2, child number 0
-------------------------------------
select /* tst exec 13 */ * from t1 where c1=:s
Plan hash value: 4291296153
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| I_T1 | 2 | 6 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:S)
18 rows selected.
Vous me direz qu’à postériori il est simple de donner le nombre de lignes qui ont été ramenées … Ok, je refais la même requête en lui demandant de me mémoriser les lignes estimées par le CBO (E-ROWS) et les lignes effectivement remontées (A-ROWS) et je refais afficher le plan.
select /*+ GATHER_PLAN_STATISTICS */ * from t1
where c1=:s ;
C1
----------
13
13
select * from table( dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 5av85qaqqqynu, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from t1 where c1=:s
Plan hash value: 838529891
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 156 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 2 | 2 |00:00:00.01 | 156 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=:S)
18 rows selected.
Ok, il estme donc bien deux lignes et en retourne 2 … ça parait logique et ça rassure
On a vu qu’un explain plan produisait systématiquement la valeur erronée 7143, d’où la tire-t-il ? Je suppose qu’il utilise la formule de cardinalité générale sans tenir compte des histogrammes :
cardialité=nombre de lignes non nulles / nombre de valeurs distinctes ce qui dans notre cas donne : 100 000 lignes non nulles pour 14 valeurs distinctes … Je vous laisse compter le temps de taper la requête :
select round(100000/14) est from dual;
EST
----------
7143
Donc l’explain plan ne s’est pas donné la peine de parcourir l’histogramme.
Une question reste en suspens : Pourquoi la variable n’est pas interprétée ? … Suite au prochain épisode, ça se complexifie …
Créer un profile SQL avec des hints nécessite Tuning Pack mais est utile lorsqu’on fait face à un progiciel qui ne peut aller modifier son code. En effet, quel DBA n’est pas régulièrement confronté au problème suivant :
Il est possible depuis la version 10g de positionner des hints sur une requête sans aller modifier le code applicatif. La technique utilise le package dbms_sqltune et est donc soumise à licence.