Exploitation, Unix

crs_stat -t c’est pas toujours cool

Un petit script piqué à Jeff Hunter (www.ideveloppement.info) et un poil modifié afin d’obtenir l’état de toutes les ressources du Cluster. Plus sympa à lire qu’un crs_stat -t. 

#!/bin/ksh

QSTAT=-u
AWK=/usr/bin/nawk

# +----------------------------------------------------------------------------+
# | TABLE HEADER                                                               |
# +----------------------------------------------------------------------------+

$AWK 
'BEGIN {printf "%-45s %-10s %-18sn", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18sn", "-----------", "------", "-----";}'

# +----------------------------------------------------------------------------+
# | TABLE BODY                                                                 |
# +----------------------------------------------------------------------------+

$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK 
'BEGIN { FS="="; state = 0; }
$1~/NAME/ {appname = $2; state=1;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18sn", appname, apptarget, appstate; state=0;}
state == 0 {next;}'

Résultat : 

HA Resource                                   Target     State
-----------                                   ------     -----
ora.TESTRAC.TESTRAC1.inst                     ONLINE     ONLINE on rac01
ora.TESTRAC.TESTRAC2.inst                     ONLINE     ONLINE on rac02
ora.TESTRAC.db                                ONLINE     ONLINE on rac02
ora.TOTO.TOTO1.inst                           ONLINE     ONLINE on rac01
ora.TOTO.TOTO2.inst                           ONLINE     ONLINE on rac02
ora.TOTO.db                                   ONLINE     ONLINE on rac02
ora.rac01.ASM1.asm                            ONLINE     ONLINE on rac01
ora.rac01.ASM_LIST_rac01.lsnr                 ONLINE     ONLINE on rac01
ora.rac01.gsd                                 ONLINE     ONLINE on rac01
ora.rac01.ons                                 ONLINE     ONLINE on rac01
ora.rac01.vip                                 ONLINE     ONLINE on rac01
ora.rac02.ASM2.asm                            ONLINE     ONLINE on rac02
ora.rac02.ASM_LIST_rac02.lsnr                 ONLINE     ONLINE on rac02
ora.rac02.gsd                                 ONLINE     ONLINE on rac02
ora.rac02.ons                                 ONLINE     OFFLINE
ora.rac02.vip                                 ONLINE     ONLINE on rac02

Le script original auquel il faut passer en paramètre le nom de la ressource de laquelle on souhaite avoir l’état : 

#!/bin/ksh

# +----------------------------------------------------------------------------+
# |                          Jeffrey M. Hunter                                 |
# |                      jhunter@idevelopment.info                             |
# |                         www.idevelopment.info                              |
# |----------------------------------------------------------------------------|
# |      Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved.       |
# |----------------------------------------------------------------------------|
# | DATABASE : Oracle                                                          |
# | FILE     : rac_crs_stat                                                    |
# | CLASS    : UNIX Shell Scripts                                              |
# | PURPOSE  : This KSH script will query all CRS resources using the crs_stat |
# |            script. The report will be a formatted version of the           |
# |            crs_stat -t command, but in tabular form with resource name     |
# |            and status. Filtering options are available by passing in a     |
# |            single string parameter to this script. This argument will be   |
# |            used to limit the output to HA resources whose names match      |
# |            that string.                                                    |
# | USAGE    : rac_crs_stat.ksh [RESOURCE_KEY]                                 |
# | NOTE     : This script requires the environment $ORA_CRS_HOME to be set to |
# |            your CRS installation.                                          |
# | NOTE     : As with any code, ensure to test this script in a development   |
# |            environment before attempting to run it in production.          |
# +----------------------------------------------------------------------------+

# +----------------------------------------------------------------------------+
# | GLOBAL VARIABLES                                                           |
# +----------------------------------------------------------------------------+

RSC_KEY=$1
QSTAT=-u
AWK=/usr/bin/awk

# +----------------------------------------------------------------------------+
# | TABLE HEADER                                                               |
# +----------------------------------------------------------------------------+

$AWK 
'BEGIN {printf "%-45s %-10s %-18sn", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18sn", "-----------", "------", "-----";}'

# +----------------------------------------------------------------------------+
# | TABLE BODY                                                                 |
# +----------------------------------------------------------------------------+

$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK 
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18sn", appname, apptarget, appstate; state=0;}'

Métrologie, SQL

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

Exploitation, SQL, Support Niveau 2, Support Niveau 3

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
Exploitation, Métrologie, SQL, Support Niveau 2, Support Niveau 3

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
Exploitation, Support Niveau 3, Tuning

Afficher la liste des “fix control” passés sur une base

Les fix control sont apportés par les patchs / patchsets / patchsets updates et influent sur le comportement de l’optimiseur. La table x$qksbgsys les liste et donne leur description succincte

La requête suivante liste les bugs, ce qu’ils font et la version à partir de laquelle ils ont étés introduits

select BUGNO_QKSBGSYROW
  , DESC_QKSBGSYROW
  , OFE_QKSBGSYROW
  , EVENT_QKSBGSYROW
from x$qksbgsys
/

BUGNO_QKSBGSYROW DESC_QKSBGSYROW                                                  OFE_QKSBGSYROW            EVENT_QKSBGSYROW
---------------- ---------------------------------------------------------------- ------------------------- ----------------
        4611850 do not clobber predicate during first-k row estimate phase       10.2.0.2                                 0
        4663804 use smallest table as first table in join card. initial ordering 10.2.0.2                                 0
        4663698 for cached NL table set tab_cost_io to zero                      10.2.0.2                                 0
[...]

La requete suivante sur la table x$qksbgses est équivalente

select distinct BUGNO_QKSBGSEROW
   , DESC_QKSBGSEROW
   , OFE_QKSBGSEROW
   , EVENT_QKSBGSEROW
from x$qksbgses
/

Il est possible de désactiver un des correctifs par hint :

select /*+ OPT_PARAM('_fix_control' '9195582:0') */ * from dual ;

Depuis quelques version les vues V$SYS_FIX_CONTROL et V$SESSION_FIX_CONTROL permettent de voir ces données plus facilement

desc V$SESSION_FIX_CONTROL
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SESSION_ID                                NUMBER
 BUGNO                                     NUMBER
 VALUE                                     NUMBER
 SQL_FEATURE                               VARCHAR2(64)
 DESCRIPTION                               VARCHAR2(64)
 OPTIMIZER_FEATURE_ENABLE                  VARCHAR2(25)
 EVENT                                     NUMBER
 IS_DEFAULT                                NUMBER

desc V$SYSTEM_FIX_CONTROL
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 BUGNO                                     NUMBER
 VALUE                                     NUMBER
 SQL_FEATURE                               VARCHAR2(64)
 DESCRIPTION                               VARCHAR2(64)
 OPTIMIZER_FEATURE_ENABLE                  VARCHAR2(25)
 EVENT                                     NUMBER
 IS_DEFAULT                                NUMBER
Développement, Exploitation, SQL, Support Niveau 3

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
/
Développement

Citation

“The programmers didn’t like the idea at all because it deprived them of the intellectual excitement of not quite understanding what they were doing. They like the challenge of chasing the bugs.”

E. Dijkstra (Prix Turing)

Support Niveau 3

vipca -silent

Créer les adresses vip en utilisant vipca en mode silencieux :

En étant connecté root :

vipca -silent -nodelist rac01,rac02 -nodevips 'rac01/rac01-vip/255.255.255.0/ce1,rac02/rac02-vip/255.255.255.0/ce1'

Gérer le failover des VIP :

crs_stat -p ora.rac01.vip > /var/tmp/ora.rac01.vip.cap
crs_stat -p ora.rac02.vip > /var/tmp/ora.rac02.vip.cap

changer les lignes

HOSTING_MEMBERS=rac01

en

HOSTING_MEMBERS=rac01 rac02

crs_register ora.rac01.vip -dir /var/tmp -u
crs_register ora.rac02.vip -dir /var/tmp -u
Bonnes pratiques, Développement, Exploitation, Métrologie, PL/SQL, SQL, Support Niveau 2, Tuning

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.

Exploitation, Métrologie, SQL

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 %