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

Paramètres internes de l’instance

La table x$kvit donne une liste de paramètres dynamiques utilisés au moment du select.

La version 10g remonte la liste suivante :

select KVITTAG
, KVITDSC
, KVITVAL
from x$kvit
/

KVITTAG KVITDSC KVITVAL
-------------------- ---------------------------------------------------------------- ----------
ksbcpu number of logical CPUs in the system used by Oracle 2
ksbcpucore number of physical CPU cores in the system used by Oracle 0
ksbcpusocket number of physical CPU sockets in the system used by Oracle 0
ksbcpu_hwm high water mark of number of CPUs used by Oracle 2
ksbcpucore_hwm high water mark of number of CPU cores on system 0
ksbcpusocket_hwm high water mark of number of CPU sockets on system 0
ksbcpu_actual number of available CPUs in the system 2
ksbcpu_dr CPU dynamic reconfiguration supported 1
kcbnbh number of buffers 136551
kcbldq large dirty queue if kcbclw reaches this 25
kcbfsp Max percentage of LRU list foreground can scan for free 40
kcbcln Initial percentage of LRU list to keep clean 2
kcbnbf number buffer objects 750
kcbwst Flag that indicates recovery or db suspension 0
kcteln Error Log Number for thread open 0
kcvgcw SGA: opcode for checkpoint cross-instance call 0
kcvgcw SGA:opcode for pq checkpoint cross-instance call 0

La version 9i remonte la liste suivante :


KVITTAG KVITDSC KVITVAL
-------------------- ---------------------------------------------------------------- ----------
kcbnbh number of buffers 381120
kcbldq large dirty queue if kcbclw reaches this 25
kcbdsp Max percentage of LRU list dbwriter can scan for dirty 25
kcbfsp Max percentage of LRU list foreground can scan for free 40
kcbnbf number buffer objects 5000
kcbwst Flag that indicates recovery or db suspension 0
kcteln Error Log Number for thread open 0
kcvgcw SGA: opcode for checkpoint cross-instance call 0

Incidence du statistics level

La variable statistics level permet des stocker des statistiques dans certaines vues.

La requête suivante permet de voir quelle vue est impactée, à quel niveau de statistique et si le calcul de la statistique peut être modifié au niveau session

select decode(ACTIVATION_LEVEL,1,'Typical','All') "A Level"
, Name
, decode(SESSION_CHANGEABLE,1,'Ok',' -') "Modif"
, decode(SESSION_STATUS,1,'On',' -') "Sess"
, decode(SYSTEM_STATUS,1,'On',' -') "Sys"
, VIEW_NAME
from x$prmsltyx
order by ACTIVATION_LEVEL
/

En 11gR2 on obtient

A Level NAME                                                             Mo Se Sy VIEW_NAME
------- ---------------------------------------------------------------- -- -- -- -------------------------
Typical Buffer Cache Advice - On On V$DB_CACHE_ADVICE
Typical V$IOSTAT_* statistics - On On
Typical Timed Statistics Ok On On
Typical MTTR Advice - On On V$MTTR_TARGET_ADVICE
Typical Adaptive Thresholds Enabled - On On
Typical Segment Level Statistics - On On V$SEGSTAT
Typical PGA Advice - On On V$PGA_TARGET_ADVICE
Typical Shared Pool Advice - On On V$SHARED_POOL_ADVICE
Typical Modification Monitoring - On On
Typical Longops Statistics - On On V$SESSION_LONGOPS
Typical Bind Data Capture - On On V$SQL_BIND_CAPTURE
Typical Ultrafast Latch Statistics - On On
Typical Threshold-based Alerts - On On
Typical Global Cache Statistics - On On
Typical Active Session History - On On V$ACTIVE_SESSION_HISTORY
Typical Undo Advisor, Alerts and Fast Ramp up - On On V$UNDOSTAT
Typical Streams Pool Advice - On On V$STREAMS_POOL_ADVICE
Typical Time Model Events Ok On On V$SESS_TIME_MODEL
Typical Plan Execution Sampling Ok On On V$ACTIVE_SESSION_HISTORY
Typical Automated Maintenance Tasks - On On
Typical SQL Monitoring Ok On On V$SQL_MONITORING
All Plan Execution Statistics Ok - - V$SQL_PLAN_STATISTICS
All Timed OS Statistics Ok - -

En 10g on obtient

A Level NAME                                     Mo Se Sy VIEW_NAME
------- ---------------------------------------- -- -- -- ------------------------------
Typical Buffer Cache Advice - On On V$DB_CACHE_ADVICE
Typical Streams Pool Advice - On On V$STREAMS_POOL_ADVICE
Typical Timed Statistics Ok On On
Typical MTTR Advice - On On V$MTTR_TARGET_ADVICE
Typical Undo Advisor, Alerts and Fast Ramp up - On On V$UNDOSTAT
Typical Segment Level Statistics - On On V$SEGSTAT
Typical PGA Advice - On On V$PGA_TARGET_ADVICE
Typical Shared Pool Advice - On On V$SHARED_POOL_ADVICE
Typical Modification Monitoring - On On
Typical Longops Statistics - On On V$SESSION_LONGOPS
Typical Bind Data Capture - On On V$SQL_BIND_CAPTURE
Typical Ultrafast Latch Statistics - On On
Typical Threshold-based Alerts - On On
Typical Global Cache Statistics - On On
Typical Active Session History - On On V$ACTIVE_SESSION_HISTORY
All Plan Execution Statistics Ok - - V$SQL_PLAN_STATISTICS
All Timed OS Statistics Ok - -

En 9i on obtient

A Level NAME                           Mo Se Sy VIEW_NAME
------- ------------------------------ -- -- -- ------------------------------
Typical Buffer Cache Advice - On On V$DB_CACHE_ADVICE
Typical MTTR Advice - On On V$MTTR_TARGET_ADVICE
Typical Timed Statistics Ok On On
Typical PGA Advice - On On V$PGA_TARGET_ADVICE
Typical Shared Pool Advice - On On V$SHARED_POOL_ADVICE
Typical Segment Level Statistics - On On V$SEGSTAT
All Timed OS Statistics Ok - -
All Plan Execution Statistics Ok - - V$SQL_PLAN_STATISTICS

Lister les tables X$ d’une version Oracle

Ce petit script PERL permet de lister les tables “X$” du moteur … Ce qui ne dit pas ce qu’elles font

#!/usr/bin/perl -w

use strict;

#open O, ($ORACLE_HOME."/bin/oracle");
open O, ("/logiciels/oracle/ora_10.2.0/bin/oracle");
open F, (">x");

my $l;
my $p = ' ' x 40;
my %x;

while (read (O,$l,10000)) {
$l = $p.$l;

foreach ($l =~ /(x$w{3,})/g) {
$x{$_}++;
}

$p = substr ($l,-40);
}

foreach (sort keys %x) {
print F "$_n";
}

Ce script provient du site http://www.adp-gmbh.ch

On peut trouver certaines explications sur le contenu des tables “X$” sur freeshell, sur le site BC Oracle Training ou en bien d’autres endroits 😉

La version 10 d’oracle en liste quelques 350 par exemple la table x$kwddef liste l’ensemble des mots clés du SQL, la requête suivante permet donc de les lister :

select KEYWORD from x$kwddef
order by KEYWORD
/

Générer des rapports statspack

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

SET SERVEROUTPUT OFF
set feedback off

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

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

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

@reports.sql

drop procedure dba_all_reports;

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

On peut décommenter les lignes

--  :date_fin:=&2;

et

-- execute dba_all_reports(:delta, :date_fin)

et supprimer la ligne

execute

dba_all_reports(:delta)

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

Les requêtes consommatrices

Bon un petit peu de technique histoire d’aider ma mémoire avec une requête bien sympatoche histoire de tracer la consommation SQL d’une base


select sql_id, dsk_rd, tot_rd, to_char((dsk_rd/tot_rd)*100,'999D99') || ' %' "PCT RDS"
from (select distinct 1 c1
, sql_id
, sum(disk_reads_DELTA) over (partition by sql_id) dsk_rd
, sum(disk_reads_delta) over (partition by 1) tot_rd
from DBA_HIST_SQLSTAT
where nvl(disk_reads_DELTA,0) > 0
order by 3 desc)
where rownum < 11
and tot_rd > 0

Au résultat on obtient un petit tableau de ce type


SQL_ID DSK_RD TOT_RD PCT RDS
------------- ---------- ---------- ---------
b7jn4mf49n569 6234 21773 28,63 %
gfjvxb25b773h 1990 21773 9,14 %
c7sn076yz7030 939 21773 4,31 %
cvn54b7yz0s8u 849 21773 3,90 %
cfz686a6qp0kg 808 21773 3,71 %
c2p32r5mzv8hb 709 21773 3,26 %
7q2wnyku8mfv0 630 21773 2,89 %
b3ys9bs1v9cch 547 21773 2,51 %
39m4sx9k63ba2 437 21773 2,01 %
02577v815yp77 412 21773 1,89 %

Bien cryptique ? Certes mais utile dans le cadre d’un audit de perf. (à partir de la version 10 d’oracle).

Pour tracer une évolution au travers des snapshots AWR (Automatic Workload Reppository) on pourra modifier de cette manière :


break on snap_id skip 1

select SNAP_ID
, SQL_ID
, disk_reads_DELTA
, to_char((disk_reads_delta/tot)*100,'999D99') || ' %' "PCT RDS"
from (select SNAP_ID
, SQL_ID
, disk_reads_DELTA
, dense_rank() over ( partition by snap_id order by disk_reads_DELTA desc, sql_id asc) rk
, sum(disk_reads_DELTA) over ( partition by snap_id) tot
from DBA_HIST_SQLSTAT
where nvl(disk_reads_DELTA,0) > 0)
where rk < 11
and tot > 0
order by snap_id, rk

La remontée est alors du type :


SNAP_ID SQL_ID DISK_READS_DELTA PCT RDS
---------- ------------- ---------------- ---------
273 b3ys9bs1v9cch 18 42,86 %
4ng4xn7jw3dbb 8 19,05 %
0a1k5zuh04f38 7 16,67 %
96g93hntrzjtr 3 7,14 %
bunssq950snhf 2 4,76 %
as3uq6ggb3gx6 1 2,38 %
f4fvybbfcbk5y 1 2,38 %
gc7b0drtzbyc6 1 2,38 %
8sd37ztuwkyyc 1 2,38 %

276 dnwpm0gdccrph 6 100,00 %

SNAP_ID SQL_ID DISK_READS_DELTA PCT RDS
---------- ------------- ---------------- ---------

287 g337099aatnuj 1 100,00 %

289 8yh7x8qkgswu2 2 100,00 %

297 b3ys9bs1v9cch 16 42,11 %
5rxbazwmcdfaz 7 18,42 %
70vs1d7ywk5m0 7 18,42 %
4ng4xn7jw3dbb 3 7,89 %
96g93hntrzjtr 3 7,89 %
as3uq6ggb3gx6 1 2,63 %

SNAP_ID SQL_ID DISK_READS_DELTA PCT RDS
---------- ------------- ---------------- ---------
297 gc7b0drtzbyc6 1 2,63 %