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