Calcul des statistiques

Afficher les valeurs des paramètres utlisés par défaut par dbms_stats

col sname for a30
col SPARE4 for a50

select SNAME, spare4 from SYS.OPTSTAT_HIST_CONTROL$ ;

SNAME SPARE4
------------------------------ --------------------------------------------------
SKIP_TIME
STATS_RETENTION
TRACE 0
DEBUG 0
SYS_FLAGS 1
CASCADE DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE NULL
METHOD_OPT FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE TRUE
GRANULARITY AUTO
AUTOSTATS_TARGET AUTO

12 rows selected.

Les valeurs ici ne sont déjà pas les valeurs utilisée par oracle par défaut, NO_INVALIDATE a été modifiée

Ecrire une procédure de calcul des statistiques

Pour un client, j’ai écrit très rapidement cette procédure de calcul de statistiques. Très (trop) imparfaite

create or replace procedure sys.calc_stats is
/*
* Procédure de calcul des statistiques ****************************
*
* Les statistiques son calculees sur les tables dont le cumul des modifications
* (insert / delete / updates) dépasse 5% du volume de la table. Elle invalide
* les plans en memoire et ignore ertains utilisateurs. Son parallelisme est de 4
* et les statistiques ne sont pas calculees sur les index de la table
*
* ******************************************************************************
* Version 1.0 pour ************ sur ORA 102040
* ******************************************************************************
*/
begin
for c in (
select tm.table_name tbn
, t.owner tbo
from ( select table_name
, table_owner
, sum(inserts) inserts
, sum(deletes) deletes
, sum(updates) updates
from dba_tab_modifications
where table_owner not in ( 'SYS', 'SYSTEM', 'SYSMAN', 'TOOLS'
, 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'GOLDENGATE'
, 'XDB', 'TOAD', 'OUTLN', 'TSMSYS', 'ORDSYS')
group by table_name, table_owner ) tm
inner join dba_tables t
on ( t.owner=tm.table_owner
and t.table_name=tm.table_name )
where 1=1
and t.owner not in ( 'SYS', 'SYSTEM', 'SYSMAN', 'TOOLS'
, 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'GOLDENGATE'
, 'XDB', 'TOAD', 'OUTLN', 'TSMSYS', 'ORDSYS')
and (tm.inserts+tm.updates+tm.deletes)/(nvl(t.num_rows,0)+1) > 0.05
order by nvl(t.num_rows,0) asc )
loop
dbms_stats.gather_table_stats(c.tbo, c.tbn, null, null, null, 'for all indexed columns size repeat', 4, 'ALL', FALSE, null, null, null, FALSE );
end loop;
end;
/

Manque du paramétrage et de l’automatisme

Paramétrage
  • seuil de calcul
  • no_invalidate
Automatismes
  • Echantillonnage
  • Cascade

A faire évoluer donc …

Package de diagnostique pour les undotbs

Les scripts se basent sur l’activité AWR pour faire leur diagnostique, en toute logique leur utilisation est soumise à licence.

set serveroutput on 

DECLARE
tsn VARCHAR2(40);
tss NUMBER(10);
aex BOOLEAN;
unr NUMBER(5);
rgt BOOLEAN;
retval BOOLEAN;
BEGIN
retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
dbms_output.put_line('Undo Tablespace Is: ' || tsn);
dbms_output.put_line('Undo Tablespace Size Is: ' || TO_CHAR(tss));

IF aex THEN
dbms_output.put_line('Undo Autoextend Is Set To: TRUE');
ELSE
dbms_output.put_line('Undo Autoextend Is Set To: FALSE');
END IF;

dbms_output.put_line('Undo Retention Is: ' || TO_CHAR(unr));

IF rgt THEN
dbms_output.put_line('Undo Guarantee Is Set To: TRUE');
ELSE
dbms_output.put_line('Undo Guarantee Is Set To: FALSE');
END IF;
END;
/

Résultat

Undo Tablespace Is: UNDOTBS1
Undo Tablespace Size Is: 32768
Undo Autoextend Is Set To: TRUE
Undo Retention Is: 900
Undo Guarantee Is Set To: FALSE

Estimer le meilleur paramétrage pour “undo_retention” Entre 2 dates

SELECT  dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE)
FROM dual;

En général

SELECT  dbms_undo_adv.best_possible_retention
FROM dual;

Entre 2 snapshots AWR

SELECT dbms_undo_adv.best_possible_retention(10, 20)
FROM dual;

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

01
02
03
04
05
06
07
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 ) ;

Sauvegarder la config de putty

Putty

Putty est un client multiprotocole pour windows diponible sur le site putty.org. La sauvegarde de sa configuration n’est plus très facile depuis qu’il ne s’appuie plus sur un fichier de configuration mais sur la base de registre de Windows. Le script suivant permet la sauvegarde des la configuration putty dans un fichier reg

Le script

@ECHO OFF
regedit /s putty.reg
regedit /s puttyrnd.reg
start /w putty.exe
regedit /ea new.reg HKEY_CURRENT_USERSoftwareSimonTathamPuTTY
copy new.reg putty.reg
del new.reg
regedit /s puttydel.reg

Le script a été trouvé dans la doc PuTTY et utilise deux fichiers de paramètre puttyrnd.reg et puttydel.reg. leur utilisation est expliquée sur le site de la doc

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;}'

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