Catégorie : Tuning

Métrologie, SQL, Tuning

PCT COST ? Comment 7271 peut-il représenter 0% de 2 ?

Si on effectue une requête SQL et que l’on demande l’affichage de son plan d’exécutio par défaut on obtient le coût individuel de chaque action, ainsi un parcours complet de table ou d’index est associé à un coût global associé à son pourcentage de coût CPU et c’est là que ça se complique un poil. En effet, si on interroge directement les tables ou les vues du dictionnaire la relation en le contenu de la colonne CPU_COST des tables contenant les plans d’exécution rapportée à la colonne COST contenant le coût global de la l’action, on est loin de trouver un rapport quelconque avec le pourcentage affiché.

select * from sys.dual ;

D
-
X

select * from table(dbms_xplan.display_cursor()) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cps942q7r3w9m, child number 0
-------------------------------------
select * from sys.dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.

l
  1  select id
  2       , lpad(' ',DEPTH)||OPERATION||' '||OPTIONS operation
  3       , OBJECT_NAME NAME
  4       , CARDINALITY "ROWS"
  5       , BYTES
  6       , COST
  7       , CPU_COST
  8       , IO_COST
  9  from v$sql_plan
 10* where plan_hash_value=272002086
/

        ID OPERATION            NAME  ROWS  BYTES  COST   CPU_COST    IO_COST
---------- -------------------- ---- ----- ------ ----- ---------- ----------
         0 SELECT STATEMENT                           2
         1  TABLE ACCESS FULL   DUAL     1      2     2       7271          2

(suite…)

Tuning

Créer un plan d’exécution graphique avec les interfaces de programmation (API) google

L’affichage d’un plan d’exécution de manière graphique est toujours appréciable et le web propose pléthores d’API pour le faire. Parmis elles google visualization qui permet entr’autres choses de faire de jolis graphiques et d’afficher de manière simple des organigrammes ou pourquoi pas des plans d’exécution de requêtes SQL.

On peut trouver les plans d’exécution dans diverses tables de la base de donnée. Ici, j’ai choisi d’afficher un plan d’exécution pioché dans la table dba_hist_sql_plan et donc sort d’un historique AWR. Je pré suppose que je connais le SQLID et la valeur de hashage du plan à afficher (plan hash value).

Si on s’intéresse à la brique orgchart de google visualization API on s’apperçoit qu’il suffit de lui donner un tableau javascript contenant au minimum des identifiants uniques dans la première colonne et l’identifiant du pêre dans une deuxième colonne pour que l’arbre s’affiche : de jolies cases bleues contant les identifiants uniques … Ok, ce n’est pas transcendant, mais c’est déjà un premier pas : pas besoin de requête hyérarchique pour afficher une hyérarchie et comble de bonheur le travail est partagé entre la base de donnée et le client

(suite…)

Métrologie, SQL, Support Niveau 3, Tuning

INDEX FULL SCAN et INDEX FAST FULL SCAN

Suite à un échange à propos du fonctionnement du CBO et plus particulièrement sur les choix de parcours d’index en full scan ou en fast full scan sur le forum developpez.net qui était de la teneur suivante :

Intervenant 1 – Est-il vrai que si on effectue le requête select ename from emp, oracle n’utilisera pas l’index même si celui-ci existe ?
Intervenant 2 – Vrai sauf si la colonne ename ne peut pas être nulle et ce par contrainte, auquel cas un index full scan sera choisi
Intervenant 3 – Ne vouliez vous pas dire index fast full scan ?
Intervenant 2 – Non :
(suite…)

Exploitation, Métrologie, SQL, Support Niveau 2, Support Niveau 3, Tuning

Calcul des statistiques

Afficher les valeurs des paramètres utlisés par défaut par dbms_stats

col sname for a30
col SPARE4 for a50

select SNAME, spare4 from SYS.OPTSTAT_HIST_CONTROL$ ;

SNAME                          SPARE4
------------------------------ --------------------------------------------------
SKIP_TIME
STATS_RETENTION
TRACE                          0
DEBUG                          0
SYS_FLAGS                      1
CASCADE                        DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT               DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE                         NULL
METHOD_OPT                     FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE                  TRUE
GRANULARITY                    AUTO
AUTOSTATS_TARGET               AUTO

12 rows selected.

Les valeurs ici ne sont déjà pas les valeurs utilisée par oracle par défaut, NO_INVALIDATE a été modifiée

Ecrire une procédure de calcul des statistiques

Pour un client, j’ai écrit très rapidement cette procédure de calcul de statistiques. Très (trop) imparfaite

create or replace procedure sys.calc_stats is
    /*
     * Procédure de calcul des statistiques            ****************************
     *
     * Les statistiques son calculees sur les tables dont le cumul des modifications
     * (insert / delete / updates) dépasse 5% du volume de la table. Elle invalide
     * les plans en memoire et ignore ertains utilisateurs. Son parallelisme est de 4
     * et les statistiques ne sont pas calculees sur les index de la table
     *
     * ******************************************************************************
     * Version 1.0 pour ************ sur ORA 102040
     * ******************************************************************************
     */
begin
  for c in (
   select tm.table_name tbn
        , t.owner tbo
   from ( select table_name
               , table_owner
               , sum(inserts) inserts
               , sum(deletes) deletes
               , sum(updates) updates
          from dba_tab_modifications
          where table_owner not in ( 'SYS', 'SYSTEM', 'SYSMAN', 'TOOLS'
                                 , 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'GOLDENGATE'
                                 , 'XDB', 'TOAD', 'OUTLN', 'TSMSYS', 'ORDSYS')
          group by table_name, table_owner ) tm
        inner join dba_tables t
                   on ( t.owner=tm.table_owner
                    and t.table_name=tm.table_name )
   where 1=1
     and t.owner not in ( 'SYS', 'SYSTEM', 'SYSMAN', 'TOOLS'
                        , 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'GOLDENGATE'
                        , 'XDB', 'TOAD', 'OUTLN', 'TSMSYS', 'ORDSYS')
     and (tm.inserts+tm.updates+tm.deletes)/(nvl(t.num_rows,0)+1) > 0.05
      order by nvl(t.num_rows,0) asc  )
  loop
      dbms_stats.gather_table_stats(c.tbo, c.tbn, null, null, null, 'for all indexed columns size repeat', 4, 'ALL', FALSE, null, null, null, FALSE );
  end loop;
end;
/

Manque du paramétrage et de l’automatisme

Paramétrage
  • seuil de calcul
  • no_invalidate
Automatismes
  • Echantillonnage
  • Cascade

A faire évoluer donc …

Exploitation, Support Niveau 3, Tuning

Afficher la liste des “fix control” passés sur une base

Les fix control sont apportés par les patchs / patchsets / patchsets updates et influent sur le comportement de l’optimiseur. La table x$qksbgsys les liste et donne leur description succincte

La requête suivante liste les bugs, ce qu’ils font et la version à partir de laquelle ils ont étés introduits

select BUGNO_QKSBGSYROW
  , DESC_QKSBGSYROW
  , OFE_QKSBGSYROW
  , EVENT_QKSBGSYROW
from x$qksbgsys
/

BUGNO_QKSBGSYROW DESC_QKSBGSYROW                                                  OFE_QKSBGSYROW            EVENT_QKSBGSYROW
---------------- ---------------------------------------------------------------- ------------------------- ----------------
        4611850 do not clobber predicate during first-k row estimate phase       10.2.0.2                                 0
        4663804 use smallest table as first table in join card. initial ordering 10.2.0.2                                 0
        4663698 for cached NL table set tab_cost_io to zero                      10.2.0.2                                 0
[...]

La requete suivante sur la table x$qksbgses est équivalente

select distinct BUGNO_QKSBGSEROW
   , DESC_QKSBGSEROW
   , OFE_QKSBGSEROW
   , EVENT_QKSBGSEROW
from x$qksbgses
/

Il est possible de désactiver un des correctifs par hint :

select /*+ OPT_PARAM('_fix_control' '9195582:0') */ * from dual ;

Depuis quelques version les vues V$SYS_FIX_CONTROL et V$SESSION_FIX_CONTROL permettent de voir ces données plus facilement

desc V$SESSION_FIX_CONTROL
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SESSION_ID                                NUMBER
 BUGNO                                     NUMBER
 VALUE                                     NUMBER
 SQL_FEATURE                               VARCHAR2(64)
 DESCRIPTION                               VARCHAR2(64)
 OPTIMIZER_FEATURE_ENABLE                  VARCHAR2(25)
 EVENT                                     NUMBER
 IS_DEFAULT                                NUMBER

desc V$SYSTEM_FIX_CONTROL
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 BUGNO                                     NUMBER
 VALUE                                     NUMBER
 SQL_FEATURE                               VARCHAR2(64)
 DESCRIPTION                               VARCHAR2(64)
 OPTIMIZER_FEATURE_ENABLE                  VARCHAR2(25)
 EVENT                                     NUMBER
 IS_DEFAULT                                NUMBER
Bonnes pratiques, Développement, Exploitation, Métrologie, PL/SQL, SQL, Support Niveau 2, Tuning

Générer des rapports statspack

Petite procédure pour générer un nombre important de rapports statspack.

SET SERVEROUTPUT OFF
set feedback off

var delta number;
var date_fin varchar2(20);
begin
:delta:=&1;
--  :date_fin:=&2;
end;
/

create or replace procedure dba_all_reports( delta in number
                                         , fin in varchar default to_char(sysdate, 'YYYYMMDDHH24MI') ) as
begin
for i in (select snap_id
        , snap_next
        , to_char(snap_time, 'MMDD_HH24MI') rpt_name
        , instance_name
      from (select s.SNAP_ID
              , lead(s.SNAP_ID,1) over (order by s.SNAP_ID) SNAP_NEXT
              , s.STARTUP_TIME
              , lead(s.STARTUP_TIME, 1) over (order by s.SNAP_ID) STARTUP_NEXT
              , s.SNAP_TIME
              , i.INSTANCE_NAME
            from stats$snapshot s, v$instance i)
      where SNAP_NEXT=SNAP_ID+1
      and STARTUP_TIME=STARTUP_NEXT
      and snap_time between to_date(fin,'YYYYMMDDHH24MI')-delta and to_date(fin,'YYYYMMDDHH24MI') )
loop
DBMS_OUTPUT.ENABLE (32000);
dbms_output.put_line ('define begin_snap='||i.snap_id);
dbms_output.put_line ('define end_snap='||i.snap_next);
dbms_output.put_line ('define report_name='||i.instance_name||'_'||i.rpt_name);
dbms_output.put_line ('@?/rdbms/admin/spreport.sql');
end loop;
end;
/

SET SERVEROUTPUT ON
spool reports.sql
-- execute dba_all_reports(:delta, :date_fin)
execute dba_all_reports(:delta)
spool off

@reports.sql

drop procedure dba_all_reports;

Les rapports ainsi générés sont nommés <ORACLE_SID>_<MOIS><JOUR>_<HEURE><MINUTES>.lst

On peut décommenter les lignes

--  :date_fin:=&2;

et

-- execute dba_all_reports(:delta, :date_fin)

et supprimer la ligne

execute

dba_all_reports(:delta)

de manière à générer des rapports anciens.