Catégorie : SQL

Développement, SQL

Trouver le DDL d’une vue dynamique (V$… ou GV$…)

On utilise v$fixed_view_definition

?
01
02
03
SELECT *
FROM v$fixed_view_definition
where view_name = upper('v$fixed_view_definition')

On obtient : 

V$FIXED_VIEW_DEFINITION
SELECT * FROM gv$fixed_view_definition where inst_id = USERENV('Instance')

Et donc, comme on a autre chose que du fromage blanc entre les oreille on passe la requête suivante :

?
01
02
03
SELECT *
FROM v$fixed_view_definition
where view_name = upper('gv$fixed_view_definition')

Pour finalement obtenir :

GV$FIXED_VIEW_DEFINITION
select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.indx

C’était pourtant pas si compliqué

Développement, SQL

Requêtage du vide 1/2

Je veux générer la consommation de temps de chaque SQL_ID sur une période donnée, j’utilise ASH (ou plus exactement v$active_session_history).

Je définis un certain nombre de variables pour rendre le code lisible et ré-utilisable.

  • deb: date de début de l’échantillonnage
  • fin: date de fin de l’échantillonnage
  • nbsec: nombre de seconde d’aggrégation
  • nbVals: nombre de valeurs à générer dans l’intervalle d’échantillonnage
  • DBID: commenté, pourrait serveir à travailler avec DBA_HIST_ACTIVE_SESS_HISTORY
?
01
02
03
04
05
06
var deb varchar2(25)
var fin varchar2(25)
var frmt varchar2(25)
var nbsec number
var nbVals number
-- var dbid number

Je positionne les valeurs des variables, je veux échantillonner sur les 10 dernières minutes

?
01
02
03
04
05
-- exec select dbid into :dbid from v$database
exec :frmt :=  'YYYYMMDDHH24MISS'
exec select to_char(trunc( sysdate - interval '10' minute , 'MI'), :frmt ), to_char( trunc (sysdate, 'MI'), :frmt) into :deb, :fin from dual
exec :nbsec := 60
exec :nbVals := 10

La requête est la suivante (et elle n’est forcément pas simple)

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
with dts as ( select  to_char( to_date(:deb, :frmt) 
                             + ( rownum/ ( 60 *60 *24 ) ) * :nbsec 
                             , :frmt ) dt
              from dual
              connect by rownum <= :nbVals ) ,
-- ----------------------------------------------------------------------------------
    vals as ( select to_char(trunc(SAMPLE_TIME, 'MI'), :frmt) sample_time_min       
--                   , INSTANCE_NUMBER
--                   , DBID   
                   , SQL_ID
                   , count(*) sec_per_min
--                   , count(*) * 10 sec_per_min
              from v$active_session_history
--              from dba_hist_active_sess_history
              where SAMPLE_TIME between to_date(:deb, :frmt) and to_date(:fin, :frmt)
--                and DBID = :dbid
              group by trunc(SAMPLE_TIME, 'MI')          
--                     , INSTANCE_NUMBER
--                     , DBID
                     , SQL_ID ),
-- ----------------------------------------------------------------------------------
  TOPSQL as ( select SQL_ID
                   , sum(sec_per_min) pos
              from vals
              group by sql_id
              order by 2 desc, sql_id asc )
-- ----------------------------------------------------------------------------------
select dt
     , pos
     , sql_id
     , max(sec_per_min) sec_per_min
from ( Select distinct dt
            , sql_id
            , pos
            , case when dt=sample_time_min then sec_per_min
                   else                         0           end  sec_per_min
       from dts, vals natural join TOPSQL )
group by dt, sql_id, pos
order by dt, sql_id, pos desc
/

Décorticons

  • La sous requête factorisée DTS :
    ?
    01
    02
    03
    04
    05
    select  to_char( to_date(:deb, :frmt) 
                   + ( rownum/ ( 60 *60 *24 ) ) * :nbsec 
                   , :frmt ) dt
    from dual
    connect by rownum <= :nbVals

    permet de générer les dates d’échantillons à retenir sous forme d’horodatage régulier.

  • La sous requête factorisée VALS (sans les commentaires qui permettent de travailler en mode historique)
    ?
    01
    02
    03
    04
    05
    06
    07
    select to_char(trunc(SAMPLE_TIME, 'MI'), :frmt) sample_time_min          
         , SQL_ID
         , count(*) sec_per_min
    from v$active_session_history
    where SAMPLE_TIME between to_date(:deb, :frmt) and to_date(:fin, :frmt)
    group by trunc(SAMPLE_TIME, 'MI')          
           , SQL_ID

    permet de relier les SQL_ID à leur charge

  • La sous requête factorisée TOPSQL
    ?
    01
    02
    03
    04
    05
    select SQL_ID
         , sum(sec_per_min) pos
    from vals
    group by sql_id
    order by 2 desc, sql_id asc )

    permet de calculer la charge globale de chaque SQL_ID et de les classer.

  • LA requête en tant que telle effectue le produit cartésien et ne retient que ce qui l’intéresse … C’est cher payé, mais ça donne le résultat escompté
    ?
    01
    02
    03
    04
    05
    06
    07
    08
    09
    10
    11
    12
    select dt
         , pos
         , sql_id
         , max(sec_per_min) sec_per_min
    from ( Select distinct dt
                , sql_id
                , pos
                , case when dt=sample_time_min then sec_per_min
                       else                         0           end  sec_per_min
           from dts, vals natural join TOPSQL )
    group by dt, sql_id, pos
    order by dt, sql_id, pos desc

Le résultat avec un peu de présentation ( break on dt skip 1 ) :

DT                               POS SQL_ID        SEC_PER_MIN
------------------------- ---------- ------------- -----------
20150427211800                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v           0

20150427211900                    10 a1xgxtssv5rrp          10
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v           0

20150427212000                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v           0

20150427212100                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v           0

20150427212200                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v           0

20150427212300                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j          10
                                  10 gv1qak5fn39tk          10
                                  10 935y1fyshgm9v           0

20150427212400                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a          10
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v           0

20150427212500                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v           0

20150427212600                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v          10

20150427212700                    10 a1xgxtssv5rrp           0
                                  10 cyz7q9n00p23a           0
                                  10 d0v9kqgaysm5j           0
                                  10 gv1qak5fn39tk           0
                                  10 935y1fyshgm9v           0


50 lignes sélectionnées.

Certes il fallait y réfléchir un peu plus d’une demi seconde mais …

C’était pourtant pas si compliqué.

Ca reste pourtant très perfectible. On va la remettre sur le métier, histoire que ça ne soit pas du vite-fait mal-fait. Suite dans un prochain épisode.

Développement, SQL

Générer un horodatage régulier

Problématique : on veut générer une suite de dates (surtout les heures, minutes et secondes) depuis en SQL pur

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
-- nombre de secondes
var nbsec number
 
-- nombre de valeurs souhaitées
var nbVals number
 
-- On veut les 15 premiers multiples de 19 secondes
exec :nbsec := 19
exec :nbVals := 15
 
-- La requête
select  to_char( trunc(sysdate-1)
               + ( rownum/ ( 60 *60 *24 ) ) * :nbsec 
               , 'HH24:MI:SS' ) dt
from dual
connect by rownum <= :nbVals ;

Résultat

DT
--------
00:00:19
00:00:38
00:00:57
00:01:16
00:01:35
00:01:54
00:02:13
00:02:32
00:02:51
00:03:10
00:03:29
00:03:48
00:04:07
00:04:26
00:04:45

15 lignes sélectionnées.

C’était pourtant pas si compliqué.

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é.

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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 &quot;ROWS&quot;
  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…)

Bonnes pratiques, Développement, Exploitation, SQL, Support Niveau 2, Support Niveau 3

SQLPlus l(e seul)’outil qu’il vous faut pour administrer vos bases de données Oracle

Cet article est repris d’un ancien blog et fusionné avec un autre

SQLPlus est l’outil incontournable du DBA Oracle. Même si beaucoup n’utilisent que des interfaces graphiques, il arrive un moment ou un autre où il doit se connecter à une machine qui ne propose pas cette commodité et dès lors, l’outil basique, livré avec le moteur, devient un passage obligé voire incontournable ou presque.

Je liste ici quelques trucs, astuces, commandes qui permettent d’utiliser SQLPlus de manière sympathique et sans trop de prise de tête.

(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…)

Développement, SQL

sql2csv ou comment générer un fichier csv depuis SQL*PLUS

La question revient souvent sur les forums : comment produire un fichier csv depuis SQL*Plus …

Une solution a été donnée sur le forum Developpez.net, elle est copiée ici dans une version un peu remaniée et très légèrement plus générique

L’appel est simple depuis sqlplus: @sql2csv <nom du fichier contenant la(les) requête(s) à exporter en csv>

-- merci à skuatamad (http://www.developpez.net/forums/u253956/skuatamad/)
  --
  -- 1 paramètre : le nom du script sql à exécuter
  -- En sortie un fichier CSV
  --
  
  store set settings_sqlplus replace
  
  set echo off
  set feedback off
  set linesize 32767
  set pages 0 emb on newp none
  set sqlprompt ''
  set trimspool on
  set long 9999999
  set longc 9999999
  set colsep ';'
  set heading on
  set recsep off
  set headsep on
  set underline off
  
  spool &&1..csv
  @&&1
  spool off
  
  undef 1
  @settings_sqlplus

Par exemple avec cette requête sql stockée dans le fichier sessions.sql :

select sid
     , serial#
     , process
     , sql_id
     , last_call_et
     , status
  from v$session ;

On obtient génère le fichier csv de la manière suivante:

SQL> @sessions

       SID    SERIAL# PROCESS      SQL_ID        LAST_CALL_ET STATUS
---------- ---------- ------------ ------------- ------------ --------
       146         10 4312         4gd6b1r53yt88        60116 ACTIVE
       151          2 4308                              60116 ACTIVE
       157          7 3836                              60126 ACTIVE
       159         46 6640:5200    g8730nuf9c1xf            0 ACTIVE
       160          1 3796                              60133 ACTIVE
       161          1 3792         4gd6b1r53yt88        60133 ACTIVE
       162          1 3788                              60133 ACTIVE
       163          1 3784                              60133 ACTIVE
       164          1 3780                              60133 ACTIVE
       165          1 3776                              60133 ACTIVE
       166          1 3772                              60133 ACTIVE
       
       SID    SERIAL# PROCESS      SQL_ID        LAST_CALL_ET STATUS
---------- ---------- ------------ ------------- ------------ --------
       167          1 3768                              60133 ACTIVE
       168          1 3764                              60133 ACTIVE
       169          1 3760                              60133 ACTIVE
       170          1 3756                              60133 ACTIVE
       
15 rows selected.

SQL> @sql2csv sessions
Wrote file settings_sqlplus
       SID;   SERIAL#;PROCESS     ;SQL_ID       ;LAST_CALL_ET;STATUS
       146;        10;4312        ;4gd6b1r53yt88;       60140;ACTIVE
       151;         2;4308        ;             ;       60140;ACTIVE
       157;         7;3836        ;             ;       60150;ACTIVE
       159;        46;6640:5200   ;g8730nuf9c1xf;           0;ACTIVE
       160;         1;3796        ;             ;       60157;ACTIVE
       161;         1;3792        ;4gd6b1r53yt88;       60157;ACTIVE
       162;         1;3788        ;             ;       60157;ACTIVE
       163;         1;3784        ;             ;       60157;ACTIVE
       164;         1;3780        ;             ;       60157;ACTIVE
       165;         1;3776        ;             ;       60157;ACTIVE
       166;         1;3772        ;             ;       60157;ACTIVE
       167;         1;3768        ;             ;       60157;ACTIVE
       168;         1;3764        ;             ;       60157;ACTIVE
       169;         1;3760        ;             ;       60157;ACTIVE
       170;         1;3756        ;             ;       60157;ACTIVE
       
SQL> ho type sessions.csv
       SID;   SERIAL#;PROCESS     ;SQL_ID       ;LAST_CALL_ET;STATUS
       146;        10;4312        ;4gd6b1r53yt88;       60140;ACTIVE
       151;         2;4308        ;             ;       60140;ACTIVE
       157;         7;3836        ;             ;       60150;ACTIVE
       159;        46;6640:5200   ;g8730nuf9c1xf;           0;ACTIVE
       160;         1;3796        ;             ;       60157;ACTIVE
       161;         1;3792        ;4gd6b1r53yt88;       60157;ACTIVE
       162;         1;3788        ;             ;       60157;ACTIVE
       163;         1;3784        ;             ;       60157;ACTIVE
       164;         1;3780        ;             ;       60157;ACTIVE
       165;         1;3776        ;             ;       60157;ACTIVE
       166;         1;3772        ;             ;       60157;ACTIVE
       167;         1;3768        ;             ;       60157;ACTIVE
       168;         1;3764        ;             ;       60157;ACTIVE
       169;         1;3760        ;             ;       60157;ACTIVE
       170;         1;3756        ;             ;       60157;ACTIVE

Convenons-en c’est dune laideur absolue même si dans bien des cas ça suffit.

La version 12 apporte à sqlplus l’option CSV à “SET MARKUP” qui a pour effet de créer un fichier CSV normalisé

set markup csv on delim ";"
 
select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED from all_tables where rownum &lt; 11 ;
 
"OWNER";"TABLE_NAME";"TABLESPACE_NAME";"STATUS";"LAST_ANALYZED"
"SYS";"DUAL";"SYSTEM";"VALID";"24-FEB-21"
"SYS";"SYSTEM_PRIVILEGE_MAP";"SYSTEM";"VALID";"24-JAN-21"
"SYS";"TABLE_PRIVILEGE_MAP";"SYSTEM";"VALID";"24-JAN-21"
"SYS";"USER_PRIVILEGE_MAP";"SYSTEM";"VALID";"24-JAN-21"
"SYS";"STMT_AUDIT_OPTION_MAP";"SYSTEM";"VALID";"24-JAN-21"
"SYS";"AV_DUAL";"SYSTEM";"VALID";"24-JAN-21"
"SYS";"WRR$_REPLAY_CALL_FILTER";"SYSAUX";"VALID";"24-JAN-21"
"SYS";"SCHEDULER_FILEWATCHER_QT";"SYSTEM";"VALID";
"SYS";"HS_BULKLOAD_VIEW_OBJ";"SYSTEM";"VALID";"24-JAN-21"
"SYS";"HS$_PARALLEL_METADATA";"SYSTEM";"VALID";"24-JAN-21"
 
10 rows selected.

Mieux non ?

Bonnes pratiques, Exploitation, SQL

Mes Tricks SQL*Plus

Je suis DBA depuis poulala et je m’entête à ne pas utiliser d’interfaces graphiques coassantes ou non pour administrer mes bases de données pour les bonnes (ou mauvaises) raisons que :

  • C’est pas assez hardcore
  • Ça fait des trucs sans me prévenir
  • Faudrait que j’aprenne à m’en servir et j’ai d’autres chats à fouetter
  • J’aime pas wind….s
  • Le seul java que j’aime est de genre féminin
  • On a (presque) toujours un petit SQL*PLUS sous la main

Donc j’utilise SQL*Plus et son interface si (peu) riche. Alors pour les timides qui n’osent pas entrer dans ce formidable outil et qui un jour s’y retrouvent contraints je couche ici quelques raccourcis bien pratiques qui rendent l’outil puissant, formidable voire incontournable si on a besoin d’aller vraiment vite.

Se connecter à une base avec sqlplus

Si votre tnsnames.ora est renseigné

$ sqlplus
[...]
Entrez le nom utilisateur : <utilisateur>/<mot de passe>@<alias tns>
[...]
SQL>

Si votre tnsnames.ora n’est pas renseigné

$ sqlplus
[...]
Entrez le nom utilisateur : <utilisateur>/<mot de passe>@//<serveur>:<port>/<service>
[...]
SQL>

Voir les constantes définies

SQL> def
DEFINE _DATE           = "09/10/12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "//127.0.0.1:1521/orcl" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000300" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000300" (CHAR)
DEFINE _RC             = "1" (CHAR)
SQL>

Utiliser un dépôt de scripts centralisé

SQL> def r=http://depot/scripts/sql
SQL> @&r/cre_super_user.sql
Utilisateur cree, compte verouille

On peut s’amuser à écrire les scripts dans un langage web comme PHP et donc protéger l’accès au scripts (plus ou moins).

Affecter et visualiser les variables de liaison (bind variables) définies dans la session

SQL> variable toto number
SQL> exec :toto:=7
SQL> variable titi number
SQL> exec :titi:=9
SQL> print

      TOTO
----------
         7

      TITI
----------
         9

Sauvegarder et restaurer les settings avant de les modifier

Sauvegarder

SQL> store set settings_sqlplus replace

Restaurer

SQL> @settings_sqlplus

Créer un ficher de sortie propre

SQL> set ver off       -- evite l'affichage des réécritures de constantes
SQL> set lin 32767     -- taille maxi d'une ligne
SQL> set trimspool on  -- enlève les blancs en fin de ligne dans le fichier de sortie
SQL> set pages 50000   -- taille maxi d'une page ou set pages 0 emb on newp none L. Schneider :
                       -- http://laurentschneider.com/wordpress/2007/06/sqlplus-pagesize-explained.html
SQL> set termout off   -- évite la sortie sur écran, seul le fichier sera renseigné
SQL> set echo off      -- évite l'affichage des requêtes et donc leur écriture 
SQL> set feed off      -- évite les infos de fin de requêtes (n lignes ...)
SQL> set timi off      -- évite le chronométrage en fin de requête
SQL> set autot  off    -- évite la production d'un plan en fin de requête
SQL> set long 9999999  -- permet l'affichage de longs de 9999999 octets
SQL> set longc 9999999 -- permet l'affichage de longs de 9999999 caractères*

SQL> clear computes    -- supprime les agrégations automatiques de colonnes
SQL> clear columns     -- supprime les formatages de colonnes
SQL> clear breaks      -- supprime les sauts

SQL> TTITLE    off     -- supprime les titres de haut de requête (ou de pages)  
SQL> BTITLE    off     -- supprime les titres de bas de requête
SQL> REPHEADER off     -- supprime les en-tête de rapports
SQL> REPFOOTER off     -- supprime les pieds de rapports

SQL> spool <fichier de sortie>
SQL> -- instructions
SQL> spool off

Concatener des constantes

SQL> def NOM_FIC=monfic
SQL> spool &&MON_FIC..txt
SQL> -- instructions
SQL> spool off

Impossible de se connecter mais analyse requise

$ sqlplus -prelim

SQL*Plus: Release 11.2.0.3.0 Production on Mer. Oct. 10 08:36:15 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Entrez le nom utilisateur : / as sysdba
SQL>

Cette connexion est dite préliminaire et ne permet que d’utiliser oradebug ou de faire un shutdown abort
Voilà déjà de quoi s’amuser. Avec ces quelques infos/trucs vous pouvez administrer efficacement tout un parc de bases de données sans passer par des interfaces lourdes et (bien souvent) inutiles. Reste à connaitre le SQL et le catalogue Oracle mais après tout vous êtes DBA non ?

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 …

Développement, PL/SQL, SQL

Nombre de colonnes variable et PL/SQL

Ce que je veux faire

Dans mon post précédent, j’ai créé une fonction qui me permet d’afficher sous forme de tableau à deux dimensions diverses informations tirées d’une table. Nommée pompeusement transposition, le requête permet entre autres d’afficher en ligne ou en colonne, le topn sql_id consommateurs sur un wait event et leur évolution sur un certain nombre de clichés.

Maintenant, je voudrais exploiter ces données dans du code PL/SQL, j’ai donc besoin de créer du code qui saura s’adapter au nombre de colonnes remontées. Un premier code permet d’afficher les valeurs

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
declare
  requete  clob;
  nb_cols  number;
  tab_cols t_varchar;
  auto_plsql clob;
  ttext    varchar2(32767);
begin
  p_trsp('sql_id'                              
       , 'snap_id'
       , 'dba_hist_sqlstat'
       , 'EXECUTIONS_DELTA'
       , 'sys'
       , '834'
       , '840'
       , 18
       , 'yes'
       , requete
       , nb_cols
       , tab_cols);
  auto_plsql :='begin
for c in (';
  dbms_lob.append(auto_plsql,requete);
  ttext:=')
loop
dbms_output.put(to_char(c.snap_id)||'' '');
';
  dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  for i in 1..nb_cols
  loop
    ttext:='dbms_output.put(to_char(c.c_'||trim(to_char(i))||',''9999'')||'' '');
';
    dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  end loop;
  ttext:='dbms_output.put_line('' '');
end loop;
end;';
  dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  execute immediate auto_plsql;
end;
/

Ce dui donne :

834     0  1803  1730  1430  1024     0   414     0     0   339   312   298   269     0   318   213   209   256  
835  2085  1493  1316   905   952   723   291   714     0   229   234   233   232   241   184   187   181   161   
836  2068  1490  1308   862   944   720   291   708   708   229   228   228   228   241   175   184   180   134   
837  1962  1466  1265   870   892   712   292     0     0   228   228   228   228   237   170   181   176   133   
838  1644  1490  1096   700   732   718   291     0     0   229   231   231   231   241   175   184   172   132   
839  1909  1494  1275  1064   864   721   328   648   648   229   235   233   231   241   193   184   178   154   
840  2028  1490  1288   858   924   723   291     0   693   230   231   231   231   241   175   184   180   133

Et si on limite topn à 4 (donc 4 colonnes + snap id) on obtient :

834     0  1803  1730  1430
835  2085  1493  1316   905
836  2068  1490  1308   862
837  1962  1466  1265   870
838  1644  1490  1096   700
839  1909  1494  1275  1064
840  2028  1490  1288   858

Et si on va un poil plus loin pour traiter le résultat des colonnes dans du PL/SQL …

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
CREATE OR REPLACE
PACKAGE OJO_RPT
AS
PROCEDURE report(
    debut IN varchar2,
    fin   IN varchar2,
    topn  IN NUMBER);
END OJO_RPT;
/
 
CREATE OR REPLACE
PACKAGE body OJO_RPT
AS
-- private proc
type t_varchar is table of varchar2(1000);
 
procedure p_trsp   ( lig   in varchar2         -- Colonne qui sera utilisee en tete de ligne
         , col    in varchar2         -- Colonne qui sera utilisee en tete de colonne
         , ltab   in varchar2         -- Table source
         , pivot  in varchar2         -- Colonne qui sera utilisee pour remplir le tabeau
         , owner  in varchar2         -- Proprietaire de la table
         , mini   in varchar2         -- Valeur minimale pour col
         , maxi   in varchar2         -- Valeur maximale pour col
         , topn   in number           -- Nombre de lig affichees ordonnes par cumul de pivot descendant
         , trsp  in varchar2 := 'No'  -- Transposition de la table (lig en tete de colonne et col en tete de ligne)
                  , req   out clob
                  , ncol  out number
                  , t_ncl out t_varchar ) is
 v_col    varchar2(32767); 
 v_where  varchar2(32767);  -- clause where pour mini et maxi
 v_wtopn  varchar2(1000);   -- clause where pour topn
 v_owner  varchar2(255);   
 c1  sys_refcursor;   
 v_poreq  varchar2(32767); 
  i               number;
begin
        t_ncl:=t_varchar();
 -- Gestion de la variable topn, si null on prend tout
 if topn is null
 then
   v_wtopn:=' ';
 else
   v_wtopn:=' where rownum <= '||topn;
 end if;
 
 -- Gestion de la variable owner, si null on prend ls schema appelant
 if owner is null
 then
  select sys_context('USERENV','CURRENT_SCHEMA') into v_owner from sys.dual;
 else
  v_owner:=owner;
 end if;
  
 -- Gestion des variables mini et maxi, si null on prend tout
 if ( mini is not null or maxi is not null )
 then
  v_where:=' where ';
  if mini is not null
  then
   v_where:=v_where||col||' >
= '||mini ;
   if maxi is not null
   then
       v_where:=v_where||' and '||col||' <= '||maxi ;
   end if;
  else
      v_where:=v_where||col||' <= '||maxi ;
  end if;
 end if;
  
    -- Si on ne transpose pas lig et col 
 if upper(trsp) = 'NO'
 then
   req:='select * from (Select '||lig||' c_0
, sum('||pivot||') total ';
          i:=1;
   open c1 for 'select distinct to_char('||col||') from '||v_owner||'.'||ltab||v_where||' order by 1' ;
   loop
    fetch c1 into v_col;
    exit when c1%notfound;
    v_poreq:='
   , sum(decode(to_char('||col||'),'''||v_col||''','||pivot||',0)) c_'||trim(to_char(i)) ;
    dbms_lob.writeappend(req,length(v_poreq),v_poreq);
                  t_ncl.extend();
                  t_ncl(i):=trim(to_char(v_col));
                  i:=i+1;
   end loop;
          ncol:=c1%rowcount;
          close c1;
   v_poreq:='
from '||v_owner||'.'||ltab||v_where||'
group by '||lig||'
order by 2 desc)'||v_wtopn ;
   dbms_lob.writeappend(req,length(v_poreq),v_poreq);
 
    -- Si on transpose lig et col 
 else
   req:='Select '||col||' c_0';
          i:=1;
   open c1 for 'select ent from (select distinct to_char('||lig||') ent, sum('||pivot||') tot from '||v_owner||'.'||ltab||v_where||' group by to_char('||lig||') order by 2 desc)'||v_wtopn ;
   loop
     fetch c1 into v_col;
     exit when c1%notfound;
     v_poreq:='
   , sum(decode(to_char('||lig||'),'''||v_col||''','||pivot||',0)) c_'||trim(to_char(i)) ;
     dbms_lob.writeappend(req,length(v_poreq),v_poreq);
                          t_ncl.extend();
                          t_ncl(i):=trim(to_char(v_col));
                          i:=i+1;
   end loop;
          ncol:=c1%rowcount;
   close c1;
   v_poreq:='
from '||v_owner||'.'||ltab||v_where||'
group by '||col||'
order by 1 asc';
   dbms_lob.writeappend(req,length(v_poreq),v_poreq);
 end if;       
end p_trsp;
 
procedure trt( t in t_varchar ) is
  vt varchar2(32767);
  c  sys_refcursor;
begin
  open c for 'select * from table(t)';
  loop
    fetch c into vt;
    exit when c%notfound;
    dbms_output.put_line(vt);
  end loop;
end trt;
 
 
-- public proc
PROCEDURE report(
    debut IN varchar2,
    fin   IN varchar2,
    topn  IN NUMBER) is
  requete  clob;
  nb_cols  number;
  tab_cols t_varchar;
  auto_plsql clob;
  ttext varchar2(32767);
begin
  for c0 in (select column_name from dba_tab_columns where table_name='DBA_HIST_SQLSTAT' and owner='SYS' and column_name like '%DELTA')
  loop
  p_trsp('sql_id'                              
       , 'snap_id'
       , 'dba_hist_sqlstat'
       , c0.column_name
       , 'sys'
       , debut
       , fin
       , topn
       , 'no'
       , requete
       , nb_cols
       , tab_cols);
  auto_plsql :='begin
for c in (';
  dbms_lob.append(auto_plsql,requete);
  ttext:=')
loop
dbms_output.put(to_char(c.c_0)||'' '');
';
  dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  for i in 1..nb_cols
  loop
    ttext:='dbms_output.put(to_char(c.c_'||trim(to_char(i))||',''99999999'')||'' '');
';
    dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  end loop;
  ttext:='dbms_output.put_line('' '');
end loop;
end;';
  dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
  dbms_output.put_line(c0.column_name);
  execute immediate auto_plsql;
  end loop;
end report;
 
END OJO_RPT;
/

Le résultat de cette requête étant une succession de tableaux, dont je donne ici les trois premiers :

FETCHES_DELTA
cm5vu20fhtnq1      6549      5494      5490      5401      5490      5497      5490
8t43xdhf4d9x2         0      2085      2068      1962      1644      1909      2028
0k8522rmdzg4k      1976      1604      1600      1575      1600      1606      1600
089dbukv1aanh      1730      1316      1308      1265      1096      1275      1288
aykvshm7zsabd         0         0      2640      2618         0         0         0
5ms6rbzdnq16t         0       723       720       712       718       721       723
83taa7kaw59c1      3587         0         0         0         0         0         0
5kyb5bvnu3s04       414       291       291       292       291       328       291
7qqnad1j615m7         0       714       708         0         0       648         0
5hfunyv38vwfp         0         0       708         0         0       648       693
END_OF_FETCH_COUNT_DELTA
8t43xdhf4d9x2         0      2085      2068      1962      1644      1909      2028
cm5vu20fhtnq1      1803      1493      1490      1466      1490      1494      1490
089dbukv1aanh      1730      1316      1308      1265      1096      1275      1288
8vwv6hx92ymmm      1430       905       862       870       700      1064       858
6v7n0y2bq89n8      1024       952       944       892       732       864       924
5ms6rbzdnq16t         0       723       720       712       718       721       723
5kyb5bvnu3s04       414       291       291       292       291       328       291
7qqnad1j615m7         0       714       708         0         0       648         0
5hfunyv38vwfp         0         0       708         0         0       648       693
43c5ykm1mcp2a       312       234       228       228       231       235       231
SORTS_DELTA
5ms6rbzdnq16t         0       723       720       712       718       721       723
fsbqktj5vw6n9       213       187       184       181       184       184       184
4jrfrtx4u6zcx         0       122       122       128       122       150       122
aykvshm7zsabd         0         0       360       357         0         0         0
db78fxqxwxt7r       447         0         0         0         0         0         0
btwkwwx56w4z6         0        60        60        59        60        60        60
75u022kdra1fz         0        60        60        59        60        60        60
7ng34ruy5awxq       338         0         0         0         0         0         0
grb1cf30q5r7m       314         0         0         0         0         0         0
gvt8zu1k1tsff         0       122         0         0         0       150         0