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