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

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

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

La 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

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 ?

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 …

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

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 …

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

Transposition de tables

Ce que je souhaite faire

Je souhaite rendre lisible sous excel une table historique, par exemple pour dbs_hist_snapshot, je souhaite voir pour le top 10 des requêtes consommatrices en temps elapse les différents temps elapse pour une fenêtre de snapshots … idéalement je souhaite avoir en en tête de ligne le sql_id, en en tête de colonne le snap_id et dans les cases du tableau les valeurs de temps elapse pour la requêete au moment du snapshot … Un petit schéma ?

SQL_ID             TOTAL      C_834      C_835      C_836      C_837      C_838      C_839      C_840
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
6gvch1xu9ca3g   42391392   13013927    5392808    5393650    4953967    3261169    4969584    5406287
459f3z9u4fb3u   23436549   13594062    1852118    2019437    1463763    1243657    1659715    1603797
7mdacxfm37utk   17700940    3612265    2736617    2562545    2386164    1282571    2257281    2863497
2b064ybzkwf1y    7346698    1214429    1087398    1132594    1069030     781162    1021148    1040937
70vs1d7ywk5m0    5545662    5545662          0          0          0          0          0          0
a8j39qb13tqkr    5468028    2037413     585054     672582     653276     451146     484936     583621
3am9cfkvx7gq1    5258369     906223     765851     693923     830057     601760     762278     698277
6v7n0y2bq89n8    4980042     970111     742508     774718     700769     417384     657582     716970
2whm2vvjb98k7    4015376    4015376          0          0          0          0          0          0

Je ne travaillerai qu’avec des colonnes centrales numériques, sinon ça ne fonctionne pas …

Générer la requête

J’utilise un code PL/SQL pour générer la requête :

create or replace function f_trsp   ( lig   in varchar2
                                        , col   in varchar2
                                        , ltab  in varchar2
                                        , pivot in varchar2
     , owner in varchar2 
     , ctab  in varchar2 
     , mini  in varchar2 
     , maxi  in varchar2 ) return clob is
v_col   varchar2(32767);
v_where varchar2(32767);
v_owner varchar2(255);
c1 sys_refcursor;
v_poreq varchar2(32767);
v_tab   varchar2(32767);
req     clob;
begin
 req:='
Select '||lig||'
     , sum('||pivot||') total ';
 
 if owner is null
 then 
  select sys_context('USERENV','CURRENT_SCHEMA') into v_owner from sys.dual;
 else
  v_owner:=owner;
 end if;
 
 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;
 
 if ctab is null 
 then
  v_tab:=ltab;
 else
  v_tab:=ctab;
 end if;
   
 open c1 for 'select distinct to_char('||col||') from '||v_owner||'.'||nvl(ctab,ltab)||v_where||' order by 1' ;
 loop
  fetch c1 into v_col;
  exit when c1%notfound;
  v_poreq:='
     , sum(decode('||col||','||v_col||','||pivot||',0)) c_'||trim(v_col) ;
  dbms_lob.writeappend(req,length(v_poreq),v_poreq);
 end loop;
 close c1;
 v_poreq:='
from '||v_owner||'.'||ltab||v_where||'
group by '||lig||'
order by 2 desc';
 dbms_lob.writeappend(req,length(v_poreq),v_poreq);
        return req;
end f_trsp;
/

J’execute le fonction en limitant le nombre de lignes retournées (pour avoir le « top n »

set lines 155 pages 10000 long 999999 longc 999999

desc f_trsp
FUNCTION f_trsp RETURNS CLOB
 Nom d'argument                  Type                    E/S par défaut ?
 ------------------------------ ----------------------- ------ --------
 LIG                            VARCHAR2                IN
 COL                            VARCHAR2                IN
 LTAB                           VARCHAR2                IN
 PIVOT                          VARCHAR2                IN
 OWNER                          VARCHAR2                IN
 CTAB                           VARCHAR2                IN
 MINI                           VARCHAR2                IN
 MAXI                           VARCHAR2                IN


select 'select * from ('||f_trsp('sql_id'
                               , 'snap_id'
                               , 'dba_hist_sqlstat'
                               , 'ELAPSED_TIME_DELTA'
                               , 'sys'
                               , 'dba_hist_snapshot'
                               , '834'
                               , '840')||')
 where rownum < 10 ' from dual
/

'SELECT*FROM('||F_TRSP('SQL_ID','SNAP_ID','DBA_HIST_SQLSTAT','ELAPSED_TIME_D
----------------------------------------------------------------------------
select * from (
Select sql_id
     , sum(ELAPSED_TIME_DELTA) total
     , sum(decode(snap_id,834,ELAPSED_TIME_DELTA,0)) c_834
     , sum(decode(snap_id,835,ELAPSED_TIME_DELTA,0)) c_835
     , sum(decode(snap_id,836,ELAPSED_TIME_DELTA,0)) c_836
     , sum(decode(snap_id,837,ELAPSED_TIME_DELTA,0)) c_837
     , sum(decode(snap_id,838,ELAPSED_TIME_DELTA,0)) c_838
     , sum(decode(snap_id,839,ELAPSED_TIME_DELTA,0)) c_839
     , sum(decode(snap_id,840,ELAPSED_TIME_DELTA,0)) c_840
from sys.dba_hist_sqlstat where snap_id >
= 834 and snap_id <= 840
group by sql_id
order by 2 desc)
 where rownum < 10

Ecoulé : 00 :00 :00.93

Il « suffit » ensuite d’exécuter la requête pour obtenir le tableau

set autotrace on
set timi on

select * from (
Select sql_id
     , sum(ELAPSED_TIME_DELTA) total
     , sum(decode(snap_id,834,ELAPSED_TIME_DELTA,0)) c_834
     , sum(decode(snap_id,835,ELAPSED_TIME_DELTA,0)) c_835
     , sum(decode(snap_id,836,ELAPSED_TIME_DELTA,0)) c_836
     , sum(decode(snap_id,837,ELAPSED_TIME_DELTA,0)) c_837
     , sum(decode(snap_id,838,ELAPSED_TIME_DELTA,0)) c_838
     , sum(decode(snap_id,839,ELAPSED_TIME_DELTA,0)) c_839
     , sum(decode(snap_id,840,ELAPSED_TIME_DELTA,0)) c_840
from sys.dba_hist_sqlstat where snap_id >
= 834 and snap_id <= 840
group by sql_id
order by 2 desc)
where rownum < 10
/

SQL_ID             TOTAL      C_834      C_835      C_836      C_837      C_838      C_839      C_840
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
6gvch1xu9ca3g   42391392   13013927    5392808    5393650    4953967    3261169    4969584    5406287
459f3z9u4fb3u   23436549   13594062    1852118    2019437    1463763    1243657    1659715    1603797
7mdacxfm37utk   17700940    3612265    2736617    2562545    2386164    1282571    2257281    2863497
2b064ybzkwf1y    7346698    1214429    1087398    1132594    1069030     781162    1021148    1040937
70vs1d7ywk5m0    5545662    5545662          0          0          0          0          0          0
a8j39qb13tqkr    5468028    2037413     585054     672582     653276     451146     484936     583621
3am9cfkvx7gq1    5258369     906223     765851     693923     830057     601760     762278     698277
6v7n0y2bq89n8    4980042     970111     742508     774718     700769     417384     657582     716970
2whm2vvjb98k7    4015376    4015376          0          0          0          0          0          0

9 ligne(s) sélectionnée(s).

Ecoulé : 00 :00 :00.54

Plan hash value: 3078857970

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |     2 |   224 |     6  (34)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                         |                  |       |       |            |       |          |       |
|   2 |   VIEW                                 |                  |     2 |   224 |     6  (34)| 00:00:01 |       |       |
|*  3 |    SORT ORDER BY STOPKEY               |                  |     2 |    98 |     6  (34)| 00:00:01 |       |       |
|   4 |     HASH GROUP BY                      |                  |     2 |    98 |     6  (34)| 00:00:01 |       |       |
|   5 |      NESTED LOOPS                      |                  |       |       |            |       |          |       |
|   6 |       NESTED LOOPS                     |                  |     2 |    98 |     4   (0)| 00:00:01 |       |       |
|*  7 |        TABLE ACCESS BY INDEX ROWID     | WRM$_SNAPSHOT    |     1 |    16 |     2   (0)| 00:00:01 |       |       |
|*  8 |         INDEX SKIP SCAN                | WRM$_SNAPSHOT_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
|   9 |        PARTITION RANGE AND             |                  |     1 |       |     1   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 10 |         INDEX RANGE SCAN               | WRH$_SQLSTAT_PK  |     1 |       |     1   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|  11 |       TABLE ACCESS BY LOCAL INDEX ROWID| WRH$_SQLSTAT     |     2 |    66 |     2   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<10)
   3 - filter(ROWNUM<10)
   7 - filter("SN"."STATUS"=0)
   8 - access("SN"."SNAP_ID">
=834 AND "SN"."SNAP_ID"<=840)
       filter("SN"."SNAP_ID">
=834 AND "SN"."SNAP_ID"<=840)
  10 - access("SN"."DBID"="SQL"."DBID" AND "SN"."SNAP_ID"="SQL"."SNAP_ID" AND
              "SN"."INSTANCE_NUMBER"="SQL"."INSTANCE_NUMBER")
       filter("SQL"."SNAP_ID">
=834 AND "SQL"."SNAP_ID"<=840)


Statistiques
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        434  consistent gets
          0  physical reads
          0  redo size
       1463  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          9  rows processed

C’est efficace, le requête est exécutée en 54 centièmes de secondes, et son plan d’exécution est satisfaisant … bon reste maintenant à exploiter ce type de tables.

C’est sympas mais …

Mon top N est par rapport aux lignes … Mettons que je veuilles transposer la table résultat (je veux dire vraiment transposer, comme dans un tableur). C’est à dire dire je veux un snap par ligne, et N colonnes, qui vont donner les N SQL_id les plus importants sur la fenêtre de snaps choisie … C’est un poil plus compliqué, mais peut être pas tant que ça.

create or replace function f_trsp   ( lig   in varchar2
                                        , col   in varchar2
                                        , ltab  in varchar2
                                        , pivot in varchar2
     , owner in varchar2 
     , ctab  in varchar2 
     , mini  in varchar2 
     , maxi  in varchar2
                                        , topn  in number
                                        , cumul in varchar2 := 'No') return clob is
v_col   varchar2(32767);
v_where varchar2(32767);
v_wtopn varchar2(1000);
v_owner varchar2(255);
c1 sys_refcursor;
v_poreq varchar2(32767);
v_tab   varchar2(32767);
req     clob;
begin
 
        if topn is null
        then
          v_wtopn:='';
        else
          v_wtopn:=' where rownum <= '||topn;
        end if;
        
 if owner is null
 then 
  select sys_context('USERENV','CURRENT_SCHEMA') into v_owner from sys.dual;
 else
  v_owner:=owner;
 end if;
 
 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;
 
 if ctab is null 
 then
  v_tab:=ltab;
 else
  v_tab:=ctab;
 end if;
 
        if upper(cumul) = 'NO'
        then
          req:='select * from (Select '||lig||'
    , sum('||pivot||') total ';
          open c1 for 'select distinct to_char('||col||') from '||v_owner||'.'||nvl(ctab,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(v_col) ;
                  dbms_lob.writeappend(req,length(v_poreq),v_poreq);
          end loop;
  -- ncol:=c1%rowcount+2;
          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);
        else
          req:='Select '||col||' ';
          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(v_col) ;
                  dbms_lob.writeappend(req,length(v_poreq),v_poreq);
          end loop;
  -- ncol:=c1%rowcount+2;
          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;        
        return req;
end f_trsp;
/

testons après les flush mémoire qui vont bien.

select f_trsp('sql_id'
                               , 'snap_id'
                               , 'dba_hist_sqlstat'
                               , 'ELAPSED_TIME_DELTA'
                               , 'sys'
                               , 'dba_hist_snapshot'
                               , '834'
                               , '840'
                               , 9
                               , 'No') lig from dual
/

LIG
------------------------------------------------------------------------------
select * from (Select sql_id
    , sum(ELAPSED_TIME_DELTA) total
       , sum(decode(to_char(snap_id),'834',ELAPSED_TIME_DELTA,0)) c_834
       , sum(decode(to_char(snap_id),'835',ELAPSED_TIME_DELTA,0)) c_835
       , sum(decode(to_char(snap_id),'836',ELAPSED_TIME_DELTA,0)) c_836
       , sum(decode(to_char(snap_id),'837',ELAPSED_TIME_DELTA,0)) c_837
       , sum(decode(to_char(snap_id),'838',ELAPSED_TIME_DELTA,0)) c_838
       , sum(decode(to_char(snap_id),'839',ELAPSED_TIME_DELTA,0)) c_839
       , sum(decode(to_char(snap_id),'840',ELAPSED_TIME_DELTA,0)) c_840
  from sys.dba_hist_sqlstat where snap_id >
= 834 and snap_id <= 840
  group by sql_id
  order by 2 desc) where rownum <= 9


Ecoulé : 00 :00 :00.76

select f_trsp('sql_id'
                               , 'snap_id'
                               , 'dba_hist_sqlstat'
                               , 'ELAPSED_TIME_DELTA'
                               , 'sys'
                               , 'dba_hist_snapshot'
                               , '834'
                               , '840'
                               , 9
                               , 'yes') lig from dual
/

LIG
----------------------------------------------------------------------------------------------
Select snap_id
       , sum(decode(to_char(sql_id),'6gvch1xu9ca3g',ELAPSED_TIME_DELTA,0)) c_6gvch1xu9ca3g
       , sum(decode(to_char(sql_id),'459f3z9u4fb3u',ELAPSED_TIME_DELTA,0)) c_459f3z9u4fb3u
       , sum(decode(to_char(sql_id),'7mdacxfm37utk',ELAPSED_TIME_DELTA,0)) c_7mdacxfm37utk
       , sum(decode(to_char(sql_id),'2b064ybzkwf1y',ELAPSED_TIME_DELTA,0)) c_2b064ybzkwf1y
       , sum(decode(to_char(sql_id),'70vs1d7ywk5m0',ELAPSED_TIME_DELTA,0)) c_70vs1d7ywk5m0
       , sum(decode(to_char(sql_id),'a8j39qb13tqkr',ELAPSED_TIME_DELTA,0)) c_a8j39qb13tqkr
       , sum(decode(to_char(sql_id),'3am9cfkvx7gq1',ELAPSED_TIME_DELTA,0)) c_3am9cfkvx7gq1
       , sum(decode(to_char(sql_id),'6v7n0y2bq89n8',ELAPSED_TIME_DELTA,0)) c_6v7n0y2bq89n8
       , sum(decode(to_char(sql_id),'2whm2vvjb98k7',ELAPSED_TIME_DELTA,0)) c_2whm2vvjb98k7
  from sys.dba_hist_sqlstat where snap_id >
= 834 and snap_id <= 840
  group by snap_id
  order by 1 asc


Ecoulé : 00 :00 :00.62

Les requêtes sont bien générées … Alors on teste avec flush et autotrace …

select * from (Select sql_id
    , sum(ELAPSED_TIME_DELTA) total
       , sum(decode(to_char(snap_id),'834',ELAPSED_TIME_DELTA,0)) c_834
       , sum(decode(to_char(snap_id),'835',ELAPSED_TIME_DELTA,0)) c_835
       , sum(decode(to_char(snap_id),'836',ELAPSED_TIME_DELTA,0)) c_836
       , sum(decode(to_char(snap_id),'837',ELAPSED_TIME_DELTA,0)) c_837
       , sum(decode(to_char(snap_id),'838',ELAPSED_TIME_DELTA,0)) c_838
       , sum(decode(to_char(snap_id),'839',ELAPSED_TIME_DELTA,0)) c_839
       , sum(decode(to_char(snap_id),'840',ELAPSED_TIME_DELTA,0)) c_840
  from sys.dba_hist_sqlstat where snap_id >
= 834 and snap_id <= 840
  group by sql_id
  order by 2 desc) where rownum <= 9
/

SQL_ID             TOTAL      C_834      C_835      C_836      C_837      C_838      C_839      C_840
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
6gvch1xu9ca3g   42391392   13013927    5392808    5393650    4953967    3261169    4969584    5406287
459f3z9u4fb3u   23436549   13594062    1852118    2019437    1463763    1243657    1659715    1603797
7mdacxfm37utk   17700940    3612265    2736617    2562545    2386164    1282571    2257281    2863497
2b064ybzkwf1y    7346698    1214429    1087398    1132594    1069030     781162    1021148    1040937
70vs1d7ywk5m0    5545662    5545662          0          0          0          0          0          0
a8j39qb13tqkr    5468028    2037413     585054     672582     653276     451146     484936     583621
3am9cfkvx7gq1    5258369     906223     765851     693923     830057     601760     762278     698277
6v7n0y2bq89n8    4980042     970111     742508     774718     700769     417384     657582     716970
2whm2vvjb98k7    4015376    4015376          0          0          0          0          0          0

9 ligne(s) sélectionnée(s).

Ecoulé : 00 :00 :00.65


Plan hash value: 950671510

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |   112 |    54   (6)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY               |               |       |       |            |          |       |       |
|   2 |   VIEW                       |               |     1 |   112 |    54   (6)| 00:00:01 |       |       |
|*  3 |    SORT ORDER BY STOPKEY     |               |     1 |    49 |    54   (6)| 00:00:01 |       |       |
|   4 |     HASH GROUP BY            |               |     1 |    49 |    54   (6)| 00:00:01 |       |       |
|*  5 |      HASH JOIN               |               |   105 |  5145 |    52   (2)| 00:00:01 |       |       |
|   6 |       PART JOIN FILTER CREATE| :BF0000       |     8 |   128 |     3   (0)| 00:00:01 |       |       |
|*  7 |        TABLE ACCESS FULL     | WRM$_SNAPSHOT |     8 |   128 |     3   (0)| 00:00:01 |       |       |
|   8 |       PARTITION RANGE AND    |               |   612 | 20196 |    48   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|*  9 |        TABLE ACCESS FULL     | WRH$_SQLSTAT  |   612 | 20196 |    48   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
--------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=9)
   3 - filter(ROWNUM<=9)
   5 - access("SN"."DBID"="SQL"."DBID" AND "SN"."SNAP_ID"="SQL"."SNAP_ID" AND
              "SN"."INSTANCE_NUMBER"="SQL"."INSTANCE_NUMBER")
   7 - filter("SN"."SNAP_ID">
=834 AND "SN"."SNAP_ID"<=840 AND "SN"."STATUS"=0)
   9 - filter("SQL"."SNAP_ID">
=834 AND "SQL"."SNAP_ID"<=840)


Statistiques
----------------------------------------------------------
       4049  recursive calls
          4  db block gets
       1018  consistent gets
        274  physical reads
          0  redo size
       1463  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         85  sorts (memory)
          0  sorts (disk)
          9  rows processed


Select snap_id
       , sum(decode(to_char(sql_id),'6gvch1xu9ca3g',ELAPSED_TIME_DELTA,0)) c_6gvch1xu9ca3g
       , sum(decode(to_char(sql_id),'459f3z9u4fb3u',ELAPSED_TIME_DELTA,0)) c_459f3z9u4fb3u
       , sum(decode(to_char(sql_id),'7mdacxfm37utk',ELAPSED_TIME_DELTA,0)) c_7mdacxfm37utk
       , sum(decode(to_char(sql_id),'2b064ybzkwf1y',ELAPSED_TIME_DELTA,0)) c_2b064ybzkwf1y
       , sum(decode(to_char(sql_id),'70vs1d7ywk5m0',ELAPSED_TIME_DELTA,0)) c_70vs1d7ywk5m0
       , sum(decode(to_char(sql_id),'a8j39qb13tqkr',ELAPSED_TIME_DELTA,0)) c_a8j39qb13tqkr
       , sum(decode(to_char(sql_id),'3am9cfkvx7gq1',ELAPSED_TIME_DELTA,0)) c_3am9cfkvx7gq1
       , sum(decode(to_char(sql_id),'6v7n0y2bq89n8',ELAPSED_TIME_DELTA,0)) c_6v7n0y2bq89n8
       , sum(decode(to_char(sql_id),'2whm2vvjb98k7',ELAPSED_TIME_DELTA,0)) c_2whm2vvjb98k7
  from sys.dba_hist_sqlstat where snap_id >
= 834 and snap_id <= 840
  group by snap_id
  order by 1 asc
/

   SNAP_ID C_6GVCH1XU9CA3G C_459F3Z9U4FB3U C_7MDACXFM37UTK C_2B064YBZKWF1Y C_70VS1D7YWK5M0 C_A8J39QB13TQKR C_3AM9CFKVX7GQ1 C_6V7N0Y2BQ89N8 C_2WHM2VVJB98K7
---------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------
       834        13013927        13594062         3612265         1214429         5545662         2037413          906223          970111         4015376
       835         5392808         1852118         2736617         1087398               0          585054          765851          742508               0
       836         5393650         2019437         2562545         1132594               0          672582          693923          774718               0
       837         4953967         1463763         2386164         1069030               0          653276          830057          700769               0
       838         3261169         1243657         1282571          781162               0          451146          601760          417384               0
       839         4969584         1659715         2257281         1021148               0          484936          762278          657582               0
       840         5406287         1603797         2863497         1040937               0          583621          698277          716970               0

7 ligne(s) sélectionnée(s).

Ecoulé : 00 :00 :01.07


Plan hash value: 3006464286

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |     1 |    49 |    53   (4)| 00:00:01 |       |    |
|   1 |  SORT GROUP BY            |               |     1 |    49 |    53   (4)| 00:00:01 |       |    |
|*  2 |   HASH JOIN               |               |   105 |  5145 |    52   (2)| 00:00:01 |       |    |
|   3 |    PART JOIN FILTER CREATE| :BF0000       |     8 |   128 |     3   (0)| 00:00:01 |       |    |
|*  4 |     TABLE ACCESS FULL     | WRM$_SNAPSHOT |     8 |   128 |     3   (0)| 00:00:01 |       |    |
|   5 |    PARTITION RANGE AND    |               |   612 | 20196 |    48   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|*  6 |     TABLE ACCESS FULL     | WRH$_SQLSTAT  |   612 | 20196 |    48   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
-----------------------------------------------------------------------------------------------------------

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

   2 - access("SN"."DBID"="SQL"."DBID" AND "SN"."SNAP_ID"="SQL"."SNAP_ID" AND
              "SN"."INSTANCE_NUMBER"="SQL"."INSTANCE_NUMBER")
   4 - filter("SN"."SNAP_ID">
=834 AND "SN"."SNAP_ID"<=840 AND "SN"."STATUS"=0)
   6 - filter("SQL"."SNAP_ID">
=834 AND "SQL"."SNAP_ID"<=840)


Statistiques
----------------------------------------------------------
       3968  recursive calls
          0  db block gets
        991  consistent gets
        308  physical reads
          0  redo size
       1429  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         76  sorts (memory)
          0  sorts (disk)
          7  rows processed

On réécrit, on commente, on enlève les variables inutiles …

create or replace function f_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 (forcément numérique)
         , 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)
         ) return clob is            -- Generation d'un ordre SQL dans un Clob
 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);  
 req      clob;             
begin
 -- 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||', sum('||pivot||') total ';
   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(v_col) ;
    dbms_lob.writeappend(req,length(v_poreq),v_poreq);
   end loop;
   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||' ';
   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(v_col) ;
     dbms_lob.writeappend(req,length(v_poreq),v_poreq);
   end loop;
   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;        
 
 return req;
end f_trsp;
/

Bon c’est mignon, mais on a un nombre de colonnes variables et non défini en résultat, du coup c’est pas forcément simple à utiliser dans un code PL/SQL … A suivre

Stored outlines

Pour voir quelle catégorie d’outlines est utilisée au niveau instance il suffit de suivre La note metalink (internal only, you don’t have access) ou de retrouver ses diverses transcription oveurzeunaitte comme par exemple sur le site Oracle Guy ou le blog El Caro histoire de rester sur Blogger.

Donc que est l’intérêt de ce post ??? Le français de France allons, pour les dba qui se vrillent l’œil dès que la Perfide Albion vient envahir leur champ de vision

Alors comment qu’on fait Madame ?

Simplissime, on sort le code hexa d’une partie de la SGA (ou de l’UGA si des fois on fait ça au niveau session) L’enfance de l’art non ?

Les Instructions qui vont bien :

SQL> oradebug setmypid
SQL> oradebug dumpvar sga sgauso

les résultats sont pour le moins cocaces

qolprm sgauso_ [20014F44, 20014F68) = 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000

Qui signifie : « FALSE » … Oui c’est pas forcément évident

qolprm sgauso_ [8001304C, 80013070) = 00000001 00074445 4641554C 54000000 ... 

Qui signifie : « DEFAULT » … Sachant que DEFAULT fait 7 lettres et qu’en hexadécimal DEFAULT s’écrit 4445 4641554c 54, on commence à comprendre, on la refait doucement :

qolprm sgauso_ [8001304C, 80013070) = 00000001 00074445 4641554C 54000000 ... 
7 D E F A U L T

OK ?

Rendre dbms_metadata lisible

DBMS_METADATA lisible, comment faire ?

Le package dbms_metadata existe depuis la version 9i et permet de regénerer les commandes DDL relatives aux objets ORACLE … Maintenant, pour l’utilisation et la liste des focntion au choix : RTFM ou RTFDB (DB pour Donald B le bien connu copiste en chef de la documentation Oracle) ce qui, au final revient à peu près au même. Cependant, souvent sa sortie contient des informations dont on se passerait bien, comme la gestion des extents ou autres alors qu’on utilise la plupart du temps les paramètres par défaut du tablespace.

Deux trois petites astuces bien sympa à retenir

  • Ne jamais faire un dbms_metadata sur une liste d’objets (si un objet plante, les suivants ne sont pas traités)
  • Utiliser long 9999999 et longc 9999999
  • Utiliser des lignes larges (300 à 400 caractères, on peut aller jusqu’à 32768 soit 32k) et un « trimspool on » s’il on choisit de rediriger la sortie dans un fichier.
  • Demander la mise en forme avec les terminaisons SQL ( / ou ; )
    exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE ); 
  • Eviter les paramètres de stockage afin d’utiliser ceux du tablespace :
    exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false); 

L’exemple suivant donne le DDL de la table WRH$_SQL_PLAN dans ses deux versions

Comportement par défaut de DBMS_METADATA

select dbms_metadata.get_ddl('TABLE','WRH$_SQL_PLAN',USER) from sys.dual ;
DBMS_METADATA.GET_DDL('TABLE','WRH$_SQL_PLAN',USER)
--------------------------------------------------------------------------

  CREATE TABLE "SYS"."WRH$_SQL_PLAN"
   (    "SNAP_ID" NUMBER,
        "DBID" NUMBER NOT NULL ENABLE,

        [...]
        
        "OTHER_XML" CLOB,
         CONSTRAINT "WRH$_SQL_PLAN_PK" PRIMARY KEY ("DBID", "SQL_ID", "PLAN_HASH_VALUE", "ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"
 LOB ("OTHER_XML") STORE AS BASICFILE (
  TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

DBMS_METADATA lisible, ou du moins beaucoup moins inutilement chargé

Paramétrage

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

PL/SQL procedure successfully completed.

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);

PL/SQL procedure successfully completed.

sortie DBMS_METADATA lisible

select dbms_metadata.get_ddl('TABLE','WRH$_SQL_PLAN',USER) from sys.dual ;
DBMS_METADATA.GET_DDL('TABLE','WRH$_SQL_PLAN',USER)
--------------------------------------------------------------------------

  CREATE TABLE "SYS"."WRH$_SQL_PLAN"
   (    "SNAP_ID" NUMBER,
        "DBID" NUMBER NOT NULL ENABLE,

        [...]

        "OTHER_XML" CLOB,
         CONSTRAINT "WRH$_SQL_PLAN_PK" PRIMARY KEY ("DBID", "SQL_ID", "PLAN_HASH_VALUE", "ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "SYSAUX"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "SYSAUX"
 LOB ("OTHER_XML") STORE AS BASICFILE (
  TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING ) ;

Comptons les logs (redo ou arch) générés

Je l’oublie toujours et pourtant je l’utilise aussi souvent que possible : Le script qui donne le nombre de logs par heures …

SET VERIFY OFF

COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'

SELECT
TO_CHAR(first_time, 'MM/DD') DAY
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'00',1,0)) H00
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'01',1,0)) H01
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'02',1,0)) H02
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'03',1,0)) H03
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'04',1,0)) H04
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'05',1,0)) H05
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'06',1,0)) H06
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'07',1,0)) H07
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'08',1,0)) H08
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'09',1,0)) H09
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'10',1,0)) H10
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'11',1,0)) H11
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'12',1,0)) H12
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'13',1,0)) H13
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'14',1,0)) H14
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'15',1,0)) H15
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'16',1,0)) H16
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'17',1,0)) H17
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'18',1,0)) H18
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'19',1,0)) H19
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'20',1,0)) H20
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'21',1,0)) H21
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'22',1,0)) H22
, SUM(DECODE(TO_CHAR(first_time, 'HH24'),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v$log_history a
GROUP BY TO_CHAR(first_time, 'MM/DD')
/

Ce qui donne le fameux :

DAY     00   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23    Total
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --------
08/14 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 12 16
08/15 12 13 14 13 9 14 13 12 10 15 9 14 15 7 17 5 17 7 15 8 16 4 19 5 283
08/16 15 8 6 17 5 17 9 11 11 18 12 2 17 15 10 3 14 20 2 13 0 20 17 1 263
08/17 11 0 20 19 3 10 1 21 18 2 12 0 12 18 13 4 11 0 16 23 8 0 0 0 222
08/28 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1
09/09 0 0 0 0 0 0 0 0 0 0 4 1 0 0 36 15 2 0 0 0 0 0 13 13 84
09/10 2 0 16 2 16 11 1 0 17 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 69
10/16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 25 2 8 11 50
10/17 6 0 5 8 4 6 10 1 0 1 2 1 2 13 12 0 1 6 7 7 1 0 2 2 97
10/18 3 3 3 5 18 2 4 7 5 9 1 2 2 3 3 3 6 1 18 1 0 0 0 0 99
10/23 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
10/27 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1
10/28 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 11 10 0 0 0 0 24
10/29 0 0 0 0 0 0 0 0 0 4 18 7 0 3 5 5 6 6 4 3 3 5 4 15 88
10/30 1 1 5 5 6 3 4 7 3 3 2 8 0 17 0 0 0 0 0 0 0 0 0 0 65

Bien pratique pour tailler les logs